如何設定MYSQL資料庫複製(How To setup MYSQL Replication)

最近,我所設立的BLOG,在測試及正式環境下,都得手動同步資料,挺麻煩的
Recently, it’s really troublesome to sync the data between testing and production server.

於是想起MYSQL的資料庫複製功能
it made me think about MYSQL REPLICATION.

我的正式環境為LINUX FEDORA OS, MYSQL為5.5.20版,測試環境為WINDOWS 7, MYSQL則為WAMP裡的5.5.16版本.不過經過測試,一樣可以同步資料
My production environment is running on the LINUX FEDORA OS, and MYSQL version number is 5.5.20, testing environment is windows 7 and mysql is 5.5.16, although it’s different OS and MYSQL version, it still can be replicated between each other.

說真的設定也沒有很難
Actually, the setting is not very difficult.

但在設定的過程中也不是沒有遇到困難,提醒
Despite it’s easy, still need to remind something as below, those were what i encountered.

1.注意權限(SLAVE機台要有連入MASTER主機的權限): 我一開始一直卡在這裡,後來才發現我手殘設錯一個小數點,我原來要192.168.1.0/24都能開放權限,應設192.168.1.但我少加了小數點,後來直接把IP也加上,縮小點也算安全一點,才使同步的錯誤解除

1. notice the access right(slave mysql need to have the access right to reach the master one): at the beginning I did not notice about this, when I was trying to enable the access right to 192.168.1.0/24, I lost a decimal dot behind 192.168.1. when i found this mistake, just add the specific ip address on it, it also meet the security principle in the network world. after completing this, I just resolve this problem.

2.注意語法: 我曾還到CHANGE MASTER那一段指令,有一個單括號漏了,結果那些設定出了點問題,我也花了點時間確認
但總計來看,MYSQL的資料庫複製功能好用又不太難設定.

2. notice the setting of CHANGE MASTER section, I lost a single brackets,and then i encountered the trouble again, and also take me for a while to confirm about this. anyway, MYSQL REPLICATION is a good function and also easy for setting.

主要參考自以下連結內的文章介紹
mainly refer to the paper URL as below.
http://www.hackosis.com/how-to-setup-mysql-replication/

MySQL is the relational database system of choice for open sourcers. Replication is the process of replicating data from one MySQL database server (the master) into another (the slave). We?ll go into why you would want to replicate a MySQL database in another article.

MySQL Replication

Using the master-slave configuration mentioned above, only the changes made to the master are replicated in the slave. Changes made to the slave do not affect the master.

If you follow the steps below, you can set up MySQL replication in a matter of minutes.

Open the my.cnf or my.ini file (depending on whether you are running Linux or Windows).
Type in the following, somewhere below ?[mysqld]?

log-bin=mysql-bin
server-id=1
innodb_flush_log_at_trx_commit=1
sync_binlog=1
Restart MySQL on the master server.
Create a new user on the master server with the ?REPLICATION SLAVE? privelege. You don?t need to assign any other priveleges to this user. In the following commands, replace X.X.X.X with the IP address of the slave server.

CREATE USER ?user?@ ?X.X.X.X? IDENTIFIED BY ?password?;
GRANT REPLICATION SLAVE ON *.* TO ?user?@’X.X.X.X? IDENTIFIED BY ?password?;
Execute ?FLUSH TABLES WITH READ LOCK;? on the master to prevent writing to the databases.
Execute ?SHOW MASTER STATUS;? on the master and note down the values because we?ll use these later.
Execute ?UNLOCK TABLES;? on the master.
Open the my.cnf or my.ini on the slave server.
Enter somewhere below ?[mysqld]? on the slave server:

server-id=2
Save the file and restart mysqld.
Execute the following on the slave server (adjust values according to user setup in step 4 and values retrieved from step 6):

CHANGE MASTER TO
MASTER_HOST=?X.X.X.X?,
MASTER_USER=?user?,
MASTER_PASSWORD=?password?,
MASTER_PORT=3306,
MASTER_LOG_FILE=?mysql-bin.000001?,
MASTER_LOG_POS=98,
MASTER_CONNECT_RETRY=10;
Execute the following on the slave server:

START SLAVE;
Check the mysql log on the slave to ensure that the connection to the master has been successful. You should see a line similar to the following:
091104 8:42:02 [Note] Slave I/O thread: connected to master ?root@X.X.X.X:3306?, replication started in log ?mysql-bin.000001? at position 98
Now you should have successfully configured master-slave MySQL replication.

Something you might want to consider is limiting the binary log files retentions with the expire_logs_days on the master, otherwise the replicated date will just grow and grow.