MySQL full backup, incremental backup and recovery [go]

Original link: http://www.360doc.com/content/11/1209/09/834950_170836197.shtml

Scenario: Perform a full backup every Sunday, performing an incremental backup every 1 o'clock in the afternoon

[Applicable version] MySQL 4.x–6.x

Configuration

The prerequisite for performing an incremental backup is that MySQL turns on the log-bin log switch, for example, in My.ini or my.cnf

Join

Log-bin=/opt/data/mysql-bin

The string after "Log-bin=" is recorded in the log directory, and is generally recommended on a different disk than the MySQL data directory.

[Full backup]

Assume that Sunday 1 o'clock in the afternoon performs a full backup for the MyISAM storage engine.

Mysqldump–lock-all-tables–flush-logs–master-data=2-u root-p Test > Backup_sunday_1_pm.sql

Replace –lock-all-tables with –single-transaction for InnoDB

–flush-logs to end the current log, generate a new log file

The –master-data=2 option will record the name of the new log file after the full backup in output SQL.

References for later recovery, such as the output of a backup SQL file, contain:

–change MASTER to Master_log_file= ' mysql-bin.000002′, master_log_pos=106;

Other Notes:

If Mysqldump plus –delete-master-logs clears the previous log to free up space. However, if the server is configured as a mirrored replication master, it is dangerous to delete the MySQL binary log with Mysqldump–delete-master-logs because the contents of the binary log may not be fully processed from the server. In this case, it is more secure to use PURGE MASTER logs.

[Incremental backup]

Use Mysqladmin flush-logs daily to create a new log and end the previous log write process. And

Take the previous log backup, for example in the previous example, to start saving the log file under the data directory mysql-bin.000002, ...

[Recover from backup]

* Restore full backup

Mysql-u Root-p < Backup_sunday_1_pm.sql

* Restore Incremental backups

Mysqlbinlog mysql-bin.000002 ... | Mysql-u root-p

Note that this recovery process will also be written to the log file, if the amount of data is large, it is recommended to turn off the log function

Reference: http://bbs.bitscn.com/171622

Common parameters:

–lock-tables,-l

Lock all tables before dumping them. The tables is locked with a READ LOCAL to allow concurrent inserts in the case of MyISAM

Tables. For transactional tables such as InnoDB and Bdb,–single-transaction are a much better option, because it does not need

To lock the tables at all.

Please note if dumping multiple databases,–lock-tables locks tables for each database separately. Therefore, this

option does not guarantee, the tables in the dump file is logically consistent between databases. Tables in different

Databases May is dumped in completely different states.

–no-data,-D

Do not write any table row information (that is, does not dump table contents). This was very useful if you want to dump only the

CREATE Table statement for the table.

–opt

This option is shorthand; It is the same as Specifying–add-drop-table–add-locks–create-options–disable-keys

–extended-insert–lock-tables–quick–set-charset. It should give you a fast dump operation and produce a dump file that can

Be reloaded to a MySQL server quickly.

The–opt option is enabled by default. Use–skip-opt to disable it. See the discussion at the beginning

Information about selectively enabling or disabling certain of the options affected by–opt.

*–compatible=name

It tells Mysqldump that the exported data will be compatible with which database or which old version of the MySQL server. Values can be ANSI, mysql323, MYSQL40, PostgreSQL, Oracle, MSSQL, DB2, MAXDB, No_key_options, no_tables_options, no_field_options, etc. , to use a few values, separate them with commas. Of course, it is not guaranteed to be fully compatible, but is as compatible as possible.

*–complete-insert,-c

The exported data takes the full INSERT method that contains the field name, that is, all the values are written in one line. This can improve insertion efficiency, but may be affected by the Max_allowed_packet parameter, causing the insert to fail. Therefore, it is prudent to use this parameter, at least I do not recommend it.

*–default-character-set=charset

Specifies which character set to export the data in, and if the data table is not in the default Latin1 character set, you must specify this option when exporting, or you will have garbled problems after importing the data again.

*–disable-keys

Tell mysqldump to add/*!40000 ALTER table Table DISABLE KEYS at the beginning and end of the INSERT statement */; and/*!40000 ALTER table Table ENABLE KEYS */; statement, which can greatly increase the speed of the INSERT statement, because it rebuilds the index after all the data has been inserted. This option is only suitable for MyISAM tables.

*–extended-insert = True|false

By default, mysqldump turns on –complete-insert mode, so if you don't want to use it, use this option to set its value to false.

*–hex-blob

Export binary string fields using hexadecimal format. This option must be used if you have binary data. The field types affected are BINARY, VARBINARY, BLOB.

*–lock-all-tables,-x

Before starting the export, the commit request locks all tables in all databases to ensure data consistency. This is a global read lock, and the –single-transaction and –lock-tables options are turned off automatically.

*–lock-tables

It is similar to –lock-all-tables, but locks the currently exported data table instead of locking down all the tables in the library at once. This option applies only to the MyISAM table, if the Innodb table is available with the –single-transaction option.

*–no-create-info,-t

Exports only data without adding a CREATE TABLE statement.

*–no-data,-d

No data is exported, only the database table structure is exported.

*–opt

This is just a shortcut option, which is equivalent to adding –add-drop-tables–add-locking–create-option–disable-keys–extended-insert–lock-tables–quick– at the same time Set-charset option. This option allows mysqldump to export data quickly, and the exported data can be quickly returned. This option is turned on by default, but can be disabled with –skip-opt. Note that if you run mysqldump without specifying the –quick or –opt option, the entire result set is placed in memory. Problems can occur if you export a large database.

*–quick,-q

This option is useful when exporting large tables, forcing mysqldump to cache records directly from the server query instead of getting all the records back into memory.

*–routines,-r

Export stored procedures and custom functions.

*–single-transaction

This option submits a begin SQL statement before exporting the data, and begin does not block any applications and ensures consistent state of the database at the time of export. It applies only to transaction tables, such as InnoDB and BDB.

This option and the –lock-tables option are mutually exclusive because lock tables causes any pending transactions to be implicitly committed.

To export large tables, you should use the –quick option together.

*–triggers

Export the trigger at the same time. This option is enabled by default and is disabled with –skip-triggers