How To Connect MySQL via Command Line?

MySQL database server provides different methods to connect, manage and use itself. The command-line interface is a way to access, log in and manage the MySQL database server, databases, tables, users, etc. In order to access the mysql client should be used via the command-line interface.

mysql Command Syntax

The mysql command is a client tool used to connect MySQL databases via command line interface. The mysql command has the following syntax.

mysql -u USER -p HOST

USER is the user name we want to authenticate or connect for. Generally the root is used which is the default MySQL user.
HOST is the hostname or IP address of the MySQL database server. The IP address or hostname can be used but the hostname should be resolved properly. If the MySQL database is located in the local host the localhost or 127.0.0.1 can be used.

Connect Local MySQL Database with mysql Client

The mysql command can be used to connect local database using the following command. If you are using Linux, Ubuntu etc. the sudo command is also used too.

sudo mysql -u root -p

[sudo] password for ismail: 
Enter password: 
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 49
Server version: 10.3.29-MariaDB-0ubuntu0.20.10.1 Ubuntu 20.10
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)]>

Connect Local MySQL Database with mysql Client

First the sudo command password for the current user ismail is entered. Then the root user password for the MySQL database is provided.

If you are using Windows operating system the sudo command can be omitted.

mysql -u root -p

As we can see that if the HOST is not specified the localhost is assumed implicitly. Alternatively we can specify the local host like below.

mysql -u root -p localhost

Alternatively the localhost can be expressed as IP address 127.0.0.1 .

mysql -u root -p 127.0.0.1

Connect Remote MySQL Database with mysql Client

The mysql command can be used to connecte remote hosts MySQL databases. Just the remote host hostname or IP address should be provided properly. In the following exmaple we connect the MySQL database located in the IP address 192.168.1.10 .

mysql -u root -p 192.168.1.10

Alternatively the remote host hostname can be used to connect MySQL database server. But the hostname shouldbe resolved into IP address properly. In the following example we connect the MySQL database server db.wisetut.com .

mysql -u root -p db.wisetut.com

Provide Password To mysql Command

The -p option is used for password and the password can be provided to the mysql command before interactive login. But providing the password to mysql command as parameter is not secure.

mysql -u root -p1q2w3e...

List/Show Databases

After connecting to the MySQL we can list existing databases with the show databases command like below.

show databases;

Select Database

In order to work with a specific database in a practical way we should select this database. After selecting database every SQL query we execute is related with this database. A database can be selected with the select DATABASE command where DATABASE is the database name we want to select.

select mysql;