Use MySQL Client and Related Utilities in Linux

Introduction

MySQL is an open source relational database management system (RDBMS) that has been popular for over twenty years. It is widely used on Linux systems for both web server solutions and standalone applications. This article will provide an overview of the most commonly used MySQL utilities, including mysql and mysqladmin, along with some examples on how to use them. It's worth noting that the explanations in this article also apply to MariaDB, a popular fork of MySQL created by the original developers, due to concerns that MySQL may not remain open source and intend to maintain high compatibility with MySQL.

Install and use MySQL

When installing MySQL, two packages are provided: mysql-server, which contains the server and all the utilities to connect to the server, and mysql-client, which contains only the utilities to connect to a server located elsewhere. Regardless of which package you choose, there will be several commands that start with "mysql".

Connecting to a MySQL Server

The mysql command is the command line client and main binary for connecting to a MySQL server. It provides a shell where we can interact with MySQL or MariaDB server. Most Linux distributions require you to run these utilities as root.

$ sudo mysql -u root
Welcome to the MySQL monitor.  Commands end with ; or \g.
...
mysql>

With this command we access the shell in interactive mode. We can enter SQL statements in succession to interact with our databases, using the same connection.

Create and Manage Databases and Tables

For example, we can query the system database to get all the users in the system ?

mysql> select host, user from mysql.user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | root             |
...
+-----------+------------------+
6 rows in set (0.00 sec)

We can also use MySQL-specific statements like USE and SHOW TABLES ?

mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql           |
+---------------------------+
| columns_priv              |
...
+---------------------------+
33 rows in set (0.01 sec)

Administrative Tasks

mysqladmin is a tool to perform administrative tasks on the server. It's a non-interactive client that prompts for the command with the action we want to perform. For example, we can use mysqladmin to create and drop a database ?

$ sudo mysqladmin create the_database
$
$ sudo mysqladmin drop the_database
...
Do you really want to drop the 'the_database' database [y/N] y
Database "the_database" dropped
$

Additional Utilities

In this section, we will explore some additional utilities that can be used to manage and maintain MySQL databases. These utilities include "mysqldump", "mysqlrepair" and "mysqlimport". mysqldump is a powerful database backup tool and can be used to dump a single database or a collection of databases for backup or transfer to another SQL server. "mysqlrepair" is a utility for repairing damaged tables in a MySQL database and can be used to repair MyISAM and InnoDB tables. Finally, "mysqlimport" is a tool for importing data from text files into a MySQL table, where each row represents a new record and each field is separated by a tab.

mysqldump

mysqldump is a utility for creating database backups. It can be used to download a database or a collection of databases for backup or transfer to another SQL server.

$ mysqldump -u root -p db_name > db_name.sql

mysqlrepair

mysqlrepair is used to repair damaged tables in a MySQL database. It can be used to repair MyISAM and InnoDB tables.

$ mysqlrepair -u root -p db_name table_name

mysqlimport

mysqlimport is used to import data from text files into a MySQL table. The text file must have a specific format, with each line representing a new record and each field separated by a tab.

$ mysqlimport -u root -p db_name table_name.txt

Conclusion

In conclusion, MySQL is a powerful and widely used relational database management system that is perfect for small and large applications. The MySQL client and related utilities on Linux allow you to connect to a MySQL server, create and manage databases and tables, and manipulate data. This article has provided an overview of how to use the MySQL client and related utilities on Linux, including example commands for connecting to a MySQL server, creating and managing databases and tables, and manipulating data, as well as additional utilities for security backup and repair. Understanding the differences between these utilities will help you use them more effectively and efficiently.