First, the purpose: the main server in the MySQL database synchronization to the server, so that the operation of the primary server can be updated immediately from the server, to avoid the main server due to the environment or network abnormal temporarily unusable, to achieve the backup effect.
Second, the environment:
The master-Slave server operating system, the database version are the same, specific as follows:
Operating system: Windows 2003 Server R2
MySQL database version: 5.0.51b
Primary server (replace with a) IP address: 192.168.4.100
From server (replace with B) IP address: 192.168.4.101
Third, the configuration process:
1. Create a remote access backup account in a.
Create a backup account in the MySQL database of a, which commands the following:
Copy Code code as follows:
Mysql> GRANT REPLICATION Slave,reload,super on the *.* to july@ ' 192.168.4.101 ' "identified by ' 123456 ';
mysql> Flush privileges;
Mysql> Quit;
Statement Explanation:
In the first sentence *.* represents all databases, if you want to back up a database, if the database name is some, then replace Some.*, where to july@ ' 192.168.4.101 ', July for remote access username, IP address for remote access Ip,by ' 123456 ' in 123456 for remote access password;
The second sentence is executed so that the remote access user is created in effect;
Implementation instructions:
After you execute the above statement, to see if the remote access user has been successfully created, execute the following statement:
mysql> use MySQL;
Mysql> select Host,user from user;
One more in the user list
+------------------------------------+------------------+
|host |user |
+------------------------------------+------------------+
|192.168.4.101 |july |
+------------------------------------+------------------+
This indicates that the remote access user has been created successfully.
2. Shut down A's MySQL service and copy data to B.
Turn off the MySQL service for a, copy the data from A to B, make the A and B server initial data exactly the same, and disable database write operations on the server before synchronizing the backup to ensure that the initial data for Server A and B is identical.
3. Modify the My.ini configuration information in a
Open the My.ini file and add the following under [Mysqld] (#内容为注释):
# SERVER-ID:A The server's ID value
server-id=10
#log-bin: Binary change Log
Log-bin=c:\log/log-bin.log
4, reboot a server.
5, shut down B server, the My.ini of B for the relevant configuration
Here you need to note:
Because the slave configuration information is set up, MySQL generates master.info in the database data directory, so if you want to modify the related slave configuration to delete the file first, otherwise the modified configuration cannot take effect.
Open the My.ini configuration file for B and add the following in [Mysqld]:
#B服务器ID值
server-id=11
#远程访问IP地址
master-host=192.168.4.100
#远程访问用户名
Master-user=july
#远程访问用户密码
master-password=123456
#主服务器端口 (default is 3306, note here to see if the primary server port is blocked because of a firewall)
master-port=3306
#同步时间间隔为60秒
Master-connect-retry=60
#设置要同步备份的数据库, the database here is some
Replicate-do-db=some
6, restart B server, to this has completed the master-slave backup, if there are errors, can be viewed through mysql_error.log.
Description: This document for the network collection of data on their own test finishing, mainly to facilitate their follow-up search and have the need for reference, if there is infringement, contact is deleted.
