MySQL主從切換
1、檢視從庫資料庫狀態：
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 2 | root | 127.0.0.1:59012 | sakila | Query | 0 | NULL | show processlist |
| 3 | system user | | NULL | Connect | 17618 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 17305 | 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: 172.16.33.143
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql.000003
Read_Master_Log_Pos: 3296928
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 3296845
Relay_Master_Log_File: mysql.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test
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: 3296928
Relay_Log_Space: 3297001
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: 1
1 row in set (0.00 sec)
mysql> show processlist;
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
| 1 | root | 127.0.0.1:33623 | sakila | Query | 0 | NULL | show processlist |
| 2 | master | 172.16.33.144:48988 | NULL | Binlog Dump | 17455 | 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;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000003 | 3296928 | | |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2、修改配置檔案：
主庫：read_only = 1
從庫：#read_only = 1
3、操作從庫：
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 2 | root | 127.0.0.1:59012 | sakila | Query | 0 | NULL | show processlist |
| 3 | system user | | NULL | Connect | 17770 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 17457 | 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> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 2 | root | 127.0.0.1:59012 | sakila | Query | 0 | NULL | show processlist |
| 4 | system user | | NULL | Connect | 17526 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
確認從庫的狀態為：Slave has read all relay log
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.33.143
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql.000003
Read_Master_Log_Pos: 3296928
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 3296845
Relay_Master_Log_File: mysql.000003
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test
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: 3296928
Relay_Log_Space: 3297001
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
1 row in set (0.00 sec)
4、檢視主庫狀態：
mysql> show master status;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000003 | 3296928 | | |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5、主從切換，從庫變主庫：
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000001 | 107 | | |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6、主從切換，主庫變從庫：
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='172.16.33.144',master_port=3306,master_user='master',master_password='123456',master_log_file=' mysql-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.04 sec)
7、重啟主從：
[root@yoon ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
8、檢視主從是否正常：
主庫：
show processlist;
show master status;
從庫：
show processlist;
start slave;
show slave status\G
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 2 | root | 127.0.0.1:59012 | sakila | Query | 0 | NULL | show processlist |
| 3 | system user | | NULL | Connect | 17618 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 17305 | 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: 172.16.33.143
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql.000003
Read_Master_Log_Pos: 3296928
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 3296845
Relay_Master_Log_File: mysql.000003
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test
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: 3296928
Relay_Log_Space: 3297001
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: 1
1 row in set (0.00 sec)
mysql> show processlist;
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+--------+---------------------+--------+-------------+-------+-----------------------------------------------------------------------+------------------+
| 1 | root | 127.0.0.1:33623 | sakila | Query | 0 | NULL | show processlist |
| 2 | master | 172.16.33.144:48988 | NULL | Binlog Dump | 17455 | 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;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000003 | 3296928 | | |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
2、修改配置檔案：
主庫：read_only = 1
從庫：#read_only = 1
3、操作從庫：
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 2 | root | 127.0.0.1:59012 | sakila | Query | 0 | NULL | show processlist |
| 3 | system user | | NULL | Connect | 17770 | Waiting for master to send event | NULL |
| 4 | system user | | NULL | Connect | 17457 | 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> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> show processlist;
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User | Host | db | Command | Time | State | Info |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 2 | root | 127.0.0.1:59012 | sakila | Query | 0 | NULL | show processlist |
| 4 | system user | | NULL | Connect | 17526 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL |
+----+-------------+-----------------+--------+---------+-------+-----------------------------------------------------------------------------+------------------+
2 rows in set (0.00 sec)
確認從庫的狀態為：Slave has read all relay log
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 172.16.33.143
Master_User: master
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql.000003
Read_Master_Log_Pos: 3296928
Relay_Log_File: slave-relay-bin.000002
Relay_Log_Pos: 3296845
Relay_Master_Log_File: mysql.000003
Slave_IO_Running: No
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB: test
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: 3296928
Relay_Log_Space: 3297001
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
1 row in set (0.00 sec)
4、檢視主庫狀態：
mysql> show master status;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000003 | 3296928 | | |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
5、主從切換，從庫變主庫：
mysql> slave stop;
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+--------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--------------+----------+--------------+------------------+
| mysql.000001 | 107 | | |
+--------------+----------+--------------+------------------+
1 row in set (0.00 sec)
6、主從切換，主庫變從庫：
mysql> reset master;
Query OK, 0 rows affected (0.02 sec)
mysql> reset slave;
Query OK, 0 rows affected (0.00 sec)
mysql> change master to master_host='172.16.33.144',master_port=3306,master_user='master',master_password='123456',master_log_file=' mysql-bin.000001',master_log_pos=107;
Query OK, 0 rows affected (0.04 sec)
7、重啟主從：
[root@yoon ~]# service mysqld restart
Shutting down MySQL. SUCCESS!
Starting MySQL.. SUCCESS!
8、檢視主從是否正常：
主庫：
show processlist;
show master status;
從庫：
show processlist;
start slave;
show slave status\G
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/28939273/viewspace-1782430/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 手工切換MySQL主從MySql
- mysql主從搭建切換MySql
- mysql for linux主從切換MySqlLinux
- mysql主從複製+主備切換MySql
- Redis主從切換Redis
- 5.6 MySql主從自動切換指令碼MySql指令碼
- mysql mha 主從自動切換 高可用MySql
- MySQL 主從切換延時高問題分析MySql
- Redis sentinel主從切換Redis
- 一個月後，我們又從 MySQL 雙主切換成了主 - 從！MySql
- 實戰dataguard主從切換
- nginx keepalived 主從切換Nginx
- Mysql+keepalived主主切換薦MySql
- MYSQL5.6主從+keepalive高可用自動切換MySql
- mysql 5.7+keepalived主從切換步驟簡述MySql
- keepalived配置redis主從切換Redis
- StoneDB 主從切換實踐方案
- MySQL 主備庫切換記錄MySql
- REDIS主從頻繁切換事件排查Redis事件
- Mongodb資料同步和主從切換MongoDB
- Redis叢集的主從切換研究Redis
- oracle 11g datagurd主從切換Oracle
- Mysql 5.6 Master和Slave 主備切換MySqlAST
- MHA實現mysql主從資料庫手動切換的方法MySql資料庫
- MySQL從庫切換成主庫後的relay-bin問題MySql
- keepalived與mysql主主叢集自動切換MySql
- 生產環境中mysql資料庫由主從關係切換為主主關係MySql資料庫
- MySQL 5.7傳統複製到GTID線上切換（一主一從）MySql
- 【MySQL（二十二）】一主一從換主MySql
- Redis 哨兵模式實現主從故障互切換Redis模式
- (九)主題切換
- docker Redis單機主從哨兵模式切換失敗DockerRedis模式
- Dledger是如何實現主從自動切換的
- Redis+Keepalived主從熱備秒級切換Redis
- mysql主備切換canal出現的問題解析MySql
- postgresql10主從+keepalived高可用自動切換(切換關鍵在指令碼)SQL指令碼
- 主備切換(failover)AI
- ORACLE 12C DATAGUARD環境搭建和主從切換Oracle