本文實(shí)例講述了MHA實(shí)現(xiàn)mysql主從數(shù)據(jù)庫(kù)手動(dòng)切換的方法，分享給大家供大家參考。具體方法如下：

一、準(zhǔn)備工作

1、分別在Master和Slave執(zhí)行如下,方便mha檢查復(fù)制:

復(fù)制代碼 代碼如下:grant all privileges on *.* toidentified by 'rootpass';

grant all privileges on *.* to identified by 'rootpass';

grant replication slave on *.* to identified by 'jppasswd';

grant replication slave on *.* to identified by 'jppasswd';

flush privileges;

2、將master設(shè)置為只讀

復(fù)制代碼 代碼如下: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)

交互模式:

復(fù)制代碼 代碼如下:#masterha_master_switch --master_state=alive --conf=/etc/masterha/app1.cnf --new_master_host=10.1.1.231 --new_master_port=63306

或非交互模式:

復(fù)制代碼 代碼如下:#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、主上執(zhí)行:

復(fù)制代碼 代碼如下: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上執(zhí)行如下sql命令；

復(fù)制代碼 代碼如下: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狀態(tài)，并測(cè)試

復(fù)制代碼 代碼如下: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)

主庫(kù)10.1.1.231上插入記錄

復(fù)制代碼 代碼如下:mysql> insert into test_slave_002 values(555551111,1,55555,99999,44.11,2222,91919);

Query OK, 1 row affected (0.00 sec)

從庫(kù)查詢記錄已經(jīng)存在

復(fù)制代碼 代碼如下: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、更新配置文件:

更新主庫(kù)my.cnf配置添加

復(fù)制代碼 代碼如下:skip_slave_start

注意:防止重啟數(shù)據(jù)庫(kù)，啟動(dòng)slave進(jìn)程，導(dǎo)致數(shù)據(jù)不一致。

更新從庫(kù)my.cnf配置添加，設(shè)置slave庫(kù)為只讀:

復(fù)制代碼 代碼如下:read_only=1

relay_log_purge=0

然后重啟主庫(kù)和從庫(kù),觀察庫(kù)的信息:

主庫(kù)信息:

復(fù)制代碼 代碼如下: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)

從庫(kù)信息:

復(fù)制代碼 代碼如下: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)

希望本文所述對(duì)大家的MySQL數(shù)據(jù)庫(kù)程序設(shè)計(jì)有所幫助。

更多信息請(qǐng)查看IT技術(shù)專欄