mysql 主从复制与读写分离

mysql 主从复制与读写分离

目录

一 mysql 主从复制概述

1.1 mysql 主从同步概述

在实质际的生产环境中,对数据库的读和写都在同一个数据库服务器中,是不能满足实院需求的。无论是在安全性、高可用性还是高并发等各个方面都是完全不能满足实际需求的。

因此,通过主从复制的方式来同步数据,再通过读写分离来提升数据库的并发负载能力。有点类似于rsync,但是不同的是rsync是对磁盘文件做备份,而mysql主从复制是对数据库中的数据、语句做备份。


1.2mysql 主从复制类型

  1. statement:基于语句的复制。在主服务器上执行sql语句,在从服务器上执行同样的语句,mysql默认采用基于语句的复制,执行效率高
  2. row: 基于行的复制。把改变的内容复制过去,而不是把命令在从服务器上执行一遍
  3. mixed:混合类型的复制。默认采用基于语句的复制,一旦发现基于语句无法精确复制时,就会采用基于行的复制

1.3 mysql 主从同步工作过程

  1. master 节点将数据的改变记录成二进制日志(bin log),当master上的数据发生改变时,则将其改变写入二进制日志中。
  2. slave节点会在一定时间间隔内对master的二进制日志进行探测其是否发生改变,如果发生改变,则开始一个I/O线程请求
  3. 同时master节点为每个I/O 线程启动一个dump线程,用于向其发送二进制事件,并保存值slave节点本地的中继日志(relay log)中,slave节点将启动sql线程从中继日志中读取二进制日志,在本地重放,即解析成sql语句逐一执行,使得其数据和master节点的保持一致,最后,I/O线程和sql线程进入睡眠状态,等待下一次被唤醒。

注:

  • 中继日志通常会位于OS的缓存中,所以中级日志的开销很小
  • 复制过程有一个很重要的限制,即复制在slave上时串行化的,也就是说master上并行更新操作不能子啊slave上并行操作

mysql 主从复制与读写分离



1.4 主从复制延迟的原因与优化

延迟原因:

  • master服务器高并发,形成大量事务
  • 网络延迟
  • 主从硬件折本导致
    • cpu主频,内存io,硬盘io

优化:

  • 从库优化mysql参数。比如增大innodb_buffer_pool_size,让更多操作在mysql内存中完成,减少磁盘操作
  • 从库使用高性能主机。包括cpu强悍,内存加大。避免使用虚拟云主机,使用物理主机,这样提高了i/o方面性能
  • 从库使用ssd磁盘
  • 网络优化,避免跨机房实现同步


二:搭建主从同步

2.1 拓扑

master:192.168.23.103 mysq5.7

slave 1:192.168.23.13 mysql5.7

slave 2:192.168.23.12 mysql5.7

客户端:192.168.23.104 mysql

关闭服务器的 firewalld和selinux设置



2.2 配置主从服务器时间同步

配置时间同步,可以使用chrony ,也可以使用ntp,但是两者只能使用其一

2.2.1 主服务器配置

yum -y install ntp
vim /etc/ntp.conf
#在文件末尾添加
server 127.127.23.0  #设置时间源为本地
fudge 127.127.23.0 stratum 8  #设置时间层级(15以内)

#启动ntpd服务
service ntpd start 
#查看udp123端口
netstat -naup | grep :123

2.2.2 从服务器配置

yum -y install ntp
 service  ntpd start 
 #进行时间同步,同步主服务器的时间
 /usr/sbin/ntpdate  192.168.23.10
 
 #配置周期性同步时间
 crontab -e 
 */30 * * * * /usr/sbin/ntpdate 192.168.23.103

2.3 主服务器mysql配置

 systemctl stop firewalld
 setenforce 0


vim /etc/my.cnf
.....
server-id = 103          #配置server id,集群内mysql服务器server -id不可以重复
log-bin=mysql-bin        #主服务器开启二进制日志
binlog_format=mixed      #使用混合模式
log-slave-updates=true    #允许从服务器更新二进制日hi

#重启mysql服务
systemctl restart mysqld
#进入数据库
mysql -uroot -pabc123
#刷新二进制日志
flush logs;  

#创建主从复制授权用户
grant  replication slave on *.* to 'myslave'@'192.168.23.%'  identified by 'abc123';

#查看当前 二进制日志名file和偏移量position
mysql> show  master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000001 |      451 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)



2.4 从服务器mysql配置

#关闭firewalld和selinux
systemctl stop firewalld
setenforce 0


vim /etc/my.cnf
....
#配置slave1的server id为12,slave 2 的server-id为13
server-id= 12 (server-id=13 #slave2)

relay-log=relay-log-bin                  #开启中继日志,从主服务器上同步日志文件记录到本地
relay-log-index=slave-relay-bin.index    #定义中继日志的路径


#重启mysqld服务
systemctl restart mysqld

mysql 主从复制与读写分离


#进入数据库
mysql -uroot -pabc123

#配置同步
change master to
	master_host='192.168.23.103',   #主服务器地址
	master_user='myslave',          #使用的用户
	master_password='abc123',        #密码
	master_log_file='mysql-bin.000001',   #当前二进制日志名
	master_log_pos=451;                #二进制日志偏移量
	 
start slave;        #启动同步。如果报错,执行reset  slave,然后重新设置

#查看slave状态,Slave_IO_Running和Slave_SQL_Running 状态都是yes
#如果状态不对,则stop slave;停止同步,然后再使用change命令重新配置
show slave status \G   

注意:
一般Slave_IO_Running:No 的可能性有:

  1. 网络不通
  2. my.cnf配置有问题
  3. 密码、file文件名、pos偏移量不对
  4. 防火墙没有关闭

mysql 主从复制与读写分离



2.5 验证主从复制

#在主服务器上进行增删改操作
#创建数据库test02
mysql> create database test02;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bank               |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test02             |
+--------------------+



#从服务器上进行查看
#此时,从服务器上自动创建了数据库 test02
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
| test02             |
+--------------------+
5 rows in set (0.00 sec)



三: 读写分离概述

3.1、什么是读写分离

读写分离,基本是的原理是让主数据处理事务性增,删,改操作(insert,update,delete), 而从数据库处理select 查询操作。数据库复制被用来把事务性操作导致的变更同步到集群从数据库


3.2 为什么做读写分离

因为数据库的 ”写“(写10000 条数据可能需要3分钟)操作比较耗时。

但是数据库的”读”(读10000条数据可能只要5秒钟)。

所以读写分离,解决的是数据库的写入影响了查询的效率


3.3 什么时候要读写分离

数据库不一定要读写分离,如果程序使用数据库较多时,而更新少,查询多的情况下会考虑使用。利用数据库主从同步,再通过读写分离可以分担数据库压力,提高性能。


3.4常见的mysql 读写分离

3,4,1 基于程序代码内部实现

在代码中根据select、insert进行路由分类,这类方法也是目前生产环境应用最广泛的。
优点是性能较好,因为在程序代码中实现,不需要增加额外的设备为硬件开支;缺点是需要开发人员来实现, 运维人 员无从下手。
但是并不是所有的应用都适合在程序代码中实现读写分离,像一些大型复杂的Java应用,如果在程序代码中实现读写分离对代码改动就较大。

3.4.2 基于中间代理层实现

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

  1. MySQL-Proxy

    1. MySQL-Proxy为MySQL开源项目,通过其自带的lua脚本进行SQL判断。

  2. Atlas

    1. 是由奇虎360的Web平台部基础架构团队开发维护的一个基于MySQL协议的数据中间层项目。它是在mysql-proxy0.8.2版本的基础上,对其进行了优化,增加了一些新的功能特性。360内部使用Atlas运行的mysql业务,每天承载的读写请求数达几十亿条,支持事物以及存储过程。

  1. Amoeba

    1. 由陈思儒开发,作者曾就职于阿里巴巴。该程序由Java语言进行开发,阿里巴巴将其用于生产环境。但是它不支持事务和存储过程。
    2. 由于使用MySQL Proxy需要写大量的Lua脚本,这些Lua并不是现成的,而是需要自己去写。这对于并不熟悉MySQL Proxy内置变量和MySQL Protocol的人来说是非常困难的。
    3. Amoeba是一个非常容易便用、可移植性非常强的软件。因此它在生产环境中被广泛应用于数据库的代理层。

  2. maxscale 服务

    1. 由mariaDB公司开发
    2. 下载地址https://downloads.mariadb.com/files/MaxScale

  1. mycat 软件
    1. 基于java的分布式数据库系统中间件,为高并发环境的分布式存储提供解决方案
    2. 适合数据大量写入的存储需求
    3. 支持mysql,oracle,sqlserver,MongoDB等
    4. 提供数据读写分离服务
    5. 提供数据分片服务
    6. 基于阿里巴巴Cobar进行研发的开源软件


四 :使用amoeba 搭建mysql读写分离

4.1 拓扑

master: 192.168.23.103

slave 1:192.168.23.12

slave2:192.168.23.13

amoeba:192.168.23.11 软件 amoeba-mysql-binary-2.2.0.tar.gz ,jdk-6u14-linux-x64.bin

关闭服务器的firewalld和selinux功能


4.2 安装JAVA环境

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

cd /opt/
#将ameoba软软件和jdk软件上传到 /opt/目录中
ls amoeba-mysql-binary-2.2.0.tar.gz 
ls jdk-6u14-linux-x64.bin 

cp jdk-6u14-linux-x64.bin   /usr/local/
cd /usr/local/
mv   /usr/local/jdk1.6.0_14/  /usr/local/jdk1.6
chmod +x jdk-6u14-linux-x64.bin

./jdk-6u14-linux-x64.bin   
#一直按回车,直到出现"please enter “yes” or “no" ,然后输入yes并回车
#出现  Press Enter to continue 。..... ,按下回车

mysql 主从复制与读写分离

mysql 主从复制与读写分离


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
export PATH=$PATH:$AMOEBA_HOME/bin


source  /etc/profile
java -version  #需要1.6版本

mysql 主从复制与读写分离



4.3 安装 amoeba软件

mkdir /usr/local/amoeba
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz  -C /usr/local/amoeba/

chmod -R 755 /usr/local/amoeba/

/usr/local/amoeba/bin/amoeba
# 显示amoeba start|stop 说明安装成功

mysql 主从复制与读写分离


4.4 配置读写分离

4.4.1 主从mysql 开发权限给amoeba

#及群里所有mysql服务器
mysql -uroot -pabc123
#创建amoeba的授权登录用户
grant all on *.* to  amoeba@'192.168.23.%' identified by 'abc123';

4.4.2 amoeba 服务器修改/amoeba配置文件

cd /usr/local/amoeba/conf/
cp amoeba.xml{,.bak}

#修改amoeba配置文件,此为xml文件,使用<!-- --> 注释
vim amoeba.xml  

<!-- 配置amoeba服务器登录用户名和密码-->
30// <property name="user">amoeba</property>
32//  <property name="password">abc123</property>

<!-- defaultpool,默认池,定义写的数据库即maste。 master为自定义名 -->
115// <property name="defaultPool">master</property>

<!--打开原117和  120行的注释,配置 写为master,读为slave1和slave2-->
117// <property name="writePool">master</property>
118//  <property name="readPool">slaves</property>


:wq

mysql 主从复制与读写分离

mysql 主从复制与读写分离



4.4.3 修改数据库配置文件

cp dbServers.xml{,.bak}
#修改数据库配置文件
vim dbServers.xml

<!--注释23行。注释默认进入test库,以防mysql中没有test库报错-->
23// <!--<property name="schema">test</property>-->
<!--mysql的授权用户为amoeba -->
26// <property name="user">amoeba</property>

<!-- 删除或者取消28和30行的注释,使得29行的类容生效.此行配置mysql授权用户的密码-->
28// <!--  mysql password -->
29// <property name="password">abc123</property>
30// 

<!--设置主服务器名为master-->
45// <dbServer name="master"  parent="abstractServer">
<!-- 设置主服务器的ip地址-->0
48// <property name="ipAddress">192.168.23.103</property>

<!--设置从服务器名为slave1 -->
52// <dbServer name="slave1"  parent="abstractServer">
<!-- 设置slave1 ip地址 -->
55// <property name="ipAddress">192.168.23.12</property>

  
<!-- 复制slave1的配置(6行),修改为slave2 配置-->
58// <dbServer name="slave2"  parent="abstractServer">
59//     <factoryConfig>
60//         <!-- mysql ip -->
61//         <property name="ipAddress">192.168.23.13</property>
62//      </factoryConfig>
63// </dbServer>

<!-- 定义slaves(amoeba的配置文件amoeba.xml的 readpol部分定义的名字是slaves)-->
65// <dbServer name="slaves" virtual="true"> 
<!--将slave1,slave2加入 -->
71//<property name="poolNames">slave1,slave2</property>

mysql 主从复制与读写分离

mysql 主从复制与读写分离

4.5 启动amoeba,并进行测试

4.5.1 启动amoeba

#启动amoeba服务,信息提示框可以使用ctrl +c取消。或者重开一个终端
/usr/local/amoeba/bin/amoeba start &
#amoeba使用tcp的8066端口
netstat -natp | grep java

mysql 主从复制与读写分离


4.5.2 客户端连接测试

#使用amoeba服务器的用户密码登录, 指定ip为amoeba服务器, 指定端口为8066
mysql -u amoeba -pabc123 -h192.168.23.11 -P 8066

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bank               |
| mysql              |
| performance_schema |
| sys                |
| test               |
| test02             |
+--------------------+
7 rows in set (0.01 sec)

mysql> create table class (id int,name char(20));
Query OK, 0 rows affected (0.03 sec)

mysql> show tables;
+------------------+
| Tables_in_test02 |
+------------------+
| class            |
+------------------+
1 row in set (0.01 sec)

4.5.3 从数据库对同一张数据表写入不同的内容

slave 1 配置

mysql> use test02

#在amoeba服务器创建的表,也同步到了slave上
mysql> show tables;
+------------------+
| Tables_in_test02 |
+------------------+
| class            |
+------------------+
1 row in set (0.00 sec)

mysql> insert into class values (1,'slave1');
Query OK, 1 row affected (0.01 sec)

mysql> select * from class;
+------+--------+
| id   | name   |
+------+--------+
|    1 | slave1 |
+------+--------+
1 row in set (0.00 sec)


slave2配置

mysql> use test02
mysql> show tables;
+------------------+
| Tables_in_test02 |
+------------------+
| class            |
+------------------+
1 row in set (0.00 sec)

mysql> select * from class;
Empty set (0.00 sec)

mysql> insert into class  values (2,'slave2');
Query OK, 1 row affected (0.00 sec)

mysql> select * from class;
+------+--------+
| id   | name   |
+------+--------+
|    2 | slave2 |
+------+--------+
1 row in set (0.00 sec)


4.5.4 在amoeba上查看 test02库class表

 mysql -u amoeba -pabc123 -h192.168.23.11 -P 8066 
 
 #查看class表,内容分别为slave1,和slave2轮询
 mysql> select * from test02.class;
+------+--------+
| id   | name   |
+------+--------+
|    2 | slave2 |
+------+--------+
1 row in set (0.00 sec)

mysql> select * from test02.class;
+------+--------+
| id   | name   |
+------+--------+
|    1 | slave1 |
+------+--------+
1 row in set (0.00 sec)
上一篇:想要彻底搞懂大厂是如何实现Redis高可用的?看这篇文章就够了!(1.2W字,建议收藏)


下一篇:mysql主从复制