MySQL备份

2021-10-07 15:13:05

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/
- 选择Percona XtraBackup版本https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/Percona-XtraBackup-2.4.22-rc99a781-el7-x86_64-bundle.tar
- 或只下一个https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.22/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.22-1.el7.x86_64.rpm
依赖包下载，下载地址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

码农公寓

MySQL备份

1.MySQL备份类型

1.1物理备份

1.1.1热备（hot backup）

1.1.2冷备（cold backup）

1.1.3温备（warm backup）

1.2逻辑备份

1.3物理、逻辑备份的区别

1.4备份方式的选择

1.5MySQL备份工具

1.6mysql备份策略

2.mysqldump逻辑备份

3.mysqldump全量备份

3.1修改配置文件开启二进制日志

3.2进行全量备份

3.3全量还原

3.4增量备份

4.xtrabackup物理备份

4.1xtrabackup安装

4.2xtrabackup使用

4.2.1用户权限说明

4.2.2格式和参数

4.3xtrabackup完整备份

4.3.1备份

4.3.2还原

4.4xtrabackup增量备份

4.4.1查看完整备份的LSN

4.4.2以全备创建增量备份

4.4.3增量还原

5.xrabackup数据流压缩

5.1使用重定向生成压缩文件

5.2使用ssh和cat组合命令，直接备份到其他服务器上

5.3使用gzip压缩一下

相关文章