MySQL--主从复制与读写分离

现实背景

在实际生产环境中，如果对数据库的读写都在同一个数据库服务器中，无论是在安全性、高可用性，还是高并发等各个方面都是完全不能满足实际需求的，因此，一般来说都是用过主从复制（Master-slave）的方式来同步数据，在通过读写分离来提升数据库的高并发负载能力这样的方案进行部署与实施的。

主从复制

主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。

MySQL主从复制类型

MySQL的复制类型

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

主从复制的工作过程

  主从复制的过程大致为，MySQL主服务器开启二进制日志文件，MySQL的相关操作都记录再二进制文件中，由I/O线程完成从主服务器二进制文件（Binary）中读取相关操作再写入到中继日志（Relay log）中，从服务器SQL线程从中继日志中读取相关操作得以与主服务器同步。

  我们在完成主从复制的过程中，相当重要的部分为在从服务器上指定主服务器二进制文件和文件中的同步位置position；指明了这两个位置即可明确的知道从哪里开始同步了。

MySQL读写分离

  由于数据库在进行读写的时候会进行行的锁定或者表的锁定，这样如果读写操作均在一台服务器上的话，极大的限制了服务器的高并发的处理能力，所以进行数据库的读写分离。

读写分离方案

• 基于程序代码内部实现
• 基于中间代理层实现
• MySQL-Proxy
• Amoeba

MySQL读写分离原理

  由前端Amoeda代理服务器来进行读写的分离识别，将处理事务性的查询分配到主服务器上，将select查询分配到从服务器上，从服务器可从主服务器上同步的写相关操作后的数据。

  我们在配置读写分离的过程中，涉及到三次授权，一是主服务器授权给从服务器的可以复制的权限；二是MySQL服务器授权给amoeda的读或写的权限；三是Amoeba服务器允许用户访问授权；这三次授权均是通过创建相应用户给予权限。
  前方代理服务器在从服务器上进行轮询读操作，也就是轮流。

MySQL主从复制与读写分离配置示例

案例概述

• 在企业应用中，成熟的业务通常数据量都比较大
• 单台MySQL在安全性、高可用性和高并发方面都无法满足实际的需求
• 配置多台主从数据库服务器以实现读写分离

案例环境

• amoeba服务器maoeda: 20.0.0.100 安装amoeba-mysql-binary-2.2.0.tar，jdk-6u14-linux-x64
• MySQL主服务器master：20.0.0.31 编译安装mysql-5.7.17.tar，boost_1_59_0.tar
• MySQL从服务器slave01：20.0.0.61 编译安装mysql-5.7.17.tar，boost_1_59_0.tar
• MySQL从服务器slave02: 20.0.0.110 编译安装mysql-5.7.17.tar，boost_1_59_0.tar
• 客户端client：20.0.0.10 yum安装mysql

配置步骤

三台MySQL服务器编译安装mysql

1.安装MySQL环境依赖包
[root@localhost ~]#yum -y install ncurses ncurses-devel bison cmake gcc gcc-c++

2.创建程序用户，用于管理维护进程
[root@localhost ~]#useradd -s /sbin/nologin mysql

3.编译安装
把安装包放到服务器中
[root@localhost ~]#tar zxvf mysql-5.7.17.tar.gz -C /opt
[root@localhost ~]#tar zxvf boost_1_59_0.tar.gz -C /usr/local

[root@localhost local]# mv /usr/local/boost_1_59_0/ /usr/local/boost

[root@localhost local]# cd /opt/mysql-5.7.17/
[root@localhost mysql-5.7.17]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \
-DSYSCONFDIR=/etc \
-DSYSTEMD_PID_DIR=/usr/local/mysql \
-DDEFAULT_CHARSET=utf8 \
-DDEFAULT_COLLATION=utf8_general_ci \
-DWITH_INNOBASE_STORAGE_ENGINE=1 \
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=/usr/local/boost \
-DWITH_SYSTEMD=1
###boost的摆放位置和之前的有所不同
[root@localhost mysql-5.7.17]#make &&make install

4.数据库目录进行权限调整
[root@localhost ~]# chown -R mysql:mysql /usr/local/mysql/

5.调整配置文件
vi /etc/my.cnf

[client]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysql]
port = 3306
default-character-set=utf8
socket = /usr/local/mysql/mysql.sock

[mysqld]
user = mysql 
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysqld.pid
socket = /usr/local/mysql/mysql.sock
server-id = 1

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,PIPES_AS_CONCAT,ANSI_QUOTES

6.设置环境变量
echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
echo 'export PATH' >> /etc/profile
source /etc/profile

7.初始化数据库
[root@localhost mysql]# cd /usr/local/mysql/bin/
[root@localhost bin]# mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql/ \
--datadir=/usr/local/mysql/data
8.启动
[root@localhost bin]# cd ..
[root@localhost mysql]# cp usr/lib/systemd/system/mysqld.service /lib/systemd/system/mysqld.service
[root@localhost mysql]#systemctl daemon-reload
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# netstat -antp |grep mysql
tcp6       0      0 :::3306                 :::*                    LISTEN      36069/mysqld    

9.设置MySQL密码
[root@localhost mysql]# mysqladmin -u root -p password

10.登录数据库
[root@localhost mysql]# mysql -uroot -p

主MySQL服务器配置

[root@localhost ~]#iptables -F
[root@localhost ~]#setenforce 0
[root@localhost ~]# hostnamectl set-hostname master
[root@localhost ~]# su
[root@master ~]# vim /etc/my.cnf
...
## 主服务器的id号，不能与其他MySQL服务器重复
server-id=10
## 开启二进制日志文件，文件名开头为master-bin
log-bin=master-bin
## 允许从服务器复制
log-slave-updates=true
[root@master ~]# systemctl restart mysqld
[root@master ~]# mysql -uroot -p123456
## 授权，创建一个用户myslave，可以通过此用户在20.0.0.0/24主机上对主服务器进行复制，密码为123456
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by '123456';
mysql> flush privileges;
## 查看二进制日志文件，和在文件中复制的具体位置节点，在从服务器上配置是要用的
mysql>show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000002 |      154 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)
## 看完之后就不要对数据库做其他操作了，不然position会不对

两台从服务器配置

[root@localhost ~]#iptables -F
[root@localhost ~]#setenforce 0
[root@localhost ~]# hostnamectl set-hostname slave01
[root@localhost ~]# su
[root@slave01 ~]# vim /etc/my.cnf
## 服务器id
server-id = 20
## 中继日志
relay-log=relay-log-bin
## 中继日志索引
relay-log-index=slave-relay-bin.index
[root@slave01 ~]# systemctl restart mysqld
[root@slave01 ~]# mysql -uroot -proot
## 指定主服务器和相关授权用户，
mysql>change master to master_host='20.0.0.31',master_user='myslave',master_password='123456',master_log_file='master-bin.000002',master_log_pos=154;
mysql>start slave;
mysql>show slave status\G;
##要看到这两项为yes
               Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

验证：就是在主服务器上创建库或表就可以在从服务器上看到了

代理服务器amoeba配置

[root@localhost ~]#iptables -F
[root@localhost ~]#setenforce 0
[root@localhost ~]# hostnamectl set-hostname slave01
[root@localhost ~]# su
## 准备安装包
[root@amoeba ~]# cp jdk-6u14-linux-x64.bin /usr/local/
[root@amoeba ~]# cd /usr/local
[root@amoeba local]# chmod +x jdk-6u14-linux-x64.bin
[root@amoeba local]# ./jdk-6u14-linux-x64.bin
 一直回车，yes，再回车
[root@amoeba local]# mv jdk1.6.0_14/ /usr/local/jdk1.6
[root@amoeba local]# 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
[root@amoeba local]# source /etc/profile
[root@amoeba local]#mkdir /usr/local/amoeba
##到amoeba安装包的目录下
[root@amoeba ~]# tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/
[root@amoeba ~]# /usr/local/amoeba/bin/amoeba
amoeba start|stop
#### 这里需要去三台MySQL服务器上授权一个用户
在三台mysql服务器上添加权限给amoeba访问
mysql>grant all on *.* to 'test'@'20.0.0.%' identified by 'test123';
#######
[root@amoeba ~]# cd /usr/local/amoeba/
[root@amoeba amoeba]# vim conf/amoeba.xml 
30                                         <property name="user">amoeba</property>
 31 
 32                                         <property name="password">amoeba123</property>
 33 

115                 <property name="defaultPool">master</property>
116 
117                 
118                 <property name="writePool">master</property>
119                 <property name="readPool">slaves</property>
120                 
[root@amoeba amoeba]# vim conf/dbServers.xml 
如果以下配置有相应的行是在<!-- -->中就是被注释了蓝色的，要去掉注释再改
20                       <property name="schema">mysql</property>

                        <!-- mysql user -->
23                        <property name="user">test</property>

                        <!--  mysql password -->
29                       <property name="password">test123</property>

                </factoryConfig>

45 <dbServer name="master"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
48                        <property name="ipAddress">20.0.0.31</property>
                </factoryConfig>
        </dbServer>

52        <dbServer name="slave01"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
55                        <property name="ipAddress">20.0.0.61</property>
                </factoryConfig>
        </dbServer>
从上面复制下来，修改下即可
        <dbServer name="slave02"  parent="abstractServer">
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.110</property>
                </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">slave01,slave02</property>
                </poolConfig>
        </dbServer>
[root@amoeba amoeba]# /usr/local/amoeba/bin/amoeba start&
[1] 3278
[root@amoeba amoeba]# log4j:WARN log4j config load completed from file:/usr/local/amoeba/conf/log4j.xml
2020-08-27 09:43:53,879 INFO  context.MysqlRuntimeContext - Amoeba for Mysql current versoin=5.1.45-mysql-amoeba-proxy-2.2.0
log4j:WARN ip access config load completed from file:/usr/local/amoeba/conf/access_list.conf
2020-08-27 09:43:54,244 INFO  net.ServerableConnectionManager - Amoeba for Mysql listening on 0.0.0.0/0.0.0.0:8066.
2020-08-27 09:43:54,247 INFO  net.ServerableConnectionManager - Amoeba Monitor Server listening on /127.0.0.1:6800.

[root@amoeba ~]# netstat -antp | grep java
tcp6       0      0 :::8066                 :::*                    LISTEN      3278/java           
tcp6       0      0 127.0.0.1:6800          :::*                    LISTEN      3278/java           
tcp6       0      0 20.0.0.100:42394        20.0.0.110:3306         ESTABLISHED 3278/java           
tcp6       0      0 20.0.0.100:37476        20.0.0.31:3306          ESTABLISHED 3278/java           
tcp6       0      0 20.0.0.100:39476        20.0.0.61:3306          ESTABLISHED 3278/java  

客户端配置及验证

客户端验证
[root@localhost ~]# hostnamectl set-hostname client
[root@localhost ~]# su
[root@client ~]# yum -y install mysql
[root@client ~]# mysql -uamoeba -pamoeba123 -h 20.0.0.100 -P8066
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MySQL connection id is 1449171289
Server version: 5.1.45-mysql-amoeba-proxy-2.2.0 Source distribution

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MySQL [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mask               |
| mysql              |
| performance_schema |
| school             |
| sys                |
+--------------------+
6 rows in set (0.00 sec)
验证正常的主从同步
##在客户端上创建一个jialing的数据库可以在主从服务器上都看到
MySQL [(none)]> create database jialing;
Query OK, 1 row affected (0.01 sec)
验证读写分离
到两台从服务器上关掉slave，
mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)
之后到客户端上向数据表中插入几条数据，
MySQL [mask]> insert into mess values(1,'jialing'),(2,'fangcc');
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0
只能在主服务器上看到，验证了写只在主服务器上写

到slave01上插入几条数据
mysql> insert into mess values(3,'yandi');
Query OK, 1 row affected (0.00 sec)
到slave02上插入几条数据
mysql> insert into mess values(3,'zhousheng');
Query OK, 1 row affected (0.00 sec)
到客户端上查看，会以轮询的方式读两个从服务器的数据，而不是主服务器的，验证了读只在从服务器上
MySQL [mask]> select * from mess;
+------+----------+
| id   | name     |
+------+----------+
|    1 | zhangsan |
|    2 | lisi     |
|    3 | yandi    |
+------+----------+
3 rows in set (0.01 sec)

MySQL [mask]> select * from mess;
+------+-----------+
| id   | name      |
+------+-----------+
|    1 | zhangsan  |
|    2 | lisi      |
|    3 | zhousheng |
+------+-----------+
3 rows in set (0.00 sec)

相关软件包

自取