mysql主从复制 如果主库出现故障 从库升级为主库故障修复后在恢复

mysql主从复制 如果主库出现故障 从库升级为主库故障修复后在

恢复

环境配置：

• 系统版本

[root@localhost ~]# cat /etc/redhat-release 
CentOS Linux release 7.7.1908 (Core)

• 内核版本

 [root@localhost ~]# uname -r
3.10.0-1062.el7.x86_64

• 角色分配

A ：主 – 故障 - 主 192.168.74.190
B ：从 – 主 – 从 192.168.74.191

首先确保主从环境
master 主：
模拟主库故障重启

slave 从:
关闭重置slave 重置master

MariaDB [(none)]> stop slave; Query OK, 0 rows affected (0.01 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 all on . to’slave’@’%’ 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 | 445 |
±-----------------±---------±-------------±-----------------+

模拟新的数据生成（创建库并插入数据）

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

MariaDB [(none)]> use ceshi; Database changed MariaDB [ceshi]> create
table a(id int(10),name varchar(10)); Query OK, 0 rows affected (0.00
sec)

MariaDB [ceshi]> insert into 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 [(none)]> show master status;
±-----------------±---------±-------------±-----------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
±-----------------±---------±-------------±-----------------+
| mysql-bin.000001 | 842 |
±-----------------±---------±-------------±-----------------+
1 row in set (0.00 sec)

主恢复 从又恢复从 将从的新增数据打包 发给主

[root@localhost ~]# mysqlbinlog /var/lib/mysql/mysql-bin.000001 --start-position=445 --stop-position=842 > /root/a.sql

[root@localhost ~]# scp /root/a.sql 192.168.74.190:/root/
The authenticity of host ‘192.168.74.190 (192.168.74.190)’ can’t be established. ECDSA key
fingerprint is SHA256:DQjrTMPNwX4iiAGlu9Dn2BFf6LK9v8EIRKZhu0cNRnQ.
ECDSA key fingerprint is MD5:97:2c:78:4e:ea:ba:ad:3f:10:88:4d:1d:d9:b9:a1:ec.
Are you sure you want to continue connecting (yes/no)? yes
Warning: Permanently added ‘192.168.74.190’ (ECDSA) to the list of known hosts.
root@192.168.74.190’s password:
a.sql 100% 2164 1.5MB/s 00:00

master上接受并查看

MariaDB [(none)]> source /root/a.sql

并查看
MariaDB [ceshi]> show databases;
±-------------------+
| Database |
±-------------------+
| information_schema |
| 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)