mysql主从同步

mysql主从同步

---

常用sql

set sql_log_bin=0 #暂停二进制复制
show processlist  #查看线程
show status like '%semi%'; #查看半同步状态
show slave status \G; #查看从库状态
show variables like '%binlog%' #查看变量

docker主从复制：

1.全量备份数据库
2.启动docker

mkdir -p /home/{mysql3306,mysql3307,mysql3308}
chown -R mysql:mysql  /home/{mysql3306,mysql3307,mysql3308}

docker run -d --user 306:306   --name mysql3306 -p 3306:3306  -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai   -v /home:/home -v /etc/my3306.cnf:/etc/my.cnf   mysql:8.0.33

docker run -d --user 306:306   --name mysql3307 -p 3307:3306  -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai   -v /home:/home -v /etc/my3307.cnf:/etc/my.cnf   mysql:8.0.33


docker run -d --user 306:306   --name mysql3308 -p 3306:3306  -e MYSQL_ROOT_PASSWORD=123456 -e TZ=Asia/Shanghai   -v /home:/home -v /etc/my3308.cnf:/etc/my.cnf   mysql:8.0.33

#配置文件内容，按照不同端口修改不同的配置
[mysqld]
bind-address = 0.0.0.0
default_time_zone = '+8:00'

log_timestamps=system
datadir=/home/mysql
socket=/home/mysql/mysql.sock
pid-file=/home/mysql/mariadb.pid


log-error=/home/mysql/log
general_log=on
general_log_file=/home/mysql/general_log
log_output=TABLE
slow_query_log= on
slow_query_log_file=/home/mysql/slow_query_log
long_query_time=3
log_bin =/home/mysql/binlog/binlog
server-id=3306



!includedir /etc/my.cnf.d

[client]
socket=/home/mysql/mysql.sock

#从库配置增加 read-only = on

3.创建同步账号，在主库创建，从库不要绕开主库操作

create user repluser@'%' identified by '123456@Root'
grant replication all on *.* to repluser@'%'
#查看主库状态,记录同步日志点
show master status;

CHANGE MASTER TO  MASTER_HOST='10.1.0.18', MASTER_USER='repluser', MASTER_PASSWORD='123456@Root', MASTER_PORT=3306, MASTER_LOG_FILE='binlog.000021', MASTER_LOG_POS=157 



#查看从库状态
show slave status;

4.开启同步

start slave;
show processlist; #查看线程
show slave status; #查看从库状态

5.复制错误跟踪日志

 select * from performance_schema.replication_applier_status_by_worker \G;
 # Worker 1 failed executing transaction 'ANONYMOUS' at source log binlog.000021, end_log_pos 780; Error 'Operation CREATE USER failed for 'repluser'@'%'' on query. Default database: 'mysql'. Query: 'CREATE USER 'repluser'@'%' IDENTIFIED WITH 'caching_sha2_password' AS '$A$005$*q]Vu@ZX)60QO{hbcQVLFFwPpGhdPYOQ9EbulU21d1bQVsyKo.f/nUKZB''
此错误为在从库上手动添加了同步用户。可以在binlog中找到这个pos点，跳过这个点后往后继续执行

stop slave; #先停掉同步线程
RESET REPLICA all; #重置同步
# 配置文件里增加
slave_skip_error=1007 跳过错误编号


MySQL主从复制报错Authentication plugin ‘caching_sha2_password‘ reported error: Authentication
#主库执行 ALTER USER 'repluser'@'%' IDENTIFIED WITH mysql_native_password BY '123456@Root';

6.主主复制

CHANGE MASTER TO  MASTER_HOST='10.1.0.18', MASTER_USER='repluser', MASTER_PASSWORD='123456@Root', MASTER_PORT=3307, MASTER_LOG_FILE='binlog.000021', MASTER_LOG_POS=157 

3306 服务器配置
auto_increment_offset=10
auto_increment_increment=2

3307 服务器配置
auto_increment_offset=11
auto_increment_increment=2

lvs服务器采用 dnat 模式，rr 算法，保证每个机器处理请求一致
lvs ip:192.168.1.17 
net.ipv4.ip_forward=1
ipvsadm -A -t 192.168.1.17:3306 -s rr
ipvsadm -a -t 192.168.1.17:3306 -r 10.1.0.18:3306
ipvsadm -a -t 192.168.1.17:3306 -r 10.1.0.18:3307
ipvsadm-save -n > /etc/sysconfig/ipvsadm

192.168.1.17 配置双网卡ip 10.1.0.17
10.1.0.18 配置路由为  10.1.0.17

7.半同步复制
7.1 开启插件

#在mysql 客户端执行,主节点开启
install plugin rpl_semi_sync_master soname 'semisync_master.so';

#配置文件增加
rpl_semi_sync_master_enabled =on
rpl_semi_sync_master_timeout=3000


#在mysql 客户端执行,从节点开启
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';

#配置文件增加
rpl_semi_sync_slave_enabled =on

7.2验证半同步连接客户

show status like '%semi%';

8.同步数据过滤器

#方法1：
binlog-do-db=db1 #主库配置 仅复制db1
show master status; #查看状态
#方法2
replicate_do_db="db1" #从库配置仅同步db1
 show slave status \G; #查看Replicate_Do_DB 

9.GTID同步

主库3306 从库3307 开启GTID同步配置
gtid_mode=on
enforce_gtid_consistency=on

#从库执行
stop slave;
RESET REPLICA all;
CHANGE MASTER TO  MASTER_HOST='10.1.0.18', MASTER_USER='repluser', MASTER_PASSWORD='123456@Root', MASTER_PORT=3306, MASTER_AUTO_POSITION=1;