MHA是一款mysql主從配置服務器監控的軟件了,我們可以利用它來實現mysql主從服務器監控并且保證它們的穩定性.

一、準備工作

1、分別在Master和Slave執行如下,方便mha檢查復制:

1. grant all privileges on *.* to 'root'@'10.1.1.231' identified by 'rootpass'; 
2. grant all privileges on *.* to 'root'@'10.1.1.234' identified by 'rootpass'; 
3. grant replication slave on *.* to 'jpsync'@'10.1.1.231' identified by 'jppasswd'; 
4. grant replication slave on *.* to 'jpsync'@'10.1.1.234' identified by 'jppasswd'; 
5. flush privileges; 

2、將master設置為只讀:

1. mysql> set global read_only=1; 
2. Query OK, 0 rows affected (0.00 sec) 
3. mysql> show variables like 'read_only'; 
4. +---------------+-------+ 
5. | Variable_name | Value | 
6. +---------------+-------+ 
7. | read_only     | ON    | 
8. +---------------+-------+ 
9. 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、主上執行:

1. mysql> show master status; 
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. 1 row in set (0.00 sec) 

2、在10.1.1.234上執行如下sql命令；

1. change master to master_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.  
4. mysql> show slave status＼G; 
5. *************************** 1. row *************************** 
6.                Slave_IO_State: Waiting for master to send event 
7.                   Master_Host: 10.1.1.231 
8.                   Master_User: jpsync 
9.                   Master_Port: 63306 
10.                 Connect_Retry: 60 
11.               Master_Log_File: mysql-master-bin.000013 
12.           Read_Master_Log_Pos: 120 
13.                Relay_Log_File: compute-0-52-relay-bin.000002 
14.                 Relay_Log_Pos: 290 
15.         Relay_Master_Log_File: mysql-master-bin.000013 
16.              Slave_IO_Running: Yes 
17.             Slave_SQL_Running: Yes 

3、查看master狀態,并測試:

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

主庫10.1.1.231上插入記錄:

1. mysql> insert into  test_slave_002 values(555551111,1,55555,99999,44.11,2222,91919); 
2. Query OK, 1 row affected (0.00 sec) 

從庫查詢記錄已經存在:

1. mysql> select * from test_slave_002 where id=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. 1 row in set (0.00 sec) 

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

1,skip_slave_start

注意:防止重啟數據庫,啟動slave進程,導致數據不一致.

更新從庫my.cnf配置添加,設置slave庫為只讀:

1

2

read_only=1

relay_log_purge=0

然后重啟主庫和從庫,觀察庫的信息:

主庫信息:

1. mysql> show processlist; 
2. +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 
3. | Id | User   | Host             | db   | Command     | Time | State                                                                 | Info             | 
4. +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 
5. |  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             | 
6. |  2 | root   | localhost        | NULL | Query       |    0 | init                                                                  | show processlist | 
7. +----+--------+------------------+------+-------------+------+-----------------------------------------------------------------------+------------------+ 
8. 2 rows in set (0.00 sec) 
9.  
10.  
11. mysql> show master status; 
12. +-------------------------+----------+--------------+--------------------------------------+-------------------+ 
13. | File                    | Position | Binlog_Do_DB | Binlog_Ignore_DB                     | Executed_Gtid_Set | 
14. +-------------------------+----------+--------------+--------------------------------------+-------------------+ 
15. | mysql-master-bin.000014 |      120 | denovo_ng    | mysql,denovo,test,information_schema |                   | 
16. +-------------------------+----------+--------------+--------------------------------------+-------------------+ 
17. 1 row in set (0.00 sec) 

從庫信息:

1. mysql> show slave status＼G; 
2. *************************** 1. row *************************** 
3.                Slave_IO_State: Waiting for master to send event 
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.  
16. mysql> show processlist; 
17. +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 
18. | Id | User        | Host      | db   | Command | Time | State                                                                       | Info             | 
19. +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 
20. |  1 | system user |           | NULL | Connect |   58 | Waiting for master to send event                                            | NULL             | 
21. |  2 | system user |           | NULL | Connect |   58 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             | 
22. |  3 | root        | localhost | NULL | Query   |    0 | init                                                                        | show processlist | 
23. +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------------+------------------+ 
24. 3 rows in set (0.00 sec) --Vevb.com