mysql日志

一、mysql错误日志

#作用：查看mysql启动时错误

#总结：
1. 默认开启的
2. 路径在 $datadir/data/ 下面，以'主机名'.err命令
3. 配置文件设置
    [root@db01 ~]# vim /etc/my.cnf
    log_error=mysql.err
    log_error=/tmp/mysql.err
4. 查看错误日志路径
    mysql> show variables like '%log_error%';
    [root@db01 ~]# mysqladmin variables | grep -w 'log_error'

二、一般查询日志

#作用：记录执行过的sql语句（不论是否提交）

#总结：
1. 默认关闭的
2. 配置开启一般查询日志
    [root@db01 ~]# vim /etc/my.cnf
    general_log=on
    general_log_file=/usr/local/mysql/data/mysql.log
3. 保存位置是 /usr/local/mysql/data/mysql.log
4. 查看查询日志位置
    mysql> show variables like '%general%';

三、二进制日志

#作用：
1. 记录我们对数据库增删改的操作；
2. 数据恢复；

#总结：
1. 默认是不开启的
2. 配置开启binlog
    [root@db01 ~]# vim /etc/my.cnf
    server_id=1
    log_bin=mysql-bin
3. 查看binlog保存路径
    mysql> show variables like '%log_bin%';
4. 查看binlog文件
    [root@db01 ~]# ll /usr/local/mysql/data/
    mysql> show binary logs;
5. 刷新binlog
    mysql> flush logs;
6. 删除binlog
    1）删除七天前的
    2）保留三天
    3）删除指定binlog之前的
    4）重置binlog

1.binlog工作模式

#查看工作模式
mysql> show variables like 'binlog_format';

1. STATEMENT：语句模式
    作用：记录数据增，删，改的sql语句
    查看：[root@db01 data]# mysqlbinlog mysql-bin.000001
    优点：
        1）容易理解，易读
        2）相对于其他模式，占用磁盘较小

2. row：行级模式
    作用：记录数据库数据的变化过程
    修改binlog模式：
        [root@db01 ~]# vim /etc/my.cnf
        binlog_format=row
    优点：严谨，安全
    查看：[root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002

3. 混合模式

2.binlog恢复数据

1. 查看binlog，找到要恢复的数据
    [root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002
    mysql> show binlog events in 'mysql-bin.000002';
2. 提取要恢复的数据
    [root@db01 data]# mysqlbinlog --start-position=120 --stop-position=798 /usr/local/mysql/data/mysql-bin.000002 > /tmp/row.sql
3. 导入要恢复的数据
    set sql_log_bin=0;
    source /tmp/row.sql;

四、慢查询日志

#作用：是将影响数据库查询速度，查询缓慢的语句记录到日志，以便于针对性优化

#总结：
1. 默认关闭的
2. 配置开启慢日志
    [root@db01 ~]# vim /etc/my.cnf
    [mysqld]
    #指定是否开启慢查询日志
    slow_query_log = 1
    #指定慢日志文件存放位置（默认在data）
    slow_query_log_file=/usr/local/mysql/data/slow.log
    #设定慢查询的阀值(默认10s)
    long_query_time=3
    #不使用索引的慢查询日志是否记录到日志
    log_queries_not_using_indexes
3. 查看慢日志内容
    cat /application/mysql/data/slow.log
    [root@db01 tmp]# mysqldumpslow -s c -t 10 /usr/local/mysql/data/slow.log
        参数说明:
        -s: 是表示按照何种方式排序，c、t、l、r分别是按照记录次数、时间、查询时间、返回的记录数来排序，ac、at、al、ar，表示相应的倒叙；
        -t: 是top n的意思，即为返回前面多少条的数据；
        -g: 后边可以写一个正则匹配模式，大小写不敏感的；

mysql备份与恢复

一、数据备份

1.备份的原因

1. 数据重要
2. 备份就是为了恢复
3. 减少数据的丢失

2.备份类型

1. 冷备份: 先停库库和服务，然后备份

2. 热备份：不停库和服务，然后备份，备份同时也不阻止数据的写入和读取

3. 温备份: 不停库和服务，然后备份，备份同时会阻止数据的写入和读取（锁表）

3.备份的方式

1）逻辑备份

1.binlog 方式 （搭配mysqlbinlog）
2.into outfile（属于逻辑备份）
    1）配置数据库授权目录
        [root@db01 data]# vim /etc/my.cnf
        [mysqld]
        secure-file-priv=/tmp
    2）数据库使用outfile导出
        mysql> select * from world.city into outfile '/tmp/2.txt';

3.mysqldump
4.replication（属于逻辑备份，并不是真正的备份方法）

2）物理备份

1.备份data目录
    1）将数据data目录转移
    2）将要备份的数据库data目录打包
    3）将打包的数据目录传到备份的机器
    4）解压备份的数据目录到备份的数据库
    5）登录数据库查看
    6）通过binlog导出新增数据并导入（导入前确定备份数据库工作模式与源数据库相同）

2.Xtrabackup

3）备份类型

1.全备：将全部数据备份
2.增量备分：
    1）恢复数据需要合并多次
    2）每个文件都是新数据，不会浪费磁盘空间
3.差异备份：
    1）恢复数据只需要合并一次
    2）每个文件都包含重复内容，比较占用磁盘

二、mysqldump

#mysql客户端：
    mysql
    mysqladmin
    mysqldump

1.mysqldump常用参数

1. 不加参数：(常用于备份单个表)
    1）备份指定库
        [root@db01 ~]# mysqldump world > /tmp/world.sql
    2）备份指定库下的指定表
        [root@db01 ~]# mysqldump world city > /tmp/city.sql
    3）备份指定库下的指定多个表
        [root@db01 ~]# mysqldump world city country > /tmp/country.sql

2. -A：== --all-databases  全库备份
    [root@db01 ~]# mysqldump -A > /tmp/full.sql
3. -B：指定库备份，只能指定库的名字
    [root@db01 tmp]# mysqldump -B world data > /tmp/ku.sql
4. -F：备份同时刷新binlog
    [root@db01 ~]# mysqldump -A -F > /tmp/full.sql （一般不使用，会刷新出多个binlog）
5. --master-data=2：备份时打点，记录备份时刻位置点
    [root@db01 data]# mysqldump -B data --master-data=2  > /tmp/data.sql
    --master-data值：
        0：不打点（不如不加参数）
        1：打点不注释（一般使用在主从扩展机器的时候）
            扩展从数据库，数据同步之后做主从，可以不写master_log_pos和master_log_file了
            只需要写：
                change master to
                master_host='172.16.1.51',
                master_user='rep',
                master_password='123',
                master_port=3307;
                #master_log_pos=120,
                #master_log_file='mysql-bin.000012';
        2：打点并注释（日常全库备份时）
6. --single-transaction：快照备份 （搭配--master-data可以做到热备）
    [root@db01 data]# mysqldump -A --master-data=2 --single-transaction > /tmp/full.sql
7. -d：仅表结构
8. -t：仅数据
9. -R：备份存储过程和函数数据
10. --triggers：备份触发器数据

#备份完整命令
    mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction > /tmp/full.sql

11. gzip命令，系统命令
    mysqldump -uroot -p123 -A -R --triggers --master-data=2 --single-transaction | gzip > /tmp/full.sql.gz
    #压缩包导入数据
    [root@db01 data]# zcat /tmp/full.sql.gz | mysql
    [root@db01 data]# zcat /tmp/full.sql.gz | mysql -uroot -p123
    #备份时可以加入时间戳
    [root@db01 data]# mysqldump -B exchange -R --triggers --master-data=2 --single-transaction --skip-lock-tables| gzip > /tmp/full$(date +%F).sql.gz

三、企业案例

1.背景

1.正在运行的网站系统，MySQL数据库，数据量25G，日业务增量10-15M。

2.备份策略：每天23：00，计划任务调用mysqldump执行全备脚本

3.故障时间点：上午10点开发人员误删除一个核心业务表，如何恢复？

2.数据恢复思路

1.停业务避免数据的二次伤害
2.找一个临时的库，恢复前一天的全备
3.截取前一天23：00到第二天10点误删除之前的binlog，恢复到临时库
4.测试可用性和完整性
5.开启业务前的两种方式
    1）直接使用临时库顶替原生产库，前端应用割接到新库
    2）将误删除的表单独导出，然后导入到原生产环境
6.开启业务

3.故障模拟

1）准备全备的数据

#创建库
mysql> create database backup;
#切换库
mysql> use backup
#创建两个表
mysql> create table test1 select * from world.city;
mysql> create table test2 select * from world.country;
#全备
[root@db01 tmp]# mysqldump -A --master-data=2 --single-transaction > /tmp/full.sql

2）模拟23:00到第二天10:00新增数据

#创建库
mysql> create table test3 select * from world.city;
mysql> create table test4 select * from world.country;
#修改数据
mysql> update test4 set code='CHN' where 1=1;
#查看数据
mysql> show tables;
mysql> select * from test4;

3）模拟10:00删除数据

#删除核心表
mysql> drop table test4；
Query OK, 0 rows affected (0.01 sec)

mysql> drop table test2;
Query OK, 0 rows affected (0.01 sec)

4.恢复数据

1）先停库

[root@db01 ~]# systemctl stop mysql

2）准备新的数据库

3）通过binlog找到23:00到第二天10:00之间新增的数据

1.找到起始位置点
    查看全备时打点的位置点  120
2.找到结束位置点
    [root@db01 data]# mysqlbinlog --base64-output=decode-rows -vvv mysql-bin.000002 > 1.txt
    [root@db01 data]# vim 1.txt
    /DROP            211318
3.截取数据
    [root@db01 data]# mysqlbinlog --start-position=120 --stop-position=211318 mysql-bin.000002 > /tmp/huifu.sql

4）将全备数据和增备数据传到新库

[root@db01 data]# scp /tmp/full.sql 172.16.1.52:/tmp/  
[root@db01 data]# scp /tmp/huifu.sql 172.16.1.52:/tmp/

5）将全备和增备数据恢复到数据库

[root@db02 mysql]# mysql < /tmp/full.sql
[root@db02 mysql]# mysql < /tmp/huifu.sql

6）确认数据

#查看数据
mysql> show tables;
mysql> select * from test4;

7）恢复生产环境提供业务

1.将恢复的表导出，导入到生产库（如果核心业务表很小）
    1）导出指定表
        [root@db02 mysql]# mysqldump backup test2 test4 > /tmp/test.sql
    2）将sql传输到生产库
        [root@db02 mysql]# scp /tmp/test.sql 172.16.1.51:/tmp/
    3）指定库导入表
        [root@db01 data]# mysql backup < /tmp/test.sql

2.应用服务修改数据库配置连接到新库（如果核心业务表很大）

四、物理备份之 Xtrabackup

1.上传或者下载xtrabackup包

1.下载
    #下载epel源
    wget -O /etc/yum.repos.d/epel.repo  https://mirrors.aliyun.com/repo/epel-6.repo
    #安装依赖
    yum -y install perl perl-devel libaio libaio-devel perl-Time-HiRes perl-DBD-MySQL
    #下载Xtrabackup
    wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/redhat/6/x86_64/percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

2.上传
    [root@db01 ~]# rz percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

2.安装xtrabackup

[root@db01 ~]# yum localinstall -y percona-xtrabackup-24-2.4.4-1.el6.x86_64.rpm

[root@db01 ~]# xtrabackup
[root@db01 ~]# innobackupex

3.xtrabackup特性

1.对于非innodb表（比如myisam）是直接锁表cp数据文件，属于一种温备。
2.对于innodb的表（支持事务），不锁表，cp数据页最终以数据文件方式保存下来，并且把redo和undo一并备走，属于热备方式。
3.备份时读取配置文件/etc/my.cnf

4.xtrabackup全量备份

1.创建备份目录
    [root@db01 ~]# mkdir /backup
2.全备
    [root@db01 ~]# innobackupex --user=root /backup/full
    #如果有密码
    [root@db01 ~]# innobackupex --user=root --password=123 /backup/full
3.查看文件
    [root@db01 ~]# ll /backup/full/
    total 12316
    drwxr-x--- 10 root root      269 Apr 26 14:59 2020-04-26_14-59-48
    drwxr-x--- 10 root root      269 Apr 26 15:02 2020-04-26_15-02-39
4.去除时间戳全备
    [root@db01 ~]# innobackupex --user=root --no-timestamp /backup/full
    [root@db01 ~]# innobackupex --user=root --no-timestamp /backup/full-$(date +%F)
5.再次查看文件
    [root@db01 backup]# ll
    total 0
    drwxr-x--- 10 root root 269 Apr 26 15:07 full
    drwxr-x--- 10 root root 269 Apr 26 15:12 full-2020-04-26
    [root@db01 backup]# cd full
    [root@db01 full]# ll
    total 12316
    drwxr-x--- 2 root root      156 Apr 26 15:07 backup
    -rw-r----- 1 root root      418 Apr 26 15:07 backup-my.cnf
    drwxr-x--- 2 root root       54 Apr 26 15:07 data
    -rw-r----- 1 root root 12582912 Apr 26 15:07 ibdata1
    drwxr-x--- 2 root root     4096 Apr 26 15:07 mysql
    drwxr-x--- 2 root root     4096 Apr 26 15:07 performance_schema
    drwxr-x--- 2 root root       86 Apr 26 15:07 row
    drwxr-x--- 2 root root       86 Apr 26 15:07 statement
    drwxr-x--- 2 root root       20 Apr 26 15:07 test
    drwxr-x--- 2 root root      144 Apr 26 15:07 world
    -rw-r----- 1 root root       21 Apr 26 15:07 xtrabackup_binlog_info
    -rw-r----- 1 root root      113 Apr 26 15:07 xtrabackup_checkpoints
    -rw-r----- 1 root root      468 Apr 26 15:07 xtrabackup_info
    -rw-r----- 1 root root     2560 Apr 26 15:07 xtrabackup_logfile

5.xtrabackup恢复数据

1.连接数据库，删除库
    mysql> drop database performance_schema;
2.停止数据库
    [root@db01 ~]# systemctl stop mysql
3.将redo进行重做，已提交的写到数据文件，未提交的使用undo回滚，模拟CSR的过程
    [root@db01 ~]# innobackupex --user=root --apply-log /backup/full
    [root@db01 ~]# innobackupex --user=root --password=123 --apply-log /backup/full
4.全备恢复数据
    [root@db01 ~]# cd /usr/local/mysql/
    [root@db01 mysql]# mv data data.bak
    [root@db01 mysql]# cp -r /backup/full ./data
    [root@db01 mysql]# chown -R mysql.mysql data
    [root@db01 ~]# systemctl start mysql
    #或者
    [root@db01 mysql]# innobackupex --copy-back /backup/full
    [root@db01 mysql]# chown -R mysql.mysql data
    [root@db01 ~]# systemctl start mysql
5.登录数据库查看
    mysql> show databases;

6.xtrabackup增量备份

1.基于上一次全备进行增量
2.增量备份无法单独恢复，必须基于全备进行恢复
3.所有增量必须要按顺序合并到全备当中

7.xtrabackup增量备份实践

1.先进行全备
    [root@db01 ~]# innobackupex --user=root --no-timestamp /backup/full
2.模拟新增数据
    mysql> create database xtra;
    mysql> use xtra;
    mysql> insert into test1 values(1),(2),(3);
    mysql> create table test2(id int);
    mysql> insert into test2 values(1),(2),(3);
3.增量备份
    [root@db01 ~]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/full/ /backup/first
    [root@db01 ~]# innobackupex --user=root --password=123 --no-timestamp --incremental --incremental-basedir=/backup/full/ /backup/first
    --incremental: 开启增量备份，使用增量备份
    --incremental-basedir: 指定针对哪个备份进行增量备份
4.确定是否衔接
    [root@db01 ~]# cat /backup/full/xtrabackup_checkpoints 
    backup_type = full-backuped
    from_lsn = 0
    to_lsn = 5297752

    [root@db01 ~]# cat /backup/first/xtrabackup_checkpoints 
    backup_type = incremental
    from_lsn = 5297752            #该值必须与全备的to_lsn一致
    to_lsn = 5308907

8.再次增量备份

1.模拟新增数据
    mysql> create database tow;
    mysql> create database two;
    mysql> use two;
    mysql> create table test1(id int);
    mysql> insert into test1 values(1),(2),(3);
2.再次增量备份
    [root@db01 ~]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/first /backup/two
3.再次查看衔接
    [root@db01 ~]# cat /backup/two/xtrabackup_checkpoints 
    backup_type = incremental
    from_lsn = 5308907                #该值必须与上一次备份的to_lsn一致
    to_lsn = 5314510

9.差异备份

#增备文件指定的上一次备份只要是全备，那他就是差异备份
[root@db01 ~]# innobackupex --user=root --no-timestamp --incremental --incremental-basedir=/backup/full/ /backup/chayi

#查看衔接
[root@db01 ~]# cat /backup/chayi/xtrabackup_checkpoints 
backup_type = incremental
from_lsn = 5297752                    #差异备份，该值一定等于上一次全备的to_lsn
to_lsn = 5314510

10.xtrabackup增量恢复数据

1.准备恢复
    1）full+first+two
    2）需要将first和two按顺序合并到full中
    3）分步骤进行--apply-log
    4）删除数据库
        mysql> drop database backup;
2.处理全量数据，只做redo，不做undo
    [root@db01 ~]# innobackupex --user=root --password=123 --apply-log --redo-only /backup/full/
    全备的 checkpoints  to_lsn = 5297752
3.处理第一次增量数据，合并到ful，只做redo，不做undo
    [root@db01 ~]# innobackupex --apply-log --redo-only --incremental-dir=/backup/first /backup/full/
    再次查看全备的 checkpoints  to_lsn = 5308907
4.处理第二次增量数据，合并到full，既做redo，也做undo
    [root@db01 ~]# innobackupex --apply-log --incremental-dir=/backup/two /backup/full/
    再次查看全备的 checkpoints  to_lsn = 5314510
5.最后再把整体的full数据模拟redo和undo
    [root@db01 ~]# innobackupex --apply-log /backup/full/
6.停库
    [root@db01 ~]# systemctl stop mysql
7.移走data目录
    [root@db01 ~]# cd /usr/local/mysql/
    [root@db01 mysql]# mv data data.bak
8.恢复数据
    [root@db01 mysql]# innobackupex --copy-back /backup/full/
    [root@db01 mysql]# chown -R mysql.mysql data
9.查看数据
    [root@db01 mysql]# systemctl start mysql
    mysql> show databases;