Backup and Manage MySQL Databases from the Command Lines

MySQL databases play an integral part in many computer systems, especially when it comes to web hosting and internet services. With all that valuable data being entrusted to the database it is essential to make backups, and it is important to know how to administer the database server. MySQL includes two powerful tools for these tasks : “mysqldump” and “mysqladmin.” As their names suggest, the first is for backups and the other is for administration. Mastering these two commands is paramount if you want to become a competent MySQL admin.

The mysqldump command creates a file of SQL statements that when run, will recreate the same tables and data that are in the database. It can be used as a method of backup or as an easy way to copy a database from one server to another. It can also create output as comma separated values (CSV), or even in XML. Since the resulting output is a text file of SQL statements, administrators have the flexibility of viewing and altering the dump file.

To dump a single table in a database, use mysqldump as follows:

mysqldump -u root -p testdb tablename

Where “testdb” is the name of the database and “tablename” is the name of the table you wish to dump. The “-u” option specifies which MySQL user to use during the dump and “-p” tells mysqldump to get the password interactively (i.e. you will need to type in the password when prompted). The user needs to have read access to the database that is being dumped (technically the user needs the SELECT, SHOW VIEW, TRIGGER and LOCK TABLES privileges). The “root” user is normally configured (along with a password) during the MySQL server installation.

mysqldump-testdb

The output will be sent directly to the console. To send the output to a file, and to specify the password on the command line (useful for backup scripts) then use:

mysqldump -u root --password='secret' testdb tablename > testdb_table_backup.sql

Where “secret” is the password for the user root, and tablename is the name of the table you want to back up. You should replace “_table_” in the filename with the real table name. The resulting file “testdb_table_backup.sql” will contain all the SQL statements necessary to recreate the table.

You can supply more than one table parameter to back up several tables, or if you omit it completely, then the entire database will be dumped. For example:

mysqldump -u root --password='secret' testdb > testdb_backup.sql

The resulting file “testdb_backup.sql” will contain a dump of all the tables in the database.

To dump more than one database you need to use the --databases option. For example:

mysqldump -u root --password='secret' --databases testdb testdb2 > testbd_testdb2_backup.sql

To backup all the tables from all the databases on the MySQL server, use the “--all-databases” option:

mysqldump -u root --password='secret' --all-databases > all_dbs.sql

The mysqladmin tool is used to perform administrative tasks including checking the server’s configuration and current status, and to create and drop databases, etc. To create a database called “testdb” use:

mysqladmin -u root -p create testdb

The “-u” and “-p” options work the same as with the mysqldump command.

To delete a database along with all of its tables use the “drop” sub-command:

mysqladmin -u root -p drop testdb

After you confirm the deletion of the database, it, along with all its tables, will be removed from the server.

mysqladmin-drop-testdb2

The mysqadmin command can also retrieve various statistics from the MySQL server. Try the “status” and “extended-status” sub-commands. The simplest status request is the “ping” command which checks to see if the server is alive. Here is an example of how to use it:

mysqladmin -u root --password='secret' ping

You can get detailed information about the mysqldump command from the MySQL documentation page A Database Backup Program, and likewise for the mysqladmin command from the Client for Administering a MySQL Server page.

If you have any questions regarding the examples given above, please ask in the comments section below.