MySQL日志，MySQL备份及恢复

日志

1.日志分类：

A.error log（错误日志）：启动，停止，关闭失败报错，rpm安装位置  /var/log/mysql.log

B.General query log(通用查询日志)：所有的查询都记下来

C.binary log（二进制日志）：实现备份，增量备份。只记录改变数据，除了select都记

D.relay log（中继日志）：读取主服务器的binlog，在本地回放，保持一致

E.slow log（慢查询日志）：指导调优，定义某个查询语句，定义超时时间，通过日志提供调优建议给开发人员

F.DDL log：定义语句的日志

error log ：

vim /etc/my.cnf        进入主配置文件，观察日志是否启动

log-error=/var/log/mysqld.log    标记是否启动日志，以及日志位置（如果哪天mysql服务起不来了，来这个日志看看）

binary  log:

1.启动二进制日志   #vim /etc/my.cnf

log_bin          添加该字段。指启动二进制日志呢

server-id=2   集群问题，必须指定该主机的序号，数字随意

#systemctl restart mysqld    重启数据库

2.查看二进制日志文件当前状态

ls /var/lib/mysql/*bin*        查询二进制文件生成完成。

mysqlbinlog -v /var/lib/mysql/localhost-bin.000001

# at 123      找到一个记录位置

 进入数据库，进行数据操作

完成二进制日志启动，查询测试

mysqlbinlog -v /var/lib/mysql/localhost-bin.000001

slow query log   

默认慢查询日志未开启

开启慢查询日志功能    vim  /etc/my.cnf   加入以下两行

slow_query_log=1

long_query_time=3

重启服务器

systemctl  restart mysqld

select benchmark(1000000000,2*3);

数据库备份

1.备份技术：

物理备份/冷备份:直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。(tar,cp,scp)   拷贝数据，  优点快，缺点服务停止。

逻辑备份/热备份:备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库。(mysqldump,mydumper)  效率相对较低

2.备份种类：

完全备份

增量备份：特点：因每次仅备份自上一次备份（注意是上一次，不是第一次）以来有变化的文件，所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢复，恢复时间长。

差异备份：特点：占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后 一次的差异版本(包含所有的差异)，恢复速度介于完整备份和增量备份之间。

MySQL备份工具：percona-xtrabackup

获取软件包：官方网站   Percona – The Database Performance Experts

安装yum 仓库  yum  install mysql-community-libs-compat -y

yum安装 percona-xtrabackup：   yum -y install percona-xtrabackup-24.x86_64

查询安装结果    rpm -ql percona-xtrabackup-24

1.完全备份流程innobackupex --user=root --password='数据库密码' /xtrabackup/full（备份文件存放位置）

查看备份目录。数据库，配置文件，日志文件   ls /xtrabackup/full/

观看二进制日志位置    ls /xtrabackup/full/2021-11-10_12-03-03/

完全恢复流程

A.停止数据库

systemctl  stop  mysqld

B.生成回滚日志innobackupex --apply-log /xtrabackup/full/2021-11-10_12-03-03/

C.恢复文件   innobackupex --copy-back /xtrabackup/full/2021-11-10_12-03-03/

增量备份

准备工作
    使用指定的数据库

 create database testdb;
    use testdb;
    create table test(id int);
     insert into test values (1);

完整备份：周一
    r

m -rf /xtrabackup/*
    date 11150000 （时间建议靠后设置）
    innobackupex --user=root --password='数据库密码' /xtrabackup
    ll /xtrabackup/
2021-11-15_00-04-58

增量备份：周二
  

 date 11160000 （更新时间）
    mysql -uroot -p'数据库密码' -e  'insert into testdb.test values (2)'
    innobackupex --user=root --password='数据库密码'  --incremental /xtrabackup/     --incremental-basedir=/xtrabackup/2021-11-15_00-04-58

        basedir基于周一的备份。   会生成一个今天的。
 

   ls  /xtrabackup/
       2021-11-15_00-04-58  2021-11-16_00-07-27

增量备份：周三
  

 date 11170000
    mysql -uroot -p'数据库密码' -e  'insert into testdb.test values (3)'
    innobackupex --user=root --password='数据库密码'     --incremental /xtrabackup/
--incremental-basedir=/xtrabackup/2021-11-16_00-07-27

        basedir基于周2的备份。
会生成一个今天的。
  

 ls  /xtrabackup/
 2021-11-15_00-04-58  2021-11-16_00-07-27  2021-11-17_00-08-36

增量恢复

停止数据库     systemctl stop mysqld

 清理环境         rm -rf /var/lib/mysql/*

周一
  

 innobackupex --apply-log --redo-only /xtrabackup/2021-11-15_00-04-58  （ 回滚合并）

周二

 innobackupex --apply-log --redo-only /xtrabackup/2021-11-15_00-04-58    --incremental-dir=/xtrabackup/2021-11-16_00-07-27

恢复

innobackupex --copy-back /xtrabackup/2021-11-15_00-04-58  
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld

如果此时想要恢复到周三的状态下

 systemctl stop mysqld
 innobackupex --apply-log --redo-only /xtrabackup/2021-11-15_00-04-58  --incremental-dir=/xtrabackup/2021-11-17_00-08-36
# rm -rf /var/lib/mysql/*
innobackupex --copy-back /xtrabackup/2021-11-15_00-04-58 
chown -R mysql.mysql /var/lib/mysql
systemctl start mysqld
mysql -uroot -p'数据库密码' -e 'select * from testdb.test'

差异备份：做一次完整备份，再做备份的话是在第一次完整备份的基础上做的备份，恢复的时候，做日志回滚，是第一次完整备份+想要恢复到的那天的日志，不需要像增量备份做多次累加

MySQL备份工具  ： mysqldump + binlog

1.优势
  自动记录日志position位置。

可用性，一致性(锁表机制)

2. 语法：mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql
    （参数说明
        库的范围
            -A, --all-databases    所有库
            school    数据库名
            school stu_info t1    是指school数据库的表stu_info、t1
            -B, --databases bbs test mysql    多个数据库
         --single-transaction                    #InnoDB 一致性 服务可用性
        --master-data=1|2    #该选项将会记录binlog的日志位置与文件名并追加到文件中，或添加注释
        高级选项
             --opt         #同时启动各种高级选项
            -R, --routines             #备份存储过程和存储函数
             -F, --flush-logs        #备份之前刷新日志，截断日志。备份之后新binlog。
            --triggers        #备份触发器）

3.备份过程

请准备两套root密码
	密码1
		Wanghy@123
			配置到当前数据库中。
	密码2
		Wanghy@1234
			备用

准备一个新库  testdb1
testdb1.t1
	mysql> select * from testdb1.t1;
+------+
| id   |
+------+
|    1 |
|    2 |
|    3 |
+------+

执行备份   master-data=2 注释掉日志记录

[root@localhost ~]# mysqldump -p'Wanghy@123' --all-databases --single-transaction 
 --master-data=2  --flush-logs  >  /backup/`date +%F-%H`-mysql-all.sql

观察备份细节

vim /backup/2021-11-11-11mysql-all.sql
LOCK TABLES `user` WRITE;  观察各种锁机制，用来保证数据一致性
22 CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.000004', MASTER_LOG_POS=154;
	二进制日志截断位置。第22行

备份后的，数据变更行为
    

在testdb2.t2 中插入数据1，2,3；
创testdb3库
切断二进制日志（重启数据库）在testdb2.t2 中插入数据4；
删testdb3库
在testdb2.t2 中插入数据5；

    

恢复流程

1 备份二进制日志文件
    cp /var/lib/mysql/*bin* ~
2. 停止数据库
    systemctl stop mysqld
3. 清理环境
    rm -rf /var/lib/mysql/*

4.启动数据库

 systemctl start mysqld
    grep 'password' /var/log/mysqld.log
        找密码，再改一下密码。请使用密码2
        -Ll1;DkH%qCe
        mysqladmin -uroot -p'-Ll1;DkH%qCe' password 'Wanghy@1234'
        注意
            语法要求password后面有空格。

5.mysql恢复数据
    

mysql -p'Wanghy@1234' < /backup/2021-11-11-11mysql-all.sql
    mysql -p'Wanghy@1234' -e 'flush privileges'
        请使用备份时密码   此时密码已经变更为  Wanghy@123

此时数据库只有testdb1

6.二进制日志恢复
 

   观察二进制截取记录
        vim /backup/2021-11-11-11mysql-all.sql
        CHANGE MASTER TO MASTER_LOG_FILE='localhost-bin.0000010'
        MASTER_LOG_POS=154

 mysqlbinlog localhost-bin.000002 localhost-bin.000003
 --start-position=154  | mysql -p'Wanghy@123'
    注意后续有多少日志，要跟多少日志名字。

7.此时数据是完整的

8.请思考误删除的问题
    如何保留testdb3库
        [root@localhost ~]#mysqlbinlog  localhost-bin.00004      ......005.....006...... > 1.txt
        删除1.txt中不需要的at（比如test3库是误操作）
        cat  1.txt | mysql -p'QianFeng@123'
    课后题：是否可以通过mysqlbinlog start 和stop 命令解决。

9.关于数据恢复时的多余日志。
    方法一
        原因
            每次还原数据库都会增加日志的体积。但这些都是还原操作。恢复数据库的日志占用了存储空间。
        mysql>  set sql_log_bin =0  
         再mysql>  source   /backup/*.sql
        观察
            二进制日志并没有发生变化。
    方法2
        在备份文件中，加入关闭二进制日志。   （具体操作看磊哥mysql 27节）

记录的导出和导入

记录导出
    1.注意目录权限
        

vim /etc/my.cnf     添加上
        secure-file-priv=/backup

mysql不信任该目录
        chown mysql.mysql /backup     mysql用户没有权限
   导出格式： SELECT... INTO OUTFILE 导出文本文件

  

 mysql> SELECT * FROM testdb1.t1 INTO OUTFILE '/backup/testdb1.t1.txt';

            错误提示1
                mysql> select * from t1 where id1=44  into outfile '/backup/1.sql';
ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv option so it cannot execute this statement
MYSQL的运行需要  安全的文件权限 选项才可保存。
                授权目录
                    secure-file-priv=/backup
            错误提示2
                mysql> select * from t1 where id1=44  into outfile '/abc/1.sql';
ERROR 1 (HY000): Can't create/write to file '/abc/1.sql' (Errcode: 13 - Permission denied)
            定义字段分隔符
                 FIELDS TERMINATED BY '---'    
    其他导出方式：mysql 命令导出文本文件
        mysql -uroot -p'QianFeng@123' -e 'select * from testdb1.t1' > /backup/testdb1.t2.txt
        mysql -u root -p'QianFeng@123' --xml -e 'select * from testdb1.t1' > /backup/testdb1.t3.txt
        mysql -u root -p'QianFeng@123' --html -e 'select * from testdb1.t1' > /backup/index.html

LOAD DATA INFILE 导入文本文件
    注意目录权限
        vim /etc/my.cnf
        secure-file-priv=/backup
            mysql不信任该目录
        chown mysql.mysql /backup
            mysql用户没有权限
    DELETE FROM testdb1.t1;
        删除表的内容
        表的导出和导入只备份表记录，不会备分表结构。因此需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据。
    LOAD DATA INFILE '/backup/testdb1.t1.txt'  INTO TABLE testdb1.t1;
        读取记录备份文件
    select * from testdb1.t1;