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)

希望本文所述对大家的mysql数据库程序设计有所帮助。