You will need to know what the IP address you are connecting from.
1) Granting AccessGranting access to a user from a remote host is fairly simple and can be accomplished from just a few steps. First you will need to login to your MySQL server as the root user. You can do this by typing the following command:
# mysql -u root -pThis will prompt you for your MySQL root password.
Once you are logged into MySQL you need to issue the GRANT command that will enable access for your remote user. In this example we will be creating a brand new user (fooUser) that will have full access to the fooDatabase database.
Keep in mind that this statement is not complete and will need some items changed. Please change 184.108.40.206 to the IP address that we obtained above. You will also need to change my_password with the password that you would like to use for fooUser.
mysql> GRANT ALL ON fooDatabase.* TO [email protected]'220.127.116.11' IDENTIFIED BY 'my_password';This statement will grant ALL permissions to the newly created user fooUser with a password of 'my_password' when they connect from the IP address 18.104.22.168
2)Testing RemotelyNow you can test your connection remotely. You can access your MySQL server from another Linux server:
# mysql -u fooUser -p -h 22.214.171.124 Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 17 Server version: 5.0.45 Source distribution Type 'help;' or '\h' for help. Type '\c' to clear the buffer. mysql> _
- Granting ALL permissions is not advised. Using GRANT SELECT,INSERT,UPDATE,DELETE is a wise alternative for a normal user.
- If you are using iptables you will need to make an entry in your firewall for TCP port 3306. When creating your firewall rule you can simply use the name 'mysql' for the port number. Search our wiki for iptables and you will find a list of common rule sets.
- If you would like to grant only to a specific table you can use database.table instead of database.*. In respect to our example above you could put fooDatabase.fooTable.