730 lines
18 KiB
Markdown
730 lines
18 KiB
Markdown
<h2><center>数据备份与恢复</center></h2>
|
||
|
||
------
|
||
|
||
## 1. 简介
|
||
|
||
所有备份数据都应放在非数据库本地，而且建议有多份副本
|
||
|
||
**备份：** 能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方
|
||
|
||
**冗余：** 数据有多份冗余，但不等备份，只能防止机械故障还来的数据丢失，例如主备模式、数据库集群
|
||
|
||
备份考虑的因素：
|
||
|
||
- 数据的一致性
|
||
- 服务的可用性
|
||
|
||
分类一：
|
||
|
||
- 逻辑备份：备份的是建表、建库、插入等操作所执行SQL语句；适用于中小型数据库，效率相对较低（mysqldump）
|
||
- 物理备份：直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本（tar、xtrabackup）
|
||
|
||
分类二：
|
||
|
||
- 热备份：数据库启动同时给客户端提供服务的情况下
|
||
- 冷备份：数据库要关掉或者不能给客户端提供服务
|
||
|
||
备份方案：
|
||
|
||
- 完全备份：备份所有数据
|
||
- 增量备份：每次备份上一次备份到现在产生的新数据
|
||
- 差异备份：只备份跟完整备份不一样的
|
||
|
||
|
||
|
||
## 2. tar 备份
|
||
|
||
注意：
|
||
|
||
备份期间，服务不可用
|
||
|
||
备份过程：完全物理备份
|
||
|
||
停止数据库
|
||
|
||
```bash
|
||
[root@mysql ~]# systemctl stop mysqld
|
||
```
|
||
|
||
tar备份数据
|
||
|
||
```bash
|
||
[root@mysql ~]# mkdir /backup
|
||
[root@mysql ~]# cd /var/lib/mysql
|
||
[root@mysql mysql]# tar -zvcf /backup/`date +%F`-mysql-all.tar.gz ./*
|
||
```
|
||
|
||
启动数据库（备份完成后启动数据库，继续为其他服务提供服务）
|
||
|
||
```bash
|
||
[root@mysql ~]# systemctl start mysqld
|
||
```
|
||
|
||
**恢复过程：模拟数据丢失，恢复数据**
|
||
|
||
停止数据库
|
||
|
||
```bash
|
||
[root@mysql ~]# systemctl stop mysqld
|
||
```
|
||
|
||
清理环境
|
||
|
||
```bash
|
||
[root@mysql ~]# rm -rf /var/lib/mysql/*
|
||
```
|
||
|
||
导入备份数据
|
||
|
||
```bash
|
||
[root@mysql ~]# tar xvf /backup/2025-03-12-mysql-all.tar.gz -C /var/lib/mysql/
|
||
[root@mysql ~]# chown mysql.mysql /var/lib/mysql/* -R
|
||
```
|
||
|
||
启动数据库（恢复后验证数据是否回复成功）
|
||
|
||
```bash
|
||
[root@mysql ~]# systemctl start mysqld
|
||
```
|
||
|
||
## 3. xtrabackup 备份
|
||
|
||
**简介**
|
||
|
||
percona-xtrabackup是开源免费的支持MySQL 数据库热备份的软件；能对InnoDB和XtraDB存储引擎的数据库非阻塞地备份；它不暂停服务创建Innodb热备份；为mysql做增量备份；在mysql服务器之间做在线表迁移；使创建replication更加容易；备份mysql而不增加服务器的负载
|
||
|
||
|
||
|
||
安装软件：
|
||
|
||
```bash
|
||
[root@mysql ~]# wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.7/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm
|
||
[root@mysql ~]# yum -y localinstall percona-xtrabackup-24-2.4.7-1.el6.x86_64.rpm
|
||
```
|
||
|
||
### 完整备份
|
||
|
||
创建备份目录：
|
||
|
||
```bash
|
||
[root@mysql ~]# mkdir -p /xtrabackup/full/
|
||
```
|
||
|
||
备份：
|
||
|
||
```bash
|
||
[root@mysql ~]# innobackupex --user=root --password='Wxin@123456' /xtrabackup/full/
|
||
```
|
||
|
||
查看备份数据：
|
||
|
||
```bash
|
||
[root@mysql ~]# ls /xtrabackup/full/
|
||
2025-03-12_13-14-19
|
||
```
|
||
|
||
模拟数据丢失数据恢复：（以下操作模拟数据丢失）
|
||
|
||
丢失前数据库中的数据：
|
||
|
||
```bash
|
||
[root@mysql ~]# mysql -uroot -p'Wxin@123456'
|
||
mysql> show databases;
|
||
+--------------------+
|
||
| Database |
|
||
+--------------------+
|
||
| information_schema |
|
||
| mysql |
|
||
| performance_schema |
|
||
| sys |
|
||
| test |
|
||
+--------------------+
|
||
5 rows in set (0.00 sec)
|
||
```
|
||
|
||
数据丢失：
|
||
|
||
```bash
|
||
[root@mysql ~]# systemctl stop mysqld
|
||
[root@mysql ~]# rm -rf /var/lib/mysql/*
|
||
[root@mysql ~]# rm -rf /var/log/mysqld.log
|
||
[root@mysql ~]# rm -rf /var/log/mysql-slow/slow.log (有则删除，无则不需要操作)
|
||
```
|
||
|
||
恢复前的验证：
|
||
|
||
```bash
|
||
[root@mysql ~]# innobackupex --apply-log /xtrabackup/full/2025-03-12_13-14-19/
|
||
```
|
||
|
||
恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里
|
||
|
||
```bash
|
||
[root@mysql ~]# cat /etc/my.cnf
|
||
datadir=/var/lib/mysql
|
||
```
|
||
|
||
恢复数据：
|
||
|
||
```bash
|
||
[root@mysql ~]# innobackupex --copy-back /xtrabackup/full/2025-03-12_13-14-19/
|
||
```
|
||
|
||
修改权限：
|
||
|
||
```bash
|
||
[root@mysql ~]# chown mysql.mysql /var/lib/mysql -R
|
||
```
|
||
|
||
启动服务：
|
||
|
||
```bash
|
||
[root@mysql ~]# systemctl start mysqld
|
||
```
|
||
|
||
验证：
|
||
|
||
```SQL
|
||
[root@mysql ~]# mysql -uroot -p'Wxin@123456'
|
||
mysql> show databases;
|
||
+--------------------+
|
||
| Database |
|
||
+--------------------+
|
||
| information_schema |
|
||
| mysql |
|
||
| performance_schema |
|
||
| sys |
|
||
+--------------------+
|
||
4 rows in set (0.00 sec)
|
||
```
|
||
|
||
### 增量备份
|
||
|
||
注意：
|
||
|
||
在进行增量备份前先进行完整备份
|
||
|
||
原理：每次备份上一次备份到现在产生的新数据
|
||
|
||
案例：周一进行全备，周二到周天进行增量备份
|
||
|
||
完整备份：（周一）
|
||
|
||
```bash
|
||
[root@mysql ~]# innobackupex --user=root --password='Wxin@123456' /xtrabackup/full/
|
||
```
|
||
|
||
创建增量备份存放数据目录：
|
||
|
||
```bash
|
||
[root@mysql ~]# mkdir -p /xtrabackup/zeng
|
||
```
|
||
|
||
模拟数据增加：
|
||
|
||
```SQL
|
||
mysql> create database test;
|
||
Query OK, 1 row affected (0.00 sec)
|
||
|
||
mysql> create table test.t1( id int, name varchar(30), age int, sex enum('m','f') );
|
||
Query OK, 0 rows affected (0.01 sec)
|
||
|
||
mysql> insert into test.t1 values(1,'zhangsan',22,'m');
|
||
Query OK, 1 row affected (0.01 sec)
|
||
|
||
mysql> insert into test.t1 values(2,'lisi',22,'f');
|
||
Query OK, 1 row affected (0.00 sec)
|
||
```
|
||
|
||
第一次增量备份：（周二）
|
||
|
||
```bash
|
||
[root@mysql ~]# innobackupex --user=root --password='Wxin@123456' --incremental /xtrabackup/zeng/ --incremental-basedir=/xtrabackup/full/2025-03-12_13-14-19/
|
||
|
||
第一次增量备份的数据：
|
||
[root@mysql ~]# ls /xtrabackup/zeng/
|
||
2025-03-12_13-35-42
|
||
```
|
||
|
||
模拟数据增加：
|
||
|
||
```SQL
|
||
mysql> delete from t1 where id=2;
|
||
Query OK, 1 row affected (0.01 sec)
|
||
```
|
||
|
||
第二次增量备份：（周三）
|
||
|
||
```bash
|
||
[root@mysql ~]# innobackupex --user=root --password='Wxin@123456' --incremental /xtrabackup/zeng/ --incremental-basedir=/xtrabackup/zeng/2025-03-12_13-35-42/
|
||
|
||
第二次增量备份的数据：
|
||
[root@mysql ~]# ls /xtrabackup/zeng/
|
||
2025-03-12_13-35-42 2025-03-12_13-39-06
|
||
```
|
||
|
||
后面的增量备份重复上面的操作（略）
|
||
|
||
增量备份数据恢复流程：（需要模拟数据的丢失）
|
||
|
||
停止数据库：
|
||
|
||
```bash
|
||
[root@mysql ~]# systemctl stop mysqld
|
||
```
|
||
|
||
删除数据：
|
||
|
||
```bash
|
||
[root@mysql ~]# rm -rf /var/lib/mysql/*
|
||
[root@mysql ~]# rm -rf /var/log/mysqld.log
|
||
其他数据根据实际情况删除
|
||
```
|
||
|
||
依次重演回滚：
|
||
|
||
```bash
|
||
全备回滚：
|
||
[root@mysql ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-12_13-14-19/
|
||
|
||
第一次增量回滚：
|
||
[root@mysql ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-12_13-14-19/ --incremental-dir=/xtrabackup/zeng/2025-03-12_13-35-42/
|
||
|
||
第二次增量回滚：
|
||
[root@mysql ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-12_13-14-19/ --incremental-dir=/xtrabackup/zeng/2025-03-12_13-39-06/
|
||
|
||
根据实际增量备份的次数回滚，可以想恢复到那个时间节点就回滚到那个时间节点，所有的回滚都给全备
|
||
```
|
||
|
||
恢复数据：
|
||
|
||
```bash
|
||
[root@mysql ~]# innobackupex --copy-back /xtrabackup/full/2025-03-12_13-14-19/
|
||
```
|
||
|
||
修改权限：
|
||
|
||
```bash
|
||
[root@mysql ~]# chown mysql.mysql /var/lib/mysql -R
|
||
```
|
||
|
||
启动数据库：
|
||
|
||
```bash
|
||
[root@mysql ~]# systemctl start mysqld
|
||
```
|
||
|
||
验证：
|
||
|
||
```SQL
|
||
mysql> show databases;
|
||
+--------------------+
|
||
| Database |
|
||
+--------------------+
|
||
| information_schema |
|
||
| mysql |
|
||
| performance_schema |
|
||
| sys |
|
||
| test |
|
||
+--------------------+
|
||
5 rows in set (0.00 sec)
|
||
|
||
mysql> use test;
|
||
mysql> show tables;
|
||
+----------------+
|
||
| Tables_in_test |
|
||
+----------------+
|
||
| t1 |
|
||
+----------------+
|
||
1 row in set (0.00 sec)
|
||
|
||
mysql> select * from t1;
|
||
+------+----------+------+------+
|
||
| id | name | age | sex |
|
||
+------+----------+------+------+
|
||
| 1 | zhangsan | 22 | m |
|
||
+------+----------+------+------+
|
||
1 row in set (0.00 sec)
|
||
```
|
||
|
||
### 差异备份
|
||
|
||
注意：
|
||
|
||
在进行增量备份前先进行完整备份
|
||
|
||
原理：只备份跟完整备份不一样的
|
||
|
||
案例：周一进行全备，周二到周天进行差异备份
|
||
|
||
完整备份：（周一）
|
||
|
||
```bash
|
||
[root@mysql ~]# mkdir /xtrabackup/full -p
|
||
[root@mysql ~]# innobackupex --use=root --password='Wxin@123456' /xtrabackup/full
|
||
```
|
||
|
||
创建增量备份存放数据目录：
|
||
|
||
```bash
|
||
[root@mysql ~]# mkdir -p /xtrabackup/XY
|
||
```
|
||
|
||
模拟数据增加:
|
||
|
||
```SQL
|
||
mysql> show databases;
|
||
+--------------------+
|
||
| Database |
|
||
+--------------------+
|
||
| information_schema |
|
||
| mysql |
|
||
| performance_schema |
|
||
| sys |
|
||
+--------------------+
|
||
4 rows in set (0.00 sec)
|
||
|
||
mysql> create database test;
|
||
Query OK, 1 row affected (0.00 sec)
|
||
|
||
mysql> create table test.t1(
|
||
-> id int,
|
||
-> name varchar(20)
|
||
-> );
|
||
Query OK, 0 rows affected (0.01 sec)
|
||
```
|
||
|
||
第一次增量备份：（周二）
|
||
|
||
```bash
|
||
[root@mysql ~]# innobackupex --user=root --password='Wxin@123456' --incremental /xtrabackup/XY/ --incremental-basedir=/xtrabackup/full/2025-03-12_14-25-19/
|
||
|
||
第一次增量备份的数据：
|
||
[root@mysql ~]# ls /xtrabackup/XY/
|
||
2025-03-12_14-26-56
|
||
```
|
||
|
||
模拟数据增加：
|
||
|
||
```SQL
|
||
mysql> insert into t1 values(1,'zhangsan');
|
||
Query OK, 1 row affected (0.00 sec)
|
||
|
||
mysql> insert into t1 values(2,'lisi');
|
||
Query OK, 1 row affected (0.00 sec)
|
||
```
|
||
|
||
第二次增量备份：（周三）
|
||
|
||
```bash
|
||
[root@mysql ~]# innobackupex --user=root --password='Wxin@123456' --incremental /xtrabackup/XY/ --incremental-basedir=/xtrabackup/full/2025-03-12_14-25-19/
|
||
|
||
第二次增量备份的数据：
|
||
[root@mysql ~]# ls /xtrabackup/XY/
|
||
2025-03-12_14-26-56 2025-03-12_14-28-16
|
||
|
||
注意：后面的差异备份跟之前一样，根据需求可以继续差异备份
|
||
```
|
||
|
||
差异备份恢复流程：（模拟数据丢失）
|
||
|
||
停止数据库：
|
||
|
||
```bash
|
||
[root@mysql ~]# systemctl stop mysqld
|
||
```
|
||
|
||
删除数据：
|
||
|
||
```bash
|
||
[root@mysql ~]# rm -rf /var/lib/mysql/*
|
||
[root@mysql ~]# rm -rf /var/log/mysqld.log
|
||
```
|
||
|
||
重演数据回滚：
|
||
|
||
```bash
|
||
完整备份回滚：
|
||
[root@mysql ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-12_14-25-19/
|
||
|
||
差异备份回滚（根据差异备份的原理，如果恢复所有数据只需要将最后依次差异回滚）
|
||
[root@mysql ~]# innobackupex --apply-log --redo-only /xtrabackup/full/2025-03-12_14-25-19/ --incremental-dir=/xtrabackup/XY/2025-03-12_14-26-56/
|
||
```
|
||
|
||
恢复数据：
|
||
|
||
```bash
|
||
[root@mysql ~]# innobackupex --copy-back /xtrabackup/full/2025-03-12_14-25-19/
|
||
```
|
||
|
||
修改权限：
|
||
|
||
```bash
|
||
[root@mysql ~]# chown mysql.mysql /var/lib/mysql -R
|
||
```
|
||
|
||
启动数据库：
|
||
|
||
```bash
|
||
[root@mysql ~]# systemctl start mysqld
|
||
```
|
||
|
||
验证：
|
||
|
||
```SQL
|
||
mysql> use test;
|
||
mysql> select * from t1;
|
||
+------+----------+
|
||
| id | name |
|
||
+------+----------+
|
||
| 1 | zhangsan |
|
||
| 2 | lisi |
|
||
+------+----------+
|
||
2 rows in set (0.00 sec)
|
||
```
|
||
|
||
## 4. mysqldump 备份
|
||
|
||
数据库数据：
|
||
|
||
```SQL
|
||
mysql> select * from t1;
|
||
+------+----------+------+------+
|
||
| id | name | sex | age |
|
||
+------+----------+------+------+
|
||
| 1 | zhangsan | f | 22 |
|
||
| 2 | lisi | m | 22 |
|
||
| 3 | wangwu | m | 22 |
|
||
| 4 | liliu | m | 22 |
|
||
| 5 | sanwu | m | 22 |
|
||
+------+----------+------+------+
|
||
5 rows in set (0.00 sec)
|
||
|
||
mysql> select * from t2;
|
||
+------+-------+
|
||
| id | name |
|
||
+------+-------+
|
||
| 1 | name1 |
|
||
| 2 | name2 |
|
||
| 3 | name3 |
|
||
+------+-------+
|
||
3 rows in set (0.01 sec)
|
||
|
||
mysql> select * from t3;
|
||
+------+------+
|
||
| id | age |
|
||
+------+------+
|
||
| 1 | 11 |
|
||
| 2 | 22 |
|
||
| 3 | 33 |
|
||
| 4 | 44 |
|
||
+------+------+
|
||
4 rows in set (0.00 sec)
|
||
```
|
||
|
||
备份表：（前提有库有表）
|
||
|
||
```bash
|
||
[root@mysql ~]# mysqldump -u root -p'Wxin@123456' test t1 > /root/mysql/t1.sql
|
||
```
|
||
|
||
删除表：
|
||
|
||
```SQL
|
||
mysql> drop table test.t1;
|
||
Query OK, 0 rows affected (0.00 sec)
|
||
|
||
mysql> use test;
|
||
mysql> show tables;
|
||
+----------------+
|
||
| Tables_in_test |
|
||
+----------------+
|
||
| t2 |
|
||
| t3 |
|
||
+----------------+
|
||
2 rows in set (0.00 sec)
|
||
```
|
||
|
||
恢复表：（恢复之前模拟数据丢失）
|
||
|
||
```bash
|
||
[root@mysql ~]# mysql -uroot -p'Wxin@123456' test < /root/mysql/t1.sql
|
||
```
|
||
|
||
验证：
|
||
|
||
```SQL
|
||
mysql> show tables;
|
||
+----------------+
|
||
| Tables_in_test |
|
||
+----------------+
|
||
| t1 |
|
||
| t2 |
|
||
| t3 |
|
||
+----------------+
|
||
3 rows in set (0.00 sec)
|
||
```
|
||
|
||
备份一个库：
|
||
|
||
```bash
|
||
[root@mysql ~]# mysqldump -u root -p'Wxin@123456' test > /root/mysql/ku1.sql
|
||
```
|
||
|
||
备份多个库：
|
||
|
||
```bash
|
||
[root@mysql ~]# mysqldump -u root -p'Wxin@123456' -B test test2 test3 > /root/mysql/kuall.sql
|
||
```
|
||
|
||
备份所有库：
|
||
|
||
```bash
|
||
[root@mysql ~]# mysqldump -u root -p'Wxin@123456' -A > /root/mysql/all.sql
|
||
```
|
||
|
||
数据恢复：
|
||
|
||
为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志
|
||
|
||
binlog使用binlog日志恢复数据时也会产生binlog日志（如果开启的话，需要关闭）
|
||
|
||
```bash
|
||
mysql> set sql_log_bin=0;
|
||
Query OK, 0 rows affected (0.00 sec)
|
||
```
|
||
|
||
模拟数据丢失
|
||
|
||
```SQL
|
||
mysql> drop database test;
|
||
Query OK, 3 rows affected (0.01 sec)
|
||
|
||
mysql> drop database test2;
|
||
Query OK, 0 rows affected (0.00 sec)
|
||
|
||
mysql> drop database test3;
|
||
Query OK, 0 rows affected (0.01 sec)
|
||
[root@mysql ~]# mysql -uroot -p'Wxin@123456' -D test < /root/mysql/ku1.sql
|
||
mysql: [Warning] Using a password on the command line interface can be insecure.
|
||
ERROR 1049 (42000): Unknown database 'test'
|
||
出现该错误是因为在恢复的时候需要有库的存在
|
||
|
||
[root@mysql ~]# mysql -uroot -p'Wxin@123456' -e "create database test"
|
||
mysql: [Warning] Using a password on the command line interface can be insecure.
|
||
[root@mysql ~]# mysql -uroot -p'Wxin@123456' -D test < /root/mysql/ku1.sql
|
||
|
||
[root@mysql ~]# mysql -uroot -p'Wxin@123456' -e "create database test"
|
||
[root@mysql ~]# mysql -uroot -p'Wxin@123456' -e "create database test2"
|
||
[root@mysql ~]# mysql -uroot -p'Wxin@123456' -e "create database test3"
|
||
[root@mysql ~]# mysql -uroot -p'Wxin@123456' -D test test2 test3 < /root/mysql/kuall.sql
|
||
|
||
或者
|
||
mysql> source /k1.sql
|
||
```
|
||
|
||
## 5. binlog 日志备份
|
||
|
||
原理：日志方法备份恢复数据
|
||
|
||
日志默认存储位置：
|
||
|
||
rpm ： /var/lib/mysql
|
||
|
||
编译: 安装目录的var下
|
||
|
||
产生日志：
|
||
|
||
方式一：编译安装
|
||
|
||
```bash
|
||
[root@mysql ~]# ./usr/local/mysql/bin/mysqld_safe --log-bin --user=mysql --server-id=1 &
|
||
|
||
查看binlog日志
|
||
[root@mysql ~]# mysqlbinlog slave2-bin.000001 -v --base64-output=decode-rows
|
||
时间点 ： 141126 14:04:49
|
||
位置点 : at 106
|
||
```
|
||
|
||
方式二：rpm安装（永久）
|
||
|
||
```SQL
|
||
[root@mysql ~]# vim /etc/my.cnf
|
||
log-bin=mylog
|
||
server-id=1 //做主从复制使用
|
||
|
||
[root@mysql ~]# systemctl restart mysqld
|
||
|
||
查看：
|
||
[root@mysql ~]# ls /var/lib/mysql
|
||
auto.cnf ib_logfile0 mysql.sock.lock
|
||
ca-key.pem ib_logfile1 performance_schema
|
||
ca.pem ibtmp1 private_key.pem
|
||
client-cert.pem mylog.000001 public_key.pem
|
||
client-key.pem mylog.index server-cert.pem
|
||
ib_buffer_pool mysql server-key.pem
|
||
ibdata1 mysql.sock sys
|
||
|
||
[root@mysql ~]# mysqlbinlog /var/lib/mysql/mylog.000001 -v --base64-output=decode-rows
|
||
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1*/;
|
||
/*!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0*/;
|
||
DELIMITER /*!*/;
|
||
# at 4
|
||
#250312 15:34:10 server id 1 end_log_pos 123 CRC32 0x5f281ad6 Start: binlog v 4, server v 5.7.30-log created 250312 15:34:10 at startup
|
||
# Warning: this binlog is either in use or was not closed properly.
|
||
ROLLBACK/*!*/;
|
||
# at 123
|
||
#250312 15:34:10 server id 1 end_log_pos 154 CRC32 0x014a2356 Previous-GTIDs
|
||
# [empty]
|
||
SET @@SESSION.GTID_NEXT= 'AUTOMATIC' /* added by mysqlbinlog */ /*!*/;
|
||
DELIMITER ;
|
||
# End of log file
|
||
/*!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
|
||
/*!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0*/;
|
||
|
||
方法二：
|
||
mysql> show binlog events in "mylog.000001";
|
||
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
|
||
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
|
||
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
|
||
| mylog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 |
|
||
| mylog.000001 | 123 | Previous_gtids | 1 | 154 | |
|
||
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
|
||
2 rows in set (0.00 sec)
|
||
|
||
默认查看第一个
|
||
mysql> show binlog events;
|
||
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
|
||
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
|
||
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
|
||
| mylog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 |
|
||
| mylog.000001 | 123 | Previous_gtids | 1 | 154 | |
|
||
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
|
||
2 rows in set (0.00 sec)
|
||
```
|
||
|
||
**数据恢复**
|
||
|
||
根据时间点恢复数据：
|
||
|
||
```bash
|
||
[root@mysql ~]# mysqlbinlog --start-datetime='2025-3-12 15:34:10' --stop-datetime='2025-3-12 5:34:10' /var/lib/mysql/mylog.000001 | mysql -u root -pWxin@123456
|
||
```
|
||
|
||
根据位置点恢复数据：
|
||
|
||
```SQL
|
||
mysql> show binlog events;
|
||
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
|
||
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
|
||
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
|
||
| mylog.000001 | 4 | Format_desc | 1 | 123 | Server ver: 5.7.30-log, Binlog ver: 4 |
|
||
| mylog.000001 | 123 | Previous_gtids | 1 | 154 | |
|
||
+--------------+-----+----------------+-----------+-------------+---------------------------------------+
|
||
2 rows in set (0.00 sec)
|
||
|
||
[root@mysql ~]# mysqlbinlog --start-position 123 --stop-position 154 /var/lib/mysql/mylog.000001 | mysql -u root -pWxin@123456
|
||
```
|
||
|