各位用户为了找寻关于MHA实现mysql主从数据库手动切换的方法的资料费劲了很多周折。这里教程网为您整理了关于MHA实现mysql主从数据库手动切换的方法的相关资料，仅供查阅，以下为您介绍关于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数据库程序设计有所帮助。