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
$ 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
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