mysql主从复制与主主复制-白红宇

mysql主从复制与主主复制

阅读量：7223 次

发布时间：2019-06-29

本文共 5290 字，大约阅读时间需要 17 分钟。

mysql主从复制与主主复制

1、MySQL简介

MySQL是一种关系数据库管理系统，关系数据库将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

MySQL所使用的 SQL 语言是用于访问数据库的最常用标准化语言。MySQL 软件采用了双授权政策，分为社区版和商业版，由于其体积小、速度快、总体拥有成本低，尤其是开放源码这一特点，一般中小型网站的开发都选择 MySQL 作为网站数据库。

由单台MySQL数据库服务器不能满足实际的需求。此时数据库集群就很好的解决了这个问题了。采用MySQL分布式集群，能够搭建一个高并发、负载均衡的集群服务器（这篇博客暂时不涉及）。在此之前我们必须要保证每台MySQL服务器里的数据同步。数据同步我们可以通过MySQL内部配置就可以轻松完成，主要有主从复制和主主复制。

2、基础环境搭建：

2.1 环境说明

IP：192.168.92.143

IP：192.168.92.156

CentOS 7.4.1708

mysql 5.7.22

2.2 设置主机名、静态解释：/etc/hosts

2.3 ssh免密登录

ssh-keygen -t rsa

ssh-copy-id -i .ssh/id_rsa.pub 192.168.92.143

2.4 ntp同步

yum install chrony -y

编辑 /etc/chrony.conf 文件

server master iburst

allow 192.168.92.0/24

systemctl enable chronyd.service

systemctl start chronyd.service

chronyc sources

2.5 安装数据库：

(因使用的是公司脚本，后期更新mysql的安装）

3、MySQL主从复制

3.1 配置文件

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

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

server_id=1#两台服务器的ID要不一样

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

192.168.92.143 为主数据库服务器

log-bin=mysql-bin

server_id=1

192.168.92.156 为从数据库服务器

log-bin=mysql-bin

server_id=2

配置完成后要记得重启服务

systemctl restart mysqld

3.2 开始构建主从复制

第一步：

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

用户：backup

密码：1234

mysql>grant replication slave on . to 'backup'@'192.168.92.156' identified by '1234';

mysql>flush privileges;

第二步：

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

mysql>SHOW MASTER STATUS;

第三步：        告知二进制文件名与位置        在192.168.92.156中执行：

mysql> change master to MASTER_HOST='192.168.92.143', MASTER_USER='backup', MASTER_PASSWORD='1234', MASTER_LOG_FILE='mysql-bin.000003', MASTER_LOG_POS=604;

完成主从复制配置

3.3 测试主从复制

在192.168.92.156中

mysql>SHOW SLAVE STATUS\G #查看主从复制是否配置成功

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

实际测试：

--登陆192.168.92.143主MySQL

mysql>SHOW DATABASES;

--登陆192.168.92.156从MySQL

mysql>SHOW DATABASES;

192.168.92.143主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.92.156从MySQL操作：

mysql>show databases;

mysql>use aa;

mysql>show tables;

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

4、MySQL主主复制

4.1 实现原理

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

4.2 配置文件

--192.168.92.143

server-id=1 #任意自然数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.92.156

server-id=2

log-bin=mysql-bin

auto_increment_increment=2

auto_increment_offset=2

replicate-do-db=aa

配置好后重启MySQL

4.3 开始构建主主复制

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

第一步：

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

用户：backup2

密码：1234

mysql>grant replication slave on . to 'backup2'@'192.168.92.143' identified by '1234';

mysql>flush privileges;

第二步：

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

mysql>show master status;

第三步：       告知二进制文件名与位置       在192.168.92.143中执行：

mysql> change master to MASTER_HOST='192.168.92.156', MASTER_USER='backup2', MASTER_PASSWORD='1234', MASTER_LOG_FILE='mysql-bin.000007', MASTER_LOG_POS=615;

完成主主复制配置

重启MySQL服务

4.4 测试主主复制

登录mysql查看：

mysql>SHOW SLAVE STATUS\G #查看主从复制是否配置成功

192.168.92.143

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

测试：

--192.168.92.143

mysql>use aa;       mysql>select*from tab1;       tab1无数据       --192.168.92.156       mysql>use aa;       mysql>select*from tab1;       tab1无数据       --192.168.92.1443插入数据       mysql>insert into tab1 (name) value(‘11’),(‘11’),(‘11’);       --192.168.92.156插入数据       mysql>insert into tab1 (name) value ('12'),('12'),('12');

查看数据：

两个主机数据结果一样！

5、注意事项

5.1 常见出错点：

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

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

（3）stop slave后，数据变更，再start slave。出错。这步可以省略，但要记得重启服务。

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

（4）当配置完成Slave_IO_Running、Slave_SQL_Running不全为YES时，大多数问题都是数据不统一导致。这时先重启服务，再进行查看。

5.2mysql error code(备忘)：

1005：创建表失败

1006：创建数据库失败