Mysqldump points and procedures to be aware of for backup and restore

Mysqldump is a logical backup tool that comes with MySQL and can be Win Bei or hot- spares , both of which are backed up in the event that the database service does not stop

For MyISAM can only be Win Bei

• MyISAM does not support transactions, locking is also relatively fast, there is no hot standby mechanism

For InnoDB It is best to use hot backup

• InnoDB support transactions, in the production environment, the use of hot standby lock may take a long time, the transaction to be synchronized from the cache to the log file, the log file is finally synchronized to the database file, even if the lock must wait to finish

• Write lock priority may be greater than read lock

• And if you start a very large business, you may not be able to lock it for a long time.

• If you really want to use hot standby, you have to wait a long time to lock, you can show ENGINE INNODB Status View INNODB write status, the write is completed before the dump

The reason why InnoDB can be hot-spares.

• With the--single-transaction parameter, you can open a large transaction until the end of the backup, and the transaction isolation level defaults to Repeatable-read, so the data is consistent across the entire read process. The data is always in the state at the beginning of the backup when the backup can be done

Process:

General use of mysqldump for full backups, followed by binary log for subsequent incremental backups

For the MyISAM engine

1: Lock Table

• Backup, there may be users are writing data , need to read the lock # FLUSH TABLES with read lock;

2: record binary log location

• What is the current binary log and where the event is located, so that when the instant point of recovery to know from which event after the import to restore, generally first log rolling # FLUSH LOGS; SHOW MASTER STATUS;

3: Start Backup

4: backup finished unlocking # UNLOCK TABLES;

5: Incremental backup

• Copy the binaries directly

You can actually use the parameters directly when using the mysqldump command, without the above 1,2,4 steps

• --lock-all-tables: Lock All Tables

• --master-data=2: record location as change master to, default comment

• --flush-logs: Refresh log

• --all-databases: backing up all libraries

• --databases db_name1,db_name2: backing up multiple libraries

• These are also common parameters

For the InnoDB storage engine

Use the --single-transaction parameter for hot spares without using-lock-all-tables locking

Example: warm backup + Full backup + incremental backup (cycles are subject to availability)

Backup:

1: Full backup

• Mysqldump-uroot-hlocalhost-p--all-databases--lock-all-tables--flush-logs--master-data=2 >/backup/all ' Date +%F '. sql

2: If the backup cycle is one day, incremental backup

• Scroll Log # FLUSH LOGS first;

• Cp-a the binary log of the day directly in the data directory

• Or better use the Mysqlbinlog command redirect # Mysqlbinlog mysql-bin.000015 >/backup/increment ' Date +%f '. sql

3: Backup based on cycle

Restores:

1: The most serious situation is that the database is completely crashing, which requires the database to initialize

• scripts/mysql_install_db--user=mysql--datadir/mysql/data/#利用初始化脚本

2: Full backup prior to import

• Mysql-uroot-hlocalhost-p < All2015-03-22.sql

3: Import Incremental backup

• Mysql-uroot-hlocalhost-p ...

4: Data of the day using binary log instant Point Restore (General binary log files should be stored in different storage for security)

• Mysqlbinlog mysql-bin.000016 | Mysql-uroot-hlocalhost-p

This article is from the "Call Me boxin" blog, so be sure to keep this source http://boxinknown.blog.51cto.com/10435935/1677038

Mysqldump points and procedures to be aware of for backup and restore