MySQL日志和备份

Untitled

MyCLI 的安装
MyCLI 是一个用在MySQL，MariaDB，Percona数据上的客户端工具，其可以高亮显示SQL语句，并有语法提示和补全功能

# 1. 配置好epel源

# 2. 安装python-pip
yum -y install python34-pip.noarch

# 3. 安装MyCLI
pip3.4 install mycli

# 4. 连接MySQL
mycli -uUSER -pPASSWORD -hHOST

MySQL 日志

MySQL 日志文件有

查询日志
慢查询日志
错误日志
二进制日志
事务日志
中继日志

查询日志

查询日志记录了所有对MySQL数据库的请求信息，不仅仅是查询，包括所有的操作，比如对数据库、表的操作及增删改查等，错误的操作也会被记录。

查询日志默认是关闭的，一般情况不开启查询日志，占用空间过大，若调试需开启查询日志，开启方法为

SET @@global.general_log=ON;

查询日志可以记录在MySQL表中，也可以记录在文件中，设置方法如下

# 记录于文件中
SET @@global.log_output='FILE';

# 记录于表表中
SET @@global.log_output='TABLE';

# 记录于文件和表中
SET @@global.log_output='TABLE,FILE';

查询表中的查询日志文件

select * from mysql.general_log;

设置查询日志文件存储的路径

SET @@global.general_log_file="FILE_NAME";
select @@global.general_log_file;

查询记录于文件中的查询日志

cat /var/lib/mysql/node1.log

慢查询日志

慢查询：查询时间超出指定时长的查询

查询时长

set @@long_query_time=5;

存储方式的设定与查询日志的设定方式一样

# 记录于文件中
SET @@global.log_output='FILE';

# 记录于表表中
SET @@global.log_output='TABLE';

# 记录于文件和表中
SET @@global.log_output='TABLE,FILE';

开启慢速查询日志

SET @@global.log_slow_queries=ON
SET @@global.slow_query_log=ON

查看慢速查询日志存储路径

select @@slow_query_log_file;

查看表中的慢速查询日志

select * from mysql.slow_log;

错误日志

错误日志主要记录以下几类信息：

mysqld启动或关闭过程中的输出信息
mysqld运行中产生的错误信息
event scheduler 运行时产生的信息
主从复制架构中，从服务器启动复制线程产生的信息
1. 查看错误日志的存储位置

MariaDB> set @@global.log_error=OFF;

警告日志的开启与关闭

 SET @@global.log_warnings=0|1

二进制日志

二进制日志记录了引起数据库数据改变的SQL语句，不包括SELEC和SHOW语句

二进制日志主要有以下几种作用

恢复 (recovery) ：数据库恢复一般都需要二进制日志做最后系统崩溃前 point-in-time 的恢复，比如数据库做了完整恢复和增量恢复后仍然有数据库崩溃前到最近一次增量备份时间段内的数据未恢复，此时就需要使用二进制日志来恢复。
复制 (replication) ：复制和恢复的原理一样，在主从数据库服务器的架构中，从服务器需要从主服务器上复制二进制日志文件到从服务器上，存储为中继日志后，再同步到从服务器上，从而实现主从异步同步数据的目的。
审计 (audit) : 可以通过分析二进制日志中的信息，判断是否有对数据库进行入驻攻击。

二进制日志记录的格式

select @@global.binlog_format;

set @@global.binlog_format={ROW|STATEMENT|MIXED}

查看二进制日志文件列表

MariaDB> show binary logs; 

MariaDB> show master logs;
+----------------+-----------+
| Log_name       | File_size |
+----------------+-----------+
| log-bin.000001 | 30331     |
| log-bin.000002 | 1038814   |
| log-bin.000003 | 286       |
| log-bin.000004 | 493       |
| log-bin.000005 | 561       |
| log-bin.000006 | 264       |
| log-bin.000007 | 264       |
| log-bin.000008 | 245       |
+----------------+-----------+

滚动二进制日志文件，从启一个文件记录二进制日志文件

MariaDB> flush logs;

查看当前使用的二进制日志文件

MariaDB> show master status;
+----------------+----------+--------------+------------------+
| File           | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+----------------+----------+--------------+------------------+
| log-bin.000009 | 245      |              |                  |
+----------------+----------+--------------+------------------+

查看二进制日志文件中的事件信息

show binlog events in 'log-bin.000001' from 321 limit 1 \G;
---
'log-bin.000001' : 文件名
from 321 ： 从哪个字节开始读取
limit : 读取的行数

设定配置文件启动二进制日志文件

vim /etc/my.cnf.d/server.cnf
[server]
# 不解析名字
skip_name_resolve=ON  
# InnoDB引擎的每个表单独存储于一个文件中
innodb_file_per_table=ON
# 连接数
max_connections=10000
# 服务器编号
server_id=10
# 二进制日志文件存储路径
log_bin=log-bin

查看二进制日志文件的最大字节数

MariaDB> select @@max_binlog_size;
+-------------------+
| @@max_binlog_size |
+-------------------+
| 1073741824        |
+-------------------+

控制写操作是否会记录与二进制日志文件中，进行二进制日志的恢复时，关闭，操作完后启动

select @@session.sql_log_bin;

set @@session.sql_log_bin=0|1;

控制一旦事务提交立即将内存的日志同步到磁盘，启用则保证了数据的安全，但会影响性能

select @@sync_binlog;

set @@sync_binlog=0|1;

使用 mysqlbinlog 查看二进制日志文件

mysqlbinlog [options] FILE_NAME
---
[options]
    --start-datetime="YYYY-MM-DD hh:mm:ss"
    --stop-datetime="YYYY-MM-DD hh:mm:ss"
    -j, --start-position=NUM
    --stop-position=NUM
    --user USER
    --host HOST
    --password PASSWORD

# example
mysqlbinlog  --start-date="2017-11-10 22:46:50" /var/lib/mysql/log-bin.000009;
 mysqlbinlog  --start-position=316 --stop-position=444 /var/lib/mysql/log-bin.000009;

二进制日志格式

# at 316
#171110 22:46:50 server id 10  end_log_pos 444  Query   thread_id=7 exec_time=0 error_code=0
use `courses`/*!*/;
SET TIMESTAMP=1510325210/*!*/;
insert into students(id,name,major) values(2,'Moretz','Music')
/*!*/;

事件的起始位置：# at 316
事件发生的日期时间：#171110 22:46:50
事件发生的服务器: server id 10
事件的结束位置：server id 10
事件的类型：Query
事件发生时所在服务器执行此事件的连接线程的ID：thread_id=7
语句的时间戳与将其写入二进制日志文件中的时间差，小于1s的为0：exec_time=0
错误代码，正确为0：error_code=0
设定事件发生时的时间戳：语句的时间戳与将其写入二进制日志文件中的时间差：exec_time=0
    --小于1s都是为0
错误代码：error_code=0
设定事件发生时的时间戳：SET TIMESTAMP=1472608568/*!*/;
事件内容：insert into students(id,name,major) values(2,'Moretz','Music')

中继日志

从服务器记录从主服务器上复制二进制日志文件的事件日志

事务日志

事务存储引擎InnoDB用于保证事务特性的日志文件

MySQL 备份

备份类型

MySQL备份类型从不同的角度有不同的划分

按照备份时数据服务是否在线
- 热备 (Hot Backup / Online Backup)
- 温备 (Warm Backup)
- 冷备 (Cold Backup / Offline Backup)
按照备份的数据范围
- 完全备份：所有数据库
- 部分备份：数据库的一部分，比如某个表
按照备份的时间范围
- 全量备份
- 增量备份
- 差异备份
按照备份后的数据类型
- 逻辑备份
- 物理备份或裸备

备份工具

1. mysqldump # mysql 自带的备份工具，对InnoDB实现逻辑、热备，支持全量备份，MyISAM温备

2. cp/tar # 使用逻辑卷达到几乎热备的效果，属于物理备份

3. xtrabackup # Percona 提供的开源、免费的备份工具，支持对InnoDB实现物理、热备，并且支持全量备份，增量备份或差异备份

使用 mysqldump 实现备份

mysqldump 命令

# mysqldump 命令语法
# 1. 备份指定库中的指定表
shell> mysqldump [options] db_name [tbl_name ...]
# 2. 备份指定的数据库
shell> mysqldump [options] --databases db_name ...
# 3. 备份所有数据库
shell> mysqldump [options] --all-databases

---
[options]
    -l | --lock-tables #备份时锁住每个架构下面的所有表，用户备份MyISAM时使用，备份InnoDB时使用 --single-transaction, 并且--lock-tables和--single-transaction是互斥的，只能使用其中的一种，对于架构下面即有MyISAM也有InnoDB，只能使用--locak-tables了。使用--lock-tables只能保证每个架构下面的表一致性，不能保证所有表一致性。锁住的情况下可以继续读，但不能执行写操作
    -x | --lock-all-tables #备份时锁住所有表，保证了所有表的一致性，但备份时间过长的话会影响业务。
    -single-transaction #使用于InnoDB进行热备，备份开始时开启一个长长的事务(start transaction)以保证数据的一致性。
    --master-data=1|2 # 等于2时，CHANGE MASTER TO 语句被注释
    -E | --events #备份事件调度器
    -R | --routines #备份程序或函数
    --triggers #备份触发器

备份策略

全量备份+binlog

第一步：开启二进制日志，并设定相应参数

# 创建数据库备份目录和二进制日志存储目录
[node1@root ~]# mkdir -p /app/data/log 
[node1@root ~]# chown mysql:mysql /app/data/log

vim /etc/my.cnf.d/server.cnf
[server]
skip_name_resolve=ON
innodb_file_per_table=ON
max_connections=10000
server_id=1
log_bin=/app/data/log/log-bin #设定二进制日志路径及前缀名

第二步：准备备份测试环境

# 1. 创建用户bkuser, 用做备份数据库用户
MariaDB> grant all on *.* to 'bkuser'@'172.18.17.%' identified by '123';
MariaDB> flush privileges;

# 2. 创建测试数据库和表
MariaDB> create database courses;
MariaDB> use courses;
MariaDB> create table students(id int primary key, name char(20) not null, major varchar(200));

# 3. 向表中插入数据
[node1@root mysql]# for i in {1..100};do mysql -ubkuser -p123 -h172.18.17.21 -e "insert into courses.students(id,name) values($i,'stu$i');";done

第三步：备份数据

# 备份
[20@root ~]#  mysqldump -h172.18.17.21 -ubkuser -p123 --all-databases --single-transaction -E -R --triggers --master-data=2 -F > /app/data/all-backup_$(date +%F-%H-%M-%S).sql

# 修改数据
MariaDB> delete from courses.students where id between 1 and 20;

# 模拟数据库崩溃
rm /var/lib/mysql/* -rf

第四步：还原数据库

# 查看最近一次全量备份数据库中记录的二进制日志文件名，和位置
vim /app/data/all-backup_2017-11-11-16-14-43.sql 
-- CHANGE MASTER TO MASTER_LOG_FILE='log-bin.000004', MASTER_LOG_POS=245;

# 将备份和相应二进制日志拷贝到另外一个目录下，防止二次破坏影响还原
[node1@root ~]# mkdir /app/bakup
[node1@root ~]# cp -r /app/data/* /app/bakup/

# 还原全量备份
# 关闭二进制日志记录，还原数据库的过程中，我们不希望记录日志
MariaDB> set @@session.sql_log_bin=OFF;
# 还原
[node1@root ~]# mysql < /app/bakup/all-backup_2017-11-11-16-14-43.sql 

# 重放二进制日志完成系统崩溃前的数据还原
mysqlbinlog --start-position 245 /app/bakup/log/log-bin.000004 > /app/bakup/bin-log.sql
mysql < /app/bakup/bin-log.sql 

# 开启二进制日志记录功能
MariaDB> set @@session.sql_log_bin=ON;

第五步：做一次全量备份

[node1@root ~]# mysqldump --all-databases --single-transaction -F --master-data=2 -R -E --triggers > /app/data/all-bakup_$(date +"%F-%H-%M-%S").sql

备份脚本

备份脚本

[node1@root scritps]# cat mysql_backup.sh 
#/bin/bash

user="bkuser"
password="123"
host="172.18.17.21"

data_bk_dir="/app/data"
binlog_bk_dir="/app/data/log"
cnf_bk_dir=/app/data/cnf/$(date +"%F-%H-%M-%S")

[ -d "$binlog_bk_dir" ] ||     mkdir -p "$binlog_bk_dir"
[ -d "$cnf_bk_dir" ] || mkdir -p "$cnf_bk_dir"

/usr/bin/chown mysql:mysql "$binlog_bk_dir"

/usr/bin/mysqldump -u ${user} -p${password} -h ${host} --all-databases --single-transaction -F --master-data=2 -R -E --triggers > ${data_bk_dir}/all-bakup_$(date +"%F-%H-%M-%S").sql

cp -ar /etc/my.cnf* ${cnf_bk_dir}

添加到计划任务，根据需求制定任务计划，这里假设每周做一次全量备份，时间为每周六03:00进行备份

vim /etc/crontab
0 3 * * 6 root /usr/bin/bash /app/scripts/mysql_backup.sh

使用 xtrabackup 实现备份

下载安装percona-xtrabackup
percona-xtrabackup 下载地址

yum -y install percona-xtrabackup-24-2.4.8-1.el7.x86_64.rpm

全量+binlog

第一步：实验环境
实验环境和上面一个一样

# 1. 创建存储备份文件和二进制日志文件的目录
[node1@root ~]# mkdir /app/data/{log,all,increment}
[node1@root ~]# chown mysql:mysql /app/data/log

# 2. mysql配置文件
vim /etc/my.cnf.d/server.cnf
    [server]
    skip_name_resolve=ON
    innodb_file_per_table=ON
    max_connections=10000
    server_id=1
    log_bin=/app/data/log/log-bin

# 3. 启动mariadb
 systemctl start mariadb

# 4. 创建备份用户
MariaDB>  grant all on *.* to 'bkuser'@'172.18.17.%' identified by '123';

# 5. 创建数据库和表
MariaDB> create database courses;
MariaDB> create table courses.students(id int primary key, name char(20) not null, major varchar(200));

# 6. 插入数据
for i in {1..100};do mysql -ubkuser -p123 -h172.18.17.21 -e "insert into courses.students(id,name) values($i,'stu$i');";done

第二步：全量备份

# 全量备份
innobackupex -ubkuser -p123 -H172.18.17.21 /app/data/all/

# 查看备份的LSN信息
cat /app/data/all/2017-11-11_20-31-48/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1707853
last_lsn = 1707853
compact = 0
recover_binlog_info = 0=

# 查看备份的日志位置
cat /app/data/all/2017-11-11_20-31-48/xtrabackup_binlog_pos_innodb 
/app/data/log/log-bin.000003    21207

第三步：修改数据，并模拟系统崩溃

# 修改数据
delete from  courses.students where id between 1 and 50；

# 删除数据库，模拟系统崩溃
[node1@root ~]# systemctl stop mariadb
[node1@root ~]# rm /var/lib/mysql/* -rf

第四步：恢复

# 使用xtrabackup 恢复全量数据
# 1. 执行 preparing 操作
innobackupex --apply-log /app/data/all/2017-11-11_20-31-48/
# 2. 恢复数据库
innobackupex --copy-back /app/data/all/2017-11-11_20-31-48/
# 3. 修改属主属组
chown -R mysql:mysql /var/lib/mysql

第五步：使用二进制日志完成最后崩溃前的恢复

# 1. 复制二进制文件到指定位置备用
cp  /app/data/log/log-bin.000003 /app/data/
# 5. 启动数据库，并设置不记录二进制日志
MariaDB> set @@session.sql_log_bin=0;

# 6. 恢复
mysqlbinlog --start-position 21207 /app/data/log-bin.000003 > /app/data/log-bin.sql
mysql < /app/data/log-bin.sql

第六步：启用数据前做一次全量备份后方可继续使用

全量+增量+binlog
第一步：在上一个 “全量+binlog” 的基础上演示该实验

MariaDB> select * from courses.students;
+----+-------+-------+
| id | name  | major |
+----+-------+-------+
| 51 | stu51 | NULL  |
| 52 | stu52 | NULL  |
| 53 | stu53 | NULL  |
| 54 | stu54 | NULL  |
| 55 | stu55 | NULL  |
| 56 | stu56 | NULL  |
| 57 | stu57 | NULL  |
| 58 | stu58 | NULL  |
| 59 | stu59 | NULL  |
| 60 | stu60 | NULL  |
+----+-------+-------+

第二步：全量备份

innobackupex -ubkuser -p123 -H172.18.17.21 /app/data/all/

# 查看全量备份的LSN编号
cat /app/data/all/2017-11-11_21-14-54/xtrabackup_
xtrabackup_binlog_info  xtrabackup_checkpoints  xtrabackup_info         xtrabackup_logfile      
[node1@root ~]# cat /app/data/all/2017-11-11_21-14-54/xtrabackup_checkpoints 
backup_type = full-backuped
from_lsn = 0
to_lsn = 1719643
last_lsn = 1719643
compact = 0
recover_binlog_info = 0

第三步：第一次增量备份

# 修改数据
MariaDB> update courses.students set major="Engish" where id between 51 and 55;

# 增量备份
[node1@root ~]# innobackupex -ubkuser -p123 -H172.18.17.21 --incremental /app/data/increment/ --incremental-basedir=/app/data/all/2017-11-11_21-14-54

# 查看增量备份的LSN编号
# cat /app/data/increment/2017-11-11_21-22-04/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1719643
to_lsn = 1720617
last_lsn = 1720617
compact = 0
recover_binlog_info = 0

第四步：第二次增量备份

# 修改数据
update courses.students set major="math" where id between 56 and 60;

# 增量备份
innobackupex -ubkuser -p123 -H172.18.17.21 --incremental /app/data/increment/ --incremental-basedir=/app/data/increment/2017-11-11_21-22-04

# 查看增量备份的LSN编号
# cat /app/data/increment/2017-11-11_21-25-28/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 1720617
to_lsn = 1722266
last_lsn = 1722266
compact = 0
recover_binlog_info = 0

# 查看二进制日志名字及位置
cat /app/data/increment/2017-11-11_21-25-28/xtrabackup_binlog_info 
log-bin.000001  681

第五步：模拟数据库崩溃

# 1. 破坏前修改数据库，为演示使用二进制日志恢复做准备
MariaDB> delete from courses.students where id in (51,53,55);
MariaDB> select * from courses.students;
+----+-------+--------+
| id | name  | major  |
+----+-------+--------+
| 52 | stu52 | Engish |
| 54 | stu54 | Engish |
| 56 | stu56 | math   |
| 57 | stu57 | math   |
| 58 | stu58 | math   |
| 59 | stu59 | math   |
| 60 | stu60 | math   |
+----+-------+--------+

# 2. 模拟数据库崩溃
systemctl stop mariadb
rm /var/lib/mysql/* -rf

第六步：恢复前准备工作

# 1. 备份二进制日志
cp /app/data/log/log-bin.000001 /app/data/

# 2. 合并备份
# 2.1 合并全量，不回滚日志
innobackupex --redo-only --apply-log /app/data/all/2017-11-11_21-14-54
# 2.2 合并第一次量，不回滚日志
innobackupex --redo-only --apply-log /app/data/all/2017-11-11_21-14-54 --incremental-dir=/app/data/increment/2017-11-11_21-22-04
# 2.2 合并第二次量，不回滚日志
innobackupex --redo-only --apply-log /app/data/all/2017-11-11_21-14-54 --incremental-dir=/app/data/increment/2017-11-11_21-25-28
# 2.3 提交并回滚日志
innobackupex --apply-log /app/data/all/2017-11-11_21-14-54

# 查看合并后的备份LSN编号，检查合并是否成功
cat /app/data/all/2017-11-11_21-14-54/xtrabackup_checkpoints 
backup_type = full-prepared
from_lsn = 0
to_lsn = 1722266
last_lsn = 1722266
compact = 0
recover_binlog_info = 0

# 查看合并后的日志名称及位置
cat /app/data/all/2017-11-11_21-14-54/xtrabackup_binlog_info 
log-bin.000001  681

第七步：恢复

# 恢复
innobackupex --copy-back /app/data/all/2017-11-11_21-14-54

# 修改属主属组
chown -R mysql:mysql /var/lib/mysql

# 启动数据库

第八步：使用二进制日志重放完成最后崩溃前的恢复

# 恢复时关闭二进制日志记录功能
MariaDB> set @@session.sql_log_bin=0;

# 恢复
mysqlbinlog --start-position=681 /app/data/log-bin.000001 > /app/data/log-bin.sql
mysql < /app/data/log-bin.sql

第九步：做一次全量备份

innobackupex -ubkuser -p123 -H172.18.17.21 /app/data/all/

zhubiao

MySQL日志和备份

Table of Contents