MySQL主从复制、读写分离（利用Amoeba和Mycat）、完全同步

koeda（粉丝冲刺一个亿）

已于 2023-11-16 19:40:27 修改

阅读量598

点赞数 1

分类专栏： MySQL 文章标签：数据库运维 mysql

于 2023-11-14 19:11:22 首次发布

本文链接：https://blog.csdn.net/koeda1/article/details/134405742

版权

MySQL 专栏收录该内容

7 篇文章 0 订阅

订阅专栏

一、主从复制

1、概念

主从复制：主MySQL上的数据，新增、修改库、表、表里的数据都会同步到从MySQL服务器上

主从复制是单向的，只能从主复制到从服务器

1.1主从复制延迟问题：

网络延迟
主从硬件设备（CPU主频、内存的读写性能IO、硬件IO）
同步复制而不是异步复制

解决方案：

硬件方面：主库一般来说不需要动的太多，从库的硬件配置要更好。提升随机写的性能。硬盘可以考虑缓存固态的、升级CPU的核数、扩展一下内存。尽量使用物理机（不要用云服务器）
网络层面，主从服务器都配置在一个局域网内，尽量避免跨网段和跨机房
架构方面：做读写分离，把写入控制在主，从库负责读，减轻从库的压力
配置MySQL方面：从配置文件的角度实现性能最大化

1.2、MySQL安全和性能配置：

追求安全性配置：

innodb_flush_log_at_trx_commit=1

#每次事务提交时都会刷新事务日志。以确保持久性，最高级别的数据安全性，但是会影响性能，默认就是1

0就是事务提交时不会立刻刷新，而是每秒刷新一次，可以提高性能，但是发生故障会导致数据丢失。

2表示事务提交时，事务日志不会写入硬盘而是保存在系统缓存，同时也不会进行刷新。有一定的安全性和性能。但是对内存要求比较高。生成中一般都是默认1

sync binlog=1

#1也是默认值，每次提交事务之后，直接把二进制日志刷新到磁盘，以确保日志的持久性，占用性能比较高，但是安全性高

0表示二进制日志写入到缓存，也不会刷新日志，故障发生也会丢失数据，对内存的要求也提高了

自定义数字N：表示每N个事务才执行一次刷新到磁盘。提高性能，但是一旦崩溃数据也会大量丢失。

追求性能化配置：

sync binlog=0

innodb_flush_log_at_trx_commit=2

logs-slave-updates=0

#从库的更新不会写入二进制日志（不建议）

innodb buffer_pool_size 300M 500G

#控制innodb存储引擎缓冲池的大小，设置的数值越高，可以提高他的innodb的性能，更多的数据和索引都可以缓存在内存中。减少磁盘的访问次数。对系统内存要求比较高。

1.3、主从复制的工作过程：

主节点的数据记录发生变化都会记录在二进制日志
Slave节点会在一定时间内对主库的二进制文件进行探测，看其是否发生变化，如果有变化，从库会开启一个IO的线程，请求master主库的二进制事件
主库会给每一个IO读写的线程启动一个dump线程，用于发送二进制事件给从库，从库通过IO读写线程获取更新，Slave_sql 负责将更新写入到从库本地。实现主从一致

1.4、mysql主从复制注意点：

只能在主库上发生变化，然后同步到从
复制过程是串行化过程，在从库上复制是串行的，主库的并行更新不能在slave从库上并行操作
主从复制的设计目的就是为了在主库上写，在从库上查。读写分离，实现高可用。
若主从版本不一致，从的版本一定要高于主，保证可以向下兼容
因为若主的版本更新，低版本的从无法兼容的。

流程图：

1.5、MySQL的主从复制的模式：

异步复制：MySQL的默认复制方法就是异步复制。

异步：只要执行完之后。客户端提交事务，主MySQL会立即把结果返回给从服务器，主MySQL并不关心MySQL从MySQL是否已经接收，并且处理。主MySQL一旦崩溃，主MySQL的事务可能没有传到从MySQL，这个时候强行把从提升为主，可能到新的主MySQL，数据不完整。（很少见，工作中都是异步复制）

全同步复制：主库执行完成一个事务，要等所有的从库都执行了该事务之后才会返回客户端，因为需要等待所有从库全部执行完成，性能必然下降。（适用于对数据一致性和数据完整性要求非常高的场景）
半同步复制：介于异步复制和全同步复制之间。主库执行完一个客户端提交的事务之后，至少等待一个从库接收并处理完成之后才会返回非客户端。半同步在一定程度上提高了数据的安全性。夜壶有一定的延迟。这个延迟的时间一般是一个tcp-ip的往返时间（从发送到接受的时间，单位是毫秒。RTT），半同步复制最好是在低延迟的网络中使用。

2、主从复制实验：

架构：

3台MySQL：

MySQL31（主）、32（从）、33（从）

test21（读写分离客户服务器）、test22（客户端）

全部关闭防火墙安全机制

三台MySQL主从服务器之间时间同步：

yum -y install ntp

vim /etc/ntp.cnf

尾行添加：

server 127.127.0.0

fudge 127.127.0.0 stratum 8

##下面截图写错了！！

这里的IP地址第三位写的是是本机ip第三位。本机20.0.0.0，所以这里填0

stratum 数字：数字越小时间精确度越高，设置fudge 8时间层级8最高到15，从本地获取之间同步源

重启ntpd

两台从MySQL配置ntp

/usr/sbin/ntpdata 20.0.0.31 指向主MySQL

在生产中设置定时任务：crontab -e -u root

date查看时间

时间同步之后开始配置：

主MySQL：

vim /etc/my.cnf

log-bin=master-bin

binlog_format = MIXED

log-slave-updates=true ——允许从服务器复制数据时，可以从主的二进制日志写到自己的二进制日志中

重启MySQL

进入MySQL

给从服务器新建用户和授权：

grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123456';

flush privileges;

show master status;

靠位置点同步

从MySQL：

vim /etc/my.cnf

server-id不能一样，不能重复

添加：

relay-log=relay-log-bin

relay-log-index=slave-relay-bin.index

relay_log_recovery=1 ——不加默认是0，设1表示开启中继日志的恢复。从服务器出现异常，或者崩溃时，从服务器会从主服务器的二进制日志的正确读取和应用中继日志。同步。

relay-log=relay-log-bin:

指定了从服务器上中继日志的基本文件名。在这个例子中，

中继日志的文件名将以 "relay-log-bin" 开头。

relay-log-index=slave-relay-bin.index:

指定了中继日志索引文件的名称。中继日志索引文件用于记录中继日志文件的顺序和位置。

在这个例子中，索引文件名为 "slave-relay-bin.index"。

relay_log_recovery=1:

用于配置从服务器在启动时是否执行中继日志的恢复操作。设置为 1 表示启用中继日志的恢复，

通常在从服务器出现异常或崩溃后重启时使用。

这有助于确保从服务器能够从主服务器的二进制日志中正确地读取和应用中继日志，以保持数据一致性

重启MySQL

另一台从MySQL一样配置

show master status;

根据住的二进制文件名和位置点对两个从MySQL进行同步：

主：

两个从MySQL同步：

CHANGE master to master_host='20.0.0.31',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=599;

配置同步，注意 master_log_file 和 master_log_pos 的值要与Master查询的一致

start slave; #启动同步，如有报错执行 reset slave;

查看slave状态：

show slave status\G;

slave_IO_Running:Yes ：负责和主库的IO通信

Slave_SQL_Running:Yes ：负责自己的Slave MySQL进程

若slave_IO_Running:No怎么解决？

网络问题
vim /etc/my.cnf配置文件错了

3、

CHANGE master to master_host='192.168.233.21',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604;

要么文件名写错了，要么位置偏移量不对

4、防火墙和安全机制问题（面试问，不能说这个）

到此主从复制结束

打开navicat验证：

主MySQL新建查询：

创建一个新库、新表，看从有没有同步

二、读写分离：

要实现读写分离，第一步必须先实现读写分离

读写分离：所有的写入操作都在主库，从库只负责读（select）。如果有更新，是从主库复制到从库

1、概念：

1.1、为什么要有读写分离？

数据库在写入数据时，比较耗时（MySQL写1万条数据，3分钟）
数据库在读的时候速度很快（读1万条数据，5秒）

读写分离之后，数据库的写入和读取是分开的，哪怕写入的数据量比较大，但是不影响查询的效率。

1.2、什么场景下需要读写分离：

数据库不是一定需要读写分离的。只有在某些程序在使用数据库，更新少，但是查询较多，这种情况可以考虑读写分离

若读和差的需求差不多，也可以考虑读写分离

生产库一般都会做读写分离，测试库一般不管

在工作中，数据库的读写不可能在同一个库中完成。既不安全，也不能满足高可用，也不能实现高并发。工作中都会做读写分离

1.3、MySQL读写分离的原理：

根据脚本实现：在代码中实现路由分类。在select和insert中进行路由分类。这种方法是最多的。

特点：性能好，在代码中就可以实现，不需要额外的硬件设备

缺点：开发实现的，根我们无关。如果说大型的复杂的应用，设计改动的代码非常多

基于中间层代理实现：mysql-proxy，是MySQL自带的开源项目，基于自带的lua脚本。这些lua脚本不是现成的，要自己写，不熟悉他的内置变量写不出来的
atlas：360内部自己使用的代理工具，不对外公开。每天读写请求承载量可以到几十亿条。而且还支持事务和存储过程
Amoeba：是基于java开发的开源软件。不支持事务和存储过程。但是Amoeba还是用的最多的，功能比较强大的软件。基于jdk1.5开发的，官方推荐1.5-1.6（今天实现读写分离的方式）

2、实验：用Amoeba来实现读写分离：

3台MySQL：

MySQL31（主）、32（从）、33（从）

test1（读写分离客户服务器，部署Amoeba）、test2（客户端）

安装Amoeba软件和jdk环境

vim /etc/profile最后一行

export JAVA_HOME=/usr/local/jdk1.6

export CLASSPATH=.:$JAVA_HOME/lib:$JAVA_HOME/jre/lib

export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin

export AMOEBA HOME=/usr/local/amoeba

export PATH=$PATH:$AMOEBA_HOME/bin

安装Amoeba

先mkdir /usr/local/amoeba

三台MySQL设置权限，允许Amoeba可以访问

grant all on *.* to 'amoeba'@'20.0.0.%' identified by '123456';

flush privileges;

回到Amoeba客户端修改配置文件：

修改amoeba.xml 用户信息配置文件

先备份：

cp amoeba.xml amoeba.xml.bak

改配置文件：vim amoeba.xml

30行用户改成amoeba

31行修改密码123456

115行修改

修改完毕之后保存退出

修改dbservers.xml 数据库信息文件

先备份：

cp dbServers.xml dbServers.xml.bak

23行，更改注释范围

26行修改：

45行修改：

52行修改：

65行修改：

71行修改：

修改完成后

启动Amoeba+后台运行

查看java启没启动

netstat -antp | grep java

测试读写分离：

客户端安装MySQL测试：（epel源）

yum -y install epel-release

yum -y install mariadb-server mariadb

安装好了之后重启mariadb，查看端口是否启动

在所有三台主从MySQL上配置文件上加入：

开启日志：

vim /etc/my.cnf

general_log=ON

general_log_file=/usr/local/mysql/data/mysql_general.log

重启MySQL

打开三台MySQL的日志

从客户端连接Amoeba服务器进行测试：

往表中插入数据，看日志，主服务器上有写入日志记录，从服务器上有复制日志记录——写

查看表，从服务器日志有记录，主记录没有——读

3、实验：mycat实现读写分离

安装mycat：

（1）主机上安装java（mycat基于java）

#yum安装java

yum install java -y

#确认安装成功

java -version

（2）切换至opt目录，下载mycat安装包

cd /opt

wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz

（3）创建/apps文件夹，并解压mycat包至/apps下

mkdir /apps

tar zxvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz -C /apps/

（4）设置变量环境

echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh

source /etc/profile.d/mycat.sh

（5）启动mycat，查看日志文件，最后可以看到启动成功

mycat start

#注意内存小于2G 起不来

Starting Mycat-server...

tail -f /apps/mycat/logs/wrapper.log

#启动成功日志末尾会出现successfully

(6)客户端连接数据库

#这里密码初始为123456 需要加端口

mysql -uroot -p123456 -h 20.0.0.21 -P8066

修改Mycat配置：

修改 mycat 配置文件 /apps/mycat/conf/server.xml

vim /apps/mycat/conf/server.xml

#去掉44行行注释，对应的在51行行末注释，删除50行行末注释，5 * 60 * 1000L; //连接空> 闲检查

#修改45行端口号为3306

45 <property name="serverPort">3306</property>

#配置Mycat的连接信息(账号密码)，在110 和111行，可以修改，这边不修改了

修改 mycat 配置文件/apps/mycat/conf/schema.xml：

1000dd全部删除，添加如下：

schema.xml是最主要的配置项，此文件关联mysql读写分离策略，读写分离、分库分表策略、分片节点都是在此文件中配置的.MyCat作为中间件，它只是一个代理，本身并不进行数据存储，需要连接后端的MySQL物理服务器，此文件就是用来连接MySQL服务器的。

<?xml version="1.0"?>

<!DOCTYPE mycat:schema SYSTEM "schema.dtd">

<mycat:schema xmlns:mycat="http://io.mycat/">

<dataHost name="localhost1" maxCon="1000" minCon="10" balance="1"

writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">

<heartbeat>select user()</heartbeat>

</writeHost>

</dataHost>

</mycat:schema>

主服务器上授权：

mysql -uroot -p123123

#授权

GRANT ALL ON *.* TO 'root'@'192.168.59.%' IDENTIFIED BY '123456';

#查看创建成功

use mysql;

select user,host from user;

重启mycat服务，客户机连接mycat

mycat restart

tail -f /apps/mycat/logs/wrapper.log

查看3306端口，可以监听到主从服务器

ss -antp|grep 3306

重启mycat服务，客户机连接mycat

客户端测试读写分离

主从服务器打开log

tail -f /usr/local/mysql/data/localhost.log

客户端查看表：

日志：

主没有显示

三、完全同步实验：

如何实现完全同步（从库的更新也能同步到主）

主从完全同步，实际上就是主主同步

配置主数据库

修改mysql配置文件：

vim /etc/my.cnf

server-id = 1

#两个库的id不能重复

log-bin=mysql-bin

#存放日志文件的位置

auto_increment_increment=2

#设置主键自增长的步数，有几台主机就设置几台

auto_increment_offset=1

#设置自增起始值，这一台是1那么下一台就是2

replicate-do-db=demo_db

#选择需要同步的数据库，注意这个是MySQL中的数据库，可以不提前创建。

配置完成后重启服务

创建一个用户名字为master1密码是123456

在1上创建个master1

create user 'master1'@'%' identified with mysql_native_password by '123456';

给他所有权限

grant all replication slave on *.* to 'master1'@'%';

刷新

flush privileges;

查看权限：

show grants for 'master1'@'%';

看一下二进制文件和漂移位置

show master status;

配置从数据库：

vim /etc/my.cnf

创建用户master2 和上面一样

create user 'master2'@'%' identified with mysql_native_password by '123456';

给他所有权限

grant all replication slave on *.* to 'master2'@'%';

刷新

flush privileges;

查看权限：

show grants for 'master2'@'%';

看一下二进制文件和漂移位置

show master status;

CHANGE master to master_host='20.0.0.31',master_user='master1',master_password='123456',m123456',master_log_file='master-bin.000001',master_log_pos=768;

再去主1同步：

CHANGE master to master_host='20.0.0.32',master_user='master2',master_password='123456',m123456',master_log_file='master-bin.000001',master_log_pos=1179;