單向同步複製需要注意的幾個引數及常用命令

log-bin=mysql-bin 預設是所有庫都寫二進位制日誌

binlog-do-db=db_name 需要寫二進位制日誌的庫
binlog-ignore-db=db_name 不需要寫二進位制日誌的庫

replicate-do-db=db_name 需要同步複製的庫
replicate-ignore-db=db_name 不需要同步複製的庫

replicate-wild-do-table=db_name.% 需要同步複製的表
replicate-wild-ignore-table=db_name.% 不需要同步複製的表，預設其他所有同步

binlog-do-db, binlog-ignore-db, replicate-do-db 和 replicate-ignore-db 來過濾複製(某些資料庫), 儘管有些使用, 他們是危險的.
對於很多的例項,有更安全的替換方案 replicate-wild-do-table=db_name.%
（具體原因：網上很多資料可以查到）

配置檔案：
master端：
[mysqld]
port = 3306
server-id = 1
replicate_wild_do_table=centreon_status.%
log-bin=mysql-bin

slave端：
[mysqld]
port = 3306
server-id = 2
master-host=192.168.9.146
master-user=replication
master-password=123456
replicate_wild_do_table=centreon_status.%
log-bin=mysql-bin

Master端：
mysql> show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000015 | 4386841 | | |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

檢視日誌：
mysql> SHOW BINLOG EVENTS in 'mysql-bin.000015' from 4386841;
+------------------+---------+-------------+-----------+-------------+-------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+---------+-------------+-----------+-------------+-------------------------------------------------------------------+
| mysql-bin.000015 | 4386841 | Query | 1 | 4386928 | BEGIN |
| mysql-bin.000015 | 4386928 | Table_map | 1 | 4387029 | table_id: 146 (centreon_status.nagios_programstatus) |
| mysql-bin.000015 | 4387029 | Update_rows | 1 | 4387245 | table_id: 146 flags: STMT_END_F |
| mysql-bin.000015 | 4387245 | Xid | 1 | 4387272 | COMMIT /* xid=23872 */ |
| mysql-bin.000015 | 4387272 | Query | 1 | 4387359 | BEGIN |
| mysql-bin.000015 | 4387359 | Table_map | 1 | 4387460 | table_id: 146 (centreon_status.nagios_programstatus) |
| mysql-bin.000015 | 4387460 | Update_rows | 1 | 4387676 | table_id: 146 flags: STMT_END_F |
| mysql-bin.000015 | 4387676 | Xid | 1 | 4387703 | COMMIT /* xid=23876 */ |
| mysql-bin.000015 | 4387703 | Query | 1 | 4387790 | BEGIN |
| mysql-bin.000015 | 4387790 | Table_map | 1 | 4387923 | table_id: 140 (centreon_status.nagios_hoststatus) |
| mysql-bin.000015 | 4387923 | Update_rows | 1 | 4388673 | table_id: 140 flags: STMT_END_F |
| mysql-bin.000015 | 4388673 | Xid | 1 | 4388700 | COMMIT /* xid=23878 */ |
| mysql-bin.000015 | 4388700 | Query | 1 | 4388787 | BEGIN |
| mysql-bin.000015 | 4388787 | Table_map | 1 | 4388874 | table_id: 123 (centreon_status.nagios_customvariablestatus) |
| mysql-bin.000015 | 4388874 | Update_rows | 1 | 4388968 | table_id: 123 flags: STMT_END_F |
| mysql-bin.000015 | 4388968 | Xid | 1 | 4388995 | COMMIT /* xid=23879 */ |
| mysql-bin.000015 | 4388995 | Query | 1 | 4389082 | BEGIN |
| mysql-bin.000015 | 4389082 | Table_map | 1 | 4389169 | table_id: 123 (centreon_status.nagios_customvariablestatus) |
| mysql-bin.000015 | 4389169 | Update_rows | 1 | 4389271 | table_id: 123 flags: STMT_END_F |
| mysql-bin.000015 | 4389271 | Xid | 1 | 4389298 | COMMIT /* xid=23880 */ |
| mysql-bin.000015 | 4389298 | Query | 1 | 4389385 | BEGIN |
| mysql-bin.000015 | 4389385 | Table_map | 1 | 4389472 | table_id: 123 (centreon_status.nagios_customvariablestatus) |
| mysql-bin.000015 | 4389472 | Update_rows | 1 | 4389586 | table_id: 123 flags: STMT_END_F |
| mysql-bin.000015 | 4389586 | Xid | 1 | 4389613 | COMMIT /* xid=23881 */ |

Slave端：
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='192.168.9.146', MASTER_PORT=3306, MASTER_USER='replication', MASTER_PASSWORD='123456', MASTER_LOG_FILE='mysql-bin.000015', MASTER_LOG_POS=2762078;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.9.146
Master_User: replication
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000015
Read_Master_Log_Pos: 4502237
Relay_Log_File: mysqld-relay-bin.000002
Relay_Log_Pos: 1740410
Relay_Master_Log_File: mysql-bin.000015
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table: centreon_status.%
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 4502237
Relay_Log_Space: 1740566
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:
1 row in set (0.00 sec)

ERROR:
No query specified

mysql>

同步時需要初始化資料，這時候可以把主庫的表鎖住，這樣就不會出現初始化資料不一致：

備份master資料庫資料
mysql> flush tables with read lock; //不要退出這個終端，否則這個鎖就不生效了。從伺服器的資料庫建好後。在主伺服器執行解鎖

同時要記錄下mysql-bin.000003和1271
mysql> show master status;
+------------------+----------+--------------+-------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+-------------------------------+
| mysql-bin.000002 | 106 | blog,www | mysql,test,information_schema |
+------------------+----------+--------------+-------------------------------+
1 row in set (0.01 sec)
取得快照並記錄日誌名和偏移量

開啟另一個終端對主伺服器資料目錄做快照。
#cd /usr/local/mysql/data
#tar -zcvf backup.tar.gz blog

此時在主庫解開table的鎖定
mysql> unlock tables;

告訴主伺服器，如果當前的資料庫(即USE選定的資料庫)是db_name，應將更新記錄到二進位制日誌中。其它所有沒有明顯指定的資料庫被忽略。如果使用該選項，你應確保只對當前的資料庫進行 ...

例如你設定了
binlog-do-db=db1

然後你執行了以下語句
use db1;
create database db2;
第二句語句並不會記入log
即使當前資料庫是db1，因為考慮的是語句中的資料庫名；

相反，除了CREATE DATABASE, ALTER DATABASE, and DROP DATABASE 以外的語句是對當前資料庫有關的。

例如你設定了
binlog-do-db=db1

然後你執行了以下語句
use db2;
insert into db1.table1 values(1);
這條語句是不會計入log的，因為當前db是db2。而與操作db無關。

單向同步複製需要注意的幾個引數及常用命令

相關文章