MySQL备份
为什么要备份
-
能够防止由于机械以及人为误操作带来的数据丢失。
-
冗余：数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。
备份必须重视的内容
-
备份内容databases Binlog my.cnf
-
所有备份数据都应放在非数据库本地，而且建议多份副本
-
测试环境中做日常恢复演练，恢复较备份更为重要。
备份过程中必须考虑因素
-
数据的一致性
-
服务的可用性
1.MySQL备份类型
1.1物理备份
对数据库操作系统的物理文件（如数据文件、日志文件等）的备份。物理备份又可分为脱机备份（冷备份）和联机备份（热备份）。这种类型的备份适用于出现问题时需要快速恢复的大型重要数据库。
1.1.1热备（hot backup）
-
在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日志文件
-
对应用基本无影响（应用程序读写不会阻塞，但是性能还是会有下降，所以尽量不要在主上做备份，在从库上做）
1.1.2冷备（cold backup）
-
备份数据文件，需要停机，是在关闭数据库的时候进行的
-
备份datadir目录下的所有文件
1.1.3温备（warm backup）
-
针对myisam的备份（myisam不支持热备），备份时实例只读不可写，数据库锁定表格（不可写入但可读）的状态下进行的
-
对应用影响很大
-
通常加一个读锁
1.2逻辑备份
对数据库逻辑组件（如表等数据库对象的备份，表示为逻辑数据库结构create database、create table等于语句）和内容（insert语句或分割文本文件）的信息。这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不同机器体系结构上重新创建数据。
1.3物理、逻辑备份的区别
| - | 逻辑备份 | 物理备份 |
|---|---|---|
| 备份方式 | 备份数据库逻辑内容 | 备份数据库物理文件 |
| 优点 | 备份文件相对较小，只备份表中的数据与结构 | 恢复速度比较快（物理文件恢复基本已经完成恢复） |
| 缺点 | 恢复速度较慢（需要重建索引，存储过程等） | 备份文件相对较大（备份表空间，包含数据与索引，碎片） |
| 对业务影响 | 缓冲池污染（把所有数据读一遍，读到bp中），I/O负载加大 | I/O负载加大 |
| 代表工具 | mysqldump | ibbackup、xtrabackup，mysqlbackup |
1.4备份方式的选择
-
备份速度
-
恢复速度
-
备份大小
-
对业务影响
1.5MySQL备份工具
-
ibbackup
-
官方备份工具
-
收费
-
物理备份
-
-
xtrabackup
-
开源社区备份工具
-
开源免费，ibbackup的免费版本（老版本有问题，备份出来的数据可能有问题）
-
物理备份
-
-
mysqldump
-
官方自带备份工具，开源免费
-
逻辑备份（速度慢）
-
不阻塞dml，阻塞ddl
-
-
mysqlbackup
-
mysql官方备份工具
-
innodb引擎的表mysqlbackup可以进行热备
-
非innodb表mysqlbackup就只能温备
-
物理备份，热备还原速度快
-
适合大规模数据使用
-
1.6mysql备份策略
-
完全备份
-
每次对数据进行完整的备份，即对整个数据库的备份、数据库结构和文件结构的备份，保存的是备份完成时的数据库，是差异备份与增量备份的基础。
-
优点：备份与恢复操作简单方便
-
缺点：数据存在大量的重复；占用大量空间；备份与恢复时间长。
-
-
差异备份
-
备份那些自从上次完全备份之后被修改过的所有文件，备份的时间起点是从上次完整备份起，备份数据量会越来越大，恢复数据是，只需恢复上次的完全备份与最近的一次差异备份。
-
-
增量备份
-
只有那些在上次完全备份或增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点，金备份者之间的数据变化，因而备份的数据量小，占用空间小，备份速度快。但恢复是，需要从上一次的完整备份起到最后一次增量备份依次恢复，如中间某次的备份数据损坏，将导致数据的丢失。
-
二进制日志保留天数：全备一次天数的两倍加一（n*2+1）
2.mysqldump逻辑备份
-
mysqldump是MySQL自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。
-
它的备份原理是通过协议连接到MySQL数据库，将需要备份的数据查询出来，将查询出的数据转换成对应的insert语句，当我们需要还原这些数据时，只需执行这些insert语句，即可将对应的数据还原。
-
mysqldump [选项] 数据库名 [表名] > 脚本名
mysqldump [选项] --数据库名 [表名] > 脚本
mysqldump [选项] --all-databases [表名] > 脚本
还原系统命令行
# mysqladmin -uroot -p'xxx' create db_name # mysql -uroot -p'xxx' db_name < /data/db_name.db
source方法
> use db_name; > source /data/db_name.db
备份所有数据
# mysqldump -uroot -p'xxx' -A > /data/mysql-all.sql # mysql -p > show databases; > drop database binlog; > drop database gtid; > drop database test; # mysql -uroot -p'xxx' < /data/mysql-all.sql 备份是没有添加--set-gtid-purged=OFF会出现事物冲突 # mysql -p > reset master; 主从关系上禁止使用，否则主从关系会崩溃！！！
备份指定数据库
# mysqldump -uroot -p'xxx' gtid --set-gtid-purged=OFF > /data/mysql-gtid.sql # mysql -uroot -p > drop database gtid; # mysql -uroot -p'xxx' < /data/mysql-gtid.sql # mysql -uroot -p > source /data/mysql-gtid.sql
备份指定数据库排除某些表
# mysqldump -uroot -p'xxx' gtid --ignore-table=gtid.t1 --ignore-table=gtid.t2 --set-gtid-purged=OFF > /data/mysql-gtid.sql
-
再导入备份数据库前，db_name如果没有，是需要创建的；而且与db_name.db中数据库名是一样的才可以导入
备份指定表
# mysqldump -uroot -p'xxx' gtid t1 --set-gtid-purged=OFF > /data/mysql-gtid-table.sql # mysql -uroot -p > drop table gtid.t1; > source /data/mysql-gtid-table.sql
3.mysqldump全量备份
环境：centos7，5.7.28 yum
3.1修改配置文件开启二进制日志
# vim /etc/my.cnf [mysqld] server-id=2 log-bin=/var/log//mysql/bin-log # mkdir -p /var/log/mysql # id mysql # chown -R mysql.mysql /var/log/mysql # ll -d /var/log/mysql # mkdir -p /backup/mysql # chown -R mysql.mysql /backup/mysql # systemctl restart mysqld
3.2进行全量备份
# mysqldump -uroot -hlocalhost -p'xxx' -P3306 --all-databases --triggers --routines --events --single-transaction --master-data=1 --flush-logs --set-gtid-purged=OFF > /backup/mysql/$(date +%F%H)-mysql-all.sql
3.3全量还原
模拟数据库数据丢失
# systemctl stop mysqld # rm -rf /var/lib/mysql/* # systemctl restart mysqld //日志里找回root密码 # grep 'password' /var/log/mysqld.log # mysql -uroot -p'xxxx' > alter user 'root'@'localhost' identified by 'A.123com'; > show databases;
还原
# sed -i '23a SET sql_log_bin=0;' /backup/mysql/2020-03-1313-mysql-all.sql # mysql -uroot -p'A.123com' < /backup/mysql/2020-03-1313-mysql-all.sql # mysql -uroot -p'xxxx' > set sql_log_bin=1;
或
# mysql -uroot -p'xxxx' > set sql_log_bin=0; > source /backup/mysql/2020-03-1313-mysql-all.sql > set sql_log_bin=1;
-
导入后当前的密码不变，当进入数据库flush privileges之后，密码恢复到备份时的密码
> flush privileges;
3.4增量备份
-
备份与恢复环境
-
数据库完整备份+数据库增量备份
-
新建数据表，进行全量备份，随时间推移，数据库突然崩溃
全量备份之后新增的数据依赖二进制日志恢复
实例：
创建数据
# mysql -uroot -p'xxxx' > create database test; > create table test.t1(id int,name varchar(32)); > insert into test.t1 values(1,'test1'),(2,'test2'); //进行一次全量备份（备份了2行数据） # mysqldump -uroot -hlocalhost -p'xxx' -P3306 --all-databases --triggers --routines --events --single-transaction --master-data=1 --flush-logs --set-gtid-purged=OFF > /backup/mysql/$(date +%F%H)-mysql2-all.sql # mysql -uroot -p'xxxx' > insert into test.t1 values(3,'test3'),(4,'test4'); t1表共4行数据
模拟数据丢失并找回密码
# rm -rf /var/lib/mysql/* # systemctl start mysqld # grep 'password' /var/log/mysqld.log # mysql -uroot -p'xxxx' > alter user 'root'@'localhost' identified by 'A.123com';
恢复全量备份的数据
# cd /backup/mysql # sed -i '23a SET sql_log_bin=0;' /backup/mysql/2020-03-1314-mysql2-all.sql # mysql -uroot -p'A.123com' < /backup/mysql/2020-03-1314-mysql2-all.sql # mysql -uroot -p'A.123com' > select * from test.t1; 当前只恢复了2行数据
恢复剩下没有备份的数据
# sed -n '22p' /backup/mysql/2020-03-1314-mysql2-all.sql ...MASTER_LOG_FILE='bin-log.000005',MASTER_LOG_POS=154 # ls /var/log/mysql 1..4 bin-log.000005 bin-log.000006 bin-log.000007 # mysqlbinlog --start-position=154 /var/log/mysql/bin-log.000005 /var/log/mysql/bin-log.000006 /var/log/mysql/bin-log.000007 |mysql -uroot -p'A.123com'
4.xtrabackup物理备份
Xtrabackup是一个开源的免费的热备工具，在Xtrabackup包中主要有Xtrabackup和innobackupex两个工具。其中Xtrabackup只能备份InnoDB和XtraDB两种引擎；innobackupex则是封装了Xtrabackup，同时增加了备份MylSAM引擎的功能。
Xtrabackup备份时不能备份表结构、触发器等等，也不能只能区分.idb数据文件。另外innobackupex还不能完全支持增量备份，需要和xtrabackup结合起来实现全备功能。
4.1xtrabackup安装
-
xtrabackup下载地址https://www.percona.com/downloads/
-
依赖包下载，下载地址http://rpmfind.net/linux/atrpms/el6-x86_64/atrpms/stable/libev-4.04-2.el6.x86_64.rpm
安装
# cd # tar xf Percona-XtraBackup-2.4.22-rc99a781-el7-x86_64-bundle.tar # yum localinstall -y libev-4.04-2.el6.x86_64.rpm # yum install -y percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
配置文件
# vim /etc/my.cnf [mysqld] datadir=/var/lib/mysql //xtrabackup根据目录获取需要备份的文件 # systemctl restart mysqld
4.2xtrabackup使用
一般使用innobackupex脚本，因为innobackupex是perl脚本对xtrabackup的封装和功能扩展
4.2.1用户权限说明
备份数据库时会涉及两个用户：系统用户与数据库内部的用户
一、系统用户
-
需要在datadir（配置文件内设置的目录）上具有读写执行权限（rwx）
二、数据库内部用户
-
RELOAD和LOCK TABLES权限，执行FLUSH TABLES WITH READ LOCK;
-
REPLICATION CLIENT权限，获取binary log（二进制日志文件）位置；
-
CREATE TABLESPACE权限，导入表，用户表级别的恢复；
-
SUPER权限，在slave环境下备份用来启动和关闭slave线程。
4.2.2格式和参数
innobackupex [参数] [目的地址] [源地址]
--user --password --port --stream 打包(数据流) --defaults-file 指定默认配置文件，默认读取/etc/my.cnf --no-timestamp 不创建时间戳文件，而改用目的地址（可以自动创建） --copy-back 备份还原的主要选项 --incremental 使用增量备份，默认使用完整备份 --incremental-basedir= 与--incremental选项联合使用，改参数指定上一级备份的地址来做增量备份 --incremental-dir= 还原时指定增量备份路径 --apply-log 对xtrabackup的--prepare参数的封装 --redo-only --apply-log组, 强制备份日志时只redo ,跳过rollback。这在做增量备份时非常必要。 对于全备份和增量备份（出最后一个增备外） 都需要加 redo-only ; （中间的增备如果 rollback ，那增备之间可以无法衔接起来）
4.3xtrabackup完整备份
4.3.1备份
# innobackupex --user=root --password=A.123com /backup/mysql //有大量输出备份信息 # innobackupex --user=root --password=A.123com /backup/mysql 2>>/backup/mysql/backup.log //将备份输出信息保存到文件 # innobackupex --user=root --password=A.123com --no-timestamp /backup/mysql/test 2>>/backup/mysql/backup-test.log //不创建时间戳文件，使用指定文件名 # ls /backup/mysql 2020-01-01_11-45-30 backup.log # ls 2020-01-01_11-45-30 # cat xtrabackup_checkpoints backup_type = full-backuped //全备 from_lsn = 0 //初始 to_lsn = 3971256 //备份到 last_lsn = 3971265 //最后序列值 compact = 0 recover_binlog_info = 0 flushed_lsn = 3971265 //刷新值
4.3.2还原
-
innobackupex --copy-back不会覆盖已存在的文件。而且还原时需要先关闭服务，如果服务是启动的，那么就不能还原到datadir
# innobackupex --user=root --password=A.123com /backup/mysql 2>>/backup/mysql/backup.log # systemctl stop mysqld # rm -rf /var/lib/mysql/* # innobackupex --copy-back /backup/mysql/2020-01-01_11-45-30/ 2>>/backup/mysql/copyback.log # ls /var/lib/mysql # chown -R mysql:mysql /var/lib/mysql //不改属主，无法重启mysql # systemctl start mysqld
4.4xtrabackup增量备份
-
增量备份的实现，依赖于innodb页上面的LSN（log sequence number），每次对数据库的修改都会导致LSN自增。增量备份会复制指定LSN<日志序列号>之后的所有数据页。
4.4.1查看完整备份的LSN
# innobackupex --user=root --password=A.123com /backup/mysql # cat xtrabackup_checkpoints backup_type = full-backuped //全备 from_lsn = 0 //初始 to_lsn = 3971256 //备份到 last_lsn = 3971265 //最后序列值 compact = 0 recover_binlog_info = 0 flushed_lsn = 3971265 //刷新值
4.4.2以全备创建增量备份
先做一次全备
//全备 # innobackupex --user=root --password=A.123com /backup/mysql # cd /backup/mysql # cd 2020-01-01_11-45-30/ # cat xtrabackup_checkpoints backup_type = full-backuped //全备 from_lsn = 0 //初始 to_lsn = 2630193 //备份到 last_lsn = 2630202 //最后序列值 compact = 0 recover_binlog_info = 0 flushed_lsn = 2630202 //刷新序列值
增量备份
//新增数据 > create database test_db; > create table t1(id int,name varchar(32)); > use test_db; > insert into t1 values(1,'zhangsan'); > desc test_db; //增量备份 > innobackupex --user=root --password=A.123com --incremental /backup/mysql/ --incremental-basedir=/backup/mysql/2020-01-01_11-45-30 > ls /backup/mysql 2020-01-01_11-45-30 2020-01-01_11-50-25 > cd 2020-01-01_11-50-25 > cat xtrabackup_checkpoints backup_type = incremental //全备 from_lsn = 2630193 //初始 to_lsn = 2634822 //备份到 last_lsn = 2634831 //最后序列值 compact = 0 recover_binlog_info = 0 flushed_lsn = 2634831 //刷新序列值
4.4.3增量还原
> cd /backup/mysql > ls 2020-01-01_11-45-30 2020-01-01_11-50-25
恢复数据准备全量备份
# innobackupex --apply-log --redo-only /backup/mysql/2020-01-01_11-45-30/ //需要日志在加上 2>>/backup/mysql/copyback.log
应用第一次增量备份到全量备份
//增备合入到全备 # innobackupex --apply-log --redo-only /backup/mysql/2020-01-01_11-45-30/ --incremental-dir=/backup/mysql/2020-01-01_11-50-25 # cd /backup/mysql/2020-01-01_11-45-30/ # cat xtrabackup_checkpoints backup_type = log-applied from_lsn = 0 to_lsn = 2634822 last_lsn = 2634831 compact = 0 recover_binlog_info = 0 flushed_lsn = 2634831
-
对比之前查看的第一次增量备份的last_lsn位置，在应用第一次增量备份到全量后，可以看到last_lsn已经被应用和第一次全量备份的位置相同了
# systemctl stop mysqld # rm -rf /var/lib/mysql # ls /var/lib/mysql //恢复数据 # innobackupex --copy-back/mysql/2020-01-01_11-45-30/ # ls /var/lib/mysql # chown -R mysql:mysql /var/lib/mysql # systemctl start mysqld # mysql -p'A.123com' > show database test_db; > use test_db; > show tables; > select * from t1;
-
进行数据备份是，必须参数--apply-log --redo-only先合并全备数据目录数据，确保全备数据目录数据的一致性；
-
再将增备数据使用--inremental-dir合并到全备数据当中；
-
最后通过全备数据进行恢复数据，多个增备需逐一合并到全备数据中，在进行恢复。
5.xrabackup数据流压缩
-
-stream
-
使用-stream时，会输出打包的数据流，并不会直接生成打包文件，此时需要使用重定向或其他命令对数据流进行处理。
-
5.1使用重定向生成压缩文件
-
将标准输出重定向为tar文件，将标准错误重定向到日志文件
# innobackupex --databases=test_db -user=root -password=A.123com --stream=tar /backup/mysql/ > /backup/mysql/`date +%F`.tar 2> /backup/mysql/backup.log # ls /backup/mysql/ 2020-01-01.tar backup.log # cd /backup/mysql/ # mkdir test_db.bak //压缩时指定压缩目录，压缩中没有归档目录 # tar xf 2020-01-01.tar -C ./test_db.bak # ls
5.2使用ssh和cat组合命令，直接备份到其他服务器上
# ssh-keygen # ssh-copy-id 192.168.1.2 # ssh root@192.168.1.2 "mkdir /backup/mysql" # innobackupex --databases=test_db --user=root --password=A.123com --stream=tar 2> /backup/mysql/backup.log |ssh root@192.168.1.2 "cat - > /backup/mysql/`date +%F`.tar" # ssh 192.168.1.2 # ls /backup/mysql # mkdir /backup/mysql/test_db.bak # tar xf 2020-01-02.tar -C ./test_db.bak # ls /backup/mysql/test_db.bak # exit
5.3使用gzip压缩一下
# rm -rf /backup/mysql/* # innobackupex --databases=test_db --user=root --pA.123com --stream=tar /backup/mysql/ 2>/backup/mysql/backup.log |gzip > /backup/mysql/`date +%F`.tar.gz # ls /backup/mysql/ 2020-01-03.tar.gz backup.log # mkdir /backup/mysql/test_db.bak # tar zxf /backup/mysql/2020-01-03.tar.gz -c /backup/mysql/test_db.bak # ls /backup/mysql/test_db.bak