从零开始的Linux运维屌丝之路,资源免费分享平台   运维人员首选:简单、易用、高效、安全、稳定、社区活跃的开源软件
  • 首页
  • MYSQL
  • MYSQL 5.5.62 主从复制 半同步复制

MYSQL 5.5.62 主从复制 半同步复制

发布:蔺要红07-06分类: MYSQL


MSYQL主从复制并不是磁盘上文件直接同步,主库把binlog日志发送到从库,从库把binlog解析成SQL语句,在数据库里按顺序执行SQL语句
MYSQL主从复制都是异步的复制方式,并不是严格的实时数据同步
Mysql 5.7 从节点配置多线程主从复制
Mysql 采用多线程进行复制是从 Mysql 5.6 开始支持的内容,但是只能每个数据库只能一个线程,也就是说如果我们只有一个数据库,则主从复制时也只有一个线程在工作。相当于还是以前的单线程。 
从 Mysql 5.7 开始支持同一数据库下并行主从复制。
不过默认情况下,还是单数据库单个线程,如果需要使用多线程,需要在从节点进行配置。
Mysql 5.7 对主从复制增加了一种类型,共有两种类型,如下:
	DATABASE 基于库的并行复制 , 每个数据库对应一个复制线程
	LOGICAL_CLOCK 基于组提交的并行复制方式,同一个数据库下可以有多个线程
# MYSQL主从  从库不要超过5台, master - Slave
  MYSQL互为主从 (有缺点) master - master
  MYSQL级连单向双主同步  master - Slave 
                              - master - Slaves
                                       - Slaves
  MYSQL环状同步(忽略)
  MYSQL环状级连(忽略)	


推荐架构



主从复制原理图
https://www.linyaohong.com/uploads/allimg/190830/1-1ZS0220015A2.jpg



主从复制过程
# 1.主库开启binlog
server-id = 1
log-bin = /data/3306/mysql-bin
binlog_format=STATEMENT
max_binlog_size = 512M  #默认1G
expire_logs_days = 15
binlog_cache_size = 512K
max_binlog_cache_size = 2M

# 2.锁表或停止服务备份主库数据,并且恢复到从库
备份:mysqldump -uroot -p111111 -S /data/3306/mysql.sock -B -F -R --triggers --single-transaction --master-data=2 --events linyaohong >/backup/linyaohong.sql
恢复:gunzip < /backup/linyaohong.sql.gz|mysql -uroot  -p111111 -S /data/3307/mysql.sock

# 3.主库授权可以同步的账号
grant replication slave,replication client on *.* to repl@'10.10.10.1%' identified by "111111";
flush privileges;

#replication slave 权限代表允许slave主机通过此用户连接master以便建立主从 复制关系
#replication client 权限代表允许执行show master status,show slave status,show binary logs命令

# 4.主库查看binlog文件名,和pos位置点
mysql> show master status; (这里如果有新数据写入会变,所以推荐用备份文件过滤归来位置点)
[root@test01 /home]# grep CHANGE /backup/linyaohong.sql
-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000027', MASTER_LOG_POS=107;

# 5.从库配置server-id,不能和主库相同
read-only                      # 配置从库不能写
server-id = 7
slave-skip-errors = 1032,1062  # 自动跳过错误号
#log-slave-updates                  # 如果从开需要开启binlog,一定要加此参数,不开启忽略即可

# 6.从库连接主库()
change master to master_host='10.10.10.190',master_user='repl',master_password='111111',master_log_file='mysql-bin.000005', master_log_pos=681;
show slave status \G;

# change master 出错, 
修改master info不生效,可以执行 reset slave all; # 然后重新change

# 7.从库开启主从复制
start slave;  # 启动 
show slave status\G  # 查看状态

stop slave; # 停止主从复制( 经测试,如果主库重启数据库,从库会有60秒左右的时间延迟去连接主库)
# error reconnecting to master 'repl@10.10.10.190:3306' - retry-time: 60  retries: 86400

# 8.主库解表 / 对外提供服务,

快速步骤如下(123步骤提前做好):​​​​​​
1. 主库的log-bin和server-id参数配置正确
2. 安装好要配置从库的数据库,配置好server-id参数和log-bin
3. 登录主库,增加从库连接主库同步的账户,例如:rep,并授权 replication slave同步的权限。
4. 半夜mysqldump带x和-master-data=2的参数备份,全备数据备份文件,第二天把它恢复到从库
5. 从备份文件过滤出binlog日志文件和位置点,在从库执行 change master to语句,
6. 从库开启同步开关, start slave
7. 从库 show slave status\g,检查同步状态,并在主库进行更新测试

从库生成的文件
relay-bin.000008
relay-bin.000009
relay-bin.index
relay-log.info
# 从库data目录下生成
master.info

需要关注的状态
Slave_IO_Running: Yes     # 两个YES 代表同步正常
Slave_SQL_Running: Yes
Seconds_Behind_Master: 0  # 同步延迟时间,更准确的判断主从延迟的方法:在主库写时间戳,然后从库读取时间戳,和当前数据库的时间进行比较,从而判断是否延迟

进程的查看
mysql> show processlist;  # 从库/可以看到有2个线程,一个I/O线程,一个SQL线程
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time  | State                                                                       | Info             |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 10 | system user |           | NULL | Connect | 57345 | Waiting for master to send event                                            | NULL             |
| 11 | system user |           | NULL | Connect | 35096 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 16 | root        | localhost | NULL | Query   |     0 | NULL                                                                        | show processlist |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

mysql> show processlist;  # 从库/可以看到有2个线程,一个I/O线程,一个SQL线程
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| Id | User        | Host      | db   | Command | Time  | State                                                                       | Info             |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
| 10 | system user |           | NULL | Connect | 57345 | Waiting for master to send event                                            | NULL             |
| 11 | system user |           | NULL | Connect | 35096 | Slave has read all relay log; waiting for the slave I/O thread to update it | NULL             |
| 16 | root        | localhost | NULL | Query   |     0 | NULL                                                                        | show processlist |
+----+-------------+-----------+------+---------+-------+-----------------------------------------------------------------------------+------------------+
3 rows in set (0.00 sec)

指定排除的库和同步的库(可以通过主库不记录binlog 和 从库忽略同步的数据库两种方法来控制),
binlog_do_db         # 设定哪些数据库需要记录Binlog;
binlog_ignore_db     # 设定哪些数据库不要记录Binlog;

# 多个写法,不支持 binlog-do-db = linyaohong,hong [5.5.62版本测试结果]
binlog-do-db = linyaohong
binlog-do-db = hong

mysql> show master status; # 设定忽略那些数据不记binlog
+------------------+----------+-----------------+------------------+
| File             | Position | Binlog_Do_DB    | Binlog_Ignore_DB |
+------------------+----------+-----------------+------------------+
| mysql-bin.000001 |      107 | linyaohong,hong |                  |
+------------------+----------+-----------------+------------------+
1 row in set (0.00 sec)

# 从库上设定需要复制的数据库
# 测试版本5.5.62
replicate-do-db=linyaohong
replicate-do-db=yaohong

# 从库上设定可以忽略的数据库
# 测试版本5.5.62
replicate-ignore-db=hong
replicate-ignore-db=mysql
replicate-ignore-db=yaohong

# 同理忽略不同步的表
replicate_wild_do_table=mysql.table01  # 一个

replicate_wild_do_table=mysql.%        # 如果使用同步库,而不是用use db; 会出现主从不一直的情况(推荐这种写法)
replicate_wild_ignore_table=mysql.%

# 可以通过show slave status\G 来查看状态

# mysql5.5.62 测试总结:
replicate-do-db=linyaohong,yaohong  # 这种写法测试不生效
replicate_wild_do_table = linyaohong.%,yaohong.% # 这种写法测试也不生效

从库设置只读权限
1. read-only                      # 配置从库不能写
2. 根据上面的方法配置
   binlog-ignore-db = mysql     # 主库设置不记录binlog也可以
   replicate-ignore-db = mysql  # 从库设置
3 .
    主库权限授权 grant SELECT,INSERT,UPDATE,DELETE,CREATE,DROP on myweb.* to 'linyaohong'@'10.10.10.%' IDENTIFIED BY  '123456';
    从库给数据库权限 grant SELECT on myweb.* to 'linyaohong'@'10.10.10.%' IDENTIFIED BY  '123456';

# 具有super权限的用户可以更新,不收read-only参数影响,
# 当来自服务器线程可以更新,不受read-only参数影响,例如repl同步用户

当主从复制出现错误的时候解决办法
错误代码:   
https://mirrors.linyaohong.com/conf/mysql/MySQL_master_slave_error.txt
# 当主从复制出现错误的时候解决办法
stop slave;  # 临时停止同步开关 或者 stop slave io_therad; 
set global sql_slave_skip_counnter =1 # 将同步指针向下移动一个,如果多次不同步,可以重复执行(需要看错误是否可以跳过)
start slave;  # 开启同步开关  start slave io_therad;

其他可能引起故障的原因
MYSQL自身原因,以及人为重复插入数据
不同数据库版本引起的不同步,低版本到高版本可以,但是高版本不能往低版本同步
MYSQL的运行错误和程序BUG
binlog记录模式, 例如:row level模式比默认的MIXED要好

主从延迟问题和解决
1.从库太多,建议3-5个
2.从库硬件比主库查,导致主从复制延迟
3.慢SQL语句过多,
4.主从复制单线程,[更换mysql 5.7]
5.网络延迟问题
6.主库读写压力大,建议前端加缓存

半同步复制(mysql5.5以后的版本自带这个插件)

#-------------------------主库--------------------------------
# 查看半同步插件的位置
mysql -uroot -p111111 -S /data/3306/mysql.sock -e "show variables like 'plugin_dir';"  
[root@test01 ~]# ls -l /application/mysql/lib/plugin/semisync_*
install plugin rpl_semi_sync_master soname 'semisync_master.so';  # 安装主库
mysql> show plugins;  # 安装完毕检查
mysql> set global rpl_semi_sync_master_timeout = 10000 # 1秒
mysql> SHOW VARIABLES LIKE 'rpl_semi_sync%';
+------------------------------------+-------+
| Variable_name                      | Value |
+------------------------------------+-------+
| rpl_semi_sync_master_enabled       | OFF   |
| rpl_semi_sync_master_timeout       | 10000 | # 超时时间
| rpl_semi_sync_master_trace_level   | 32    |
| rpl_semi_sync_master_wait_no_slave | ON    |
+------------------------------------+-------+
6 rows in set (0.00 sec)

开启半同开关,master端开启
mysql> SET GLOBAL rpl_semi_sync_master_enabled = 1;

#-------------------------从库--------------------------------
install plugin rpl_semi_sync_slave soname 'semisync_slave.so';    # 安装
stop start slave;
start slave IO_THREAD;start slave IO_THREAD;  # 从库线程重启(一定要重启)
show status like 'Rpl_semi_sync%';            # 从库查看状态

# 测试对比当主库插入数据,从库不正常的情况

mysql>  show status like 'Rpl_semi_sync%';
+--------------------------------------------+-------+    
| Variable_name                              | Value |    
+--------------------------------------------+-------+    
| Rpl_semi_sync_master_clients               | 0     |    # 记录支持半同步的slave的个数
| Rpl_semi_sync_master_net_avg_wait_time     | 235   |    # master 等待slave回复的平均等待时间,单位微秒
| Rpl_semi_sync_master_net_wait_time         | 3763  |    # master 总的等待时间
| Rpl_semi_sync_master_net_waits             | 16    |    # master 等待slave回复的的总的等待次数
| Rpl_semi_sync_master_no_times              | 2     |    # master 关闭半同步复制的次数
| Rpl_semi_sync_master_no_tx                 | 67    |    # master 没有收到slave的回复而提交的次数
| Rpl_semi_sync_master_status                | OFF   |    # 标记master现在是否是半同步复制状态
| Rpl_semi_sync_master_timefunc_failures     | 0     |    # 时间函数未正常工作的次数
| Rpl_semi_sync_master_tx_avg_wait_time      | 583   |    # 开启Semi-sync,事务返回需要等待的平均时间
| Rpl_semi_sync_master_tx_wait_time          | 8745  |    # 事务等待备库响应的总时间
| Rpl_semi_sync_master_tx_waits              | 15    |    # 事务等待备库响应的总次数
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |    # 改变当前等待最小二进制日志的次数
| Rpl_semi_sync_master_wait_sessions         | 0     |    # 当前有多少个session 因为slave 的回复而造成等待
| Rpl_semi_sync_master_yes_tx                | 15    |    # master 成功接收到slave的回复的次数
+--------------------------------------------+-------+    
15 rows in set (0.00 sec)

# 测试对比当主库插入数据,从库正常的情况

mysql>  show status like 'Rpl_semi_sync%';
+--------------------------------------------+-------+
| Variable_name                              | Value |
+--------------------------------------------+-------+
| Rpl_semi_sync_master_clients               | 1     |
| Rpl_semi_sync_master_net_avg_wait_time     | 842   |
| Rpl_semi_sync_master_net_wait_time         | 14329 |
| Rpl_semi_sync_master_net_waits             | 17    |
| Rpl_semi_sync_master_no_times              | 2     |
| Rpl_semi_sync_master_no_tx                 | 67    |
| Rpl_semi_sync_master_status                | ON    |
| Rpl_semi_sync_master_timefunc_failures     | 0     |
| Rpl_semi_sync_master_tx_avg_wait_time      | 583   |
| Rpl_semi_sync_master_tx_wait_time          | 8745  |
| Rpl_semi_sync_master_tx_waits              | 15    |
| Rpl_semi_sync_master_wait_pos_backtraverse | 0     |
| Rpl_semi_sync_master_wait_sessions         | 0     |
| Rpl_semi_sync_master_yes_tx                | 15    |
+--------------------------------------------+-------+
15 rows in set (0.00 sec)



温馨提示如有转载或引用以上内容之必要,敬请将本文链接作为出处标注,如有侵权我会在24小时之内删除!

欢迎使用手机扫描访问本站