[转]微服务轮子项目(27) -MySQL 主从复制、主从切换、主主复制

技术_编程语言 专栏收录该内容
128 篇文章 0 订阅

 

 

 

 

1. 主从复制

 

1.1 主库安装

 

1.1.1 创建配置文件

 

1.创建目录

 

mkdir -p /opt/mysql/master/conf

 

2.创建my.cnf文件

 

vim /opt/mysql/master/conf/my.cnf

 

内容如下:

 

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
skip-character-set-client-handshake

binlog_format=ROW
binlog_rows_query_log_events=1
server_id = 1
log-bin= mysql-bin
gtid_mode=on
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
sync_master_info=1
binlog_checksum=CRC32

slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=4
binlog_transaction_dependency_tracking=WRITESET_SESSION
transaction_write_set_extraction=XXHASH64

transaction-isolation=READ-COMMITTED
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
expire_logs_days=30

max_connections=3600

# Custom config should go here
!includedir /etc/mysql/conf.d/

 

备注:

 

skip-character-set-client-handshake:忽略应用程序想要设置的其他字符集
binlog_format:为设置binlog格式
binlog_rows_query_log_events:在row模式下开启该参数,将把sql语句打印到binlog日志里面,默认是0(off);
server_id:这个的值必需所有mysql实例都不重复 log-bin:binlog的名称
gtid_mode:开启GTID,用来代替classic的复制方法
enforce_gtid_consistency:开启gtid的一些安全限制,阻止不安全的语句执行
master_info_repository=table和relay_log_info_repository=table:master.info和relay.info保存在表中
relay_log_recovery:当slave从库宕机后,假如relay-log损坏了,导致一部分中继日志没有处理,则自动放弃所有未执行的relay-log,并且重新从master上获取日志,这样就保证了relay-log的完整性
sync_master_info:每个事务都会刷新master.info binlog_checksum:默认为NONE,表示在图1的箭头1 不生成checksum, 这样就可以兼容旧版本的mysql。 此外,就只能设置为CRC32了
slave-parallel-type:DATABASE为默认值,基于库的并行复制方式;LOGICAL_CLOCK基于组提交的并行复制方式
slave_parallel_workers:设置多少个SQL Thread(coordinator线程)来进行并行复制
binlog_transaction_dependency_tracking:控制是否使用WRITESET策略,WRITESET_SESSION是在写集合的基础上增加约束,保证按照前后顺序执行
transaction_write_set_extraction:控制检测事务依赖关系时采用的HASH算法
transaction-isolation:事务隔离级别 read-only:是否只读,0为否,1为是
replicate-ignore-db:配置忽略同步的数据库 expire_logs_days:binlog日志过期时间,默认不过期
max_connections:mysql最大连接数

 

1.1.2 启动主库

 

1.创建启动命令

 

vim /opt/mysql/master/start.sh

 

内容如下:

 

MYSQL_DIR=/opt/mysql/master
docker stop mysql-m
docker rm mysql-m
docker run -d \
    -p 3306:3306 \
    --name mysql-m \
    -v ${MYSQL_DIR}/conf/my.cnf:/etc/mysql/my.cnf \
    -v ${MYSQL_DIR}/data/mysql:/var/lib/mysql \
    -v ${MYSQL_DIR}/log:/opt/mysql/log \
    -e MYSQL_ROOT_PASSWORD=1q2w3e4r \
    mysql:5.7.25

 

MYSQL_ROOT_PASSWORD:为root账号的登录密码

 

1.1.3 主库创建用于同步的账号

 

1.登录进主库

 

## 进去docker容器内
docker exec -it mysql-m bash

## 登录mysql数据库
mysql -uroot -p1q2w3e4r

 

2.创建backup账号

 

GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';

 

1.2 从库安装

 

进入从库的服务器执行以下操作,建议是不同于主库的服务器,如果服务器相同需要修改3306端口为其他的值。

 

1.2.1 创建配置文件

 

创建目录

 

mkdir -p /opt/mysql/slave/conf

 

创建my.cnf文件

 

vim /opt/mysql/slave/conf/my.cnf

 

内容如下:

 

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
skip-character-set-client-handshake

binlog_format=ROW
binlog_rows_query_log_events=1
server_id = 2
log-bin= mysql-bin
gtid_mode=on
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
sync_master_info=1
binlog_checksum=CRC32

slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=4

binlog_transaction_dependency_tracking=WRITESET_SESSION
transaction_write_set_extraction=XXHASH64

transaction-isolation=READ-COMMITTED
read-only=1
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
expire_logs_days=30

max_connections=3600

# Custom config should go here
!includedir /etc/mysql/conf.d/

 

不同于主库的配置如下:

 

server_id:设为2
read-only:设为只读

 

1.2.2 启动从库

 

创建启动命令

 

vim /opt/mysql/slave/start.sh

 

内容如下:

 

MYSQL_DIR=/opt/mysql/slave
docker stop mysql-s
docker rm mysql-s
docker run -d \
    -p 3306:3306 \
    --name mysql-m \
    -v ${MYSQL_DIR}/conf/my.cnf:/etc/mysql/my.cnf \
    -v ${MYSQL_DIR}/data/mysql:/var/lib/mysql \
    -v ${MYSQL_DIR}/log:/opt/mysql/log \
    -e MYSQL_ROOT_PASSWORD=1q2w3e4r \
    mysql:5.7.25

 

1.2.3 关联主库

 

1.登录进从库

 

## 进去docker容器内
docker exec -it mysql-s bash

## 登录mysql数据库
mysql -uroot -p1q2w3e4r

 

2.执行关联master语句

 

change master to master_host='192.168.28.130',master_port=3306,master_user='backup',master_password='123456',MASTER_AUTO_POSITION=1;

 

master_host:为主库ip
master_port:主库端口
master_user:主库用于同步的帐号
master_password:主库用于同步的帐号密码
master_auto_position:slave连接master将使用基于GTID的复制协议

 

1.2.4 启动并查看slave

 

## 启动slave
start slave;

## 查看slave的状态
show slave status\G

 

在这里插入图片描述

 

Slave_IO_RunningSlave_SQL_Running 都为Yes就代表配置成功了
Seconds_Behind_Master:为主从延时(ms)

 

1.2.5 创建从库的普通用户

 

read_only=1只读模式,可以限定普通用户进行数据修改的操作,但不会限定具有super权限的用户(如超级管理员root用户)的数据修改操作,所以需要另外创建普通账号来操作从库。

 

GRANT select,insert,update,delete,create,drop,alter ON *.* to 'zlt'@'%' identified by '1q2w3e4r';

 

1.3 主库查看同步信息

 

登录主库,查看binlog线程,执行以下语句查看正在执行的线程:

 

show processlist;

 

在这里插入图片描述
查看所有从库信息:

 

show slave hosts;

 

在这里插入图片描述

 

2. 主从切换

 

2.1 对主库进行锁表

 

flush tables with read lock;

 

flush tables with read lock成功获得锁之前,必须等待所有语句执行完成(包括SELECT)。所以如果有个慢查询在执行,或者一个打开的事务,或者其他进程拿着表锁,flush tables with read lock就会被阻塞,直到所有的锁被释放。

 

2.2 检查master同步状态

 

在主库执行

 

show processlist;

 

如果显示Master has sent all binlog to slave; waiting for more updates,则可以执行下一步了,否则需要等待

 

2.3 检查slave同步状态

 

在从库执行

 

show processlist;

 

确保显示为 Slave has read all relay log; waiting for more updates

 

2.4 提升slave为master

 

1.在从库执行以下语句

 

stop slave;
reset master;
reset slave all;

 

reset slave all 命令会删除从库的 replication 参数,之后 show slave status\G 的信息返回为空。

 

2.创建同步用户,在从库执行

 

GRANT REPLICATION SLAVE ON *.* to 'backup'@'%' identified by '123456';

 

3.修改配置并重启

 

  • 修改从库的my.cnf文件,将read-only的值改为0并重启mysql

 

2.5 将原来master变为slave

 

1.修改配置并重启

 

  • 修改主库的my.cnf文件,将read-only的值改为1并重启mysql

 

2.在主库执行以下语句

 

reset master;

reset slave;

change master to master_host='192.168.28.130',master_port=3306,master_user='backup',master_password='123456',MASTER_AUTO_POSITION=1;

start slave;

 

3.查看新slave的状态

 

## 在主库下执行
show slave status\G

 

Slave_IO_RunningSlave_SQL_Running都显示Yes就代表成功了

 

3. 主主复制

 

3.1 主库配置文件

 

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
skip-character-set-client-handshake

binlog_format=ROW
binlog_rows_query_log_events=1
server_id = 1
log-bin= mysql-bin
gtid_mode=on
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
sync_master_info=1
binlog_checksum=CRC32

slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=4

binlog_transaction_dependency_tracking=WRITESET_SESSION
transaction_write_set_extraction=XXHASH64

transaction-isolation=READ-COMMITTED
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
slave-skip-errors=all
expire_logs_days=30

max_connections=3600

auto_increment_offset = 1
auto_increment_increment = 2

# Custom config should go here
!includedir /etc/mysql/conf.d/

 

3.2 第二主库配置文件

 

[client]
default-character-set=utf8
[mysql]
default-character-set=utf8
[mysqld]
collation-server = utf8_unicode_ci
init-connect='SET NAMES utf8'
character-set-server = utf8
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /var/lib/mysql
secure-file-priv= NULL
symbolic-links=0
skip-character-set-client-handshake

binlog_format=ROW
binlog_rows_query_log_events=1
server_id = 2
log-bin= mysql-bin
gtid_mode=on
enforce_gtid_consistency=ON
master_info_repository=TABLE
relay_log_info_repository=TABLE
relay_log_recovery=ON
sync_master_info=1
binlog_checksum=CRC32

slave-parallel-type=LOGICAL_CLOCK
slave_parallel_workers=4

binlog_transaction_dependency_tracking=WRITESET_SESSION
transaction_write_set_extraction=XXHASH64

transaction-isolation=READ-COMMITTED
read-only=0
replicate-ignore-db=mysql
replicate-ignore-db=sys
replicate-ignore-db=information_schema
replicate-ignore-db=performance_schema
slave-skip-errors=all
expire_logs_days=30

max_connections=3600

auto_increment_offset = 2
auto_increment_increment = 2

# Custom config should go here
!includedir /etc/mysql/conf.d/

 

3.3 总结

 

主主配置与主从配置的区别在于以下2点:

 

  • 两个库的read-only都为0,同为可写可读
  • 新增auto_increment_offsetauto_increment_increment参数,并且两个库的auto_increment_offset值不相同

 

  • 0
    点赞
  • 0
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

相关推荐
程序员的必经之路! 【限时优惠】 现在下单,还享四重好礼: 1教学课件免费下载 2课程案例代码免费下载 3专属VIP学员群免费答疑 4下单还送800元编程大礼包 【超实用课程内容】  根据《2019-2020年中国开发者调查报告》显示,超83%的开发者都在使用MySQL数据库。使用量大同时,掌握MySQL早已是运维DBA的必备技能,甚至部分IT开发岗位也要求对数据库使用和原理有深入的了解和掌握。 学习编程,你可能会犹豫选择 C++ 还是 Java;入门数据科学,你可能会纠结于选择 Python 还是 R;但无论如何, MySQL 都是 IT 从业人员不可或缺的技能!   套餐中一共包含2门MySQL数据库必学的核心课程共98课时   课程1:《MySQL数据库从入门到实战应用》   课程2:《高性能MySQL实战课》   【哪些人适合学习这门课程?】  1平时只接触了语言基础,并未学习任何数据库知识的人;  2MySQL掌握程度薄弱的人,课程可以让你更好发挥MySQL最佳性能; 3想修炼更好的MySQL内功,工作中遇到高并发场景可以游刃有余; 4被面试官打破沙锅问到底的问题问到怀疑人生的应聘者。 【课程主要讲哪些内容?】 课程一:《MySQL数据库从入门到实战应用》 主要从基础篇,SQL语言篇MySQL进阶篇三个角度展开讲解,帮助大家更加高效的管理MySQL数据库。 课程二:《高性能MySQL实战课》主要从高可用篇MySQL8.0新特性篇,性能优化篇,面试篇四个角度展开讲解,帮助大家发挥MySQL的最佳性能的优化方法,掌握如何处理海量业务数据和高并发请求 【你能收获到什么?】  1.基础再提高,针对MySQL核心知识点学透,用对; 2.能力再提高,日常工作中的代码换新貌,不怕问题; 3.面试再加分,巴不得面试官打破沙锅问到底,竞争力MAX。 【课程如何观看?】  1登录CSDN学院 APP 在我的课程中进行学习; 2移动端:CSDN 学院APP注意不是CSDN APP哦  本课程为录播课,课程永久有效观看时长 【资料开放】 课件课程案例代码完全开放给你,你可以根据所学知识,自行修改优化。  下载方式:电脑登录课程观看页面,点击右侧课件,可进行课程资料的打包下载。
©️2020 CSDN 皮肤主题: 程序猿惹谁了 设计师:白松林 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值