mysql宕机从库切换_Mysql DBA 高级运维学习之路-一主多从宕机从库切换主库继续和从库同步过程...

1.服务器宕机1.1 实验环境主服务器：mysql3306从服务器1：mysql3307从服务器2：mysql3308问题：主服务器宕机，切换3307为主库我用的是多实例，模拟服务器宕机将3306端口数据库停掉。1.2 查看两个从库同步的状态(1)查看3307线程的状态mysql> show processlist\G*************************** 1. row **

侃叔

398人浏览 · 2021-01-19 01:58:27

侃叔 · 2021-01-19 01:58:27 发布

1.服务器宕机

1.1 实验环境

主服务器：mysql3306

从服务器1：mysql3307

从服务器2：mysql3308

问题：主服务器宕机，切换3307为主库

我用的是多实例，模拟服务器宕机将3306端口数据库停掉。

1.2 查看两个从库同步的状态

(1)查看3307线程的状态

mysql> show processlist\G

*************************** 1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 1857

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 1857

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 3. row ***************************

Id: 3

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: NULL

Info: show processlist

3 rows in set (0.00 sec)

(2)查看3308线程的状态

mysql> show processlist\G

*************************** 1. row ***************************

Id: 1

User: system user

Host:

db: NULL

Command: Connect

Time: 1930

State: Waiting for master to send event

Info: NULL

*************************** 2. row ***************************

Id: 2

User: system user

Host:

db: NULL

Command: Connect

Time: 1930

State: Slave has read all relay log; waiting for the slave I/O thread to update it

Info: NULL

*************************** 3. row ***************************

Id: 3

User: root

Host: localhost

db: NULL

Command: Query

Time: 0

State: NULL

Info: show processlist

3 rows in set (0.00 sec)

查看看两个线程的更新状态，确保所有relay log已经复制完毕，在每个从库上执行下面命令，直到看到has read all relay或者Slave has read all relay log

mysql> stop slave io_thread;

Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show processlist\G

1.3 选择要切换的从库

分别查看从库master.info，将binlog位置最靠前更新最快POS最大作为主库的从库，切换为主库，这里将3307切换为主库。

[root@linzhongniao ~]# sed -n '2,3p' /data/3307/data/master.info

mysqlbin_linzhongniao.000039

107

[root@linzhongniao ~]# sed -n '2,3p' /data/3308/data/master.info

mysqlbin_linzhongniao.000039

107

1.4 要切换的从库上的操作

(1)登录从库执行下面操作

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> reset master;

Query OK, 0 rows affected (0.00 sec)

mysql> quit;

(2)删除master.info和relay-log.info

进入到要切换主库的数据库目录中，删除master.info和relay-log.info

[root@linzhongniao 3307]# pwd

/data/3307

[root@linzhongniao 3307]# rm -f relay-log.info

[root@linzhongniao 3307]# cd data/

[root@linzhongniao data]# pwd

/data/3307/data

[root@linzhongniao data]# rm -f master.info

(3)检查授权表和read-only等参数

3307的配置文件中做了授权表和read-log参数，将它们注释掉

[root@linzhongniao data]# egrep "read-only|replicate-wild" /data/3307/my.cnf

#read-only

#replicate-wild-ignore-table = mysql.%

(4)3307提升为主库

开启binlog

[root@linzhongniao data]# grep "log-bin" /data/3307/my.cnf

log-bin = /data/3307/mysqlbin_linzhongniao

重启数据库服务

[root@linzhongniao data]# /data/3307/mysql restart

Restarting MySQL...

Stoping MySQL....

Starting MySQL......

至此从库3307提升为主库完毕

1.5 其他从库的操作

(1)查看3307主库master的状态，记录binlog和位置

mysql> show master status\G

*************************** 1. row ***************************

File: mysqlbin_linzhongniao.000002

Position: 107

Binlog_Do_DB:

Binlog_Ignore_DB:

1 row in set (0.00 sec)

(2)change master

将master.info和relay-log.info删除，重新change master

[root@linzhongniao 3308]# rm -f relay-log.info

[root@linzhongniao 3308]# cd data/

[root@linzhongniao data]# rm -f master.info

mysql> CHANGE MASTER TO

-> MASTER_HOST='192.168.136.113', 如果做域名解析了直接修改host文件即可

-> MASTER_PORT=3306,

-> MASTER_USER='rep',

-> MASTER_PASSWORD='123456',

-> MASTER_LOG_FILE='mysqlbin_linzhongniao.000002',

-> MASTER_LOG_POS=107;

Query OK, 0 rows affected (0.10 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

mysql> show slave status\G

*************************** 1. row ***************************

Slave_IO_State: Connecting to master

Master_Host: 192.168.136.113

Master_User: rep

Master_Port: 3306

Connect_Retry: 60

Master_Log_File: mysqlbin_linzhongniao.000002

Read_Master_Log_Pos: 107

Relay_Log_File: relay-bin.000001

Relay_Log_Pos: 4

Relay_Master_Log_File: mysqlbin_linzhongniao.000002

Slave_IO_Running: Connecting

Slave_SQL_Running: Yes

Replicate_Do_DB:

Replicate_Ignore_DB:

Replicate_Do_Table:

Replicate_Ignore_Table:

Replicate_Wild_Do_Table:

Replicate_Wild_Ignore_Table:

Last_Errno: 0

Last_Error:

Skip_Counter: 0

Exec_Master_Log_Pos: 107

Relay_Log_Space: 107

Until_Condition: None

Until_Log_File:

Until_Log_Pos: 0

Master_SSL_Allowed: No

Master_SSL_CA_File:

Master_SSL_CA_Path:

Master_SSL_Cert:

Master_SSL_Cipher:

Master_SSL_Key:

Seconds_Behind_Master: NULL

Master_SSL_Verify_Server_Cert: No

Last_IO_Errno: 2003

Last_IO_Error: error connecting to master 'rep@192.168.136.113:3306' - retry-time: 60 retries: 86400

Last_SQL_Errno: 0

Last_SQL_Error:

Replicate_Ignore_Server_Ids:

Master_Server_Id: 1

1 row in set (0.00 sec)

报错了提示找不到3306这个数据库服务器的端口，原因是在change master的时候把MASTER_PORT的写的还是3306，这个地方也是故意这么写的，在其他数据库操作的时候时候授权的用户名密码是不用改的，其他的有可能要修改，这时不必删除master.info和relay-log.info文件，直接用change master to修改即可。之所以删除重新change master是不想产生不必要的麻烦。

mysql> stop slave;

Query OK, 0 rows affected (0.00 sec)

mysql> change master to MASTER_PORT=3307;

Query OK, 0 rows affected (0.00 sec)

mysql> start slave;

Query OK, 0 rows affected (0.00 sec)

至此主库宕机切换成功。

2.数据库宕机

主库宕机，服务器还能起来登录其他从库查看线程状态，确保数据数据复制完毕。如果没有同步完毕，登录每个仓库查看从库的主从复制状态，将没有同步的binlog数据备份，在从其中一个库补全把差距补回来(增量备份)。然后把这个从库作为主库

3.主库以外宕机

例如：我们有计划切换，怎么做呢

(1)登录所有从库查看同步的状态，如果都一样主库锁表，要切换主库的从库关闭同步，设置同步参数change master。