MySQL主从复制与读写分离

一，项目前置知识点

1.MySQL主从复制原理

MySQL的复制类型
1.基于语句的复制
2.基于行的复制
3.混合类型的复制

2.MySQL读写分离原理

1.只在主服务器上写，只在从服务器上读
2.主数据库处理事务性查询，从服务器处理SELECT查询
3.数据库复制用于将事务性查询的变更同步到集群中的从数据库
4.读写分离方案
基于程序代码内部实现
基于中间代理层实现
MySQL-Proxy
Amoeba

3.复制的基本过程如下：

1.Master将用户对数据库更新的操作以二进制格式保存到 Binary Log日志文件中；
2.Slave上面的IO进程连接上Master，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；
3.Master接收到来自Slave的IO进程的请求后，通过负责复制的IO进程根据请求信息读取制定日志指定位置之后的日志信息，返回给Slave的IO进程。返回信息中除了日志所包含的信息之外，还包括本次返回的信息已经到Master端的bin-log文件的名称以及bin-log的位置；
4.Slave的IO进程接收到信息后，将接收到的日志内容依次添加到Slave端的relay-log文件的最末端，并将读取到的Master端的bin-log的文件名和位置记录到master-info文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log的哪个位置开始往后的日志内容，请发给我”；
5.Slave的Sql进程检测到relay-log中新增加了内容后，会马上解析relay-log的内容成为在Master端真实执行时候的那些可执行的内容，并在自身执行；

二、案例部署主从复制

1.项目拓扑图

2.案例环境

client：20.0.0.21
amoeba服务器：20.0.0.22
三台MySQL服务器，一主两从
master：20.0.0.23
slave1：20.0.0.24
slave2：20.0.0.25

3.案例部署

1.配置master服务器：20.0.0.23

配置NTP服务器

[root@localhost ~]# yum -y install ntp ntpdate
[root@localhost ~]# ntpdate ntp.aliyun.com
[root@localhost ~]# date
[root@localhost ~]# which ntpdate
/usr/sbin/ntpdate
[root@localhost ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate ntp.aliyun.com
[root@localhost ~]# vi /etc/ntp.conf
restrict 20.0.0.0 mask 255.255.255.0 nomodify notrap	#记得去掉注释
##21-24行删除
server 127.127.1.0			#在删除的位置添加
fudge 127.127.1.0 stratum 8

[root@localhost ~]# systemctl start ntpd
[root@localhost ~]# systemctl enable ntpd
[root@localhost ~]# systemctl start crond
[root@localhost ~]# systemctl enable crond
crontab -l

编译安装MySQL数据库：（三台服务器都需要安装）

1.准备安装环境
[root@server ~]# yum -y install \
> ncurses \
> ncurses-devel \		##字符终端下屏幕控制的基本库
> bison \
> cmake					##跨平台编译安装工具
[root@server opt]# tar zxf mysql-boost-5.7.20.tar.gz

2.cmake配置
[root@server opt]# cd mysql-5.7.20
[root@server mysql-5.7.20]# cmake \
-DCMAKE_INSTALL_PREFIX=/usr/local/mysql \		#指定将mysql数据库安装到某目录下
-DMYSQL_UNIX_ADDR=/usr/local/mysql/mysql.sock \	#指定套接字文件的存储路径，数据库连接的文件
-DSYSCONFDIR=/etc \			#指定初始化参数文件目录
-DSYSTEMD_PID_DIR=/usr/local/mysql \	#数据安装路径 
-DDEFAULT_CHARSET=utf8  \		#指定默认使用的字符集编码，如 utf8。
-DDEFAULT_COLLATION=utf8_general_ci \	#指定默认使用的字符集校对规则，utf8_general_ci 是适用于 UTF-8 字符集的通用规则。
-DWITH_INNOBASE_STORAGE_ENGINE=1 \		#安装INNOBASE存储引擎
-DWITH_ARCHIVE_STORAGE_ENGINE=1 \		#安装ARCHIVE存储引擎
-DWITH_BLACKHOLE_STORAGE_ENGINE=1 \		#安装ARCHIVE存储引擎 
-DWITH_PERFSCHEMA_STORAGE_ENGINE=1 \	#安装FEDERATED存储引擎
-DMYSQL_DATADIR=/usr/local/mysql/data \
-DWITH_BOOST=boost \
-DWITH_SYSTEMD=1

3.[root@server mysql-5.7.20]# make && make install	编译并且安装

4.建立调整配置文件
[root@server ~]# 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

5.设置环境变量
[root@server ~]# echo 'PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH' >> /etc/profile
[root@server ~]# echo 'export PATH' >> /etc/profile
[root@server ~]# source /etc/profile

6.添加用户mysql，专门用来管理进程（程序型用户，禁止登录shell）
[root@server ~]# useradd -s /sbin/nologin mysql	  ##添加用户mysql，禁止用户登录系统
[root@server ~]# id mysql
uid=1001(mysql) gid=1001(mysql) groups=1001(mysql)
[root@server ~]# chown -R mysql.mysql /usr/local/mysql	##设置属主和数组

7.初始化数据库
[root@server ~]# cd /usr/local/mysql/

[root@server mysql]# bin/mysqld \
--initialize-insecure \
--user=mysql \
--basedir=/usr/local/mysql \
--datadir=/usr/local/mysql/data

8.启动，设置密码
[root@server ~]# cp usr/lib/systemd/system/mysqld.service /usr/lib/systemd/system/
[root@server ~]# systemctl enable mysqld
[root@server ~]# systemctl start mysqld
[root@server ~]# systemctl status mysqld
[root@server ~]# netstat -anpt | grep 3306

[root@server ~]# mysqladmin -u root -p password "abc123" //刚开始没密码是空的直接回车，然后输入密码abc123,在此确认abc123,这是在root账户下运行的

[root@server ~]# mysql -u root -p     ##这个命令敲下，提示要输入密码,这个就是刚才设置的密码abc123

配置master主服务器的MySQL
1.修改主配置文件/etc/my.cnf，在mysqld下发添加

[root@server ~]# vi /etc/my.cnf
server-id = 1
log_bin=master_bin            #在server-id下面添加两行
log_slave_updates=true
systemctl restart mysqld.service

2.为slave授权

[root@server ~]# mysql -u root -pabc123
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by 'abc123';
mysql> show grants for 'myslave'@'20.0.0.%';
mysql> flush privileges;
mysql> show master status;         #查完要记住position列的值

2.配置slave1\2

配置时间同步

[root@localhost ~]# yum -y install ntp ntpdate
[root@localhost ~]# ntpdate 20.0.0.23
[root@localhost ~]# date
[root@localhost ~]# which ntpdate
/usr/sbin/ntpdate
[root@localhost ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 20.0.0.23
[root@localhost ~]# systemctl start ntpd
[root@localhost ~]# systemctl enable ntpd
[root@localhost ~]# systemctl start crond
[root@localhost ~]# systemctl enable crond
[root@localhost ~]# crontab -l

修改主配置文件/etc/my.cnf，在mysqld下发添加

[root@localhost ~]# vi /etc/my.cnf
server-id = 2     ##slave1的server-id为2，slave2的server-id修改为3
relay_log=relay-log-bin               ##添加日志
relay_log_index=slave-relay-bin.index       ##定义中继日志的索引

[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -uroot -pabc123
mysql> change master to
master_host='20.0.0.23',master_user='myslave',master_password='abc123',master_log_file='master_bin.000002',master_log_pos=599;         ##与主库保持同步，添加主库的账号，二进制日志文件，开始同步的位置
mysql> start slave;   ## 启动从服务器
mysql> show slave status \G;           ## IO线程和SQL线程需要都处于yes状态
10              ......
11              Slave_IO_Running: Yes
12              Slave_SQL_Running: Yes
13 ##如果显示NO，①主服务器数据库更新重启一下，再进入数据库，打show master status;记录日志和Position值②再在从服务器上打mysql> change master to master_host='20.0.0.23',master_user='myslave',master_password='abc123',master_log_file='master_bin.000001',master_log_pos=599;

三、案例部署读写分离

1 amoeba服务端安装jdk

[root@localhost ~]# tar zxvf jdk-8u91-linux-x64.tar.gz
[root@localhost ~]# mv jdk1.8.0_91/ /usr/local/java
[root@localhost ~]# cd /etc/profile.d/
[root@localhost profile.d]# vim java.sh   ## 指定java的环境变量
export JAVA_HOME=/usr/local/java 
export PATH=$PATH:$JAVA_HOME/bin 
[root@localhost profile.d]# source java.sh   ##将脚本导入到环境变量中
[root@localhost profile.d]# java -version   ## 查看java的版本信息
openjdk version "1.8.0_131"
OpenJDK Runtime Environment (build 1.8.0_131-b12)
OpenJDK 64-Bit Server VM (build 25.131-b12, mixed mode)

2.在amoeba服务器上安装amoeba

[root@localhost ~]# unzip amoeba-mysql-3.0.5-RC-distribution.zip 
[root@localhost ~]# mv amoeba-mysql-3.0.5-RC /usr/local/amoeba
[root@localhost ~]# chmod -R 755 /usr/local/amoeba/
[root@localhost ~]# vi /usr/local/amoeba/jvm.properties 
32 #JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPerSize=96m"          ##32行注释掉
33 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"    ##在33行添加
[root@localhost ~]# cd /usr/local/amoeba/bin/
[root@localhost ~]# launcher
[root@localhost ~]# netstat -anpt | grep 8066

3.编写amoeba管理脚本

[root@localhost ~]# vi /etc/init.d/amoeba
#!/bin/bash
#chkconfig: 35 62 62

export JAVA_HOME=/usr/local/java
export PATH=$JAVA_HOME/bin:$JAVE_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..."
$SHUTDOWM_BIN
echo " done"
;;
restart)
$SHUTDOWN_BIN
sleep 1
$AMOEBA_BIN
;;
*)
echo "Usage: $SCRIPTNAME {start|stop|restart}"
exit 1
;;
esac

[root@localhost ~]# chmod +x /etc/init.d/amoeba 
[root@localhost ~]# chkconfig --add amoeba
[root@localhost ~]# systemctl start amoeba

4.在三台MySQL数据库中为amoeba授权

[root@localhost ~]# mysql -uroot -pabc123
mysql> grant all privileges on *.* to 'test'@'20.0.0.%' identified by 'abc123';
mysql> show grants for test@'20.0.0.%';
mysql> flush privileges;

5.修改amoeba配置文件

[root@localhost ~]# vi /usr/local/amoeba/conf/amoeba.xml
 <property name="schema">mysql</property>    ##22行

		<!-- mysql user -->
 <property name="user">test</property>   #28行,5.7版本的没有test，可以换mysql
 <property name="password">123456</property>          #30行
 ......
 </property>
 <property name="sqlFunctionFile">${amoeba.home}/conf/functionMap.xml</property>
 <property name="LRUMapSize">1500</property>
 <property name="defaultPool">master</property>     #83行，去掉注释
 <property name="writePool">master</property>       #修改
<property name="readPool">slaves</property>          #修改
<property name="needParse">true</property>
</queryRouter>

## 配置三台MySQL服务器
[root@localhost ~]# vi /usr/local/amoeba/conf/dbServers.xml 
......
<property name="user">test</property>     #26行,5.7版本的没有test，可以换mysql
<property name="password">abc123</property>     #28行修改
......
         <dbServer name="master"  parent="abstractServer">    #修改部分参数
                <factoryConfig>
                        <!-- mysql ip -->
                        <property name="ipAddress">20.0.0.10</property>
                 </factoryConfig>
         </dbServer>
 
         <dbServer name="slave1"  parent="abstractServer">
                 <factoryConfig>
               <!-- mysql ip -->
                          <property name="ipAddress">20.0.0.20</property>
                  </factoryConfig>
         </dbServer>
  
          <dbServer name="slave2"  parent="abstractServer">
                  <factoryConfig>
                          <!-- mysql ip -->
                          <property name="ipAddress">20.0.0.30</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">slave1,slave2</property>
                </poolConfig>
         </dbServer>
[root@localhost ~]# cd /usr/local/amoeba/bin/
[root@localhost bin]# launcher

6.启动amoeba服务

两种启动方式，可以使用我们编写的管理脚本启动，也可以通过服务自带的launcher脚本启动服务

[root@localhost ~]# systemctl start amoeba

[root@amoeba ~]# /usr/local/amoeba/bin/launcher

注意在启动的时候，如出现

2020-12-29 21:18:48,135 ERROR net.ServerableConnectionManager - Failure listening to socket on port '8066'

表示8066端口已经被占用，通过netstat我们可以查到占用端口的进程号，再通过kill命令杀死进程即可正常启动服务

7.在客户机上测试

[root@localhost ~]# yum -y install mariadb*
[root@localhost ~]# systemctl start mariadb
[root@localhost ~]# netstat -anpt | grep 3306    
[root@localhost ~]# mysql -utest -p123456 -h 20.0.0.40 -P 8066

测试1：验证是否同步

MySQL [(none)]> create database test;
Query OK, 1 row affected (0.01 sec)
MySQL [(none)]> use test;
Database changed
MySQL [test]> create table t1(id int(2) PRIMARY KEY, name varchar(48),score int(16));
Query OK, 0 rows affected (0.01 sec)
MySQL [test]> insert into t1(id,name,score)value(1,'xiaobai',100);
Query OK, 1 row affected (0.01 sec)

主库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| t1             |
| t2             |
+----------------+
2 rows in set (0.00 sec)

mysql> select * from t1;
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  1 | xiaobai |   100 |
+----+---------+-------+
1 row in set (0.00 sec)

从库

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)
mysql> select * from t1;
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  1 | xiaobai |   100 |
+----+---------+-------+
1 row in set (0.00 sec)

测试2：读写分离

1.两个从服务器上停止服务

mysql> stop slave;

2.在主服务器上插入内容不会同步

主服务器
mysql> insert into t1 values(10,'ll',70);
Query OK, 1 row affected (0.00 sec)
从服务器
mysql> select * from test.abc;
Empty set (0.00 sec)

3.在两个从服务器上分别插入内容

从服务器1
mysql> insert into t1 values(3,'xiaotian',60);
Query OK, 1 row affected (0.00 sec)
从服务器2
mysql> insert into t1 values(4,'xiaoli',80);
Query OK, 1 row affected (0.01 sec)

4.在客户端上测试（第一次向从服务器1读数据，第二次向从服务器2读数据）

MySQL [test]> select * from t1;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaobai  |   100 |
|  3 | xiaotian |    60 |
+----+----------+-------+
2 rows in set (0.01 sec)

MySQL [test]> select * from t1;
+----+---------+-------+
| id | name    | score |
+----+---------+-------+
|  1 | xiaobai |   100 |
|  4 | xiaoli  |    80 |
+----+---------+-------+
2 rows in set (0.00 sec)

5.在通过客户端连接数据库后写入的数据只有主会记录，然后同步给从-从服务器，从而实现读写分离

MySQL [test]> insert into t1(id,name,score)value(2,'xiaoming',10);

在三台服务器查询

在客户端上看不到新插入的数据，因为同步没有开启，只有主服务器上可以看到数据

mysql> select * from t1;
+----+----------+-------+
| id | name     | score |
+----+----------+-------+
|  1 | xiaobai  |   100 |
|  2 | xiaoming |    10 |
| 10 | ll       |    70 |
+----+----------+-------+
3 rows in set (0.00 sec)

在开启同步后，主服务器上的数据会同步到各从服务器上中，但从服务器上的自己增加的数据不会同步，只有本地保存

从服务器1
mysql> start slave;       
mysql> select * from t1;
从服务器2
mysql> start slave;       
mysql> select * from t1;
客户端：
MySQL [test]> select * from abc;