Setting Up a MySQL Database with Remote User on Ubuntu/Linux Server

Setting up MySQL Database on your Linux server is bit easy, you just need to run few commands. In this article, I will discuss how you can setup MySQL DB on your Linux/Ubuntu server, enabling remote connection.

Step 1: Install MySQL Server

If MySQL is not installed on your server, you can install it using the following command:

sudo apt update
sudo apt install mysql-server

Once the installation is complete, start the MySQL service and enable it to run on boot:

sudo systemctl start mysql
sudo systemctl enable mysql

Step 2: Secure MySQL Installation

In newer MySQL versions (as of July 2022), running the mysql_secure_installation script without prior configuration, leads to error. This is because the root user by default does not authenticate using a password. To avoid this issue, follow these steps before running the script:

Open the MySQL Shell: First, log into the MySQL shell:

sudo mysql

Change the Root User’s Authentication Method: To allow the script to set a root password, change the authentication method to mysql_native_password:

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'your_password';

Replace your_password with a secure password of your choice.

Exit MySQL:

exit

Now, you can safely run the mysql_secure_installation script without encountering the recursive loop:

sudo mysql_secure_installation

Follow the prompts to complete the configuration and secure your installation.

(Optional) Revert to the Default Authentication Method: After completing the secure installation, if you prefer, you can revert the root authentication method to auth_socket to connect without a password using sudo:

mysql -u root -p
ALTER USER 'root'@'localhost' IDENTIFIED WITH auth_socket;

This will restore the default behavior where the root user can connect using the sudo mysql command without a password.

Step 3: Create a New MySQL User for Remote Access

Now, log in to the MySQL shell:

sudo mysql -u root -p

Create a new MySQL user that can access the database remotely. Replace username with your desired username, and password with a strong password:

CREATE USER 'username'@'%' IDENTIFIED BY 'password';

The % symbol allows the user to connect from any host. You can replace % with a specific IP address or domain to restrict access to a particular machine.

Step 4: Grant Privileges to the User

Now, grant the necessary privileges to the new user. Replace database_name with the name of the database the user will have access to:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'%';

To apply the changes, run the following command:

FLUSH PRIVILEGES;

Step 5: Configure MySQL for Accepting Remote Connections

MySQL, by default, is configured to accept connections only from the local machine. To allow remote connections, you need to edit the MySQL configuration file.

Open the MySQL configuration file (/etc/mysql/mysql.conf.d/mysqld.cnf) with a text editor:

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

* if the file is not found in the directory find it using this command sudo find / -name "mysqld.cnf"

Look for the line that contains bind-address (press F6 or CTRL+W button to find) and change it from 127.0.0.1 to 0.0.0.0 to accept connections from any IP address:

bind-address = 0.0.0.0

Save the file with CTRL+O and enter then exit the text editor.

Step 6: Restart MySQL Service

After making the configuration changes, to make the changes working restart the MySQL service:

sudo systemctl restart mysql

Step 7: Configure Firewall to Allow Remote Access

To allow remote access to MySQL, you need to open port 3306 (the default MySQL port) on your firewall. If you’re using UFW (Uncomplicated Firewall), you can allow traffic on port 3306 with the following command:

sudo ufw allow 3306

If you’re using another firewall or a cloud service provider’s then add the port 3306 into the security group, for remote connections.

Step 8: Test Remote Connection

From your local machine or another server, you can now try to connect to the remote MySQL server using the new user. Use the following command, replacing server_ip, username, and password with your server’s IP, MySQL username, and password: