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.
Backups
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.
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
Admin
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.
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.
Subscribe to our newsletter!
Our latest tutorials delivered straight to your inbox
Sign up for all newsletters.
By signing up, you agree to our Privacy Policy and European users agree to the data transfer policy. We will not share your data and you can unsubscribe at any time. Subscribe
Gary Sims
Gary has been a technical writer, author and blogger since 2003. He is an expert in open source systems (including Linux), system administration, system security and networking protocols. He also knows several programming languages, as he was previously a software engineer for 10 years. He has a Bachelor of Science in business information systems from a UK University.
Comments are closed