Window环境下配置MySQL 5.6的主从复制

最新推荐文章于 2022-05-10 17:18:20 发布

转载最新推荐文章于 2022-05-10 17:18:20 发布 · 72 阅读

0 ·

CC 4.0 BY-SA版权

原文链接：http://www.cnblogs.com/lonelyxmas/p/10757756.html

本文详细介绍了在Windows环境下配置MySQL5.6主从复制的过程，包括新数据库和已有数据库的配置步骤，以及数据库备份的方法。

Window环境下配置MySQL 5.6的主从复制

原文: Window环境下配置MySQL 5.6的主从复制

1、环境准备

Windows 7 64位
MySQL 5.6
主库：192.168.103.207
从库：192.168.103.208

2、配置新数据库的主从复制

2.1、修改主库配置文件

先在主数据库中创建新数据库rep_test。

然后编辑主数据库的 my.ini 文件

在 [mysqld] 节点中增加或修改如下内容：

server-id=1 #指定唯一的ID，1至32，必须的
log-bin=mysql-log-bin #指定二进制日志存放路径，必须的
binlog-do-db=rep_test #指定要同步的数据库，必须的
#binlog-ignore-db=mysql #指定不要同步的数据库，如果指定了binlog-do-db就不用再指定该项

2.2、配置主库备份账户

重启主数据库，然后在主数据库中建立一个备份账户

mysql>grant replication slave on *.* to slave@192.168.103.208 identified by 'slave123' ;
mysql>flush privileges;

PS:identified by 指定的slave是账号slave@192.168.1.128 的密码

2.3、查看主服务器状态

显示主服务器的状态信息，并且找到File 和 Position 的值记录下来；

mysql>show master status;

2.4、修改从库配置文件

在从数据库中创建新的数据库rep_test。

然后编辑从数据库的 my.ini 文件

在 [mysqld] 节点中增加如下内容：

server-id=2 #指定唯一的ID，2至32，必须的，并且不能跟主数据库一样
replicate-do-db=rep_test #指定要同步的数据库，必须的
#replicate-ignore-db=mysql #指定不要同步的数据库，

2.5、设置从库slave

重启从数据库，设置登录主数据库的账号和密码等信息，然后启动slave

mysql>change master to master_host='192.168.103.207',master_port=3307,master_user='slave',master_password='slave123', master_log_file='mysql-log-bin.000001',master_log_pos=407; 
mysql>start slave;

2.6、查看从数据库的信息

mysql>show slave status \G;

如果出现： Slave_IO_Running: Yes Slave_SQL_Running: Yes以上两项都为Yes，那说明没问题了

2.7、测试主从复制

在主数据库中创建一个新的表，然后再切换到从数据库查看是否同样多出同名的数据库

2.8、清除主从关系

mysql主从复制中，需要将主从复制关系清除，需要取消其从库角色。这可通过执行 RESET SLAVE ALL 清除从库的同步复制信息、包括连接信息和二进制文件名、位置。从库上执行这个命令后，使用show slave status将不会有输出。

reset slave是各版本Mysql都有的功能，在stop slave之后使用。主要做：
删除master.info和relay-log.info文件；
删除所有的relay log（包括还没有应用完的日志），创建一个新的relay log文件；
从Mysql 5.5开始，多了一个all参数。如果不加all参数，那么所有的连接信息仍然保留在内存中，包括主库地址、端口、用户、密码等。这样可以直接运行start slave命令而不必重新输入change master to命令，而运行show slave status也仍和没有运行reset slave一样，有正常的输出。但如果加了all参数，那么这些内存中的数据也会被清除掉，运行show slave status就输出为空了。

mysql>stop slave;
QueryOK, 0 rowsaffected (0,00 sec)

mysql>reset slave all;
QueryOK, 0 rowsaffected (0,04 sec)

mysql> show slave status\G
Emptyset (0,00 sec)

RESET MASTER

功能说明：删除所有的binglog日志文件，并将日志索引文件清空，重新开始所有新的日志文件。用于第一次进行搭建主从库时，进行主库binlog初始化工作；
测试如下：

未删除前

[root@mysql01 mysql]# pwd
/usr/local/data/mysql
[root@mysql01 mysql]# ls
auto.cnf    client-cert.pem  ibdata1      ibtmp1                     localhost.localdomain.pid  mysql-bin.000002  mysql-bin.000005  mysqld_safe.pid     public_key.pem   sys
ca-key.pem  client-key.pem   ib_logfile0  imove                      mysql                      mysql-bin.000003  mysql-bin.000006  performance_schema  server-cert.pem
ca.pem      ib_buffer_pool   ib_logfile1  localhost.localdomain.err  mysql-bin.000001           mysql-bin.000004  mysql-bin.index   private_key.pem     server-key.pem
[root@localhost bin]# ./mysql -uroot -p123456
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000006 | 16663694 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
mysql>

当前有6个binlong日志，且Position的位置为16663694
运行RESET MASTER

mysql> reset master;
Query OK, 0 rows affected (0.03 sec)


mysql> show master status\G;
*************************** 1. row ***************************
            File: mysql-bin.000001
        Position: 107
    Binlog_Do_DB: 
Binlog_Ignore_DB: 
1 row in set (0.00 sec)

显示所有的binlog已经被删除掉，且binlog从000001 开始记录

注：当数据库要清理binlog文件的时候，可以通过操作系统进行删除，也可以运行reset master进行删除。但是如果当前是主数据库，且主从数据库正常的时候，千万不能用这种方式删除。

【使用场景】第一次搭建主从数据库时，用于主库的初始化binglog操作。

RESET SLAVE

功能说明：用于删除SLAVE数据库的relaylog日志文件，并重新启用新的relaylog文件；
测试如下：

登录从数据库，未删除前

mysql> show slave status\G;
*************************** 1\. row ***************************
               Slave_IO_State: Connecting to master
                  Master_Host: 192.168.47.167
                  Master_User: server
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000019
          Read_Master_Log_Pos: 12992

Relay_Log_File: mysql02-relay-bin.000004
                Relay_Log_Pos: 4

         Relay_Master_Log_File: mysql-bin.000019

当前relaylog为0004；
先停止slave，运行RESET MASTER

mysql> stop slave;        
Query OK, 0 rows affected (0.01 sec)        
mysql> reset slave;                
Query OK, 0 rows affected (0.04 sec)
mysql> show slave status\G;
*************************** 1\. row ***************************
               Slave_IO_State: 
                  Master_Host: 192.168.47.167
                  Master_User: server
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: 
          Read_Master_Log_Pos: 4

**   Relay_Log_File: mysql02-relay-bin.000001
                Relay_Log_Pos: 4**

RESET SLAVE将使SLAVE忘记主从复制关系的位置信息。该语句将被用于干净的启动, 它删除master.info文件和relay-log.info 文件以及所有的relay log 文件并重新启用一个新的relaylog文件。

使用场景：当原来的主从关系被破坏之后，从库经过重新初始化后直接连接会报 ERROR 1201的错误，运行reset slave后，重新配置主从连接就可以了；

3、配置旧数据库的主从复制

3.1、修改主库配置文件

如果一开始数据库的架构不是主从复制，并且运行一段时间后已经有数据存在，那配置的方式略有不同。

在从数据库中创建新的数据库landclash。

编辑主数据库的 my.ini 文件，使用以下内容：

server-id=1 #指定唯一的ID，1至32，必须的
log-bin=mysql-log-bin #指定二进制日志存放路径，必须的
binlog-do-db=landclash
#binlog-ignore-db=mysql #指定不要同步的数据库，如果指定了binlog-do-db就不用再指定该项

配置主库备份账户

mysql>grant replication slave on *.* to slave123@192.168.103.208 identified by 'slave123' ;
mysql>flush privileges;

3.2、主数据库中锁定所有的表

重启主数据库，然后在主数据库中锁定所有的表

mysql>flush tables with read lock;

3.3、查看主服务器状态

显示主服务器的状态信息，并且找到File 和 Position 的值记录下来；

mysql>show master status;

3.4、数据拷贝

将主数据库data目录下需要做主从复制的数据库的同名目录拷贝到从数据库的data目录下

注意：拷贝的话也要拷贝 ibdata1 文件，不然会不显示表结构

3.5、修改从库配置文件

编辑从数据库的 my.ini 文件，加上一下内容：

server-id=2 #指定唯一的ID，2至32，必须的，并且不能跟主数据库一样
replicate-do-db=landclash #指定要同步的数据库，必须的
#replicate-ignore-db=mysql #指定不要同步的数据库，

3.6、设置从库slave

重启从数据库，因为主数据库在重新配置my.ini后，日志文件变成新的文件，所以需要再次设置登录主数据库的账号和密码等信息

mysql> stop slave;
mysql>change master to master_host='192.168.103.207',master_port=3307,master_user='slave123',master_password='slave123', master_log_file='mysql-log-bin.000001',master_log_pos=742; 
mysql>start slave;

3.7、查看从数据库的信息

再次输入查看从数据库状态的命令

mysql>show slave status \G;

我们看到红框标注的地方跟之前做新数据库主从复制不一样了。

3.8、主数据库解锁

完成上述配置后，回到主数据库，将表解锁

mysql>unlock tables;

之后在主数据库的修改就能同步到从数据库上了。

4、数据库备份

4.1备份脚本

sql备份 DataBackupSql.bat

rem *******************************Code Start*****************************
@echo off

set "Ymd=%date:~,4%%date:~5,2%%date:~8,2%"
"C:\Program Files\MySQL\MySQL Server 5.6\bin\mysqldump" -P3307 -uroot123 -proot123 landclash > c:\db_bak\landclash_%Ymd%.sql
@echo on
rem *******************************Code End*****************************

文件备份 DataBackupFile.bat

*******************************Code Start*****************************
net stop mysql
xcopy "C:\ProgramData\MySQL\MySQL Server 5.6\data\landclash\*.*" "c:\db_bak\landclash\%date:~0,10%\" /S /I
xcopy "C:\ProgramData\MySQL\MySQL Server 5.6\data\ibdata1" "c:\db_bak\landclash\%date:~0,10%\" /S /I
net start mysql
*******************************Code End *****************************

posted on 2019-04-23 17:36 NET未来之路阅读( ...) 评论( ...) 编辑收藏

转载于:https://www.cnblogs.com/lonelyxmas/p/10757756.html