mysql 主从切换

1、修改配置文件 read-only=1（主库） #read-only=1（备库） 2、查询从库状态 mysql> show processlist ; +—-+————-+———–+——+———+——+—————————————————————————–+——————+ | Id | User | Host | db | Command | Time | State | Info | +—-+————-+———–+——+———+——+—————————————————————————–+——————+ | 1 | root | localhost | ecp | Query | 0 | NULL | show processlist | | 4 | system user | | NULL | Connect | 2 | Waiting for master to send event | NULL | | 5 | system user | | NULL | Connect | 2 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | +—-+————-+———–+——+———+——+—————————————————————————–+——————+ 3 rows in set (0.00 sec) mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.1.2 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 107 Relay_Log_File: replicate.000007 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000004 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: 107 Relay_Log_Space: 549 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: Replicate_Ignore_Server_Ids: Master_Server_Id: 2 1 row in set (0.00 sec) 3、查询主库状态 mysql> show processlist; +—-+——+——————-+——+————-+——+———————————————————————–+——————+ | Id | User | Host | db | Command | Time | State | Info | +—-+——+——————-+——+————-+——+———————————————————————–+——————+ | 1 | root | localhost | ecp | Query | 0 | NULL | show processlist | | 2 | repl | 192.168.1.4:17948 | NULL | Binlog Dump | 6 | Master has sent all binlog to slave; waiting for binlog to be updated | NULL | +—-+——+——————-+——+————-+——+———————————————————————–+——————+ 2 rows in set (0.00 sec) mysql> show master status \G *************************** 1. row *************************** File: mysql-bin.000004 Position: 107 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) 4、从库操作 mysql> STOP SLAVE IO_THREAD; Query OK, 0 rows affected (0.04 sec) mysql> SHOW PROCESSLIST; +—-+————-+———–+——+———+——+—————————————————————————–+——————+ | Id | User | Host | db | Command | Time | State | Info | +—-+————-+———–+——+———+——+—————————————————————————–+——————+ | 1 | root | localhost | ecp | Query | 0 | NULL | SHOW PROCESSLIST | | 5 | system user | | NULL | Connect | 256 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL | +—-+————-+———–+——+———+——+—————————————————————————–+——————+ 2 rows in set (0.00 sec) 确保状态为：has read all relay log mysql> show slave status \G *************************** 1. row *************************** Slave_IO_State: Master_Host: 192.168.1.2 Master_User: repl Master_Port: 3306 Connect_Retry: 60 Master_Log_File: mysql-bin.000004 Read_Master_Log_Pos: 107 Relay_Log_File: replicate.000007 Relay_Log_Pos: 253 Relay_Master_Log_File: mysql-bin.000004 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: 107 Relay_Log_Space: 549 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: 2 1 row in set (0.00 sec) 5、查询主库状态 mysql> show master status \G *************************** 1. row *************************** File: mysql-bin.000004 Position: 107 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) 6、从库变主库 mysql> STOP SLAVE; Query OK, 0 rows affected (0.00 sec) mysql> RESET MASTER; Query OK, 0 rows affected (0.02 sec) mysql> RESET SLAVE; Query OK, 0 rows affected (0.03 sec) mysql> show master status \G *************************** 1. row *************************** File: mysql-bin.000001 Position: 107 Binlog_Do_DB: Binlog_Ignore_DB: 1 row in set (0.00 sec) 7、主库变从库 mysql> RESET MASTER; Query OK, 0 rows affected (0.06 sec) mysql> RESET SLAVE; Query OK, 0 rows affected (0.03 sec) mysql> CHANGE MASTER TO -> MASTER_HOST=’192.168.1.4′, -> MASTER_USER=’repl’, -> MASTER_PASSWORD=’xifenfei’, -> MASTER_LOG_FILE=’mysql-bin.000001′, -> MASTER_LOG_POS=107; Query OK, 0 rows affected (0.05 sec) mysql> start slave; Query OK, 0 rows affected (0.00 sec) 8、重启主和从库 [root@localhost mysql]# service mysql restart Shutting down MySQL….[ OK ] Starting MySQL…………….[ OK ] 9、检查主从是否都正常 主库 SHOW PROCESSLIST; show master status \G 从库 SHOW PROCESSLIST; start slave; show slave status \G 如果有错误，根据错误提示，解决问题