Mysql主从复制从0到1搭建
mysql主从复制介绍
- 主从复制是基于mysql binlog日志来实现的
- 当主库发生新的操作时都会记录新的binlog
- 从库获取主库的binlog来进行回放
- 主从复制的过程是异步的
主从复制的前提
- 2个或以上的数据库实例
- 主库需要开启二进制日志
- server_id要不同,区分不同的节点
- 主库需要建立专用的复制用户 (replication slave)
- 从库应该通过备份主库,恢复的方法进行"补课"
- 人为告诉从库一些复制信息(ip port user pass,二进制日志起点)
- 从库应该开启专门的复制线程
部署主从复制
- 检查配置文件主库是否开启二进制日志，两个节点是否配置节点id（建议主库小于从库）
View Code[mysqld] #数据路径 datadir=/home/database #socket文件位置 socket=/tmp/mysql.sock #软件安装目录 basedir=/home/mysql/ #服务器id号唯一 server_id=68 #运行端口 port=3306 #开启事务手动提交 autocommit=0 #开启二进制日志，设定二进制日志存储位置 log_bin=/home/mysql-bin #配置log_bin日志的模式 binlog_format=row #开启gtid模式进行记录 gtid-mode=on #开启gtid强一致性 enforce-gtid-consistency=true #开启慢日志记录 log-slave-updates=1 #设置慢日志的存放位置 slow_query_log_file=/home/slow.log #设定慢日志超时时间记录 long_query_time=1 #设置不走索引的语句也进行记录 log_queries_not_using_indexes=1 #定义错误日志的位置 log_error=/home/mysqlerror.log [mysql] #socket文件位置 socket=/tmp/mysql.sock #链接数据库显示数据库名 #\u 表示用户名, \h 表示主机名， \d 表示当前数据库 prompt="\\u@\\h:\\d>"
- 主库创建一个用来同步复制数据的用户
View Code
create user 'repl'@'%'identified by '123'; # 创建用户 grant replication slave on *.* to 'repl'@'%'; # 授权 #replication slave专属复制用户
- 将主库全备信息导入从库，如果是空数据库可省却此步
- 告知从库信息
View Code
#链接从库 #执行代码 CHANGE MASTER TO MASTER_HOST='xxx.xxx.xxx.xxx', # 指定主库ip地址 MASTER_USER='xxx', # 指定主库用于同步复制的用户名 MASTER_PASSWORD='xxx', # 指定主库用于同步复制的用户密码 MASTER_PORT=3306, # 指定主库的端口号 MASTER_LOG_FILE='mysql-bin.000005', # 指定导入主库的全备信息起始点二进制文件 MASTER_LOG_POS=750, # 指定导入主库的全备信息起始点位置 MASTER_AUTO_POSITION=1, # 如果使用gtid方式则添加此条，此处需要看情况进行设置 MASTER_CONNECT_RETRY=10; # 指定主服务器宕机或链接丢失情况下，从服务器线程重新尝试连接主服务器之前睡眠的秒数 # GTID 复制和普通复制的区别 （0）在主从复制环境中，主库发生过的事务，在全局都是由唯一GTID记录的，更方便Failover （1）额外功能参数（3个） （2）change master to 的时候不再需要binlog 文件名和position号,MASTER_AUTO_POSITION=1; （3）在复制过程中，从库不再依赖master.info文件，而是直接读取最后一个relaylog的 GTID号 （4） mysqldump备份时，默认会将备份中包含的事务操作，以以下方式 #### SET @@GLOBAL.GTID_PURGED='8c49d7ec-7e78-11e8-9638-000c29ca725d:1-11'; 告诉从库，我的备份中已经有以上事务，你就不用运行了，直接从下一个GTID开始请求binlog就行。
- 从库开启复制线程连接主库
View Code
start slave;
- 检查主从复制状态信息
View Code
show slave status \G #打印内容 # Slave_IO_Running: Yes # Slave_SQL_Running: Yes
主从复制原理
主从复制涉及到的文件
主库：binlog 二进制日志
从库：
relaylog 中继日志
master.info 主库信息文件
relaylog.info relaylog 应用的信息
主从复制涉及到的线程
主库：
Binlog_Dump Thread：DUMP_T
从库：
SLAVE_IO_THREAD：IO_T
SLAVE_SQL_THREAD：SQL_T
原理图：
从库执行change master to 命令(主库的连接信息+复制的起点)
从库会将以上信息,记录到master.info文件
从库执行 start slave 命令,立即开启IO_T和SQL_T 从库 IO_T,读取master.info文件中的信息，获取到IP,PORT,User,Pass,binlog的位置信息
从库IO_T请求连接主库,主库专门提供一个DUMP_T,负责和IO_T交互
IO_T根据binlog的位置信息(mysql-bin.000004 , 444),请求主库新的binlog
主库通过DUMP_T将最新的binlog,通过网络TP给从库的IO_T
IO_T接收到新的binlog日志,存储到TCP/IP缓存,立即返回ACK给主库,并更新master.info
IO_T将TCP/IP缓存中数据,转储到磁盘relaylog中.
SQL_T读取relay.info中的信息,获取到上次已经应用过的relaylog的位置信息
SQL_T会按照上次的位置点回放最新的relaylog,再次更新relay.info信息
从库会自动purge应用过relay进行定期清理
补充说明: 一旦主从复制构建成功,主库当中发生了新的变化,都会通过dump_T发送信号给IO_T,增强了主从复制的实时性.
主从复制监控
show slave status \G # 主库执行
#打印结果： Slave_IO_State: Waiting for master to send event # 与主库有关的信息（master.info） Master_Host: 192.168.1.8 //主库的地址 Master_User: repl //主库用于主从复制的用户 Master_Port: 3306 //主库端口号 Connect_Retry: 10 //指定主服务器宕机或链接丢失情况下，从服务器线程重新尝试连接主服务器之前睡眠的秒数 Master_Log_File: mysql-bin.000005 //当前主从复制下主库所使用的二进制日志文件 Read_Master_Log_Pos: 1175 //当前主从复制下主库的位置点 # 从库relay应用信息相关（relay-log.info） Relay_Log_File: localhost-relay-bin.000002 Relay_Log_Pos: 745 Relay_Master_Log_File: mysql-bin.000005 # 从库线程运行状态（排错使用） Slave_IO_Running: Yes Slave_SQL_Running: Yes # 过滤复制相关的信息 Replicate_Do_DB: Replicate_Ignore_DB: Replicate_Do_Table: Replicate_Ignore_Table: Replicate_Wild_Do_Table: Replicate_Wild_Ignore_Table: Last_Errno: 0 Last_Error: Skip_Counter: 0 Exec_Master_Log_Pos: 1175 Relay_Log_Space: 956 Until_Condition: None Until_Log_File: Until_Log_Pos: 0 Master_SSL_Allowed: No Master_SSL_CA_File: Master_SSL_CA_Path: Master_SSL_Cert: Master_SSL_Cipher: Master_SSL_Key: # 从库延时主库的时间（秒） Seconds_Behind_Master: 0 # 主库SSL证书 Master_SSL_Verify_Server_Cert: No # 从库线程运行报错提示 Last_IO_Errno: 0 Last_IO_Error: Last_SQL_Errno: 0 Last_SQL_Error: Replicate_Ignore_Server_Ids: Master_Server_Id: 6 Master_UUID: 60fe8aae-d62e-11ea-bbbe-00505683acb6 Master_Info_File: /home/3308/data/master.info # 延时从库（设定延时） SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: # GTID复制相关 Retrieved_Gtid_Set: 60fe8aae-d62e-11ea-bbbe-00505683acb6:3-4 Executed_Gtid_Set: 063ea32f-d54e-11ea-8584-00505683acb6:1-4, 60fe8aae-d62e-11ea-bbbe-00505683acb6:3-4 Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version:
主从复制故障处理
IO线程损坏
1、连接主库：connecting
解决方法：
从库： stop slave ; reset slave all; change master to 。。。 start slave
2、请求主库二进制同步失败
解决方法：
从库： stop slave ; reset slave all; CHANGE MASTER TO MASTER_HOST='10.0.0.51', MASTER_USER='repl', MASTER_PASSWORD='123', MASTER_PORT=3307, MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=154, MASTER_CONNECT_RETRY=10; start slave;
SQL线程损坏
relay-log损坏
回放relaylog
约束冲突（主键、唯一键、非空...）
合理处理方法：
把握一个原则，一切以主库为基准
出现问题尽量进行反操作
最直接稳妥的方法就是重新构建主从
不推荐的解决方案： 方法一： stop slave; set global sql_slave_skip_counter = 1; start slave; #将同步指针向下移动一个，如果多次不同步，可以重复操作。 start slave; 方法二： /etc/my.cnf slave-skip-errors = 1032,1062,1007
有效避免主从sql线程故障的方法：
1、从库只读
2、使用读写分离中间件
主从延时的原因
主库方面：
1、binlog写入不及时，sync_binlog=1
2、默认情况下dump_t是串行传输binlog，在并发事务量大的情况下或者大事务，会导致传送日志较慢。解决方法：必须GTID，使用group commit方式来支持DUMP_T并行
3、主库繁忙
从库方面：
1、由于从库是单sql线程，不管传的日志多少，只能一次执行一个事务，5.6版本有了GTID可以实现多sql线程，但是只能基于不同库的事务进行回放。5.7版本增强了GTID，增加了seq_no，并且新增了并发sql线程(logical_clock),MTS技术
2、主从硬件配置差异过大
3、主从参数配置
4、主从版本不一致
5、主从索引不一致
主从复制延时策略设置
1、延时从库
sql线程延时：数据已经写入relaylog中，sql线程延时运行，防止出现主库删库，从库跟随删除，一般建议3-6小时
mysql>stop slave; mysql>CHANGE MASTER TO MASTER_DELAY = 300; //以秒为单位 mysql>start slave; # 查询主从状态 show slave status \G SQL_Delay: 300 //延时设置的时间 SQL_Remaining_Delay: NULL //延时剩余时间
2、延时从库逻辑故障处理
监控到数据库逻辑故障 停止从库sql线程，记录已经回放的位置点（作为截取日志起点） mysql> stop slave sql_thread; mysql> show slave status \G Relay_Log_File: db01-relay-bin.000002 Relay_Log_Pos: 626 截取从库relaylog日志恢复到主库drop之前 恢复业务 从库替代主库进行工作 或 从库导出故障库还原到主库
快速解除主从关系
# 从库 stop slave; reset slave all; # 主库 reset master;
主从复制过滤
# 从库配置过滤 vi /etc/my.cnf- # 添加配置命令 replicate_do_db=repl //需要复制的数据库，如果有多个可以写多行
