mysql数据库主从库配置之主从切换mysql：主从复制(replication)搭建 介绍了 mysql 主从配置过程，这篇 介绍手工主从切换过程。一 环境信息主库 192.168.1.60/3306 主机名 db1备库 192.168.1.61/3306 主机名 db2备注： 主从节点 mysql 安装略，replication 安装略。二 主从切换--主，备节点都要创建 replication 用户。如果无，参考下面创建。create user 'rep1'@'%' identified by 'rep1abcd1243d'; grant replication slave on *.* to 'rep1'@'%';
--查询从库状态root@localhost:francs>show slave status\g
--查询主库状态root@localhost:francs>show slave hosts; +-----------+-------------------+------+-----------+--------------------------------------+ | server_id | host | port | master_id | slave_uuid | +-----------+-------------------+------+-----------+--------------------------------------+ | 2 | 192.168.2.38(db2) | 3306 | 1 | ad397a06-7c56-11e4-b2fb-000c29dcb3b5 | +-----------+-------------------+------+-----------+--------------------------------------+ 1 row in set (0.00 sec)
--从库: 停止 io_thread 线程root@localhost:francs>stop slave io_thread; root@localhost:francs>show slave status\g *************************** 1. row *************************** slave_io_state: master_host: 192.168.2.37 master_user: rep1 master_port: 3306 connect_retry: 60 master_log_file: bin-log.000001 read_master_log_pos: 362 relay_log_file: db2-relay-bin.000002 relay_log_pos: 523 relay_master_log_file: bin-log.000001 slave_io_running: no slave_sql_running: yes replicate_do_db: replicate_ignore_db: replicate_do_table: replicate_ignore_table: replicate_wild_do_table: replicate_wild_ignore_table: last_errno: 0 last_error: skip_counter: 0 exec_master_log_pos: 362 relay_log_space: 694 until_condition: none until_log_file: until_log_pos: 0 master_ssl_allowed: no master_ssl_ca_file: master_ssl_ca_path: master_ssl_cert: master_ssl_cipher: master_ssl_key: seconds_behind_master: null master_ssl_verify_server_cert: no last_io_errno: 0 last_io_error: last_sql_errno: 0 last_sql_error: replicate_ignore_server_ids: master_server_id: 1 master_uuid: 0c130d47-22bb-11e4-aaaa-000c2986ac80 master_info_file: mysql.slave_master_info sql_delay: 0 sql_remaining_delay: null slave_sql_running_state: slave has read all relay log; waiting for the slave i/o thread to update it master_retry_count: 86400 master_bind: last_io_error_timestamp: last_sql_error_timestamp: master_ssl_crl: master_ssl_crlpath: retrieved_gtid_set: executed_gtid_set: auto_position: 0 1 row in set (0.00 sec)
--激活从库(从库上操作)root@localhost:francs>stop slave; root@localhost:francs>reset master; root@localhost:francs>reset slave all; root@localhost:francs>show binary logs; +----------------+-----------+ | log_name | file_size | +----------------+-----------+ | bin-log.000001 | 120 | +----------------+-----------+ 1 row in set (0.00 sec)
备注：reset slave all 命令会删除从库的 replication 参数，之后 show slave status\g 的信息返回为空。--将原来主库变为从库mysql> change master to master_host='192.168.1.61', master_port=3306, master_user='rep1', master_password='rep1abcd1243d', master_log_file='bin-log.000001', master_log_pos=120; root@localhost:francs>start slave; root@localhost:francs>show slave status\g
备注：这步执行之后，发现 db1 日志文件报了以下错误，提示 db1 连接不上 db2。--db1 日志报错2015-03-02 14:24:14 26198 [error] slave i/o: error connecting to master 'rep1@192.168.1.61:3306' - retry-time: 60 retries: 8, error_code: 1045
--解决过程--连接测试 [mysql@db1 ~]$ mysql -h 192.168.1.60 -p 3306 -urep1 备注：居然不需要密码能直接能连上。 --测试匿名用户 [mysql@db1 ~]$ mysql -h 192.168.1.60 -p 3306 -urep2 备注：依然不需要密码。 --原因分析 root@localhost:francs>select host,user,password from mysql.user where user=''; +-----------+------+----------+ | host | user | password | +-----------+------+----------+ | localhost | | | | db1 | | | +-----------+------+----------+ 备注: 原来 db2 节点上存在 user 为空的的两行，表示匿名用户可以连接数据库， 删除这两行，之后 flush privileges; --再连接测试 [mysql@db1 ~]$ mysql -h 192.168.1.60 -p 3306 -urep1 备注：这次连接需要密码了。之后再次观看 db1 同步日志，不再报错。
三 数据验证 主从切换后db2 为主节点， db1 为备节点，在 db2 节点上插入一条数据测试同步是否正常。--db2 上执行root@localhost:francs>insert into test_sr(id) values(30); query ok, 1 row affected (0.03 sec) root@localhost:francs>select * from test_sr order by id desc limit 1; +------+---------------------+ | id | create_time | +------+---------------------+ | 30 | 2015-03-02 15:19:53 | +------+---------------------+ 1 row in set (0.00 sec)
--db1 上验证root@localhost:francs>select * from test_sr order by id desc limit 1; +------+---------------------+ | id | create_time | +------+---------------------+ | 30 | 2015-03-02 15:19:53 | +------+---------------------+ 1 row in set (0.00 sec)
备注：数据同步正常，以上是对 mysql 主备切换的初次了解，后续再补充。
http://www.bkjia.com/phpjc/1128996.htmlwww.bkjia.comtruehttp://www.bkjia.com/phpjc/1128996.htmltecharticlemysql数据库主从库配置之主从切换 mysql：主从复制(replication)搭建 介绍了 mysql 主从配置过程，这篇 介绍手工主从切换过程。 一 环境信息 主...