4.MySQL备份和恢复-摩杜云开发者社区

备份类型

•  完全备份，部分备份

•  完全备份，增量备份，差异备份

增量备份：仅备份最近一次完全备份或增量备份(如果存在增量)以来变化的数据,备份较快, 还原复杂

差异备份：仅备份最近一次完全备份以来变化的数据,备份较慢,还原简单

冷,温,热备份 
冷备：读,写操作均不可进行,数据库停止服务 
温备：读操作可执行；但写操作不可执行 
热备：读,写操作均可执行  
MyISAM：温备,不支持热备  
InnoDB：都支持 

物理和逻辑备份 
物理备份：直接复制数据文件进行备份,与存储引擎有关,占用较多的空间,速度快 
逻辑备份：从数据库中"导出"数据另存而进行的备份,与存储引擎无关,占用空间少,速度慢,可能丢失精度

备份工具

• cp, tar等复制归档工具：物理备份工具,适用所有存储引擎；只支持冷备；完全和部分备份 
• LVM的快照：先加读锁,做快照后解锁,几乎热备；借助文件系统工具进行备份
• mysqldump：逻辑备份工具,适用所有存储引擎,对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备,结合binlog的增量备份 
• xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具,支持完全备份,增量备份 
• MariaDB Backup：从MariaDB 10.1.26开始集成,基PerconaX traBackup2.3.8实现 
• mysqlbackup：热备份, MySQL Enterprise Edition 组件 
• mysqlhotcopy：PERL语言实现,几乎冷备,仅适用于MyISAM存储引擎,使用LOCK TABLES, FLUSH TABLES和cp或scp来快速备份数据库

备份的内容

数据 
二进制日志,InnoDB的事务日志 
用户帐号,权限设置,程序代码(存储过程,函数,触发器,事件调度器) 
服务器的配置文件

冷备份
[root@centos8 ~]#systemctl stop mysqld
[root@centos8 ~]#tar zcf mysql-backup.tar.gz /var/lib/mysql/
[root@centos8 ~]#scp mysql-backup.tar.gz 10.0.0.100:

还原（在备份的机器上还原）
[root@centos8 ~]#systemctl stop mysqld
[root@centos8 ~]#rm -rf /data/mysql/*
[root@centos8 ~]#tar xf mysql-backup.tar.gz -C /opt/
[root@centos8 ~]#mv /opt/var/lib/mysql/* /data/mysql/

guk验证
生成密钥：ssh-keygen
拷贝到：ssh-copy-id 10.0.0.100
这样脚本就可以执行，计划任务备份到10.0.0.100

mysqldump备份工具
-A 备份全部数据库
-B 相当于databases,后面跟库名，可以备份多个数据库
-u 用户
-p 密码
[09:40:59 root@rocky8 ~]# mysqldump -uroot -B db1  > /backup/mysql-B.sql
mysql -uroot < / /backup/mysql-B.sql
部分数据库备份还原时，需要指定数据库，还原过程中不能让用户访问
[09:42:09 root@rocky8 ~]# mysqldump  -A > /backup/mysql-all.sql
全部备份包含mysql+用户数据库
1.for db in `mysql -uroot -e 'show databases' |grep -Ev '(^Ddatabase|information_schema|performance_schema|sys)$'`;do mysqldump -B $db > /backup/$db-`date +%F`.sql;done
2.[root@centos8 ~]#mysql -uroot -e 'show databases'|grep -Ewv  '^(Database|information_schema|performance_schema|sys)$' | sed -rn 's# (.*)#mysqldump -B \1 | gzip > /backup/\1.sql.gz#p' |bash
将数据库分库备份
--master-data现在已改成--source-data
加此选项，会记录完全备份之后二进制日志起点位置
-F  或    flush logs;  
生成新的二进制日志文件
CHANGE MASTER TO 
所备份的数据之前加此记录，默认为1，适用于主从复制的多机使用
--compect    #去掉注释,适合调试,节约备份占用的空间,生产不使用
-d         #只备份表结构,不备份数据,即只备份create table
-t         #只备份数据,不备份表结构,即不备份create table
--hex-blob  #十六进制

mysqldump的MyISAM存储引擎相关的备份选项：
-x         #加全局读锁,锁定所有库的所有表,同时加--single-transaction或-- lock-tables选项会关闭此选项功能,注意：数据量大时,可能会导致长时间无法并发访问数据库
-l         #对于需要备份的每个数据库,在启动备份之前分别锁定其所有表,默认为on,-- skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致

mysqldump的InnoDB存储引擎相关的备份选项：
--single-transaction
#此选项Innodb中推荐使用,不适用MyISAM,此选项会开始备份前,先执行START TRANSACTION指令开启事务 
#此选项通过在单个事务中转储所有表来创建一致的快照. 仅适用于存储在支持多版本控制的存储引擎中的表 (目前只有InnoDB可以); 转储不保证与其他存储引擎保持一致. 在进行单事务转储时,要确保有效的转储 文件(正确的表内容和二进制日志位置),没有其他连接应该使用以下语句：ALTER TABLE,DROP TABLE, RENAME TABLE,TRUNCATE TABLE,此选项和--lock-tables(此选项隐含提交挂起的事务)选项是相互 排斥,备份大型表时,建议将--single-transaction选项和--quick结合一起使

InnoDB建议备份策略
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --masterdata=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql
新版8.0.26以上
mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --sourcedata=2 --flush-privileges --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

MyISAM建议备份策略
mysqldump -uroot -p123456 -A -F -E -R -x --master-data=1 --flush-privileges -- triggers --default-character-set=utf8 --hex-blob >${BACKUP}/fullbak_${BACKUP_TIME}.sql

分库备份脚本
[root@centos8 ~]#cat backup_db.sh  
#!/bin/bash 
TIME=`date +%F_%H-%M-%S` 
DIR=/backup 
PASS=magedu 
[ -d "$DIR" ] || mkdir $DIR 
for DB in `mysql -uroot -p "$PASS" -e 'show databases' | grep -Ewv  "^Database|.*schema$"`;do  
mysqldump -F --single-transaction --master-data=2 --default-characterset=utf8 -q -B $DB | gzip >  ${DIR}/${DB}_${TIME}.sql.gz done