How to use mysqldump for full and point-in-time backups

Mysqldump is used for logical backup in MySQL. Although it is not fast, it is very flexible and has many functions. It is definitely a powerful tool when used flexibly.

First, let's think about a question: why does MySQL need to be backed up? The main reason is data security. For example, if the main database crashes and the data is accidentally deleted, a full backup is very important.

Should full backup be performed from the primary database or the secondary database?

1: From the main library

The main database is more important, but in fact, the backup will not affect the database

mysqldump --host= --user= --password= --single-transaction --master-data=1 --flush-logs --databases >~/db.log

The --single-transaction parameter enables consistent reading without locking the table, which means that data updates are not affected during backup.

it dumps the consistent state of the database at the time when START TRANSACTION was issued without blocking any applications.

Because consistent reading can guarantee the location of the coordinates point, even if the backup time is long, the correct synchronization location point can be obtained.

While a —single-transaction dump is in process, to ensure a valid dump file (correct table contents and binary log coordinates)

The --master-data parameter is also very important. The exported statements will contain the CHANGE MASTER TO statement, including the binary file and location point synchronized to the backup statement.

Use this option to dump a master replication server to produce a dump file that can be used to set upanother server as a slave of the master. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped server. These are the master server coordinates from which the slave should start replicating after you load the dump file into the slave.

--flush-logs will force the regeneration of a new binary file, which will make recovery easier.

2: From the secondary library

It feels safer to back up from the secondary library.

mysqldump --host=--user= --password= --dump-slave=1 --flush-logs --apply-slave-statements --include-master-host-port --databases >~/db.log;

The --dump-slave and --master-data parameters are very similar:

This option is similar to --master-data except that it is used to dump a replication slave server to produce a dump file that can be used to set up another server as a slave that has the same master as the dumped server. It causes the dump output to include a CHANGE MASTER TO statement that indicates the binary log coordinates (file name and position) of the dumped slave's master. These are the master server coordinates from which the slave should start replicating.

Remember that it gets the bin log coordinates of the primary library (not the backup library)

—dump-slave causes the coordinates from the master to be used rather than those of the dumped server

The dumped statement will include — Position to start replication or point-in-time recovery from.

--apply-slave-statements will automatically include the start and stop slave statements in the dump statement. --include-master-host-port includes the connection information of the master library.

One thing must be remembered, even with the --single-transaction statement, --dump-slave will suspend MySQL synchronization, that is, the data in the backup database lags behind the main database, so the general automated script will remove the backup database first when backing up.

This option causes mysqldump to stop the slave SQL thread before the dump and restart it again after.

3: How to perform point-in-time recovery

I haven't tried it in practice. First, restore based on the most recent full backup, and then import subsequent binlog files (if these files are still there), so it is best to back up the binlog statements in the secondary database as well.

If the data is deleted by mistake, import the binlog statements from the backup point (--flush-logs takes effect) to this morning, or find a safe binlog location to restore it. As for how to skip "dangerous statements", it is more difficult to control.

The above is the details of how to use mysqldump for full and point-in-time backup. For more information about mysqldump for full and point-in-time backup, please pay attention to other related articles on 123WORDPRESS.COM!