mysql8学习手册第七部分备份

第七章：Backups

• Taking backups using mysqldump
• Taking backups using mysqlpump
• Taking backups using mydumper
• Taking backups using flat files
• Taking backups using XtraBackup
• Locking instances for backup
• Binary log backup

介绍：备份主要分为两种方式，一种是逻辑备份，通过重新执行一系列的sql来实现存储数据
另一种是物理备份：所有的文件都重新存储在另一个仓库中。

逻辑备份：mysqldump mysqlpump mydumper
物理备份：xtraBackup and flat files

Mysqldump:

具体参考：https://blog.csdn.net/qq942477618/article/details/18554257
备份所有用户

备份所有表：

mysqldump -uroot -p  --all-databases >dump_all.sql;

备份某指定数据库
mysqldump -uroot -p --databases databasename >databasename_backup.sql
如：
mysqldump -uroot -p --databases employees >employees_backup.sql

备份某个数表：
mysql -uroot -p --databases databasename --tables tablename >databasename_backup.sql
如：

mysqldump -uroot -p --databases employees --tables employees > employees_backup.sql

备份忽略某张表：

mysql -uroot -p --databasees databases --ignore-table=employees.salary >databasename_backup.sql

如：
mysqldump --databases employees --ignore-table=employees.salary > employees_backup.sql

Specific rows:指定行
例如：

mysqldump   -uroot -p --databases employees --tables employees  --where="hire_date>'2000-01-01'" >employees_after_2000.sql

Backup from a remote server 从远程服务器备份

 mysqldump --all-databases --routines --events
--triggers --hostname <remote_hostname> > dump.sql

只备份基本表结构 不备份数据：

mysqldump --all-databases --routines --events --triggers --no-data > schema.sql

只备份数据，不备份表结构

mysqldump --all-databases --no-create-db --no- create-info --complete-insert > data.sql

Backup users

mysqlpump -uroot -p --exclude-databases=% --users >users_backup.sql

备份所有用户–不包含root

mysqlpump --exclude-databases=% --exclude-users=root --users > users_backup.sql

安装：mydumper
https://blog.csdn.net/yang_hwei/article/details/78834957

mydumper -u root --password=<password> --outputdir /backups

备份单个表：

mydumper -u root --password= -B employees -T employees --triggers --events --routines --outputdir /backup/employee_table

Backup of specific databases using regex 使用正则匹配备份指定数据库

备份 排除mysql 和test两个数据库
mydumper -u root --password= --regex ‘^(?!(mysql|test))’ --outputdir /backups/specific_dbs

Taking backup of a big table using mydumper 使用mydumper备份大表

To speed up the dump and restore of a big table, you can split it into small chunks. The chunk size can be specified by the number of rows it contains and each chunk will be written into a separate file:

mydumper -uroot --password= -B employees -T employees --triggers --events --routines --rows=1000 -t 8 --trx-consistency-only --outputdir /backups/employees_table_chunks

Non-blocking backup 不锁表备份
To provide consistent backup, mydumper acquires GLOBAL LOCK by executing FLUSH TABLES WITH READ LOCK 为了保持一致性备份，mydumper会进行表级锁，使用FLUSH TABLES WITH READ LOCK

Compressed backups 压缩备份

mydumper -u root --password=Zja1540774503_ -B employees -T employees -t 8 --trx-consistency-only --compress --outputdir /backups/employees_compress

Backing up only data 只备份数据

You can use the --no-schemas option to skip the schema and take a data-only backup:

mydumper -u root --password=<password> -B employees -T employees -t 8 --no-schemas --compress --trx-consistency-only --outputdir /backups/employees_data