mysql 主从 lock,my17_Mysql 主从切换

注意事项：

从库提升为主库read_only要设置为OFF

原主库改为从库后，read_only要设置ON

read_only=ON并不能对root生效，确保root不会进行数据写入

从主库进行 flush tables with read lock 开始，到主从切换完毕，数据库不能对外提供写服务；一直对外提供读服务

建立主从

***************************************************

grant replication slave on *.* to [email protected]%‘ identified by ‘rootroot‘;

show master status\G;

CHANGE MASTER TO

MASTER_HOST=‘192.168.56.103‘,

MASTER_USER=‘repl_user‘,

MASTER_PASSWORD=‘rootroot‘,

MASTER_PORT=3302,

MASTER_LOG_FILE=‘mysql-bin.000001‘,

MASTER_LOG_POS=154,

MASTER_CONNECT_RETRY=10;

start slave;

主库加锁，锁定数据使之不再变化

***********************************

mysql -uautomng -p -h192.168.56.103 -P3302

use vodb;

call p_addtest(1000000); #使用过程插入数据以模拟业务数据，此处如果是使用root用户执行的，那么后面变为从库，仅仅使用read_only=OFF是无法中止该过程的

mysql -uroot -p -S /data/mysql/log/eee/mysql_eee.sock

flush logs;

flush tables with read lock;

确定从库与主库一致

**************************************

mysql> show full processlist;

+----+-------------+-----------+------+---------+------+---------------------------------------------+-----------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-------------+-----------+------+---------+------+---------------------------------------------+-----------------------+

| 2 | root | localhost | NULL | Sleep | 6017 | | NULL |

| 3 | root | localhost | vodb | Query | 0 | starting | show full processlist |

| 42 | system user | | NULL | Connect | 172 | Waiting for master to send event | NULL |

| 43 | system user | | NULL | Connect | 0 | Waiting for dependent transaction to commit | NULL |

| 44 | system user | | NULL | Connect | 114 | Waiting for an event from Coordinator | NULL |

| 45 | system user | | NULL | Connect | 114 | System lock | NULL |

| 46 | system user | | NULL | Connect | 173 | Waiting for an event from Coordinator

从库要出现 Slave has read all relay log;

mysql> show full processlist;

+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+

| Id | User | Host | db | Command | Time | State | Info |

+----+-------------+-----------+------+---------+------+--------------------------------------------------------+-----------------------+

| 2 | root | localhost | NULL | Sleep | 6058 | | NULL |

| 3 | root | localhost | vodb | Query | 0 | starting | show full processlist |

| 42 | system user | | NULL | Connect | 213 | Waiting for master to send event | NULL |

| 43 | system user | | NULL | Connect | 1 | Slave has read all relay log; waiting for more updates | NULL |

| 44 | system user | | NULL | Connect | 79 | Waiting for an event from Coordinator

从库提升为主

*************************************************

reset master;

stop slave;

reset slave all;

grant replication slave on *.* to [email protected]%‘ identified by ‘rootroot‘;

flush privileges;

set global read_only=OFF;

show master status\G;

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

File: mysql-bin.000001

Position: 613

记下master status后，数据库就可以对外提供写服务了

原主库变成新从库

*************************************************

#如果root用户有业务，则直接关库，是防止一些未执行完的过程、触发器、JOB继续修改库，直接关闭一次，完全中断所有事务，包括root用户正在执行的事务；如果root没有业务使用，则不必关心此问题

#mysql> shutdown;

#mysqld_safe --defaults-file=/etc/my_eee.cnf --user=mysql &

set global read_only=ON;

reset master;

CHANGE MASTER TO

MASTER_HOST=‘192.168.56.102‘,

MASTER_USER=‘repl_user‘,

MASTER_PASSWORD=‘rootroot‘,

MASTER_PORT=3302,

MASTER_LOG_FILE=‘mysql-bin.000001‘,

MASTER_LOG_POS=613,

MASTER_CONNECT_RETRY=10;

start slave;

原文：https://www.cnblogs.com/perfei/p/9680844.html