1.1 Environment Construction
Prepare two Windows NT hosts, install Ikey Server Windows version, make sure the version is correct, ensure the MySQL service starts properly, make sure that the two hosts are in the same LAN, determine which one is the main, standby machine, assume A is the host, B is standby, Suppose a host IP address is: 192.168.1.101,B host IP address is 192.168.1.102
1.2 Creating a synchronization account
Login MySQL database on a and B nodes, create synchronization account and give synchronization permissions as follows:
A node operation:
Run cmd, CD into the Ikey version of the installation directory under the Ikey\mysql\bin, and then perform the mysql–uroot–p123456 login MySQL database execution:
Mysql>grant REPLICATION SLAVE on *.* to "yw[j1]" @ "192.168.1.10[J2] 2" identified by ' ym[j3 ';
Mysql>flush privileges;
The B-node operation steps as above, the permissions are given as follows:
Mysql>grant REPLICATION SLAVE on *.* to "yw[j4]" @ "192.168.1.10[j5] 1" identified by ' ym[j6 ';
Mysql>flush privileges;
After doing so, run net stop MySQL, and stop the two node database separately.
1.3 Configure Data synchronization options
1.3.1 A host operation
When a version of Ikey Server Windows is installed, a MY_MASTER.CNF profile is generated under the Ikey\mysql\backup folder under the installation directory, and the my_master.cnf files are copied to the installation directory under the host a. Ikey \mysql\bin folder, rename to My.cnf, before renaming, please back up the original my.cnf file, the following describes the changes my.cnf made:
Log-bin=mysql-bin #同步事件的日志记录文件
binlog-do-db=ikey_db #提供数据同步服务的数据库日志
Binlog-do-db=ikey_log #提供数据同步服务的数据库日志
Server-id=1
master-host=192.168.1.102 #主机B的IP地址
Master-user=ym #同步帐户
Master-password=ym #同步帐户密码
master-port=3306 #端口, MySQL Port of host
Master-connect-retry=60 #重试间隔60秒
replicate-do-db=ikey_db #同步的数据库
Replicate-do-db=ikey_log #同步的数据库
1.3.2 B host Operation
After a version of Ikey Server Windows is installed, a MY_SLAVE.CNF profile is generated under the Ikey\mysql\backup folder under the installation directory, and the my_slave.cnf file is copied to the installation directory under the B standby in ikey\ Mysql\bin folder, rename it to My.cnf, before renaming, please back up the original my.cnf file, modify the same host A, different configuration is the following:
server-id=2
Master-host=192.168.1.101 #主机A的地址
The above need to note is, to define the Master-host host IP address, please modify according to the actual situation.
Make sure that the synchronization user, host IP, and sync account passwords are the same as the settings that you set.
1.3.3 Validating Data Synchronization
When the above configuration is completed, reboot host A and Standby B database, run net start MySQL,
To view the synchronization configuration, login to the MySQL database.
To view master information on a 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)
To view slave information on the B node:
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 looking at the sync state on the B node, we can clearly see the synchronization configuration information set and the current synchronization status.
You can also view master information on the B node and view slave information on Node A.
Add data on a host, and test the data on the B standby for synchronization and reverse testing.
1.4 Synchronous Maintenance
When you need to change the node IP, the Mysql synchronization configuration also needs to be modified, before changing the node IP, we do the following:
Execute mysql> stop slave on A and B nodes respectively; Stops the current sync state.
If a node IP is changed to 192.168.1.103 at this point, you need to do the following:
1.4.1 a 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 b Node:
Change account permissions in b--àa direction synchronization
Mysql>show Grants for YM@192.168.1.101 (formerly a node IP);
To view the permissions originally assigned to the 192.168.1.101 connection, at this point we remove the synchronization account and reassign the 192.168.1.103 connection permissions 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 a-àb direction Sync configuration entry:
Mysql>change MASTER to
-> master_host= ' 192.168.1.103 ', # MASTER server address
-> master_user= ' ym ',
->master_password= ' ym ',->->master_log_file= ' mysql-bin.000002 ', # just now we record a node database to perform a replicated log file.
-> master_log_pos=118; #刚才记录的A节点数据库要复制的日志文件位置.
Query OK, 0 rows affected (0.02 sec)
At the same time, you need to view master information for Node B, sync 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)
Also, modify the B-node MySQL master configuration file
master-host=192.168.1.103 #主机A的IP地址
1.4.3 Then a node:
To modify the b-àa direction Sync configuration entry:
Mysql>change MASTER to
->master_log_file= ' mysql-bin.000003 ', # just now we record the B-node database to perform the replicated log file.
-> master_log_pos=98; #刚才记录的B节点数据库要复制的日志文件位置.
Query OK, 0 rows affected (0.02 sec)
Then start the synchronization state for A and B nodes and run the start slave respectively; Then look at the sync status and make sure show slave status is viewed on each machine;
Slave_io_running:yes
Slave_sql_running:yes
is in the normal sync state.
The above is for a single node IP modification of the operation, if a, b node IP needs to be modified, refer to the above configuration to operate, it is best to first for a single node IP to modify, and to ensure that one-way synchronization no problem, then the second node IP modification and configuration.
--------------------------------------------------------------------------------
[J1] Users who perform synchronization permissions
IP address of [J2] Host B
[J3] Sync account password
[J4] Users who perform synchronization permissions
IP address of [J5] host A
[J6] Sync account password
First step:
Add in the My.ini of a database
Copy Code code as follows:
Server-id=1
Log-bin=c:\mysqlback #同步事件的日志记录文件
Binlog-do-db=test1 #提供数据同步服务的数据库
Binlog-do-db=test2 #提供数据同步服务的数据库
Binlog-do-db=test3 #提供数据同步服务的数据库
master-host=192.168.0.102 #主机B的地址
master-user=use102 #主机B提供应B的用户, the user needs to include the database test1 TEST12TEST3 permissions
master-password=usepwd102 #访问密码
master-port=3306 #端口, MySQL Port of host
Master-connect-retry=60 #重试间隔60秒
Replicate-do-db=test1 #同步的数据库
Replicate-do-db=test2 #同步的数据库
Replicate-do-db=test3 #同步的数据库
Step Two:
Add in the My.ini of the B database
Copy Code code as follows:
server-id=2
Log-bin=c:\mysqlback #同步事件的日志记录文件
Binlog-do-db=test1 #提供数据同步服务的数据库
Binlog-do-db=test2 #提供数据同步服务的数据库
Binlog-do-db=test3 #提供数据同步服务的数据库
Master-host=192.168.0.101 #主机B的地址
Master-user=use101 #主机A提供给A的用户, the user needs to include the database test1 TEST12TEST3 permissions
Master-password=usepwd101 #访问密码
master-port=3306 #端口, MySQL Port of host
Master-connect-retry=60 #重试间隔60秒
Replicate-do-db=test1 #同步的数据库
Replicate-do-db=test2 #同步的数据库
Replicate-do-db=test3 #同步的数据库
Step Three:
Give a MySQL data permission to b
Mysql>grant FILE on *.* to ' use101 ' @ ' 192.168.0.102 ' identifiedby ' pwd101 ';
B's MySQL data will be the right to B operation ibid.
Fourth Step:
Restart the AB database after:
B Machine:
Mysql>slave start;
View sync Configuration
A machine:
Mysql>show Master Status\g;
B Machine:
Mysql>show slave status\g;
If a is not synchronized with the B database, check the. err file under the MySQL installation directory.
If the error message in the slave log is as follows:
060807 11:40:17 [ERROR] while trying to obtain the list of slaves from the master ' xxx.xxx.xxx:3306 ' user ' rep ' got the FO Llowing error: ' Access denied. Need the REPLICATION SLAVE privilegefor this operation ' on 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 in *.* to ' rep ' @ ' 192.168.0.10 2 ' identified by PASSWORD ' xxx ' has been granted the Replicaion SLAVE permission, how will you report this mistake?
By looking at the manuals and the source code, you know slave needs to execute a statement to update the slave list:
Show SLAVE HOSTS, and the execution of this statement requires Replicaiton CLIENT permissions, and therefore the error. Therefore, as long as the account to add REPLICATION CLIENT permissions on it.
Grant Selectreplication slavereplicaion CLIENT on *.* to ' use101 ' @ ' 192.168.0.102 ' identified by ' pwd101 ';
