windows下mysql双向同步备份实现方法
程序员文章站
2022-09-10 18:11:23
1.1 环境搭建 准备两台windows nt 主机，分别安装好ikey server windows 版本，确定版本无误，确保mysql服务正常启动，确保两台主机处于同一...
1.1 环境搭建
准备两台windows nt 主机，分别安装好ikey server windows 版本，确定版本无误，确保mysql服务正常启动，确保两台主机处于同一个局域网中，确定好哪台做为主、备机器，假设a为主机，b为备机，假设a主机ip地址为：192.168.1.101，b主机ip地址为192.168.1.102
1.2 创建同步帐户
分别在a、b节点上登陆mysql 数据库，创建同步帐户并赋予同步权限，如下：
a节点操作：
运行cmd, cd进入ikey版本安装目录下的ikey\mysql\bin下，然后执行mysql –uroot –p123456 登陆mysql数据库执行：
mysql>grant replication slave on *.* to "yw[j1] "@"192.168.1.10[j2] 2" identified by 'ym[j3] ';
mysql>flush privileges;
b节点操作步骤如上，权限赋予如下：
mysql>grant replication slave on *.* to "yw[j4] "@"192.168.1.10[j5] 1" identified by 'ym[j6] ';
mysql>flush privileges;
执行以上操作后，运行net stop mysql, 分别停止两节点数据库.
1.3 配置数据同步选项
1.3.1 a主机操作
安装好的ikey server windows 版本后,会在安装目录下的ikey\mysql\backup文件夹下生成一个my_master.cnf配置文件，在a主机上将my_master.cnf 文件复制到安装目录下的ikey\mysql\bin文件夹下，并重命名为my.cnf,重命名之前请先备份原有my.cnf文件，以下介绍my.cnf做的修改之处：
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端口
master-connect-retry=60 #重试间隔60秒
replicate-do-db=ikey_db #同步的数据库
replicate-do-db=ikey_log #同步的数据库
1.3.2 b主机操作
安装好的ikey server windows 版本后,会在安装目录下的ikey\mysql\backup文件夹下生成一个my_slave.cnf配置文件，在b备机上将my_slave.cnf 文件复制到安装目录下的ikey\mysql\bin文件夹下，并重命名为my.cnf,重命名之前请先备份原有my.cnf文件，修改之处同a主机，不同配置在于以下：
server-id=2
master-host=192.168.1.101 #主机a的地址
以上需要注意的地方是，要定义master-host的主机ip地址，请根据实际情况进行修改。
请确保以上同步用户、主机ip、和同步帐户密码与所设配置相同.
1.3.3 验证数据同步
当以上配置完毕后，分别重新启动主机a和备机b数据库，运行net start mysql,
查看同步配置情况,登陆mysql 数据库。
在a节点上查看master 信息：
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)
在b节点上查看slave信息：
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
通过在b节点上查看同步状态，我们可以清楚的看到所设置的同步配置信息以及当前同步状态。
同样可以在b节点上查看master 信息以及在a节点上查看slave 信息。
添加数据在a主机上，同时测试b备机上数据是否同步，以及反向测试。
1.4 同步维护
当需要更改节点ip 时，mysql 同步配置也需要进行相应修改，在更改节点ip之前，我们先做如下操作：
分别在a、b节点上执行mysql> stop slave; 停止当前同步状态。
假如此时a 节点ip 改为192.168.1.103，需要做如下操作：
1.4.1 a节点：
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节点：
更改b--àa方向同步的帐户权限
mysql>show grants for ym@192.168.1.101(原a节点ip)；
查看到原赋予从192.168.1.101 连接的权限，此时我们删除此同步帐户，并重新赋予192.168.1.103 连接的权限，如下：
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;
然后修改a-àb方向同步配置项：
mysql>change master to
-> master_host='192.168.1.103', # master服务器地址
-> master_user='ym',
->master_password='ym', -> ->master_log_file='mysql-bin.000002', # 刚才我们记录a节点数据库要执行复制的日志文件。
-> master_log_pos=118; #刚才记录的a节点数据库要复制的日志文件位置。
query ok, 0 rows affected (0.02 sec)
同时需要查看此时b节点的master信息，同步log日志及pos位置
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)
同时请修改b节点mysql主配置文件中
master-host=192.168.1.103 #主机a的ip地址
1.4.3 接着a节点：
修改b-àa方向同步配置项：
mysql>change master to
->master_log_file='mysql-bin.000003', # 刚才我们记录b节点数据库要执行复制的日志文件。
-> master_log_pos=98; #刚才记录的b节点数据库要复制的日志文件位置。
query ok, 0 rows affected (0.02 sec)
然后针对a、b节点启动同步状态，分别运行start slave; 然后查看同步状态，确保在每台机器上查看show slave status;得出
slave_io_running: yes
slave_sql_running: yes
即处于正常同步状态。
以上是针对单节点ip修改所做的操作，如果a、b节点ip 都需要修改，参考以上配置进行操作，最好先针对单个节点ip 进行修改，并确保单向同步没有问题后，再进行第二个节点ip修改以及配置。
--------------------------------------------------------------------------------
[j1]执行同步权限的用户
[j2]主机b的ip地址
[j3]同步帐户密码
[j4]执行同步权限的用户
[j5]主机a的ip地址
[j6]同步帐户密码
第一步：
在a数据库的my.ini中添加
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的用户，该用户中需要包括数据库test1 test12test3的权限
master-password=usepwd102 #访问密码
master-port=3306 #端口，主机的mysql端口
master-connect-retry=60 #重试间隔60秒
replicate-do-db=test1 #同步的数据库
replicate-do-db=test2 #同步的数据库
replicate-do-db=test3 #同步的数据库
第二步：
在b数据库的my.ini中添加
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的用户，该用户中需要包括数据库test1 test12test3的权限
master-password=usepwd101 #访问密码
master-port=3306 #端口，主机的mysql端口
master-connect-retry=60 #重试间隔60秒
replicate-do-db=test1 #同步的数据库
replicate-do-db=test2 #同步的数据库
replicate-do-db=test3 #同步的数据库
第三步：
将a的mysql数据的权限给b
mysql>grant file on *.* to 'use101'@'192.168.0.102'identifiedby 'pwd101';
将b的mysql数据的权限给b操作同上。
第四步：
重启ab数据库，后：
b机器：
mysql>slave start;
查看同步配置情况
a机器：
mysql>show master status\g;
b机器：
mysql>show slave status\g;
假如a与b数据库没有同步，检查mysql安装目录下的.err文件。
如果slave日志中报错信息如下：
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'在master上，执行以下语句查看权限：
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'已经授予了 replicaion slave 权限了，怎么还会报这个错呢？
通过查看手册和源码，才知道slave需要执行一个语句来更新slave列表：
show slave hosts;而执行这个语句则需要 replicaiton client 权限，因此才会报错。因此，只要重新给 帐号加上 replication client 权限就可以了。
grant selectreplication slavereplicaion client on *.* to 'use101'@'192.168.0.102'identified by 'pwd101';
准备两台windows nt 主机，分别安装好ikey server windows 版本，确定版本无误，确保mysql服务正常启动，确保两台主机处于同一个局域网中，确定好哪台做为主、备机器，假设a为主机，b为备机，假设a主机ip地址为：192.168.1.101，b主机ip地址为192.168.1.102
1.2 创建同步帐户
分别在a、b节点上登陆mysql 数据库，创建同步帐户并赋予同步权限，如下：
a节点操作：
运行cmd, cd进入ikey版本安装目录下的ikey\mysql\bin下，然后执行mysql –uroot –p123456 登陆mysql数据库执行：
mysql>grant replication slave on *.* to "yw[j1] "@"192.168.1.10[j2] 2" identified by 'ym[j3] ';
mysql>flush privileges;
b节点操作步骤如上，权限赋予如下：
mysql>grant replication slave on *.* to "yw[j4] "@"192.168.1.10[j5] 1" identified by 'ym[j6] ';
mysql>flush privileges;
执行以上操作后，运行net stop mysql, 分别停止两节点数据库.
1.3 配置数据同步选项
1.3.1 a主机操作
安装好的ikey server windows 版本后,会在安装目录下的ikey\mysql\backup文件夹下生成一个my_master.cnf配置文件，在a主机上将my_master.cnf 文件复制到安装目录下的ikey\mysql\bin文件夹下，并重命名为my.cnf,重命名之前请先备份原有my.cnf文件，以下介绍my.cnf做的修改之处：
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端口
master-connect-retry=60 #重试间隔60秒
replicate-do-db=ikey_db #同步的数据库
replicate-do-db=ikey_log #同步的数据库
1.3.2 b主机操作
安装好的ikey server windows 版本后,会在安装目录下的ikey\mysql\backup文件夹下生成一个my_slave.cnf配置文件，在b备机上将my_slave.cnf 文件复制到安装目录下的ikey\mysql\bin文件夹下，并重命名为my.cnf,重命名之前请先备份原有my.cnf文件，修改之处同a主机，不同配置在于以下：
server-id=2
master-host=192.168.1.101 #主机a的地址
以上需要注意的地方是，要定义master-host的主机ip地址，请根据实际情况进行修改。
请确保以上同步用户、主机ip、和同步帐户密码与所设配置相同.
1.3.3 验证数据同步
当以上配置完毕后，分别重新启动主机a和备机b数据库，运行net start mysql,
查看同步配置情况,登陆mysql 数据库。
在a节点上查看master 信息：
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)
在b节点上查看slave信息：
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
通过在b节点上查看同步状态，我们可以清楚的看到所设置的同步配置信息以及当前同步状态。
同样可以在b节点上查看master 信息以及在a节点上查看slave 信息。
添加数据在a主机上，同时测试b备机上数据是否同步，以及反向测试。
1.4 同步维护
当需要更改节点ip 时，mysql 同步配置也需要进行相应修改，在更改节点ip之前，我们先做如下操作：
分别在a、b节点上执行mysql> stop slave; 停止当前同步状态。
假如此时a 节点ip 改为192.168.1.103，需要做如下操作：
1.4.1 a节点：
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节点：
更改b--àa方向同步的帐户权限
mysql>show grants for ym@192.168.1.101(原a节点ip)；
查看到原赋予从192.168.1.101 连接的权限，此时我们删除此同步帐户，并重新赋予192.168.1.103 连接的权限，如下：
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;
然后修改a-àb方向同步配置项：
mysql>change master to
-> master_host='192.168.1.103', # master服务器地址
-> master_user='ym',
->master_password='ym', -> ->master_log_file='mysql-bin.000002', # 刚才我们记录a节点数据库要执行复制的日志文件。
-> master_log_pos=118; #刚才记录的a节点数据库要复制的日志文件位置。
query ok, 0 rows affected (0.02 sec)
同时需要查看此时b节点的master信息，同步log日志及pos位置
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)
同时请修改b节点mysql主配置文件中
master-host=192.168.1.103 #主机a的ip地址
1.4.3 接着a节点：
修改b-àa方向同步配置项：
mysql>change master to
->master_log_file='mysql-bin.000003', # 刚才我们记录b节点数据库要执行复制的日志文件。
-> master_log_pos=98; #刚才记录的b节点数据库要复制的日志文件位置。
query ok, 0 rows affected (0.02 sec)
然后针对a、b节点启动同步状态，分别运行start slave; 然后查看同步状态，确保在每台机器上查看show slave status;得出
slave_io_running: yes
slave_sql_running: yes
即处于正常同步状态。
以上是针对单节点ip修改所做的操作，如果a、b节点ip 都需要修改，参考以上配置进行操作，最好先针对单个节点ip 进行修改，并确保单向同步没有问题后，再进行第二个节点ip修改以及配置。
--------------------------------------------------------------------------------
[j1]执行同步权限的用户
[j2]主机b的ip地址
[j3]同步帐户密码
[j4]执行同步权限的用户
[j5]主机a的ip地址
[j6]同步帐户密码
第一步：
在a数据库的my.ini中添加
复制代码 代码如下:
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的用户，该用户中需要包括数据库test1 test12test3的权限
master-password=usepwd102 #访问密码
master-port=3306 #端口，主机的mysql端口
master-connect-retry=60 #重试间隔60秒
replicate-do-db=test1 #同步的数据库
replicate-do-db=test2 #同步的数据库
replicate-do-db=test3 #同步的数据库
第二步：
在b数据库的my.ini中添加
复制代码 代码如下:
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的用户，该用户中需要包括数据库test1 test12test3的权限
master-password=usepwd101 #访问密码
master-port=3306 #端口，主机的mysql端口
master-connect-retry=60 #重试间隔60秒
replicate-do-db=test1 #同步的数据库
replicate-do-db=test2 #同步的数据库
replicate-do-db=test3 #同步的数据库
第三步：
将a的mysql数据的权限给b
mysql>grant file on *.* to 'use101'@'192.168.0.102'identifiedby 'pwd101';
将b的mysql数据的权限给b操作同上。
第四步：
重启ab数据库，后：
b机器：
mysql>slave start;
查看同步配置情况
a机器：
mysql>show master status\g;
b机器：
mysql>show slave status\g;
假如a与b数据库没有同步，检查mysql安装目录下的.err文件。
如果slave日志中报错信息如下：
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'在master上，执行以下语句查看权限：
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'已经授予了 replicaion slave 权限了，怎么还会报这个错呢？
通过查看手册和源码，才知道slave需要执行一个语句来更新slave列表：
show slave hosts;而执行这个语句则需要 replicaiton client 权限，因此才会报错。因此，只要重新给 帐号加上 replication client 权限就可以了。
grant selectreplication slavereplicaion client on *.* to 'use101'@'192.168.0.102'identified by 'pwd101';