Instructions to activate Remote SQL Server Linux

I. Introduction

In today’s article, I will guide you to enable remote sql feature on Linux system for any user or all databases under user root.
In some controls, this remote function is available as cPanel and you only need to configure it to be able to remote. But with manual servers or some scripts, you have to configure permissions for these remote users. And to do this, please check out this tutorial.
See also Remote SQL on controls:Enable remote database CentOS Web Panel (CWP)

II. Steps to take

To do this, you need to identify the user that needs to enable remote and you must be able to access the database as root via the command mysql -u root -pand log in with the mysql root password. This information is set when you install MySQL/MariaDB.

enable Remote SQL Server Linux

1. Remotely enable any Database

To enable Remote SQL for a user you use the following command to execute.

Step 1: Access the database

First you access the database with root. To enable remote sql for specified user.

mysql -u root -p

As the first picture I have many databases and I will enable remote for the database named database1as follows.

Step 2: Remote SQL

To enable for a certain database you use the following command

mysql -u root -p 
GRANT ALL PRIVILEGES ON *.* TO 'database1'@'%' IDENTIFIED BY 'Mat_Khau_User_Database' WITH GRANT OPTION; 
FLUSH PRIVILEGES;

In it you need to change the following

  • database1: Replace with your database name
  • Mat_Khau_User_Database: Enter the password of Database user
  • %: If you allow all remote IPs you leave the %. If you only allow 1 IP, please replace it with your own

Once done, exit and restart the mysql/mariadb service. Here I use MariaDB, so I will restart it with the commandsystemctl restart mariadb
Actual photo taken

enable Remote SQL Server Linux

Check the SQL connection.
Here I use Navicat softwareto connect, also you can use other software like workbench or php function.
Below is the actual photo of the connection and the information included

  • Connection Name: Give it any name
  • Host: Enter the server IP
  • Port: 3306 (Default)
  • User Name: Enter user Database
  • Edit Password: Enter User Database Password
enable Remote SQL Server Linux

After the import is complete, click Test Connection to check. If it shows a message Connection Successfullike the picture, it was successful

enable Remote SQL Server Linux

And here is a picture of successful connection when activating remote sql.

enable Remote SQL Server Linux

2. Enable for the entire Database

If your server has multiple database accounts and you want to enable all of them, just use the user rootto remote and can execute all the databases you do as follows.

Step 1: Access the database

You use the command mysql -u root -pthen enter the mysql root password to enter.
mysql -u root -p

Step 2: Enable remote sql

You use the following command to enable the root user.

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'mat_khau_root_mysql' WITH GRANT OPTION; 
FLUSH PRIVILEGES;

That drum:

  • root: is the root user used to remote
  • %: If you allow all remote IPs you leave the %. If you only allow 1 IP, please replace it with your own
  • mat_khau_root_mysql: You enter the mysql root password

Restart the MySQL/MariaDB service
Once done, exit and restart the mysql/mariadb service. Here I use MariaDB, so I will restart it with the commandsystemctl restart mariadb

systemctl restart mariadb

Check the SQL connection.
Here I use Navicat softwareto connect, also you can use other software like workbench or php function.

Below is the actual photo of the connection and the information included

  • Connection Name: Give it any name
  • Host: Enter the server IP
  • Port: 3306 (Default)
  • User Name: Enter root
  • Edit Password: Enter Password rootmysql

GIF image for demo


Support 24/24h with coupon code: 1DOLLAROFF 10% discount. Click now! 👆
On this page