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
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
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
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
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
2: Full backup prior to import
3: Import Incremental backup
4: Data of the day using binary log instant Point Restore (General binary log files should be stored in different storage for security)
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