Introduction
Normally the default setting of MySQL instance is only configured to listen for local connections and these instances are not accessible remotely. To make MySQL to listen to external IP
$ sudo nano /etc/mysql/mysql.conf.d/mysqld.cnf
Go to the line that contains bind-address.
You must change the ip address here in the file. if the bind-address.
is not there then manually type the last line.
/etc/mysql/mysql.conf.d/mysqld.cnf
. . . lc-messages-dir = /usr/share/mysql skip-external-locking # # Instead of skip-networking the default is now to listen only on # localhost which is more compatible and is not less secure. bind-address = 0.0.0.0 . . .
Restart
$ sudo systemctl restart mysql
Login to MySQL
For an existing MySQL user account you can simply connect to the database from your remote host, you’ll need to reconfigure that account to connect from the remote server instead of localhost..
or, If you’ve enabled password authentication for root use the seconf command
or, To change a user’s host, you can use MySQL’s RENAME USER
command.
$ sudo mysql or $ mysql -u root -p or $ RENAME USER 'sharique'@'localhost' TO 'sharique'@'remote_server_ip';
Also we can create new user account that will only connect from the remote host .
CREATE USER 'sharique'@'remote_server_ip' IDENTIFIED BY 'password';
To use this with PHP Application phpMyAdmin for example.
CREATE USER 'sharique'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';
you can always create a user that authenticates with caching_sha2_plugin
and then also ALTER
it later .
ALTER USER 'sharique'@'remote_server_ip' IDENTIFIED WITH mysql_native_password BY 'password';
Grant appropriate privileges
mysql> GRANT CREATE, ALTER, DROP, INSERT, UPDATE, DELETE, SELECT, REFERENCES, RELOAD on *.* TO 'sharique'@'remote_server_ip' WITH GRANT OPTION; mysql> FLUSH PRIVILEGES; mysql> exit
Allow Firewall if any , remember port 3306
— MySQL’s default port — to allow traffic to MySQL
$ sudo ufw allow from remote_IP_address to any port 3306 $ sudo ufw allow 3306
Remote Access to MySQL
Now finally try to remote access MySQL from another machine. If you’re able to access your database, it confirms that the bind-address
is correct and working now. Also note the ip address used for setting bind-address
to 0.0.0.0
is insecure as it allows connections to your server from any IP address. It is advisable to use your network subnet IP Address to connect to MySQL.