Mysql 主从复制与读写分离

简介

        在实际的生产环境中,如果对MySQL数据库的读和写都在一台数据库服务器中操作，无论是在安全性、高可用性还是高并发等各个方面都是不能满足实际需求的。本章将首先讲解MySQL主从复制和MySQL 读写分离的原理，然后介绍如何配置MySQL主从复制和MySQL 读写分离。

一、Mysql主从复制与读写分离概述

        在实际的生产环境中，如果对数据库的读和写都在同一个数据库服务器中操作，无论是在安全性,高可用性还是高并发等各个方面都是完全不能满足实际需求的，因此，一般来说都是通过主从复制(Master-Slave)的方式来同步数据，再通过读写分离来提升数据库的并发负载能力这样的方案来进行部署与实施的。
        一台MySQL主服务器带两台MySQL从服务器做了数据复制，前端应用在进行数据库写操作时，对主服务器进行操作，在进行数据库读操作时，对两台从服务器进行操作，这样大量减轻了主服务器的压力。

二、Mysql主从复制原理

        MySQL的主从复制和MySQL的读写分离两者有着紧密联系，首先要部署主从复制，只有主从复制完成了，才能在此基础上进行数据的读写分离。

1.MySQL 支持的复制类型

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

2.复制的工作过程

三、读写分离原理

        简单来说，读写分离就是只在主服务器上写，只在从服务器上读。基本的原理是让主数据库处理事务性查询，而从数据库处理select查询。数据库复制被用来把事务性查询导致的变更同步到群集中的从数据库。

目前较为常见的MySQL 读写分离分为两种。
1.基于程序代码内部实现
        在代码中根据 select、insert进行路由分类，这类方法也是目前生产环境应用最广泛的。优点是性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支；缺点是需要开发人员来实现，运维人员无从下手。

2.基于中间代理层实现

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

• 1.MySQL-Proxy。MySQL-Proxy为MySQL开源项目，通过其自带的lua脚本进行SQL判断，虽然是MySQL 官方产品，但是MySQL 官方并不建议将MySQL-Proxy用到生产环境。
• 2.Amoeba(变形虫)。由陈思儒开发，其曾就职于阿里巴巴。该程序用 Java 语言进行开发，阿里巴巴将其用于生产环境。它不支持事务和存储过程。
• 经过上述简单的比较，通过程序代码实现MySQL读写分离自然是一个不错的选择，但是并不是所有的应用都适合在程序代码中实现读写分离，例如一些大型复杂的Java应用，如果在程序代码中实现读写分离对代码改动就较大。所以,像这种大型复杂的应用一般会考虑使用代理层来实现。

四、实验案例

实验环境：虚拟机 3台 centos7.9、网卡NAT模式 数量 1、组件包mysql-5.6.36.tar.gz  cmake-2.8.6.tar.gz

设备	IP	备注
Centos01	192.168.19.10	Amoeba
Centos02	192.168.19.20	Master
Centos03	192.168.19.30	Slave

初始配置

关闭防火墙
[root@love ~]# systemctl disable firewalld --now

关闭Selinux
[root@love ~]# setenforce 0
[root@love ~]# sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config

1.Mysql安装

安装所需组件包

[root@love ~]# yum -y install gcc gcc-c++ ntp vim net-tools ncurses-devel autoconf

解压并安装cmake

[root@love ~]# tar zxf cmake-2.8.6.tar.gz
[root@love ~]# cd cmake-2.8.6
[root@love cmake-2.8.6]# ./configure && gmake && gmake install

解压并安装Mysql

root@love ~]# tar zxf mysql-5.6.36.tar.gz 
[root@love ~]# cd mysql-5.6.36
[root@love mysql-5.6.36]# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all
[root@love mysql-5.6.36]# make && make install

创建mysql用户和组

[root@love mysql-5.6.36]# groupadd mysql
[root@love mysql-5.6.36]# useradd -M -s /sbin/nologin mysql -g mysql

修改mysql安装目录权限，复制默认配置文件

[root@love mysql-5.6.36]# chown -R mysql:mysql /usr/local/mysql
[root@love mysql-5.6.36]# cp support-files/my-default.cnf /etc/my.cnf
cp：是否覆盖"/etc/my.cnf"？ y

Mysql数据库初始化

[root@love mysql-5.6.36]# cd /usr/local/mysql/
[root@love mysql]# ./scripts/mysql_install_db --user=mysql  --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

添加mysql 到系统服务

[root@love mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@love mysql]# chmod -x /etc/init.d/mysqld
[root@love mysql]# chkconfig --add mysqld

启动mysql服务

[root@love ~]# systemctl start mysqld

添加mysql操作指令关联系统

[root@love ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile && . /etc/profile

用户登录测试

2.Mysql主从复制

[root@love ~]# cat << EOF >> /etc/ntp.conf 
> server 127.127.1.0
> fudge 127.127.1.0 stratum 8
> EOF
[root@love ~]# systemctl start ntpd

Ntp服务slave配置（从）

[root@love ~]# yum -y install ntpdate
[root@love ~]# ntpdate 192.168.19.20

测试同步

3.主服务器master

修改配置文件

[root@love ~]# sed -i 's/# server_id = .*/server_id = 11/' /etc/my.cnf
[root@love ~]# sed -i '/server_id = 11/a\log_bin = master-bin' /etc/my.cnf
[root@love ~]# sed -i '/log_bin = master-bin/a\log-slave-updates = true' /etc/my.cnf
[root@love ~]# systemctl restart mysqld

创建授权用户myslave 密码123456

[root@love ~]# mysql -u root
mysql> grant replication slave on *.* to 'myslave'@'192.168.19.%' identified by '123456';
mysql> flush privileges;

查询mysql主节点状态，记录file名字 和 position 编号

4.从服务器配置slave

修改配置文件

[root@love1 ~]# sed -i 's/# server_id = .*/server_id = 22/' /etc/my.cnf
[root@love1 ~]# sed -i '/server_id = 22/a\log_bin = master-bin' /etc/my.cnf
[root@love1 ~]# sed -i '/log_bin = master-bin/a\log-slave-updates = true' /etc/my.cnf
[root@love1 ~]# systemctl restart mysqld

连接主服务器

[root@love ~]# mysql -u root
mysql> change master to master_host='192.168.19.20',master_user='myslave',master_password='123456',master_log_fille='master-bin.000001',master_log_pos=411;
Query OK, 0 rows affected, 2 warnings (0.01 sec)
mysql> start slave;

5.MySQL读写分离

部署java运行环境

[root@love ~]# chmod +x jdk-6u14-linux-x64.bin
[root@love ~]# ./jdk-6u14-linux-x64.bin
[root@love ~]# mv jdk1.6.0_14 /usr/local/jdk1.6

添加java运行环境变量

[root@love ~]# 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/

加载java环境变量

[root@love ~]# . /etc/profile
[root@love ~]# java -version

6.部署amoeba

解压并编译安装amoeba

[root@love ~]# mkdir /usr/local/amoeba
[root@love ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
[root@love ~]# cd /usr/local/amoeba/
[root@love amoeba]# vim conf/amoeba.xml
<property name="user">root</property>            客户端连接的用户
<property name="password">123456</property>      客户端连接密码

取消注释
<property name="writePool">server1</property>      写入数据池名称
<property name="readPool">server2</property>        读取数据池名称

编辑数据库连接文件

[root@love amoeba]# vim conf/dbServers.xml
<property name="port">3306</property>
<property name="schema">test</property> 		默认库 名称
<property name="user">root</property>          链接数据库用户名
<property name="password">123456</property>  链接数据库密码  如有注释要取消

修改ipaddress 127.0.0.1 为mysql 主和从的IP
<dbServer name="server1"  parent="abstractServer">  
	<factoryConfig>
       <!-- mysql ip -->
       <property name="ipAddress">192.168.19.20</property>  修改主MYSQL IP 
    </factoryConfig>
</dbServer>
<dbServer name="server1"  parent="abstractServer">  
	<factoryConfig>
       <!-- mysql ip -->
       <property name="ipAddress">192.168.19.30</property>  修改主MYSQL IP 
    </factoryConfig>
</dbServer>

启动amoeba

[root@love amoeba]# nohup ./bin/amoeba start 2>&1 &

查看端口

安装mysql客户端

[root@love ~]# yum -y install mariadb

连接amoeba测试

创建表

[root@love ~]# mysql -u root -p123456 -h 192.168.19.10 -P8066
MySQL [(none)]> create table zang1 (id int(10),name varchar(10),address varchar(20));

插入数据

MySQL [test]> insert into zang1 values('1','zhang','this_is_master');

查询验证：在amoeba上插入数据时，主服务器会同步数据(主服务器在amoeba上拿数据，从服务器只能在主服务器上拿数据)