Categories

Allow remote connections in MySQL or MariaDB server

Posted by By Adam 2 years Ago
Allow remote connections MySQL or MariaDB server

If you want to make a connection between two MySQL or MariaDB servers then you have to remote connections MySQL or MariaDB

We have two ways to allow remote access to the destination MySQL or MariaDB server from the Root User of MySQL and New User.

Allow MySQL or MariaDB port

To establish a connection, you have to allow the port of MySQL or MariaDB from both the source server and destination server. Otherwise, the server will not able to make a connection.

For Ubuntu or Debian

sudo ufw allow 3306/tcp

For CentOS or Red hat or Fedora

iptables -I INPUT -i eth0 -p tcp --destination-port 3306 -j ACCEPT

To Allow Remote Access to Root User

In this, we will grant the remote access privileges to the Root user of the destination server. So, any MySQL or MariaDB server can establish the connection.

  • Login into the MySQL.
mysql -u root -p

It will ask for a password, Enter the Root password of the MySQL server.

  • We can’t directly make changes in MySQL Root User, so we have to create a new Root User for Remote Access.
CREATE USER 'root'@'%' IDENTIFIED BY 'root_password';
  • Allow remote access privileges.
GRANT ALL ON *.* TO root@'%';
  • Save the changes in MySQL or MariaDB server.
flush privileges;

To Allow Remote Access to New User

We have to grant the privileges to the new user of the destination MySQL or MariaDB server.

  • Create a new user in MySQL or MariaDB server for the remote access.
CREATE USER 'remote_user'@'%' IDENTIFIED BY 'strong_password';

Replace the remote_userand strong_password with your desired username and password.

  • Allow the new user to access all the databases in the MySQL or MariaDB server.
GRANT ALL PRIVILEGES ON *.* TO 'remote_user'@'%';
  • Save the change made on the MySQL or MariaDB server.
flush privileges;

Revoke Remote Access for Root User

To revoke the remote access privileges assign to the root user. We need to follow these steps:

  • From this command you will revoke the privileges of Newly created Root User from accessing the databases.
REVOKE ALL PRIVILEGES ON *.* FROM 'remote_user'@'%';
  • Or If the Remote access is not longer needed then you can just delete the new Root User which was created for Remote access.
drop user 'remote_user'@'%';
  • Save the changes done in the MySQL server.
flush privileges;

Revoke Remote Access for New User

To revoke the remote access privileges assign to the root user. We need to follow these steps:

  • Through the below command, we will revoke the privileges to access the databases.
REVOKE ALL PRIVILEGES ON *.* FROM 'root'@'%';
  • Or you can directly remove the user.
drop user 'root'@'%';
  • Save the changes done in the MySQL server.
flush privileges;

Read Also: How to Start, Stop, or Restart Apache

Conclusions

In this tutorial, we have learned how we can allow remote connections to MySQL or MariaDB servers. With the help of remote access, we can transfer dump files from one MySQL server to another MySQL server. We can easily access the database and can modify them with the help of remote access.