MHA完成mysql主從數據庫手動切換的辦法。本站提示廣大學習愛好者：（MHA完成mysql主從數據庫手動切換的辦法）文章只能為提供參考，不一定能成為您想要的結果。以下是MHA完成mysql主從數據庫手動切換的辦法正文
本文實例講述了MHA完成mysql主從數據庫手動切換的辦法，分享給年夜家供年夜家參考。詳細辦法以下：
1、預備任務
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
2、切換完今後，若何讓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)
願望本文所述對年夜家的MySQL數據庫法式設計有所贊助。
