第二十三天：mysql数据备份及还原

天忍

一、备份类型
完全备份，部分备份　　完全备份：整个数据集　　部分备份：只备份数据子集，如部分库或表完全备份、增量备份、差异备份　　增量备份：仅备份最近一次完全备份或增量备份（如果存在增量）以来变化的数据，备份较快，还原复杂　　差异备份：仅备份最近一次完全备份以来变化的数据，备份较慢，还原简单冷、温、热备份　　冷备：读、写操作均不可进行，数据库停止服务　　温备：读操作可执行；但写操作不可执行　　热备：读、写操作均可执行物理和逻辑备份　　物理备份：直接复制数据文件进行备份，与存储引擎有关，占用较多的空间，速度快　　逻辑备份：从数据库中"导出"数据另存而进行的备份，与存储引擎无关，占用空间少，速度慢，可能丢失精度1、备份什么　　数据　　二进制日志、InnoDB的事务日志　　用户帐号，权限设置，程序代码（存储过程、函数、触发器、事件调度器）　　服务器的配置文件2、备份工具cp, tar等复制归档工具：物理备份工具，适用所有存储引擎；只支持冷备；完全和部分备份LVM的快照：先加读锁，做快照后解锁，几乎热备；借助文件系统工具进行备份mysqldump：逻辑备份工具，适用所有存储引擎，对MyISAM存储引擎进行温备；支持完全或部分备份；对InnoDB存储引擎支持热备，结合binlog的增量备份xtrabackup：由Percona提供支持对InnoDB做热备(物理备份)的工具，支持完全备份、增量备份MariaDB Backup： 从MariaDB 10.1.26开始集成，基于Percona XtraBackup 2.3.8实现mysqlbackup：热备份， MySQL Enterprise Edition 组件mysqlhotcopy：PERL 语言实现，几乎冷备，仅适用于MyISAM存储引擎，使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库 二、mysqldump 备份工具1、mysqldump 说明
逻辑备份工具：mysqldump, mydumper, phpMyAdminSchema和数据存储在一起、巨大的SQL语句、单个巨大的备份文件mysqldump是MySQL的客户端命令，通过mysql协议连接至mysql服务器进行备份命令格式:

1. mysqldump [OPTIONS] database [tables]   #支持指定数据库和指定多表的备份，但数据库本身定义不备份
2. mysqldump [OPTIONS] -B DB1 [DB2 DB3...] #支持指定数据库备份，包含数据库本身定义也会备份
3. mysqldump [OPTIONS] -A [OPTIONS]        #备份所有数据库，包含数据库本身定义也会备份

复制代码

2、mysqldump 常见通用选项：

1. -u, --user=name     User for login if not current user
2. -p, --password[=name]  Password to use when connecting to server
3. -A, --all-databases #备份所有数据库，含create database
4. -B, --databases db_name…  #指定备份的数据库，包括create database语句
5. -E, --events：#备份相关的所有event scheduler
6. -R, --routines：#备份所有存储过程和自定义函数
7. --triggers：#备份表相关触发器，默认启用,用--skip-triggers，不备份触发器
8. --default-character-set=utf8 #指定字符集
9. --master-data[=#]：#注意：MySQL8.0.26版以后，此选项变为--source-data
10. #此选项须启用二进制日志
11. #1：所备份的数据之前加一条记录为CHANGE MASTER TO语句，非注释，不指定#，默认为1，适合于主从复
12. 制多机使用
13. #2：记录为被注释的#CHANGE MASTER TO语句，适合于单机使用,适用于备份还原
14. #此选项会自动关闭--lock-tables功能，自动打开-x | --lock-all-tables功能（除非开启--
15. single-transaction）
16. -F, --flush-logs #备份前滚动日志，锁定表完成后，执行flush logs命令,生成新的二进制日志文件，配合-A 或 -B 选项时，会导致刷新多次数据库。建议在同一时刻执行转储和日志刷新，可通过和--
17. single-transaction或-x，--master-data 一起使用实现，此时只刷新一次二进制日志
18. --compact        #去掉注释，适合调试，节约备份占用的空间,生产不使用
19. -d, --no-data    #只备份表结构,不备份数据,即只备份create table
20. -t, --no-create-info #只备份数据,不备份表结构,即不备份create table
21. -n,--no-create-db #不备份create database，可被-A或-B覆盖
22. --flush-privileges #备份mysql或相关时需要使用
23. -f, --force       #忽略SQL错误，继续执行
24. --hex-blob        #使用十六进制符号转储二进制列，当有包括BINARY,VARBINARY,BLOB，BIT的数据类型的列时使用，避免乱码
25. -q, --quick     #不缓存查询，直接输出，加快备份速度

复制代码

mysqldump的MyISAM存储引擎相关的备份选项：
MyISAM不支持事务，只能支持温备；不支持热备，所以必须先锁定要备份的库，而后启动备份操作

1. -x,--lock-all-tables #加全局读锁，锁定所有库的所有表，同时加--single-transaction或--lock-tables选项会关闭此选项功能，注意：数据量大时，可能会导致长时间无法并发访问数据库
2. -l,--lock-tables #对于需要备份的每个数据库，在启动备份之前分别锁定其所有表，默认为on,--skip-lock-tables选项可禁用,对备份MyISAM的多个库,可能会造成数据不一致
3. #注：以上选项对InnoDB表一样生效，实现温备，但不推荐使用

复制代码

mysqldump的InnoDB存储引擎相关的备份选项：InnoDB 存储引擎支持事务,可以利用事务的相应的隔离级别,实现热备，也可以实现温备但不建议用

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

复制代码

3、生产环境实战备份策略
InnoDB建议备份策略

1. mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --masterdata=2 --flush-privileges --default-character-set=utf8 --hex-blob
2. >${BACKUP}/fullbak_${BACKUP_TIME}.sql
3. #新版8.0.26以上
4. mysqldump -uroot -p123456 -A -F -E -R --triggers --single-transaction --sourcedata=2 --flush-privileges --default-character-set=utf8 --hex-blob
5. >${BACKUP}/fullbak_${BACKUP_TIME}.sq

复制代码

4、特定数据库的备份脚本

1. [root@centos8 ~]#cat mysql_backup.sh
2. #!/bin/bash
3. TIME=`date +%F_%H-%M-%S`
4. DIR=/backup
5. DB=hellodb
6. PASS=magedu
7. [ -d $DIR ] || mkdir $DIR
8. mysqldump -uroot -p "$PASS" -F -E -R --triggers  --single-transaction --masterdata=2 --default-character-set=utf8 -q  -B $DB | gzip >
9. ${DIR}/${DB}_${TIME}.sql.gz

复制代码

5、分库备份并压缩

 6、分库备份的实战脚本

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

复制代码

7、利用二进制日志，还原数据库最新状态

1. #二进制日志独立存放
2. [mysqld]
3. log-bin=/data/mysql/mysql-bin
4. #完全备份，并记录备份的二进制位置
5. mysqldump  -uroot -pmagedu -A -F --default-character-set=utf8  --singletransaction --master-data=2 | gzip > /backup/all_`date +%F`.sql.gz
6. #修改数据库
7. insert students (name,age,gender)value('mage',20,'M');
8. insert students (name,age,gender)value('wang',22,'M');
9. #损坏数据库
10. rm -rf /var/lib/mysql/*
11. #还原
12. cd /backup
13. gzip -d all_2019-11-25.sql.gz
14. #CentOS 8 需要事先生成数据库相关文件，CentOS7 不需要执行此步
15. mysql_install_db  --user=mysql

复制代码

8、mysqldump 和二进制日志结合实现差异(增量)备份 

1. [root@centos8 ~]#mysqldump -uroot -p -A -F --single-transaction --master-data=2
2. |gzip > /backup/all-`date +%F`.sql.gz
3. #观察上面备份文件中记录的二进制文件和位置，定期将其之后生成的所有二进制日志进行复制备份
4. [root@centos8 ~]#cp /var/lib/mysql/mariadb-bin.000003 /backup #假设mariadbbin.000003是后续生成的二进制日志
5. [root@centos8 ~]#mysqlbinlog backup/mariadb-bin.000003 > /backup/inc.sql

复制代码

 
 
三、xtrabackup 备份工具
1、xtrabackup 工具介绍
Percona 公司官网：www.percona.com　　　　percona-server　　　　InnoDB --> XtraDBXtrabackup备份工具　　percona提供的mysql数据库备份工具，惟一开源的能够对innodb和xtradb数据库进行热备的工具　　手册：https://www.percona.com/doc/percona-xtrabackup/LATEST/index.html　　下载: https://www.percona.com/downloads/ xtrabackup 特点：　　备份还原过程快速、可靠　　备份过程不会打断正在执行的事务　　能够基于压缩等功能节约磁盘空间和流量　　自动实现备份检验　　开源，免费xtrabackup工具文件组成　　Xtrabackup2.2 版之前包括4个可执行文件：　　　　innobackupex: Perl 脚本　　　　xtrabackup: C/C++，编译的二进制程序　　　　xbcrypt: 加解密　　　　xbstream: 支持并发写的流文件格式 说明:xtrabackup 是用来备份 InnoDB 表的，不能备份非 InnoDB 表，和 MySQL Server 没有交互innobackupex 脚本用来备份非 InnoDB 表，同时会调用 xtrabackup 命令来备份 InnoDB 表，还会和MySQL Server 发送命令进行交互，如加全局读锁（FTWRL）、获取位点（SHOW SLAVE STATUS）等。即innobackupex是在 xtrabackup 之上做了一层封装实现的备份过程：

 
备份生成的相关文件使用innobackupex备份时，其会调用xtrabackup备份所有的InnoDB表，复制所有关于表结构定义的相关文件(.frm)、以及MyISAM、MERGE、CSV和ARCHIVE表的相关文件，同时还会备份触发器和数据库配置信息相关的文件。这些文件会被保存至一个以时间命名的目录中,在备份时，innobackupex还会在备份目录中创建如下文件：xtrabackup_info：文本文件，innobackupex工具执行时的相关信息，包括版本，备份选项，备份时长，备份LSN(log sequence number日志序列号)，BINLOG的位置xtrabackup_checkpoints：文本文件，备份类型（如完全或增量）、备份状态（如是否已经为prepared状态）和LSN范围信息,每个InnoDB页(通常为16k大小)都会包含一个日志序列号LSN。LSN是整个数据库系统的系统版本号，每个页面相关的LSN能够表明此页面最近是如何发生改变的xtrabackup_binlog_info：文本文件，MySQL服务器当前正在使用的二进制日志文件及至备份这一刻为止二进制日志事件的位置，可利用实现基于binlog的恢复backup-my.cnf：文本文件，备份命令用到的配置选项信息xtrabackup_logfile：备份生成的二进制日志文件 2、xtrabackup 安装在EPEL源中 

1. yum install percona-xtrabackup

复制代码

3、xtrabackup 用法
xtrabackup工具备份和还原，需要三步实现1. 备份：对数据库做完全或增量备份2. 预准备： 还原前，先对备份的数据，整理至一个临时目录3. 还原：将整理好的数据，复制回数据库目录中 xtrabackup 选项参考：https://www.percona.com/doc/percona-xtrabackup/LATEST/genindex.html备份： 

1. innobackupex [option] BACKUP-ROOT-DIR

复制代码

 Prepare预准备：

1. innobackupex --apply-log [option] BACKUP-DIR

复制代码

选项说明： 

1. --apply-log：#一般情况下,在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚
2. 未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。此选项作
3. 用是通过回滚未提交的事务及同步已经提交的事务至数据文件使数据文件处于一致性状态
4. --use-memory：#和--apply-log选项一起使用，当prepare 备份时，做crash recovery分配的内存
5. 大小，单位字节，也可1MB,1M,1G,1GB等，推荐1G
6. --export：#表示开启可导出单独的表之后再导入其他Mysql中
7. --redo-only：#此选项在prepare base full backup，往其中合并增量备份时候使用，但不包括对最
8. 后一个增量备份的合并

复制代码

还原： 

1. innobackupex --copy-back [选项] BACKUP-DIR
2. innobackupex --move-back [选项] [--defaults-group=GROUP-NAME] BACKUP-DIR

复制代码

选项说明： 

 还原注意事项：
1. datadir 目录必须为空。除非指定innobackupex --force-non-empty-directorires选项指定，否则--copy-back选项不会覆盖2. 在restore之前,必须shutdown MySQL实例，不能将一个运行中的实例restore到datadir目录中3. 由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户, 执行chown -R mysql:mysql /data/mysql,以上需要在用户调用innobackupex之前完成 4、利用 xtrabackup 实现完全备份及还原

 5、利用xtrabackup8.0 完全，增量备份及还原MySQL8.0

 

 

 6、xtrabackup单表导出和导入

 

 
 

来源:https://www.cnblogs.com/dujy/p/18014349
免责声明：由于采集信息均来自互联网，如果侵犯了您的权益，请联系我们【E-Mail:cb@itdo.tech】 我们会及时删除侵权内容，谢谢合作！