MySQL全备+binlog恢复（含例题）

全备+binlog恢复

使用mysqldump备份所有数据库，并恢复。要求保持数据库的一致性的前提下尽量减少数据库的锁,然后用binlog前滚到指定时间点。
周一到周日，每天晚上22:00都备份了一个全备，某一天（周四）早上10:00数据库down了，怎么恢复到最新的时间点。
思路：周三晚上22:00通过全备恢复，周三晚上22:00-周四早上10:00通过binlog恢复
准备两台mysql服务器，不要是主从服务器。

[root@xianxin opt]# vi /etc/my.cnf 
[client]
port            = 3306
socket          = /tmp/mysql.sock

[mysql]
no-auto-rehash

[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/data
port = 3306
socket = /tmp/mysql.sock
log-error = error.log
slow_query_log_file = slow.log
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
lower_case_table_names =1

server_id=1   #另一台mysql服务器id=2
gtid_mode=on
enforce_gtid_consistency=on
#binlog
log_bin=master-binlog
log-slave-updates=1
binlog_format=row


#relay log
skip-slave-start=1

重启MySQL
[root@xianxin opt]# service mysql restart
Shutting down MySQL… SUCCESS!
Starting MySQL. SUCCESS!

1. 准备数据

mysql> create database cool;
Query OK, 1 row affected (0.10 sec)

mysql> use cool;
Database changed

mysql> create table cool(id int, name varchar(10));
Query OK, 0 rows affected (0.84 sec)

mysql> insert into cool values(1,‘a’),(2,‘b’),(3,‘c’);
Query OK, 3 rows affected (0.64 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> commit;
Query OK, 0 rows affected (0.02 sec)

2. 备份数据库

[root@xianxin opt]# mysqldump -uroot -ptest --set-gtid-purged=OFF --master-data=2 --single-transaction -A > /opt/full_$(date +%F).sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

[root@xianxin opt]# ls
full_2021-05-23.sql full_.sql mysql-5.7.34-linux-glibc2.12-x86_64.tar.gz

3. 新建一个数据库及表

周三晚上十点到周四早上十点模拟新增数据。

mysql> create database t1;
Query OK, 1 row affected (0.03 sec)

mysql> use t1;
Database changed
mysql> create table t1(id int, name varchar(20));
Query OK, 0 rows affected (0.06 sec)

mysql> insert into t1 (id,name) values(1,‘php’),(2,‘java’),(3,‘python’);
Query OK, 3 rows affected (0.04 sec)
Records: 3 Duplicates: 0 Warnings: 0

mysql> select * from t1;
±-----±-------+
| id | name |
±-----±-------+
| 1 | php |
| 2 | java |
| 3 | python |
±-----±-------+
3 rows in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

commit（提交）的作用
在数据库的插入、删除和修改操作时，只有当事务在提交到数据库时才算完成。
在数据库中，在事务提交前，只有操作数据库的这个人才能有权看到所做的事情，别人只有在最后提交完成时才能看见。

4. 模拟故障删库

周四早上十点模拟故障删库t1
mysql> drop database t1;
Query OK, 1 row affected (0.09 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

5. 查找binlog的起点及终点

在故障的mysql服务器上查看周三晚上十点备份的binlog起点
– CHANGE MASTER TO MASTER_LOG_FILE=‘master-binlog.000001’, MASTER_LOG_POS=154;
[root@xianxin opt]# vi /opt/full_2021-05-23.sql

-- MySQL dump 10.13  Distrib 5.7.34, for linux-glibc2.12 (x86_64)
--
-- Host: localhost    Database:
-- ------------------------------------------------------
-- Server version       5.7.34-log

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

--
-- Position to start replication or point-in-time recovery from
--

-- CHANGE MASTER TO MASTER_LOG_FILE='master-binlog.000001', MASTER_LOG_POS=154;

查看binlog终点

mysql> show master status;
+----------------------+----------+--------------+------------------+------------------------------------------+
| File                 | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set                        |
+----------------------+----------+--------------+------------------+------------------------------------------+
| master-binlog.000001 |      912 |              |                  | 66a4eec9-b0bd-11eb-b808-000c29962af4:1-4 |
+----------------------+----------+--------------+------------------+------------------------------------------+
1 row in set (0.00 sec)

mysql> show binlog events in 'master-binlog.000001';
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name             | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+
| master-binlog.000001 |   4 | Format_desc    |         1 |         123 | Server ver: 5.7.34-log, Binlog ver: 4                             |
| master-binlog.000001 | 123 | Previous_gtids |         1 |         154 |                                                                   |
| master-binlog.000001 | 154 | Gtid           |         1 |         219 | SET @@SESSION.GTID_NEXT= '66a4eec9-b0bd-11eb-b808-000c29962af4:1' |
| master-binlog.000001 | 219 | Query          |         1 |         307 | create database t1                                                |
| master-binlog.000001 | 307 | Gtid           |         1 |         372 | SET @@SESSION.GTID_NEXT= '66a4eec9-b0bd-11eb-b808-000c29962af4:2' |
| master-binlog.000001 | 372 | Query          |         1 |         483 | use `t1`; create table t1(id int, name varchar(20))               |
| master-binlog.000001 | 483 | Gtid           |         1 |         548 | SET @@SESSION.GTID_NEXT= '66a4eec9-b0bd-11eb-b808-000c29962af4:3' |
| master-binlog.000001 | 548 | Query          |         1 |         618 | BEGIN                                                             |
| master-binlog.000001 | 618 | Table_map      |         1 |         664 | table_id: 141 (t1.t1)                                             |
| master-binlog.000001 | 664 | Write_rows     |         1 |         730 | table_id: 141 flags: STMT_END_F                                   |
| master-binlog.000001 | 730 | Xid            |         1 |         761 | COMMIT /* xid=461 */                                              |
| master-binlog.000001 | 761 | Gtid           |         1 |         826 | SET @@SESSION.GTID_NEXT= '66a4eec9-b0bd-11eb-b808-000c29962af4:4' |
| master-binlog.000001 | 826 | Query          |         1 |         912 | drop database t1                                                  |
+----------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+

13 rows in set (0.00 sec)
所以可以知道，binlog的起点是154，终点为826

6. 截取binlog起点及终点的日志

[root@xianxin opt]# mysqlbinlog --skip-gtids --start-position=154 --stop-position=826 /data/mysql/data/master-binlog.000001 > /opt/binlog.sql

#按时间截取是如下命令：
#mysqlbinlog slave-binlog.000002 --start-datetime=‘2021-05-19 22:00:00’ --stop-datetime=‘2021-05-19 10:00:00’ -r > /opt/binlog.sql

7. 临时库上恢复数据库到最新时间点

临时库上恢复全备+binlog，将全备文件及截取的binlog文件恢复到临时库上（另外一台临时服务器上）
[root@xianxin opt]# scp /opt/binlog.sql root@192.168.60.10:/opt
The authenticity of host ‘192.168.60.10 (192.168.60.10)’ can’t be established.
ECDSA key fingerprint is SHA256:Rgttz3lGgV5Puj6bFsCWckmHlaIjTT/8Id/Tc6qKyrA.
ECDSA key fingerprint is MD5:e6:ad:67:8b:95:e8:f5:62:72:14:4a:58:87:dc:59:22.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.60.10’ (ECDSA) to the list of known hosts.
root@192.168.60.10’s password:
binlog.sql 100% 2291 1.1MB/s 00:00

[root@xianxin opt]# scp /opt/full_2021-05-23.sql root@192.168.60.10:/opt
root@192.168.60.10’s password:
full_2021-05-23.sql 100% 853KB 5.7MB/s 00:00

[root@aslb opt]# mysql -uroot -ptest

mysql> source /opt/full_2021-05-17.sql;
#现在只是恢复到周三晚上十点。所以并没有周三晚上十点到周四早上十点的数据。即t1数据库。

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| cool |
| mysql |
| performance_schema |
| sys |
±-------------------+

mysql> source /opt/binlog.sql;
#恢复周三晚上十点到周四早上十点新增的数据。

mysql> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| cool |
| mysql |
| performance_schema |
| sys |
| t1 |
±-------------------+
6 rows in set (0.00 sec)

例题：

a.创建一个数据库ceshi
b.在ceshi下创建一张表t1
c.插入5行新数据
d.全备
e.插入两行数据，任意修改3行数据，删除一行数据
f.删除所有设备
g.再t1中又插入五行新数据，修改3行数据
需求，跳过f恢复数据表

思路：查找binlog的起点及终点—截取binlog起点及终点的日志—临时库上恢复数据库到最新时间点