建站
咨詢
售后
建站咨詢
新聞中心
這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷(xiāo)解決方案
MySQL5.5常用的復(fù)制環(huán)境管理命令
1、檢查Slave節(jié)點(diǎn)的各個(gè)狀態(tài)
顯示slave線程的重要參數(shù)。
MySQL> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.78.139
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 414
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 560
Relay_Master_Log_File: mysql-bin.000012
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: 414
Relay_Log_Space: 717
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: 100
1 row in set (0.00 sec)
Slave_IO_State 這個(gè)字段是SHOW PROCESSLIST輸出結(jié)果中State字段的拷貝。這個(gè)字段告訴你線程正在做什么。
Slave_IO_Running 指示I/O線程是否啟動(dòng)并成功連接到Master。
Slave_SQL_Running 指示SQL線程是否啟動(dòng)
Last_IO_Errno, Last_IO_Error 導(dǎo)致I/O線程停止運(yùn)行的錯(cuò)誤號(hào)和錯(cuò)誤日志，如果Last_IO_Error里面的值不是空值，則相關(guān)的錯(cuò)誤也會(huì)出現(xiàn)在slave節(jié)點(diǎn)的錯(cuò)誤日志里。
Last_SQL_Errno, Last_SQL_Error 導(dǎo)致SQL線程停止運(yùn)行的錯(cuò)誤號(hào)和錯(cuò)誤日志，錯(cuò)誤號(hào)為0且錯(cuò)誤日志為空，說(shuō)明沒(méi)有錯(cuò)誤。
Seconds_Behind_Master Slave節(jié)點(diǎn)與Master節(jié)點(diǎn)的延遲時(shí)間。當(dāng)Slave節(jié)點(diǎn)正在進(jìn)行更新操作的時(shí)候，這個(gè)字段會(huì)顯示Slave節(jié)點(diǎn)上面現(xiàn)有時(shí)間和Master節(jié)點(diǎn)對(duì)同一事件處理時(shí)間的差異。當(dāng)Slave節(jié)點(diǎn)沒(méi)有事件的時(shí)候，這個(gè)值為0。
Master_Log_File I/O線程正在讀取的Master節(jié)點(diǎn)的二進(jìn)制日志的名稱
Read_Master_Log_Pos I/O線程正在讀取的Master節(jié)點(diǎn)的二進(jìn)制日志的位置
Relay_Master_Log_File SQL線程最近執(zhí)行過(guò)的event（事件）所在的Master二進(jìn)制日志文件名
Exec_Master_Log_Pos SQL線程讀取和執(zhí)行的Master節(jié)點(diǎn)二進(jìn)制日志的位置，下一個(gè)事務(wù)或事件將從這個(gè)位置開(kāi)始。當(dāng)你執(zhí)行CHANGE MASTER TO語(yǔ)句的時(shí)候，可以使用這個(gè)字段的值。
Relay_Log_File SQL線程正在讀取和執(zhí)行的relay log名稱
Relay_Log_Pos SQL線程正在讀取和執(zhí)行的relay log的位置
2、顯示線程狀態(tài)
Master節(jié)點(diǎn)的線程狀態(tài)
mysql> show processlist\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
*************************** 1. row ***************************
Id: 2
User: repl
Host: 192.168.78.137:42524
db: NULL
Command: Binlog Dump
Time: 63044
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Slave節(jié)點(diǎn)的線程狀態(tài)
mysql> show processlist\G
*************************** 1. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 62933
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 62831
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3、顯示當(dāng)前有多少個(gè)Slave節(jié)點(diǎn)注冊(cè)到Master節(jié)點(diǎn)，在Master節(jié)點(diǎn)執(zhí)行
mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
| 200 | | 3306 | 100 |
+-----------+------+------+-----------+
1 row in set (0.00 sec)
4、停止slave線程。執(zhí)行這個(gè)命令需要SUPER權(quán)限。推薦的最佳實(shí)踐是在關(guān)閉Slave節(jié)點(diǎn)數(shù)據(jù)庫(kù)服務(wù)之前執(zhí)行STOP SLAVE這個(gè)命令。
mysql> stop slave;
Query OK, 0 rows affected (0.14 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.78.139
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 414
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 560
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: No
Slave_SQL_Running: No
可以單獨(dú)停止某個(gè)線程
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
5、啟動(dòng)兩個(gè)slave線程。I/O線程負(fù)責(zé)從master服務(wù)器中讀取事件（events）并將它們儲(chǔ)存到relay log中。SQL線程負(fù)責(zé)從relay log中讀取事件并執(zhí)行它們。執(zhí)行START SLAVE需要SUPER權(quán)限。
mysql> start slave;
Query OK, 0 rows affected (0.08 sec)
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
可以單獨(dú)啟動(dòng)某個(gè)線程
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
本文題目：MySQL5.5常用的復(fù)制環(huán)境管理命令
文章轉(zhuǎn)載：http://www.dlmjj.cn/article/ihsjsh.html
顯示slave線程的重要參數(shù)。
MySQL> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.78.139
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 414
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 560
Relay_Master_Log_File: mysql-bin.000012
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: 414
Relay_Log_Space: 717
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: 100
1 row in set (0.00 sec)
Slave_IO_State 這個(gè)字段是SHOW PROCESSLIST輸出結(jié)果中State字段的拷貝。這個(gè)字段告訴你線程正在做什么。
Slave_IO_Running 指示I/O線程是否啟動(dòng)并成功連接到Master。
Slave_SQL_Running 指示SQL線程是否啟動(dòng)
Last_IO_Errno, Last_IO_Error 導(dǎo)致I/O線程停止運(yùn)行的錯(cuò)誤號(hào)和錯(cuò)誤日志，如果Last_IO_Error里面的值不是空值，則相關(guān)的錯(cuò)誤也會(huì)出現(xiàn)在slave節(jié)點(diǎn)的錯(cuò)誤日志里。
Last_SQL_Errno, Last_SQL_Error 導(dǎo)致SQL線程停止運(yùn)行的錯(cuò)誤號(hào)和錯(cuò)誤日志，錯(cuò)誤號(hào)為0且錯(cuò)誤日志為空，說(shuō)明沒(méi)有錯(cuò)誤。
Seconds_Behind_Master Slave節(jié)點(diǎn)與Master節(jié)點(diǎn)的延遲時(shí)間。當(dāng)Slave節(jié)點(diǎn)正在進(jìn)行更新操作的時(shí)候，這個(gè)字段會(huì)顯示Slave節(jié)點(diǎn)上面現(xiàn)有時(shí)間和Master節(jié)點(diǎn)對(duì)同一事件處理時(shí)間的差異。當(dāng)Slave節(jié)點(diǎn)沒(méi)有事件的時(shí)候，這個(gè)值為0。
Master_Log_File I/O線程正在讀取的Master節(jié)點(diǎn)的二進(jìn)制日志的名稱
Read_Master_Log_Pos I/O線程正在讀取的Master節(jié)點(diǎn)的二進(jìn)制日志的位置
Relay_Master_Log_File SQL線程最近執(zhí)行過(guò)的event（事件）所在的Master二進(jìn)制日志文件名
Exec_Master_Log_Pos SQL線程讀取和執(zhí)行的Master節(jié)點(diǎn)二進(jìn)制日志的位置，下一個(gè)事務(wù)或事件將從這個(gè)位置開(kāi)始。當(dāng)你執(zhí)行CHANGE MASTER TO語(yǔ)句的時(shí)候，可以使用這個(gè)字段的值。
Relay_Log_File SQL線程正在讀取和執(zhí)行的relay log名稱
Relay_Log_Pos SQL線程正在讀取和執(zhí)行的relay log的位置
2、顯示線程狀態(tài)
Master節(jié)點(diǎn)的線程狀態(tài)
mysql> show processlist\G
ERROR 2006 (HY000): MySQL server has gone away
No connection. Trying to reconnect...
Connection id: 4
Current database: *** NONE ***
*************************** 1. row ***************************
Id: 2
User: repl
Host: 192.168.78.137:42524
db: NULL
Command: Binlog Dump
Time: 63044
State: Master has sent all binlog to slave; waiting for binlog to be updated
Info: NULL
Slave節(jié)點(diǎn)的線程狀態(tài)
mysql> show processlist\G
*************************** 1. row ***************************
Id: 2
User: system user
Host:
db: NULL
Command: Connect
Time: 62933
State: Waiting for master to send event
Info: NULL
*************************** 2. row ***************************
Id: 3
User: system user
Host:
db: NULL
Command: Connect
Time: 62831
State: Slave has read all relay log; waiting for the slave I/O thread to update it
Info: NULL
3、顯示當(dāng)前有多少個(gè)Slave節(jié)點(diǎn)注冊(cè)到Master節(jié)點(diǎn)，在Master節(jié)點(diǎn)執(zhí)行
mysql> SHOW SLAVE HOSTS;
+-----------+------+------+-----------+
| Server_id | Host | Port | Master_id |
+-----------+------+------+-----------+
| 200 | | 3306 | 100 |
+-----------+------+------+-----------+
1 row in set (0.00 sec)
4、停止slave線程。執(zhí)行這個(gè)命令需要SUPER權(quán)限。推薦的最佳實(shí)踐是在關(guān)閉Slave節(jié)點(diǎn)數(shù)據(jù)庫(kù)服務(wù)之前執(zhí)行STOP SLAVE這個(gè)命令。
mysql> stop slave;
Query OK, 0 rows affected (0.14 sec)
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 192.168.78.139
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 414
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 560
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: No
Slave_SQL_Running: No
可以單獨(dú)停止某個(gè)線程
mysql> stop slave io_thread;
Query OK, 0 rows affected (0.00 sec)
5、啟動(dòng)兩個(gè)slave線程。I/O線程負(fù)責(zé)從master服務(wù)器中讀取事件（events）并將它們儲(chǔ)存到relay log中。SQL線程負(fù)責(zé)從relay log中讀取事件并執(zhí)行它們。執(zhí)行START SLAVE需要SUPER權(quán)限。
mysql> start slave;
Query OK, 0 rows affected (0.08 sec)
mysql> stop slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
可以單獨(dú)啟動(dòng)某個(gè)線程
mysql> start slave sql_thread;
Query OK, 0 rows affected (0.00 sec)
mysql> start slave io_thread;
Query OK, 0 rows affected (0.00 sec)
本文題目：MySQL5.5常用的復(fù)制環(huán)境管理命令
文章轉(zhuǎn)載：http://www.dlmjj.cn/article/ihsjsh.html
