《MYSQL教程MHA實現mysql主從數據庫手動切換的方法》要點：
本文介紹了MYSQL教程MHA實現mysql主從數據庫手動切換的方法，希望對您有用。如果有疑問，可以聯系我們。
MYSQL應用本文實例講述了MHA實現mysql主從數據庫手動切換的辦法,分享給大家供大家參考.具體辦法如下：
MYSQL應用一、準備工作
MYSQL應用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
MYSQL應用二、切換完以后,如何讓10.1.1.231為主,10.1.1.234為從,操作步驟:
MYSQL應用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應用希望本文所述對大家的MySQL數據庫程序設計有所贊助.
《MYSQL教程MHA實現mysql主從數據庫手動切換的方法》是否對您有啟發，歡迎查看更多與《MYSQL教程MHA實現mysql主從數據庫手動切換的方法》相關教程，學精學透。維易PHP學院為您提供精彩教程。
轉載請注明本頁網址：
http://www.snjht.com/jiaocheng/10429.html
