一 、数据库备份概述
1、数据库备份类型
从物理与逻辑的角度 | |
---|---|
冷备份 | 在数据库关闭状态下进行备份操作 |
热备份 | 在数据库处于运行状态时进行备份操作,该备份方法依赖数据库的日志文件 |
温备份 | 数据库锁定表格(不可写入但可读)的状态下进行备份操作 |
从数据库的备份策略角度 | |
---|---|
完全备份 | 每次对数据进行完整的备份,即对整个数据库的备份、数据库结构和文件结构的备份 |
差异备份 | 备份那些自从上次完全备份之后被修改过的所有文件,备份的时间节点是从上次完整备份起 |
增量备份 | 只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。以上次完整备份或上次的增量备份的时间为时间点,仅备份这之间的数据变化 |
MySQL中进行不同方式的备份还要考虑存储引擎是否支持
MyISAM | InnoDB | |
---|---|---|
热备 | × | √ |
温备 | √ | √ |
冷备 | √ | √ |
我们在考虑完数据在备份时, 数据库的运行状态之后还需要考虑对于MySQL数据库中数据的备份方式
物理备份一般就是通过tar,cp等命令直接打包复制数据库的数据文件达到备份的效果
逻辑备份一般就是通过特定工具从数据库中导出数据并另存备份(逻辑备份会丢失数据精度)
- 物理备份
- 逻辑备份
2、备份需要考虑的问题
一般情况下, 我们需要备份的数据分为以下几种
- 数据
- 二进制日志,InnoDB事务日志
- 代码(存储过程、存储函数、触发器、事件调度器)
- 服务器配置文件
3、备份工具
这里我们列举出常用的几种备份工具
- mysqldump:逻辑备份工具, 适用于所有的存储引擎,支持温备、完全备份、部分备份、对于InnoDB存储引擎支持热备
- xtrabackup:一款非常强大的InnoDB/XtraDB热备工具,支持完全备份、增量备份,由percona提供
4、设计合适的备份策略
针对不同的场景下, 我们应该制定不同的备份策略对数据库进行备份, 一般情况下, 备份策略一般为以下三种
- 直接cp,tar复制数据库文件
- mysqldump+复制BIN LOGS
- xtrabackup
以上的几种解决方案分别针对于不同的场景
- 如果数据量较小,可以使用第一种方式,直接复制数据库文件
- 如果数据量还行,可以使用第二种方式,先使用mysqldump对数据库进行完全备份,然后定期备份BINARY LOG达到增量备份的效果
- 如果数据量很大,而又不过分影响业务运行,可以使用第三种方式,使用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-purged | OFF,可以使用在日常备份参数;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.6 | MySQL 5.7 | MySQL 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、所需的权限和特权
数据库用户需要对要备份的表/数据库具有以下权限:
RELOAD
和LOCK TABLES
(除非 -no-lock指定选项)以便开始复制文件,并且FLUSH TABLES WITH READ LOCK
在使用备份锁时需要此权限。FLUSH ENGINE LOGS
,LOCK TABLES FOR BACKUP
,LOCK BINLOG FOR BACKUP
REPLICATION CLIENT
以获得二进制日志位置。CREATE TABLESPACE
为了导入表。PROCESS
为了运行SHOW ENGINE INNODB STATUS
(这是强制性的),并且可以选择查看在服务器上运行的所有线程。SUPER
为了在复制环境中启动/停止副本线程,请使用XtraDB Changed Page Tracking for Incremental Backups和Improved FLUSH TABLES WITH READ LOCK handling。CREATE
权限以创建 PERCONA_SCHEMA.xtrabackup_history数据库和表。ALTER
特权以升级 PERCONA_SCHEMA.xtrabackup_history数据库和表。INSERT
特权,以便将历史记录添加到 PERCONA_SCHEMA.xtrabackup_history表。SELECT
特权以便使用innobackupex --incremental-history-name
或innobackupex --incremental-history-uuid
为了功能查找PERCONA_SCHEMA.xtrabackup_historyinnodb_to_lsn
表中的值 。
创建具有完整备份所需的最低权限的数据库用户的 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'
评论区