目 录CONTENT

文章目录

MySQL备份与恢复

简中仙
2020-10-16 / 0 评论 / 0 点赞 / 77 阅读 / 0 字 / 正在检测是否收录...
温馨提示:
本文最后更新于2024-03-13,若内容或图片失效,请留言反馈。 本文如有错误或者侵权的地方,欢迎您批评指正!

一 、数据库备份概述

1、数据库备份类型

从物理与逻辑的角度
冷备份在数据库关闭状态下进行备份操作
热备份在数据库处于运行状态时进行备份操作,该备份方法依赖数据库的日志文件
温备份数据库锁定表格(不可写入但可读)的状态下进行备份操作
从数据库的备份策略角度
完全备份每次对数据进行完整的备份,即对整个数据库的备份、数据库结构和文件结构的备份
差异备份备份那些自从上次完全备份之后被修改过的所有文件,备份的时间节点是从上次完整备份起
增量备份只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点,仅备份这之间的数据变化

MySQL中进行不同方式的备份还要考虑存储引擎是否支持

MyISAMInnoDB
热备×
温备
冷备

我们在考虑完数据在备份时, 数据库的运行状态之后还需要考虑对于MySQL数据库中数据的备份方式

物理备份一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果

逻辑备份一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)

  • 物理备份
  • 逻辑备份

2、备份需要考虑的问题

一般情况下, 我们需要备份的数据分为以下几种

  • 数据
  • 二进制日志,InnoDB事务日志
  • 代码(存储过程、存储函数、触发器、事件调度器)
  • 服务器配置文件

3、备份工具

这里我们列举出常用的几种备份工具

  • mysqldump:逻辑备份工具, 适用于所有的存储引擎,支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备
  • xtrabackup:一款非常强大的InnoDB/XtraDB热备工具,支持完全备份、增量备份,由percona提供

4、设计合适的备份策略

针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下三种

  • 直接cp,tar复制数据库文件
  • mysqldump+复制BIN LOGS
  • xtrabackup

以上的几种解决方案分别针对于不同的场景

  1. 如果数据量较小,可以使用第一种方式,直接复制数据库文件
  2. 如果数据量还行,可以使用第二种方式,先使用mysqldump对数据库进行完全备份,然后定期备份BINARY LOG达到增量备份的效果
  3. 如果数据量很大,而又不过分影响业务运行,可以使用第三种方式,使用xtrabackup进行完全备份后,定期使用xtrabackup进行增量备份或差异备份

二、冷备份

1、备份数据库

# systemctl stop mysqld
# mkdir /backup
# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
tar: 从成员名中删除开头的“/”
# ls -l /backup/
总用量 732
-rw-r--r-- 1 root root 747366 Jun  8 18:10 mysql_all-2020-06-08.tar.gz

2、恢复数据库

# mkdir /restore
# tar zxf /backup/mysql_all-2020-06-08.tar.gz -C /restore/
# mv /restore/usr/local/mysql/data/ /usr/local/mysql/
# systemctl start mysqld

三、逻辑备份(mysqldump)

1、特点

优点不需要下载安装,备份出来的是SQL,文本格式,可读性高,便于备份处理,压缩比较高,节省备份的磁盘空间
缺点依赖于数据库引擎,需要从磁盘把数据读出,转换成SQL进行转储,比较耗费资源,数据量大的话效率较低
建议100G以内的数据量级,可以使用mysqldump

2、连接MySQL客户端

本地备份:

# mysqldump -uroot -p -S /tmp/mysql.sock

远程备份:

# mysqldump -uroot -p -h 10.0.0.51 -P3306
参数说明参数说明
-u连接到服务器时使用的MySQL用户名-p连接服务器时使用的密码
-S指定连接mysql的socket文件位置-h从给定主机上的MySQL服务器转储数据。默认主机是localhost
-P用于连接的TCP / IP端口号

3、备份数据库

1、备份指定库中的部分表

不加参数直接指定库名+表名表示导出指定库下的表,1库+1表或多表(表间空格隔开)

mysqldump [选项]库名[表名1][表名2] … > /备份路径/备份文件名

# 将mysql库中的user表导出为mysql-user.sql
# mysqldump -u root -p mysql user > mysql-user.sql

2、备份一个或多个完整的库(含表)

mysqldump [选项]--databases 库名1 [库名2]…>/备份路径/备份文件名

# 将整个auth库导出为auth.sql文件
# mysqldump -u root -p --databases auth > auth.sql
# 将world,oldguo,wordpress三个库导出为db.sql文件
# mysqldump -uroot -p1 -B world oldguo wordpress >/backup/db.sql

3、备份MySQL服务器中所有的库。

mysqldump [选项]--all-databases > /备份路径/备份文件名

创建备份文件full.sql,其中包括 MySQL服务器中的所有库

# mysqldump -u root -p --all-databases > all-data.sql
# mysqldump -uroot -p1 -A  >/backup/full.sql
参数说明
--all-databases/-A导出全部数据库
--databases/-B导出几个数据库。参数后面所有名字参量都被看作数据库名

4、其他备份参数

# mysqldump -uroot -p -A -R -E --triggers >/data/backup/full.sql
参数说明
-R存储过程和函数
-E事件
--triggers触发器
# mysqldump -uroot -p  -A  -R --triggers -F >/bak/full.sql
参数说明
-F在备份开始时,刷新一个新binlog日志
# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF >/data/backup/full.sql
# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=ON >/data/backup/full.sql
# mysqldump -uroot -p -A -R -E --triggers --master-data=2 --single-transaction --set-gtid-purged=OFF --max-allowed-packet=256M >/data/backup/full.sql
参数说明
--master-data=2记录备份时刻的binlog信息,自动锁表
--single-transaction对于InnoDB的表,进行一致性快照备份,不锁表;不加--single-transaction是温备份,加了--single-transaction对于InnoDB表不锁表备份(快照备份)
--set-gtid-purgedOFF,可以使用在日常备份参数;auto、on,在构建主从复制环境时需要的参数配置
--max-allowed-packet服务器发送和接受的最大包长度

4、恢复数据库

1、查看备份文件

# grep -v "^--" backup.sql | grep -v "^/" | grep -v "^$"

2、完全恢复

命令格式:mysql [选项][库名][表名]< /备份路径/备份文件名

备份文件auth.sql恢复auth库

# mysql -u root -p < ~/auth.sql
Enter password: 
# mysql -u root -p -e 'show databases;'
Enter password: 
+--------------------+
| Database           |
+--------------------+
| information_schema |
| auth               |
| mysql              |
| performance_schema |
| test               |
+--------------------+

3、增量恢复

注意:Mysql官方提供的mysqldump命令并不支持增量备份

—般恢复将所有备份的二进制日志内容全部恢复
命令格式mysqlbinlog [--no-defaults] 增量备份文件
基于位置的恢复
格式1恢复数据到指定位置
mysqlbinlog --stop-position='操作id' 二进制日志
格式2从指定的位置开始恢复数据
mysqlbinlog --start-position='操作id' 二进制日志
基于时间点的恢复
格式1从日志开头截止到某个时间点的恢复
mysqlbinlog [--no-defaults] --stop-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 I mysql -u 用户名 -p 密码
格式2从某个时间点到日志结尾的恢复
mysqlbinlog [--no-defaults] --start-datetime=‘年-月-日 小时:分钟:秒’ 二进制日志 I mysql -u 用户名 -p 密码
格式3从某个时间点到某个时间点的恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime=‘年-月-日小时:分钟:秒,二进制日志 I mysql -u 用户名 -p 密码

1、一般恢复

1、开启二进制日志功能

需要注意的是,增量备份需要MySQL的二进制日志(binlog)功能开启。在执行增量备份之前,你需要确保MySQL的binlog功能已经启用,并且已经设置了正确的binlog文件和位置

# vim /etc/my.cnf
[mysqld]
log_bin=/usr/local/mysql/mysql-bin
binlog_format=row
# systemctl restart mysqld

2、先进行一次完全备份

# mkdir /mysql_bak
# mysqldump -uroot -p client user_info > /mysql_bak/client_userinfo-$(date +%F).sql
# 将内存中log日志写磁盘,保存在当前binlog文件中,并产生一个新的binlog日志文件。
# mysqladmin -uroot -p flush-logs
# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 167 Jun  8 19:02 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 120 Jun  8 19:02 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql  68 Jun  8 19:02 /usr/local/mysql/mysql-bin.index

3、继续录入新的数据并进行增量备份

# mysql -uroot -p
mysql> use client;

mysql> insert into user_info values('000009','zhaoliu','nan','019','37');

mysql> insert into user_info values('000010','sunqi','nan','020','36');

mysql> select * from user_info;
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000009    | zhaoliu  | nan    | 019         |     37 |
| 000010    | sunqi    | nan    | 020         |     36 |
+-----------+----------+--------+-------------+--------+

4、刷新二进制日志

# mysqladmin -uroot -p flush-logs 
# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 167 Jun  8 19:02 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 679 Jun  8 19:05 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql 120 Jun  8 19:05 /usr/local/mysql/mysql-bin.000003
-rw-rw---- 1 mysql mysql 102 Jun  8 19:05 /usr/local/mysql/mysql-bin.index

模拟误操作删除user_info表

# mysql -uroot -p -e 'drop table client.user_info;'
Enter password: 
# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

5、恢复操作

恢复完全备份

# mysql -uroot -p client < /mysql_bak/client_userinfo-2020-06-08.sql 
Enter password: 
# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
+-----------+----------+--------+-------------+--------+

恢复增量备份

# cp /usr/local/mysql/mysql-bin.000002 /mysql_bak/
# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002 | mysql -u root -p
Enter password: 
# mysql -uroot -p -e 'select * from client.user_info;'Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000009    | zhaoliu  | nan    | 019         |     37 |
| 000010    | sunqi    | nan    | 020         |     36 |
+-----------+----------+--------+-------------+--------+

2、基于位置恢复

1、模拟误操作删除user_info表

# mysql -uroot -p -e 'drop table client.user_info;'Enter password: 
# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist

恢复完全备份

# mysql -uroot -p client < /mysql_bak/client_userinfo-2020-06-08.sql Enter password: 
# mysql -uroot -p -e 'select * from client.user_info;'
Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
+-----------+----------+--------+-------------+--------+

查看二进制日志文件的具体内容

# mysqlbinlog --no-defaults /mysql_bak/mysql-bin.000002
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
/*!40019 SET @@session.max_insert_delayed_threads=0*/;
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
DELIMITER /*!*/;
# at 4
#200608 19:02:44 server id 1  end_log_pos 120 CRC32 0x529ba9a5 	Start: binlog v 4, server v 5.6.36-log created 200608 19:02:44
BINLOG '
1BreXg8BAAAAdAAAAHgAAAAAAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAaWp
m1I=
'/*!*/;
# at 120
#200608 19:04:15 server id 1  end_log_pos 203 CRC32 0x6bab226a 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1591614255/*!*/;
SET @@session.pseudo_thread_id=3/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1075838976/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8 *//*!*/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
BEGIN
/*!*/;
# at 203
#200608 19:04:15 server id 1  end_log_pos 346 CRC32 0xae3036de 	Query	thread_id=3	exec_time=0	error_code=0
use `client`/*!*/;
SET TIMESTAMP=1591614255/*!*/;
insert into user_info values('000009','zhaoliu','nan','019','37')
/*!*/;
# at 346
#200608 19:04:15 server id 1  end_log_pos 377 CRC32 0xeb5a7bce 	Xid = 33
COMMIT/*!*/;
# at 377
#200608 19:04:32 server id 1  end_log_pos 460 CRC32 0x57bd8218 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1591614272/*!*/;
BEGIN
/*!*/;
# at 460                      //在操作ID为"460"的时候,user_info表中插入了 "sunqi"的用户数据
#200608 19:04:32 server id 1  end_log_pos 601 CRC32 0xcfb81de2 	Query	thread_id=3	exec_time=0	error_code=0
SET TIMESTAMP=1591614272/*!*/;
insert into user_info values('000010','sunqi','nan','020','36')
/*!*/;
# at 601
#200608 19:04:32 server id 1  end_log_pos 632 CRC32 0x4b0d9c26 	Xid = 34
COMMIT/*!*/;
# at 632
#200608 19:05:41 server id 1  end_log_pos 679 CRC32 0x86716eb7 	Rotate to mysql-bin.000003  pos: 4
DELIMITER ;
# End of log file
ROLLBACK /* added by mysqlbinlog */;
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;

恢复到操作ID为’'460"之前的数据,即不恢复"孙七"的信息

# mysqlbinlog --no-defaults --stop-position='460' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
# mysql -uroot -p -e 'select * from client.user_info;'Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000009    | zhaoliu  | nan    | 019         |     37 |
+-----------+----------+--------+-------------+--------+

恢复到操作ID为’'460"之后到结束的数据,即仅恢复"孙七"的信息,跳过 "赵六"的信息恢复

# mysqlbinlog --no-defaults --start-position='460' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
# mysql -uroot -p -e 'select * from client.user_info;'Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000010    | sunqi    | nan    | 020         |     36 |
+-----------+----------+--------+-------------+--------+

3、基于时间点恢复

恢复到22:56 : 15之前的数据,即不恢复"孙七"的信息

# mysqlbinlog --no-defaults --stop-datetime='2020-06-08 19:04:32' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
# mysql -uroot -p -e 'select * from client.user_info;'Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000009    | zhaoliu  | nan    | 019         |     37 |
+-----------+----------+--------+-------------+--------+

仅恢复"孙七"的信息,跳过"赵六"的信息恢复

# mysqlbinlog --no-defaults --start-datetime='2020-06-08 19:04:32' /mysql_bak/mysql-bin.000002 | mysql -uroot -p
Enter password: 
# mysql -uroot -p -e 'select * from client.user_info;'Enter password: 
+-----------+----------+--------+-------------+--------+
| 身份证    | 姓名     | 性别   | 用户ID号    | 资费   |
+-----------+----------+--------+-------------+--------+
| 000006    | zhangsan | nan    | 016         |     10 |
| 000007    | lisi     | nv     | 017         |     91 |
| 000008    | wangwu   | nv     | 018         |     23 |
| 000010    | sunqi    | nan    | 020         |     36 |
+-----------+----------+--------+-------------+--------+

四、物理备份(xtrabackup)

Percona XtraBackup是世界上唯一开源、免费的MySQL热备份软件,可为InnoDB和 XtraDB 数据库执行非阻塞备份。使用Percona XtraBackup,您可以获得以下好处:

  • 快速可靠地完成备份
  • 备份期间不间断的事务处理
  • 节省磁盘空间和网络带宽
  • 自动备份验证
  • 由于恢复时间更快,正常运行时间更长

兼容性矩阵

工具
软件
MySQL 5.6MySQL 5.7MySQL 8.0
Percona XtraBackup 2.4检查圈检查圈禁令圈
Percona XtraBackup 8.0禁令圈禁令圈检查圈

1、特点

这是Percona XtraBackup功能的简短列表。有关更多信息,请参阅文档。

  • 在不暂停数据库的情况下创建热 InnoDB 备份
  • 对 MySQL 进行增量备份
  • 将压缩的 MySQL 备份流式传输到另一台服务器
  • 在线在 MySQL 服务器之间移动表
  • 轻松创建新的 MySQL 复制副本
  • 不增加服务器负载的情况下备份MySQL
  • FLUSH TABLES WITH READ LOCK备份锁是Percona Server 5.6+ 中可用的轻量级替代方案。Percona XtraBackup 使用它们自动复制非 InnoDB 数据,以避免阻塞修改 InnoDB 表的 DML 查询。
  • Percona XtraBackup 根据每秒的 IO 操作数执行节流。
  • Percona XtraBackup 跳过二级索引页面并在准备紧凑备份时重新创建它们。
  • Percona XtraBackup 甚至可以从完整备份中导出单个表,而不管 InnoDB 版本如何。
  • 使用 Percona XtraBackup 导出的表可以导入到 Percona Server 5.1、5.5 或 5.6+,或 MySQL 5.6+。
优点类似于直接cp数据文件,不需要管逻辑结构,相对来说性能较高
缺点可读性差,压缩比低,需要更多磁盘空间
建议大于100G小于1TB的数据量级,可以使用
存储引擎对于非Innodb表时,锁表cp数据文件,属于一种温备份;对于Innodb的表(支持事务的),不锁表,拷贝数据页,最终以数据文件的方式保存下来,把一部分redo和undo一并备走,属于热备方式
备份流程备份执行的瞬间立即触发ckpt,已提交的数据脏页从内存刷写到磁盘并记录此时的LSN号;备份时,拷贝磁盘数据页和记录备份过程中产生的redo和undo,即checkpoint LSN之后的日志;在恢复时,xbk会调用InnoDB引擎的CSR过程,将数据和redo的LSN追平,然后进行一致性恢复

2、所需的权限和特权

数据库用户需要对要备份的表/数据库具有以下权限:

创建具有完整备份所需的最低权限的数据库用户的 SQL 示例如下:

mysql> CREATE USER 'bkpuser'@'localhost' IDENTIFIED BY 's3cret';
mysql> GRANT RELOAD, LOCK TABLES, PROCESS, REPLICATION CLIENT ON *.* TO'bkpuser'@'localhost';
mysql> FLUSH PRIVILEGES;

3、下载 Percona-XtraBackup

注:由于新的MySQL重做日志和数据字典格式,8.0版本只支持mysql8.0和percona8.0,早于mysql8.0的版本需要使用xtrabackup2.4备份和恢复。

# yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL libev
# wget https://downloads.percona.com/downloads/Percona-XtraBackup-2.4/Percona-XtraBackup-2.4.27/binary/redhat/7/x86_64/percona-xtrabackup-24-2.4.27-1.el7.x86_64.rpm
# yum -y install percona-xtrabackup-24-2.4.27-1.el7.x86_64.rpm

查看安装情况

[root@localhost download]# rpm -qa|grep xtrabackup
percona-xtrabackup-24-2.4.21-1.el7.x86_64
percona-xtrabackup-test-24-2.4.21-1.el7.x86_64
percona-xtrabackup-24-debuginfo-2.4.21-1.el7.x86_64

查看安装目录

[root@localhost download]#  rpm -ql percona-xtrabackup-24-2.4.21-1.el7.x86_64
/usr/bin/innobackupex #是将xtrabackup进行封装的perl脚本,提供了备份myisam表的能力
/usr/bin/xbcloud
/usr/bin/xbcloud_osenv
/usr/bin/xbcrypt
/usr/bin/xbstream
/usr/bin/xtrabackup #最主要的备份工具,是用于热备 innodb,xtradb表中数据的工具,不能备份其他类型的表,也不能备份数据表结构
/usr/lib64/xtrabackup/plugin/keyring_file.so
/usr/lib64/xtrabackup/plugin/keyring_vault.so
/usr/share/doc/percona-xtrabackup-24-2.4.21
/usr/share/doc/percona-xtrabackup-24-2.4.21/LICENSE
/usr/share/man/man1/innobackupex.1.gz
/usr/share/man/man1/xbcrypt.1.gz
/usr/share/man/man1/xbstream.1.gz
/usr/share/man/man1/xtrabackup.1.gz

如果出现以下问题可以这样解决

Failed to connect to MySQL server: Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2).
# mkdir /var/lib/mysql/
# ln -s /tmp/mysql.sock /var/lib/mysql/mysql.sock

4、创建用于备份的用户

mysql -uroot -pTZWnN3gD7WvwXf2I
mysql>grant reload,lock tables,replication client,create tablespace,process,super on *.* to backupuser@'localhost' identified by 'backup@che123';
#mysql> grant create,insert,select on percona_schema.* to 'backupuser'@'localhost' ;    # 指定库备份
mysql> FLUSH PRIVILEGES;

5、完全备份

# innobackupex --user=root --password=1 /data/backup
# innobackupex --user=root --password=1 --no-timestamp /backup/full
参数说明
--no-timestamp不要创建一个时间戳目录来存储备份
# cd /data/backup/2020-10-16_19-37-19/
# ll
总用量 12340
-rw-r----- 1 root root      487 10月 16 19:37 backup-my.cnf
drwxr-x--- 2 root root       48 10月 16 19:37 binlog
drwxr-x--- 2 root root       20 10月 16 19:37 blog
drwxr-x--- 2 root root       48 10月 16 19:37 gtid
-rw-r----- 1 root root      781 10月 16 19:37 ib_buffer_pool
-rw-r----- 1 root root 12582912 10月 16 19:37 ibdata1
drwxr-x--- 2 root root     4096 10月 16 19:37 mysql
drwxr-x--- 2 root root     8192 10月 16 19:37 performance_schema
drwxr-x--- 2 root root       82 10月 16 19:37 school
drwxr-x--- 2 root root     8192 10月 16 19:37 sys
drwxr-x--- 2 root root       56 10月 16 19:37 test
drwxr-x--- 2 root root      144 10月 16 19:37 world
-rw-r----- 1 root root       62 10月 16 19:37 xtrabackup_binlog_info
-rw-r----- 1 root root      141 10月 16 19:37 xtrabackup_checkpoints
-rw-r----- 1 root root      540 10月 16 19:37 xtrabackup_info
-rw-r----- 1 root root     2560 10月 16 19:37 xtrabackup_logfile
# vim xtrabackup_binlog_info
mysql-bin.000007        194     7d01bcc5-e854-11ea-9c8f-000c29bc0487:1-4
# vim xtrabackup_checkpoints
backup_type = full-backuped
from_lsn = 0            //上次所到达的LSN号
to_lsn = 123959158            //备份开始时间(ckpt)点数据页的LSN
last_lsn = 123959167            //备份结束后,redo日志最终的LSN
compact = 0
recover_binlog_info = 0
flushed_lsn = 123959167   
文件说明
xtrabackup_binlog_info记录备份时刻的二进制日志信息,可以作为binlog截取的起点
xtrabackup_checkpoints备份中包含的LSN号的起点,全备:0,增量:上次备份的结束位置
xtrabackup_info备份信息
xtrabackup_logfile备份过程日志

6、增量备份

注:必须依赖于全备

# innobackupex --user=root --password=1 --no-timestamp --incremental --incremental-basedir=/backup/full /backup/inc1 
参数说明
--incremental开关
--incremental-basedir=/backup/full基于哪个备份进行增量
/backup/inc1增量备份的位置点

7、恢复数据库

1、恢复前的准备

1、整理备份文件

# innobackupex --apply-log --redo-only  /backup/full

2、合并增量备份文件到完全备份文件,并整理备份

# innobackupex --apply-log --redo-only  --incremental-dir=/backup/inc1 /backup/full 
# innobackupex --apply-log  --incremental-dir=/backup/inc2 /backup/full 

3、最后整理备份文件

# innobackupex --apply-log  /backup/full
参数说明
--apply-log通过应用位于同一目录中名为“xtrabackup logfile”的事务日志文件,在backup - dir中准备一个备份。另外,创建新的事务日志
--redo-only强制xtrabackup忽略“rollback”阶段只做“redo”.
--incremental-dir指定增量文件所在的目录备份将与完整备份结合在一起,形成一个完整的备份

2、截取二进制日志

# cat /backup/inc2/xtrabackup_binlog_info
mysql-bin.000031	1997	aa648280-a6a6-11e9-949f-000c294a1b3b:1-17,
e16db3fd-a6e8-11e9-aee9-000c294a1b3b:1-9
# mysqlbinlog /data/binlog/mysql-bin.000031 | grep 'SET'
SET @@SESSION.GTID_NEXT= 'e16db3fd-a6e8-11e9-aee9-000c294a1b3b:12'/*!*/;
# mysqlbinlog --skip-gtids --include-gtids='e16db3fd-a6e8-11e9-aee9-000c294a1b3b:10-12' /data/binlog/mysql-bin.000031>/backup/binlog.sql

3、恢复备份数据

# cp -a  /backup/full/* /data/mysql/data/
# chown -R mysql. /data/
# /etc/init.d/mysqld start
mysql> set sql_log_bin=0;
mysql> source /backup/binlog.sql

五、数据库迁移(5.6升级5.7)

1、5.6 数据库备份

# mysqldump -uroot -p123 -A --master-data=2 --single-transaction -R -E --triggers >/tmp/full.sql
# scp /tmp/full.sql 10.0.0.51:/data/3308 

2、导入5.7数据库

# mysql -S /data/3308/mysql.sock
mysql> source /data/3308/full.sql
# mysql_upgrade -uroot -p123 -S /data/3308/mysql.sock      # 旧版本更新到新版本

3、binlog的持续追加(主从复制)

4、停业务,恢复剩余的binlog

mysql5.6脚本导入5.7版本库中报错解决

explicit_defaults_for_timestamp=1			# 时间格式默认为空报错
sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
default-time-zone = '+8:00'
0

评论区