阿里云-云小站(无限量代金券发放中)
【腾讯云】云服务器、云数据库、COS、CDN、短信等热卖云产品特惠抢购

MySQL主从复制详解

211次阅读
没有评论

共计 7902 个字符,预计需要花费 20 分钟才能阅读完成。

本文涉及 MySQL 主从复制原理及 1 个简单是主从复制验证。

一.MySQL 主从复制原理

1. 主从复制架构图

MySQL 主从复制详解

主从复制中的三个线程:

  1. Binlog Dump 线程:此线程运行在主库,当主从都配置好后,从库运行 START SLAVE 启动复制后,会在主库上生成一个 BinlogDump 线程,该线程的主要作用就是读取主库 Binlog 事件,然后发送到从库(从库的 I / O 线程)。
  2. I/ O 线程:此线程运行在从库,作用是向主数据库要数据,并且将主库发送过来的变更事件写入到从库的中继日志中。
  3. SQL 线程:此线程运行在从库,主要作用是读取中继日志中的变更事件并更新从库。

2. 主从复制流程

大致流程:主库在事务提交时会把变更作为事件记录(Events)到二进制文件(binlog)当中,从库的 IO 线程从主库获取二进制日志(binlog),并在本地保存为中继日志(relay-log),然后通过 SQL 线程来在从库上执行中继日志中的内容,使从库和主库保持一致。

详细流程如下:

  1. 主库验证从库发起的连接;
  2. 主库为从库开启一个线程;
  3. 从库将主库日志的偏移位告诉主库;
  4. 主库检查该值是否小于当前二进制日志偏移位。
  5. 如果小于,则通知从库可以取数据。
  6. 从库持续从主库取数据,直至取完,这时,从库线程进入睡眠,主库线程同时进入睡眠。
  7. 当主库有更新时,主库线程被激活,并将二进制日志推送给从库,并通知从库线程进入工作状态。
  8. 从库 SQL 线程执行二进制日志,随后进入睡眠状态。

二.验证环境

1. 操作系统

CentOS-6.7-x86_64

2. MySQL 版本

MySQL 版本是 5.6.36: https://cdn.mysql.com//Downloads/MySQL-5.6/mysql-5.6.36.tar.gz

3. 拓扑图

MySQL 主从复制详解

  1. 采用 VMware ESXi 虚拟出的 2 台服务器 master/slave,地址 10.11.4.196/198;
  2. MySQL 已安装并配置完成,可参考 http://www.linuxidc.com/Linux/2017-10/147551.htm 中的 MySQL 部分;

三.主库配置

1. my.cnf 配置

[root@master ~]# vim /etc/my.cnf
[mysqld]
# ID 值唯一的标识了群集中的主从服务器,master_id 必须为 1 到 232–1 之间的一个正整数值,slave_id 值必须为 2 到 232–1 之间的一个正整数值.
server_id = 196

# 启用 binlog, 启用后才可通过 I / O 写到 Slave 的 relay-log, 是进行 replication 的前提.
log_bin = /mysql/mysql-bin

# 设置 binlog 文件的最大值,当达到这个值会自动生成一个新的 binlog 文件.
max_binlog_size = 1G

# binlog 会占据大量磁盘空间, 可以设置 binlog 文件过期时间, 以天为单位.
# expire-logs-days =

# 配置每次事务提交时是否都需要刷新 binlog 到磁盘, 默认配置 0, 是由文件系统自己控制; 如果设置为 1,默认每次事务提交都会刷新 binlog 到磁盘.
# 因为 binlog 也有缓存,事务提交时先写缓存, 如果系统突然宕机, 可能会丢失缓存中的记录; 但每次事务提交都写磁盘会对性能造成影响.
# 通过半同步复制可以解决因系统突然宕机而导致 binlog 缓存数据丢失的问题.
sync_binlog = 0

# 二进制日志记录有三种方式:row, STATEMENT and mixed
# row, 基于行的复制, 记录每一行的变更操作, 优点: 对复制的兼容性高, 缺点: 日志记录量大,对 IO 的影响也很大, 不容易用来做分析;
# STATEMENT, 基于语句的复制, 记录操作的 sql 语句, 是默认的格式, 优点: 日志量小, 便于用来做分析,IO 影响小, 缺点: 时间上可能不完全同步造成偏差,执行语句的用户也可能是不同一个用户;
# mixed, 混合模式, 默认采用 STATEMENT 记录, 当出现不确定函数时就采取 row 记录.
binlog-format = mixed

# 只将指定数据库的变动写入二进制文件 binlog, 如果有多个数据库可用逗号分隔, 或者使用多个 binlog-do-db 选项;
# 在 databases 中无相应数据库时, 开启 binlog-do-db 会导致 mysql 无法启动.
# binglog-do-db = 
# 对指定数据库的变动不写入二进制文件 binlog, 如果有多个数据库可用逗号分隔, 或者使用多个 binlog-ignore-db 选项.
binlog-ignore-db = information_schema,mysql,performance_schema,test
# 指定需要复制同步的数据库,如果有多个数据库可用逗号分隔,或者使用多个 replicate-do-db 选项
# replicate-do-db = 
# 指定不需要复制同步的数据库, 如果有多个数据库可用逗号分隔, 或者使用多个 replicate-ignore-db 选项.
# replicate-ignore-db = 

#配置文件在重启后生效
[root@master ~]# service mysqld restart

2. 创建复制用户

#在主库上为 10.11.4.0 网段的主机授权,从库用户 repl 获得 REPLICATION SLAVE 权限
[root@master ~]#mysql -uroot -p
Enter password:

mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'10.11.4.%' IDENTIFIED  BY 'repl';
mysql> flush privileges;

3. 刷新表并设置只读

#锁表,禁止新数据写入;
#(视情况执行),设置只读是为了防止在获取 binlog 文件名与偏移量时,或从主库备份数据库到从库时,主库发生变化;初次同步完成后勿忘解锁
[root@master ~]#mysql -uroot -p
Enter password:

mysql>flush tables with read lock;

4. 获取 master binlog 文件名与偏移量

#获取到 binlog 文件名与偏移量,可为从库设定同步复制点
[root@master ~]# mysql -uroot -p
Enter password:

mysql>show master status;

MySQL 主从复制详解

5. 解除只读锁定

#解除只读锁定
[root@master ~]# mysql -uroot -p
Enter password:

mysql>unlock tables;

6. iptables

[root@master ~]# vim /etc/sysconfig/iptables
-A INPUT -m state --state NEW -m tcp -p tcp --dport 3306 -j ACCEPT

[root@master ~]# service iptables restart

四.从库配置

1. my.cnf 配置

#将主库服务器上的 my.cnf 文件拷贝到从库服务器
[root@master ~]# scp /etc/my.cnf slave:/etc/
Enter password:

#修改 server id 值与主库服务器不同,其余配置保持不变即可;
#从库可以配置 slave_skip_errors=[err_code1,err_code2,… | all]参数;
#在复制过程,由于各种原因导致 binlog 中的 sql 出错,默认情况下从库会停止复制,要用户介入;
#可以通过设置 slave-skip-errors 来定义错误号,如果复制过程中遇到已定义的错误号,便可以跳过;如果从库是用来做备份,设置这个参数会存在数据不一致,建议不使用;如果是从库是分担主库的查询压力,可以考虑采用。
[root@slave ~]# vim /etc/my.cnf
[mysqld]
# ID 值唯一的标识了群集中的主从服务器,master_id 必须为 1 到 232–1 之间的一个正整数值,slave_id 值必须为 2 到 232–1 之间的一个正整数值.
server_id = 198


#使用 --skip-slave-start 启动,可以不立即启动从库的复制线程,方便后续配置操作
[root@slave ~]# service mysqld stop
[root@slave ~]# mysqld_safe --skip-slave-start &

2. 配置同步

#配置从库向主库提交的参数,如果参数有错误,可以重新配置;
#master-host、master-user、master-password、master-port 等也可在 my.cnf 文件中指定;
#”start slave”启动复制。
[root@slave ~]# mysql -uroot -p
Enter password:

mysql> change master to 
       master_host = '10.11.4.196', 
       master_user = 'repl', 
       master_password = 'repl', 
       master_log_file = 'mysql-bin.000001',
       master_log_pos = 120;
mysql> start slave;

3. 设置从库只读

#主从复制框架基本完成,但从库还可以进行数据的写入操作;
#如果有用户向从库中写数据,然后从库在从主库同步数据库时,会造成数据错乱,从而造成数据损坏,所以需要把从库设置成只读
[root@slave ~]# mysql -uroot -p
Enter password:

mysql> set global read_only=1;
mysql> show global variables like 'read%';

MySQL 主从复制详解

  1. read-only = OFF/ON,全局变量,只有管理员具有修改权限;
  2. read-only = ON,此功能只对非管理员组用户有效;
  3. 通过命令设置的 read-only 在服务重启后消失,可以在 my.cnf 文件中设置永久生效。

五.验证

1. 查看线程

1)主库

[root@master ~]# mysql -uroot -p
Enter password:

mysql> show processlist\G;

MySQL 主从复制详解

  1. 主库的 binlog dump 线程已由从库的 repl 用户启动。

2)从库

[root@slave ~]# mysql -uroot -p
Enter password:

mysql> show processlist\G;

MySQL 主从复制详解

  1. 从库的 I / 0 线程与 SQL 线程已由系统用户启动;
  2. SQL 线程常见还有 1 个常见状态 ” Reading event from the relay log”。

2. 查看从库状态

[root@slave ~]# mysql -uroot -p
Enter password:

mysql> show slave status\G;

MySQL 主从复制详解

  1. 重点关注 Slave_IO_Running 与 Slave_SQL_Running,状态均为 YES 时正常。
#slave status 各指标意义如下(红色粗体指标比较重要):
Slave_IO_State: I/ O 线程已连接 master, 正等待二进制日志事件到达
Master_Host: master ip
Master_User: 连接 master 的用户
Master_Port: master 端口
Connect_Retry: 当重新建立主从连接时, 如果连接建立失败, 重试间隔时间,默认 60s。Master_Log_File: I/ O 线程当前正在读取的 master 二进制日志文件
Read_Master_Log_Pos: 在当前的 master 二进制日志中,I/ O 线程已读取到的位置
Relay_Log_File: SQL 线程当前正在读取和执行的中继日志文件的名称
Relay_Log_Pos: SQL 线程在当前的中继日志中已读取和执行的位置
Relay_Master_Log_File: SQL 线程执行的 master 二进制文件
Slave_IO_Running: I/ O 线程是否运行并成功地连接到 master
Slave_SQL_Running: SQL 线程是否运行
Replicate_Do_DB: 需要复制的数据库
Replicate_Ignore_DB: 不需要复制的数据库
Replicate_Do_Table: 需要复制的表
Replicate_Ignore_Table: 不需要复制的表
Replicate_Wild_Do_Table: 限制复制更新的表,匹配指定的数据库和表名模式的语句
Replicate_Wild_Ignore_Table: 不需要要复制表,匹配给出的通配符模式的语句
Last_Errno: 错误代码
Last_Error: 错误信息
Skip_Counter: SQL_SLAVE_SKIP_COUNTER 的值
Exec_Master_Log_Pos: master 上一个被执行的位置
Relay_Log_Space: 中继日志文件大小
Until_Condition: 在 START SLAVE 语句的 UNTIL 子句中指定的值
Until_Log_File: 用于指示日志文件名
Until_Log_Pos: 位置值
Master_SSL_Allowed: 如果允许对主服务器进行 SSL 连接, 则值为 Yes, 否则 NO
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: slave 落后 master 多少的一个指标. 此状态是一个很重要的性能指标, 正常为 0, 如果 slave 的 I / O 线程无法连接 master 显示 null
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 最近的 IO 线程错误代码,其中 2003 代表 I / o 线程无法连接主服务器
Last_IO_Error: 最近的 IO 线程错误信息(例如:error reconnecting to master 'repl@192.168.1.6:3306' - retry-time: 60  retries: 3)Last_SQL_Errno: 最近的 SQL 线程错误代码
Last_SQL_Error: 最近的 SQL 线程错误信息
Replicate_Ignore_Server_Ids:
Master_Server_Id: master 的服务器 ID
Master_UUID: master 的 UUID 值
Master_Info_File: slave 的 master.info 文件路径
SQL_Delay: 正数表明 slave 有延迟了
SQL_Remaining_Delay: 整数表明延迟时间
Slave_SQL_Running_State: SQL 线程运行状态,SQL 线程已经处理了中继日志文件中的所有事件,现在正等待 I / O 线程将新事件写入中继日志
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp: 最近的 I / O 线程错误时间
Last_SQL_Error_Timestamp: 最近的 SQL 线程报错时间
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0

3. 从库中的 3 个文件

在从库的数据库路径下会生成 3 个文件:master.info,relay-log.info,relay-bin

master.info: 记录 master 的 ip,账号,密码,以及从库的 I / O 线程当前读取到主库的 binglog 的位置。

relay-log.info:记录从库的 SQL 线程当前读取到中继日志 (relay-bin) 的位置。

relay-bin: 中继日志, 记录的格式与主库的二进制日志一样,但中继日志在 SQL 线程执行完当前中继日志中的事件之后会删除中继日志中的内容。

4. 查看新建数据数据库同步情况

1)主库创建数据库与表

[root@master ~]# mysql -uroot -p
Enter password:

mysql> create database dbtest;
mysql> use dbtest;
mysql> create table tabtest(id int);
mysql> insert into tabtest() values(1),(2);

MySQL 主从复制详解

2)从库查看数据库与表

[root@slave ~]# mysql -uroot -p

Enter password:

(1)查看数据库

mysql> show databases;

MySQL 主从复制详解

(2)查询表

mysql> select * from dbtest.tabtest;

MySQL 主从复制详解

(3)slave 状态

mysql> show slave status\G;

MySQL 主从复制详解

  1. 同步复制的 binlog 偏移量已变更,relaylog 偏移量也变更。

六.补充(不做验证): 半同步复制

从 MySQL5.5 开始,MySQL 以插件的形式支持半同步复制。

可参考:

https://dev.mysql.com/doc/refman/5.6/en/replication-semisync-interface.html

1. 概念

1)异步复制(Asynchronous replication)

MySQL 默认的复制即是异步的,主库在执行完客户端提交的事务后会立即将结果返给给客户端,并不关心从库是否已经接收并处理。这时主如果 crash 掉了,主上已经提交的事务可能并没有传到从上,如果此时,强行将从提升为主,可能导致新主上的数据不完整。

2)全同步复制(Fully synchronous replication)

当主库执行完一个事务,所有的从库都执行了该事务才返回给客户端。因为需要等待所有从库执行完该事务才能返回,所以全同步复制的性能会受到到严重的影响。

3)半同步复制(Semisynchronous replication)

介于异步复制和全同步复制之间,主库在执行完客户端提交的事务后不是立刻返回给客户端,而是 等待至少一个从库接收到并写到 relay log 中才返回给客户端 。相对于异步复制,半同步复制提高了数据的安全性,同时它也造成了一定程度的延迟,这个延迟最少是一个 TCP/IP 往返的时间。所以, 推荐半同步复制在小事务,低延时的网络中使用,可以实现在性能很小损失的情况下的零数据丢失

2. 注意事项

  1. 半同步复制需要安装相应插件支持,主从的插件不同;
  2. 半同步复制一定程度上保证提交的事务已经传给了至少一个备库(可通过环境变量设置);
  3. 半同步复制仅保证事务已经传递到备库上,并不确保已经在备库上执行完成了,即从库的执行同步过来的 binlog 是异步的;
  4. 半同步复制并不是严格意义上的半同步复制。如果半同步复制发生超时(由 rpl_semi_sync_master_timeout 参数控制,单位是毫秒,默认为 10000,即 10s),会暂时关闭半同步复制,转而使用异步复制;当 master dump 线程发送完一个事务的所有事件之后,如果在 rpl_semi_sync_master_timeout 内,收到了从库的响应,则主从又重新恢复为半同步复制;
  5. 5.6 版本是在主库 flush disk 之后发送 binlog;5.7 版本加入新参数 ”rpl_semi_sync_master_wait_point=AFTER_SYNC“,控制主库响应从库的 binlog 请求之后再 flush disk,如果将参数修改为参数 ”rpl_semi_sync_master_wait_point=AFTER_COMMIT“,则效果同 5.6 版本。

本文永久更新链接地址:http://www.linuxidc.com/Linux/2017-10/147550.htm

正文完
星哥玩云-微信公众号
post-qrcode
 0
星锅
版权声明:本站原创文章,由 星锅 于2022-01-22发表,共计7902字。
转载说明:除特殊说明外本站文章皆由CC-4.0协议发布,转载请注明出处。
【腾讯云】推广者专属福利,新客户无门槛领取总价值高达2860元代金券,每种代金券限量500张,先到先得。
阿里云-最新活动爆款每日限量供应
评论(没有评论)
验证码
【腾讯云】云服务器、云数据库、COS、CDN、短信等云产品特惠热卖中