MySQL主从复制与读写分离

简介

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

MySQL主从复制原理

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

1.MySQL支持的复制类型

(1)基于语句的复制。在主服务器上执行的SQL语句，在从服务器上执行同样的语句。MySQL 默认采用基于语句的复制，效率比较高。

(2)基于行的复制。把改变的内容复制过去，而不是把命令在从服务器上执行一遍。

(3)混合类型的复制。默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采 用基于行的复制。

2.复制的工作过程

MySQL度制的工作过程如图

(1)在每个事务更新数据完成之前，Master在二进制日志记录这些改变。写入二进制日志完成 后.Master通知存储引擎提交事务。

(2) Slave 将 Master 的 Binary log复制到其中继日志。首先，Slave 开始一个工作线程一-/0线 程，/0线程在Master上打开一个普通的连接，然后开始Binlog dump process, Binlog dump process 从Master 的二进制日志中读取事件，如果已经跟上Master.它会睡眠并等待Master产生新的事件。/O 线程将这些事件写入中继日志。

 (3) SQL slave thread (SQL从线程)处理该过程的最后一步。SQL线程从中继日志读取事件， 并重放其中的事件而更新 Slave 的数据，使其与Master 中的数据一致。只要该线程与V/0线程保持一 致，中继日志通常会位于OS的缓存中，所以中继日志的开销很小。

         复制过程有一个很重要的限制，即复制在 Slave上是串行化的，也就是说 Master上的并行更新 操作不能在Slave上并行操作。

MySQL读写分离原理

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

目前较为常见的MySQL读写分离分为两种。

1.基于程序代码内部实现

        在代码中根据 select, inser 进行路由分类，这类方法也是目前生产环境应用最广泛的。优点是 性能较好，因为在程序代码中实现，不需要增加额外的设备作为硬件开支;缺点是需要开发人员来 实现，运维人员无从下手。

2.基于中间代理层实现

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

(1)MySQL-Proxy.MySQL-Proxy为MySQL开源项目，通过其自带的Iu脚本进行SQL判断，虽 然是MySQL官方产品，但是MySQL官方并不建议将MySQL-Proxy 用到生产环境。

(2) Amoeba(变形虫)。由陈思儒开发，其曾就职于阿里巴巴。该程序用 Java语言进行开发， 阿里巴巴将其用于生产环境。它不支持事务和存储过程。

        经过上述简单的比较，通过程序代码实现MySQL读写分离自然是一个不错的选择，但是并不是 所有的应用都适合在程序代码中实现读写分离，例如一些大型复杂的Java应用，如果在程序代码中 实现读写分离对代码改动就较大。所以，像这种大型复杂的应用一般会考虑使用代理层来实现。本 章后续案例通过Amoeba实现。 

案例环境

初始配置

关闭防火墙

systemctl disable firewalld --now

关闭Selinux

setenforce 0

sed -i 's/SELINUX=.*/SELINUX=disabled/g' /etc/selinux/config

配置IP DNS 网关

nmtui

挂载光盘

mount /dev/cdrom /mnt/
mount: /dev/sr0 写保护，将以只读方式挂载

MySQL安装

主从两台配置一样

安装所需组件包
[root@lgc ~]# yum -y install gcc gcc-c++ ntp vim net-tools ncurses-devel autoconf  

上传mysql安装包 mysql-5.6.36.tar.gz  cmake-2.8.6.tar.gz
解压cmakne
[root@lgc ~]# tar zxf cmake-2.8.6.tar.gz

编译安装cmake
[root@lgc ~]# cd cmake-2.8.6/
[root@lgc cmake-2.8.6]# ./configure && gmake && gmake install

解压mysql
[root@lgc cmake-2.8.6]# cd
[root@lgc ~]# tar zxf mysql-5.6.36.tar.gz


编译安装mysql
[root@lgc ~]# cd mysql-5.6.36
[root@lgc 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@lgc mysql-5.6.36]# make && make install

创建mysql用户和组
[root@lgc mysql-5.6.36]# groupadd mysql
[root@lgc mysql-5.6.36]# useradd -M -s /sbin/nologin mysql -g mysql

修改mysql安装目录权限  复制默认配置文件
[root@lgc mysql-5.6.36]# chown -R mysql:mysql /usr/local/mysql
[root@lgc mysql-5.6.36]# cp support-files/my-default.cnf /etc/my.cnf

Mysql数据库 初始化
[root@lgc mysql-5.6.36]# cd
[root@lgc ~]# cd /usr/local/mysql/
[root@lgc mysql]# ./scripts/mysql_install_db --user=mysql  --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data

添加mysql 到系统服务
[root@lgc mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@lgc mysql]# chmod +x /etc/init.d/mysqld
[root@lgc mysql]# chkconfig --add mysqld

启动MySQL服务
[root@lgc ~]# systemctl start mysql

添加mysql操作指令关联系统
[root@lgc ~]# echo "PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile && . /etc/profile

用户登录测试
[root@lgc ~]# mysql -u root

Mysql主从复制

主配置

[root@lgc ~]# cd /usr/local/mysql/
[root@lgc mysql]# cat << EOF >> /etc/ntp.conf 
server 127.127.1.0
fudge 127.127.1.0 stratum 8
EOF


[root@lgc mysql]# systemctl start ntpd

从配置

Ntp服务 slave配置
[root@lgc ~]# yum -y install ntpdate
[root@lgc ~]# ntpdate 192.168.55.48

 主服务器

修改配置文件
[root@lgc mysql]# sed -i 's/# server_id = .*/server_id = 11/' /etc/my.cnf
[root@lgc mysql]# sed -i '/server_id = 11/a\log_bin = master-bin' /etc/my.cnf
[root@lgc mysql]# sed -i '/log_bin = master-bin/a\log-slave-updates = true' /etc/my.cnf
[root@lgc mysql]# systemctl restart mysqld


创建授权用户 myslave  123456
[root@lgc ~]# mysql -u root

mysql> grant replication slave on *.* to 'myslave'@'192.168.55.%' identified by '123456';

mysql> grant replication slave on *.* to 'root'@'192.168.55.%' identified by '123456';     //给后续amoeba主机使用授权root用户

mysql> flush privileges;

mysql> show master status;

从服务器配置

修改配置文件
[root@lgc mysql]# sed -i 's/# server_id = .*/server_id = 22/' /etc/my.cnf
[root@lgc mysql]# sed -i '/server_id = 22/a\relay-log = relay-log-bin' /etc/my.cnf
[root@lgc mysql]# sed -i '/relay-log = relay-log-bin/a\relay-log-index = slave-relay-bin.index' /etc/my.cnf
[root@lgc mysql]# systemctl restart mysqld

连接主服务器
[root@lgc mysql]# mysql -u root

mysql> change master to master_host='192.168.55.48',master_user='myslave',master_passwoord='123456',master_log_file='master-bin.000001',master_log_pos=624;

mysql> start slave;

mysql> show slave status\G;

MySQL读写分离

部署Java 运行环境

上传安装包 jdk-6u14-linux-x64.bin

[root@Haproxy ~]# chmod +x jdk-6u14-linux-x64.bin

[root@Haproxy ~]# ./jdk-6u14-linux-x64.bin

[root@Haproxy ~]# mv jdk1.6.0_14 /usr/local/jdk1.6

 添加java 运行环境变量

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


[root@Haproxy ~]# source /etc/profile

[root@Haproxy ~]# 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)

//Amoeba是基于jdk1.5开发的，所以官方推荐使用jdk1.5或1.6版本，高版本不建议使用

部署amoeba

上传amoeba软件包
创建amoeba主目录
[root@Haproxy ~]# mkdir /usr/local/amoeba
[root@Haproxy ~]# tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba
[root@Haproxy ~]# chmod -R 755 /usr/local/amoeba/
[root@Haproxy ~]# cd /usr/local/amoeba/
[root@Haproxy 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@Haproxy 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>  //链接数据库密码  如有注释要取消



<dbServer name="server1"  parent="abstractServer">  
<factoryConfig>
       <!-- mysql ip -->
       <property name="ipAddress">192.168.55.48</property>
    </factoryConfig>
</dbServer>
       <property name="ipAddress">192.168.55.49</property>

//修改ipaddress 127.0.0.1 为mysql 主和从的IP




[root@Haproxy amoeba]# /usr/local/amoeba/bin/amoeba
amoeba start|stop      //显示此内容说明Amoeba安装成功

 

启动amoeba

[root@Haproxy amoeba]# nohup ./bin/amoeba start &
[root@Haproxy amoeba]# netstat -anpt | grep java





..没启动查看进程删除java进程 重新启动
[root@Haproxy amoeba]# ps -aux
[root@Haproxy amoeba]# kill -9 9034  

 ​​​​​​

安装mysql客户端

[root@Haproxy ~]# yum -y install mariadb  //mariadb的yum安装等同于源码包安装的mysql

测试

amoeba主机上连接

[root@Haproxy ~]# mysql -u root -p123456 -h 192.168.55.47 -P8066  //通过代理访问mysql

MySQL [(none)]> create table info (id int(10),name varchar(20));   //创建表info

MySQL [(none)]> insert into info values('1','liming'); 
MySQL [(none)]> insert into info values('2','libai');         //插入数据

在从服务器上停止同步

mysql> stop slave;
Query OK, 0 rows affected (0.00 sec)

在主服务器上info表中添加zhangshang

mysql> insert into info values('3','zhangshang');
Query OK, 1 row affected (0.00 sec)

amoeba主机上查询验证

MySQL [(none)]> select * from info;

主服务器上查询

mysql> select * from info;

 

由于从服务器停止了同步，amoeba主机上查询到的只有1和2的数据，在主服务器上创建的数据3是查询不到的。

amoeba主机读取从服务器，主服务器创建数据同步到从服务器，停止从服务器同步后，主机便不能读取停止同步之后创建的数据。

原文地址：https://blog.csdn.net/m0_73258133/article/details/136443157

免责声明：本站文章内容转载自网络资源，如本站内容侵犯了原著者的合法权益，可联系本站删除。更多内容请关注自学内容网（zxcms.com）！