Be sure to check out my other posts on mysqldump:
– Scripting Backups of MySQL with Perl via mysqldump
– Splitting a MySQL Dump File Into Smaller Files Via Perl
– Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 1 of 2
– Creating and restoring database backups with mysqldump and MySQL Enterprise Backup – Part 2 of 2
I have already written several posts on using mysqldump. This post will be a quick guide to using mysqldump to backup and restore your databases. And we will look at using the MySQL binary log (binlog) along with mysqldump for point-in-time recovery. If your database is extremely large, and for databases in a production environment, you might want to consider using MySQL Enterprise Backup (mysqlbackup), which is part of the MySQL Enterprise Edition.
For those of you that aren’t familiar with mysqldump:
The mysqldump client is a utility that performs logical backups, producing a set of SQL statements that can be run to reproduce the original schema objects, table data, or both. It dumps one or more MySQL database for backup or transfer to another SQL server. The mysqldump command can also generate output in CSV, other delimited text, or XML format.
The best feature about mysqldump is that it is easy to use. The main problem with using mysqldump occurs when you need to restore a database. When you execute mysqldump, the database backup (output) is an SQL file that contains all of the necessary SQL statements to restore the database – but restoring requires that you execute these SQL statements to essentially rebuild the database. Since you are recreating your database, the tables and all of your data from this file, the restoration procedure can take a long time to execute if you have a very large database.
NOTE: If you are using GTID’s (global transaction identifiers) in your database, you will need to include the –set-gtid-purged=OFF option, otherwise you will receive this error:
Warning: A partial dump from a server that has GTIDs will by default include the
GTIDs of all transactions, even those that changed suppressed parts of the database.
If you don’t want to restore GTIDs, pass –set-gtid-purged=OFF. To make a complete dump, pass –all-databases –triggers –routines –events. For these examples, I will not include the –set-gtid-purged=OFF option.
Dumping and making a copy of a single database
To dump/backup a single database:
mysqldump -uroot -p database_name > db_dump.sql
To load the dump file back into mysql, you will need to create the new database first. If you use the –databases option before the database name, mysqldump will also dump the CREATE DATABASE and USE statements that you need prior to inserting the data from the dump.
You can either use mysqladmin to create the new database, or create it from a MySQL prompt:
# mysqladmin create new_database_name
mysql> CREATE DATABASE new_database_name;
Next, you can simply import the dump file into mysql.
# mysql new_database_name < db_dump.sql
You can also use the dump file to move the database to another server. If you did not use the –databases option, then you will need to create the database first.
Dumping events, routines, triggers
Here are the options for mysqldump to also dump event scheduler events, stored procedures or functions. If you want to include these, use these options:
–routines – dumps stored procedures and functions
–events – dumps Event Scheduler events
–triggers – dumps triggers
When you use mysqldump, triggers are dumped by default. If you want to disable any of these functions, use the “skip” versions: http://ift.tt/1pp1UJF, –skip-routines, or –skip-triggers.
Only dump table definitions
If you want to just dump the CREATE TABLE statements without any data, use the –no-data option.
# mysqldump –no-data database_name > db_definitions_dump.sql
You might want to add the –routines and –events options to include stored routines and event definitions.
# mysqldump –no-data –routines –events database_name > db_definitions_dump.sql
Only dump the data
If you want to just dump the data without any table definitions, you can use the –no-create-info option:
# mysqldump –no-create-info database_name > db_data_dump.sql
Using mysqldump to test a new version of mysql
Let’s say that you need to upgrade to a new version of mysql. In order to test to see if your database objects are handled properly by the new version, you can simply dump the data definitions and import them into the new version of MySQL (preferably on a separate computer).
On the computer with the old version of MySQL:
mysqldump –all-databases –no-data –routines –events > db_definitions.sql
Then, on the upgraded server, you can just import this dump file:
mysql -uroot -p < db_definitions.sql
This will help you spot any potential incompatibilities with the new version of MySQL. If you don’t receive any errors, you can then dump the data and load into the new server. Be sure to run some test queries on the new upgraded server as well.
Point-in-time recovery using mysqldump and the binary logs
The MySQL binary logs (binlogs) contains all of the SQL statements or “events” that could change the database (I say “could” because a delete statement that does not delete any rows would still be entered into the binary log – unless you are using row-based logging). For more information about the binary log, see: http://ift.tt/WclZj3.
Since the binlog contains all of the events that happen to the database, you can use the binlog to apply these same changes to a different database. If you started your MySQL instance with the binlogs enabled, and you have never flushed the binlogs, then the binlogs contain all of the SQL statements for all of the data that is in your database. The binlog itself is like a backup of your database.
If you want to use the binary logs in addition to mysqldump to restore your database, you need to have the binary logs (binlogs) enabled. There are many options for the binlogs (see http://ift.tt/UgG6dj, but the only two that you really need for this example are:
–log-bin[=base_name]
–log-bin-index[=file_name]
One other option is to use the –binlog-format. You can set this value to STATEMENT (default), ROW or MIXED. For more information about these options, see http://ift.tt/1pp1UJH.
These variables need to go into your my.cnf or my.ini file under the [mysqld] section, and this will require a restart of mysqld.
Once you have the binary log enabled, you will need to do a few things differently when you use mysqldump. You will need to:
– flush the tables and place a READ lock on the tables
– check to see what binlog is being used
– check the position of the binlog
– dump your data with mysqldump
– release the lock
By placing a read lock on the tables, you are stopping anyone from modifying the data in the database. By having the binlog and binlog position, these will allow you use the binary logs to restore any statements that happened after the mysqldump. Open two terminal windows – one with a MySQL prompt, and another with a root prompt:
In the MySQL prompt, issue the READ lock and SHOW MASTER STATUS:
mysql> FLUSH TABLES WITH READ LOCK;
Query OK, 0 rows affected (0.47 sec)
mysql> SHOW MASTER STATUS\G
*************************** 1. row ***************************
File: mysql-bin.000008
Position: 191
Binlog_Do_DB: Binlog_Ignore_DB: Executed_Gtid_Set: 1 row in set (0.00 sec)
Now you are ready to dump the database with whatever options you need:
# mysqldump –all-databases > db_000008_191_dump.sql
Once the dump has finished, you can release the lock:
mysql> UNLOCK TABLES;
Query OK, 0 rows affected (0.00 sec)
It is extremely important that you write down the binlog file and position from the SHOW MASTER STATUS statement and somehow associate it with the dump file. One way to do this is to insert the binlog file name and position into the dump file name. In my example above, I did this by adding both to the dump file name db_000008_191_dump.sql.
When you have to restore the database, you will need to load the dump file first, and then apply the binlog(s) to the database.
Let’s assume that we need to restore the entire database. First, we will import the dump file:
# mysql -uroot -p < db_000008_191_dump.sql
Next, you will need to load the information from the binlog(s). To load information from the binlogs, you need to use the mysqlbinlog utility. You can check your MySQL data directory to see how many binlogs you have used since the one that was in the SHOW MASTER STATUS statement:
$ ls -l mysql-bin*
-rw-rw—- 1 mysql wheel 67110 Apr 4 16:22 mysql-bin.000001
-rw-rw—- 1 mysql wheel 1230893 Apr 4 16:24 mysql-bin.000002
-rw-rw—- 1 mysql wheel 13383907 Apr 4 17:03 mysql-bin.000003
-rw-rw—- 1 mysql wheel 13383907 Apr 4 19:03 mysql-bin.000004
-rw-rw—- 1 mysql wheel 13383907 Apr 4 19:07 mysql-bin.000005
-rw-rw—- 1 mysql wheel 13383907 Apr 18 16:48 mysql-bin.000006
-rw-rw—- 1 mysql wheel 13383907 Apr 21 13:37 mysql-bin.000007
-rw-rw—- 1 mysql wheel 13383907 Apr 21 13:37 mysql-bin.000008
-rw-rw—- 1 mysql wheel 154847 Apr 21 13:37 mysql-bin.000009
-rw-rw—- 1 mysql wheel 171 Apr 21 13:37 mysql-bin.index
You can also just look at the mysql-bin.index file (located in your MySQL data directory), which contains a list of all of the binary files in use:
# cat mysql-bin.index
./mysql-bin.000001
./mysql-bin.000002
./mysql-bin.000003
./mysql-bin.000004
./mysql-bin.000005
./mysql-bin.000006
./mysql-bin.000007
./mysql-bin.000008
./mysql-bin.000009
In this example, we will need to apply the changes from the binlog file mysql-bin.000008 after position 191, and then all of the mysql-bin.000009 binlog. You will need to add the correct data directory PATH to your mysqlbinlog statement.
mysqlbinlog –start-position=191 $DATA_DIR_PATH/mysql-bin.000008 | mysql -u root -p
After you have inserted all of mysql-bin.000008 after position 191, you can insert the entire mysql-bin.000009 binlog file:
mysqlbinlog $DATA_DIR_PATH/mysql-bin.000009 | mysql -u root -p
Note: During the restore process, you do not want anyone inserting any data into the database.
Your database should now be back to the state when the database crashed or was deleted. It isn’t mandatory, but it is also a good idea to copy the binlogs to a separate location as part of your backup routine. You can use mysqlbinlog to do this as well – see: http://ift.tt/Rwfc2V.
For more information on using the binary logs for point-in-time recovery, see http://ift.tt/1mwazW6. There are a lot of other options for using binlogs. The best option for backing up and restoring your MySQL database is to use the MySQL Enterprise Backup (mysqlbackup), which is part of the MySQL Enterprise Edition subscription, which includes 24×7 MySQL Support and the other Enterprise features.
Tony Darnell is a Principal Sales Consultant for MySQL, a division of Oracle, Inc. MySQL is the world’s most popular open-source database program. Tony may be reached at info [at] ScriptingMySQL.com and on LinkedIn.
via Planet MySQL
Using mysqldump and the MySQL binary log – a quick guide on how to backup and restore MySQL databases