MySQL主从复制

最新推荐文章于 2024-11-15 18:38:47 发布
最新推荐文章于 2024-11-15 18:38:47 发布
阅读量90 收藏
点赞数
文章标签： 数据库 开发工具 运维
原文链接：http://www.cnblogs.com/netonline/p/7603578.html
版权

原文发表于cu：2017-06-09

参考文档：

  1. 主从复制原理：https://segmentfault.com/a/1190000008942618

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

一．MySQL主从复制原理

1. 主从复制架构图

主从复制中的三个线程:

  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. 拓扑图

  1. 采用VMware ESXi虚拟出的2台服务器master/slave，地址10.11.4.196/198；
  2. MySQL已安装并配置完成，可参考http://www.cnblogs.com/netonline/p/7327409.html中的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;

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%';

  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;

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

2）从库

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

mysql> show processlist\G;

  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;

  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);

2）从库查看数据库与表

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

Enter password:
(1)查看数据库 
mysql> show databases;

(2)查询表 
mysql> select * from dbtest.tabtest;

(3)slave状态
mysql> show slave status\G;

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

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

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

可参考：

http://www.cnblogs.com/chenmh/p/5744227.html

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版本。

转载于:https://www.cnblogs.com/netonline/p/7603578.html

mysql数据库账号导出_Mysql 数据库导入及导出
weixin_35297190的博客
01-28 997
Mysql 数据库导入及导出数据库导出：1.导出整个数据库mysqldump -u 用户名 -p 数据库名 > 导出的文件名mysqldump -u root -p dataname >dataname.sql这个时候会提示要你输入root用户名的密码,输入密码后dataname数据库就成功备份在mysql/bin/目录中.2.导出一个表mysqldump -u 用户名 -p 数据库名...
MySQL数据库备份之复制
weixin_33827590的博客
03-11 168
MySQL数据库备份之复制目录:1.MySQL复制简介2.MySQL复制的优势与思路3.数据复制环境之主服务器设置4.数据复制环境之从服务器设置5.创建复制帐号6.获取主服务器二进制日志信息7.对现有数据库进行快照备份8.配置从服务器连接主服务器进行数据复制9.数据同步验证1. MySQL复制使用MySQL复制功能可以将主服务器上的数据复制到多台从服务器上。默认情况下复制是异步传输方式，从服务器不...
mysql数据库备份复制相关
ceiling的博客
04-26 302
1、MyISAM引擎备份加读锁 mysqldump -A -F -B --lock-all-tables | gzip /root/mysql$(date +%F).sql.gz 2、InnoDN引擎备份加行锁 mysqldump -A -F -B --single-transaction | gzip /root/mysql$(date +%F).sql.gz 3、删除二进制文件bin-
MYSQL主从复制和读写分离
weixin_34205076的博客
08-13 122
Mysql复制特点（异步复制）：主节点中一个用户请求一个写操作时，主节点不需要把写的数据在本地操作完成同时发送给从服务器并等待从服务器反馈写入完成再响应用户。主节点只需要把写入操作在本地完成，就响应用户。但是，从节点中的数据有可能会落后主服务，可以使用很多软件来检查主从数据是否一致。复制原理：Mysql中有一种日志叫做binlog（二进制日志）。这个日志会记录下所有修改了数据...
数据库的主从复制原理（超级详细）
王海翔的博客
03-31 7970
概念 MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表 主要用途 1. 读写分离 在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。
mysql主从
只是个人笔记，请见谅
06-19 235
mysql主从 1.主从复制原理 主从复制步骤： 主库将所有的写操作记录到binlog日志中并生成一个log dump线程，将binlog日志传给从库的I/O线程 从库生成两个线程，一个I/O线程，一个SQL线程 I/O线程去请求主库的binlog，并将得到的binlog日志写到relay log（中继日志） 文件中 SQL线程，会读取relay log文件中的日志，并解析成具体操作，来实现主从的操作一致，达到最终数据一致的目的 主 将写操作记录到binlog日志 生成log dump线程 从 有
Mysql主从复制
xiaogengtongxu的博客
07-04 839
这意味着当主服务器或从服务器端发生故障时，有可能从服务器没有接收到主服务器发送过来的binlog日志，这就会造成主服务器和从服务器的数据不一致，甚至在恢复时造成数据的丢失。在从库发现某几张表与主库数据不一致，而这几张表数据量也比较大，手工比对数据不现实，并且重做整个库也比较慢，这个时候可以只重做这几张表来修复主从不一致这种方案缺点是在执行导入期间需要暂时停止从库复制，不过也是可以接受的。read_only的含义是只可以读，不能修改数据库，主要目的是，避免出现从被修改，主的数据不能同步，造成主从复制失败。
docker-compose关于mysql主从复制一键搭建脚本
09-18
关于博客: https://blog.csdn.net/qq_42413011/article/details/126914080 的搭建脚本
使用keepalived实现对mysql主从复制的主备自动切换.docx
01-09
"使用keepalived实现对mysql主从复制的主备自动切换" 本文主要讲解如何使用keepalived实现对mysql主从复制的主备自动切换。keepalived是一款高可用性解决方案，可以实现虚拟IP的管理和服务监控，在mysql主从复制...
SpringBoot第 12 讲：SpringBoot+MySQL主从复制、读写分离
10-10
在本讲中，我们将深入探讨如何使用SpringBoot与MySQL实现主从复制以及读写分离的架构设计。这一技术方案在大型分布式系统中尤为常见，它能够有效地提高数据库系统的可用性和性能。 首先，让我们理解主从复制的核心...
mysql copy pending_实战丨MySQL 复制一致性问题探索
weixin_36362326的博客
01-21 386
原标题：实战丨MySQL 复制一致性问题探索文 / 华夏银行信息科技部  胡捷 王辉 陈伟MySQL 数据库内建的复制功能是构建基于 MySQL 的大规模、高性能应用的基础。复制就是让一个 MySQL主库 (Master) 将数据通过日志的方式经网络传送到另一台或多台 MySQL 从库(Slave)，然后在从库上重放该日志，以达到和主库数据同步的目的。MySQL 的复制模式分为异步复制、全同步复制...
MySQL主从复制——主库已有数据的解决方案
weixin_34390996的博客
07-30 886
在上篇文章中我们介绍了基于Docker的Mysql主从搭建，一主多从的搭建过程就是重复了一主一从的从库配置过程，需要注意的是，要保证主从库my.cnf中server-id的唯一性。搭建完成后，可以在主库show slave hosts查看有哪些从库节点。 我们来简单了解一下Mysql主从复制的过程： (1) master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件...
Mybatis
最新发布
庸不易的博客
11-15 790
1、定义不同：#{} 预处理：而 ${} 是直接替换2、使用不同：#{} 适用于所有类型的参数匹配；但 ${} 只适用于数值类型。3、安全性不同：#{} 性能高，并且没有安全问题；但 $ {} 存在 SQL 注入的安全问题。4、使用场景不同：当传递的是一个 SQL 关键字 的时候，只能 使用 ${}。当传递的是一个字段，总之，就是需要获取到参数类型 与 内容，只能使用 #{}。（PS：数字类型，#{} 和 ${} 都是可以使用的！5、 ${} 不能用于 模糊匹配查询；
MySQL 8.0特性-自增变量的持久化
myneth的博客
11-13 537
在MySQL 8.0之前，自增主键AUTO_INCREMENT的值如果大于max(primary key)+1，在MySQL重启后，会重置AUTO_INCREMENT=max(primary key)+1，这种现象在某些情况下会导致业务主键冲突或者其他难以发现的问题。下面通过案例来对比不同的版本中自增变量是否持久化。
约束（MYSQL）
2301_80363309的博客
11-13 321
not null（非空） unique（唯一） default（默认约束，规定值） 主键约束primary key（非空且唯一） auto_increment（自增类型） 复合主键 check（保证列中值符合指定条件） foreign key（外键约束）
labview中连接sql server数据库查询语句
weixin_70653117的博客
11-13 388
日常记录
gbase8s数据库常见的连接算法
wangyx01的博客
11-13 219
外表指针指向第一条数据，内表指针顺序移动，找到和外表匹配项，记录，内表循环结束后外边指针继续向下移动，一直到外表循环结束为止。hash 连接中只有极少量的随机读，大部分都为顺序读，极大缓解了大量随机读的压力。有则取捅中进行匹配（若捅中有多条数据则发生hash碰撞），没有则继续匹配下一。hash 连接避免了嵌套循环的大量的随机读，简单伪代码如下。在gbase8s 中 ，常见的连接算法主要分为如下几种。细分可分为索引嵌套循环，块嵌套循环，半嵌套循环等。嵌套循环的缺点，在大量数据下会发生大量的随机读。
MySQL主从复制配置教程
"mysql主从复制安装配置进阶.pptx" MySQL主从复制是一种常见的数据库高可用性和负载均衡方案，它允许数据从一个主服务器（Master）实时同步到一个或多个从服务器（Slave）。这种配置有助于实现数据冗余，提高系统的...
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值