mysql replication, 同步 database

MYSQL的master/slave資料同步配置
How to Set Up Replication
Mysql Replication 如何設定覆寫機制
這邊設定的replication, 只有單向, 兩個database, 一個是master 一個是slave

master 資料變動的時候, slave 會跟著變, 反之則否

Step1:
(In master)
在master 上開一個有 replication slave 權限的user 叫 slave_user, password 是 slave_password, 而且只有從 192.168.11.103 來的能用<br />grant replication slave on *.* to slave_user@192.168.11.103 identified by 'slave_password';<br />
Step2 :
(In master)
設定/etc/mysql/my.cnf<br />server-id = 1<br />log_bin = /var/log/mysql/mysql-bin.log<br />binlog_do_db = cake_dev<br /><br />innodb_flush_log_at_trx_commit=1<br />sync_binlog=1<br />

Step3
(In slave)
設定 my.cnf <br />server-id = 2<br /># The replication master for this slave - required<br />master-host = 192.168.11.187<br /><br /># The username the slave will use for authentication when connecting to the master - required<br />master-user = slave_user<br /><br /># The password the slave will authenticate with when connecting to the master - required<br />master-password = slave_password<br /><br /># The port the master is listening on. optional - defaults to 3306<br />master-port = 3306<br /><br />master-connect-retry = 60<br />replicate-do-db=cake_dev<br />

Step4
(In master and slave)
在同步之前, 要先把master 已經有的資料弄到slave 上<br />#(In master) 把master上 cake_dev database的資料dump 出來<br />mysqldump -uroot cake_dev > /tmp/cake_dev.db<br /><br />#(In slave) 把資料灌進slave cake_dev 的database , 當然 database 請先建好<br />mysql -uroot -phiroot cake_dev < cake_dev.db<br /><br />

Step5:
(In master and slave) master 和 slave 兩邊的mysql 都要重起
/etc/init.d/mysql restart