How To Connect Remote MySQL/MariaDB Database From Linux Terminal?

There are many ways that we can connect the remote MySQL/MariaDB database.

You can use the GUI client called MySQL Workbench.

Alternatively, we can access it right from Linux terminal.

It’s very simple but you may face challenges while accessing the remote database due to limitation or security reason.

If so, how to check and what needs to be done to fix the issue?

• Navigate to this link to view other MySQL related articles.

1) Common Syntax To Access MySQL/MariaDB Database From Linux Terminal?

You can use the below syntax to access remote MySQL/MariaDB database right from your Linux terminal.

# mysql -u [DB-UserName] -h [DB-HostName or IP] -p [Don’t enter the password here]

DB-UserName: It's Your Database UserName
DB-HostName or IP: Database hosted server IP or hostname
Password: Password of your database

2) How To Access MySQL/MariaDB Database From Linux Terminal?

We are going to access the MySQL/MariaDB database from Linux terminal. Will see what will happens.

# mysql -h centos7.2daygeek.com -u daygeek_2daygeek -p
Enter password: 
ERROR 1045 (28000): Access denied for user 'daygeek_2daygeek'@'centos7.2daygeek.com' (using password: YES)

ohh, i got an error message but i don’t know what was the issue.

I dam sure that i gave the correct password but it’s throwing an error. I’m guessing, it could be privilege issue. Let me check this on server.

3) How To Check User Privileges On MySQL/MariaDB Database?

Check the user privileges on the server where MySQL/MariaDB database is hosted.

First login to the server.

root@remote-server# mysql -u root -p
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.14-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> 

Use the below command to check the corresponding DB user privileges, use your DB username instead of ours.

MariaDB [(none)]> select user,host from mysql.user where user ='daygeek_2daygeek';
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| daygeek_2daygeek | localhost |
+------------------+-----------+
1 row in set (0.00 sec)

The above output clearly shows that you can able to access the DB only from this server because the DB user has localhost privilege.

4) How To Grant Privilege To User To Access MySQL/MariaDB Database From Remote System?

Don’t give wildcard privilege to DB user because its HARM your system badly (It’s not best practices).

Instead you can grant access to particular IP or host (If you want to grant access to particular IP, make sure it should be a static IP).

MariaDB [(none)]> GRANT ALL PRIVILEGES ON daygeek_daygeek.* TO 'daygeek_2daygeek'@'ubuntu19.2daygeek.com';
Query OK, 0 rows affected (0.00 sec)

Flush the privileges, which makes the new changes to take effect.

MariaDB [mysql]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

We can able to see the newly granted privileges by running the following command.

select user,host from mysql.user where user ='daygeek_2daygeek';
+------------------+-----------------------+
| user             | host                  |
+------------------+-----------------------+
| daygeek_2daygeek | ubuntu19.2daygeek.com |
| daygeek_2daygeek | localhost             |
+------------------+-----------------------+
2 rows in set (0.00 sec)

5) How To Access MySQL/MariaDB Database From Linux Terminal?

As we had already fixed all the outstanding issue and we can able to access the remote database right from terminal without any issue. Let’s test it out and see the results.

# mysql -h centos7.2daygeek.com -u daygeek_2daygeek -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 9
Server version: 10.3.14-MariaDB Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]>

Yup, i can able to access the remote MySQL/MariaDB database from my local Linux system without any issue. Let’s see if i can able to perform any action on it.

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| daygeek_daygeek    |
+--------------------+
2 rows in set (0.13 sec)

Yes, it’s working fine as expected.

Finally, exit from the database.

MariaDB [(none)]> exit
Bye