Master-slave synchronization backup procedure for mysql Databases in windows (one-way synchronization)

Implementation of mysql two-way synchronous backup in windows

The following articles mainly describe the correct operation solution for implementing master-slave synchronous backup of MySQL Databases in windows, on some related websites, I saw the operation steps for implementing the master-slave synchronous backup of MySQL Databases in windows, but few of them were successful in the end, therefore, we will share with you a complete solution.
The following configurations have been successfully configured on the local machine:

Function: A is the master server and B is the slave server. In the initial state, the data information in A and B is the same. When the data in A changes, B also changes accordingly, synchronize the data information of A and B to achieve the purpose of backup.

Environment:

MySQL database versions A and B are 4.1.20

A:

Operating System: Windows 2003 server

IP Address: 192.168.100.1

B:

Operating System: Windows 2003 server

IP Address: 192.168.100.2

Configuration process:
1. Create A backup account in MySQL database A. The command is as follows:
Copy codeThe Code is as follows:
Grant replication slave, RELOAD, super on *.*
TO backup @ '192. 168.100.2'
Identified by '20140901 ';

Create an account backup and only allow login from 192.168.100.2. The password is 1234.
2. Because the new password algorithm of mysql version is different, enter: set password for 'backup '@ '192. 168.100.2' = old_password ('123') in mysql ');
3. Shut down server A, copy the data in server A to server B, synchronize the data in server A and server B, and ensure that before all the settings are completed, do not perform write operations on server A and server B so that the data in the two databases must be the same!
4. modify the configuration of server A, open the mysql/my. ini file, and add the following content under [mysqld:
Server-id = 10
Log-bin = c: \ log-bin.log
Server-id: ID value of master server
Log-bin: Binary Change Daily Value
5. Restart server A. From now on, it will record the database changes related to the customer heap into the binary change log.
6. Shut down server B and configure Server B's Jinxi so that it can know its image ID, where to find the master server, and how to connect to the server. The simplest case is that the master and slave servers run on different hosts respectively and use the default TCP/IP ports as long as the mysql/my. add the following commands to the INI file.
Copy codeThe Code is as follows:
[Mysqld]
Server-id = 11
Master-host = 192.168.100.1
Master-user = backup
Masters-password = 1234
// Optional
Replicate-do-db = backup
Server-id: ID of slave server B. Note that it cannot be the same as the ID of the master server.
Master-host: the IP address of the master server.
Master-user: The account connecting the slave server to the master server.
Master-password: the account and password used to connect the slave server to the master server.
Replicate-do-db: tells the master server to only synchronize images for the specified database.


7. Restart slave server B. Now all settings are complete. Update the data in A, and B will also perform synchronization updates immediately. If the slave server is not synchronously updated, you can view the mysql_error.log file on the slave server to troubleshoot the error.
8. Because the slave configuration information is set, mysql generates master.info in the database data directory. Therefore, to modify the slave configuration, delete the file first, otherwise, the modified configuration does not take effect.

Binlog-do-db

The official document 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 master server that if the current database (that is, the database selected for USE) is db_name, update records should be recorded in the binary log. All other databases that are not explicitly specified are ignored. If this option is used, make sure that only the current database is performed...

For example, if you set
Copy codeThe Code is as follows:
Binlog-do-db = db1
Then you run the following statement:
Use db1;
Create database db2;
The second statement is not logged.
Even if the current database is db1, the database name in the statement is considered;

On the contrary, statements other than create database, alter database, and drop database are related to the current DATABASE.
For example, if you set
Copy codeThe Code is as follows:
Binlog-do-db = db1
Then you run the following statement:
Use db2;
Insert into db1.table1 values (1 );
This statement is not counted into logs because the current db is db2. It has nothing to do with db operations.


First query 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)
It can be seen that there is a problem with the File and Position of the two. You need to set Master_Log_File and Read_Master_Log_Pos for the master database on Slave; execute the following statement;
Mysql> slave stop;
Mysql> change master to MASTER_HOST = '2017. 168.0.1 ', MASTER_USER = 'test', MASTER_PASSWORD =' ****** ', MASTER_LOG_FILE = 'updatelog. 000012 ', MASTER_LOG_POS = 15016;
Make sure that Slave_IO_Running: Yes, Slave_ SQL _Running: Yes must be YES to prove that Slave's I/O and SQL are normal.

Change master to MASTER_LOG_FILE = 'mysql-bin.000006 ', MASTER_LOG_POS = 6267;

CHANGE MASTER
MASTER_HOST = '1970. 168.1.100 ',
MASTER_USER = 'jb51 ',
MASTER_PASSWORD = 'www .jb51.net ',
MASTER_LOG_FILE = 'mysql-bin.000002 ',
MASTER_LOG_POS = 76146;

Then, check the Server Load balancer on the server Load balancer. If the Server Load balancer instance is normal, it will be OK.

Copy codeThe Code is as follows:
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 ).
View server_id. Generally, 1 or 2 is not recommended for large users. We recommend that you use to prevent this problem.
Show variables like 'server _ id ';