如何实现MySQL主从同步和读写分离

it2025-12-28 3

前言一、案例概述1.1 原因1.2 解决方案1.3 更高级的解决方案二、MySQL主从复制2.1 MySQL主从复制的类型2.2 主从复制的工作原理及过程2.2.1 MySQL主从复制原理2.2.2 MySQL主从复制的工作过程三、MySQL读写分离3.1 案例概述3.2 MySQL读写分离原理四、主从复制实验4.1 实验准备4.2 实验拓扑图4.3 主从复制实验过程4.3.1 配置防火墙4.3.2 建立时间同步环境4.3.3 编译服务器安装MySQL4.3.4 配置mysql主服务器（192.168.100.42）4.3.5 配置mysql从服务器 4.4 实验验证五、读写分离实验5.1 实验准备5.2 实验拓扑图5.3 实验环境配置5.3.1 安装Java环境5.3.2安装amoeba5.3.3 制作amoeba管理脚本5.3.4 在三台mysql数据库中为amoeba授权 5.4 实验验证

前言

生产环境中，服务器不可能只有一台，而多台服务器的存在必然要用到主从复制同步

一、案例概述

1.1 原因

在企业网站中，后端MySQL数据库只有一台时，会有以下问题

单点故障，服务不可用无法处理大量的并发数据请求数据丢失—大灾难

1.2 解决方案

增加MySQL数据库服务器，对数据进行备份，形成主备确保主备MySQL数据库服务器数据是一样的主服务器宕机了，备份服务器继续工作，数据有保障

MySQL主从复制与读写分离是密切相关的

1.3 更高级的解决方案

通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力

二、MySQL主从复制

2.1 MySQL主从复制的类型

基于语句的复制（默认）在主服务器上执行的语句，从服务器执行同样的语句基于行的复制把改变的内容复制到从服务器混合类型的复制一旦发现基于语句无法精确复制时，就会采用基于行的复制

2.2 主从复制的工作原理及过程

2.2.1 MySQL主从复制原理

MySQL的复制类型基于语句的复制基于行的复制混合类型的复制

2.2.2 MySQL主从复制的工作过程

在每个事务更新数据完成之前，Master在二进制日志记录这些改变。写入二进制日志完成后，Master通知存储引擎提交事务Slave将Master的Binary log复制到中继日志，首先Slave开始一个工作线程–I/О线程，I/O线程在Master上开一个普通的连接，然后开始Binlog dump process ,Binlog dump process从Master的二进制日志中读取事件，如果已经跟上Master，它会睡眠并等待Master产生的新事件。1/O线程将这些日志写入中继日志SQL Slave thread(SQL从线程)处理该过程的最后一步，SQL线程从中继日志读取事件，并重放其的事件而更新Slave数据，使其与Master中的数据一致。只要该线程与I/O线程保持一致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小

三、MySQL读写分离

3.1 案例概述

在企业应用中，在大量的数据请求下，单台数据库将无法承担所有的读写操作

配置多台数据库服务器以实现读写分离本案例在主从复制的基础上实现读写分离

3.2 MySQL读写分离原理

读写分离就是只在主服务器上写，只在从服务器上读主数据库处理事务性查询，从数据库处理SELECT查询数据库复制用于将事务性查询的变更同步到集群中的从数据库读写分离方案基于程序代码内部实现基于中间代理层实现 MySQL-ProxyAmoeba Amoeba:是一个以MySQL为底层数据存储，并对应用提供MySQL协议接口的proxy，外号变形虫读取请求发送给从服务器时，采用轮询调度算法主服务器挂掉，我们会采用MHA解决（MHA实验会单独写一章，此次实验不会用到）此实验涉及到的账号权限主从同步账号节点服务器开放调度账号Amoeba代理账号

四、主从复制实验

4.1 实验准备

主机操作系统IP地址主要软件Mastercentos-7.6-X86_64192.168.100.42mysql-boost-5.7.20.tar.gzSlave1centos-7.6-X86_64192.168.100.43mysql-boost-5.7.20.tar.gzSlave2centos-7.6-X86_64192.168.100.44mysql-boost-5.7.20.tar.gz客户机（client）centos-7.6-X86_64192.168.100.45mysql-boost-5.7.20.tar.gz

4.2 实验拓扑图

4.3 主从复制实验过程

4.3.1 配置防火墙

所有服务器关闭 Firewall或者进行规则设置 systemctl stop firewalld.service setenforce 0

4.3.2 建立时间同步环境

在主服务器上安装配置NTP时间同步服务器使用yum安装NTP服务修改 ntp. conf，设置主服务器为时间同步源

在主服务器搭建时间同步服务器NTP（192.168.100.42）

[root@master ~]# yum install ntp -y [root@master ~]# vim /etc/ntp.conf ...省略内容... server 0.centos.pool.ntp.org iburst server 1.centos.pool.ntp.org iburst server 2.centos.pool.ntp.org iburst server 3.centos.pool.ntp.org iburst ###添加一下两段 server 127.127.195.0 ##本地是时钟源 fudge 127.127.195.0 stratum 8 ##设置时间层级为8 ...省略内容... [root@master ~]# service ntpd restart [root@master ~]# systemctl restart ntpd [root@master ~]# systemctl enable ntpd

在所有从服务器上进行时间同步使用yum安装 ntpdate并进行时间同步

登陆到192.168.100.43 [root@localhost ~]# yum -y install ntpdate [root@localhost ~]# ntpdate 192.168.100.42 ###设置周期性计划任务（crontab） [root@localhost ~]# crontab -e */2*** */usr/sbin/ntpdate 192.168.100.42 >>/var/log/ntpdate.log [root@localhost ~]# systemctl restart crond [root@localhost ~]# systemctl enable crond [root@localhost ~]# tail -f /var/log/ntpdate.log ###动态查看更新日志文件登陆到192.168.100.44 [root@localhost ~]# yum -y install ntpdate [root@localhost ~]# ntpdate 192.168.100.42 ###设置周期性计划任务（crontab） [root@localhost ~]# crontab -e */2*** */usr/sbin/ntpdate 192.168.100.42 >>/var/log/ntpdate.log [root@localhost ~]# systemctl restart crond [root@localhost ~]# systemctl enable crond [root@localhost ~]# tail -f /var/log/ntpdate.log ###动态查看更新日志文件

4.3.3 编译服务器安装MySQL

三台主从服务器都需要安装MySQL服务器 mysql安装看我的另外两篇博客LAMP架构和LNMP架构里面有写 LAMP架构 https://blog.csdn.net/qq_48191100/article/details/108867562. LNMP架构 https://blog.csdn.net/qq_48191100/article/details/108963246.

4.3.4 配置mysql主服务器（192.168.100.42）

修改/etc/my.cnf配置文件，增加服务器id，配置二进制日志选项 [root@master ~]# vim /etc/my.cnf ...省略内容... ###mysqld字段下面修改 server-id = 11 ###在原来server-id = 1的地方修改为server-id = 11，然后在下面新增以下两条命令 log-bin=master-bin ###设置二进制日志名 log-slave-updates=true ###从服务器更新二进制日志 [root@master ~]# systemctl restart mysqld.service ###重启mysql服务登陆mysql服务，授权所有从服务器复制二进制日志的权限 [root@master ~]# mysql -u root -p Enter password: ###192.168.100.0段的从服务器拥有复制权限，可以使用myslave身份123456密码复制所有的库和表 GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.100.%' IDENTIFIED BY 'abc123'; mysql> flush privileges; ###刷新 Query OK, 0 rows affected (0.00 sec) mysql> show master status; ###查看位置id +-------------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +-------------------+----------+--------------+------------------+-------------------+ | master-bin.000001 | 604 | | | | +-------------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec)

4.3.5 配置mysql从服务器

配置第一台从服务器（192.168.100.43）

[root@master ~]# vim /etc/my.cnf ...省略内容... ###修改mysqld字段下面修改 server-id = 22 ###在原来server-id = 1的地方修改为server-id = 22，然后在下面新增以下两条命令 realy-log=realy-log-bin log-log-index=slave-realy-bin.index [root@master ~]# systemctl restart mysqld.service ###重启mysql服务

登最Slave数据库配置同步注意:

这边的master_log_file=‘master-bin.00001’ ,mastr_log_pos=604;要和Master 数据库信息一致，不一致的话要更改先stop love 然后更改同步信息 [root@localhost mysql]# mysql -uroot -p mysql> change master to master_host=‘192.168.100.42 ’,master _user='myslave ,master_password=‘abc123’ ,master_log_file='master-bin.000001",master_log _pos=604; Query OK, 0 rows affected，2 warnings (0.01 sec) mysql> start slave; Query OK. 0 rows affected (0.00 sec) mysql> show slave status\G ...内容省略... Slave_IO_Running: Yes ###发现IO是正常的 Slave_SQL_Running: Yes ###SQL是正常的 ...内容省略...

同理配置第二台从服务器（192.168.100.44）

[root@master ~]# vim /etc/my.cnf ...省略内容... ###修改mysqld字段下面修改 server-id = 33 ###在原来server-id = 1的地方修改为server-id = 33，然后在下面新增以下两条命令 realy-log=realy-log-bin log-log-index=slave-realy-bin.index [root@master ~]# systemctl restart mysqld.service ###重启mysql服务 [root@localhost mysql]# mysql -uroot -p mysql> change master to master_host=‘192.168.100.42 ’,master _user='myslave ,master_password=‘abc123’ ,master_log_file='master-bin.000001",master_log _pos=604; Query OK, 0 rows affected，2 warnings (0.01 sec) mysql> start slave; Query OK. 0 rows affected (0.00 sec) mysql> show slave status\G ...内容省略... Slave_IO_Running: Yes ###发现IO是正常的 Slave_SQL_Running: Yes ###SQL是正常的 ...内容省略...

4.4 实验验证

登录192.168.100.42

登录192.168.100.43

登录192.168.100.44

上述结果表明实验成功

五、读写分离实验

5.1 实验准备

主机操作系统IP地址主要软件amoebacentos-7.6-X86_64192.168.100.41（1）amoeba-mysql-3.0.5-RC-distribution.zip （2） jdk-8u144-linux-x64.tar.gzMastercentos-7.6-X86_64192.168.100.42mysql-boost-5.7.20.tar.gzSlave1centos-7.6-X86_64192.168.100.43mysql-boost-5.7.20.tar.gzSlave2centos-7.6-X86_64192.168.100.44mysql-boost-5.7.20.tar.gz客户机centos-7.6-X86_64192.168.100.45mysql-boost-5.7.20.tar.gz

5.2 实验拓扑图

5.3 实验环境配置

在开始实验前，我们需要先配置三台MySQL服务器的主从复制

5.3.1 安装Java环境

如果是图形化界面，那就已经有了java环境，我们需要先卸载原有的java环境，否则实验无法完成

[root@localhost ~]# java -version ##确定java版本 openjdk version "1.8.0_181" OpenJDK Runtime Environment (build 1.8.0_181-b13) OpenJDK 64-Bit Server VM (build 25.181-b13, mixed mode) [root@localhost ~]# rpm -qa |grep java java-1.8.0-openjdk-headless-1.8.0.181-7.b13.el7.x86_64 ###卸载 tzdata-java-2018e-3.el7.noarch python-javapackages-3.4.1-11.el7.noarch java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64 ###卸载 javapackages-tools-3.4.1-11.el7.noarch java-1.7.0-openjdk-1.7.0.191-2.6.15.5.el7.x86_64 ###卸载 java-1.7.0-openjdk-headless-1.7.0.191-2.6.15.5.el7.x86_6 ###卸载 ###查出openjdk相关的文件并且删除它 [root@localhost ~]# rpm -e --nodeps java-1.8.0-openjdk-headless-1.8.0.181-7.b13.el7.x86_64 [root@localhost ~]# rpm -e --nodeps java-1.8.0-openjdk-1.8.0.181-7.b13.el7.x86_64 [root@localhost ~]# rpm -e --nodeps java-1.7.0-openjdk-1.7.0.191-2.6.15.5.el7.x86_64 [root@localhost ~]# rpm -e --nodeps java-1.7.0-openjdk-headless-1.7.0.191-2.6.15.5.el7.x86_64

上传Jdk文件包到/opt目录

[root@localhost ~]# cd /opt [root@localhost opt]# tar xzvf jdk-8u144-linux-x64.tar.gz ###解压 [root@localhost opt]# cp -rv jdk1.8.0_144/ /usr/local/java [root@localhost opt]# vi /etc/profile ###将如下配置文件插到最底下 export JAVA_HOME=/usr/local/java export JRE_HOME=/usr/local/java/jre ###java环境jre java的虚拟机简称JVM export PATH=$PATH:/usr/local/java/bin export CLASSPATH=./:/usr/local/java/lib:/usr/local/java/jre/lib ###类的位置 [root@localhost opt]# source /etc/profile ###使配置文件生效 [root@localhost opt]# java -version ###java环境变成'java version "1.8.0_144" Java(TM) SE Runtime Environment (build 1.8.0_144-b01) Java HotSpot(TM) 64-Bit Server VM (build 25.144-b01, mixed mode)

5.3.2安装amoeba

[root@localhost opt]# yum -y install unzip ###如果unzip命令没有，必须装下，如果装了，此步骤跳过 ##这里我已经把amoeba软件包放入 /opt 目录下了 [root@localhost opt]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local/ [root@localhost opt]# mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba [root@localhost opt]# chmod -R 755 /usr/local/amoeba/ [root@localhost opt]#vi /usr/local/amoeba/jvm.properties ## 进入修改配置 ## 在32行，修改如下代码 JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPermSize=96m" JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k" 修改java虚拟机内存大小 = >> wq 保存

5.3.3 制作amoeba管理脚本

[root@localhost opt]# vi /etc/init.d/amoeba #!/bin/bash #chkconfig: 35 62 62 # export JAVA_HOME=/usr/local/java export PATH=$JAVA_HOME/bin:$JAVA_HOME/jre/bin:$PATH NAME=Amoeba AMOEBA_BIN=/usr/local/amoeba/bin/launcher SHUTDOWN_BIN=/usr/local/amoeba/bin/shutdown PIDFILE=/usr/local/amoeba/Amoeba-MySQL.pid SCRIPTNAME=/etc/init.d/amoeba case "$1" in start) echo -n "Starting $NAME... " $AMOEBA_BIN echo " done" ;; stop) echo -n "Stoping $NAME... " $SHUTDOWN_BIN echo " done" ;; restart) $SHUTDOWN_BIN sleep 1 $AMOEBA_BIN ;; *) echo "Usage: $SCRIPTNAME {start|stop|restart}" exit 1 ;; esac = >> wq 保存 [root@localhost opt]# chmod +x /etc/init.d/amoeba ## 添加执行权限 [root@localhost opt]# chkconfig --add amoeba [root@localhost opt]# service amoeba start ## 通过上面写的脚本，才能使用这句代码，开启amoeba ctrl + c '//开启后 ctrl + c 停止就行了' [root@localhost opt]# yum install net-tools ## 没有netstat命令则进行安装 [root@localhost opt]# netstat -anpt | grep 8066 //默认监听在8066端口

5.3.4 在三台mysql数据库中为amoeba授权

##登录192.168.100.42主服务器 [root@localhost ~]# mysql -u root -p ###输入密码abc123 mysql> create database test; Query OK, 1 row affected (0.01 sec) mysql> GRANT ALL ON *.* TO test@'192.168.100.%' IDENTIFIED BY 'abc123'; mysql> FLUSH PRIVILEGES; mysql> quit ##登录192.168.100.43从服务器1 [root@localhost ~]# mysql -u root -p ####输入密码abc123 mysql> GRANT ALL ON *.* TO test@'192.168.100.%' IDENTIFIED BY 'abc123'; mysql> FLUSH PRIVILEGES; mysql> quit ##登录192.168.100.44从服务器2 [root@localhost ~]# mysql -u root -p ####输入密码abc123 mysql> GRANT ALL ON *.* TO test@'192.168.100.%' IDENTIFIED BY 'abc123'; mysql> FLUSH PRIVILEGES; mysql> quit

配置Amoeba读写分离，两个slave读负载均衡

[root@localhost opt]# cd /usr/local/amoeba [root@localhost amoeba]# vi conf/amoeba.xml ---28行-----设置客户端连接amoeba前端服务器时使用的用户名和密码---- <property name="user">amoeba</property> ----30行--------- <property name="password">abc123</property> ------------------以上配置用于客户端连接用户名密码------------- ---83-去掉注释-同时把默认的server1改成master,把默认的servers改成 slaves <property name="defaultPool">master</property> <property name="writePool">master</property> <property name="readPool">slaves</property> '//上面修改后如下'  这种注释一定要去掉 82 <property name="LRUMapSize">1500</property> 83 <property name="defaultPool">master</property> 84 <property name="writePool">master</property> 85 <property name="readPool">slaves</property> 86 <property name="needParse">true</property> ## 该另一个配置文件 [root@localhost amoeba]# vi conf/dbServers.xml --26-29--去掉注释-- <property name="user">test</property> ## 授权，连主数据库的。这是账号 <property name="password">abc123</property> ## 这是密码 -----------------以上是授权数据库账号和密码的--------------------- --------主服务器地址----------- ## 修改成下面的配置 43 <dbServer name="master" parent="abstractServer"> 46 <property name="ipAddress">192.168.100.42</property> --------从服务器地址----------- --50--从服务器主机名1-- <dbServer name="slave1" parent="abstractServer"> --53--从服务器IP地址1-- <property name="ipAddress">192.168.100.43</property> ## 在配置文件中，slave2是没有的，在下面添加从服务器主机2 <dbServer name="slave2" parent="abstractServer"> <factoryConfig>  <property name="ipAddress">192.168.100.44</property> </factoryConfig> </dbServer> ----------改另一个配置------------ <dbServer name="slaves" virtual="true"> #####name后面改成 slaves <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">  <property name="loadbalance">1</property>  <property name="poolNames">slave1,slave2</property> #####poolNames后面改成 slave1,slave2 </poolConfig> </dbServer>

修改完配置之后，重启一下amoeba

[root@localhost amoeba]# service amoeba restart [root@localhost amoeba]# netstat -anpt | grep java

5.4 实验验证

此次实验是紧接着主从同步复制实验，mysql数据库已安装，所以直接敲命令

mysql -u amoeba -p -h 192.168.100.42 -P8066 MySQL [(none)]> create database sanku; ##创建一个库进入主从服务器查看，发现mysql主从服务器都已经自动同步

测试读写分离，关闭主从复制功能

两台从服务器关闭slave功能 mysql> stop slave; Query OK, 0 rows affected (0.00 sec) mysql> show slave status\G; ...省略内容 Slave_IO_Running: No '//发现功能已被关闭' Slave_SQL_Running: No ...省略内容

从服务器配置数据，验证读写分离

MySQL [(none)]> create database siku; ###客户端创建库 ###会发现主服务器有，但是从服务器没有，说明主从复制功能已经关闭 ###slave1从服务器设置：都有erku的库，所以各自在erku创建yibiao并插入不同数据 mysql> use erku; Database changed mysql> create table yibiao (id int not null,name char(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into yibiao values(1,'zhangsan'); '//添加zhangsan记录' Query OK, 1 row affected (0.02 sec) mysql> select * from yibiao; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | +----+----------+ 1 row in set (0.00 sec) '//slave1从服务器设置：' mysql> use erku; Database changed mysql> create table yibiao(id int not null,name char(10)); Query OK, 0 rows affected (0.01 sec) mysql> insert into yibiao values(2,'lisi'); '//添加lisi记录' Query OK, 1 row affected (0.02 sec) mysql> select * from yibiao; +----+------+ | id | name | +----+------+ | 2 | lisi | +----+------+ 1 row in set (0.00 sec)

验证

###进入客户端测试 MySQL [(none)]> use erku; ###进入库 Database changed MySQL [erku]> select * from yibiao; +----+------+ | id | name | +----+------+ | 2 | lisi | +----+------+ 1 row in set (0.01 sec) MySQL [erku]> select * from yibiao; +----+----------+ | id | name | +----+----------+ | 1 | zhangsan | +----+----------+ 1 row in set (0.01 sec) ###发现每一次查询都会轮询在slave1和slave2上查询数据，如果开启主从复制，则数据都会相同 ###读写分离试验成功

实验成功

最新回复(0)