Introduction
MySQL has a lot of tools to administer MySQL in command line. Most are provided as part of “MySQL utilities” offered by Oracle. Others are free software developed by third parties and made available under GPL licence.
Administration tools
MySQL Database administration with mysqladmin
Mysqladmin is the Swiss army knife of command line administration. This utility allows us to achieve various tasks, here are some examples.
Database creation
Host, user and password can be indicated :
shell> mysqladmin -localhost -u root -pmypassword create mynewdb
This is the equivalent to the MySQL command :
mysql> CREATE DATABASE mynewdb;
By default, the mysqladmin command solicits the local host and the shell user that runs the execution. In the case where the current user has an empty password in MySQL, which is obviously not recommended, it would be pointless to type it in. In that case, the command line would rather be as follow :
shell> mysqladmin create mynewdb
For the rest, in order to ease the reading, the latter notation will be used.
Database deleting
shell> mysqladmin drop myolddb
Dropping the database is potentially a very bad thing to do.
Any data stored in the database will be destroyed.
Do you really want to drop the ‘myolddb’ database [y/N] y Database «myolddb» dropped
This is the equivalent of the MySQL command :
mysql> DROP DATABASE myolddb;
Modifying password
In the case where special characters would be used, like spaces , quotes (or double quotes recommended for Windows) will frame the password
shell> mysqladmin password «my_new_passwd»
This is the equivalent of the following MySQL command :
mysql> SET PASSWORD FOR ‘mydbuser’@’mydbhost’ = PASSWORD(‘mynewpasswd’);
The mysqladmin password command applies to the user requested for the connexion. To change the password of the mydbuser user, proceed as follows :
shell> mysqladmin -u mydbuser -pmydbuserpasswd password «my_new_passwd»
In the case where mysqld is run with the –‑skip‑grant‑tables option, it will be necessary to read beforehand the privilege table, as shown below.
Since MySQL 5.5.3 the new password can be omitted, in that case the user will be asked to type it in interactively.
Reloading of the privileges table
When the privileges table was affected to a series of changes of rights, a user creation or a password changing, it is necessary to reload said page for the changes to take effect.This can be done with the MySQL FLUSH TABLE command or also in command line as follows :
shell> mysqladmin flush-privileges
To execute this command, the user must himself have the right RELOAD. Moreover, the same action can be made as follows :
shell> mysqladmin reload
This is equivalent to the MySQL command :
mysql> FLUSH PRIVILEGES;
Viewing the servers status
Summary information can be obtained in the following ways :
shell> mysqladmin status Uptime : 8203973 Threads :
3 Questions : 1237768640 Slow queries : 81 Opens :
16049131 Flush tables : 1 Open tables : 2048 Queries
per second avg : 150.874
For a complete server status, it is necessary to use the prefix « extended »:
shell> mysqladmin extended-status
This is the equivalent of the SQL command :
mysql> SHOW GLOBAL STATUS;
The list of state variables is relatively long, in order to at least partially satisfy the reader’s curiosity, only the first line will be displayed here:
+———————————–+————–+
| Variable_name | Value |
+———————————–+————–+
| Aborted_clients | 12 |
| Aborted_connects | 1459 |
| Binlog_cache_disk_use | 226 |
| Binlog_cache_use | 33844 |
| Bytes_received | 26333823812 |
| Bytes_sent | 923983889789 |
| Com_admin_commands | 94436 |
| Com_assign_to_keycache | 0 |
| Com_alter_db | 0 |
| Com_alter_db_upgrade | 0 |
| Com_alter_event | 0 |
| Com_alter_function | 0 |
| Com_alter_procedure | 0 |
| Com_alter_server | 0 |
| Com_alter_table | 333417 |
| Com_alter_tablespace | 0 |
| Com_analyze | 0 |
| Com_backup_table | 0 |
| Com_begin | 4 |
| Com_binlog | 0 |
[…]
Displaying the version number
Displaying the version number is done with mysqladmin followed by the version command :
Shell> mysqladmin version
Enter password:
mysqladmin Ver 8.42 Distrib 5.7.7-rc, for Linux on x86_64
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Server version 5.7.7-rc
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 28 days 6 hours 43 min 8 sec
Threads: 1 Questions: 50103 Slow queries: 0 Opens: 141 Flush tables: 1 Open tables: 126 Queries per second avg: 0.020
MySQL service shutdown
The MySQL service shutdown is done with the shutdown command :
shell> mysqladmin shutdown
Accumulated options and abbrevations
The mysqladmin utility can accumulate commands, as shown here with the version and status commands :
shell> mysqladmin version status
mysqladmin Ver 8.42 Distrib 5.7.7-rc, for Linux on x86_64
Copyright (c) 2000, 2015, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective owners.
Server version 5.7.7-rc
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 28 days 20 hours 42 min 30 sec
Threads: 1 Questions: 50111 Slow queries: 0 Opens: 141 Flush tables: 1 Open
ables: 126 Queries per second avg: 0.020
Uptime: 2493750 Threads: 1 Questions: 50112 Slow queries: 0 Opens: 141 Flush tables: 1 Open tables: 126 Queries per second avg: 0.020
In this case the results of the two commands are displayed successively. It is also possible to use the commands in abbreviated form. The abbreviation can be as short as desired, the only constraint being that there should not be any possible confusion. For example, the following command is understood :
shell> mysqladmin ve
But this one isn’t :
shell> mysqladmin sta
Indeed there is a competition between the status and start-slave options. It will therefore enter in minimum stat or star for every command.
Other functions supported by mysqladmin
To make the best use of pages of this book, not all commands will be explained. Their exhaustive description has no genuine interest here, since on the one hand, these commands are detailed in the MySQL reference documentation, and on the other hand the most useful of these commands will be implemented later in this book, or with mysqladmin either with their MySQL equivalents. Let us remember that with just mysqladmin, it is possible to stop and start a slave, view system variables, test the availability of a MySQL service, list running processes, terminate one or more processes, getting a debugging aid in the error logs, purging various caches, etc.