How to implement one-way master-slave backup for MySql databases on Windows Server

Sharing steps for one-way master/slave backup of MySql databases on Windows Server 1. objective: to synchronize the MySql database on the master server to the slave server, this allows you to update operations on the master server to the slave server in real time. This prevents the master server from being used when the environment or network is abnormal and achieves the backup effect.

II. environment:

The master/slave server OS and database versions are the same, as shown in the following code:
Operating system: Windows 2003 Server R2
MySql database version: 5.0.51b
Master server (replaced by A) IP address: 192.168.4.100
Slave server (replaced by B) IP address: 192.168.4.101

III. configuration process:

1. create A remote access backup account in.
Create A backup account in MySql database A. The command is as follows:

Mysql> grant replication slave, RELOAD, super on *. * TO July @ '192. 168.4.101 'identified BY '20140901 ';
Mysql> Flush privileges;
Mysql> Quit;


Statement explanation:

In the first sentence *. * indicates all databases. if you want to back up a database, replace it with "some" if the database name is "some. *, where TO July @ '100. 168.4.101 ', July is the remote access user name, IP address is the remote access IP address, and BY '123' 123456 is the remote access password;
After the second statement is executed, the remote access user creation takes effect;

Execution instructions:

After executing the preceding statement, if you want to check whether the remote access user has been created successfully, run the following statement:
Mysql> use mysql;
Mysql> select host, user from user;
One more entry in the user list
+ ------------------------------------ + ------------------ +
| Host | user |
+ ------------------------------------ + ------------------ +
| 192.168.4.101 | July |
+ ------------------------------------ + ------------------ +
This indicates that the remote access user has been created successfully.
2. disable MySql service A and copy data to B.
Disable Mysql service A and copy the data in server A to server B, so that the initial data of server A and server B is completely the same, and the database write operation on the server is prohibited before the synchronous backup, to ensure that the initial data of server A and server B is identical.
3. modify my. ini configuration information in.
Open the my. ini file and add the following content under [mysqld] (# The content is a comment ):
# Server-id: ID of server
Server-id = 10
# Log-bin: binary change log
Log-bin = c:/log/log-bin.log
4. restart server.
5. disable Server B and configure my. ini of server B.
Note:
Because the slave configuration information is set, mysql generates master.info in the database data directory. Therefore, if you want to modify the related slave configuration, delete the file first. Otherwise, the modified configuration cannot take effect.
Open the my. ini configuration file of B and add the following content to [mysqld:
# Server B ID
Server-id = 11
# Remote access IP address
Master-host = 192.168.4.100
# Remote access username
Master-user = July
# Remote access user password
Masters-password = 123456
# Master server port (the default value is 3306. check whether the master server port is disabled because of the firewall)
Master-port = 3306
# The synchronization interval is 60 seconds.
Master-connect-retry = 60
# Set the database to be backed up synchronously. the database here is some
Replicate-do-db = some
6. restart server B. The Master-slave backup has been completed. if there is an error, you can view it through mysql_error.log.

Note. BitsCN.com