Mysql实现企业级数据库主从复制架构实战：

一、 环境准备：

centos系统服务器2台、 一台用户做Mysql主服务器， 一台用于做Mysql从服务器， 配置好yum源、 防火墙关闭、 各节点时钟服务同步、 各节点之间可以通过主机名互相通信

二、 准备步骤：

1、 iptables -F && setenforce 清空防火墙策略，关闭selinux

2、 拿两台服务器都使用yum方式安装Mysql服务， 要求版本一致

3、 分别启动两台服务器mysql服务， 确保服务正常

三、实现步骤:

1、配置master主服务器（172.17.252.91）

对master进行配置，包括打开二进制日志，指定唯一的servr ID。例如，在配置文件加入如下值：

server-id=1 #配置server-id，让主服务器有唯一ID号

log-bin=mysql-bin #打开Mysql日志，日志格式为二进制

skip-name-resolve #关闭名称解析，（非必须）

（PS：在配置文件中，-和_是同一个意思，就是说log-bin和log_bin是一样的）

 

操作：

可以先给它改一个hostname名

image007.png

编辑MySQL配置文件

image009.png

修改文件内容：

image011.png

启动/重启服务 发现有二进制文件生成了

image013.png

用命令查看一下二进制文件

image015.pngid是1，从245开始

登录数据库再查看一下

image017.png

确实是position为245

 

2．创建复制帐号

在Master的数据库中建立一个备份帐户：每个slave使用标准的MySQL用户名和密码连接master

。进行复制操作的用户会授予REPLICATION SLAVE权限。

GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO slave@'172.17.%.%'IDENTIFIED BY 'magedu';

image019.png

进到mysql库里，查看一下user表，可以看到授权情况

image021.png

3．查看主服务器状态

在Master的数据库执行show master status，查看主服务器二进制日志状态

image023.png

因为刚才经过一系列的操作，position就改变了

查看一下binlog（图中这个2.row是我误操作授权错了，不用管它）

image025.png

看到目前最后的授权信息的position确实是从415-584了

 

4、配置slave从服务器（172.17.254.225）

对slave进行配置，打开中继日志，指定唯一的servr ID，设置只读权限。在配置文件加入如下值

server-id=2 #配置server-id，让从服务器有唯一ID号

relay_log = mysql-relay-bin #打开Mysql日志，日志格式为二进制

read_only = 1 #设置只读权限

log_bin = mysql-bin #开启从服务器二进制日志

log_slave_updates = 1 #使得更新的数据写进二进制日志中

 

操作：

image027.png

编辑文件/etc/my.cnf

image029.png

保存退出，启动/重启服务

然后可以看一下，确实已经生成了二进制日志

image031.png

5．启动从服务器复制线程

让slave连接master，并开始重做master二进制日志中的事件。

CHANGE MASTER TO MASTER_HOST='172.17.252.91',    #连接主IP

MASTER_USER='slave',

MASTER_PASSWORD='magedu',  #这里要跟上面授权的信息对应

MASTER_LOG_FILE='mysql-bin.000001',

MASTER_LOG_POS=xxx;     #这里要跟刚才查看授权的position起点（415）对应，就是从授权开始往下都同步

image033.png

执行start slave;  # 启动复制线程。

image035.png

6、查看从服务器状态

可使用SHOW SLAVE STATUS\G查看从服务器状态，如下所示，也可用show processlist \G查看当前复制状态：

Slave_IO_Running: Yes #IO线程正常运行

Slave_SQL_Running: Yes #SQL线程正常运行

image037.png

可以看到Read_Master_Log_Pos是584，说明已经读到Master的584位置，就是已经同步了

image039.png

Relay_log是它的中继日志

 

我们可以查看一下

image041.png

image043.png

这个授权信息我们并没有在从上执行过，但是它出现了，说明已经同步了。

我们可以继续测试一下能否同步：

先在主上创建一个lky库

image045.png

然后去从上看一下

image047.png

果然有lky库

然后我们再去主的lky库中建个表：

image049.png

再跑去从的那边看一下：

image051.png

果然有刚才新建的表，说明我们的同步已经实现成功。

 

四、 添加新slave服务器

假如master已经运行很久了， 想对新安装的slave进行数据同步， 甚至它没有master的数据。

此时， 有几种方法可以使slave从另一个服务开始， 例如， 从master拷贝数据， 从另一个slave克隆， 从最近的备份开始一个slave。 为了加快Slave与master同步， 可用以下方式先进行数据同步：

(1)master的某个时刻的数据快照；

(2)数据库的备份数据

(3)master的二进制日志文件。

MySQL复制过滤器

仅复制某些相关的数据，而非所有

两种实现思路：

（1）主服务器

主服务器仅向二进制日志中记录有关特定数据库相关的写操作；

binlog_do_db=                   #只记录某个库

binlog_ignore_db=         #只忽略记录某个库

 

（2）从服务器

从服务器的SQL THREAD仅重放关注的数据库或表相关的事件，并将其应用于本地；

replicate_do_db=             #只同步某个库

replicate_ignore_db=    #只忽略同步某个库

 

操作：

在从上，编辑配置文件/etc/my.cnf

image053.png

保存退出，重启服务

然后我们尝试在主上的lky库中的keke表中插入数据：

image055.png

image057.png

然后去从表中看一下keke表中的数据：

同步成功

image059.png

然后我们去主中再创建一个其他库，

image061.png

去从那边看一下，没有这个库，因为我们设置的只同步lky库

image063.png

所以说，这个设置是成功的。

企业常见数据库架构

一、一主多从

(1) 不同的slave扮演不同的作用(例如使用不同的索引，或者不同的存储引擎)；

(2) 用一个slave作为备用master，只进行复制；#主服务器挂了之后，可在从服务器执行

1> 在备机上执行STOP SLAVE 和RESET MASTER

2> 查看show slave status \G;

3> 然后修改应用的连接地址。

(3) 用一个远程的slave，用于灾难恢复；

二、互为主从（一般用于小并发量）

Master-Master复制的两台服务器，既是master，又是另一台服务器的slave。这

样，任何一方所做的变更，都会通过复制应用到另外一方的数据库中。

 

 

互为主从复制过程：

什么是自增长ID？

对于某些唯一性的字段，可以通过设置自增长ID来实现,自增长ID的数据，代表这个表中存在一

条唯一的记录；而自增长id是肯定不会重复的；

 

创建表叫userinfo，设置ID为自增长

create table userinfo (

id int PRIMARY KEY AUTO_INCREMENT,

name varchar(50) NOT NULL);

 

两边插入数据看数据增长

insert into userinfo(name) value('xiao'),('da'),('lao');

 

※互为主从会有一个小问题，就是如果两端同时写入时，被设为主键的id会重复冲突，因此我们可以设置一个自增长ID，让一段从1开始步长为2（每次增长2个单位）的自增长，另一端从2开始步长为2的自增长，这样设置过之后，一端的id就只有奇数，另一端id只有偶数，就不会冲突了。

 

 

互为主从：两个节点各自都要开启binlog和relay log；

1、数据不一致；

2、自动增长id；

在配置文件中，

定义一个节点使用奇数id

auto_increment_increment=2     #表示自增长字段每次递增的量

auto_increment_offset=1            #表示自增长字段从哪个数开始

另一个节点使用偶数id

auto_increment_increment=2

auto_increment_offset=2

配置：

1、server_id必须要使用不同值；

2、均启用binlog和relay log；

3、存在自动增长id的表，为了使得id不相冲突，需要定义其自动增长方式；

服务启动后执行如下两步：

4、都授权有复制权限的用户账号；

5、各把对方指定为主节点；

注意：关闭防火墙！！

 

操作：

在刚才主从的基础上，我们来继续修改为互为主从

修改第二台（之前的从）的配置文件：（将只读那句去掉）

image065.png

修改第一台（之前的主）的配置文件：（添加开启relay log那行）

image067.png

然后都保存退出，重启服务

可以先看一下第二台之前配的从是否运转良好

image069.png

说明现在已经第二台是第一台的slave了，现在再配一下第一台让它做第二台的slave即可

先看一下第二台的状态：

image071.png

看到是第三个日志，并且position号码为245

然后去第一台上输入如下指令：（因为授权的那一步我们已经通过同步做过了，于是直接进行连接主即可）

CHANGE MASTER TO MASTER_HOST='172.17.254.225',  

MASTER_USER='slave',

MASTER_PASSWORD='magedu', 

MASTER_LOG_FILE='mysql-bin.000003',

MASTER_LOG_POS=245;     

image073.png

然后start slave;启动线程。

image075.png

使用show slave status\G;命令查看设置情况

image077.png

设置成功

我们来测试一下是否能同步：

我们来在lky库中新建一个id为主键且自增长的表：

image079.png

在一端建完了，另一端也可以看到

image081.png

我们可以试一下插入三个name信息，看id是否真的是自增长：

insert into userinfo(name) value('ll'),('kk'),('yy');

image083.png

插入成功，确实id是自增长的，

然后我们去配置文件中设置增长的信息将它设成奇数偶数那种形式。

一台走奇数：

image085.png

另一台走偶数：

image087.png

两台都保存退出，重启服务

在偶数边那台插入三个数据，发现id确实是偶数的：

image089.png

再去奇数边插入三个，可以看到确实是奇数的id：

image091.png

说明我们设置成功，这样就再也不用担心当主从同时写入时id可能会冲突了。

异步同步半同步

image095.png

半同步复制：

image097.png

image099.png

操作：

那我们继续上面实现的互为主从情况下，接着去实现半同步复制

1、主节点：

INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so'; 安装半同步复制的库文件image101.png

SHOW GLOBAL VARIABLES LIKE 'rpl_semi%'; 查看状态

    image103.png

SET GLOBAL rpl_semi_sync_master_enabled=ON; 将半同步开启

    image105.png

然后需要重新加载一下线程（就是先stop一下，再start一下）

    image107.png

2、从节点：

INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';  安装从插件

SHOW GLOBAL VARIABLES LIKE 'rpl_semi%';  查看状态

    image109.png

SET GLOBAL rpl_semi_sync_slave_enabled = ON; 开启半同步复制

    image111.png

然后同样需要重新加载一下线程

    image113.png

3、查看设置

我们退出数据库，查看一下日志

    image115.png

看到主的这边日志显示半同步开启了

image117.png

去另一边从的看日志，也显示开启成功了：image119.png