0
0

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

前言：

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

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

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

MySQL主从复制原理

MySQL支持的复制类型：

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

MySQL读写分离原理

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

  • 基于程序代码内部实现

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

  • 基于中间代理层实现

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

案例环境

    如图配置实验环境，关闭防火墙、selinux、配置IP地址。开始环境为MySQL已安装快照，MySQL搭建详情参考上边的配置。![](https://img-blog.csdnimg.cn/31acda3b5d7f41c2993a2b54af8c2c64.png)

搭建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
          <property name="user">amoeba</property>                   //修改><内用户为amoeba
          <property name="password">123456</property>               //修改><内密码为123456

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

                <property name="defaultPool">master</property>          //修改><内为master
​
                <!--                                                    //此为注释注意需要删除！！！
                <property name="writePool">master</property>            //修改><内为master
                <property name="readPool">slaves</property>             //修改><内为slaves
                -->                                                     //此为注释注意需要删除！！！
  • 编辑第二个配置文件
[root@localhost amoeba]# vim conf/dbServers.xml 
                         <property name="user">test</property>              //修改><内用户为test
​
                        <!--  mysql password                                //此为注释注意需要删除！！！
                        <property name="password">123.com</property>        //修改><内密码为123.com
                        -->                                                 //此为注释注意需要删除！！！

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

        <dbServer name="master"  parent="abstractServer">                                   //name修改为master
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.1.101</property>                 //IP地址修改为192.168.1.101
                </factoryConfig>
        </dbServer>
​
        <dbServer name="slave1"  parent="abstractServer">                                   //name修改为slave1（数字1）
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.1.102</property>                 //IP地址修改为192.168.1.102
                </factoryConfig>
        </dbServer>
        
        <dbServer name="slave2"  parent="abstractServer">                           //name修改为slave2（数字2），以下内容复制slave1的6行内容，修改name和IP地址即可
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">192.168.1.103</property>                 //IP地址修改为192.168.1.103
                </factoryConfig>
        </dbServer>
​
        <dbServer name="slaves" virtual="true">
                <poolConfig class="com.meidusa.amoeba.server.MultipleServerPool">
                        <!-- Load balancing strategy: 1=ROUNDROBIN , 2=WEIGHTBASED , 3=HA-->
                        <property name="loadbalance">1</property>
​
                        <!-- Separated by commas,such as: server1,server2,server1 -->
                        <property name="poolNames">slave1,slave2</property>                 //修改><内为slave1,slave2
                </poolConfig>
        </dbServer>
  • 启动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~
标签： mysql 数据库 服务器
本文转载自: https://blog.csdn.net/qq_61116007/article/details/126089039
版权归原作者 大虾好吃吗 所有， 如有侵权，请联系我们删除。
登录后发布评论

“案例：MySQL主从复制与读写分离”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

Python Pycharm的缩进规则 及快捷键_pycharm缩进

com.intellij.diagnostic.PluginException报错导致无法启动idea

Git超详细笔记包含IDEA整合操作

毕设分享 基于大数据的b站数据分析

高效异步任务处理：深入探讨Java中的消息队列 —— 使用RabbitMQ和Kafka的实践

前端列表数据太多导致页面卡顿就这么处理

智能网联技术总结

文章导航