mysql主从搭建就不说了，本篇主要说一下主从情况下，如果主宕机或其他原因导致无法服务时，将主从位置切换。从升级为主代替服务。主暂时变为从，只读。
1.首先从库上面：
##查看是否数据全部同步完成，如出现如下提示信息，则说明数据同步结束。则可以进行后面操作了。
mysql > show processlist; +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+ | Id | User | Host | db | Command | Time | State | Info | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+ | 1 | system user | | NULL | Connect | 598 | Has read all relay log; waiting for the slave I/O thread to update it |</span> NULL | | 2 | system user | | NULL | Connect | 858 | Waiting for master to send event| NULL | | 3 | root | localhost | NULL | Query | 0 | NULL | show processlist | +----+-------------+-----------+------+---------+------+-----------------------------------------------------------------------+------------------+ 3 rows in set (0.00 sec)
2.主库上面检查是否数据已暂停读写，等待update的状态
mysql> show processlist;
+----+--------+-----------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+-----------------------+------+-------------+------+----------------------------------------------------------------+------------------+
| 1 | rep132 | 192.168.199.132:51413 | NULL | Binlog Dump | 1069 | Has sent all binlog to slave; waiting for binlog to be updated| NULL |
| 2 | root | localhost | NULL | Query | 0 | NULL | show processlist |
+----+--------+-----------------------+------+-------------+------+----------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)2.1修改主的配置文件，设置为只读
vim /etc/my.cnf read_only = 1
2.3主库设为只读
mysql>set global read_only=1;
3.从库上停掉IO thread,切换为主的角色
mysql> stop slave io_thread;
mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.199.132 Master_User: rep131 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000001 Read_Master_Log_Pos: 886 Relay_Log_File: pxebackup-relay-bin.000002 Relay_Log_Pos: 893 Relay_Master_Log_File: mysql-bin.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: 886 Relay_Log_Space: 1052 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: 1 row in set (0.00 sec)
mysql>stop slave; mysql>reset master; mysql>reset slave; mysql> show master status; +------------------+----------+--------------+------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | +------------------+----------+--------------+------------------+ | mysql-bin.000001 | 106 | | | +------------------+----------+--------------+------------------+ 1 row in set (0.00 sec)
4.主上切换为从
mysql>reset master; mysql>reset slave; mysql>change master to master_host='192.168.199.131',master_port=3306,master_user='rep131',master_password='rep131',master_log_file=' mysql-bin.000001',master_log_pos=106; Query OK, 0 rows affected (0.04 sec) mysql>flush privileges; mysql>start slave; mysql> show slave status\G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.199.131 Master_User: rep132 Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000002 Read_Master_Log_Pos: 106 Relay_Log_File: localhost-relay-bin.000005 Relay_Log_Pos: 251 Relay_Master_Log_File: mysql-bin.000002 Slave_IO_Running: Yes 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: 106 Relay_Log_Space: 555 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: 0 Master_SSL_Verify_Server_Cert: No Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: 1 row in set (0.00 sec) mysql>show processlist; ##查看是否在同步。
至此结束。
本文由 Mr Gu 创作，采用 知识共享署名4.0 国际许可协议进行许可
本站文章除注明转载/出处外，均为本站原创或翻译，转载前请务必署名
最后编辑时间为: Aug 26, 2016 at 09:24 pm