mha實現(xiàn)mysql主從數(shù)據(jù)庫手動切換的方法
來源:易賢網(wǎng) 閱讀:1465 次 日期:2014-12-10 11:55:39
溫馨提示:易賢網(wǎng)小編為您整理了“mha實現(xiàn)mysql主從數(shù)據(jù)庫手動切換的方法”,方便廣大網(wǎng)友查閱!

本文實例講述了mha實現(xiàn)mysql主從數(shù)據(jù)庫手動切換的方法,分享給大家供大家參考。具體方法如下:

一、準備工作

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),并測試

復(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)

主庫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)

從庫查詢記錄已經(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、更新配置文件:

更新主庫my.cnf配置添加

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

注意:防止重啟數(shù)據(jù)庫,啟動slave進程,導(dǎo)致數(shù)據(jù)不一致。

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

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

relay_log_purge=0

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

主庫信息:

復(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)

從庫信息:

復(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)

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

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

更多信息請查看數(shù)據(jù)庫
由于各方面情況的不斷調(diào)整與變化,易賢網(wǎng)提供的所有考試信息和咨詢回復(fù)僅供參考,敬請考生以權(quán)威部門公布的正式信息和咨詢?yōu)闇剩?/div>

2025國考·省考課程試聽報名

  • 報班類型
  • 姓名
  • 手機號
  • 驗證碼
關(guān)于我們 | 聯(lián)系我們 | 人才招聘 | 網(wǎng)站聲明 | 網(wǎng)站幫助 | 非正式的簡要咨詢 | 簡要咨詢須知 | 加入群交流 | 手機站點 | 投訴建議
工業(yè)和信息化部備案號:滇ICP備2023014141號-1 云南省教育廳備案號:云教ICP備0901021 滇公網(wǎng)安備53010202001879號 人力資源服務(wù)許可證:(云)人服證字(2023)第0102001523號
聯(lián)系電話:0871-65099533/13759567129 獲取招聘考試信息及咨詢關(guān)注公眾號:hfpxwx
咨詢QQ:526150442(9:00—18:00)版權(quán)所有:易賢網(wǎng)