Recently, it’s really troublesome to sync the data between testing and production server.
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. 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那一段指令,有一個單括號漏了,結果那些設定出了點問題,我也花了點時間確認
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.
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.
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]?
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:
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
Execute the following on the slave server:
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.