mysql主从复制
以192.168.25.131为例
一、mysql日志管理
MYSQL日志管理:
binlog该如何配置?
log_bin:开关;设定存放位置
server_id:5.7中必须要加server_id
注意:生产中,日志和数据要分开放(使用不同的硬件磁盘)
配置文件如下
vim /etc/my.cnf
server_id=6
log_bin=/application/mysql/data/mysql-bin
在binlog目录里查看文件
[root@db data]# ll
total 123076
-rw-r----- 1 mysql mysql 612 Aug 31 16:36 mysql-bin.000001 #二进制文件
-rw-r----- 1 mysql mysql 154 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/my.cnf
slow_query_log=1
slow_query_log_file=/application/mysql/data/db-slow.log
long_query_time=0.1
log_queries_not_using_indexes
保存之后再重启数据库
再查看slowlog的位置
[root@db data]# /application/mysql/data
[root@db data]# ll
total 123096
-rw-r----- 1 mysql mysql 186 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.sql
mysqldump: [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.sql
mysqldump: [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.sock
mysql> set sql_log_bin=0; 关闭二进制日志
mysql> source /tmp/full.sql; 将3307备份的文件导入
告诉从库复制的信息(用户名,密码,端口号,复制起点,IP,binlog相关)
可以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;
可以在备份的/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 to
mysql> start slave; #再启动
查看从库连接状态
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.25.131
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 448
Relay_Log_File: keepalived-master-relay-bin.000002
Relay_Log_Pos: 320
Relay_Master_Log_File: mysql-bin.000001
Slave_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.sock
mysql> 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 #主库ip
Master_User: repl #复制用户名
Master_Port: 3307
Connect_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: 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的个数
B、主从故障的分析和处理
从库复制线程的工作状态
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报错的具体信息
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.sock
mysql> 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.000004
Read_Master_Log_Pos: 154
在主库查看状态
mysql> show master status\G
*************************** 1. row ***************************
File: mysql-bin.000004
Position: 154
两者信息对应
在主库执行,搞破坏,导致主从不一致
mysql> reset master;
Query OK, 0 rows affected (0.01 sec)
在从库查看,IO_T状态为NO
mysql> show slave status\G
*************************** 1. row ***************************
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 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 event
Master_Host: 192.168.25.131
Master_User: repl
Master_Port: 3307
Connect_Retry: 10
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 463
Relay_Log_File: keepalived-master-relay-bin.000002
Relay_Log_Pos: 629
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
再在从库查看库,之前在主库创建的库也恢复过来了
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| dd |
| dd1 |
| liyu |
| mysql |
| performance_schema |
| sys |
+--------------------+