Master and slave synchronous backup steps for mysql database in windows environment of one way synchronization

Implementation of mysql bidirectional synchronous backup under windows

The following article mainly tells the implementation in windows environment MySQL database on the proper operation of master-slave synchronization backup, I saw in my 1 some related websites about windows environment for master-slave synchronization MySQL database backup steps described, but few on its successful operation to the end, so take out this paper relatively complete solution 1 up to share with you.
The following configuration has been successfully installed on the machine:

In the initial state, the data information in A and B are the same. When the data in A changes, B also changes accordingly, so that the data information in A and B can be synchronized to achieve the purpose of backup.

Environment:

The MySQL database version of A and B is 4.1.20

A:

Operating system: Windows 2003 server

IP address: 192.168.100.1

B:

Operating system: Windows 2003 server

Address of IP: 192.168.100.2

Configuration process:
1. Set up a backup account in A's MySQL database with the following commands:

 
GRANT REPLICATION SLAVE,RELOAD,SUPER ON *.* 
TO backup@'192.168.100.2' 
IDENTIFIED BY '1234'; 

Create an backup account and only allow logins from 192.168.100.2, password 1234.
2. Because the new password algorithm of mysql version is different, enter mysql and enter: set password for 'backup'@'192.168.100.2'=old_password('1234');
3. Shut down the A server, copy the data in A to the B server, synchronize the data in A and B, and make sure that write operations are forbidden in A and B servers before the end of all setup operations, so that the data 1 in the two databases must be the same!
4. Modify the configuration of A server, open the mysql/ my.ini file, and add the following contents under [mysqld] :
server-id=10
log-bin=c:\log-bin.log
server-id: the ID value of A for the primary server
log-bin: change daily value in base 2
5. Restart the A server. From now on, it will record the client's changes to the database in the binary change log.
6. Shut down B server and configure B server jinxi so that it knows its mirror ID, where to find the master server and how to connect to the server. In the simplest case, the master and slave servers are running on different hosts and both using the default TCP/IP port. Simply add the following lines to the mysql/ my.ini file that you read from the server startup.

 
[mysqld] 
server-id=11
master-host=192.168.100.1 
master-user=backup 
master-password=1234 
// The following are optional  
replicate-do-db=backup 
server-id : from the server B the ID Value. Note that you cannot talk to the primary server ID The same value.  
master-host : for the primary server IP Address.  
master-user : connection from the server to the account of the primary server.  
master-password : the account password of the primary server connected from the server.  
replicate-do-db : tells the primary server to mirror only the specified database synchronously.  

7. Restart the slave server B. All Settings are now complete. Update the data in A, and the data in B will be synchronized immediately. If the update is not synchronized from the server, you can debug it by looking at the mysql_error.log log file from the server.
8. Since the configuration information of slave is set, mysql generates master. info in the data directory of database.

binlog-do-db

The official documentation recommends that binlog-do-db is not specified on the master end, and replication-do-db is used on the slave end for filtering.

Tell the primary server that if the current database (that is, USE selected database) is db_name, the update should be logged in a binary log. All other databases that are not explicitly specified are ignored. If you use this option, you should ensure that only the current database is...

Let's say you set it

 
binlog-do-db=db1 
 Then you execute the following statement  
use db1; 
create database db2; 
 The first 2 Sentence statements are not entered log 
 Even if the current database is db1 , because you are considering the database name in the statement;  

In contrast, statements other than CREATE DATABASE, ALTER DATABASE, and DROP DATABASE are relevant to the current database.
Let's say you set it

 
binlog-do-db=db1 
 Then you execute the following statement  
use db2; 
insert into db1.table1 values(1); 
 This statement does not count log Because of the current db is db2 . With the operation db Has nothing to do.  

Query first on the server side

mysql > show master status;
+------------------+----------+--------------+------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| updatelog.000012 | 15016 | data | mysql |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
Thus it can be seen that there are problems with File and Position, so Master_Log_File and Read_Master_Log_Pos corresponding to the main library should be set on Slave. Execute the following statement;
mysql > slave stop;
mysql > CHANGE MASTER TO MASTER_HOST='192.168.0.1',MASTER_USER='test', MASTER_PASSWORD='******',MASTER_LOG_FILE='updatelog.000012',MASTER_LOG_POS=15016;
Make sure Slave_IO_Running: Yes, Slave_SQL_Running: Yes are all for YES before you can prove Slave I/O and SQL are working properly.

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000006', MASTER_LOG_POS=6267;

CHANGE MASTER TO
MASTER_HOST='192.168.1.100',
MASTER_USER='jb51',
MASTER_PASSWORD='www.ofstack.com',
MASTER_LOG_FILE='mysql-bin.000002',
MASTER_LOG_POS=76146;

And then on the slave side you can see that if it's normal it's oK, it's yes

Slave_IO_Running: Yes 
Slave_SQL_Running: Yes 

[ERROR] The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).
Check the server_id; In general, we don't recommend 1 or 2. We recommend 10,11 to prevent it
show variables like 'server_id';