mysql主從複製replication的一些相關命令

標籤：

 

 

主伺服器上的相關命令：
show master status;

mysql> show master status\G*************************** 1. row ***************************             File: host2-bin.000002         Position: 420     Binlog_Do_DB:  Binlog_Ignore_DB: Executed_Gtid_Set: 5abd564e-2b4f-11e5-a7f1-000c2954ccde:1,780aa541-2b93-11e5-a9ad-000c29ef36a8:11 row in set (0.00 sec)

 

show slave hosts;

mysql> show slave hosts;+-----------+-----------------+------+-----------+--------------------------------------+| Server_id | Host            | Port | Master_id | Slave_UUID                           |+-----------+-----------------+------+-----------+--------------------------------------+|         1 | 192.168.203.133 | 3306 |         2 | 780aa541-2b93-11e5-a9ad-000c29ef36a8 ||         3 | 192.168.203.134 | 3306 |         2 | 5abd564e-2b4f-11e5-a7f1-000c2954ccde |+-----------+-----------------+------+-----------+--------------------------------------+2 rows in set (0.00 sec)

 

show binary logs;

mysql> show binary logs;+------------------+-----------+| Log_name         | File_size |+------------------+-----------+| host2-bin.000001 |       413 || host2-bin.000002 |       420 |+------------------+-----------+2 rows in set (0.00 sec)

 

show binlog events;

mysql> show binlog events;+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+| Log_name         | Pos | Event_type     | Server_id | End_log_pos | Info                                                              |+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+| host2-bin.000001 |   4 | Format_desc    |         2 |         120 | Server ver: 5.6.16-log, Binlog ver: 4                             || host2-bin.000001 | 120 | Previous_gtids |         2 |         151 |                                                                   || host2-bin.000001 | 151 | Gtid           |         1 |         199 | SET @@SESSION.GTID_NEXT= ‘780aa541-2b93-11e5-a9ad-000c29ef36a8:1‘ || host2-bin.000001 | 199 | Query          |         1 |         262 | BEGIN                                                             || host2-bin.000001 | 262 | Table_map      |         1 |         309 | table_id: 70 (testdb.t1)                                          || host2-bin.000001 | 309 | Delete_rows    |         1 |         359 | table_id: 70 flags: STMT_END_F                                    || host2-bin.000001 | 359 | Xid            |         1 |         390 | COMMIT /* xid=423 */                                              || host2-bin.000001 | 390 | Stop           |         2 |         413 |                                                                   |+------------------+-----+----------------+-----------+-------------+-------------------------------------------------------------------+8 rows in set (0.00 sec)

下面這幾個是刪除二進位相關的，不作示範，具體可以搜尋一下用法：

purge logs to ‘log_name‘;
purge logs before ‘date‘;
reset master;(老版本flush master)
set sql_log_bin=

 

從伺服器上的相關命令:
slave start
slave stop
SLAVE STOP IO_THREAD //此線程把master段的日誌寫到本地
SLAVE start IO_THREAD
SLAVE STOP SQL_THREAD //此線程把寫到本地的日誌應用於資料庫
SLAVE start SQL_THREAD
reset slave
SET GLOBAL SQL_SLAVE_SKIP_COUNTER
load data from master
show slave status(SUPER,REPLICATION CLIENT)
CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,MASTER_USER=, MASTER_PASSWORD= //動態改變master資訊
PURGE MASTER [before ‘date‘] 刪除master端已同步過的日誌

mysql主從複製replication的一些相關命令