| 标题 | mha实现mysql主从数据库手动切换的方法 |
| 内容 | 本文实例讲述了mha实现mysql主从数据库手动切换的方法，分享给大家供大家参考。具体方法如下： 一、准备工作 1、分别在master和slave执行如下,方便mha检查复制: 复制代码 代码如下:grant all privileges on *.* toidentified by 'rootpass'; grant all privileges on *.* to identified by 'rootpass'; grant replication slave on *.* to identified by 'jppasswd'; grant replication slave on *.* to 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数据库程序设计有所帮助。 |
| 随便看 |
|
在线学习网考试资料包含高考、自考、专升本考试、人事考试、公务员考试、大学生村官考试、特岗教师招聘考试、事业单位招聘考试、企业人才招聘、银行招聘、教师招聘、农村信用社招聘、各类资格证书考试等各类考试资料。