Database backup-mysqldump

Database backup Type

One, hot backup warm backup cold backup

Hot backup: Data read/write not affected

Warm backup: Can only perform read operations

Cold backup: Offline backup, read and write both terminated

Second, physical backup logical backup

Physical Backup: Copying data files

Logical backup: Export data to a text file

Three, full backup incremental backup differential backup

Full backup: Back up all data

Incremental backup: Backs up only data that has changed from the last full backup or incremental backup

Differential backup: Backs up only data that has changed since the last full backup

mysqldump is a MySQL-backed backup tool that enables warm backups of MyISAM tables and hot backups of InnoDB tables. However, it is very slow to restore, and it is easy to use when backing up small databases. When you use mysqldump warm backup, you should first lock the database so that it can only read operations. The collective steps should be:

1mysql>flush tables with read lock;

2mysqldump Backup new Open a connection for backup, the above lock table is valid only for the current session

3mysql>unlock tables;

These operations, mysqldump command parameters can be completed, mysqldump commonly used command parameters:

#mysqldump-U user-p

--all-databases back up all databases, you don't need to create them before you restore them

--lock-all-tables means locking all tables

--master-data=n

N=0 indicates that the log file location is not logged

N=1 means that the change MASTER to is used to record

N=2 represents a record using change MASTER to, but the default annotation is generally used n=2

--flush-logs performs binary log scrolling for easy backup of binary logs

These are the common parameters,

If the backup table type is InnoDB type, you can use--single-transaction to start the hot spare, and you do not need to--lock-all-tables to lock the table at this time.

This article is from the "Bremen Band" blog, declined reprint!