How to enable remote Login to MySQL Database Server in Ubuntu / Debian

At first Install and Configure MySQL Database Server in Ubuntu. If you have not installed yet, read this story.

So, right now you are connected with MySQL Database server with Ubuntu/Debian machine. This story is going to assist you to know how to connect MySQL Database server from a different/remote machine. You can use remote root connection or if you want, you can use remote user connection too. Here, you will know how to connect remotely to MySQL not only by root login but also by user login.

Remote Root Login

You can check at the same time is it working or not by typing some commands in another terminal. So, first of all install mysql-client in your machine

sudo apt install mysql-client -y

Step 1 — Let’s connect to the server via SSH in your main machine.

ssh root@server_ip_address

Step 2 — You can get the ip address via bellow command too 😉

ip a

Step 3 — Now you need to go to the mysql configuration file & have to edit the configuration.

nano /etc/mysql/mysql.conf.d/mysqld.cnf

Step 4 — Now find bind address = 127.0.0.1 statement, by default it allows only the localhost to connect the database. Put “#” before the statement. Then it will be as #bind address = 127.0.0.1 So, it will allow to connect the database from anywhere 😀

If you don’t find the bind address line then you can add bind address = 0.0.0.0 or skip-networking and skip-bind-address in that configuration file. If you put any one of these three then do not put ‘#’ before it [Good to know]. Also check your port address. Here, your port address is 3306. So, it will allow also to connect the database from anywhere 😀

After editing your configuration file may like

[mysqld_safe]
socket          = /var/run/mysqld/mysqld.sock
nice            = 0

[mysqld]
#
# * 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
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            = 127.0.0.1
#skip-networking 
#skip-bind-address
#
# * Fine Tuning
#
key_buffer_size         = 16M
max_allowed_packet      = 16M
thread_stack            = 192K
thread_cache_size       = 8

query_cache_limit       = 1M
query_cache_size        = 16M

log_error = /var/log/mysql/error.log

expire_logs_days        = 10
max_binlog_size   = 100M

NB — [ Good to know ] You can allow MySQL database access from a specific IP_ADDRESS

sudo ufw allow from your_remote_ip_address to any port 3306

Or, [ Good to know ] you can allow connections to MySQL database from any remote IP_ADDRESS too

sudo ufw allow 3306

Step 5 — Now restart your mysql server

sudo systemctl restart mysql

Step 6— Now you have connect the Database server by root user.

sudo mysql -u root -p

Step 7— Check you databases

show databases;

Step 8— Now lock mysql as it holds the database configuration

use mysql;

Step 9— Now run below query & check the output

SELECT host FROM user WHERE user = 'root';

Here, we’ve only loalhost as host which means the user root is only allowed to login by localhost [not by different machine].

Step 10— Now we are going to add root in host to allow remote login from different machine too. Now, run below query

GRANT ALL ON *.* to root@'%' IDENTIFIED BY 'put_your_root_password';

Here *.* means all the databases with tables can be accessible by root identified by your root password. Now put the below query again & check the host

SELECT host FROM user WHERE user = 'root';

Here, % is added, means you can connect to database by root remotely. Now write exit query to log out from mysql.

exit;

Step 11 — So, your remote root connection configuration is done. Your can check the connection via another terminal. Open another terminal. Do not forget to install mysql-client [ sudo apt install mysql-client -y ]. Now write below command to check the remote connection.

mysql -h ip_address -u root -p

Checking via another terminal window

Or you can test the remote connection using MySQL Workbench too. Too easy, just download it if you don’t have it.

So, this is all about remote root login from different machine. Now comes remote user login process.

Remote User Login

It is just like root login process. Follow step 1 to step 6.

After connecting to mysql server via sudo mysql -u root -p command, write below query to create user.

create user "iamuser"@"%" identified by "put_your_user_password";

Now run below query

GRANT ALL ON dbname.* TO iamuser@'%' IDENTIFIED BY 'put_your_user_password';

Here dbname is a specific Database, instead of ‘%’ you can use ‘IP_ADDRESS’. Now do exit; query.

So, your remote user login configuration is done. You can check the connection via another terminal. Open another terminal. Do not forget to install mysql-client [ sudo apt install mysql-client -y ]. Now write below command to check the remote connection.

mysql -u user -h database_server_ip -p

Or you can use MySQL Workbench too.

While configuring you may get some errors too. Let’s talk about some errors.

" ERROR 2003 (HY000): Can't connect to MySQL server on 'database_server_ip' "

Above error says, either database port 3306 is not open or MySQL server is not listening on the connecting IP_ADDRESS. Solution is check & follow step 4 correctly.

Let’s see another error

"ERROR 1130 (HY000): Host 'database_server_ip' is not allowed to connect to this MySQL server "

Above error says, user doesn’t have permission to access the remote MySQL server. Solution is check & follow step 6 to last correctly.

Finally, now you can connect to MySQL Database Server from any machine.

Have fun 🔥 💪

If you have any query, please put in below. Find me on Github.