帝国模板之家为广大用户提供专业、一站式建站服务。

网站开发

网站开发栏目,介绍前端、后台、服务器、数据库、建站工具等实用网站开发教程。

当前位置:首页 > 教程 > 网站开发 > 正文

MYSQL数据库主从复制原理和配置

帝国模板之家 2021-03-31 网站开发 评论

原理:

MySQL 主从复制是一个异步的复制过程,主库发送更新事件到从库,从库读取更新记录,并执行更新记录,使得从库的内容与主库保持一致。每一个主从复制的连接,都有三个线程。拥有多个从库的主库为每一个连接到主库的从库创建一个 log dump 输出线程,每一个从库都有它自己的 I/O 线程和 SQL 线程。

步骤:

1.主库会将所有的更新记录保存到 Binarylog 文件。

2.每当有从库连接到主库的时候,主库都会创建一个 log dump 线程发送 Binarylog 文件到从库。

3.当从库复制开始的时候,从库就会创建两个线程进行处理,一个 I/O 线程,一个 SQL 线程。

4.I/O 线程去请求主库的 Binarylog文件,并将得到的 Binarylog 文件写到 Relaylog 文件中。

5.SQL 线程会读取 Relaylog 文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致。

配置

binlog简介

binlog是一个二进制格式的文件,用于记录用户对数据库增量操作的SQL语句信息,例如更改数据库表和更改内容的SQL语句都会记录到binlog里,但是对库表等内容的查询不会记录。

默认情况下,binlog日志是二进制格式的,不能使用查看文本工具的命令(比如,cat,vi等)查看,而使用mysqlbinlog解析查看

mysqlbinlog --base64-output=decode-rows -v /var/lib/mysql/mysql-bin.000001

log_bin                 = /var/lib/mysql/mysql-bin.log   #默认路径可修改
expire_logs_days        = 7                              #日志过期时间,设置为0则永不过期
binlog_format           = ROW          #复制模式
max_binlog_size         = 100M         #超过max_binlog_size或超过6小时会切换到下一序号文件
 
binlog_cache_size       = 16M           
#二进制日志缓冲大小,通过show status like 'binlog_%';查看调整写入磁盘的次数,写入磁盘为0最好
 
max_binlog_cache_size   = 256M
 
relay_log_recovery      = 1            
#当slave从库宕机后,假如relay-log损坏了,
#导致一部分中继日志没有处理,则自动放弃所有未执行的relay-log,
#并且重新从master上获取日志,这样就保证了relay-log的完整性。
 
sync_binlog             = 1            #二进制日志(binary log)同步到磁盘的频率
innodb_flush_log_at_trx_commit = 1     #每次事务提交将日志缓冲区写入log file,并同时flush到磁盘。

以下2个参数可以减少网络问题导致的主从数据同步延迟

slave_net_timeout       = 5       #当slave从主数据库读取log数据失败后,等待多久重新建立连接并获取数据,默认3600秒
 
 
#此选项为从库 change master to 的参数
master-connect-retry    = 60      #当重新建立主从连接时,如果连接建立失败,间隔多久后重试

MySQL binlog的三种工作模式

Statement level(默认)
每一条被修改数据的sql都会记录到master的bin-log中,slave在复制的时候sql进程会解析成和原来master端执行过的相同的sql再次执行

优点:不需要记录每一行的数据变化,减少bin-log日志量,节约磁盘IO,提高新能

缺点:容易出现主从复制不一致

BEGIN
/*!*/;
# at 503
# at 535
#190629 22:44:03 server id 2  end_log_pos 535 CRC32 0x5f886e71     Intvar
SET INSERT_ID=1/*!*/;
#190629 22:44:03 server id 2  end_log_pos 687 CRC32 0x0f3e188e     Query    thread_id=2    exec_time=0    error_code=0
SET TIMESTAMP=1561819443/*!*/;
insert into student (name,age) values
('tom',20),('alice',16),('helen',22)
/*!*/;
# at 687
#190629 22:44:03 server id 2  end_log_pos 718 CRC32 0x10c379a2     Xid = 25
COMMIT/*!*/;
Row level
日志中会记录每一行数据被修改的情况,然后在slave端对相同的数据进行修改。

优点:能清楚的记录每一行数据修改的细节

缺点:数据量太大

BEGIN
/*!*/;
# at 194
#190629 21:39:57 server id 2  end_log_pos 250 CRC32 0xa2b4d227     Table_map: `school`.`student` mapped to number 73
# at 250
#190629 21:39:57 server id 2  end_log_pos 328 CRC32 0x69783f01     Write_rows: table id 73 flags: STMT_END_F
### INSERT INTO `school`.`student`
### SET
###   @1=1
###   @2='tom'
###   @3=20
### INSERT INTO `school`.`student`
### SET
###   @1=2
###   @2='alice'
###   @3=16
### INSERT INTO `school`.`student`
### SET
###   @1=3
###   @2='helen'
###   @3=22
# at 328
#190629 21:39:57 server id 2  end_log_pos 359 CRC32 0x91fe958e     Xid = 50
COMMIT/*!*/;
Mixed(混合模式)
以上两种模式的混合使用,一般的复制使用STATEMENT模式保存binlog,对于STATEMENT模式无法复制的操作使用ROW模式保存binlog,MySQL会根据执行的SQL语句选择日志保存方式。

sync_binlog
sync_binlog=0
当事务提交之后,MySQL不做fsync之类的磁盘同步指令刷新binlog_cache中的信息到磁盘,而让Filesystem自行决定什么时候来做同步,或者cache满了之后才同步到磁盘。这个是性能最好的。

sync_binlog=1
当每进行1次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

sync_binlog=n
当每进行n次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。

注:大多数情况下,对数据的一致性并没有很严格的要求,所以并不会把 sync_binlog 配置成 1. 为了追求高并发,提升性能,可以设置为 100 或直接用 0,而对于支付服务这样的应用,还是比较推荐 sync_binlog = 1.

innodb_flush_log_at_trx_commit
innodb_flush_log_at_trx_commit=0
设置为0,该模式速度最快,但不太安全,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。log buffer将每秒一次地写入log file中,并且log file的flush(刷到磁盘)操作同时进行。该模式下在事务提交的时候,不会主动触发写入磁盘的操作。

innodb_flush_log_at_trx_commit=1
当设置为1,该模式是最安全的,但也是最慢的一种方式。在mysqld 服务崩溃或者服务器主机crash的情况下,binary log 只有可能丢失最多一个语句或者一个事务。每次事务提交时MySQL都会把log buffer的数据写入log file,并且flush(刷到磁盘)中去,该模式为系统默认。

innodb_flush_log_at_trx_commit=2
当设置为2,该模式速度较快,也比0安全,只有在操作系统崩溃或者系统断电的情况下,上一秒钟所有事务数据才可能丢失。每次事务提交时MySQL都会把log buffer的数据写入log file,但是flush(刷到磁盘)操作并不会同时进行。该模式下,MySQL会每秒执行一次 flush(刷到磁盘)操作。
这世上无忧无虑的人,反而相对寿命会长点。

已有 位小伙伴发表了看法

欢迎 发表评论

  • 匿名发表

关于

帝国cms模板

网页插件

帝国模板之家 | www.moyouyouw.cn

本站所有模板/文章除标明原创外,均来自网络转载,版权归原作者所有,如果有侵犯到您的权益,请联系站长删除,谢谢! 寻找帝国cms模板首选帝国模板之家,用心做站,上线至今广受好评,值得信赖!

  • 交流群1:帝国cms交流群1
  • 交流群2:帝国cms交流群2
在线QQ 给我留言