Menu

How to connect To Remote MySQL/MariaDB Database Server Via Command Line in Linux

 @ November 15, 2018 LINUX, MySQL

This post will guide you how to connect to the remote MySQL/MariaDB database server from command Line Interface under your Linux operating system. How do I access my remote MySQL database from command line on Linux.

Connect Remote MySQL Server Via Linux Terminal

If you are using a CentOS/Ubuntu Linux, and you want to connect to a remote MySQL/MariaDB database server, you need to install mysql client package firstly on your Linux system. Just issue the following command to install it:

$ sudo yum install mysql-client

or

$ sudo apt-get install mysql-client

Outputs:

devops@devops-osetc:~$ sudo apt-get install mysql-client
sudo: unable to resolve host devops-osetc
Reading package lists... Done
Building dependency tree
Reading state information... Done
The following additional packages will be installed:
libaio1 mysql-client-5.7 mysql-client-core-5.7 mysql-common
The following NEW packages will be installed:
libaio1 mysql-client mysql-client-5.7 mysql-client-core-5.7 mysql-common
0 upgraded, 5 newly installed, 0 to remove and 598 not upgraded.
Need to get 7,782 kB of archives.
After this operation, 65.7 MB of additional disk space will be used.
Do you want to continue? [Y/n] y
Get:1 http://us.archive.ubuntu.com/ubuntu xenial/main amd64 libaio1 amd64 0.3.110-2 [6,356 B]
Get:2 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 mysql-client-core-5.7 amd64 5.7.24-0ubuntu0.16.04.1 [6,030 kB]
Get:3 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 mysql-common all 5.7.24-0ubuntu0.16.04.1 [15.3 kB]
Get:4 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 mysql-client-5.7 amd64 5.7.24-0ubuntu0.16.04.1 [1,720 kB]
Get:5 http://us.archive.ubuntu.com/ubuntu xenial-updates/main amd64 mysql-client all 5.7.24-0ubuntu0.16.04.1 [10.1 kB]
Fetched 7,782 kB in 13s (592 kB/s)
Selecting previously unselected package libaio1:amd64.
(Reading database ... 178950 files and directories currently installed.)
Preparing to unpack .../libaio1_0.3.110-2_amd64.deb ...
Unpacking libaio1:amd64 (0.3.110-2) ...
Selecting previously unselected package mysql-client-core-5.7.
Preparing to unpack .../mysql-client-core-5.7_5.7.24-0ubuntu0.16.04.1_amd64.deb ...
Unpacking mysql-client-core-5.7 (5.7.24-0ubuntu0.16.04.1) ...
Selecting previously unselected package mysql-common.
Preparing to unpack .../mysql-common_5.7.24-0ubuntu0.16.04.1_all.deb ...
Unpacking mysql-common (5.7.24-0ubuntu0.16.04.1) ...
Selecting previously unselected package mysql-client-5.7.
Preparing to unpack .../mysql-client-5.7_5.7.24-0ubuntu0.16.04.1_amd64.deb ...
Unpacking mysql-client-5.7 (5.7.24-0ubuntu0.16.04.1) ...
Selecting previously unselected package mysql-client.
Preparing to unpack .../mysql-client_5.7.24-0ubuntu0.16.04.1_all.deb ...
Unpacking mysql-client (5.7.24-0ubuntu0.16.04.1) ...
Processing triggers for libc-bin (2.23-0ubuntu5) ...
Processing triggers for man-db (2.7.5-1) ...
Setting up libaio1:amd64 (0.3.110-2) ...
Setting up mysql-client-core-5.7 (5.7.24-0ubuntu0.16.04.1) ...
Setting up mysql-common (5.7.24-0ubuntu0.16.04.1) ...
update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Setting up mysql-client-5.7 (5.7.24-0ubuntu0.16.04.1) ...
Setting up mysql-client (5.7.24-0ubuntu0.16.04.1) ...
Processing triggers for libc-bin (2.23-0ubuntu5) ...

After installed mysql client package, you can use mysql command to connect to your MySQL Database server, issue the following command:

$ mysql -u dbuser_name -h database_servername -p

then you need to type the password for dbuser.

Let’s see the below example to connect my database server:
Type:

$ mysql -u root -h localhost -p

Outputs:

devops@devops-osetc:~$ mysql -u root -h localhost -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.24-0ubuntu0.16.04.1 (Ubuntu)

Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

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

mysql>

Now you have connected your MySQL Database server and you can do some mysql operation, such as: query, update, and so on.

List All Databases in Your MySQL Server

If you want to list all databases in your MySQL/Mariadb Server, you can type the following mysql command:

Note: you need to connect to your MySQL Database server firstly.

> show databases;

Outputs:

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.02 sec)

mysql>

Selecting a Database

If you want to query a table or data from a table in a specific database, you need to select that database firstly, just executing the following mysql command:

> use mysql;

Outputs:

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql>

So the current database has been changed to mysql database.

Create a New Database

If you want to create a new database in your connected MySQL database server, just use the create command to achieve it. type:

> create database myosetc;

Outputs:

mysql> create database myosetc;
Query OK, 1 row affected (0.00 sec)

mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| myosetc |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)

mysql>

Form the above outputs, we can see that the myosetc database has been created in MySQL server.

List All Tables in a Database

If you want to list all tables in a specific database or your current database, you can use the following command:

>show tables;

Outputs:

mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
| db |
| engine_cost |
| event |
| func |
| general_log |
| gtid_executed |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| innodb_index_stats |
| innodb_table_stats |
| ndb_binlog_index |
| plugin |
| proc |
| procs_priv |
| proxies_priv |
| server_cost |
| servers |
| slave_master_info |
| slave_relay_log_info |
| slave_worker_info |
| slow_log |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+---------------------------+
31 rows in set (0.01 sec)

mysql>

Obtain Information About Table Structure

If you want to check the structure of one table in your database, and you can use the desc command to get it, type:

> desc user;

Outputs:

mysql> desc time_zone;
+------------------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+------------------+------------------+------+-----+---------+----------------+
| Time_zone_id | int(10) unsigned | NO | PRI | NULL | auto_increment |
| Use_leap_seconds | enum('Y','N') | NO | | N | |
+------------------+------------------+------+-----+---------+----------------+
2 rows in set (0.00 sec)

List all Data in A Table

If you want to list all data in a table, such as, server_cost table, just issue the following command:

>select * from server_cost;

Outputs:

mysql> select * from server_cost;
+------------------------------+------------+---------------------+---------+
| cost_name | cost_value | last_update | comment |
+------------------------------+------------+---------------------+---------+
| disk_temptable_create_cost | NULL | 2017-08-01 13:39:40 | NULL |
| disk_temptable_row_cost | NULL | 2017-08-01 13:39:40 | NULL |
| key_compare_cost | NULL | 2017-08-01 13:39:40 | NULL |
| memory_temptable_create_cost | NULL | 2017-08-01 13:39:40 | NULL |
| memory_temptable_row_cost | NULL | 2017-08-01 13:39:40 | NULL |
| row_evaluate_cost | NULL | 2017-08-01 13:39:40 | NULL |
+------------------------------+------------+---------------------+---------+
6 rows in set (0.00 sec)

mysql>

Quit MySQL Connection

If you want to quit the connection of your MySQL Database, just issue the following mysql command:

>quit

Outputs:

mysql> quit
Bye
devops@devops-osetc:~$

Video:Connect Remote MySQL Server Remotly

You might also like:

Sidebar
back to top