Implementation of MySQL two-way synchronous backup in Windows

1.1 Environment Construction
Prepare two windows NT hosts, install the Ikey server Windows version, and confirm that the version is correct. Ensure that the MySQL service starts properly and that the two hosts are in the same LAN, determine which master and slave machines are used as the master and slave machines. Assume that host a is the master server, host B is the slave server, host a has the IP address 192.168.1.101, and host B has the IP address 192.168.1.102.

1.2 create a synchronization account
Log on to the MySQL database on node A and Node B, create a synchronization account, and grant the synchronization permission as follows:

Node A operations:

Run cmd, run CD to go to Ikey \ mysql \ bin under the installation directory of the Ikey version, and then run mysql-uroot-p123456 to log on to the MySQL database for execution:

Mysql> grant replication slave on *. * to "yw [J1]" @ "192.168.1.10 [J2] 2" identified by 'ym [J3] ';

Mysql> flush privileges;

Perform the following operations on Node B:

Mysql> grant replication slave on *. * to "yw [J4]" @ "192.168.1.10 [J5] 1" identified by 'ym [J6] ';

Mysql> flush privileges;

After performing the preceding operations, run net stop MySQL to stop the two-node databases respectively.

1.3 configure data synchronization options
1.3.1 host a operations
After installing the Ikey server for Windows, A my_master.cnf configuration file is generated in the Ikey \ mysql \ backup folder under the installation directory, copy the my_master.cnf file to the Ikey \ mysql \ bin folder under the installation directory on host a and rename it to my. CNF. Back up the original my before renaming. CNF file. The following describes my. CNF modification:

Log-bin = mysql-bin # log record files of synchronization events

BINLOG-do-DB = ikey_db # database logs that provide the data synchronization service

BINLOG-do-DB = ikey_log # database logs that provide the data synchronization service

Server-id = 1
Master-host = 192.168.1.102 # IP address of host B
Master-user = ym # synchronization account
Master-Password = ym # synchronization account password
Master-Port = 3306 # port, MySQL port of the host
Master-connect-retry = 60 # Retry Interval 60 seconds
Replicate-do-DB = ikey_db # synchronized Database

Replicate-do-DB = ikey_log # synchronized Database

1.3.2 host B operations
After installing the Ikey server for Windows, A my_slave.cnf configuration file is generated in the Ikey \ mysql \ backup folder under the installation directory, copy the my_slave.cnf file to the Ikey \ mysql \ bin folder in the installation directory and rename it to my. CNF. Back up the original my before renaming. the CNF file is modified in the same way as host a. The different configurations are as follows:

Server-id = 2
Master-host = 192.168.1.101 # address of host

Note that you must modify the IP address of the master-host according to the actual situation.

Make sure that the above synchronization user, Host IP address, and synchronization account password are the same as the settings.

1.3.3 verify data synchronization
After the preceding configuration is complete, restart host a and backup database B to run Net start MySQL,

View the synchronization configuration and log on to the MySQL database.

View the master information on node:

Mysql> show Master status;

+ ------------------ + ---------- + ------------------ +

| File | position | binlog_do_db | binlog_ignore_db |

+ ------------------ + ---------- + ------------------ +

| Mysql-bin.000001 | 98 | ikey_db, ikey_log |

+ ------------------ + ---------- + ------------------ +

1 row in SET (0.00 Sec)

View slave information on Node B:

Mysql> show slave status \ G;

* *************************** 1. row ***************************

Slave_io_state: Waiting for Master to send event

Master_host: 192.168.1.101

Master_user: ym

Master_port: 3306

Connect_retry: 60

Master_log_file: mysql-bin.000001

Read_master_log_pos: 98

Relay_log_file: testBBB-relay-bin.000002

Relay_log_pos: 235

Relay_master_log_file: mysql-bin.000001

Slave_io_running: Yes

Slave_ SQL _running: Yes

Replicate_do_db: ikey_db, ikey_log

By viewing the synchronization status on Node B, we can clearly see the configured synchronization configuration and the current synchronization status.

You can also view master information on Node B and slave information on node.

Add data to host a and test whether data on the slave B is synchronized and perform reverse testing.

1.4 synchronization Maintenance
When you need to change the node IP address, you also need to modify the MySQL synchronization configuration. before changing the node IP address, perform the following operations:

Run mysql> stop slave on node A and Node B respectively to stop the current synchronization status.

If the IP address of node A is changed to 192.168.1.103, perform the following operations:

1.4.1 node:
Mysql> show Master status;

+ ------------------ + ---------- + ------------------ +

| File | position | binlog_do_db | binlog_ignore_db |

+ ------------------ + ---------- + ------------------ +

| Mysql-bin.000002 | 118 | ikey_db, ikey_log |

+ ------------------ + ---------- + ------------------ +

1 row in SET (0.00 Sec)

1.4.2 Node B:
Change account permissions synchronized from B -- à

Mysql> show grants for ym@192.168.1.101 (original a node IP );

View the permissions granted to the connection from 192.168.1.101. In this case, delete the synchronization account and re-grant the connection permission from 192.168.1.103 as follows:

Mysql> drop user ym@192.168.1.101;

Mysql> flush privileges;

Mysql> grant replication slave on *. * To ym@192.168.1.103 identified by 'ym ';

Mysql> flush privileges;

Then, modify the synchronization configuration item from A to B:

Mysql> change master

-> Master_host = '192. 168.1.103 ', # master server address

-> Master_user = 'ym ',

-> Master_password = 'ym',-> master_log_file = 'mysql-bin.000002 ', # We recorded the log file to be copied in the node database just now.

-> Master_log_pos = 118; # the location of the log file to be copied in the node database just recorded.

Query OK, 0 rows affected (0.02 Sec)

At the same time, you need to view the master information of Node B, and synchronize log logs and Pos locations.

Mysql> show Master status;

+ ------------------ + ---------- + ------------------ +

| File | position | binlog_do_db | binlog_ignore_db |

+ ------------------ + ---------- + ------------------ +

| Mysql-bin.000003 | 98 | ikey_db, ikey_log |

+ ------------------ + ---------- + ------------------ +

1 row in SET (0.00 Sec)

Modify the MySQL master configuration file of Node B.

Master-host = 192.168.1.103 # IP address of host

1.4.3 next to node:
Modify the configuration items for direction B-à a synchronization:

Mysql> change master

-> Master_log_file = 'mysql-bin.000003 ', # We recorded the log file to be copied by the B-node database just now.

-> Master_log_pos = 98; # the location of the log file to be copied in the B-node database just recorded.

Query OK, 0 rows affected (0.02 Sec)

Start the synchronization status for node A and Node B, and run start slave respectively. Then, check the synchronization status to check the show slave status on each machine.

Slave_io_running: Yes

Slave_ SQL _running: Yes

That is, it is in the normal synchronization status.

The preceding operations are for IP address modification on a single node. If the IP addresses of node A and Node B need to be modified, you are advised to modify the IP address of a single node first, make sure that there is no problem with unidirectional synchronization, and then modify and configure the IP address of the second node.

--------------------------------------------------------------------------------

[J1] the user who executes the synchronization permission

[J2] IP address of host B

[J3] synchronization account password

[J4] the user who executes the synchronization permission

[J5] IP address of host

[J6] synchronization account password

Step 1:
In my. ini of database A, addCopyCodeThe Code is as follows: Server-id = 1
Log-bin = c: \ mysqlback # synchronize the log file of the event
BINLOG-do-DB = test1 # database that provides the data synchronization service
BINLOG-do-DB = Test2 # database that provides the data synchronization service
BINLOG-do-DB = test3 # database that provides the data synchronization service
Master-host = 192.168.0.102 # address of host B
Master-user = use102 # host B provides the user corresponding to user B. The user must have the permissions of database test1 test12test3.
Master-Password = usepwd102 # Access Password
Master-Port = 3306 # port, MySQL port of the host
Master-connect-retry = 60 # Retry Interval 60 seconds
Replicate-do-DB = test1 # synchronized Database
Replicate-do-DB = Test2 # synchronized Database
Replicate-do-DB = test3 # synchronized Database

Step 2:
AddCopy codeThe Code is as follows: Server-id = 2
Log-bin = c: \ mysqlback # synchronize the log file of the event
BINLOG-do-DB = test1 # database that provides the data synchronization service
BINLOG-do-DB = Test2 # database that provides the data synchronization service
BINLOG-do-DB = test3 # database that provides the data synchronization service
Master-host = 192.168.0.101 # address of host B
Master-user = use101 # the user that host a provides to a needs to include the permission of database test1 test12test3
Master-Password = usepwd101 # Access Password
Master-Port = 3306 # port, MySQL port of the host
Master-connect-retry = 60 # Retry Interval 60 seconds
Replicate-do-DB = test1 # synchronized Database
Replicate-do-DB = Test2 # synchronized Database
Replicate-do-DB = test3 # synchronized Database

Step 3:
Grant Mysql Data Permissions Of A to B
Mysql> grant file on *. * To 'use101 '@ '192. 168.0.102' identifiedby 'pwd101 ';
Grant the permission of Mysql Data of B to the same operation as that of B.
Step 4:
Restart the AB database, and then:
Machine B:
Mysql> slave start;
View synchronization configurations
Machine:
Mysql> show Master Status \ G;
Machine B:
Mysql> show slave status \ G;
If database A and database B are not synchronized, check the. Err file in the MySQL installation directory.
If the slave log reports the following error information:
060807 11:40:17 [Error] while trying to obtain the list of slaves from the master 'xxx. xxx. XXX: 3306 'user' rep 'got the following error: 'Access denied. you need the replication slave privilegefor this operation 'on the master, execute the following statement to view permissions:
Mysql> show grant for 'use101 '@ '192. 168.0.102 '\ G **************************** 1. row ************************* grants for rep@192.168.0.102: grant select replication slave on *. * To 'rep '@' 192. 168.0.102 'identified by password 'xxx' has been granted the replicaion slave permission. How can I still report this error?
You can view the manual and source code to know that slave needs to execute a statement to update the slave list:
Show slave hosts; if this statement is executed, the replicaiton Client permission is required. Therefore, an error is returned. Therefore, you only need to add the replication Client permission to the account.
Grant selectreplication slavereplicaion client on *. * To 'use101 '@ '192. 168.0.102' identified by 'pwd101 ';