Linking MySQL Workbench to my Remote Server
I've just downloaded MySQL Workbench.
But I don't quite understand how to syn this with the databases on my remote server.
Work bench asks for "hostname" so I provided the hostname of my remote server. I designate port 3306.
I then provide a username. This is the username I use when I log into PhpAdmin -- should I be using a different one?
Then I provide a password, again the same one I use for PhpAdmin.
But this doesn't work.
Oddly, the error always tells me my user name is: username@current_network_im_using_to_access_the_internet
But this doesn't seem right -- on phpAdmin my user name says username@localhost.
I'm not quite sure what to do.
Can you help me?
Answer
MySQL treats logins as specific to the host they originate from. You can have a different password from your home machine than the one you use on the server itself, and you can have entirely different sets of permissions granted to the same username from different origin hosts.
On PHPMyadmin, the database is running on the same server as the web server, and therefore refers to itself as localhost, with IP 127.0.0.1. Your machine on which Workbench is installed must access MySQL with different credentials than your username@localhost. The server requires you to grant access to your username from any host you intend to connect from.
In PhpMyAdmin, you will need to grant access to your database from the remote host: (See also Pekka's answer for how to allow connections from any host)
GRANT ALL PRIVILEGES on dbname.* TO yourusername@your_remote_hostname IDENTIFIED BY 'yourpassword';
To see all the grants you currently have on localhost so that you can duplicate them for the remote host:
SHOW GRANTS FOR yourusername@localhost;
Additionally, the MySQL server needs to be setup to accept remote connections in the first place. This isn't always the case, especially on web hosting platforms. In the my.cnf file, the skip-networking line has to be removed or commented out. If there is no skip-networking line, you must comment out the line:
bind-address = 127.0.0.1
...then restart MySQL.