学习
实践
活动
专区
工具
TVP
写文章

MySQL主从复制与主主复制

1、简介

MySQL作为世界上使用最为广泛的数据库之一,免费是其原因之一。但不可忽略的是它本身的功能的确很强大。随着技术的发展,在实际的生产环境中,由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群,能够搭建一个高并发、负载均衡的集群服务器(这篇博客暂时不涉及)。在此之前我们必须要保证每台MySQL服务器里的数据同步。数据同步我们可以通过MySQL内部配置就可以轻松完成,主要有主从复制和主主复制。

2、环境说明

两台linux虚拟主机

Linux版本CentOS7、MySQL 5.7

ip:192.168.1.11、192.168.1.12()()

3、主从复制

3.1、MySQL

已经安装好,并且没有任何数据()

3.2、配置文件

一般Linux中的MySQL配置文件都在/etc/my.cnf(windows中的配置文件为mysql.ini)

log-bin=mysql-bin #开启二进制日志

server-id=1 #设置server-id

注意:二进制日志必须开启,因为数据的同步实质上就是其他的MySQL数据库服务器将这个数据变更的二进制日志在本机上再执行一遍。

192.168.1.11 为主数据库服务器

192.168.1.12 为从数据库服务器

3.3、开始构建主从复制

第一步:

在192.168.1.11中创建一个192.168.1.12主机中可以登录的MySQL用户

用户:mysql12

密码:mysql12

MySQL 5.7及以前版本

mysql> grant replication slave on *.* to ‘mysql12’@’192.168.1.12’ identified by ‘mysql12’;

mysql> flush privileges;

MySQL 8创建

create user 'mysql1'@'192.168.1.12' identified by 'mysql1';#创建用户

grant replication slave on *.* to 'mysql1'@'192.168.1.12';#授权

flush privileges;

第二步:

查看192.168.1.11MySQL服务器二进制文件名与位置

mysql> show master status;

第三步:

告知二进制文件名与位置

在192.168.1.12中执行:

mysql> CHANGE MASTER TO

MASTER_HOST='192.168.1.75',

MASTER_USER='mysql1',

MASTER_PASSWORD='mysql1',

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=2188;

完成主从复制配置

3.4、测试主从复制

在192.168.1.12中

mysql> start slave; #开启复制

mysql> show slave status\G #查看主从复制是否配置成功

当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常

实际测试:

--登陆192.168.1.11主MySQL

mysql> show databases;

--登陆192.168.1.12从MySQL

mysql> show databases;

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

192.168.1.11主MySQL操作:

mysql>create database aa;

mysql>use aa;

mysql>create table tab1(id int auto_increment,name varchar(10),primary key(id));

mysql>show databases;

mysql>show tables;

192.168.1.12从MySQL操作:

mysql>show databases;

mysql>show tables;

由上面两个结果图可得知,两主机达到了数据同步。主从复制的配置就是如此的简单。

3.5、还可以用到的其他相关参数:

master开启二进制日志后默认记录所有库所有表的操作,可以通过配置来指定只记录指定的数据库甚至指定的表的操作,具体在mysql配置文件的[mysqld]可添加修改如下选项:# 不同步哪些数据库

binlog-ignore-db = mysql

binlog-ignore-db = test

binlog-ignore-db = information_schema

# 只同步哪些数据库,除此之外,其他不同步

binlog-do-db = game

4、MySql主主复制

4.1、实现原理

主主复制即在两台MySQL主机内都可以变更数据,而且另外一台主机也会做出相应的变更。聪明的你也许已经想到该怎么实现了。对,就是将两个主从复制有机合并起来就好了。只不过在配置的时候我们需要注意一些问题,例如,主键重复,server-id不能重复等等。

4.2、配置文件

--192.168.1.11

server-id=11 #任意自然数n,只要保证两台MySQL主机不重复就可以了。

log-bin=mysql-bin #开启二进制日志

auto_increment_increment=2 #步进值auto_imcrement。一般有n台主MySQL就填n

auto_increment_offset=1 #起始值。一般填第n台主MySQL。此时为第一台主MySQL

binlog-ignore=mysql #忽略mysql库

binlog-ignore=information_schema #忽略information_schema库

replicate-do-db=aa #要同步的数据库,默认所有库

--192.168.1.12

server-id=12

log-bin=mysql-bin

auto_increment_increment=2

auto_increment_offset=2

replicate-do-db=aa

配置好后重启MySQL

4.3、开始构建主主复制

因为主主复制是两个主从复制组合一起,所以我就接着上面主从复制接着配置。

第一步:

在192.168.1.12中创建一个192.168.1.11主机中可以登录的MySQL用户

用户:mysql11

密码:mysql11

mysql> grant replication slave on *.* to ‘mysql11’@’192.168.1.11’ identified by ‘mysql11’;

mysql> flush privileges;

第二步:

在192.168.1.12查看二进制日志名和位置

mysql>show master status;

第三步:

告知二进制文件名与位置

在192.168.1.11中执行:

mysql>CHANGE MASTER TO

MASTER_HOST=’192.168.1.12’,

MASTER_USER=’mysql11’,

MASTER_PASSWORD=’mysql11’,

MASTER_LOG_FILE=’mysql-bin.000084’,

MASTER_LOG_POS=107;

完成主主复制配置

4.4、测试主主复制

分别开启start slave;

mysql> show slave status\g #查看主从复制是否配置成功

192.168.1.11

192.168.1.12

当看到Slave_IO_Running: YES、Slave_SQL_Running: YES才表明状态正常

测试:

--192.168.1.11

mysql>use aa;

mysql>select*from tab1;

tab1无数据

--192.168.1.12

mysql>use aa;

mysql>select*from tab1;

tab1无数据

--192.168.1.11插入数据

mysql>insert into tab1 (name) value(‘11’),(‘11’),(‘11’);

--192.168.1.12插入数据

mysql>insert into tab1 (name) value(‘22’),(‘22’),(‘22’);

查看数据:

两个主机数据结果一样!

主主复制配置成功!

5、注意事项

1、主主复制配置文件中auto_increment_increment和auto_increment_offset只能保证主键不重复,却不能保证主键有序。

2、当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时,show slave status\G信息中有错误提示,可根据错误提示进行更正。

3、Slave_IO_Running、Slave_SQL_Running不全为YES时,大多数问题都是数据不统一导致。

常见出错点:

1、两台数据库都存在db数据库,而第一台MySQL db中有tab1,第二台MySQL db中没有tab1,那肯定不能成功。

2、已经获取了数据的二进制日志名和位置,又进行了数据操作,导致POS发生变更。在配置CHANGE MASTER时还是用到之前的POS。

3、stop slave后,数据变更,再start slave。出错。

终极更正法:重新执行一遍CHANGE MASTER就好了。

  • 发表于:
  • 原文链接https://kuaibao.qq.com/s/20180820G10ZCK00?refer=cp_1026
  • 腾讯「腾讯云开发者社区」是腾讯内容开放平台帐号(企鹅号)传播渠道之一,根据《腾讯内容开放平台服务协议》转载发布内容。
  • 如有侵权,请联系 cloudcommunity@tencent.com 删除。

关注

腾讯云开发者公众号
10元无门槛代金券
洞察腾讯核心技术
剖析业界实践案例
腾讯云开发者公众号二维码

扫码关注腾讯云开发者

领取腾讯云代金券