There are a multitude of GUI and web tools available to assist in managing MySQL databases, but for the most part, the MySQL distribution itself has very good CLI tools to manage the databases. From backup to restoration, to running SQL queries and stored procedures, the CLI tools can do almost anything that the web and GUI tools can do. They’re fairly simple to use if you have any understanding of SQL. By the end of this article you’ll have a basic understanding of the major CLI tools and how they function, and some of the operations you can perform with them.
MYSQLSHOW
The first tool in the default MySQL toolbox is mysqlshow and it does exactly what it sounds like – it shows you information about the database itself. Below is an example of mysqlshow:
core$ mysqlshow -u root -p
Enter password:
+——————————+
| Databases |
+——————————+
| information_schema |
| cacti |
| mysql |
+——————————+
Alternate usage would include:
core$ mysqlshow -u root -p mysql
Enter password:
Database: mysql
+—————————————+
| Tables |
+—————————————+
| columns_priv |
| db |
| func |
| help_category |
| help_keyword |
| help_relation |
| help_topic |
| host |
| proc |
| procs_priv |
| tables_priv |
| time_zone |
| time_zone_leap_second |
| time_zone_name |
| time_zone_transition |
| time_zone_transition_type |
| user |
+—————————————+
MYSQLADMIN
The next useful command to discuss is mysqladmin, from here you can create and drop databases, flush logs, get status of the daemon, get versions, and perform shutdown operations on the daemon itself.
One of the more useful commands is version. It displays the version of the server, protocol, connection type, location, uptime, and the current information about how the daemon is running. The last few lines also show what you would get if you used the status keyword instead of version. Here’s an example:
core$ mysqladmin -u root -p version
Enter password:
mysqladmin Ver 8.41 Distrib 5.0.51a, for debian-linux-gnu on i486
Copyright (C) 2000-2006 MySQL AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 5.0.51a-24+lenny2
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /var/run/mysqld/mysqld.sock
Uptime: 49 days 4 hours 24 min 30 sec
Threads: 11 Questions: 3622276 Slow queries: 0 Opens: 147150 Flush tables: 1 Open tables: 63 Queries per second avg: 22.852
A more simple command to retrieve the status of the server is ping.
core$ mysqladmin -u root -p ping
Enter password:
mysqld is alive
You can also create and drop databases through the mysqladmin program, although I don’t recommend it. There is one other great code you can use with mysql-admin, which is extended-status. It shows *everything* you could want to know about the diagnostic state of MySQL.
MYSQLDUMP
Another excellent tool is the mysqldump utility. It dumps databases out of MySQL cleanly for export to other systems or to backup and restoration purposes. Here is a simple example of how to dump all the databases on the system out for restoration purposes in the event of a hardware failure:
core$ mysqldump -u root -p –all-databases > all-`date +%m%d%Y`.sql
MYSQL SHELL
The last tool to discuss that is important to understand is how to use the mysql shell. From here you can perform queries, dump the database, modify user permissions, and just about anything you can imagine from updates and imports to drops and deletions using standard SQL and mysql shell syntax. The shell command can be used to execute single queries from the command line in this manner:
core$ mysql -u root -p mysql -e “select * from user”
Or you can simply open the shell directly and perform the functions you require from there using the syntax below. Once inside, it’ll duplicate the previous CLI version of the query.
core$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 144082
Server version: 5.0.51a-24+lenny2 (Debian)
Type ‘help;’ or ‘\h’ for help. Type ‘\c’ to clear the buffer.
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker start up with -A
Database changed
mysql> select * from user;
Additionally you can use the mysql tool to import databases by invoking it using this syntax:
core$ mysql -u root -p databasename < sqlfile.sql
or alternately for all the databases:
core$ mysql -u root -p < sqlfile.sql
In all, the above covers the basic use of all the major tools you will use on a daily basis when interacting with MySQL. I hope you’ve learned some tricks and feel a bit more comfortable interacting with MySQL from the command line. It’s a tremendously valuable skill that is overlooked by many people, but extremely important when building the foundation to practicing MySQL.