MYSQL安装及架构主从异步复制程序实现读写分离原理

最新推荐文章于 2024-06-11 17:35:51 发布

liu1002880404

最新推荐文章于 2024-06-11 17:35:51 发布

阅读量217

点赞数 1

分类专栏： mysql

本文链接：https://blog.csdn.net/liuerchong/article/details/107887366

版权

mysql 专栏收录该内容

21 篇文章 1 订阅

订阅专栏

环境准备

CentOS-7 安装好，最好是Minimal版本的

新建soft目录，上传mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz

配置好网络

依赖安装

Mysql 编译安装需要使用perl，先把这些编译工具安装好

yum -y install perl

yum -y install perl-devel

yum -y install 'perl(Data::Dumper)'

安装mysql
1. 解压

tar -zxvf mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz

重命名

mv mysql-5.6.10-linux-glibc2.5-x86_64 mysql

把mysql里面的所有放置到 /usr/local/mysql下

mkdir /usr/local/mysql

mv * /usr/local/mysql

1. 初始化安装
  1. 创建好数据目录

mkdir -p /data/mysql

1. 1. 初始化

cd /usr/local/mysql/scripts

./mysql_install_db --user=root --basedir=/usr/local/mysql --datadir=/data/mysql --pid-file=/data/mysql/mysql.pid --tmpdir=/tmp --explicit_defaults_for_timestamp=true

1. 1. 配置my.cnf文件

cd /usr/local/mysql/support-files

cp my-default.cnf /etc/my.cnf

cp mysql.server /etc/init.d/mysql

vi /etc/my.cnf

basedir = /usr/local/mysql

datadir = /data/mysql

socket = /tmp/mysql.sock

log-error = /data/mysql/error.log

pid-file = /data/mysql/mysql.pid

user = root

tmpdir = /tmp

1. 1. 修改环境变量

vi /etc/profile

export MYSQL_HOME=/usr/local/mysql

export PATH=$MYSQL_HOME/bin:$PATH

source /etc/profile

1. 1. 启动mysql

service mysql start

1. 1. 登陆mysql，修改给root密码

mysql

mysql>use mysql;

mysql>update user set password=password('root') where user='root' and host='localhost';

mysql>flush privileges;

1. 1. 允许远程访问

GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'root' WITH GRANT OPTION;

flush privileges;

1. 1. 关闭防火墙

systemctl stop firewalld.service

systemctl disable firewalld.service

******************************************************************************************
一个服务器上安装运行多个Mysql实例

注意：以centos7为例，mysql版本为5.6.10，由于是在一台机器上安装多个相同服务，所以最好使用二进制方式或者编译源码配置启动，不能使用yum安装

1、
#添加用户组
groupadd mysql
#添加用户mysql 到用户组mysql
useradd -g mysql mysql
并以mysql用户登录

2、
下载一份mysql二进制包
wget https://downloads.mysql.com/archives/get/file/mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz

3、
解压缩到不同的目录，比如mysql3306,mysql3307,mysql3308等

4、
在mysql3306目录下面修改启动配置文件my-default.cnf，这个文件在support-files目录下，主要修改一下几项：
basedir = /home/mysql/mysql3306
datadir = /home/mysql/mysql3306/data
port = 3306
server_id = 1

5、初始化数据库
./scripts/mysql_install_db --user=mysql --defaults-file=/home/mysql/mysql3306/support-files/my-default.cnf --datadir=/home/mysql/mysql3306/data --basedir=/home/mysql/mysql3306
如果没有报错的话，就初始化成功了，初始化成功后会在当前目录下生成一个配置文件my.cnf

6、启动
./bin/mysqld_safe --defaults-file=/home/mysql/mysql3306/my.cnf &

7、
mysql3307,mysql3308重复第4到第6步

8、windows下安装参考 https://jingyan.baidu.com/article/948f592413ab60d80ef5f964.html

为什么要关注Mysql架构？

好处

提高可用性

如果某个数据库实例出现问题,对业务来说，这时可由其他的数据库实例提供服务，或者可以快速切换到其他的数据库实例，对业务来说基本无感知，也不会导致业务的中断。同时通过数据在多个实例之间的复制，提高数据的安全性和可用性。

提高性能

业务对数据的访问可以分散到不同的数据库实例上，可以根据数据访问类型不同，将不同性质的访问操作，进行分离，都可以降低单个数据库实例的访问压力。

Mysql集群的可行方案

MySQL Cluster

由Mysql本身提供，优势：可用性非常高，性能非常好。每份数据至少可在不同主机存一份拷贝，且冗余数据拷贝实时同步。但它的维护非常复杂，存在部分Bug，目前还不适合比较核心的线上系统，所以不推荐。

DRBD磁盘网络镜像方案

Distributed Replicated Block Device，其实现方式是通过网络来镜像整个设备(磁盘).它允许用户在远程机器上建立一个本地块设备的实时镜像,与心跳链接结合使用,也可看做一种网络RAID。

优势：软件功能强大，数据可在底层快设备级别跨物理主机镜像，且可根据性能和可靠性要求配置不同级别的同步。IO操作保持顺序，可满足数据库对数据一致性的苛刻要求。但非分布式文件系统环境无法支持镜像数据同时可见，性能和可靠性两者相互矛盾，无法适用于性能和可靠性要求都比较苛刻的环境，维护成本高于MySQL Replication。另外，DRBD也是官方推荐的可用于MySQL高可用方案之一，所以这个大家可根据实际环境来考虑是否部署。

MySQL Replication

在实际应用场景中，MySQL Replication是使用最为广泛的一种提高系统扩展性的设计手段。众多的MySQL使用者通过Replication功能提升系统的扩展性后，通过简单的增加价格低廉的硬件设备成倍甚至成数量级地提高了原有系统的性能。

Mysql复制

什么是Mysql复制？

复制是指将主数据库的 DDL和 DML 操作通过二进制日志传到复制服务器(也叫从库)上,然后在从库上对这些日志重新执行(也叫重做),从而使得从库和主库的数据保持同步。MysQL支持一台主库同时向多台从库进行复制,从库同时也可以作为其他服务器的主库,实现链状的复制。

注意:

由于MySQL实现的是并不是完全同步的复制,所以主从库之间存在一定的差距,在从库上进行的査询操作需要考虑到这些数据的差异, 一般只有更新不频繁的数据或者对实时性要求不高的数据可以通过从库查询, 实时性要求高的数据仍然需要从主数据库获得。

名称解释：

DML（data manipulation language）数据操纵语言：

就是我们最经常用到的 SELECT、UPDATE、INSERT、DELETE。主要用来对数据库的数据进行一些操作。

DDL（data definition language）数据库定义语言：

其实就是我们在创建表的时候用到的一些sql，比如说：CREATE、ALTER、DROP等。DDL主要是用在定义或改变表的结构，数据类型，表之间的链接和约束等初始化工作上

好处

如果主库出现问题,可以快速切换到从库提供服务。
可以在从库上执行查询操作, 降低主库的访问压力。
某些数据库维护工作，比如备份，可以在从库上执行，以避免备份期间影响主库的服务。

原理概述

( 1 )首先, MySQL主库在事务提交时会把数据变更作为事件 Events 记录在二进制日志文件Binlog中; MySQL主库上的 sync_binlog参数控制 Binlog日志刷新到磁盘。

( 2 )主库推送二进制日志文件 Binlog中的事件到从库的中继日志 Relay Log, 之后从库根据中继日志 Relay Log重做数据变更操作,通过逻辑复制以此来达到主库和从库的数据一致。

MySQL通过3个线程来完成主从库间的数据复制:其中 Binlog Dump线程跑在主库上, I/0线程和 SQL线程跑在从库上。当在从库上启动复制时,首先创建I/0程连接主库,主库随后创建 Binlog Dump线程读取数据库事件并发送给 I/0线程, I0线程获取到事件数据后更新到从库的中继日志 Relay Log中去,之后从库上的 SQL线程读取中继日志RelayLog中更新的数据库事件并应用。

可以通过 SHOW PROCESSLIST命令在主库上査看 BinlogDump线程,从 BinlogDump 线程的状态可以看到, Mysql的复制是主库主动推送日志到从库去的,是属于“推”日志的方式来做同步。同样地,在从库上通过 SHOW PROCESSLIST可以看到l/O线程和 SQL线程, l/O线程等待主库上的 Binlog Dump线程.发送事件并更新到中继日志 RelayLog, SQL线程读取中继日志并应用变更到数据库。

复制中的各类文件解析

日志文件

复制过程中涉及了两类非常重要的日志文件: 二进制日志文件( Binlog)和中继日志文件( Relay Log)。二进制日志文件( Binlog)会把 MysQL中的所有数据修改操作以二进制的形式记录到日志文件中,包括 Create、 Drop、 Insert、 Update、 Delete 操作等,但二进制日志文件(Binlog) 不会记录 Select操作, 因为 Select操作并不修改数据。

可以通过 show variables査看 Binlog的格式, Binlog支持 Statement、 Row、 Mixed三种格式,也对应了 MysQL的3种复制技术。

中继日志文件 Relay Log的文件格式、内容和二进制日志文件 Binlog一样, 唯一的区别在于从库上的 SQL线程在执行完当前中继日志文件 Relay Log中的事件之后, SQL线程会自动删除当前中继日志文件 Relay Log，避免从库上的中继日志文件 Relay Log占用过多的磁盘空间。为了保证从库 Crash重启之后,从库的 I/0线程和 SQL线程仍然能够知道从哪里开始复制, 从库上默认还会创建两个日志文件 master.info和 relay_log.info用来保存复制的进度。这两个文件在磁盘上以文件形式分别记录了从库的 l/0线程当前读取主库二进制日志 Binlog的进度和SQL线程应用中继日志 RelayLog的进度。

可以通过 show slave status命令能够看到当前从库复制的状态。

主要参数：

Master Host: 主库的 IP.

Master User 主库上, 主从复制使用的用户账号,

Master Port:主库 MySQL的端口号,

Master_Log_File:从库的I/0线程当前正在读取的主库 Binlog的文件。

Read_Master Log_Pos:从库I/0线程当前读取到的位置。

Relay_Log_File: 从库 SQL线程正在读取和应用的中继日志 Relay Log的文件名。

Relay_Log_Pos: 从库 SQL线程当前读取并应用的中继日志 Relay Log的位置。

Relay_Master_Log_File:从库 SQL线程正在读取和应用的 Relay Log对应于主库Binlog的文件名。

Exec_Master_Log_Pos:中继日志 RelayLog中 Relay_Log_Pos位置对应于主库 Binlog 的位置。

三种复制技术

二进制日志文件 Binlog有三种格式：

Statement:基于 SQL语句级别的 Binlog,每条修改数据的 SQL都会保存到 Binlog里。

Row:基于行级别,记录每一行数据的变化,也就是将每行数据的变化都记录到 Binlog 里面, 记录得非常详细, 但是并不记录原始 SQL; 在复制的时候, 并不会因为存储过程或触发器造成主从库数据不一致的问通, 但是记录的日志量较 Statement格式要大得多。

Mixed:混合Statement和Row模式,默认情况下采用 Statement模式记录,某些情况下会切换到 Row模式

同时也对应了 MysQL复制的3种技术。

在 binlog_format设置为 Row格式时, MySQL实际上在 Binlog中逐行记录数据的变更, Row格式比 Statement格式更能保证从库数据的一致性(复制的是记录,而不是单纯操作 SQL)。当然, Row格式下的 Binlog的日志量很可能会增大非常多,在设置时需要考虑到磁盘空间间题。

参数 binlog_format可以在全局设置或者在当前 session动态设置: 在全局设置会影响所有session,而在当前 session设置则仅仅影响当前 Session。可以通过 SET命令来实时修改二进日志文件(Binlog)的格式。

常用的复制架构

复制的3种常见架构有一主多从复制架构、多级复制架构和双主复制/DrualMaster架构

一主多从

在主库读取请求压力非常大的场景下, 可以通过配置一主多从复制架构实现读写分离, 把大量对实时性要求不是特别高的读请求通过负载均衡分布到多个从库上, 降低主库的读取压力，在主库出现异常宕机的情况下, 可以把一个从库切换为主库继续提供服务。

多级复制

一主多从的架构能够解决大部分读请求压力特别大的场景的需求, 考虑到 MysQL的复制是主库“推送” Binlog日志到从库,主库的 I/0压力和网络压力会随着从库的增加而增长(每个从库都会在主库上有一个独立的 Binlog Dump线程来发送事件), 而多级复制架构解决了一主多从场景下,主库额外的 I/0和网络压力。

双主复制/Dual Master

其实就是主库 Master和 Master2互为主从， client客户端的写请求都访问主库 Master,而读请求可以选择访问主库 Master或 Master2。

双主多级复制架构

当然双主复制还能和主从复制联合起来使用:在 Master2库下配置从库 Slave、 Slave2等,这样即可通过从库 Slave等来分担读取压力，MyQL的双主多级复制架构如图所示

复制过程搭建

异步复制

主库执行完Commit后，在主库写入Binlog日志后即可成功返回客户端，无需等Binlog日志传送给从库。

步骤：

1、确保主从库安装了相同版本的数据库。

2、在主库上,设置一个复制使用的账户,并授予 REPLICATION SLAVE权限。这里创建一个复制用户repl,可以从IP为192.169.56.103的主机进行连接:

命令文本：GRANT REPLICATION SLAVE ON *.* To 'rep1'@'192.168.56.103' IDENTIFIED BY '1234test';

3、修改主数据库服务器的配置文件 my.cnf,开启 BINLOG,并设置 server-id的值。这两个参数的修改需要重新启动数据库服务才可以生效

在 my cnf中修改如下:

[mysqld]

log-bin=/home/ mysql/log/mysql-bin. log

server-id= 1

注意：如果mysql目录下无log目录，请先创建log目录

4、然后得到主库上当前的二进制日志名和偏移量值。这个操作的目的是为了在从数据库启动以后,从这个点开始进行数据的恢复。

执行show master status：

5、修改从数据库的配置文件 my.cnf,增加 server-id参数。注意 server-id的值必须是唯一的,不能和主数据库的配置相同,如果有多个从数据库服务器,每个从数据库服务器必须有自己唯一的 server-id值。

在 mycnf中修改如下:

[mysqld]

server-id=2

6、在从库上,使用 - -skip-slave- start选项启动从数据库,这样不会立即启动从数据库服务上的复制进程,方便我们对从数据库的服务进行进一步的配置:

操作命令：./bin/mysqld_safe --defaults-file=/home/mysql/mysql3307/my.cnf --skip-slave-start

7、对从数据库服务器做相应设置,指定复制使用的用户,主数据库服务器的IP、端口

以及开始执行复制的日志文件和位置等,参考代码如下:

mysql> CHANGE MASTER TO

->MASTER_HOST=master host name

->MASTER_USER=replication_user_name

-> MASTER PASSWORD=replication_password

->MASTER_LOG_FILE='recorded_log_file_name

->MASTER_LOG_POS=recorded _log_position

举例说明如下:

CHANGE MASTER TO MASTER_HOST='192.168.56.103',MASTER_PORT=3306,MASTER_USER='rep1',MASTER_PASSWORD='1234test' ,MASTER_LOG_FILE='mysql-bin.000001',MASTER_LOG_POS=428;

8、在从库上,启动 slave线程:

9、这时slave上执行 show processlist命令将显示类似如下的进程:

执行show slave status;将显示：

11、在master上执行 show processlist命令将显示类似如下的进程:

12、测试一下：

在主库上新建数据库和表，并插入数据，看看从库中是否会自动创建相关的数据库和表、插入数据。

新建数据库和表之前：

主库中创建数据库orders和表order_exp，并插入数据

检查从库

半同步复制

在 MySQL5.5之前, MySQL的复制是异步操作,主库和从库的数据之间存在一定的延迟,这样存在一个隐患:当在主库上写人一个事务并提交成功,而从库尚未得到主库推送的 Binlog日志时,主库宕机了,例如主库可能因磁盘损坏、内存故障等造成主库上该事务 Binlog丢失,此时从库就可能损失这个事务,从而造成主从不一致。

而半同步复制，是等待其中一个从库也接收到Binlog事务并成功写入Relay Log之后，才返回Commit操作成功给客户端；如此半同步就保证了事务成功提交后至少有两份日志记录，一份在主库Binlog上，另一份在从库的Relay Log上，从而进一步保证数据完整性；半同步复制很大程度取决于主从网络RTT（往返时延），以插件 semisync_master/semisync_slave 形式存在。

安装比较简单,在上一小节异步复制的环境上,安装半同步复制插件即可。

(1)首先,判断 MySQL服务器是否支持动态增加插件:

mysql> select @@have_dynamic_loading;

2)确认支持动态增加插件后,检查 MySQL的安装目录下是否存在插件：

安装插件：

在主库上安装插件semisync_master.so:

mysql> install plugin rpl_semi_sync_master SONAME 'semisync_master.so'

从库上则安装 semisync_slave.so插件:

mysql> install plugin rpl_semi_sync_slave SONAME 'semisync_slave.so';

安装完成后,从 plugin表中能够看到刚才安装的插件

mysql> select * from mysql.plugin;

3)需要分别在主库和从库上配置参数打开半同步semi-sync,默认半同步设置是不打开的,主库上配置全局参数：

mysql> set global rpl_semi_sync_master_enabled=1;

mysql> set global rpl_semi_sync_master timeout 30000;

从库上一样配置全局参数：

mysql> set global rpl_semi_sync_slave_enabled=1;

4)其他步骤同异步复制

读写分离实战

SpringBoot+MyBatis结合MySQL读写分离

读写分离要做的事情就是对于一条SQL该选择哪个数据库去执行，至于谁来做选择数据库这件事儿，无非两个，要么中间件帮我们做，要么程序自己做。因此，一般来讲，读写分离有两种实现方式。第一种是依靠中间件帮我们做SQL分离；第二种是应用程序自己去做分离。这里我们选择程序自己来做，主要是利用Spring提供的路由数据源，以及AOP

然而，应用程序层面去做读写分离最大的弱点（不足之处）在于无法动态增加数据库节点，因为数据源配置都是写在配置中的，新增数据库意味着新加一个数据源，必然改配置，并重启应用。当然，好处就是相对简单。

主要思路：

1、选择数据库执行这个关键性问题，考察Spring的封装体系中

的AbstractRoutingDataSource内部维护了一组目标数据源，并且做了路由key与目标数据源之间的映射，提供基于key查找数据源的方法。因此我们需要声明一个自己的的数据源MyRoutingDataSource来负责，并应该继承自AbstractRoutingDataSource，覆盖determineCurrentLookupKey方法，这个方法返回结果就是我们对数据源的选择。

2、考察AbstractRoutingDataSource的内部，有两个容器：

其中targetDataSources是指我们通过方法

设置的目标数据源，而resolvedDataSources是实际的数据源，Spring通过方法

进行了转换。

而在实际运行时，Spring通过getConnection方法获取数据库连接，在getConnection方法中最终调用 determineTargetDataSource方法来定位实际的数据源，在determineTargetDataSource方法就用到了我们前面覆盖的determineCurrentLookupKey方法来从容器resolvedDataSources获得实际的数据源

3、所以，我们先定义一个枚举类

来表示当前有几个数据源，并用这个类作为两个数据源容器的key。

4、同时为了保证线程安全，每个对数据库操作的线程当前所持有的数据源对象，我们用一个ThreadLocal来保存

然后用类DBContextHolder进行包装，并对外提供主从库切换方法、每个线程的数据源get和set方法

5、在我们自己的的数据源MyRoutingDataSource的determineCurrentLookupKey方法中，只需要即可。

6、在application.yml中配置原生数据源后通过配置类DataSourceConfig加载，

7、然后在 DataSourceConfig中生成一个虚拟数据源myRoutingDataSource的实例，专门负责实际数据源的持有和路由，

8、应用中的事务管理器和MyBatis均使用这个虚拟数据源myRoutingDataSource

9、为了减少对业务代码的侵入，我们定义了一个SpringAop类DataSourceAop，其中定义了两个切点，slavePointcut和masterPointcut，按照方法名进行数据源的切换，比如查询方法要求为query开头等等。

这样默认情况下，所有的查询都走从库，插入/修改/删除走主库

10、特殊情况是某些情况下我们需要强制读主库，针对这种情况，我们定义一个注解用该注解标注的就读主库，对应的，修改下DataSourceAop中切点的定义。

11、测试下我们的代码即可，对应的代码在模块rw-separation下。

liu1002880404

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MYSQL安装及架构主从异步复制程序实现读写分离原理

环境准备CentOS-7 安装好，最好是Minimal版本的新建soft目录，上传mysql-5.6.10-linux-glibc2.5-x86_64.tar.gz配置好网络依赖安装Mysql 编译安装需要使用perl，先把这些编译工具安装好yum -y install perlyum -y install perl-develyum -y install 'perl(Data::Dumper)'安装mysql 解压 tar -zxvf mysql-...
复制链接

扫一扫