mysql master-slave

1.参考

【mysql8.0手册】https://dev.mysql.com/doc/refman/8.0/en/

【mysql doc】https://dev.mysql.com/doc/

【alibaba canal】https://github.com/alibaba/canal

2.概要

通过本文可以了解主从同步原理，快速搭建一个主从环境,实现复制场景，并且利用canal实现订阅binglog日志，实现跨实例同步。

中间件版本:

mysql-8.0.27

canal 1.1.5

3.主从同步

创建容错系统的最常见方法就是冗余，复制模式分普通复制、组复制，普通复制即传统的 MySQL复制 提供了一种简单的源(mater)到副本复制方法,。

master-slave syn

MySQL 复制功能允许服务器 -主服务器 - 将所有更改发送到另一台服务器 -从服务器 -从服务器尝试应用所有更改以与主服务器保持同步。复制工作如下：

• 每当 master 的数据库被修改时，更改都会写入一个文件，即所谓的二进制日志或binlog。这是由执行修改数据库的查询的客户端线程完成的。

• master 有一个线程，称为dump thread，它不断读取 master 的 binlog 并将其发送给 slave。

• slave有一个线程，称为IO线程，它接收master的dump线程发送的binlog，并将其写入一个文件： relay log，show variables like ‘%relay%’。

• 从服务器有另一个线程，称为 SQL 线程，它不断读取中继日志并将更改应用到从服务器。

MySQL 复制功能使用三个主线程实现，一个在源服务器上，两个在副本上：

• 二进制日志转储线程。 当副本连接时，源创建一个线程将二进制日志内容发送到副本。该线程可以SHOW PROCESSLIST在源上的输出中标识为Binlog Dump线程。

  二进制日志转储线程获取源二进制日志的锁，用于读取要发送到副本的每个事件。一旦事件被读取，锁就会被释放，甚至在事件被发送到副本之前。

• 复制 I/O 接收器线程。 当START REPLICA在副本服务器上发出一条语句时，副本会创建一个 I/O（接收器）线程，该线程连接到源并要求它发送记录在其二进制日志中的更新。

  复制接收者线程读取源Binlog Dump线程发送的更新（参见上一项）并将它们复制到包含副本中继日志的本地文件。

  该线程的状态显示 Slave_IO_running在 的输出中 SHOW SLAVE STATUS。

• 复制 SQL 应用程序线程。 副本创建一个 SQL（应用程序）线程来读取由复制接收器线程写入的中继日志并执行其中包含的事务。

异步复制

半同步复制

组复制

Mysql集群

InnoDB ReplicaSet 不提供 InnoDB Cluster 提供的所有功能，例如自动故障转移或多主模式。但是您可以手动切换或故障转移到辅助实例，例如在发生故障时。您甚至可以采用现有的复制部署，然后将其作为 InnoDB ReplicaSet 进行管理。

mysql集群主要基于 MySQL Group Replication构建，提供自动成员管理、容错、自动故障转移等功能。

4.环境准备

4.1.环境目录结构

├─mysql-8.0.27-winx64
├─master
│  └─data
│  └─my.ini
├─slave_1
│  └─data
│  └─my.ini
└─slave_2
│  └─data
│  └─my.ini
└─slave_canal
│  ├─bin
│  ├─conf
│  ├─lib
│  ├─logs
│  │  ├─canal
│  │  └─example
│  └─plugin

4.2.安装mysql

下载https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.27-winx64.zip

4.2.1.master

1).配置my.ini

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[mysqld]
# 设置3307端口
port = 3307
# 设置mysql的安装目录
basedir=E:/db/mysql_cluster/mysql-8.0.27-winx64
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
datadir=E:/db/mysql_cluster/master/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=E:/db/mysql_cluster/master/error.log
pid-file=E:/db/mysql_cluster/master/mysql.pid
socket =E:/db/mysql_cluster/master/mysql.socket

# master配置
server-id = 1
log_bin = E:/db/mysql_cluster/master/bin.log
max_binlog_size = 20M
slow_query_log = 1
#(可选配置）要同步的数据库名，要同步多个数据库，就多加几个replicate-db-db=数据库名
binlog-do-db=test
#（可选配置）要忽略的数据库
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

2).安装mysql

1.创建配置文件
2.cmd初始化
>>E:/db/mysql_cluster/mysql-8.0.27-winx64/bin/mysqld --defaults-file=E:/db/mysql_cluster/master/my.ini --initialize --basedir=E:/db/mysql_cluster/mysql-8.0.27-winx64 --datadir=E:/db/mysql_cluster/master/data

3.cmd运行
>>E:/db/mysql_cluster/mysql-8.0.27-winx64/bin/mysqld  --defaults-file=E:/db/mysql_cluster/master/my.ini

4.修改密码
查看 mster/error.log 临时密码，连接mysql修改root密码

>>E:/db/mysql_cluster/mysql-8.0.27-winx64/bin/mysql -h localhost -P 3307 -u root -p
>>use mysql;
>>alter user 'root'@'localhost' identified with mysql_native_password by '123456';
>>flush privileges; 
>>exit;

3).配置slave用户

创建slaveuser，允许从slave节点连接复制binlog。

授予所有权限GRANT ALL PRIVILEGES ON *.* TO 'canal'@'%'

mysql>CREATE USER slaveuser@'%' IDENTIFIED WITH mysql_native_password BY '123456';
mysql>grant replication slave on *.* to slaveuser@'%';
mysql>GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'slaveuser'@'%';
mysql>flush privileges;
mysql>show grants for slaveuser@'%';

4).查看master状态

mysql> show master status；

4.2.2.slave

参考 https://dev.mysql.com/doc/refman/8.0/en/replication-options-replica.html slave配置参数

1).配置my.ini

[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4

[mysqld]
# 设置 3308 端口
port = 3308
# 设置mysql的安装目录
basedir=E:/db/mysql_cluster/mysql-8.0.27-winx64
# 设置 mysql数据库的数据的存放目录，MySQL 8+ 不需要以下配置，系统自己生成即可，否则有可能报错
datadir=E:/db/mysql_cluster/slave_1/data
# 允许最大连接数
max_connections=200
# 服务端使用的字符集默认为8比特编码的latin1字符集
character-set-server=utf8mb4
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
explicit_defaults_for_timestamp=true
log-error=E:/db/mysql_cluster/slave_1/error.log
pid-file=E:/db/mysql_cluster/slave_1/mysql.pid
socket =E:/db/mysql_cluster/slave_1/mysql.socket

# master配置
log_bin = E:/db/mysql_cluster/slave_1/bin.log
server-id = 2
read_only = 1
max_binlog_size = 20M
slow_query_log   = 1
#(可选配置）要同步的数据库名，要同步多个数据库，就多加几个replicate-db-db=数据库名
binlog-do-db=test
#（可选配置）要忽略的数据库
binlog-ignore-db=mysql
binlog-ignore-db=sys
binlog-ignore-db=information_schema
binlog-ignore-db=performance_schema

2）.安装mysql

参考maseter

3).配置slave

参考 master【查看master状态】

# 配置连接master信息
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', master_port = 3307,MASTER_USER='slaveuser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='bin.000001', MASTER_LOG_POS=1673,master_connect_retry = 15,master_retry_count = 0;
mysql> start slave;
mysql> show slave status;

# 删除slave配置信息
# stop slave;
# reset slave all;

创建只读用户，防止用root操作同步库

CREATE USER 'readuser'@'%' IDENTIFIED WITH mysql_native_password BY '123456'; 
GRANT SELECT ON *.* TO 'readuser'@'%';
FLUSH PRIVILEGES;

4).查看master状态

mysql> show master status；

4.3 测试

在master执行一下语句，查看slave库是否同步。

CREATE DATABASE IF NOT EXISTS test DEFAULT CHARSET utf8 COLLATE utf8_general_ci;`

create table t_data
(
   id     bigint(20) not null auto_increment comment '主键',
   name   varchar(32) comment '名称',
   create_time          datetime default CURRENT_TIMESTAMP comment '创建时间',
   update_time          datetime default CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP comment '更新时间',
   primary key (id)
) ENGINE=InnoDB  COMMENT='测试表';

insert into t_data(name) values(1);

5.开启GTID

全局事务标识符 (GTID) 是在源服务器（源）上创建并与提交的每个事务相关联的唯一标识符。此标识符不仅对于它起源的服务器是唯一的，而且对于给定复制拓扑中的所有服务器也是唯一的。

从MySQL5.6开始增加了强大的GTID（Global Transaction ID，全局事务ID）这个特性，用来强化数据库的主备一致性， 故障恢复， 以及容错能力。用于取代过去传统的主从复制（即：基于binlog和position的异步复制）。

借助GTID，在发生主备切换的情况下，MySQL的其他slave可以自动在新主上找到正确的复制位置，这大大简化了复杂复制拓扑下集群的维护，也减少了人为设置复制position发生误操作的风险。另外，基于GTID的复制可以忽略已经执行过的事务，减少了数据发生不一致的风险。

参考：https://dev.mysql.com/doc/refman/8.0/en/replication-mode-change-online-enable-gtids.html

1.修改master、slave 节点my.conf or my.ini
# GTID
gtid_mode=ON
enforce_gtid_consistency=ON

2.设置salve节点
mysql> E:/db/mysql_cluster/mysql-8.0.27-winx64/bin/mysql -h localhost -P 3308 -u root -p
mysql> STOP SLAVE ;
mysql> CHANGE MASTER TO MASTER_HOST='127.0.0.1', master_port = 3307,MASTER_USER='slaveuser', MASTER_PASSWORD='123456',MASTER_AUTO_POSITION = 1;
mysql> START SLAVE ;

Or from MySQL 8.0.22 / 8.0.23:

STOP REPLICA ;
CHANGE REPLICATION SOURCE TO MASTER_HOST='127.0.0.1', master_port = 3307,MASTER_USER='slaveuser', MASTER_PASSWORD='123456',SOURCE_AUTO_POSITION = 1;
START REPLICA ;

3.查看状态
show slave status \G;
show variables like 'gtid_%';

6.canal slave

6.1下载 canal

https://github.com/alibaba/canal/releases

6.2 配置

1）确认mysql配置
[mysqld]  
log-bin=mysql-bin #添加这一行就ok  
binlog-format=ROW #选择row模式   

mysql> show variables like 'binlog_format';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| binlog_format | ROW   |
+---------------+-------+
mysql> show variables like 'log_bin';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| log_bin       | ON    |
+---------------+-------+

2）解压到目录./slave_canal

3) 修改配置 slave_canal/conf/example/instance.properties
# 基础配置
canal.port = 11111 #端口
canal.serverMode = tcp   # canal client的模式: tcp kafka rocketMQ

# 配置destinations
canal.destinations = instance_1 # 指定实例    
canal.conf.dir = ../conf  # 实例配置目录
canal.auto.scan = true   # 自动扫描实列目录,实现实列动态加载、移除
canal.auto.scan.interval = 5 
canal.auto.reset.latest.pos.mode = false
canal.instance.tsdb.spring.xml = classpath:spring/tsdb/h2-tsdb.xml
canal.instance.global.mode = spring
canal.instance.global.lazy = false
canal.instance.global.manager.address = ${canal.admin.manager} 
canal.instance.global.spring.xml = classpath:spring/file-instance.xml 

4) 修改配置 slave_canal/conf/instance_1/instance.properties
canal.instance.master.address=127.0.0.1:3307 #指向mysql master配置
canal.instance.dbUsername=slaveuser   # 拥有主从复制、连接权限的用户
canal.instance.dbPassword=123456  # 对应dbUsername的密码

5) 启动 slave_canal、bin/startup.bat
slave_canal/logs/canal/canal.log
slave_canal/logs/instance_1/instance_1.log


 

5.1 canal客户端测试

see https://github.com/alibaba/canal.git

运行 com.alibaba.otter.canal.example.BaseCanalClientTest.main

在master节点执行 insert,可以看到bingnlog消息

在master节点执行 update,可以看到bingnlog消息

7.问题

7.1.Error_code: 1032; handler error HA_ERR_KEY_NOT_FOUND; the event’s master log FIRST, end_log_pos 429, Error_code: MY-001032

在从库删除数据后，出现主从同步问题
1)stop slave;
2)找出被删除的记录，重新插入从库
E:/db/mysql_cluster/mysql-8.0.27-winx64/bin/mysqlbinlog --no-defaults --base64-output=decode-rows -v  bin.000001 >bin.sql

or

E:/db/mysql_cluster/mysql-8.0.27-winx64/bin/mysqlbinlog --no-defaults  --base64-output=DECODE-ROWS -v  bin.000001

3)start slave;

 

7.2.client does not support authentication protocol requested by server consider upgrading mysql client

创建用户时指定mysql_native_password ， mysql8之后,加密规则是caching_sha2_password。

CREATE USER slaveuser@'%' IDENTIFIED WITH mysql_native_password BY '123456';

7.3.Access denied; you need (at least one of) the SUPER, REPLICATION CLIENT privilege(s)

为用户增加 REPLICATION CLIENT 权限

GRANT SELECT, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO '用户名'@'%';

8.备查命令

8.1.查看从节点数量

show slave hosts;
select * from information_schema.processlist as p where p.command = 'Binlog Dump'; 

8.2.查看版本

select version();

SELECT * FROM information_schema.plugins; SELECT @@innodb_version;

8.2.mysqlbinlog

查看binlog信息 mysqlbinlog -v --base64-output=DECODE-ROWS ./bin.000001

查看binglog文件 SHOW BINARY LOGS

9.数据参考:

1.机器信息DB型(内存(12*32G)384G硬盘2*480G(s4500)SSD+8*960G(s4500)SSD)

show variables like '%binlog%';
1	binlog_cache_size	1048576
2	binlog_checksum	NONE
3	binlog_direct_non_transactional_updates	OFF
4	binlog_error_action	ABORT_SERVER
5	binlog_format	ROW
6	binlog_group_commit_sync_delay	0
7	binlog_group_commit_sync_no_delay_count	0
8	binlog_gtid_simple_recovery	ON
9	binlog_max_flush_queue_time	0
10	binlog_order_commits	ON
11	binlog_row_image	FULL
12	binlog_rows_query_log_events	OFF
13	binlog_skip_flush_commands	OFF
14	binlog_space_limit	0
15	binlog_stmt_cache_size	32768
16	binlog_transaction_dependency_history_size	25000
17	binlog_transaction_dependency_tracking	COMMIT_ORDER
18	encrypt_binlog	OFF
19	have_backup_safe_binlog_info	YES
20	innodb_api_enable_binlog	OFF
21	innodb_locks_unsafe_for_binlog	OFF
22	log_statements_unsafe_for_binlog	ON
23	max_binlog_cache_size	18446744073709547520
24	max_binlog_files	0
25	max_binlog_size	1073741824
26	max_binlog_stmt_cache_size	18446744073709547520
27	sync_binlog	1




show variables like '%relay%';
1	max_relay_log_size	0
2	relay_log	relay-bin
3	relay_log_basename	/data/mysql_3306/relay-bin
4	relay_log_index	/data/mysql_3306/relay-bin.index
5	relay_log_info_file	relay-log.info
6	relay_log_info_repository	TABLE
7	relay_log_purge	ON
8	relay_log_recovery	ON
9	relay_log_space_limit	0
10	sync_relay_log	10000
11	sync_relay_log_info	10000