mysql 複製 1x 主，1x 從

我剛剛設置了一台主伺服器和一台從伺服器，但它不起作用..

在我的網站上，我連接到從屬伺服器並插入了一些行，但它們沒有出現在主伺服器上，反之亦然.. 出了什麼問題？

這就是我所做的：

Master:

-> /etc/mysql/my.cnf

[mysqld]
log-bin         = mysql-master-bin
server-id=1
# bind-address      = 127.0.0.1
binlog-do-db        = test_db


Slave:

-> /etc/mysql/my.cnf

[mysqld]
log-bin         = mysql-slave-bin
server-id=2
# bind-address      = 127.0.0.1
replicate-do-db     = test_db


Slave:

terminal 0 >
mysql> STOP SLAVE; // and drop tables


Master:

terminal 1 >
mysql> CREATE USER 'repl_slave'@'slave_ip' IDENTIFIED BY 'repl_pass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl_slave'@'slave_ip';
mysql> FLUSH PRIVILEGES;
mysql> FLUSH TABLES WITH READ LOCK;
-- leave terminal open

terminal 2 >
shell> mysqldump -u root -pPASSWORD test_db --lock-all-tables > dump.sql

mysql> SHOW MASTER STATUS;


Slave:

terminal 3 >
shell> mysql -u root -pPASSWORD test_db < dump.sql

terminal 0 >
mysql> CHANGE MASTER TO
mysql> MASTER_HOST='master_ip',
mysql> MASTER_USER='repl_slave',
mysql> MASTER_PASSWORD='repl_pass',
mysql> MASTER_PORT=3306,
mysql> MASTER_LOG_FILE='mysql-master-bin.000003',   // terminal 2 > SHOW MASTER STATUS
mysql> MASTER_LOG_POS=4,                        // terminal 2 > SHOW MASTER STATUS
mysql> MASTER_CONNECT_RETRY=10;

mysql> START SLAVE;

mysql> SHOW SLAVE STATUS;

這是奴隸狀態：

Array
(
   [Slave_IO_State] => Waiting for master to send event
   [Master_Host] => xx.xx.xx.xx
   [Master_User] => repl_slave
   [Master_Port] => 3306
   [Connect_Retry] => 10
   [Master_Log_File] => mysql-master-bin.000003
   [Read_Master_Log_Pos] => 106
   [Relay_Log_File] => mysqld-relay-bin.000002
   [Relay_Log_Pos] => 258
   [Relay_Master_Log_File] => mysql-master-bin.000003
   [Slave_IO_Running] => Yes
   [Slave_SQL_Running] => Yes
   [Replicate_Do_DB] => test_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] => 106
   [Relay_Log_Space] => 414
   [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] => 
)

根據您的從屬狀態，複製已啟動並正常執行。可能會被打破的是你的期望。MySQL 複製是一種從 master 到 slave 的單向複製，無需驗證 slave 上的一致性。第三方在從伺服器上更改的行不會被複製回主伺服器，但顯然會影響從伺服器數據的一致性。

在大多數情況下，如果不執行額外的手動檢查，您將不會注意到從站上更改的數據行 - 看看Percona Toolkit（以前稱為 Maatkit）的工具確實有助於這項任務。

您還應該從您的配置中刪除 replicate_do_db，因為此過濾選項可能沒有按照您的想法進行。除此之外，它應該開箱即用。如果沒有，請發布您用於驗證數據複製的 SQL 語句。

引用自：https://serverfault.com/questions/403850