微服务轮子项目（27） -MySQL 主从复制、主从切换、主主复制
文章目录
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_Running和Slave_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_Running和Slave_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_offset和auto_increment_increment参数，并且两个库的auto_increment_offset值不相同
相关文章
- Mysql主从复制
- Mysql远程连接数据库报错排查：pymysql.err.OperationalError: (2003, "Can't connect to MySQL server on '**.**.**.**' (timed out)")
- MYSQL 函数 字符串到整数
- 【C/C++学院】（23）Mysql数据库编程--C语言编程实现mysql客户端
- mysql-介绍、MySQL部署、数据类型、存储引擎
- mysql-proxy读写分离笔记
- MySQL工具汇总
- mysql按日期分组
- MYSQL错误解决：ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)
- Mysql：Error Code 1235,This version of MySQL doesn’t yet support ‘LIMIT & IN/ALL/ANY/SOME 错误解决
- 分布式mysql中间件（mycat）
- Python查询Mysql时返回字典结构的代码
- 【问题解决方案】MySQL安装后无法启动-net start mysql服务名无效
- MySQL内核月报 2015.01-MySQL · 捉虫动态· mysql client crash一例
- Mysql 如果有多个可选条件怎么加索引_MySQL|mysql-索引
- Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/mysql.pid).
- paip.mysql fulltext 全文搜索.最佳实践.
- Python MySQL - mysql-connector 驱动
- 〖Python 数据库开发实战 - Python与MySQL交互篇⑧〗- 项目实战 - 开发新闻管理系统(项目展示)
- 项目中常用的19条MySQL优化
- MySQL Study之--Mysql无法启动“mysql.host”
- mysql的时间戳timestamp精确到小数点后六位
- mysql的内连接，外连接（左外连接，右外连接）巩固
- 102.第十九章 MySQL数据库 -- MySQL的备份和恢复（十二）
- 手把手教你做用户画像——标签数据开发：用户画像工程化的重点模块，包含统计类、规则类、挖掘类、流式计算类标签的开发，标签相关数据可存储在Hive、MySQL、HBase、Elasticsearch等数据库中
- 【MySQL】mysql查询语句大总结_Unit04
- springboot+jwt+shiro+vue+elementUI+axios+redis+mysql简易博客项目