表里插入数据,假设主库A发生故障,停机。此时从库B提升为主库,在从库表上插入数据。此时A机器恢复,重建AB主从结构,A为主B为从。
环境
MySQL主从
192.168.59.143 主→0→主
192.168.59.144 从→主→从
一、MySQL崩了切换从为主
192.168.59.144 从→主
首先重置我们从服务器上的所有配置
mysql
MariaDB [(none)]> stop slave;
Query OK, 0 rows affected, 1 warning (0.00 sec)
MariaDB [(none)]> reset master;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> reset slave all;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> grant replication slave on *.* to 'slave'@'192.168.59.143' identified by '123';
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 | 473 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
从库升为主库完成
此时我们之前的主库会多出数据建一个库表模拟数据
192.168.59.143 主→0
首先记录一下模拟数据之前的值
mysql
MariaDB [(none)]> create database dzw charset utf8;
Query OK, 1 row affected (0.00 sec)
MariaDB [(none)]> use dzw
Database changed
MariaDB [dzw]> create table test(ID int(10),NAME varchar(10));
Query OK, 0 rows affected (0.00 sec)
MariaDB [dzw]> insert into test values(1,"暴躁程序员");
Query OK, 1 row affected (0.00 sec)
MariaDB [dzw]> select * from test;
+------+-----------------+
| ID | NAME |
+------+-----------------+
| 1 | 暴躁程序员 |
+------+-----------------+
1 row in set (0.00 sec)
在查看一下position值说明了647-1047之间产生了数据我们新主库上没有
退出MySQL进行倒增量备份
mysqlbinlog /var/lib/mysql/mysql-bin.000003 --start-position='1047' --stop-position='1047' > /root/1233.sql
把备份出来的文件拷贝到目标服务器执行恢复
mysql
source /root/1233.sql
此时两边的数据都同步了
二、之前坏掉的库已经恢复恢复到之前的主从结构
192.168.59.143
参考上面的步骤重置MySQL做主
192.168.59.144
参考上面的步骤重置MySQL做从
至此主从恢复如初