Windows 在MariaDB数据库中进行一主一从、多主一从数据同步配置（亲测有效）

最新推荐文章于 2023-06-19 16:42:23 发布

念旧、sunshine

最新推荐文章于 2023-06-19 16:42:23 发布

阅读量1.3k

点赞数 1

分类专栏： SQL

本文链接：https://blog.csdn.net/qq_36628003/article/details/100540390

版权

SQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

解决的问题场景：将各个数据库的表整合到一个地方进行统计和分析。要是每次连接不同的数据库实例操作，非常耗力。

MySQL一般只支持一主一从复制数据。虽然也可以做到，但是mysql的局限性很大。

一、准备前工作

多主一从：将主站的数据同步到从站中。即：将201主库的数据同步到509从库中。

Mariadb数据同步

二、201（主库master:192.168.140.52）

1、在201主库中添加my.ini下的[mysqld]下添加：

#开启binlog功能
log-bin=binlog         
log-bin-index=binlog.index
sync_binlog=0
server_id=201    #按照电站名称命名
bind-address=0.0.0.0 #拒绝访问引起的
binlog-do-db=test #同步的数据库

注解：

server-id:不能重复。

binlog-do-db：设置要同步的数据库

bind-address=0.0.0.0 ：可选；我是因为出现了以下错误才加上的

error：Mysql "Lost connection to MySQL server at ‘reading initial communication packet', system error: 0

注：修改了my.ini必须重启服务。my.ini很容易引起数据库服务起不起来

2、创建用户，并给用户授权仅该IP地址即509可以复制数据

create user repl;
grant replication slave on *.* to 'repl'@'192.168.140.65' identified by 'ty123456';
flush privileges;
show master status;

注解：

新建用户的原因：生产环境中是不建议使用的，必须自己重新创建一个新用户进行登录，

replication slave：复制权限。其他权限有：create\update\delete\drop...等

%表示允许所有的IP访问，可以自己设置只允许哪些IP访问，如把%替换为192.168.140.65表示只允许这个IP访问

*.*：表示这个权限是针对所有库的所有表。test.*:表示test数据的所有表

192.168.140.65：表示只允许这个IP访问，即slaves的IP地址；%表示允许所有的IP访问。。理解：主库需要赋予从库权限复制数据的权限。

show master status：来获取二进制日志的当前位置信息.。只要对数据库有操作。他的position就会改变。对my.ini操作。他的File就会变化。

记下 File 以及 Position 信息. 如果刚刚才开启了二进制日志功能，则为空.
然后,开始从master 拷贝 data 到 slave,请参考: Backup, Restore and Import将数据从master拷贝到slave以后,可以执行"UNLOCK TABLES;"释放master上的锁。

三、509（从库slaves：192.168.140.65）

1、在509电站的my.ini下的[mysqld]下添加以下代码激活二进制日志.：（不能添加到[client]下面。讲究顺序）

#开启binlog功能
log-bin=binlog         
log-bin-index=binlog.index
sync_binlog=0
server_id=509 #不能重复
bind-address=0.0.0.0 #
binlog-do-db=test

注解：同上

2、数据导入后,开始启动复制功能了。

change master 'r1' to 
master_host='192.168.140.52', 
master_port=3306,
master_user='repl',
master_password='ty123456',
master_log_file='binlog.000004',
master_log_pos=530;
start SLAVE 'r1';

stop slave 'r1';#重新更新change master 需要停止复制
show all slaves status;#显示所有状态，看看有没有错误信息没有就成功了

注解：

‘r1’:每一个通道的唯一标识

master_host：从哪个数据库开始复制数据的IP地址，即：主库的IP地址

master_user：连接数据库的用户名，即登录主库的用户名

master_password:连接数据库的密码，登录主库的密码

master_log_file、master_log_pos：从主库哪个BINLOG文件开始读取，偏移量是多少。值是通过show master status 获取的。

四、前面二、三步骤配置的是一主一从。那么要配置多主一从还需要做什么呢？

修改必须要改的参数值代码，执行步骤二和步骤三的第2点。

eg：新增一个主库101

1、（在101my.ini添加）需要修改server_id

#开启binlog功能
log-bin=binlog         
log-bin-index=binlog.index
sync_binlog=0
server_id=101    #按照电站名称命名
bind-address=0.0.0.0 #拒绝访问引起的
binlog-do-db=test #同步的数据库

2、（101数据库执行）不变。需要记住show master status 的参数值。方便下面的步骤3

create user repl;
grant replication slave on *.* to 'repl'@'192.168.140.65' identified by 'ty123456';
flush privileges;
show master status;

3、(在509数据库执行）需要修改唯一通道标识符、master_host、根据上面步骤2查询出来的值修改master_log_file和master_log_pos

change master 'r2' to 
master_host='192.168.140.101', 
master_port=3306,
master_user='repl',
master_password='ty123456',
master_log_file='binlog.000002',
master_log_pos=2140;
start SLAVE 'r2';

stop slave 'r2';#重新更新change master 需要停止复制
show all slaves status;#显示所有状态，看看有没有错误信息没有就成功了

需要注意，有一些系统配置选项可能会影响主从复制，查看下面的变量以避免发生问题：

skip-networking,如果 "skip-networking=1"，则服务器将限制只能由localhost连接，阻止其他机器远程连到此服务器上。
bind_address,类似地，如果服务器只监听 127.0.0.1(localhost)的TCP/IP连接,则远程的 slave也不能连接到此服务器.

测试：201添加了数据看看509是否更新
通过 show all slaves status 命令可知是否同步成功。