MySQL读写分离项目案例

案例需求

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

MySQL主从复制原理

MySQL的复制类型

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

MySQL主从复制的工作过程
在这里插入图片描述

MySQL读写分离原理

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

案例环境
master服务器：20.0.0.10
从服务器1：20.0.0.11
从服务器2：20.0.0.12
Amoeba服务器：20.0.0.13
管理服务器：20.0.0.14
开始前先关闭所有服务器防火墙或放行需要的端口

systemctl stop firewalld
setenforce 0

配置MASTER服务器

读写分离需要所有的mysql服务器都时间同步因为配置二进制日志文件会对比时间来获取不一至会导致问题
配置NTP服务

[root@master ~]# ntpdate ntp.aliyun.com
[root@master ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate ntp.aliyun.com
[root@master ~]# yum -y install ntp
[root@master ~]# vi /etc/ntp.conf
 17行 restrict 20.0.0.0 mask 255.255.255.0 nomodify notrap
 21行 #server 0.centos.pool.ntp.org iburst
 22行 #server 1.centos.pool.ntp.org iburst
 23行 #server 2.centos.pool.ntp.org iburst
 24行 #server 3.centos.pool.ntp.org iburst ###注释
server 127.127.1.0
fudge 127.127.1.0 stratum 8 ###设置服务器层级是8级，顶级是0

[root@master ~]# systemctl restart ntpd

配置主MySQL数据库
服务器事先使用编译安装了5.6版本的MySQL

[root@master ~]# vi /etc/my.cnf
[mysqld]
...省略
server-id=1 ###mysql服务器id，每个服务器不能相同
log_bin=master-bin ###主服务器日志文件
log_slave_updates=true ###允许中继日志读取主服务器的二进制日志

[root@master ~]# systemctl restart mysqld
[root@master ~]# cd /usr/local/mysql/data
[root@master data]# ll
总用量 122924
-rw-r-----. 1 mysql mysql       56 10月 20 15:16 auto.cnf
-rw-r-----. 1 mysql mysql      285 10月 26 15:59 ib_buffer_pool
-rw-r-----. 1 mysql mysql 12582912 10月 26 16:00 ibdata1
-rw-r-----. 1 mysql mysql 50331648 10月 26 16:00 ib_logfile0
-rw-r-----. 1 mysql mysql 50331648 10月 20 15:16 ib_logfile1
-rw-r-----. 1 mysql mysql 12582912 10月 26 16:00 ibtmp1
-rw-r-----. 1 mysql mysql      154 10月 26 16:00 master-bin.000001
-rw-r-----. 1 mysql mysql       20 10月 26 16:00 master-bin.index
drwxr-x---. 2 mysql mysql     4096 10月 20 15:16 mysql
drwxr-x---. 2 mysql mysql     8192 10月 20 15:16 performance_schema
drwxr-x---. 2 mysql mysql     8192 10月 20 15:16 sys
[root@master ~]# mysql -u root -p
mysql> grant replication slave on *.* to 'myslave'@'20.0.0.%' identified by 'abc123'; ###为所有从服务器授权所有数据库
mysql> flush privileges;
mysql> show grants for myslave@'20.0.0.%';
+------------------------------------------------------------+
| Grants for myslave@20.0.0.%                            |
+------------------------------------------------------------+
| GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'20.0.0.%' |
+------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> show master status; ###记下position的值
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |     1024 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

配置从服务器

与主服务器同步时间

[root@slave1 ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 20.0.0.10

修改从服务器配置文件
[root@slave1 ~]# vi /etc/my.cnf
[mysqld]
…省略
server-id = 12 ###MySQL服务器的id，需要配置不同数字
relay_log=relay-log-bin ###从主服务器上同步日志文件记录到本地中继日志
relay_log_index=slave-relay-bin.index ###定义中继日志的索引
[root@slave1 ~]# systemctl restart mysqld
进入数据库指定复制的master服务器

[root@slave1 ~]# mysql -u root -p
mysql> change master to master_host='20.0.0.10',master_user='myslave',master_password='abc123',master_log_file='master-bin.000001',master_log_pos=1024;
mysql> start slave; ###启动从服务器
mysql> show slave status\G ###查看从服务器状态
...省略
 Slave_IO_Running: Yes 
 Slave_SQL_Running: Yes ###这两项需要为YES

配置amoeba服务器

安装jdk

[root@amoeba opt]# tar zxf jdk-8u91-linux-x64.tar.gz
[root@amoeba opt]# mv jdk1.8.0_91/ /usr/local/java
[root@amoeba opt]# cd /etc/profile.d/
[root@amoeba profile.d]# vim java.sh
export JAVA_HOME=/usr/local/java ###设置java根目录
export PATH=$PATH:$JAVA_HOME/bin ###在PATH环境变量中添加JAVA根目录下的bin子目录
[root@amoeba profile.d]# source java.sh ###java.sh脚本导入到环境变量中，使其生效
[root@amoeba ~]# java -version ###查看版本
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)

安装amoeba

[root@amoeba opt]# unzip amoeba-mysql-3.0.5-RC-distribution.zip -d /usr/local
[root@amoeba ~]# mv /usr/local/amoeba-mysql-3.0.5-RC/ /usr/local/amoeba
[root@amoeba ~]# chmod -R 755 /usr/local/amoeba/
[root@amoeba ~]# vim /usr/local/amoeba/jvm.properties
 32行 #JVM_OPTIONS="-server -Xms256m -Xmx1024m -Xss196k -XX:PermSize=16m -XX:MaxPe    rmSize=96m"
 33行 JVM_OPTIONS="-server -Xms1024m -Xmx1024m -Xss256k"

授权所有MySQL服务器允许amoeba权限

mysql> grant all on *.* to 'test'@'20.0.0.%' identified by 'abc123'

修改amoeba主配置文件

[root@amoeba ~]# vi /usr/local/amoeba/conf/amoeba.xml
#28，30行修改如下 修改客户端连接amoeba前端服务器时使用的用户名和密码
28行                    <property name="user">amoeba</property>
29行
30行                    <property name="password">12345</property>

#在83行修改如下（去掉注释）
<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>
保存退出
[root@amoeba ~]# vi /usr/local/amoeba/conf/dbServers.xml
指定为amoeba创建的允许读取数据库的用户名和密码
22行                         <!-- mysql schema -->
23行                         <property name="schema">mysql</property> ###如果数据库版本为5.7则没有默认的test数据库
24行                         <!-- mysql user -->
25行                         <property name="user">test</property>
26行                         <!-- mysql password -->
27行                         <property name="password">abc123</property>

配置三个服务器主机名和地址
 42行         <dbServer name="master"  parent="abstractServer">
 43行                 <factoryConfig>
 44行                         <property name="ipAddress">20.0.0.10</property>
 45行                 </factoryConfig>
 46行         </dbServer>
 47行
 48行         <dbServer name="slave1"  parent="abstractServer">
 49行                 <factoryConfig>
 50行                         <property name="ipAddress">20.0.0.11</property>
 51行                 </factoryConfig>
 52行
 53行         <dbServer name="slave2"  parent="abstractServer">
 54行                 <factoryConfig>
 55行                         <property name="ipAddress">20.0.0.12</property>
 56行                 </factoryConfig>

指定名为slaves的poolNames中pools的主机名
 60行         <dbServer name="slaves" virtual="true">
 66行                         <property name="poolNames">slave1,slave2</property>
保存退出

开启amoeba服务

[root@amoeba ~]# /usr/local/amoeba/bin/launcher
[root@amoeba ~]# netstat -anpt | grep 8066 ###查看amoeba服务是否开启
tcp6       0      0 :::8066                 :::*                    LISTEN      66636/java

管理服务器测试

[root@amoeba ~]# yum -y install mysql ###安装测试用客户机
[root@amoeba ~]# mysql -uamoeba -p12345 -h 20.0.0.13 -P 8066 ###amoeba地址
mysql> create database test;
mysql> create table aaa(sn int(8) not null primary key, name varchar(3), score char(16));
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| aaa            |
+----------------+
1 row in set (0.00 sec)

查看slave服务器上是否正常复制了master服务器的操作

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

mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| aaa            |
+----------------+
1 row in set (0.00 sec)

再在客户机上查看slave服务器是否正常能正常读取

mysql -uamoeba -p123456 -h 20.0.0.13 -P 8066 ###amoeba地址
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use test;
mysql> show tables;
+----------------+
| Tables_in_test |
+----------------+
| aaa            |
+----------------+
1 row in set (0.00 sec)

这里又一个主从数据库的问题可以和大家分享一下
如果从服务器在于主同步的情况下创建了一个库
这时不会同步给主服务器，但是如果主也创建了一个同名的库，呢么这种情况从库会与主库的断开主从关系，并且健康检查会出现no伴随着报错。

原文链接：https://blog.csdn.net/TheRichCat/article/details/109527627