Contact Latest Articles Home rafled dot net

How to make a Remote MySQL Database and User in MariaDB? (SSH)


December 22, 2020 | Tech


Step-1 Login

Login to MySQL using this command. * Note: Please use your default Root user username. *

mysql -u root -p

Step-2 Database Creation

Create Database. *Here, database name is "db101", you can name your database anything you like


MariaDB [(none)]> CREATE DATABASE db101;


Step-3 User Creation

Create database user and password. *Here, database user is "databaseuser", and the password here is "password". Also, please note that sometimes you may get an error because you may have done a mysql_secure_installation, In that case, you will need to generate password accordingly how you chose the strength of password during installation.*


MariaDB [(none)]> CREATE USER  'databaseuser'@'localhost' IDENTIFIED BY 'password';


Step-4 Allow remote System Connection

Now, this command will grant access to the user you created previously, (databaseuser) on database (db101). Then, you will need to input your remote server IP address. Here, as an example we have used 1.1.1.1. and finally, you will need to input the user password you created in Step-3. Here, we have use password "password" so, we will input the same here too.


MariaDB [(none)]> GRANT ALL ON db101.* to 'databaseuser'@'1.1.1.1' IDENTIFIED BY 'password' WITH GRANT OPTION;


Step-5 Flush Privileges and Exit

Finally, Flush Privileges and exit the shell


MariaDB [(none)]> FLUSH PRIVILEGES;


MariaDB [(none)]> EXIT;



Additional Information-

If you want allow remote access on all databases by "databaseuser", use this command:


MariaDB [(none)]> GRANT ALL ON *.* to 'databaseuser'@'1.1.1.1' IDENTIFIED BY 'password' WITH GRANT OPTION;


If you want allow any ip address to access database with "databaseuser", use this command:


MariaDB [(none)]> GRANT ALL ON db101.* to 'databaseuser'@'%' IDENTIFIED BY 'password' WITH GRANT OPTION;


If you want to allow a block of IP addresses on 1.1.1.x to access database as "databaseuser", use this command:


MariaDB [(none)]> GRANT ALL ON db101.* to 'databaseuser'@'1.1.1.%' IDENTIFIED BY 'password' WITH GRANT OPTION;


Similarly, you can use this command to allow any ip on 1.1.x.x block access database as "databaseuser" using this command:


MariaDB [(none)]> GRANT ALL ON db101.* to 'databaseuser'@'1.1.%.%' IDENTIFIED BY 'password' WITH GRANT OPTION;