[转]微服务轮子项目（27） -MySQL 主从复制 主从切换 主主复制

文章目录

1. 主从复制 1.1 主库安装 1.1.1 创建配置文件1.1.2 启动主库1.1.3 主库创建用于同步的账号1.2 从库安装 1.2.1 创建配置文件 1.2.2 启动从库1.2.3 关联主库1.2.4 启动并查看slave1.2.5 创建从库的普通用户1.3 主库查看同步信息 2. 主从切换 2.1 对主库进行锁表 2.2 检查master同步状态 2.3 检查slave同步状态 2.4 提升slave为master 2.5 将原来master变为slave 3. 主主复制 3.1 主库配置文件 3.2 第二主库配置文件 3.3 总结

1. 主从复制

1.1 主库安装

1.1.1 创建配置文件

1.创建目录

mkdir -p /opt/mysql/master/conf

2.创建f文件

vim /opt/mysql/master/conf/f

内容如下:

[client]default-character-set=utf8[mysql]default-character-set=utf8[mysqld]collation-server = utf8_unicode_ciinit-connect='SET NAMES utf8'character-set-server = utf8pid-file = /var/run/mysqld/mysqld.pidsocket= /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlsecure-file-priv= NULLsymbolic-links=0skip-character-set-client-handshakebinlog_format=ROWbinlog_rows_query_log_events=1server_id = 1log-bin= mysql-bingtid_mode=onenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLErelay_log_recovery=ONsync_master_info=1binlog_checksum=CRC32slave-parallel-type=LOGICAL_CLOCKslave_parallel_workers=4binlog_transaction_dependency_tracking=WRITESET_SESSIONtransaction_write_set_extraction=XXHASH64transaction-isolation=READ-COMMITTEDread-only=0replicate-ignore-db=mysqlreplicate-ignore-db=sysreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schemaexpire_logs_days=30max_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/masterdocker stop mysql-mdocker rm mysql-mdocker run -d \-p 3306:3306 \--name mysql-m \-v ${MYSQL_DIR}/conf/f:/etc/mysql/f \-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

创建f文件

vim /opt/mysql/slave/conf/f

内容如下:

[client]default-character-set=utf8[mysql]default-character-set=utf8[mysqld]collation-server = utf8_unicode_ciinit-connect='SET NAMES utf8'character-set-server = utf8pid-file = /var/run/mysqld/mysqld.pidsocket= /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlsecure-file-priv= NULLsymbolic-links=0skip-character-set-client-handshakebinlog_format=ROWbinlog_rows_query_log_events=1server_id = 2log-bin= mysql-bingtid_mode=onenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLErelay_log_recovery=ONsync_master_info=1binlog_checksum=CRC32slave-parallel-type=LOGICAL_CLOCKslave_parallel_workers=4binlog_transaction_dependency_tracking=WRITESET_SESSIONtransaction_write_set_extraction=XXHASH64transaction-isolation=READ-COMMITTEDread-only=1replicate-ignore-db=mysqlreplicate-ignore-db=sysreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schemaexpire_logs_days=30max_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/slavedocker stop mysql-sdocker rm mysql-sdocker run -d \-p 3306:3306 \--name mysql-m \-v ${MYSQL_DIR}/conf/f:/etc/mysql/f \-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

## 启动slavestart 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.修改配置并重启

修改从库的f文件，将read-only的值改为0并重启mysql

2.5 将原来master变为slave

1.修改配置并重启

修改主库的f文件，将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_ciinit-connect='SET NAMES utf8'character-set-server = utf8pid-file = /var/run/mysqld/mysqld.pidsocket= /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlsecure-file-priv= NULLsymbolic-links=0skip-character-set-client-handshakebinlog_format=ROWbinlog_rows_query_log_events=1server_id = 1log-bin= mysql-bingtid_mode=onenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLErelay_log_recovery=ONsync_master_info=1binlog_checksum=CRC32slave-parallel-type=LOGICAL_CLOCKslave_parallel_workers=4binlog_transaction_dependency_tracking=WRITESET_SESSIONtransaction_write_set_extraction=XXHASH64transaction-isolation=READ-COMMITTEDread-only=0replicate-ignore-db=mysqlreplicate-ignore-db=sysreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schemaslave-skip-errors=allexpire_logs_days=30max_connections=3600auto_increment_offset = 1auto_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_ciinit-connect='SET NAMES utf8'character-set-server = utf8pid-file = /var/run/mysqld/mysqld.pidsocket= /var/run/mysqld/mysqld.sockdatadir = /var/lib/mysqlsecure-file-priv= NULLsymbolic-links=0skip-character-set-client-handshakebinlog_format=ROWbinlog_rows_query_log_events=1server_id = 2log-bin= mysql-bingtid_mode=onenforce_gtid_consistency=ONmaster_info_repository=TABLErelay_log_info_repository=TABLErelay_log_recovery=ONsync_master_info=1binlog_checksum=CRC32slave-parallel-type=LOGICAL_CLOCKslave_parallel_workers=4binlog_transaction_dependency_tracking=WRITESET_SESSIONtransaction_write_set_extraction=XXHASH64transaction-isolation=READ-COMMITTEDread-only=0replicate-ignore-db=mysqlreplicate-ignore-db=sysreplicate-ignore-db=information_schemareplicate-ignore-db=performance_schemaslave-skip-errors=allexpire_logs_days=30max_connections=3600auto_increment_offset = 2auto_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值不相同