MySQL Replication Master-Slave筆記

Master(my.cnf)192.168.1.203
server-id               = 1
log_bin                 = /var/log/mysql/mysql-bin.log

binlog_format = MIXED
要同步的DB
binlog_do_db = test
binlog_do_db = test2
innodb_flush_log_at_trx_commit=1
sync_binlog=1

mysql -u root -p
新增帳號給SLAVE同步使用
mysql>GRANT REPLICATION SLAVE ON *.* TO 'rep' IDENTIFIED BY 'pass';
or
mysql -u root -p -e "GRANT REPLICATION SLAVE ON *.* TO 'rep' IDENTIFIED BY 'pass';"
先鎖定資料庫
mysql -u root -p -e "FLUSH TABLES WITH READ LOCK;"
mysql -u root -p -e "SHOW MASTER STATUS\G;"
+------------------+----------+--------------+------------------+
| File Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000002 |      107 | test         |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
記下File與Position，等下會用到
quit

匯出要同步的那個資料庫
mysqldump -uroot -p --flush-logs --opt --master-data test > test.sql

解除鎖定

mysql -u root -p -e "UNLOCK TABLES;"

此時MASTER上的工作結束。

Slave(my.cnf)192.168.1.204

server-id               = 2

要同步的DB
replicate_wild_do_table = test.%

mysql -uroot -p -e "STOP SLAVE; RESET SLAVE;"

mysql -uroot -p -e "CHANGE MASTER TO MASTER_HOST='192.168.1.204',MASTER_USER='rep',MASTER_PASSWORD='pass',
MASTER_LOG_FILE='mysql-bin.000002',MASTER_LOG_POS=107;"

mysql -uroot -p -e "START SLAVE;"

檢視MASTER的狀態
mysql -uroot -p -e "SHOW MASTER STATUS;"
+------------------+----------+--------------+------------------+
| File|Position| Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000001 |      107 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

檢視SLAVE的狀態
mysql -uroot -p -e "SHOW SLAVE STATUS\G;"
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.1.204
Master_User: rep
Master_Port: 3306
......
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
......

運作正常，此時可到MASTER上新增資料看是否立即同步至SLAVE