One-way master-slave backup for MySql Databases in Windows

One-way master-slave backup for MySql Databases in Windows 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 to avoid unavailability of the master server due to environment or network exceptions,
Achieve the backup effect. II. Environment: the operating system and database version of the Master/Slave Server www.2cto.com are the same. For details, refer to: Operating System: Windows 2003 Server R2MySql 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
3. configuration process: 1. Create A remote access backup account in. Create A backup account in MySql database A with the following command: mysql> grant replication slave, RELOAD, super on *. * TO July @ '192. 168.4.101'
Identified by '200'; mysql> Flush privileges; mysql> Quit; statement explanation: In the first sentence *. * Indicates all databases. If you want to back up a database, assume that the database name is "some,
Replace it with some. *, where TO July @ '192. 168.4.101 'and July is the remote access user name,
The IP address is the remote access IP address, and 123456 in '123' is the remote access password. After the second statement is executed, the remote access user creation takes effect. Instructions for executing www.2cto.com: After the preceding statement is executed, 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 is + users + ---------------- + | host | user | + users + ---------------- + | 192.168.4.101 | July | + users + ---------------- +, indicating 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 data in server A to server B so that the initial data of server A and server B is identical,
In addition, you are not allowed to write data to the database on the server before synchronizing the backup to ensure that the initial data of server A and server B is identical.
3. modify my. enable my. INI file, add the following content under [mysqld] (# content as A comment): # server-id: server-ID = 10 # log-bin: binary change log-bin = c: \ log/log-bin.log4, restart server. 5. Disable server B and configure my. ini of server B. Note that mysql generates master.info in the database data directory because slave configuration information is set,
Therefore, if you want to modify the slave configuration, delete the file first. Otherwise, the modified configuration will not take effect. Open my. in the ini configuration file, add the following content to [mysqld: # server B ID value www.2cto.com server-id = 11 # remote access IP address master-host = 192.168.4.100 # Remote Access username master-user = July # Remote Access username master-password = 123456 # master server port (3306 by default, note that 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, here, the database is some replicate-do-db = some6, and server B is restarted. The master-slave backup has been completed. If there is an error, you can view it through mysql_error.log. Author: calm