The ‘mysqldump’ is a tool to back up a single database or a set of databases, and then move the backed up copies to a different server (can be SQL Server or MySQL server). This backup program creates a ‘dump’ of select databases to a preferred location. Typically, the process involves forming a group of SQL Statements to create and populate the tables of databases being backed up. You can use ‘mysqldump’ to create database backups in other formats, including CSV, other delimited text, or XML format.
The user performing the dump must have appropriate privileges for different objects, such as ‘SELECT’ and ‘LOCK TABLES’ (if you are not using the ‘–single-transaction’ option) privileges for tables, ‘SHOW VIEW’ for views. If backups are being performed on the server and you are using MyISAM tables, then you can speed up the process by using ‘mysqlhotcopy’.
In general, you can start ‘mysqldump’ using any of the following commands:
shell> mysqldump [options] db_name [tbl_name …]
shell> mysqldump [options] –databases db_name …
shell> mysqldump [options] –all-databases
For backing up all databases, use ‘–databases’ and ‘–all-databases’ options. By default, ‘mysqldump’ does not work on the ‘INFORMATION_SCHEMA’ database. In case you need to back up this database, you need to specify it on the command line. When dumping ‘INFORMATION_SCHEMA’, you should use the ‘–skip-lock-tables’ option. This can be accomplished if you are running MySQL 5.1.38 or later. With MySQL 5.1.64, ‘mysqldump’ also backs up the ‘general_log’ or ‘slow_query_log’ tables.
Typically, ‘mysqldump’ is used to create a ‘.sql’ file with T-SQL statements, such as DROP table, CREATE table, and INSERT. You can restore your database from dump by executing the ‘.sql’ file. With this tool, you can restore a local database to a remote destination.
Using mysqldump to Backup MySQL Databases
Dumping a Single Database
Execute the following at the command line to back up a single MySQL database:
# mysqldump -u root -p[root_password] [database_name] > dumpfilename.sql
Replace ‘[database_name]’ with the name of the database to be dumped, ‘[root_password]’ with the root password for MySQL, and ‘dumpfilename.sql’ with the name of the dump file. ‘dumpfilename.sql’ will contain commands for each table in your database.
Dumping Multiple Databases
‘mysqldump’ allows you to back up multiple databases simultaneously. To perform this task, you need to identify which databases you want to back up. In order to do this, you can use the ‘show databases’ command as follows:
# mysql -u root -ptmppassword
mysql> show databases;
For instance, to back up any two databases, you can run ‘mysqldump’ as follows:
# mysqldump -u root -p[root_password] –databases [database_name1] [database_name2] > dumpfilename.sql
You can also check whether the dump contains all the backups. To do so, do the following:
# grep -i “Current database:” [Full path of the dump]
— Current Database: `database_name1`
— Current Database: `database_name2`
The ‘mysqldump’ tool is an easy and reliable backup option for those who need to keep from facing data loss in times of database corruption and similar disasters.
Latest posts by gorge (see all)
- A Peek Into Common MySQL Database Corruption Errors and Recommended Solutions - May 1, 2013
- Exploring Different Backup options of ‘mysqldump’ Tool - April 16, 2013
- What to do after Facing Corruption in MySQL InnoDB tables - March 19, 2013