案例：MySQL主从复制与读写分离

前言：

在实际的生产环境中，对数据库的读和写都在同一个数据库服务器中，是不能满足实际需求的解决方案通过主从复制的方式来同步数据，再通过读写分离来提升数据库的并发负载能力。

目录

🍤MySQL主从复制原理

🍤MySQL读写分离原理

🍤案例环境

🍤搭建MySQL主从复制

🍣配置时间同步

🍣配置MySQL Master

🍣配置从服务器

🍣验证主从复制

🍤搭建MySQL读写分离

🍣安装Java环境

🍣安装并配置Amoeba

🍣配置Amoeba读写分离

🍣验证读写分离

🍥测试机——读操作

🍥测试机——写操作

既然如此，那就让我们开始享受今日这份晚宴吧🥘

下图中，一台MySQL主服务器带两台MySQL从服务器做了数据复制，前端应用在进行数据库写操作时，对主服务器进行操作，在进行数据库读操作做时，对两台从服务器进行操作，这样大量减轻了主服务器的压力。

MySQL主从复制原理

MySQL支持的复制类型：

基于语句的复制。在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL默认采用基于语句的复制，效率比较高。
基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。
混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的复制。

MySQL读写分离原理

🍖MySQL常见的读写分离分为两种：

基于程序代码内部实现

在代码中根据select、insert进行路由分类，这类方法也是目前生产环境应用最广泛的。

基于中间代理层实现

代理一般位于客户端和服务器之间，代理服务器连接到客户端的请求后判断后转发到后端数据库，有两个代表性程序，MySQL-Proxy、Amoeba。

案例环境

如图配置实验环境，关闭防火墙、selinux、配置IP地址。开始环境为MySQL已安装快照，MySQL搭建详情参考上边的配置。

搭建MySQL主从复制

配置时间同步

主节点服务器建立时间同步环境。


[root@mysql ~]# yum -y install ntp              //安装ntp
[root@mysql ~]# vim /etc/ntp.conf               //配置ntp，加入下面两行配置
server 127.127.1.0
fudge 127.127.1.0 stratum 8
[root@mysql ~]# systemctl restart ntpd          //启动
[root@mysql ~]# systemctl enable ntpd

两台从服务器进行同步


[root@1 ~]# yum -y install ntpdate
[root@1 ~]# ntpdate 192.168.1.101
28 Jul 08:56:50 ntpdate[4665]: no server suitable for synchronization found

配置MySQL Master

三台服务器启动MySQL服务，设置MySQL服务器的root密码。


[root@mysql ~]# systemctl start mysqld
[root@mysql ~]# mysqladmin -u root password 'pwd123'

注意：以下配置在主服务器下做。

在/etc/my.cnf中修改或增加下面内容。


[root@mysql ~]# vim /etc/my.cnf
[mysqld]                                        //在mysqld配置项下添加三行配置
server-id = 11
log_bin = master-bin
log-slave-updates = true
[root@mysql ~]# systemctl restart mysqld        //配置后重启服务

登录MySQL程序，给授权给从服务器。


[root@mysql ~]# mysql -uroot -p
mysql>  GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.1.%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      410 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从服务器

在/etc/my.cnf中添加三行内容，开启中继日志功能。

🍝注意：两台从服务器都需要配置，并且三台的server_id不能相同。


[root@1 ~]# vim /etc/my.cnf 
[mysqld]                                    //在mysqld配置项下添加三行配置 
server_id = 22
relay-log=relay-log-bin
relay-log-index=slave-relay-bin.index
[root@1 ~]# systemctl restart mysqld

登录MySQL配置时间同步

master_log_file为主服务器的File参数。

master_log_pos为主服务器的Position参数。


[root@1 ~]# mysql -uroot -p
......//省略部分内容
mysql> change master to master_host='192.168.1.101',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=410;
Query OK, 0 rows affected, 2 warnings (0.03 sec)

启动时间同步并查看slave状态（确认两个yes）。停止服务使用"stop slave;"命令。


mysql> start slave;                         //启动服务
Query OK, 0 rows affected (0.00 sec)
mysql> show slave status\G                  //查看Slave状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.1.101
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: master-bin.000001
          Read_Master_Log_Pos: 410
               Relay_Log_File: relay-log-bin.000002
                Relay_Log_Pos: 284
        Relay_Master_Log_File: master-bin.000001
             Slave_IO_Running: Yes            //此项应为yes
            Slave_SQL_Running: Yes            //此项应为yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB:

🍤配置从服务器1.102后记得配置从服务器1.103哦

验证主从复制

在主、从服务器上登录MySQL，都查看一下数据库。


[root@mysql ~]# mysql -u root -p
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.01 sec)

在主服务器创建库。


mysql> create database test_db;
Query OK, 1 row affected (0.00 sec)

两台从服务器查看数据库，显示数据库相同，则主从复制成功。


mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
| test_db            |
+--------------------+
5 rows in set (0.00 sec)

搭建MySQL读写分离

Amoeba项目开源框架于2008年发布一款Amoeba for MySQL软件。这个软件致力于MySQL的分布式数据库前端代理层，它主要为应用层访问MySQL的时候充当SQL路由功能，并具有负载均衡、高可用性、SQL过滤、读写分离、可路由相关的到目标服务器、可并发请求多台数据库。

安装Java环境

安装Java环境


[root@localhost ~]# mount /dev/cdrom /media
mount: /dev/sr0 写保护，将以只读方式挂载
[root@localhost ~]# cp /media/jdk-6u14-linux-x64.bin /root
[root@localhost ~]# chmod +x jdk-6u14-linux-x64.bin 
[root@localhost ~]# ./jdk-6u14-linux-x64.bin                    //根据提示按回车键即可
......//省略部分内容
Do you agree to the above license terms? [yes or no]            //输入yes
yes
......//省略部分内容
Press Enter to continue.....                                    //输入回车键即可
Done.
[root@localhost ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6

配置文件后面加入以下内容


[root@localhost ~]# vim /etc/profile
export  JAVA_HOME=/usr/local/jdk1.6
export  CLASSPATH=$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

执行并查看版本;java环境已经配置成功。


[root@localhost ~]# . /etc/profile
[root@localhost ~]# java -version
java version "1.6.0_14"
Java(TM) SE Runtime Environment (build 1.6.0_14-b08)
Java HotSpot(TM) 64-Bit Server VM (build 14.0-b16, mixed mode)

安装并配置Amoeba


[root@localhost ~]# mkdir /usr/local/amoeba
[root@localhost ~]# tar zxf /media/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@localhost ~]# chmod -R 755 /usr/local/amoeba/
[root@localhost ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop                               //显示此内容说明Amoeba安装成功

配置Amoeba读写分离

Master、Slave1、Slave2中开放权限给Amoeba访问。注意：三台mysql服务器都要开放权限。


mysql> grant all on *.* to test@'192.168.1.%' identified by '123.com';
Query OK, 0 rows affected (0.00 sec)

在Amoeba主机上编辑配置文件。


[root@localhost ~]# cd /usr/local/amoeba/
[root@localhost amoeba]# vim conf/amoeba.xml
          "user">amoeba                   //修改><内用户为amoeba
          "password">123456               //修改><内密码为123456

🍒同一文件内，以下配置文件中，🍒注意删除注释🍒。


                <property name="defaultPool">masterproperty>          //修改><内为master
                                                                     //此为注释注意需要删除！！！

编辑第二个配置文件


[root@localhost amoeba]# vim conf/dbServers.xml 
                         "user">test              //修改><内用户为test
                                                                         //此为注释注意需要删除！！！

🍳注意：同一文件内，修改master、name、和主机即可。


        "master"  parent="abstractServer">                                   //name修改为master
                
                        
                        "ipAddress">192.168.1.101                 //IP地址修改为192.168.1.101
                
        
        "slave1"  parent="abstractServer">                                   //name修改为slave1（数字1）
                
                        
                        "ipAddress">192.168.1.102                 //IP地址修改为192.168.1.102
                
        
        
        "slave2"  parent="abstractServer">                           //name修改为slave2（数字2），以下内容复制slave1的6行内容，修改name和IP地址即可
                
                        
                        "ipAddress">192.168.1.103                 //IP地址修改为192.168.1.103
                
        
        "slaves" virtual="true">
                class="com.meidusa.amoeba.server.MultipleServerPool">
                        
                        "loadbalance">1
                        
                        "poolNames">slave1,slave2                 //修改><内为slave1,slave2

启动Amoeba软件

🍲修改配置无误后，启动Amoeba，默认端口为tcp8066。


[root@localhost amoeba]# bin/amoeba start&                                          //关闭可以使用stop&命令
[root@localhost amoeba]# netstat -antpt | grep java
tcp6       0      0 127.0.0.1:18003         :::*                    LISTEN      5406/java           
tcp6       0      0 :::8066                 :::*                    LISTEN      5406/java                  
tcp6       0      0 192.168.1.110:35868     192.168.1.103:3306      ESTABLISHED 5314/java           
tcp6       0      0 192.168.1.110:47544     192.168.1.101:3306      ESTABLISHED 5314/java           
tcp6       0      0 192.168.1.110:58650     192.168.1.102:3306      ESTABLISHED 5314/java

验证读写分离

测试主机上安装mysql


[root@localhost ~]# yum -y install mysql
......//省略部分内容
[root@localhost ~]# mysql -u amoeba -p123456 -h 192.168.1.110 -P8066        //通过代理访问MySQL
......//省略部分内容
MySQL [(none)]>

在Master（1.101）上创建一个表，同步到各个服务器上，然后关掉两台从服务器（1.102/1.103）的Slave功能，再插入区别语句测试。


[root@mysql ~]# mysql -uroot -ppwd123
......//省略部分内容
mysql> use test_db
Database changed
mysql> create table AAA (id int(10),name varchar(10));
Query OK, 0 rows affected (0.02 sec)

（1）此刻两台从服务器的test_db库中应同步出一个名为AAA的表


mysql> show tables;
+-------------------+
| Tables_in_test_db |
+-------------------+
| AAA               |
+-------------------+
1 row in set (0.00 sec)

（2）两台从服务器（1.102、1.103）关闭slave功能。注意：生产环境中一般不允许关闭！


mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

（3）主服务器（1.101）上插入一条数据。


mysql> insert into AAA values('101','zhang3');
Query OK, 1 row affected (0.00 sec)

（4）两台从服务器上分别插入一条数据。

🍭注意：在1.102主机的配置如下：


mysql> use test_db;
Database changed
mysql> insert into AAA values('102','li4');
Query OK, 1 row affected (0.00 sec)

🍭注意：在1.103主机的配置如下：


mysql> use test_db;
Database changed
mysql> insert into AAA values('103','wang5');
Query OK, 1 row affected (0.00 sec)

测试机——读操作

（1）在client主机上第一次的查询结果如下。


MySQL [(none)]> use test_db
Database changed
MySQL [test_db]> select * from AAA;
+------+-------+
| id   | name  |
+------+-------+
|  103 | wang5 |
+------+-------+
1 row in set (0.01 sec)

(2）第二次查询结果如下。


MySQL [test_db]> select * from AAA;
+------+------+
| id   | name |
+------+------+
|  102 | li4  |
+------+------+
1 row in set (0.00 sec)

（3）第三次查询结果如下。


MySQL [test_db]> select * from AAA;
+------+-------+
| id   | name  |
+------+-------+
|  103 | wang5 |
+------+-------+
1 row in set (0.00 sec)

🥞经过三次操作只能查询到从服务器中的内容，由此看来读操作成功。

测试机——写操作

在Client主机（1.111）上插入一条语句。


MySQL [test_db]> insert into AAA values('111','zhao6');
Query OK, 1 row affected (0.01 sec)

但是在Client主机上查不到，最终只能在master（1.101）上才能查到这条语句内容，说明写操作在Master服务器上。

在Master（1.101）主机上查看内容。


mysql> select * from AAA;
+------+--------+
| id   | name   |
+------+--------+
|  101 | zhang3 |
|  111 | zhao6  |
+------+--------+
2 rows in set (0.00 sec)

由此证明，已经实现了MySQL的读写分离，目前所有的写操作都全部在Master（1.101）上，用来避免数据的不同步；所有的读操作都分摊给Slave（从服务器），用来分担数据库压力。

如果两台从服务器开启slave功能数据将被同步过去，测试机中操作能分别查到以下内容。


MySQL [test_db]> select * from AAA;
+------+--------+
| id   | name   |
+------+--------+
|  102 | li4    |
|  101 | zhang3 |
|  111 | zhao6  |
+------+--------+
3 rows in set (0.00 sec)
MySQL [test_db]> select * from AAA;
+------+--------+
| id   | name   |
+------+--------+
|  103 | wang5  |
|  101 | zhang3 |
|  111 | zhao6  |
+------+--------+
3 rows in set (0.00 sec)

🍡首先感谢各位大佬的关注，谢谢各位的指点。各位没点关注的大佬，点点关注，我们相互学习，闭门造车是行不通滴。学海无涯苦作舟，那就这样了，晚宴结束时间已到，各位我们八月见啦。ヾ(￣▽￣)Bye~Bye~

相关阅读:
华为新设备升级示例
 Python基础入门篇【41】--python中的时间包
 复习计算机网络——第二章记录（1）
JDSU故障测试仪维修OTDR光时域反射仪维修MTS2000
钢铁企业智慧制造解决方案及典型应用----工业软件讲坛第六次讲座
 软件工程中如何设计测试用例
 abap代码优化和性能调优工具
 Android Kotlin（五）数据流StateFlow和LiveData
Anaconda（Jupyter）里发现不能识别自己的GPU该怎么办?
【玩转STL】STL的简介和string类用法和接口讲解（源码解析）
原文地址：https://blog.csdn.net/qq_61116007/article/details/126089039