Despite the steps you take to secure and protect your databases, events such as power failures, natural disasters, and equipment failure can lead to the corruption and loss of data.
As a result, one of the most important steps that you can take to protect your data is to make certain that you maintain backup copies of your databases in a reliable and safe location. Ensuring that MySQL databases are backed up regularly should be part of any maintenance routine.
These backup files will contain the database and table definitions necessary to re-create your database structure as well as the instructions necessary to repopulate your tables. Using these backup files, you can recover your data to the state it was in at the time you performed the last backup. Further, you can use the binary log files to recover your data to post-backup current state. To summarize, backup and restore process involves:
- Use the mysqldump utility to back up tables in a single database or multiple databases.
- Use backup files with mysql monitor to reload databases.
- Use binary log files to update the databases after reloading.
Backing up or Exporting Databases Using mysqldump
The mysqldump program is the primary method in MySQL for backing up tables and databases and it can back up individual databases, tables in those databases, or multiple databases. When you run mysqldump, the utility creates a text file that contains the SQL statements necessary to create your database and tables safely and add data to those tables. This file is referred to as a backup file or dump file.
This section describes the usage of mysqldump utility.
Copying Data Directory
It is possible to back up databases simply by copying the data directory to a backup location. Most installations routinely take disk snapshot backups at various points. This method has several limitations, though. For example, if data is being accessed and updated during file copy, you might be copying tables that are in an inconsistent state. In addition, file-level copying may help MyISAM tables but InnoDB tables can be more complicated at file level.
It is simpler to use mysqldump which saves data as portable text files versus OS-level data directory copies.
There are three syntax variants of mysqldump where you can backup a single database, several enumerated databases, or all databases managed by MySQL.
Using the single database variant, the backup may be done for chosen tables. As a rule, the output of mysqldump is sent to a file with > backupfile.sql.
mysqldump - Syntax
|mysqldump [options] dbname [tables]|
|mysqldump [options] --databases [moreoptions] dbname1 [dbname2 ...]|
|mysqldump [options] --all-databases [moreoptions]|
mysqldump --tab=/path/to/some/dir --opt dbname1
mysqldump - Options
The table lists some useful options that apply to mysqldump command.
mysqldump - Options
|--add-drop-table||Inserts a DROP TABLE command before every CREATE TABLE; when tables are read in, existing tables are deleted. This option is part of --opt and holds by default.|
|--add-locks||Inserts LOCK TABLE before the first INSERT command and UNLOCK after the last INSERT command; generally speeds up reading in a database; this option is part of --opt and holds by default. However, the option should not be used with InnoDB tables.|
|--all||Specifies all the detailed MySQL-specific options in the CREATE TABLE command.|
|-A||--all-databases||Saves all databases managed by MySQL; CREATE DATABASE and USE are placed in the backup file.|
|-B||--databases||Stores several databases.|
|--compatible=name||Determines with what database system or standard the backup should be compatible. Allowable settings are ansi, mysql323, mysql40, postgresql, oracle, mssql, db2, maxdb, no_key_options, no_table_options, no_field_options. It is allowed to have more than one key word, separated by commas.|
|--complete-inserts||Generates for each data record a separate INSERT command.|
|--create-options||Includes all MySQL-specific options in CREATE commands. This option is part of -- opt and holds by default.|
|Specifies the character set in which the backup file is to be created (by default UTF8).|
|--delayed-inserts||Creates INSERT commands with the option DELAYED.|
|-K||--disable-keys||Inserts ALTER TABLE ... DISABLE KEYS or ... ENABLE KEYS before or after INSERT commands; indexes are thereby not updated until the end of the insertion process, which is faster.|
|-e||--extended-insert||Generates few INSERT commands with which several records can be inserted simultaneously (more efficient and reduces the size of the backup file). This option is part of --opt and holds by default.|
|-F||--flush-logs||Updates the logging files before the backup is begun.|
|-f||--force||Continues even after errors.|
|--hex-blob||Outputs the content of binary fields (BLOBs) in hexadecimal format.|
|-x||--lock-all-tables||Executes LOCK TABLE READ for the entire database. This ensures that during the entire backup no table can be changed. Note that --lock-tables does not offer this protection, but prevents changes only during the time in which a table is locked. At this time another table can be changed.|
|-l||--lock-tables||Executes a LOCK TABLE READ for every table before the data are read; this ensures that no data can be changed while mysqldump is running. This option is part of -- opt and holds by default. The obvious draw-back is that all write processes are blocked until mysqldump is done, which can take a while with large databases (with InnoDB tables, you should use --single-transaction instead of --lock-tables).|
|--master-data [=n]||Adds a comment at the end of the output containing a CHANGE MASTER command. This command is suitable for replication on a slave system. If n=1 is passed as parameter, then the CHANGE MASTER command is added in a normal fashion (not as a comment), so that it is automatically executed when the backup is restored. This option also activates --lock-all- tables if --single-transaction is not used at the same time.|
|--no-create-db||Creates no CREATE DATABASES commands. (These commands are created only if the option --all-databases or --databases is used.)|
|--no-create-info||Creates no CREATE TABLE commands, but only the INSERT commands.|
|--no-data||Creates no INSERT commands (but only CREATE TABLE commands in order to restore the database schema).|
|--opt||Shorthand for the following options: --add-drop-table, --create-options, --add-locks, --disable-keys, --extended-insert, --lock-tables, --quick, --set-charset. In most cases, this is an optimal setting, for which reason it holds by default. If you don't wish this, you must use --skip-opt.|
|-q||--quick||Outputs results record by record without internal intermediate storage. This option is part of --opt and holds by default. Without this option, first the entire table is moved into RAM and then output; the advantage of --quick is the lower memory requirement; the disadvantage is that the MySQL server is generally blocked for a longer period of time; --quick should definitely be used for very large tables (when the entire table cannot be held in RAM of the local computer).|
|-Q||--quote-names||Encloses table and column names in single quotes (e.g., 'name').|
|--set-charset||Changes the active character set at the start of mysqldump output, and at the end, the previously valid character set is restored. This option is part of --opt and holds by default. It can be deactivated with --skip-char-set.|
|--skip-opt||Deactivates the default option --opt.|
|--single-transaction||Results in all tables being read within a single transaction; this makes sense only when InnoDB tables are used, in which Backup this option ensures that no data are changed during output. This option deactivates --lock-tables.|
|-T dir||--tab=dir||Writes the result directly into the specified directory, whereby for each table two files are created, one with the table structure (*.sql) and the second with the stored data in the format of the command SELECT ... INTO OUTFILE (*.txt).|
|-w cnd||--where=condition||Considers only data records that satisfy the WHERE condition cnd or condition; the entire option must be placed in quotation marks, e.g., "-wprice>5" or "--where=ID=3".|
|-X||--xml||Creates an XML file with the contents of the table (without information on the table structure).|
If you are using --tab, then the second file (tablename.txt) contains the contents of the table directly (that is, not in the form of INSERT commands). This has several advantages: The resulting file is somewhat more compact, and a later importation can be executed significantly more quickly. (However, the operation is more complex, and only a single table can be handled.)
See Export/Import lesson for formatting options.
The options --fields and --lines should each be set in quotation marks. The following example shows how you can pass the double quote itself as a character to the option:
> mysqldump -u root -p --tab /tmp "--fields-enclosed-by=\"" ...
To reinput the file thus generated (*.txt) with mysqldump, you can use either the program mysqlimport, discussed in the following section, or the SQL command LOAD DATA.
Backing up a Single Database
MySQL allows you to back up all tables in a database or only specific tables in that database. In both cases, you use the mysqldump client utility and you specify the name of the database. When backing up only specific tables, you must specify those table names as well. In this section, you learn how to perform both types of backups.
Backing up the Entire Database
The first form of the mysqldump command that you examine backs up all the tables in a database. The database is backed up to a backup file that includes the table definitions and the INSERT statements necessary to repopulate the tables. To use this form of the command, you must specify the name of the database and the path and filename of the backup file, as shown in the following syntax:
As you can see, your command includes the mysqldump utility name, followed by the database name. The path and filename are then introduced by a right arrow (>) that tells the mysqldump utility to send the backed-up definitions and data to the specified file. If you do not include the right arrow and path and filename, the backup output would merely be displayed in your command line.
Here are a number of examples of mysqldump.
mysqldump -usakila_admin -psakila sakila > c:\mysql\backups\sakila_080311.sql
Entire backup of sakila.
All tables in the database will be backed up.
The script includes both the CREATE TABLE and INSERT statements.
The output is saved in c:\mysql\backups\sakila_080311.sql.
mysqldump -usakila_admin -psakila -X sakila actor > c:\mysql\backups\actor_080311.xml
Create an XML output file for the actor table.
Note: The filenames in our examples contain a date/time. It's a good idea to use some sort of consistent naming convention for your backup files to distinguish and locate the right one.
Contents of the Backup SQL Script
Once the file has been created, you can view its contents by using a text editor. The contents of the sakila_080311.sql file looks like:
MySQL saves some system values in user-defined variables to restore the original system settings should they be changed by any of the statements while executing the backup file, thereby ensuring that your environment is left in the same state after the restore via execution of the statements in the backup file.
The SET statement saves the current value associated with the character_set_client system variable to the @old_character_set_client user-defined variable.
/*!40101 SET @OLD_CHARACTER_SET_CLIENT=CHARACTER_SET_CLIENT */;
We then use SET at the end of the backup file to restore character_set_client system variable from @old_character_set_client we had saved before the statements:
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
The following table describes the system variables that are used to assign values to user-defined variables in the backup file.
|character_set_client||The character set that MySQL uses to process SQL statements sent by a client application. By default, MySQL uses the latin1 character set.|
|character_set_results||The character set that MySQL uses to return query results to a client application. By default, MySQL uses the latin1 character set.|
|collation_connection||The collation associated with the character set used for the connection. By default, MySQL uses the latin1_swedish_ci collation.|
|unique_checks||Specifies whether MySQL checks for uniqueness in a column configured with a unique index. By default, MySQL checks for uniqueness.|
|foreign_key_checks||Specifies whether MySQL checks foreign key constraints in a column configured as a foreign key. By default, MySQL checks foreign key constraints.|
|sql_mode||Specifies the SQL mode in which MySQL should operate. The mode determines what SQL syntax is supported and how data should be validated. By default, no mode is set.|
Enclosing Characters /*! and */
Note that the SET statements begin with the /*! symbols and end with the */ symbols. The symbols ensure the statements are executed by MySQL but ignored if they are executed in another database management system. This allows your basic SQL statements in the backup file to be used by other database systems, while preventing errors on statements that are important and unique to MySQL.
These symbols at the beginning of the statements are also followed by a number which represents a MySQL server version. This number tells MySQL to execute the statement only if that version or a later version is being used. For example, 40101 indicates that the statement should be executed only on MySQL version 4.01.01 or above.
Now take a look at one other SET statement that is included at the beginning of a backup file:
/*!40101 SET NAMES utf8 */;
The SET NAMES statement specifies the name of the character set that should be used during the execution of the statements in the backup file. The statement applies to current connection only. In this case, the SET NAMES statement causes utf8 character set to be used, same as when the backup file was created.
The contents of the backup file that we have seen so far is common to backup files created with the mysqldump utility.
The other information in the file is specific to the tables that you have backed up. For each table, the file includes a table definition and an INSERT statement. The table definition is introduced by comments similar to the following:
-- Table structure for table 'actor'
In this case, the comments tell you that the information that follows applies to the actor table. The comments are then followed by a DROP TABLE statement followed by the CREATE TABLE statement then defines the table as it was at time of backup.
After the CREATE TABLE statement, the backup file includes the section that inserts data in the actor table:
-- Dumping data for table 'actor'
LOCK TABLES 'actor' WRITE;
/*!40000 ALTER TABLE 'actor' DISABLE KEYS */;
INSERT INTO 'actor' VALUES (1,'PENELOPE','GUINESS','2006-02-15 09:34:33'),...;
/*!40000 ALTER TABLE 'actor' ENABLE KEYS */;
An ALTER TABLE statement that tells MySQL to disable the indexes precedes each INSERT statement. The ALTER TABLE statement at the end of this group of statements re-enables the indexes. MySQL does this to improve the performance of the insert operations where the indexes created after the inserts are done.
Warning: This process, however, works only for MyISAM table types and is ignored by other table types.
A LOCK TABLES statement also precedes the INSERT statement, placing a WRITE lock on the table so that no other values can be inserted in the table until after this INSERT statement has been executed. After the INSERT statement runs, the table is then unlocked.
The INSERT statement in the backup file provides values for all columns in the table as they were at time of backup. In usual Inserts, we do not specify several columns such as auto- increments, last-updates.
Ignoring Foreign Key Constraints
Both the INSERT statement and the CREATE TABLE statement that precedes it is that they create a table and insert data in a table that includes foreign key constraints.
If you were to try to create the same table manually before referenced tables are in place, you would encounter an error. MySQL, however, allows all tables to be created and values to be inserted when done through a backup file, regardless of the foreign key constraints.
This way, MySQL can ignore the order of appearance of tables in the backup file.
The option --flush-logs flushes your log files and a new binary log file is created. This is important when creating a backup because binary log files allow you to restore your database fully.
By flushing the logs, you get an exact starting point for using the binary logs when refreshing your restored database. It is recommended that you use the --flush- logs option whenever you back up your data.
To flush the logs, see following example:
mysqldump --flush-logs -u sakila_admin -psakila sakila > c:\mysql\backups\sakiladb_080311.sql
Logs are flushed before the backup starts.
A new log file is created with an incremented suffix (discussed in another lesson).
Now when you need to restore the database, it will be easier locating the proper log(s) to read.
Backing up Individual Tables
To use the mysqldump client utility to back up individual tables in a database, add the applicable table names after the database name in your mysqldump command: