1. Overview
MySQL remains one of the most popular relational databases since its beginning, more than twenty years ago. Because of being open-source, it’s always an option for web server solutions and standalone applications alike.
After installing MySQL, we’ll have several binary utilities available, and it’s easy to get confused regarding the use of each one.
In this tutorial, we’ll see the purpose of the most used MySQL utilities, along with some examples.
The explanations here also apply to MariaDB, a popular fork made by the original developers of MySQL.
MariaDB was created due to concerns that MySQL may not remain open-source, and intends to maintain high compatibility with MySQL.
2. Installing and Using MySQL
Most Linux distributions provide two packages for MySQL. The first one is usually called mysql-server. This package contains the server and all utilities to connect to the server. The second package is called mysql-client and contains only the utilities. We can use it to connect to a server on a reachable location elsewhere.
Regardless of the package, we choose to install, we will have several commands that begin with “mysql”. If our distribution uses the bash-completion package, we can type “mysql*
$ mysql
mysql mysqldump mysqlrepair
mysql_config_editor mysqldumpslow mysqlreport
mysqladmin mysqlimport mysqlshow
mysqlanalyze mysqloptimize mysqlslap
mysqlcheck mysqlpump
$ mysql
Let’s focus first on the commands that can cause more confusion: mysql and mysqladmin.
2.1. mysql
mysql is the command-line client and the main binary to connect to a MySQL server. It provides a shell where we can interact with the MySQL or MariaDB server. When installing the mysql-server package, we can connect by simply issuing the command:
$ sudo mysql -u root
Welcome to the MySQL monitor. Commands end with ; or \g.
...
mysql>
Through this command, we gain access to the shell in interactive mode. We can enter SQL statements in succession to interact with our databases, using the same connection.
Most Linux distributions will require to run these utilities as superuser, as we did above with the sudo command.
The MySQL shell allows querying the databases using SQL. For example, we can query the system database to get all the users on the system:
mysql> select host, user from mysql.user;
+-----------+------------------+
| host | user |
+-----------+------------------+
| % | root |
...
+-----------+------------------+
6 rows in set (0.00 sec)
mysql>
We can also use MySQL-specific statements like USE and SHOW TABLES:
mysql> USE mysql;
...
Database changed
mysql> SHOW TABLES;
+---------------------------+
| Tables_in_mysql |
+---------------------------+
| columns_priv |
...
+---------------------------+
33 rows in set (0.01 sec)
mysql>
There are several ways to leave the shell, but one of the easiest is pressing the keys CTRL+D:
mysql> ^DBye
$
2.2. mysqladmin
mysqladmin is a tool for performing administrative tasks on the server. It’s a non-interactive client that requires the command with the action we want to perform.
Although it’s not uncommon to mistake these last two binaries, both are used in different contexts to achieve different results.
We may use mysql more often to interact with our databases and tables. On the other hand, we may use mysqladmin more sparingly to change something on the server.
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
$
To add to the confusion, we can also create and drop databases with mysql. But that’s where the comparison ends. All other actions we’ll see now can only be achieved with mysqladmin.
Let’s see, for example, how to check the status of the server by using ping first, and then status:
$ sudo mysqladmin ping
mysqld is alive
$
$ sudo mysqladmin status
Uptime: 89118 Threads: 2 Questions: 4379 Slow queries: 0 Opens: 7087 Flush tables: 3 Open tables: 516 Queries per second avg: 0.049
$
Monitoring systems, in particular, can find the commands above very useful for periodic checks.
Let’s suppose that we need to identify and delete a long-running query. We can use processlist and kill for that:
$ sudo mysqladmin processlist
+----+-----------------+-----------+----+---------+-------+------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-----------------+-----------+----+---------+-------+------------------------+------------------+
| 5 | event_scheduler | localhost | | Daemon | 89464 | Waiting on empty queue | |
| 41 | root | localhost | | Query | 51 | executing | select ... |
| 43 | root | localhost | | Query | 0 | starting | show processlist |
+----+-----------------+-----------+----+---------+-------+------------------------+------------------+
$
$ sudo mysqladmin kill 41
$
There are several other commands we can use with mysqladmin, for instance, to change a password or reload permissions.
2.3. mysqldump
mysqldump is another helpful utility. It’s used to backup databases. It outputs a set of SQL statements that, when executed, can recreate the database we backed up. We need to redirect the output to a file if we want to save it:
$ sudo mysqldump sys > sys_database.sql
If we want to restore the backup later, we can use mysql to execute all commands in the backup file:
$ sudo mysql < sys_database.sql
When the backup contains only one database, we need to edit it to include the statement USE
$ sudo mysql < sys_database.sql
ERROR 1046 (3D000) at line 22: No database selected
$
mysqldump is a convenient tool. It produces an output that is easy to understand, and the process may take a reasonable time to execute, even for big databases.
The process of restoring a database, on the other hand, involves the execution of a large number of statements, each with its own disk I/O delay. For big databases, it may be best to perform a physical backup, copying from the filesystem directly.
2.4. mysqlimport
mysqlimport is a tool to execute the LOAD DATA statement to insert data “at a very high speed”, as the MySQL documentation explains.
Data needs to be exported first with a SELECT … INTO OUTFILE statement to be able to be imported with mysqlimport:
$ sudo mysql
...
mysql> SELECT * FROM the_database.the_table INTO OUTFILE '/var/lib/mysql-files/the_table.data';
Query OK, 7 rows affected (0.01 sec)
Later, on a different server or database, we can import the data:
$ sudo mysqlimport the_database /var/lib/mysql-files/the_table
2.5. mysqlcheck
The last utility we’ll talk about is mysqlcheck, a convenient tool to execute the CHECK TABLE SQL statement. As with other commands, we need to provide at least the database we want to check:
$ sudo mysqlcheck mysql
mysql.columns_priv OK
...
mysql.user OK
$
Additional symbolic links to mysqlcheck were added to execute other table maintenance statements — mysqlrepair for REPAIR TABLE, mysqlanalize for ANALYZE TABLE, and mysqloptimize for OPTIMIZE TABLE. We can find the usage details of each statement in the MySQL documentation.
3. Conclusion
When we install MySQL or MariaDB, we get several utilities that begin with “mysql”. It may be easy to mistake the use of each tool, especially mysql and mysqladmin.
In this tutorial, we saw examples of use for the most popular MySQL utilities. Even when mysql may be the most used tool, the remaining tools have a specialized purpose, and we’d do well to know how to use them.