MHA實現mysql主從數據庫手動切換的方法

2024-07-24 13:06:24

字體：大中小

來源：轉載

供稿：網友

這篇文章主要介紹了MHA實現mysql主從數據庫手動切換的方法,詳細的講述了主從數據庫切換的步驟與方法,非常實用,需要的朋友可以參考下

本文實例講述了MHA實現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

二、切換完以后，如何讓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)