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
- List All Databases in Your MySQL Server
- Selecting a Database
- Create a Database
- List All Tables in a Database
- Obtain Information About Table Structure
- List all Data in A Table
- Quit MySQL Connection
- Video:Connect Remote MySQL Server Remotely
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:~$