MHA实现mysql主从手动切换方法

小浪SEO博客 专注SEO优化思维、SEO技术的实战分享类博客~

MHA是一款mysql主从配置服务器监控的软件了,我们可以利用它来实现mysql主从服务器监控并且保证它们的稳定性.

一、准备工作

1、分别在Master和Slave执行如下,方便mha检查复制:

  1. grantallprivilegeson*.*to'root'@'10.1.1.231'identifiedby'rootpass';
  2. grantallprivilegeson*.*to'root'@'10.1.1.234'identifiedby'rootpass';
  3. grantreplicationslaveon*.*to'jpsync'@'10.1.1.231'identifiedby'jppasswd';
  4. grantreplicationslaveon*.*to'jpsync'@'10.1.1.234'identifiedby'jppasswd';
  5. flushprivileges;

2、将master设置为只读:

  1. mysql>setglobalread_only=1;
  2. QueryOK,0rowsaffected(0.00sec)
  3. mysql>showvariableslike'read_only';
  4. +---------------+-------+
  5. |Variable_name|Value|
  6. +---------------+-------+
  7. |read_only|ON|
  8. +---------------+-------+
  9. 1rowinset(0.00sec)

交互模式:

#masterha_master_switch --master_state=alive --conf=/etc/masterha/app1.cnf --new_master_host=10.1.1.231 --new_master_port=63306

或非交互模式:

#masterha_master_switch --master_state=alive --conf=/etc/masterha/app1.cnf --new_master_host=10.1.1.231 --new_master_port=63306 —interactive=0

二、切换完以后,如何让10.1.1.231为主,10.1.1.234为从,操作步骤:

1、主上执行:

  1. mysql>showmasterstatus;
  2. +-------------------------+----------+--------------+--------------------------------------+-------------------+
  3. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
  4. +-------------------------+----------+--------------+--------------------------------------+-------------------+
  5. |mysql-master-bin.000013|120|denovo_ng|mysql,denovo,test,information_schema||
  6. +-------------------------+----------+--------------+--------------------------------------+-------------------+
  7. 1rowinset(0.00sec)

2、在10.1.1.234上执行如下sql命令;

  1. changemastertomaster_host='10.1.1.231',master_port=63306,master_user='jpsync',
  2. master_password='jppasswd',master_log_file='mysql-master-bin.000013',master_log_pos=120;
  3. mysql>showslavestatus\G;
  4. ***************************1.row***************************
  5. Slave_IO_State:Waitingformastertosendevent
  6. Master_Host:10.1.1.231
  7. Master_User:jpsync
  8. Master_Port:63306
  9. Connect_Retry:60
  10. Master_Log_File:mysql-master-bin.000013
  11. Read_Master_Log_Pos:120
  12. Relay_Log_File:compute-0-52-relay-bin.000002
  13. Relay_Log_Pos:290
  14. Relay_Master_Log_File:mysql-master-bin.000013
  15. Slave_IO_Running:Yes
  16. Slave_SQL_Running:Yes

3、查看master状态,并测试:

  1. mysql>showslavehosts;
  2. +-----------+------+-------+-----------+--------------------------------------+
  3. |Server_id|Host|Port|Master_id|Slave_UUID|
  4. +-----------+------+-------+-----------+--------------------------------------+
  5. |1052||63306|1025|e25a3e4a-39c0-11e4-80cb-00259086c4b6|
  6. +-----------+------+-------+-----------+--------------------------------------+
  7. 1rowinset(0.00sec)

主库10.1.1.231上插入记录:

  1. mysql>insertintotest_slave_002values(555551111,1,55555,99999,44.11,2222,91919);
  2. QueryOK,1rowaffected(0.00sec)

从库查询记录已经存在:

  1. mysql>select*fromtest_slave_002whereid=555551111;
  2. +-----------+-----+-----------+--------------+----------+----------------+--------------+
  3. |id|tag|ticket_id|candidate_id|duration|source_file_id|source_start|
  4. +-----------+-----+-----------+--------------+----------+----------------+--------------+
  5. |555551111|1|55555|99999|44.11|2222|91919|
  6. +-----------+-----+-----------+--------------+----------+----------------+--------------+
  7. 1rowinset(0.00sec)

4、更新配置文件,更新主库my.cnf配置添加:

1,skip_slave_start

注意:防止重启数据库,启动slave进程,导致数据不一致.

更新从库my.cnf配置添加,设置slave库为只读:

1

2

read_only=1

relay_log_purge=0

然后重启主库和从库,观察库的信息:

主库信息:

  1. mysql>showprocesslist;
  2. +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
  3. |Id|User|Host|db|Command|Time|State|Info|
  4. +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
  5. |1|jpsync|10.1.1.234:49085|NULL|BinlogDump|17|Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated|NULL|
  6. |2|root|localhost|NULL|Query|0|init|showprocesslist|
  7. +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+
  8. 2rowsinset(0.00sec)
  9. mysql>showmasterstatus;
  10. +-------------------------+----------+--------------+--------------------------------------+-------------------+
  11. |File|Position|Binlog_Do_DB|Binlog_Ignore_DB|Executed_Gtid_Set|
  12. +-------------------------+----------+--------------+--------------------------------------+-------------------+
  13. |mysql-master-bin.000014|120|denovo_ng|mysql,denovo,test,information_schema||
  14. +-------------------------+----------+--------------+--------------------------------------+-------------------+
  15. 1rowinset(0.00sec)

从库信息:

  1. mysql>showslavestatus\G;
  2. ***************************1.row***************************
  3. Slave_IO_State:Waitingformastertosendevent
  4. Master_Host:10.1.1.231
  5. Master_User:jpsync
  6. Master_Port:63306
  7. Connect_Retry:60
  8. Master_Log_File:mysql-master-bin.000014
  9. Read_Master_Log_Pos:120
  10. Relay_Log_File:compute-0-52-relay-bin.000005
  11. Relay_Log_Pos:290
  12. Relay_Master_Log_File:mysql-master-bin.000014
  13. Slave_IO_Running:Yes
  14. Slave_SQL_Running:Yes
  15. mysql>showprocesslist;
  16. +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
  17. |Id|User|Host|db|Command|Time|State|Info|
  18. +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
  19. |1|systemuser||NULL|Connect|58|Waitingformastertosendevent|NULL|
  20. |2|systemuser||NULL|Connect|58|Slavehasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit|NULL|
  21. |3|root|localhost|NULL|Query|0|init|showprocesslist|
  22. +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
  23. 3rowsinset(0.00sec)--phpfensi.com

相关广告
  • MHA实现mysql主从手动切换方法 MHA实现mysql主从手动切换方法 MHA实现mysql主从手动切换方法
相关阅读

MHA实现mysql主从手动切换方法

2019/10/10 17:32:52 | 谷歌SEO算法 | SEO知识