With the default installation on Kubuntu, MySQL server is configured to be accessed ONLY via localhost, due to the security issues. Nevertheless, it is necessary to open the remote access should you wish to connect MySQL database externally either via SSH or to be used with scripts reside on external Web servers. Here, I provide some easy steps to configure your MySQL database server so that it can be accessed remotely.
First of all, log in to the machine, where the MySQL database is installed, via terminal (or SSH if you’re doing it remotely) and following the steps below:
Step 1: Find “my.cnf” File
Look for MySQL configuration file (my.cnf), for K(U)buntu systems, this is generally located at ‘/etc/mysql/my.cnf’. Use a text editor (e.g. Pico or vi) to edit the content.
$ sudo nano /etc/my.cnf
Step 2: Disable the “Networking Locking”
Within my.cnf, search for the line that starts with the following:
In the block of [mysqld], comment out the following line:
and make sure to change the IP address assigned to the “bind-address” is the same as IP where MySQL server is installed. So the block of “[mysqld]” should looks like similar as follows:
# * Basic Settings
user = mysql
pid-file = /var/run/mysqld/mysqld.pid
socket = /var/run/mysqld/mysqld.sock
port = 3306
basedir = /usr
datadir = /var/lib/mysql
tmpdir = /tmp
language = /usr/share/mysql/english
# For compatibility to other Debian packages that still use
# libmysqlclient10 and libmysqlclient12.
old_password = 1
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address = 127.0.0.1
bind-address = "whatever your MySQL server IP is"
* bind-address: IP address to bind to.
* skip-external-locking: DonÃ¢â‚¬â„¢t listen for TCP/IP connections at all. All interaction with mysqld must be made via Unix sockets. This option is highly recommended for systems where only local requests are allowed.
Step 3: Restart MySQL Service
Save and close my.cnf file, and restart the MySQL server for the changes to take effect:
$ sudo /etc/init.d/mysql restart
Step 4: Grant Access to Remote IP Addresses
Enter the following command:
$ sudo mysql -u root -p mysql
For granting the access to new databases:
If you want to add new database called remoteDB for user remoteUSER and remote IP 126.96.36.1990 then issue the following commands:
mysql> CREATE DATABASE remoteDB;
mysql> GRANT ALL ON remoteDB.* TO 'remoteUSER'@'188.8.131.520' IDENTIFIED BY 'PASSWORD';
For granting access to existing databases:
Assuming the connection will be made from a remote IP address at 184.108.40.2060, to database called remoteDB via user account remoteUSER. Enter the following commands:
mysql> UPDATE db set Host='220.127.116.110' WHERE Db='remoteDB';
mysql> UPDATE user set Host='18.104.22.1680' WHERE User='remoteUSER';
Host IP can be used to mark a wide range of addresses, for instance, the following command:
mysql> GRANT ALL ON remoteDB.* TO 'remoteUSER'@'123.45.67.%' IDENTIFIED BY 'PASSWORD';
covers all class C subnet IPs.
Step 5: Testing MySQL Database
Issue the following command from remote system:
$ sudo mysql -u remoteUSER Ã¢â‚¬â€œh 22.214.171.1240 Ã¢â‚¬â€œp
* -u remoteUSER: remoteUSER is one of the user account for MySQL.
* -h IP or hostname: 126.96.36.1990 is MySQL server IP address or hostname (FQDN).
* -p : Prompt for password.