MHA实现mysql主从数据库手动切换的方法
本文实例讲述了MHA实现mysql主从数据库手动切换的方法，分享给大家供大家参考。具体方法如下：
一、准备工作
1、分别在Master和Slave执行如下,方便mha检查复制:
grantallprivilegeson*.*to'root'@'10.1.1.231'identifiedby'rootpass'; grantallprivilegeson*.*to'root'@'10.1.1.234'identifiedby'rootpass'; grantreplicationslaveon*.*to'jpsync'@'10.1.1.231'identifiedby'jppasswd'; grantreplicationslaveon*.*to'jpsync'@'10.1.1.234'identifiedby'jppasswd'; flushprivileges;
2、将master设置为只读
mysql>setglobalread_only=1; QueryOK,0rowsaffected(0.00sec) mysql>showvariableslike'read_only'; +---------------+-------+ |Variable_name|Value| +---------------+-------+ |read_only |ON | +---------------+-------+ 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、主上执行:
mysql>showmasterstatus; +-------------------------+----------+--------------+--------------------------------------+-------------------+ |File |Position|Binlog_Do_DB|Binlog_Ignore_DB |Executed_Gtid_Set| +-------------------------+----------+--------------+--------------------------------------+-------------------+ |mysql-master-bin.000013| 120|denovo_ng |mysql,denovo,test,information_schema| | +-------------------------+----------+--------------+--------------------------------------+-------------------+ 1rowinset(0.00sec)
2、在10.1.1.234上执行如下sql命令；
changemastertomaster_host='10.1.1.231',master_port=63306,master_user='jpsync',
master_password='jppasswd',master_log_file='mysql-master-bin.000013',master_log_pos=120;
mysql>showslavestatus＼G;
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:10.1.1.231
Master_User:jpsync
Master_Port:63306
Connect_Retry:60
Master_Log_File:mysql-master-bin.000013
Read_Master_Log_Pos:120
Relay_Log_File:compute-0-52-relay-bin.000002
Relay_Log_Pos:290
Relay_Master_Log_File:mysql-master-bin.000013
Slave_IO_Running:Yes
Slave_SQL_Running:Yes3、查看master状态，并测试
mysql>showslavehosts; +-----------+------+-------+-----------+--------------------------------------+ |Server_id|Host|Port |Master_id|Slave_UUID | +-----------+------+-------+-----------+--------------------------------------+ | 1052| |63306| 1025|e25a3e4a-39c0-11e4-80cb-00259086c4b6| +-----------+------+-------+-----------+--------------------------------------+ 1rowinset(0.00sec)
主库10.1.1.231上插入记录
mysql>insertinto test_slave_002values(555551111,1,55555,99999,44.11,2222,91919); QueryOK,1rowaffected(0.00sec)
从库查询记录已经存在
mysql>select*fromtest_slave_002whereid=555551111; +-----------+-----+-----------+--------------+----------+----------------+--------------+ |id |tag|ticket_id|candidate_id|duration|source_file_id|source_start| +-----------+-----+-----------+--------------+----------+----------------+--------------+ |555551111| 1| 55555| 99999| 44.11| 2222| 91919| +-----------+-----+-----------+--------------+----------+----------------+--------------+ 1rowinset(0.00sec)
4、更新配置文件:
更新主库my.cnf配置添加
skip_slave_start
注意:防止重启数据库，启动slave进程，导致数据不一致。
更新从库my.cnf配置添加，设置slave库为只读:
read_only=1 relay_log_purge=0
然后重启主库和从库,观察库的信息:
主库信息:
mysql>showprocesslist; +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ |Id|User |Host |db |Command |Time|State |Info | +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ | 1|jpsync|10.1.1.234:49085|NULL|BinlogDump| 17|Masterhassentallbinlogtoslave;waitingforbinlogtobeupdated|NULL | | 2|root |localhost |NULL|Query | 0|init |showprocesslist| +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 2rowsinset(0.00sec) mysql>showmasterstatus; +-------------------------+----------+--------------+--------------------------------------+-------------------+ |File |Position|Binlog_Do_DB|Binlog_Ignore_DB |Executed_Gtid_Set| +-------------------------+----------+--------------+--------------------------------------+-------------------+ |mysql-master-bin.000014| 120|denovo_ng |mysql,denovo,test,information_schema| | +-------------------------+----------+--------------+--------------------------------------+-------------------+ 1rowinset(0.00sec)
从库信息:
mysql>showslavestatus＼G;
***************************1.row***************************
Slave_IO_State:Waitingformastertosendevent
Master_Host:10.1.1.231
Master_User:jpsync
Master_Port:63306
Connect_Retry:60
Master_Log_File:mysql-master-bin.000014
Read_Master_Log_Pos:120
Relay_Log_File:compute-0-52-relay-bin.000005
Relay_Log_Pos:290
Relay_Master_Log_File:mysql-master-bin.000014
Slave_IO_Running:Yes
Slave_SQL_Running:Yes
mysql>showprocesslist;
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
|Id|User |Host |db |Command|Time|State |Info |
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
| 1|systemuser| |NULL|Connect| 58|Waitingformastertosendevent |NULL |
| 2|systemuser| |NULL|Connect| 58|Slavehasreadallrelaylog;waitingfortheslaveI/Othreadtoupdateit|NULL |
| 3|root |localhost|NULL|Query | 0|init |showprocesslist|
+----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+
3rowsinset(0.00sec)
希望本文所述对大家的MySQL数据库程序设计有所帮助。