主從環境中,從IO程式被停掉
主從環境中 ,如果主中的binlog已經被從讀到中繼日誌,但是還沒有被sql應用,這時從庫上的從IO程式停掉了,sql程式仍然會把已經讀到中繼日誌中的內容應用:
也就是說這兩個是獨立的。
例:
在從庫上
mysql> stop slave SQL_THREAD;
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.118.161
Master_User: song
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: 1.000021
Read_Master_Log_Pos: 337
Relay_Log_File: a.000020
Relay_Log_Pos: 392
Relay_Master_Log_File: 1.000021
Slave_IO_Running: Yes
Slave_SQL_Running: No
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: 337
Relay_Log_Space: 706
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
Master_UUID: dab340ff-d2cc-11e4-a6c8-000c292f3ddd
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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)
在主上執行:
mysql> create table test(id int);
Query OK, 0 rows affected (0.04 sec)
mysql> insert into test values (1);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values (2);
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
在從上檢視
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.118.161
Master_User: song
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: 1.000021
Read_Master_Log_Pos: 816 --這個值增加了
Relay_Log_File: a.000020
Relay_Log_Pos: 392
Relay_Master_Log_File: 1.000021
Slave_IO_Running: Yes
Slave_SQL_Running: No
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: 337 --這個值沒變,因為sql程式是停著的
Relay_Log_Space: 1185
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
Master_UUID: dab340ff-d2cc-11e4-a6c8-000c292f3ddd
Master_Info_File: /var/lib/mysql/master.info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
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)
mysql>
mysql> stop slave IO_THREAD;
Query OK, 0 rows affected (0.01 sec)
mysql> start slave SQL_THREAD;
Query OK, 0 rows affected (0.03 sec)
mysql> SHOW SLAVE STATUS \G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.118.161
Master_User: song
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: 1.000021
Read_Master_Log_Pos: 816
Relay_Log_File: a.000020
Relay_Log_Pos: 871
Relay_Master_Log_File: 1.000021
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: 816 --應用了sql
Relay_Log_Space: 1185
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
Master_UUID: dab340ff-d2cc-11e4-a6c8-000c292f3ddd
Master_Info_File: /var/lib/mysql/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)
mysql> select count(*) from test; --資料已經被同步過來
+----------+
| count(*) |
+----------+
| 2 |
+----------+
1 row in set (0.30 sec)
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/25099483/viewspace-1479359/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- Linux環境中MySQL主從同步–新增新的從庫LinuxMySql主從同步
- 新環境搭建Mysql主從MySql
- 用 Docker 構建 MySQL 主從環境DockerMySql
- Docker環境搭建redis叢集(主從模式)DockerRedis模式
- Windows 環境下,MySQL 的主從複製和主主複製WindowsMySql
- windows環境下,Mysql的主從複製和主主複製WindowsMySql
- Redis多例項及主從複製環境搭建Redis
- 生產環境中mysql資料庫由主從關係切換為主主關係MySql資料庫
- 基於docker環境下搭建redis主從叢集DockerRedis
- pg升級(pg14-pg15)主從環境
- ORACLE 12C DATAGUARD環境搭建和主從切換Oracle
- 如何基於生產環境mysql 5.6.25主從部署新的mysql從庫操作指南MySql
- CentOS7.8 環境搭建 Redis 主從複製和哨兵模式CentOSRedis模式
- 揭祕MySQL 主從環境中大事務的傳奇事蹟MySql
- mysql主從中,從和主資料相差較多MySql
- GTID環境中手動修復主從故障一例(Error 1146)Error
- 從環境搭建到打包使用TypeScriptTypeScript
- VUE從零開始環境搭建Vue
- MySQL主從同步(一主一從、一主多從、主從從)等結構的概述與配置MySql主從同步
- 主從環境下升級(先升級從庫,不做切換,需要停業務,auto_position=1)
- MySQL Xtrabackup真實生產環境搭建主從複製全過程MySql
- CentOS 7.9 環境下搭建k8s叢集(一主兩從)CentOSK8S
- redis持久化策略梳理及主從環境下的策略調整記錄Redis持久化
- 生產環境故障處理演練-mysql資料庫主從恢復MySql資料庫
- 從零搭建 Node.js 線上環境Node.js
- Bash 是如何從環境變數中匯入函式的變數函式
- MySQL 主從配置-之-一主一從MySql
- 手把手教你,如何在windows系統搭建mysql主從複製的環境WindowsMySql
- 分分鐘搭建MySQL一主多從環境(r12筆記第31天)MySql筆記
- 筆記: 環境 - Postgre從安裝到使用筆記
- 從0開始搭建preact開發環境React開發環境
- 從零搭建嵌入式開發環境開發環境
- 從零開始搭建React全家桶環境React
- 從零搭建和配置OSX開發環境開發環境
- 從0開始搭建自動部署環境
- Eclipse從零配置Maven環境 筆記EclipseMaven筆記
- 從零自學Hadoop(02):環境準備Hadoop
- mysql主從複製(一):一主多從MySql