一、MySQL 主从复制原理
1、简介
MySql自带有主从复制功能,该功能是构建大型应用、高性能应用的基础。这种机制是指:将某一台主机(master)上的MySQL数据复制到多台其他主机上(slave),并重新执行一遍来实现。复制过程中一个Mysql实例充当主库(master),其他Mysql实例充当分库(slave)。
2、复制架构图
主库将更新写入binlog(二进制日志),并维护文件的一个索引以跟踪日志循环。如图:
从库生成两个线程,一个I/O线程,一个SQL线程。I/O线程去请求主库的binlog,并将得到的binlog日志写到relay log(中继日志)文件中;
主库会生成一个log dump线程,用来给从库I/O线程传binlog;SQL线程会读取relay log文件中的日志,并解析成具体操作,来实现主从的操作一致,而最终数据一致。
3、MySQL支持的复制类型
3.1 基于语句的复制。在主服务器上执行的 SQL 语句,在从服务器上执行同样的语句。配置:binlog_format = 'STATEMENT';
3.2 基于行的复制。把改变的内容复制过去,而不是把命令在从服务器上执行一遍,从 MySQL 5.0开始支持。配置: binlog_format = 'ROW';
3.3 混合类型的复制。默认采用基于语句的复制,一旦发现基于语句的无法精确的复制时,就会采用基于行的复制。配置:binlog_format = 'MIXED';
4、解决的问题
数据分布
负载平衡
备份
高可用性和容错行
5、复制的常用拓扑结构
复制的拓扑结构有以下一些基本原则:
1> 每个Slave只能有一个Master
2>每个Slave只能有一个唯一的服务器ID
3>每个Master可以有很多Slave
4>如果设置了log_slave_updates,Slave可以是其他Slave的Master,从而扩散Master的更新。
MySQL不支持多主服务器复制,即一个Slave可以有多个Master。
常用拓扑结构:
5.1 一主多复制架构
由一个Master和多个Slave组成复制系统,Slave之间不通信。
在实际场景中,MySQL复制大部分都是一主多复制这种架构。在Master读取请求压力非常大的场景下,把大量对实时性要求不是特别高的读取通过负载均衡到多个从Slave上,降低主库的读取压力。在Master宕机时,可以把一个Slave切换为主库继续提供服务。
问题建议:
1> 当Slave增加到一定数量时,Slave对Master的负载及网络带宽会成为一个严重的问题。
2> 不同的Slave扮演不同的角色(例如使用不同的索引,或者不同的存储引擎)。
3> 用一个Slave作为备用Master,只进行复制。
4> 用一个远程Slave,用于灾难恢复。
5.2 多级复制架构
一主多从的架构能够解决大部分请求压力特别大的场景需求,但随着从库的增加,会影响到Master的I/O和网络压力,而使用多级复制架构就可以解决一主多从的这个问题。但同时要注意的是,多级复制场景下主库的数据是经历了多次才到达Salve,期间的延时也会比一主多从的复制要大。
问题建议:
1> 会根据层级的多少增加复制的延时。
2> 这种方案可以与第三方软件结合使用,如:Slave + LVS + Keepalived实现高可用
5.3 双主复制/Dual Master架构
如果写压力比较大,或者DBA做维护需要主从切换,通过双主复制/Dual Master架构可以避免重复搭主从库的麻烦。
问题建议:
1> 最大的问题就是更新冲突
2> 可以采用MySQL Cluster,以及将Cluster和Replication结合起来,可以建立强大的高性能数据平台。
二、MySQL主从配置
1、基础环境配置
数据库版本:mysql 5.1.73(Slave版本 >= Master版本)
IP地址:192.168.1.100 (Master)、192.168.1.101(Slave)
2、Master服务器配置
2.1(关掉新主库的只读属性)
mysql>set global read_only=0;
mysql>flush privileges;
2.2 开启读写属性
mysql>set global read_only=1;
mysql>flush privileges;
2.3 基于行的复制。把改变的内容复制过去,而不是把命令在从服务器上执行一遍,从 MySQL 5.0开始支持
mysql> SET SESSION binlog_format = 'ROW';
mysql> SET GLOBAL binlog_format = 'ROW';
2.4 在 Master 的数据库中建立一个复制账户,每个 Slave 使用该账户连接 Master 进行复制,需要 replication slave 和 replication client 权限,Master 的连接信息会存储在文本文件 master.info 文件中。(master.info文件在 Slave 的数据目录中)
说明:创建了一个用户名为 replication 的用户,密码为 123456 ,只允许在192.168.1.101 这个 Slave 上登录。
mysql>grant replication slave on replication clinet on *.* to 'replication'@'192.168.1.101(slave IP)' identified by '123456';
mysql>flush privileges;
2.5 修改Master MySQL的数据库配置文件,默认是/etc/f
# 开启二进制日志,并指定文件所在目录,并在服务器上创建目录和授权
log-bin=/var/log/mysql/master-bin
#增加配置
binlog_format=mixed
2.6 取消server-id注释,不能和Slave一致
server-id = 1
2.7 在[mysqld]下面添加
binlog-do-db=db_test #要同步的数据库,如果有多个,则需要多写几行。
binlog-ignore-db=db_ignore_test#不需要同步的数据库,如果多个,则需要多写几行。
2.8 重启MySQL服务
2.9 查看Master状态
show master status;
File字段和Position字段要注意,在配置Slave时,需要指定相关值。
File
Position
Binlog_Do_DB
Binlog_Ignore_DB
master-bin.000208
1562
db_test
db_ignore_test
3、Slave服务器配置
3.1 取消server-id注释,不能和Slave一致
server-id = 101
3.2 添加中继日志,创建日志目录,授权
relay-log=/var/log/mysql
3.3 重启mysql服务
3.4 查看中继日志状态
mysql>show globalvariables like '%relay%';
3.5 连接Master服务器
mysql>change master to master_host='192.168.1.100',master_user='replication',master_password='123456',master_log_file='master-bin.000208',master_log_pos=1562;
选项:
master_host:Master 服务器IP
master_user:Master 服务器授权用户,也就是 Master 前面创建的那个用户
master_password:Master 服务器授权用户对应的密码
master_log_file:Master binlog 文件名,对应查询Master服务器状态时,File字段
master_log_pos:Master binlog 文件中的 Postion 值,对应查询Master服务器状态时,Position字段
更多的选项可以看:/doc/refman/5.7/en/change-master-to.html
#其中master_log_file、master_log_pos和File、Position不对应会提示1263错误,如:Slave I/O: Got fatal error 1236 from master when reading data from binary log: 'Could not find first log file name in binary log index file', Internal MariaDB error code: 1236
解决方案是:在Master服务器上,查看Master状态,根据查看结果中的File字段和Position字段。在Slave中执行,
stop slave;
change master to master_log_file='master-bin.000208',master_log_pos=1562;
start slave;
3.6 查看主从状态
show slave status;
如果Last_SQL_Error没有错误提示以及Slave中的Exec_Master_Log_Pos值和Master中的show master status中的Position值一样,这样的话,MySQL主从复制应该是成功的。
4、测试
在Master数据库中执行sql语句操作,观察Slave是否同步,如果同步则说明配置成功。
5、注意
1> 主库和从库数据库名称必须相同
2> 主库和从库的复制可以精确到表,但是在需要改主库或者从库的数据库结构时需要立刻重启slave
3> 不能在MySQL配置文件里直接写入master的配置信息,需要用change master命令来完成。
4> 指定replicate_do_db必须在f里配置,不能用change master命令来完成;
5> 如果不及时清理,日积月累二进制日志文件可能会把磁盘空间占满,可以在配置文件里加上expire_logs_days=7,只保留最近7天的日志,建议当slave不再使用时,通过reset slave来取消relaylog;
6>写一个监控脚本,用来监控 Slave 中的两个"yes",如果只有一个"yes"或者零个,就表明主从有问题
收集的几个相关知识文章:
http://hzcsky./1560073/479476/
http://storysky./628458/259280
/code-snippet/3177/mysql-zhucong-library-clock-error-%EF%BC%9A-1062-Error-Duplicate-entry-1438019-for-key-PRIMARY-on-quer
/index.php/archives/3.html
http://369258./359258/1345239
/gomysql/p/3662264.html
http://tiany./513694/173526
/chenpingzhao/p/5060874.html