1.介绍
依赖于二进制日志的，“实时”备份的一个多节点架构
2.主从复制的前提
至少2个实例
不同的server_id
主库需要开启二进制日志
主库需要授权一个专用的复制用户
主库要进行数据备份
开启专用的复制线程
3、搭建主从复制
准备多实例
(如果没有多实例就看多实例的文档)
检查server_id
mysql -S /data/3307/mysql.sock -e "select @@server_id"
mysql -S /data/3308/mysql.sock -e "select @@server_id"
mysql -S /data/3309/mysql.sock -e "select @@server_id"
##检查3307(主库)的二进制日志情况
mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"
##主库创建复制用户
[root@db01 ~]# mysql -S /data/3307/mysql.sock
mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123';
##进行主库数据备份
[root@db01 ~]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql
##恢复数据到从库(3308)
[root@db01 ~]# mysql -S /data/3308/mysql.sock
mysql> set sql_log_bin=0; ###这个是清除二进制日志的记录
mysql> source /tmp/full.sql; ###开始导入数据
##告诉从库的复制的信息
help change master to
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=444,
MASTER_CONNECT_RETRY=10;
###（这个是通二进日志进行的主从复制）
vim /tmp/full.sql ##找到他的二进制日志的名字，还有二进制日志的开始点
22 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=444;
###（一般在在备份文件里面的22行，也可以在数据库里面看这都是可以的）
##启动数据读的主从复制专线
mysql> start slave;
###如果change master to 信息输入错误，咋办？
mysql> stop slave;
mysql> 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=444,
MASTER_CONNECT_RETRY=10;
mysql> start slave;
##（这个就关掉重行提交主从复制的信息）
***********主从复制的工作原理**********
1.1 名词认识
主库 的文件名：binlog
从库的文件名：
relay-log中继日志
master.info主库信息文件
relay-log.info中继日志应用信息
1.2 主从复制的线程
主库：
binlog_dump_thread二进制日志投递线程
mysql -S /data/3307/mysql.sock -e "show processlist"
从库：
IO_Thread :从库IO线程 ： 请求和接收binlog
SQL_Thread:从库的SQL线程 ： 回放日志
2.1 工作原理
（1）从库执行 change master to 语句，会立即将主库信息记录到master.info中
（2）从库执行 start slave语句，会立即生成IO_T和SQL_T
（3）IO_T 读取master.info文件，获取到主库信息
（4）IO_T 连接主库，主库会立即分配一个DUMP_T,进行交互
（5）IO_T 根据master.info binlog信息，向DUMP_T请求最新的binlog
（6）主库DUMP_T,经过查询，如果发现有新的，截取并反回给从库IO_T
（7）从库IO_T会收到binlog，存储在TCP/IP缓存中,在网络底层返回ACK
（8）从库IO_T会更新master.info ,重置binlog位置点信息
（9）从库IO_T会将binlog，写入到relay-log中
（10）从库SQL_T 读取Relay-log.info 文件，获取上次执行过的位置点
（11）SQL_T按照位置点往下执行relaylog日志
（12）SQL_T执行完成后，重新更新relay-log.info
（13）relaylog定期自动清理的功能。
细节：
主库发生了信息的修改，更新二进制日志完成后，会发送一个“信号”给Dump_T,Dump_T通知给IO_T线程
3.1 主从复制的监控及故障分析处理
主库监控
主库：
show processlist;
Master has sent all binlog to slave; waiting for more updates
从库：
show slave status \G
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.0.0.51
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
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: 444
Relay_Log_Space: 526
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
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: 7
Master_UUID: dd822ce8-9878-11e9-b99b-000c29099eb6
Master_Info_File: /data/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:
Retrieved_Gtid_Set:
Executed_Gtid_Set:
Auto_Position: 0
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
*****************主库的信息（master.info）**************************
Master_Host: 10.0.0.51主库的IP
Master_User: repl 复制用户名
Master_Port: 3307 主库的端口
Connect_Retry: 10 断连之后重试次数
Master_Log_File: mysql-bin.000001已经获取得到binlog的文件名
Read_Master_Log_Pos: 444已经获取得到binlog的位置号
从库的relaylog的信息(relay-log.info)：
Relay_Log_File: db01-relay-bin.000002从库已经运行过的relaylog的文件名
Relay_Log_Pos: 320 从库已经运行过的relaylog的位置点
***************************************从库复制线程工作状态：****************************
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:
****************************从库延时主库的时间:********************************
Seconds_Behind_Master: 0从库延时主库的时间（秒为单位）
***************************从库线程报错详细信息：****************************
Last_IO_Errno: 0 IO报错的号码
Last_IO_Error: IO报错的具体信息
Last_SQL_Errno: 0 SQL报错的号码
Last_SQL_Error: SQL线程报错的具体原因
延时从库：
SQL_Delay: 0延时从库设定的时间
SQL_Remaining_Delay: NULL延时操作剩余时间
GTID复制信息：
Retrieved_Gtid_Set:接收到的GTID的个数
Executed_Gtid_Set: 执行了的GTID的个数
********************主从故障的分析及处理********************************
从库复制线程工作状态：
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
从库线程报错详细信息：
Last_IO_Errno: 0 IO报错的号码
Last_IO_Error: IO报错的具体信息
Last_SQL_Errno: 0 SQL报错的号码
Last_SQL_Error: SQL线程报错的具体原因
******************************* IO线程故障*************************************
………………………………（1）连接主库连接不上……………………………………
connecting
原因：网络不通 、防火墙、IP地址不对、port号不对、skip_name_resolve、连接次数上限
处理思路：
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.51 -P 3308 --->端口问题
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1130 (HY000): Host 'db01' is not allowed to connect to this MySQL server
[root@db01 ~]# mysql -urepl -p123 -h 10.0.0.52 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 2003 (HY000): Can't connect to MySQL server on '10.0.0.52' (110)'
[root@db01 ~]# mysql -urepl -p1234 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl'@'db01' (using password: YES)
[root@db01 ~]# mysql -urepl1 -p123 -h 10.0.0.51 -P 3307
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'repl1'@'db01' (using password: YES)
%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%%
*******************************:（2）请求新的binlog:***********************************
IO线程No的状态分析：
^^^^^^^^^^^^^原因一：日志名不对^^^^^^^^^^^^^^^^^^^^
从库信息：
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 444
对比备份的位置号。
^^^^^^^^^^^^原因二：日志损坏，日志不连续^^^^^^^^^^^^^^^^
演示：
主库：
mysql -S /data/3307/mysql.sock
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> flush logs;
mysql> reset master;
mysql> create database dd;
Query OK, 1 row affected (0.01 sec)
mysql> create database dd1;
Query OK, 1 row affected (0.00 sec)
mysql> create database dd2;
Query OK, 1 row affected (0.00 sec)
从库：
Slave_IO_Running: No
Slave_SQL_Running: Yes
Last_IO_Error: Got fatal error 1236 from master when reading data from binary log: 'could not find next log; the first event 'mysql-bin.000001' at 154, the last event read from '/data/3307/mysql-bin.000006' at 154, the last byte read from '/data/3307/mysql-bin.000006' at 154.'
处理：
mysql -S /data/3308/mysql.sock
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;
（3）写relaylog
（4）更新master.info
（5）server_id重复
5.2.2 SQL线程故障
原因一：
读relay-log.info
读relay-log，并执行日志
更新relay-log.info
以上文件损坏，最好是重新构建主从
原因二：
为什么一条SQL语句执行不成功？
1.主从数据库版本差异较大
2.主从数据库配置参数不一致（例如：sql_mode等）
3.想要创建的对象已经存在
4.想要删除或修改的对象不存在
5.主键冲突
6. DML语句不符合表定义及约束时
归根结底是从库写入了。
方法一：
stop slave;
set global sql_slave_skip_counter = 1;
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
方法二：万全的解决
设置从库只读，防止写入
使用中间件做成读写分离的架构
6.主从延时原因分析
从库延时主库的时间:
Seconds_Behind_Master: 0从库延时主库的时间（秒为单位）
6.1主库方面：
日志写入不及时
sync_binlog=1;
主库并发业务较高
“分布式”架构
从库太多
级联主从
对于Classic Replication：
主库是有能力并发运行事务的，但是在Dump_T在传输日志的时候，是以事件为单元传输日志的，
所以导致事务的传输工作是串行方式的，这时在主库TPS很高时，会产生比较大的主从延时。
怎么处理：
group commit。
从5.6开始加入了GTID，在复制时，可以将原来串行的传输模式变成并行的。
除了GTID支持，还需要双一保证。
6.2从库方面
Classic Replication
SQL线程只有一个，所以说只能串行执行relay的事务。
怎么解决？
多加几个SQL线程
在5.6中出现了database级别的多线程SQL
只能针对不同库下的事务，才能并发
到5.7版本加入了MTS ，真正实现了事务级别的并发SQL
7.延时从库
7.1数据损坏
物理损坏
逻辑损坏
对于传统的主从复制，比较擅长处理物理损坏。
7.2设计理念
对SQL线程进行延时设置
7.3延时多久合适？
一般企业，延时3-6小时
7.4如何设置
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
7.5如何使用延时从库
7.5.1思路
模拟故障：
mysql -S /data/3307/mysql.sock
create database delay charset utf8mb4;
use delay;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;
方法一：
stop slave;
set global sql_slave_skip_counter = 1;
/etc/my.cnf
slave-skip-errors = 1032,1062,1007
方法二：万全的解决
设置从库只读，防止写入
使用中间件做成读写分离的架构
6.主从延时原因分析
从库延时主库的时间:
Seconds_Behind_Master: 0从库延时主库的时间（秒为单位）
6.1主库方面：
日志写入不及时
sync_binlog=1;
主库并发业务较高
“分布式”架构
从库太多
级联主从
对于Classic Replication：
主库是有能力并发运行事务的，但是在Dump_T在传输日志的时候，是以事件为单元传输日志的，
所以导致事务的传输工作是串行方式的，这时在主库TPS很高时，会产生比较大的主从延时。
怎么处理：
group commit。
从5.6开始加入了GTID，在复制时，可以将原来串行的传输模式变成并行的。
除了GTID支持，还需要双一保证。
6.2从库方面
Classic Replication
SQL线程只有一个，所以说只能串行执行relay的事务。
怎么解决？
多加几个SQL线程
在5.6中出现了database级别的多线程SQL
只能针对不同库下的事务，才能并发
到5.7版本加入了MTS ，真正实现了事务级别的并发SQL
7.延时从库
7.1数据损坏
物理损坏
逻辑损坏
对于传统的主从复制，比较擅长处理物理损坏。
7.2设计理念
对SQL线程进行延时设置
7.3延时多久合适？
一般企业，延时3-6小时
7.4如何设置
mysql>stop slave;
mysql>CHANGE MASTER TO MASTER_DELAY = 300;
mysql>start slave;
mysql> show slave status \G
SQL_Delay: 300
SQL_Remaining_Delay: NULL
7.5如何使用延时从库
7.5.1思路
模拟故障：
mysql -S /data/3307/mysql.sock
create database delay charset utf8mb4;
use delay;
create table t1(id int);
insert into t1 values(1),(2),(3);
commit;
drop database delay;
开始处理：
1.停从库的SQL线程
mysql -S /data/3308/mysql.sock
mysql> stop slave sql_thread;
2.找relaylog的起点和终点
起点：
Relay_Log_File: db01-relay-bin.000002
Relay_Log_Pos: 476
终点：
mysql> show relaylog events in 'db01-relay-bin.000002'
| db01-relay-bin.000002 | 1149 | Query | 7 | 2036 | drop database delay
3.截取日志
[root@db01 ~]# mysqlbinlog --start-position=476 --stop-position=1149 /data/3308/data/db01-relay-bin.000002 >/tmp/relay.sql
4.恢复
mysql -S /data/3308/mysql.sock
set sql_log_bin=0;
source /tmp/relay.sql
9.半同步复制
加载插件
主:
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
从:
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
查看是否加载成功:
show plugins;
启动:
主:
SET GLOBAL rpl_semi_sync_master_enabled = 1;
从:
SET GLOBAL rpl_semi_sync_slave_enabled = 1;
重启从库上的IO线程
STOP SLAVE IO_THREAD;
START SLAVE IO_THREAD;
查看是否在运行
主:
show status like 'Rpl_semi_sync_master_status';
从:
show status like 'Rpl_semi_sync_slave_status';
10.GTID复制
gtid-mode=on
enforce-gtid-consistency=true
log-slave-updates=1
网友评论