We started using Qlik Views for our customers and we were in need of connecting to remote MySQL server for Data analytics. By default MySQL server let you not connect remotely unless we enable it.
Lets see how we can access remote MySQL server by creating and assigning privileges to users.
1. Change my.cnf
Edit my.cnf
[source]
nano /etc/MySQL/my.cnf
[/source]
comment the lines bind-address, skip-networking and save
[source]
#skip-networking
#bind-address = 127.0.0.1
[/source]
2. Create a MySQL user for remote access
To create MySQL user, login to MySQL server. Here I am logging in as root user
[source]
$ MySQL -u root -p
[/source]
The above command will prompt for root user password, upon entering valid password it will take it to MySQL console. We need to execute following command in MySQL console.
[source]
CREATE USER ‘remote_user’@’localhost’ IDENTIFIED BY ‘mypass’;
[/source]
The above command will create user called ‘remote_user’ which is accessible from localhost. Then create user to all connection from all hosts.
[source]
CREATE USER ‘remote_user’@’%’ IDENTIFIED BY ‘mypass’;
[/source]
3. Grant Access
To grant access for ‘remote_user’ run the following SQL.
[source]
GRANT ALL ON *.* TO ‘remote_user’@’localhost’;
GRANT ALL ON *.* TO ‘remote_user’@’%’;
[/source]
The above command will grant access for remote_user on all database. If you want to grant access to specific database then run following SQL in MySQL console.
[source]
GRANT ALL ON MyDB.* TO ‘remote_user’@’localhost’;
GRANT ALL ON MyDb.* TO ‘remote_user’@’%’;
[/source]
The above command will grant access for ‘remote_user’ only to specific database. Replace MyDb with your database name.
4. Test MySQL connection
To test the connection, you need to know the IP address of your server. IP address can be find using ifconfig command in Linux
[source]
$ ifconfig
[/source]
MySQL will run on port 3306 by default. Check the MySQL connection using telnet.
[source]
$ telnet SERVER-IP-ADDRESS 3306
[/source]
If above telnet connection is success then it is a good sign.
5. Trouble shoot
Sometime remove server connection may fail due to firewall. Ubuntu comes up with default firewall tool called ufw (Ubuntu Fire Wall). It will throw error like this
Can’t connect to MySQL server error 111
We need to enable port 3306 to outside world.
[source]
$ sudo ufw allow 3306
[/source]
Check the status of ufw by following command
[source]
sudo ufw status
[/source]
Again test the connection using telnet, it should work. If you still face the problem then re-enable and reboot the server.
[source]
$ sudo ufw enale
$ sudo reboot
[/source]
Check the connection again
[source]
$ telnet SERVER-IP-ADDRESS 3306
[/source]
Hope this helps.
