SqlServer & MySQL 读写分离主从数据同步

2020-10-20 本文已影响0人 booker丶

读写分离是互联网公司为了提升系统吞吐量，稳定性最常见的一个技术，本文章主要介绍SqlServer和MySQL如何进行读写分离

读写分离原理
读写分离基本的原理是让主数据库处理事务性增、改、删操作（INSERT、UPDATE、DELETE），而从数据库处理SELECT查询操作。数据库复制被用来把事务性操作导致的变更同步到集群中的从数据库。

应用场景
读写分离主要应用在数据查询情况多，更新操作少环境，即二八理论。利用主从同步，可以减少数据库压力，数据库优化不一定适用读写分离，也可以使用其他方案，如分表，缓存都是解决方案.可以根据项目业务情况来选择

主从数据同步
SqlServer与MySQL 读写分离实现方式上有所不同，但是原理上是相同的

SqlServer由主库发布，从库订阅实现数据同步
主库完成更新事务后返回结果，同时异步将事务日志推送到发布服务器日志，从库通过订阅拉取（从库从服务器日志拉取）或订阅推送（事务日志推送后推送给从库）方式将事务日志写入从库进行数据同步

image.png
MySQL 主库master将更新事务写入到binlog（二进制日志）
从库salve会有I/O，SQL两个线程。IO线程负责将master库binlog读取写入到中继日志relay log中，SQL线程再将relay log中SQL事务写入到从库中

image.png

读写分离实现

一、MySQL配置

拉取mysql镜像

docker pull mysql:5.7

配置文件mysqld.cnf
主库配置文件添加

#开启二进制日志
log-bin=mysql- bin
#指定serverid
server-id=2

从库配置文件添加

# 指定serverid
server-id=3
# 修改默认存储引擎
default-storage-engine=MyISAM

将配置文件放在挂载目录

image.png

创建数据库容器-挂载在刚创建的服务器目录

#主数据库容器
docker run --name mysql-master -p 3306:3306 -v
/mysql/master:/etc/mysql/mysql.conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

配置主服务
进入容器

docker exec -it mysql-master /bin/bash

连接mysql

mysql -u root -p123456

查看日志路径

show master status;

image.png

创建从库数据库容器

#从数据库容器创建
docker run --name mysql-slave1 -p 3307:3306 -v 
/mysql/slave1:/etc/mysql/mysql.conf.d -e MYSQL_ROOT_PASSWORD=123456 -d mysql:5.7

配置从数据库
进入容器

docker exec -it mysql-slave1 /bin/bash

连接mysql

mysql -u root -p123456

配置从同步主服务数据,执行如下SQL

change master to
master_host='主库ip',
master_user='root',
master_log_file='mysql-bin.000003',
master_log_pos=390,
master_port=3306,
master_password='123456';

启动slave服务

start slave;

查看slave状态

show slave status \G;

image.png
测试主从搭建效果
在 master 容器中创建一张 user 表；

Create Database TestDB;
Use TestDB;

mysql> CREATE TABLE `user` (
`user_id` bigint(20) AUTO_INCREMENT,
`username` varchar(30) NOT NULL,
`password` varchar(30) DEFAULT NULL,
PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

然后在 slave 容器查看已同步