MySQL:备份恢复

1.逻辑备份(mysqldump)

备份单表

#使用gzip进行压缩
mysqldump -u -p -h --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=OFF -q -f  dbname tablename|gzip >dbname_dump_$(date +%Y%m%d%H%M).sql,gz

#恢复时使用
zcat dbname_dump_$(date +%Y%m%d%H%M).sql.gz |mysql -u -p -h --default-character-set=utf8mb4 

备份单个库

mysqldump -u -p -h --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=OFF -f -q -R --triggers -B dbname  >dbname_dump_$(date +%Y%m%d%H%M).sql

mysqldump -u -p -h --default-character-set=utf8mb4 --master-data=2 --single-transaction --set-gtid-purged=OFF -f -q -R --triggers -B aaa|gzip>库名_dump_$(date +%Y%m%d%H%M).sql.gz

单独备份存储过程

mysqldump -h -u -p --default-character-set=utf8mb4 -d -t -R dbname >dbname_dump_$(date +%Y%m%d%H%M).sql.gz

备份表结构

#有建库语句
mysqldump -h -u -p --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF  -q -R --triggers -d -B dbname >dbname_dump_$(date +%Y%m%d%H%M).sql 
#无建库语句
mysqldump -h -u -p --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF  -q -R --triggers -d  dbname >dbname_dump_$(date +%Y%m%d%H%M).sql

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

mysqldump -h -u -p --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF -q -R --triggers -t dbname >dbname_dump_$(date +%Y%m%d%H%M)}.sql

加条件备份

mysqldump  -h1.1.1.1 -u -p --default-character-set=utf8mb4 --single-transaction --set-gtid-purged=OFF  -q dbname table --where="colname between '2022-08-01' and '2022-09-30'" >dbname_table_col_dump.sql

全参数

mysqldump --login-path=bak  --routines --events --triggers --single-transaction --databases ${DBNAME}  -q  --log-error=/data/mysqlbackup/mysqlbackup_${date +%Y%m%d%H%M}.err --result-file=/data/mysqlbackup/${DBNAME}_dump_${date +%Y%m%d%H%M}.sql

2.物理备份(xtrabackup)

全量备份 备份时进行压缩和打包至本地

xtrabackup  --backup --default-file=/etc/mysql/3306.cnf  --host='127.0.0.1'  --port=3306  --user=test  --password='pwd' --stream=xbstream --compress --compress-threads=6 --parallel=6  --target-dir=/db/temp/bak_xtrabackup_test/ 2>>3306.log 1>127.0.0.1_3306.stream 

单表备份

xtrabackup --backup --default-file=/etc/mysql/3306.cnf --host='127.0.0.1' --port=3306  --user=test --password='pwd' --stream=xbstream --compress --compress-threads=6 --parallel=6  --tables=db.tb1,db.tb2 --target-dir=/db/mysql_backup 2>3306.log 1>127.0.0.1.stream  &

解包备份

mkdir -p /db/recover/3306
nohup  xbstream  --parallel=6 -v -x  < ./127.0.0.1_3306.stream -C /db/recover/3306 > ./import_3306.log  2>&1 &

解压数据文件

nohup xtrabackup --decompress --default-file=/etc/mysql/3306.cnf  --parallel=6  --target-dir=/db/recover/3306  > decompress_3306_$(date +%Y%m%d-%H%M).log 2>&1 &

恢复redo日志

nohup xtrabackup --prepare  --default-file=/etc/mysql/3306.cnf  --parallel=6  --target-dir=/db/recover/3306  > prepare_3306_$(date +%Y%m%d-%H%M).log 2>&1 &

拷贝至数据目录

nohup xtrabackup  --default-file=/etc/mysql/3306.cnf --move-back  --target-dir=/db/temp/20231218/3306  --datadir=/db/3306/data > move-back_3306_$(date +%Y%m%d-%H%M).log 2>&1 &

如果数据库配置了多个undo文件还需要另外拷贝
mv /db/recover/3306/{undo001,undo002,undo003} /db/3306/data/

从全备中恢复单表

前提必须开启innodb_file_per_table

按照上述的流程操作到解压数据文件（假如恢复db.test表）

mysql> set FOREIGN_KEY_CHECKS=0;  
mysql> ALTER TABLE test DISCARD TABLESPACE;
shell> cp /db/recover/3306/db/test.ibd /db/3306/data/db/test.ibd
mysql> ALTER TABLE test IMPORT TABLESPACE;
mysql> set FOREIGN_KEY_CHECKS=1;