mysql主从复制
一、mysql日志管理二、日志备份恢复与迁移三、搭建主从复制三、主从复制工作原理以192.168.25.131为例
一、mysql日志管理
MYSQL日志管理：
binlog该如何配置？
log_bin:开关；设定存放位置
server_id:5.7中必须要加server_id
注意：生产中，日志和数据要分开放（使用不同的硬件磁盘）
配置文件如下
vim /etc/fserver_id=6log_bin=/application/mysql/data/mysql-bin
在binlog目录里查看文件
[root@db data]# lltotal 123076-rw-r----- 1 mysql mysql612 Aug 31 16:36 mysql-bin.000001 #二进制文件-rw-r----- 1 mysql mysql154 Aug 31 16:36 mysql-bin.000002 #每重启一次就有新的数字值增长-rw-r----- 1 mysql mysql 82 Aug 31 16:36 mysql-bin.index #对于上面两个二进制文件的索引，包含名和位置
在sql内部可以这样查看
mysql> show variables like '%log_bin%';+---------------------------------+-----------------------------------------+| Variable_name | Value|+---------------------------------+-----------------------------------------+| log_bin| ON || log_bin_basename| /application/mysql/data/mysql-bin || log_bin_index | /application/mysql/data/mysql-bin.index || log_bin_trust_function_creators | OFF || log_bin_use_v1_row_events | OFF || sql_log_bin | ON |+---------------------------------+-----------------------------------------+
binlog记录了什么？
记录了数据库中所有变更类的操作（DDL,DML,DCL）
对于DDL.DCL，记录发生过的语句，比如建库
DML（insert,upfate,delete）：前提是已经提交的语句，才能被记录到binlog中
关于记录格式：
ROW : RBR 行记录模式，记录的是行的变化 ，日志量大，够严谨，不会出现记录错误
STATEMENT : SBR 语句记录模式，记录的是操作语句，日志量少，可读性较强。对于函数类的操作，将来恢复时会造错误
MIXED : MBR 混合记录模式，交给mysql自行决定，但是没人用
5.7默认是RBR,是企业建议模式
在mysql内部可以查看到
mysql> select @@binlog_format;+-----------------+| @@binlog_format |+-----------------+| ROW |+-----------------+
二进制日志事件（event）
二进制日志的最小记录单元
对于DML,DCL，一个语句就是一个event
对于DML语句来讲：只记录已提交的事务
例如一下例子，就被分为4个event
begin; 120-340
DML1 340-460
DML2 460-550
commit; 550-760
mysql日志管理-slowlog
优化相关日志，专门用来记录sql语句的日志，定位低效sql语句的工具日志
开启慢日志
默认关闭mysql> select @@slow_query_log;+------------------+| @@slow_query_log |+------------------+|0 |+------------------+1 row in set (0.00 sec)开启：vim /etc/fslow_query_log=1slow_query_log_file=/application/mysql/data/db-slow.loglong_query_time=0.1log_queries_not_using_indexes保存之后再重启数据库
再查看slowlog的位置
[root@db data]# /application/mysql/data[root@db data]# lltotal 123096-rw-r----- 1 mysql mysql186 Sep 6 11:33 db-slow.log这就是我们设置的慢日志
分析慢日志：
mysqldumpslow -s c -t 10 /application/mysql/data/db-slow.log-s c以次数排序，-t取出前10名最慢的日志
以下为慢日志分析的结果，执行了几次，执行了多长时间
二、日志备份恢复与迁移
1.DBA在数据库备份恢复方面的职责
1.设计备份策略：全备，增量，时间，自动
2.日常备份检查
备份存在性；备份空间是否够用
2.备份类型
热备：
在数据库业务正常工作期间，进行备份数据，并且能够一致性恢复(innodb)
温备：
锁表备份，只能查询，不能修改(myisam)
冷备：
关闭数据库业务，进行数据备份
3.备份工具
A.逻辑备份工具：
基于sql语句进行备份
mysqldump (优点：不需要下载，备份出来的是SQL,可读性高，便于备份处理，文本形式，压缩比高，节省磁盘空间；缺点：依赖于数据库引擎，需要从磁盘把数据读出，然后转换为SQL语句进行转储，消耗CPU和IO资源；建议100G内的数据量用mysqldump，超过TB以上也可能选择mysqldump,配合分布式的系统，1EB=1024PB=1000000TB)
mysqlbinlog
B.物理备份工具：
基于磁盘数据文件备份，直接拷磁盘文件
xtrabackup(XBK) (优点：类似于直接CP数据文件，不需要管逻辑关系，性能较高；缺点：可读性差，压缩比低，需要更多磁盘空间；建议：>100G<TB)
4.实践
mysqldump使用
客户端通用命令，和链接有关：-u,-p,-S,-h,-P本地备份连接方式：mysqldump -uroot -pxxx -S /tmp/mysql.sock远程备份的连接方式：mysqldump -uroot -pxxx -h xxx -P 3306基本备份参数：-A 实现全库备份[root@db data]# mkdir -p /data/backup[root@db data]# mysqldump -uroot -p123456 -A -S /tmp/mysql.sock > /data/backup/full.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@db data]# echo $?0-B 分库备份备份liyu这个库[root@db backup]# mysqldump -uroot -p123456 -B liyu -S /tmp/mysql.sock > /data/backup/db.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.[root@db backup]# echo $?0库名 表名[root@db backup]# mysqldump -uroot -p123456 -B world city -S /tmp/mysql.sock > /data/backup/tab.sql必加参数：-R 在备份时，同时备份存储过程和函数，如果没有会自动忽略-E 在备份时，同时备份EVENT，没有自动忽略--triggers 在备份时，同时备份触发器，没有自动忽略--master-data=2 记录备份开始时position号，自动锁表，配合--single-transaction,减少锁表--single-transaction 对于innodb的表，实现快照备份，不锁表
三、搭建主从复制
1.介绍
依赖于二进制日志的，"实时"备份的一个多节点架构
binlog日志是用来做数据恢复，主从复制的
2.主从复制的前提(如何搭建)
A.至少两个实例
B.不同的server_id
C.主库需要开启二进制日志
D.主库需要授权一个专用复制用户
E.主库数据备份
F.开启专用复制线程
1.准备多实例环境，可以参考<<mysql安装，体系结构及管理>>里的多实例配置
2.检查server_id
[root@keepalived-master system]# mysql -S /data/3307/mysql.sock -e "select @@server_id"+-------------+| @@server_id |+-------------+| 7 |+-------------+[root@keepalived-master system]# mysql -S /data/3308/mysql.sock -e "select @@server_id"+-------------+| @@server_id |+-------------+| 8 |+-------------+[root@keepalived-master system]# mysql -S /data/3309/mysql.sock -e "select @@server_id"+-------------+| @@server_id |+-------------+| 9 |+-------------+
3.选择3307为主库，3308,3309为从库
检查3307(主库)的二进制日志情况
root@keepalived-master system]# mysql -S /data/3307/mysql.sock -e "show variables like '%log_bin%'"+---------------------------------+----------------------------+| Variable_name | Value |+---------------------------------+----------------------------+| log_bin| ON|
4.主库创建复制用户
[root@keepalived-master system]# mysql -S /data/3307/mysql.sock 进入主库mysql> grant replication slave on *.* to repl@'192.168.25.%' identified by '123456';
5.进行主库数据备份
[root@keepalived-master system]# mysqldump -S /data/3307/mysql.sock -A --master-data=2 -R -E --triggers --single-transaction > /tmp/full.sql
恢复数据到从库(3308)
mysql -S /data/3308/mysql.sockmysql> set sql_log_bin=0; 关闭二进制日志mysql> source /tmp/full.sql; 将3307备份的文件导入
告诉从库复制的信息(用户名，密码，端口号，复制起点，IP，binlog相关)
可以help change master to查看用法CHANGE MASTER TOMASTER_HOST='',MASTER_USER='replication',MASTER_PASSWORD='password',MASTER_PORT=3306,MASTER_LOG_FILE='master2-bin.001',MASTER_LOG_POS=4,MASTER_CONNECT_RETRY=10;
可以在备份的/tmp/full.sql里查看LOG_FILE,22行查看
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000001', MASTER_LOG_POS=448;
在从库3308上运行
mysql> CHANGE MASTER TO-> MASTER_HOST='192.168.25.131',-> MASTER_USER='repl',-> MASTER_PASSWORD='123456',-> MASTER_PORT=3307,-> MASTER_LOG_FILE='mysql-bin.000001',-> MASTER_LOG_POS=448,-> MASTER_CONNECT_RETRY=10;Query OK, 0 rows affected, 2 warnings (0.01 sec)mysql> start slave; #启动复制线程Query OK, 0 rows affected (0.01 sec)
遇到的问题：如果change master to输入有误，可以
mysql> stop slave; #先停止复制线程mysql> reset slave all; #清理输入错误的change master to的内容mysql> 重新输入change master tomysql> start slave; #再启动
查看从库连接状态
mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.25.131Master_User: replMaster_Port: 3307Connect_Retry: 10Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 448Relay_Log_File: keepalived-master-relay-bin.000002Relay_Log_Pos: 320Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: Yes#出现YES即为成功Slave_SQL_Running: Yes
三、主从复制工作原理
1、名词认识
文件：
主库：binlog
从库:relay-log 中继日志
master.info主库信息文件
relay-log.info中继日志应用信息
线程：
主库：binlog_dump_thread二进制日志投递线程
可以用这个命令查看mysql -S /data/3307/mysql.sock -e "show processlist"
从库：IO_Thread: 从库的IO线程，负责请求和接受主库发过来的binlog
SQL_Thread: 从库的SQL线程，回放日志
2、工作原理
1、从库执行change master to语句，会立即将主库信息记录到master.info文件里
2、从库执行start slave的瞬间，从库会立即生成IO_Thread和SQL_Thread
3、IO_Thread会读取master.info里的文件，获取到主库信息
4、IO_Thread连接到主库，主库会立即分配一个binlog_dump_thread,与IO_Thread进行交互
5、IO_Thread根据master.info binlog信息，向binlog_dump_thread请求最新的binlog
6、主库binlog_dump_thread，经过查询，如果发现有新的，截取并返回给从库的IO_Thread
7、从库IO_T会收到binlog,存储在TCP/IP缓存中，在网络底层返回ACK
8、从库IO_T会更新master.info，重置binlog位置点信息
9、从库IO_T会将binlog,写入到relaylog日志
10、从库SQL_T读取relay-log.info文件，获取上次执行过的位置
11、SQL_T按照位置点往下执行relay-log.info
12、SQL_T执行完成后，重新更新relay-log.info
13、relaylog定期自动清理
细节：主库发生了信息的修改，更新二进制日志完成后，会发送一个"信号"给binlog_dump_thread,binlog_dump_thread通知给IO_T线程
3.主从复制监控及故障处理
A.主从监控
主库：会发现有一个线程
[root@keepalived-master data]# mysql -S /data/3307/mysql.sockmysql> show processlist;+----+------+-------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+| Id | User | Host| db | Command| Time | State | Info |+----+------+-------------------------+------+-------------+-------+---------------------------------------------------------------+------------------+| 6 | repl | keepalived-master:49296 | NULL | Binlog Dump | 19863 | Master has sent all binlog to slave; waiting for more updates | NULL
从库：监控到的主库的信息(就是master.info里的信息)
mysql> show slave status\G*************************** 1. row ***************************Slave_IO_State: Waiting for master to send event #状态值Master_Host: 192.168.25.131 #主库ipMaster_User: repl #复制用户名Master_Port: 3307Connect_Retry: 10 #如果主从连不上会重试10次Master_Log_File: mysql-bin.000001 #已经获取到的binlog文件名Read_Master_Log_Pos: 448 #已经获取到的binlog位置号
从库的relaylog的信息(relay-log.info)
Relay_Log_File: keepalived-master-relay-bin.000002 #从库已经运行过的relaylog的文件名Relay_Log_Pos: 320 #从库已经运行过的relaylog的位置点
从库复制线程工作状态：
Slave_IO_Running: YesSlave_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: 0SQL_Remaining_Delay: NULL #延时操作的剩余时间
GTID复制信息
Retrieved_Gtid_Set: 接受到的GTID的个数Executed_Gtid_Set: 执行了的GTID的个数
B、主从故障的分析和处理
从库复制线程的工作状态
Slave_IO_Running: YesSlave_SQL_Running: Yes
从库线程报错详细信息
Last_IO_Errno: 0 IO报错的号码Last_IO_Error: IO报错的具体信息Last_SQL_Errno: 0 SQL报错的号码Last_SQL_Error:SQL报错的具体信息
C、IO线程故障
1、连接主库连接不上
connecting状态，NO状态
原因：
网络不通，防火墙，IP不对，port不对，密码不对，用户不对(change master to写入的原因)，连接数上限，server_id的问题，日志损坏
处理思路：
使用mysql -urepl -P3307 -p123456 -h
192.168.25.131进行手工连接，连接错了就会提示错误，就可以定位自己的错误
如何处理？
stop slave;
reset slave all;
change master to;
故障演练：日志损坏，日志不连续
主库操作：
[root@keepalived-master data]# mysql -S /data/3307/mysql.sockmysql> flush logs; #刷新日志Query OK, 0 rows affected (0.00 sec)mysql> flush logs;Query OK, 0 rows affected (0.00 sec)在从库查看状态mysql> show slave status\G*************************** 1. row ***************************Master_Log_File: mysql-bin.000004Read_Master_Log_Pos: 154在主库查看状态mysql> show master status\G*************************** 1. row ***************************File: mysql-bin.000004Position: 154两者信息对应在主库执行，搞破坏，导致主从不一致mysql> reset master;Query OK, 0 rows affected (0.01 sec)在从库查看，IO_T状态为NOmysql> show slave status\G*************************** 1. row *************************** Slave_IO_Running: No #这里！Slave_SQL_Running: YesLast_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 448, the last event read from '/data/3307/mysql-bin.000004' at 154, the last byte read from '/data/3307/mysql-bin.000004' at 154.'再在主库创建2个库，此时从库破坏，无法备份新创建的2个库mysql> create database dd;mysql> create database dd1;mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || dd || dd1|| liyu|| mysql || performance_schema || sys|+--------------------+7 rows in set (0.00 sec)在从库当中进行处理mysql> stop slave;mysql> reset slave all;mysql> CHANGE MASTER TO-> MASTER_HOST='192.168.25.131',-> MASTER_USER='repl',-> MASTER_PASSWORD='123456',-> MASTER_PORT=3307,-> MASTER_LOG_FILE='mysql-bin.000001', #这个在主库中show master status\G就可以看到操作哪个文件-> MASTER_LOG_POS=154, #上次的IO报错信息里有记录位置点-> MASTER_CONNECT_RETRY=10;Query OK, 0 rows affected, 2 warnings (0.00 sec)mysql> start slave;mysql> show slave status \G#再进行查看就恢复正常了*************************** 1. row ***************************Slave_IO_State: Waiting for master to send eventMaster_Host: 192.168.25.131Master_User: replMaster_Port: 3307Connect_Retry: 10Master_Log_File: mysql-bin.000001Read_Master_Log_Pos: 463Relay_Log_File: keepalived-master-relay-bin.000002Relay_Log_Pos: 629Relay_Master_Log_File: mysql-bin.000001Slave_IO_Running: YesSlave_SQL_Running: Yes再在从库查看库，之前在主库创建的库也恢复过来了mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || dd || dd1|| liyu|| mysql || performance_schema || sys|+--------------------+
