MySQL 主从复制与读写分离

一、案例概述
二、案例前置知识点
实验报告
- 资源列表
- 基础环境
- 一、搭建 MySQL 主从复制
- - 1、安装 MySQL 数据库
  - 2、配置 master 主服务器
  - - master
  - 3、配置 slave 从服务器
  - - slave1
    - slave2
  - 4、验证主从复制效果
- 二、搭建 MySQL 读写分离
- - 1、在 Amoeba 主机上安装 Java 环境
  - 2、安装并配置 Amoeba 软件
  - 3、配置 Amoeba 读写分离，两个 Slave 读负载均衡
  - 4、测试

一、案例概述

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

二、案例前置知识点

1、MySQL 主从复制原理

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

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

实验报告

资源列表

主机	操作系统	IP	配置
master	CentOS7	192.168.72.131	2C4G
slave1	CentOS7	192.168.72.132	2C4G
slave2	CentOS7	192.168.72.133	2C4G
amoeba	CentOS7	192.168.72.134	2C4G
client	CentOS7	192.168.72.130	2C4G

基础环境

关闭防火墙

systemctl stop firewalld
systemctl disable firewalld

关闭内核安全机制

setenforce 0
sed -i "s/^SELINUX=.*/SELINUX=disabled/g" /etc/selinux/config

修改主机名

hostnamectl set-hostname master
hostnamectl set-hostname slave1
hostnamectl set-hostname slave2
hostnamectl set-hostname amoeba
hostnamectl set-hostname client

一、搭建 MySQL 主从复制

1、安装 MySQL 数据库

# 上传脚本
[root@master ~]# tar zxf auto-install-mysql57-glibc.tar.gz 
[root@master ~]# cd auto-install-mysql57-glibc
[root@master auto-install-mysql57-glibc]# ls
auto-install-mysql57-glibc.sh  my.cnf  mysql-5.7.28-linux-glibc2.12-x86_64.tar.gz
[root@master auto-install-mysql57-glibc]# ./auto-install-mysql57-glibc.sh set password=password('123');

2、配置 master 主服务器

master

# 开启二进制日志
[root@master ~]# vi /etc/my.cnf
[mysqld]
log-bin=master-bin
binlog-format = MIXED
server-id = 1
# 重启 MySQL 服务
[root@master ~]# systemctl restart mysqld[root@master ~]# ls /usr/local/mysql/data/
auto.cnf         client-key.pem  ib_logfile1        master.err  mysql.sock.lock     server-cert.pem
ca-key.pem       ib_buffer_pool  ibtmp1             master.pid  performance_schema  server-key.pem
ca.pem           ibdata1         master-bin.000001  mysql       private_key.pem     sys
client-cert.pem  ib_logfile0     master-bin.index   mysql.sock  public_key.pem# 登录 MySQL 程序，给从服务器授权
mysql> grant replication slave on *.* to 'myslave'@'192.168.72.%' identified by '123456';
Query OK, 0 rows affected, 1 warning (0.02 sec)
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.03 sec)# MySQL 返回了主服务器的二进制日志（binary log）的当前状态 
mysql> show master status;
+-------------------+----------+--------------+------------------+-------------------+
| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+-------------------+----------+--------------+------------------+-------------------+
| master-bin.000001 |      603 |              |                  |                   |
+-------------------+----------+--------------+------------------+-------------------+
1 row in set (0.03 sec)

3、配置 slave 从服务器

slave1

# 开启二进制日志
[root@slave1 ~]# vi /etc/my.cnf 
[mysqld]
log-bin=slave1-bin
binlog-format = MIXED
server-id = 2
# 重启 MySQL 服务
[root@slave1 ~]# systemctl restart mysqld
[root@slave1 ~]# ls /usr/local/mysql/data/
auto.cnf         client-key.pem  ib_logfile1  mysql.sock.lock     server-cert.pem    slave1.err
ca-key.pem       ib_buffer_pool  ibtmp1       performance_schema  server-key.pem     slave1.pid
ca.pem           ibdata1         mysql        private_key.pem     slave1-bin.000001  sys
client-cert.pem  ib_logfile0     mysql.sock   public_key.pem      slave1-bin.index# MySQL复制环境中用于配置从服务器（slave）以连接到主服务器（master）并开始复制过程
mysql> change master to master_host='192.168.72.131',        # master 主机的IP-> master_user='myslave',        # master 主机设置的用户名-> master_password='123456',        # master 主机设置的密码-> master_log_file='master-bin.000001',# master 主机的日志文件-> master_log_pos=603;# master 主机的偏移量 当前二进制日志的位置
Query OK, 0 rows affected, 2 warnings (0.04 sec)mysql> start slave;
Query OK, 0 rows affected (0.01 sec)# 检查从服务器的复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
...... # 省略部分代码Slave_IO_Running: YesSlave_SQL_Running: Yes...... # 省略部分代码
1 row in set (0.00 sec)# 如果出现错误，执行重置重新写
# 关闭   
stop slave;
# 重置
reset slave;

slave2

# 开启二进制日志
[root@slave2 ~]# vi /etc/my.cnf 
[mysqld]
log-bin=slave2-bin
binlog-format = MIXED
server-id = 3
# 重启 MySQL 服务
[root@slave2 ~]# systemctl restart mysqld[root@slave2 ~]# ls /usr/local/mysql/data/
auto.cnf         client-key.pem  ib_logfile1  mysql.sock.lock     server-cert.pem    slave2.err
ca-key.pem       ib_buffer_pool  ibtmp1       performance_schema  server-key.pem     slave2.pid
ca.pem           ibdata1         mysql        private_key.pem     slave2-bin.000001  sys
client-cert.pem  ib_logfile0     mysql.sock   public_key.pem      slave2-bin.index# MySQL复制环境中用于配置从服务器（slave）以连接到主服务器（master）并开始复制过程
mysql> change master to master_host='192.168.72.131',        # master 主机的IP-> master_user='myslave',        # master 主机设置的用户名-> master_password='123456',        # master 主机设置的密码-> master_log_file='master-bin.000001',# master 主机的日志文件-> master_log_pos=603;# master 主机的偏移量    当前二进制日志的位置
Query OK, 0 rows affected, 2 warnings (0.04 sec)mysql> start slave;
Query OK, 0 rows affected (0.01 sec)# 检查从服务器的复制状态
mysql> show slave status\G;
*************************** 1. row ***************************
...... # 省略部分代码Slave_IO_Running: YesSlave_SQL_Running: Yes...... # 省略部分代码
1 row in set (0.00 sec)# 如果出现错误，执行重置重新写
# 关闭   
stop slave;
# 重置
reset slave;

4、验证主从复制效果

# 在主、从服务器上登录 MySQL
mysql -uroot -p123
# 在主服务器上新建数据库 kgc
mysql> create database kgc;
Query OK, 1 row affected (0.01 sec)# 在两台从服务器上分别查看数据库，显示数据库相同，则主从复制成功
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kgc                |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
5 rows in set (0.00 sec)

二、搭建 MySQL 读写分离

1、在 Amoeba 主机上安装 Java 环境

[root@master ~]# chmod +x jdk-6u14-linux-x64.bin
[root@master ~]# ./jdk-6u14-linux-x64.bin        //根据提示按 Enter 键完成即可
[root@master ~]# mv jdk1.6.0_14/ /usr/local/jdk1.6[root@master ~]# vi /etc/profile
cat >> /etc/profile << 'EOF'
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
EOF
# 刷新环境变量
source /etc/profile# 查看版本号
[root@master ~]# 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)

2、安装并配置 Amoeba 软件

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

3、配置 Amoeba 读写分离，两个 Slave 读负载均衡

SHOW GRANTS;    # 查看所有权限
select user from mysql.user;    # 查看所有用户delete from mysql.user where user='test';    # 删除指定test用户# (1)master、slave1、slave2 中开放权限给 Amoeba 访问# 在 master 节点操作
# 登录 MySQL 程序，给从服务器授权
grant all on *.* to test@'192.168.72.%' identified by '123.com';
# 刷新权限
flush privileges;
# 创建 test 数据库
create database test;# 在 amoeba 节点操作
# (2)编辑 amoeba.xml 配置文件
[root@master ~]# cd /usr/local/amoeba/conf
[root@master conf]# vi amoeba.xml
# 在 30 行左右修改 user 和 password
...... # 省略部分代码
<property name="user">amoeba</property>
<property name="password">123456</property>
...... # 省略部分代码
# 在 115 行左右修改 defaultPool、writePool、readPool
...... # 省略部分代码
<property name="defaultPool">master</property><property name="writePool">master</property>
<property name="readPool">slaves</property>
...... # 省略部分代码# (3)编辑 dbServers.xml 配置文件
[root@master conf]# vi dbServers.xml
# 在 26 行左右修改 user 和 password 写在master数据库给权限的用户和密码
...... # 省略部分代码
<!-- mysql user -->
<property name="user">test</property>
<!--  mysql password-->
<property name="password">123.com</property>
...... # 省略部分代码
# 在 45 行左右修改以下内容
...... # 省略部分代码<dbServer name="master"  parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.72.131</property></factoryConfig></dbServer><dbServer name="slave1"  parent="abstractServer"><factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.72.132</property></factoryConfig></dbServer><dbServer name="slave2"  parent="abstractServer">    # 若无slave2 段，手动添加<factoryConfig><!-- mysql ip --><property name="ipAddress">192.168.72.133</property></factoryConfig></dbServer>
...... # 省略部分代码
# 在 66 行左右修改以下内容
...... # 省略部分代码<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>        
...... # 省略部分代码# (4)配置无误后，可以启动 Amoeba 软件，其默认端口为 tcp 8066
[root@amoeba ~]# amoeba start &
[1] 9705
[root@amoeba ~]# netstat -anpt | grep 8066
tcp6       0      0 :::8066                 :::*                    LISTEN      9705/java

4、测试

# (1)在 client 主机上安装mariadb
yum -y install mariadb# (2)登录
# IP地址和端口是 amoeba 的地址和端口
mysql -u amoeba -p123456 -h 192.168.72.134 -P 8066# (3)在 Master 上创建一个表，同步到各从服务器上，然后关掉各从服务器的 Slave功能，再插入区别语句。
# 在 master节点创建表
use test;
CREATE TABLE `kgc` (`id` int(10) NOT NULL,`name` VARCHAR(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
# slave节点执行
stop slave;
# master节点执行
insert into kgc values(1,'张三');
# slave1节点执行
insert into kgc values(2,'李四');
# slave2节点执行
insert into kgc values(3,'王五');# (4)测试读操作
# 第一次查询
MySQL [(none)]> select * from test.kgc;
+----+--------+
| id | name   |
+----+--------+
|  3 | 王五   |
+----+--------+
1 row in set (0.03 sec)
# 第二次查询
MySQL [(none)]> select * from test.kgc;
+----+--------+
| id | name   |
+----+--------+
|  2 | 李四   |
+----+--------+
1 row in set (0.01 sec)
# 第三次查询
MySQL [(none)]> select * from test.kgc;
+----+--------+
| id | name   |
+----+--------+
|  3 | 王五   |
+----+--------+
1 row in set (0.00 sec)# (5)测试写操作
# 在client主机上插入一条数据
insert into kgc values(4,'张涛');
# 但在 Client 上查询不到，最终只有在 Master 上才能查看到这条语句内容，说明写操作在 Master 服务器上
MySQL [(none)]> select * from test.kgc;
+----+--------+
| id | name   |
+----+--------+
|  2 | 李四   |
+----+--------+
1 row in set (0.00 sec)