Summary of various implementation methods of mysql database backup

This article describes various ways to implement MySQL database backup. Share with you for your reference, the details are as follows:

1. Use mysqldump for backup

1. Completely back up all databases

mysqldump -u root -p --all-databases > E:/all.sql

Prior to MySQL 8, stored procedures and events were stored in the mysql.proc and mysql.event tables.

Starting with MySQL 8, the definitions of the corresponding objects are stored in the data dictionary, and these tables are not backed up.

To include stored procedures and events, use the following statement:

mysqldump -u root -p --all-databases --routines --events > E:/all.sql

2. Point-in-time recovery

To obtain point-in-time recovery, you should specify --single-transaction and --master-data

--single-transaction Before the backup, the transaction isolation level is set to REPEATABLE READ mode and START TRANSACTION is executed to provide a consistent backup.

--master-data Output the location of the server's binary log to a sql file.

mysqldump -u root -p --all-databases --routines --events --single-transaction --master-data > E:/all.sql

--master-data = 2 means that during the export process, the binlog and POS points of the current library are recorded and this line is commented in the export file.

--master-data = 1 means that during the export process, the binlog and POS points of the current library are recorded, and this line is not commented in the export file.

3. When exporting from the library, record the binary log position of the main library

mysqldump -u root -p --all-databases --routines --events --single-transaction --dump-slave > E:/all.sql

--dump-slave = 2 means that during the export process, the binlog and POS points of the master database are recorded and this line is commented in the export file.

--dump-slave = 1 means that during the export process, the binlog and POS points of the master database are recorded, and this line is not commented in the export file.

4. Specify database and table export

mysqldump -u root -p --databases database> E:/bak.sql
mysqldump -u root -p --databases database --tables data table> E:/bak.sql

5. Ignore table

mysqldump -u root -p --databases database --ignore-table=database.datatable> E:/bak.sql

6. Specify rows

mysqldump -u root -p --databases database --tables data table --where="condition" > E:/bak.sql

Or use limit to limit the result set

mysqldump -u root -p --databases database --tables data table --where="Condition LIMIT number of entries" > E:/bak.sql

7. Export remote server

mysqldump -u root -p -h host IP --all-databases --routines --events --triggers > E:/all.sql

8. Backup for merging data with other servers

mysqldump -u root -p --databases database --skip-add-drop-table --replace > E:/bak.sql

--skip-add-drop-table: Do not write the drop table statement to the export file.

--replace: will use replace into statement instead of insert statement to export.

2. Use mysqlpump for backup

1. Parallel processing, speed up the backup process by specifying the number of threads

mysqlpump --default-parallelism=8 > E:/all.sql

2. You can also specify the number of threads for each database

mysqlpump -u root -p --parallel-schemas=4:database --default-parallelism=2 > E:/all.sql

3. Exclude or include databases

mysqlpump -u root -p --include-databases=%t > E:/bak.sql

Back up all databases ending with t. Multiple databases are separated by commas. Database names can use % or _ wildcards.

In addition, there are similar --include-events, --include-routines, --include-tables, --include-triggers, --include-users, etc.

mysqlpump -u root -p --exclude-databases=a% > E:/bak.sql

Exclude databases starting with a from backup. Multiple databases are separated by commas. Database names can use % or _ wildcards.

In addition, there are similar to --exclude-events, --exclude-routines, --exclude-tables, --exclude-triggers, --exclude-users, etc.

4. Backup User

mysqlpump -u root -p --exclude-databases=% --users > E:/user.sql

You can exclude certain users via --exclude-users

mysqlpump --exclude-databases=% --exclude-users=root --users > E:/user.sql

5. Compressed backup

By using --compress-output=lz4 or --compress-output=zlib

mysqlpump -u root -p --compress-output=lz4 > E:/all.lz4
mysqlpump -u root -p --compress-output=zlib > E:/all.zlib

Decompress it with the following statement

lz4_decompress E:/all.lz4 all.sql
zlib_decompress E:/all.zlib all.sql

3. Use mydumper for backup

mydumper needs to be installed separately, official website: https://github.com/maxbube/mydumper/releases

1. Full backup

mydumper -u root --password=password --outputdir export path

2. Back up a separate table

mydumper -u root --password=password-B database-T data table --triggers --events --routines --outputdir export path

3. Use regular expressions to back up specific databases

mydumper -u root --password=password --regex '^(?!(mysql|test))' --outputdir export path

Exclude the mysql and test databases from the backup.

4. Back up large tables

mydumper -u root --password=password-B database-T data table --triggers --events --routines --rows=100000 -t 8 --trx-consistency-only --outputdir export path

--rows indicates how many rows to divide the table into

--trx-consistency-only will minimize locking if innodb.

-t specifies the number of threads

5. Compressed backup

mydumper -u root --password=password-B database-T data table-t 8 --trx-consistency-only --compress --outputdir export path

6. Back up only data

Use the --no-schemas option to skip the schema and back up only the data.

mydumper -u root --password=password-B database-T data table-t 8 --no-schemas --compress --trx-consistency-only --outputdir export path

4. Use ordinary files for backup

You can back up the files in the data directory by directly copying them. You need to shut down MySQL first, copy the files, and then start MySQL.

5. Use xtrabackup for backup

https://www.percona.com/downloads/XtraBackup/LATEST/

1. Full backup

xtrabackup --defaults-file=/etc/my.cnf --host=Host IP --user=Username --password=Password --port=Port --backup --parallel=3 --target-dir=Backup Directory

--defaults-file database configuration file

--backup Perform a backup operation

--parallel The number of concurrent threads during backup

--target-dir The directory for backup files

2. Incremental backup

xtrabackup --defaults-file=/etc/my.cnf \
--host=Host IP \
--user=username\
--password=password\
--port=3306 \
--backup \
--parallel=3 \
--target-dir=Incremental backup directory\
--incremental-basedir=full backup directory\

Incremental backup is based on full backup, --incremental-basedir points to the full backup directory

Readers who are interested in more MySQL-related content can check out the following topics on this site: "Summary of MySQL Index Operation Skills", "Summary of MySQL Common Functions", "Summary of MySQL Log Operation Skills", "Summary of MySQL Transaction Operation Skills", "Summary of MySQL Stored Procedure Skills" and "Summary of MySQL Database Lock-Related Skills".

I hope this article will be helpful to everyone's MySQL database design.