day11 主从复制

2019-06-27  本文已影响0人  藏鋒1013

回顾

1. mysqldump
-u
-p
-S
-h 
-P
-A --triggers -E
--master-data=2
--single-transaction
--set-gtid-purged=OFF  构建主从的备份千万不能加OFF,可以是ON或者AUTO
--max-allowed-packet=
2. xtrabackup
innobackupex
全备:
    innobackupex --user=xxx --password=xxx --no-timestamp /data/backup/full
增量:
    innobackupex --user=xxx --password=xxx --no-timestamp --incremental --incremental-basedir=/data/backup/full /data/backup/inc1
整理全备:
全备
    innobackupex --apply-log /data/backup/full
增量:
    只有最后一次合并的增量不需要--redo-only,过程中所有的备份整理都要加--redo-only
    inobackupex --apply-log --redo-only --incremental-dir=/data/backup/inc1/data/backup/full
恢复备份:
    innobackup --copy-back /data/backup/full
3. 备份工具如何配合binlog应用
binlog截取最重要的是找到起点和终点
周三鞋屋两点,数据损坏,截取日志的思路
mysqldump:
          起点:找到周二晚上全备脚本,找到CHANGE MASTER TO
          终点:通过events 或者 文件内容,找到故障点的位置
xtrabackup:
           起点:周二晚上增量备份,
          终点:通过events 或者 文件内容,找到故障点的位置
4.异构

操作系统版本不一样:逻辑备份 mysqldump
数据库版本不一样:
MySQL---CSV--->MongoDB ,ES
MySQL--JSON--->MongoDB ,ES


主从复制

一、介绍

依赖于二进制日志的“实时”备份的一个多节点结构

二、主从复制的前提(搭建主从复制)

2.1 至少两个实例
2.2 不同的server_id
2.3 主库要开启二进制日志
2.4 主库需要授权一个专用复制用户
2.5 主库数据备份
2.6开启专用复制线程

三、搭建主从复制

3.1 准备多实例

看前面章节
略……

3.2 检查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"

3.3

mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"
mysql -S /data/3308/mysql.sock -e "show variables like '%log_bin%'"
mysql -S /data/3309/mysql.sock -e "show variables like '%log_bin%'"

3.4 主库创建复制用户

mysql> grant replication slave on *.* to repl@'10.0.0.%' identified by '123';

3.5 进行主库数据备份

mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction >/tmp/full.sql

3.6 恢复数据到从库

mysql> set sql_log_bin=0;
mysql> cource /tmp/full.sql;

3.7 告诉从库复制的信息

mysql -S /data/3308/mysql.sock
mysql> help change master to
CHANGE MASTER TO
  MASTER_HOST='master2.example.com',
  MASTER_USER='replication',
  MASTER_PASSWORD='password',
  MASTER_PORT=3306,
  MASTER_LOG_FILE='master2-bin.001',
  MASTER_LOG_POS=4,
  MASTER_CONNECT_RETRY=10;

vim /tmp/full.sql
 22 -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=444;

3.8 启动复制线程

mysql> start slave;

3.9 如果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;

四、主从复制工作过程

4.1 名词认识

1. 文件:

(1) 主库: binlog
(2)从库:relay-log 中继日志
         master.info 主库信息文件
         relay-log.info 中继日志应用信息

2. 线程:

主库:binlog_dump_Thread 二进制日志投递线程
(mysql -S /data/3307/mysql.sock -e "show processlist")
从库:IO_Thread:从库IO线程(请求和接收binlog)
     SQL_Thread:从库的SQL线程(回放日志)

4.2 工作原理

(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会将二进制日志写到relay-log中
(10) 从库SQL_T读取relay_log.info文件,获取上次执行过的位置点
(11) SQL_T按照上次的执行点往下执行relay_log日志
(12) SQL_T执行完成之后,重新更新relay-log.info
(13)relay-log定期自动清理的功能
细节:
主库发生了信息的修改,更新二进制日志完成后,会发送一个“信号”给Dump_T,Dump_T通知给IO_T

五、主从复制监控及故障分析处理

5.1 主从监控

1. 主库:

mysql> show processlist;
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
| Id | User | Host            | db   | Command     | Time | State                                                         | Info             |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
|  2 | repl | 10.0.0.51:31712 | NULL | Binlog Dump | 6384 | Master has sent all binlog to slave; waiting for more updates | NULL             |
|  4 | root | localhost       | NULL | Query       |    0 | starting                                                      | show processlist |
+----+------+-----------------+------+-------------+------+---------------------------------------------------------------+------------------+
2 rows in set (0.12 sec)

mysql>

2. 从库:

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.000004
          Read_Master_Log_Pos: 154
               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: No
              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: 3294
              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: NULL
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: c130db0d-9879-11e9-ad28-000c296913bb
             Master_Info_File: /data/3308/data/master.info
                    SQL_Delay: 300
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: 
           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: 
1 row in set (0.00 sec)

ERROR: 
No query specified

mysql> 

相关信息:

主库的信息:
Master_Host: 10.0.0.51            ----->主库的IP
Master_User: repl                 ----->复制用户名
Master_Port: 3307                 ----->主库的端口
Connect_Retry: 10                 ----->断连之后重连的次数
Master_Log_File: mysql-bin.000004 ----->主库以获取的binlog文件名
Read_Master_Log_Pos: 154          ----->已经获取到的binlog位置号

从库的信息:(master.info)
Relay_Log_File: db01-relay-bin.000002 ----->从库已经运行的relay-log的文件名
Relay_Log_Pos: 320                    ----->

从库复制线程工作状态:
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的个数

5.2 主从故障的分析及处理

从库复制线程工作状态:

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线程报错的具体原因

5.2.1 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)    ----->IP问题

[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)    ----->用户名问题


如何处理?
stop slave;
reset slave all;
change master to
start slave;
(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_conuter = 1;
或者:
/etc/my.cnf
slave-skip-erroes = 1032,1062,1007
方法二:万全的解决
设置从库只读,防止写入
使用中间件做成读写分离的架构

六、主从延时原因分析

从库延时主库的时间:
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.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 如何使用延时从库

模拟故障:
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线程,停止主库业务。
2. 模拟SQL手工恢复relaylog到drop之前的位置点
3. 截取relaylog日志,找到起点(relay-log.info)和终点(drop 操作)
4. 恢复截取的日志,验证数据可用性。

开始处理:
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

5. 检查是否恢复
上一篇下一篇

猜你喜欢

热点阅读