Hello all,
If you try to connect to your mysql server from remote machine, and run into error like below, this article is for you.
#######################
ERROR 1130 (HY000): Host ‘1.2.3.4’ is not allowed to connect to this MySQL server 
#######################
In certain instances, we need to allow the MySQL connectivity to remote hosts and you all may please follow the steps below to initialize the remote MySQL access for the database.
===============================================================================
>> Start with editing mysql config file
vim /etc/mysql/my.cnf>> Comment out following lines.
#bind-address = 127.0.0.1
#skip-networking>> If you do not find skip-networking line, add it and comment out it.
>> Restart mysql server.
service mysql restart>> Change GRANT privilege
>> You may be surprised to see even after above change you are not getting remote access or getting access but not able to all databases. By default, MySQL username and password you are using is allowed to access MySQL-Server locally. So we need to update privileges.
>> Run a command like below to access from all machines.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'%' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
>> Run a command like below to give access from specific IP.
mysql> GRANT ALL PRIVILEGES ON *.* TO 'USERNAME'@'1.2.3.4' IDENTIFIED BY 'PASSWORD' WITH GRANT OPTION;
>> You can replace 1.2.3.4 with your IP and then you can run above command many times to GRANT access from multiple IPs.
>> You can also specify a separate USERNAME & PASSWORD for remote access.
>> Finally, You can check outcome by:
SELECT * from information_schema.user_privileges where grantee like "'USERNAME'%";>> You may also need to run the last option to enable the privileges:
mysql> FLUSH PRIVILEGES;*****************************************************************************************
>> For testing the connection:
>> From the terminal/command-line:
mysql -h HOST -u USERNAME -pPASSWORD>> Once after you get a MySQL shell, don’t forget to run show databases; to check if you have right privileges from remote machines.
===============================================================================
That's it, you have perfectly enabled the remote MySQL access.
Thank you,
