Windows server under MySql database one way master slave backup detailed implementation steps share

1. Purpose: to synchronize the MySql database in the primary server to the slave server, so that the operation of the primary server can be updated to the slave server in real time, so that the primary server can not be used due to the environment or network exception 1, so as to achieve the backup effect.

2. The environment:

Master and slave server operating system and database version are the same, as follows:
Operating system: Windows 2003 Server R2
MySql database version: 5.0.51b
Primary server (A instead) IP address: 192.168.4.100
Slave server (B instead) IP address: 192.168.4.101

3. Configuration process:

1. Create a remote access backup account at A.
Create a backup account in A's MySql database with the following commands:

 
　　mysql> GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* TO july@'192.168.4.101' IDENTIFIED BY '123456'; 
　　mysql> Flush privileges; 
　　mysql> Quit; 

Statement interpretation:

In the first statement, *.* refers to all databases. If a database is to be backed up, if the database name is some, then some.*, TO july@'192.168.4.101', july is the remote access username, IP is the remote access IP address, and BY '123456' is the remote access password.
The execution of the second statement makes the remote access user creation effective;

Executive instructions:

After executing 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 |
+------------------------------------+------------------+
At this point, the remote access user has been created successfully.
2. Close A's MySql service and copy the data to B.
Turn off A's Mysql service and copy the data from A to B, so that the initial data of A and B servers are exactly the same, and prohibit database writing to the server before synchronous backup, so as to ensure that the initial data of A and B servers are exactly the same.
3. Modify the configuration information of my.ini in A
Open the my.ini file and add the following under [mysqld] (# content is a comment) :
# server-id: ID value for A server
server - id = 10
# log-bin: change log in base 2
log - bin = c: \ log/log - bin log
4. Restart the A server.
5. Shut down B server and configure my.ini of B
Note here:
Since the configuration information of slave is set, mysql generates master.info in the data directory of the database. If you want to modify the configuration of slave, you should delete this file first, otherwise the modified configuration will not take effect.
Open the my.ini configuration file of B and add the following to [mysqld] :
#B server ID value
server - id = 11
Remote access to the IP address
master - host = 192.168.4.100
Remote access username
master - user = july
Remote access user password
master - password = 123456
Primary server port (default is 3306, note here whether the primary 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, some
replicate do - db = some
6. Restart the B server. The master and slave backups have been completed here.

Note: this document is for the network to collect the information of the trial sorting, mainly for the convenience of their follow-up search and those who need reference, if there is infringement, contact is deleted.