MHA是一位日本MySQL大牛用Perl写的一套MySQL故障切换方案，来保证数据库系统的高可用.在宕机的时间内（通常10—30秒内），完成故障切换，部署MHA，可避免主从一致性问题，节约购买新服务器的费用，不影响服务器性能，易安装，不改变现有部署。
一、准备工作
1、分别在Master和Slave执行如下,方便mha检查复制:
grant all privileges on *.* to 'root'@'10.1.1.231' identified by 'rootpass'; grant all privileges on *.* to 'root'@'10.1.1.234' identified by 'rootpass'; grant replication slave on *.* to 'jpsync'@'10.1.1.231' identified by 'jppasswd'; grant replication slave on *.* to 'jpsync'@'10.1.1.234' identified by 'jppasswd'; flush privileges;
2、将master设置为只读
mysql> set global read_only=1; Query OK, 0 rows affected (0.00 sec) mysql> show variables like 'read_only'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | read_only | ON | +---------------+-------+ 1 row in set (0.00 sec)
交互模式:
#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> show master status; +-------------------------+----------+--------------+--------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+--------------------------------------+-------------------+ | mysql-master-bin.000013 | 120 | denovo_ng | mysql,denovo,test,information_schema | | +-------------------------+----------+--------------+--------------------------------------+-------------------+ 1 row in set (0.00 sec)
2、在10.1.1.234上执行如下sql命令；
change master to master_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> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event 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: Yes
3、查看master状态，并测试
mysql> show slave hosts; +-----------+------+-------+-----------+--------------------------------------+ | Server_id | Host | Port | Master_id | Slave_UUID | +-----------+------+-------+-----------+--------------------------------------+ | 1052 | | 63306 | 1025 | e25a3e4a-39c0-11e4-80cb-00259086c4b6 | +-----------+------+-------+-----------+--------------------------------------+ 1 row in set (0.00 sec)
主库10.1.1.231上插入记录
mysql> insert into test_slave_002 values(555551111,1,55555,99999,44.11,2222,91919); Query OK, 1 row affected (0.00 sec)
从库查询记录已经存在
mysql> select * from test_slave_002 where id=555551111; +-----------+-----+-----------+--------------+----------+----------------+--------------+ | id| tag | ticket_id | candidate_id | duration | source_file_id | source_start | +-----------+-----+-----------+--------------+----------+----------------+--------------+ | 555551111 | 1 | 55555 |99999 | 44.11 | 2222 |91919 | +-----------+-----+-----------+--------------+----------+----------------+--------------+ 1 row in set (0.00 sec)
4、更新配置文件:
更新主库my.cnf配置添加
skip_slave_start
注意:防止重启数据库，启动slave进程，导致数据不一致。
更新从库my.cnf配置添加，设置slave库为只读:
read_only=1 relay_log_purge=0
然后重启主库和从库,观察库的信息:
主库信息:
mysql> show processlist; +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ | 1 | jpsync | 10.1.1.234:49085 | NULL | Binlog Dump | 17 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | | 2 | root | localhost| NULL | Query | 0 | init | show processlist | +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 2 rows in set (0.00 sec) mysql> show master status; +-------------------------+----------+--------------+--------------------------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------------+----------+--------------+--------------------------------------+-------------------+ | mysql-master-bin.000014 | 120 | denovo_ng | mysql,denovo,test,information_schema | | +-------------------------+----------+--------------+--------------------------------------+-------------------+ 1 row in set (0.00 sec)
从库信息:
mysql> show slave status\G; *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event 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> show processlist; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | Id | User| Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ | 1 | system user | | NULL | Connect | 58 | Waiting for master to send event | NULL | | 2 | system user | | NULL | Connect | 58 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | | 3 | root| localhost | NULL | Query | 0 | init | show processlist | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 3 rows in set (0.00 sec)
