How To Connect MySQL/MariaDB Server From Command Line On Linux and Windows?

MySQL or MariaDB database servers can be managed in different ways. Generally, GUI tools are provided because of their easiness. But in some cases, a command-line connection may be required to connect and manage MySQL and MariaDB database server.

Install MySQL/MariaDB Client For Ubuntu, Debian, Kali, Mint

We will start by installing MySQL/MariaDB command-line client in deb based distributions like Ubuntu, Debian, Kali, Mint, etc. The package is named as mysql-client which we will install with the apt command like below.

$ sudo apt install mysql-client

Install MySQL/MariaDB Client For Fedora, CentOS, RedHat

In the rpm-based distributions like Fedora, CentOS, RedHat can be installed with the yum command like below. Also alternatively dnf can be used like below.

$ sudo dnf install community-mysql

OR

$ sudo yum install community-mysql

Connect MySQL/MariaDB Database From Command Line

If MySQL/MariaDB database is installed on the local system we can use mysql tool to connect. We can use MySQL command with the sudo command for Linux systems like Ubuntu, Debian, Fedora, Mint, CentOS, etc.

$ sudo mysql

OR for Windows

> mysql

We can see that when we open the MySQL interactive shell some information about the connection and MySQL database server is provided. Every connection to the MySQL server has an ID called MySQL Connection ID. Server Version specifies the MySQL server version. Some copyright information is also provided. \h or help can be used to get help about the MySQL interactive shell or command line. \c will clear the current interactive shell page or content and provide a clean command-line.

Connect Specifying Username MySQL/MariaDB Database From Command Line

The default behavior of the MySQL command is using the current user name. We can provide the user name explicitly with the -u option and the user name. In this example, we will connect with the user name root

$ sudo mysql -u root

Connect Specifying Username and Password MySQL/MariaDB Database From Command Line

Up to now, we have connected to the local MySQL/MariaDB database server without explicitly specifying the password. If we want to connect to a remote database server we have to specify the password for the given user. We can also specify the remote server hostname or IP address with the -h option like below.

$ mysql -u root -h 192.168.142.144

Run SQL Without Connecting Remote MySQL/MariaDB Database

mysql command provides different features like running SQL statements remotely without using the mysql shell. We can run the SQL clause with the -e option like below.

$ sudo mysql -u root -e "SELECT VERSION();"

Show Databases From Command Line

If we want to list databases stored on the MySQL/MariaDB database without connecting them explicitly and using MySQL shell we can use show databases; command like below.

$ sudo mysql -u root -e "show databases"

Specify The Default Database From Command Line

As we use the command-line tool MySQL to connect and manage MySQL/MariaDB databases we need to specify the default database name in order to run SQL queries. We will use use statement with the database name in order to specify the default database the SQL queries run.

$ sudo mysql -u root -e "use sys;show tables;"

Run SQL Script or SQL File From Command Line On Remote MySQL/MariaDB Database Server

SQL scripts and statements can be stored in the SQL script file. We can run this script files without copy and paste just providing the file and redirecting the content of the file. We have the following SQL file content which is sqlscript.sql .

select version();
show databases;
use sys;
select * from host_summary;

We have to also specify the database name as the last parameter to the mysql command which is sys in this case.

$ sudo mysql -u root sys < sqlscript.sql