mysql主从（主宕从上）之主从恢复数据不丢失

环境模拟：主从结构、主库故障，从库升级为主库，新主写入数据，之后主从恢复，将主库故障期间新主写入的数据成功导入主库

环境部署

• 系统版本
  CentOS Linux release 7.5.1804 (Core)
• 内核版本
  3.10.0-862.el7.x86_64
• 防火墙与selinux关闭
• 角色分配
  192.168.153.179 master 主数据库
  192.168.153.178 slave 从数据库

开始模拟

首先主从环境基础

slave从：

master主：

模拟主库故障重启

vim /etc/my.cnf
systemctl restart mariadb

slave从：

vim /etc/my.cnf
systemctl restart mariadb

关闭重置slave

MariaDB [(none)]> stop slave;
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> reset slave;
Query OK, 0 rows affected (0.01 sec)

创建库并插入数据

MariaDB [(none)]> create database ceshi charset utf8;
Query OK, 1 row affected (0.00 sec)

MariaDB [(none)]> use ceshi;
Database changed
MariaDB [ceshi]> create table a(id int(4),name char(12));
Query OK, 0 rows affected (0.01 sec)

MariaDB [ceshi]> insert a values(1,'测试');
Query OK, 1 row affected (0.00 sec)

MariaDB [ceshi]> select * from a;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 测试   |
+------+--------+
1 row in set (0.00 sec)

查看插入数据之前的起始位置和插入数据后的位置

MariaDB [ceshi]> show master status ;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000004 |      633 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

之后恢复主从并查看我们的主库是否有刚刚新主接替时写入的数据

MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

slave从：

根据我们之前的新主的“mysql-bin.000004”日志文件获取到插入数据前后的位置来将数据导入sql文件，之后将文件传送到我们的master主

导出数据到sql文件

mysqlbinlog mysql-bin.000004 --start-position=245 --stop-position=6
33 > /root/mysql-bin.sql

传输到主master服务器

master主：

将数据导入库内

MariaDB [(none)]> source /root/mysql-bin.sql

成功导入

MariaDB [ceshi]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| ceshi              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

MariaDB [ceshi]> use ceshi;
Database changed
MariaDB [ceshi]> select * from a;
+------+--------+
| id   | name   |
+------+--------+
|    1 | 测试   |
+------+--------+
1 row in set (0.00 sec)

成功将数据导入