MySQL学习记录三

一、逻辑备份

**本质：**导出的是SQL语句文件
**优点：**不论是什么存储引擎，都可以用mysqldump备份SQL语句
**缺点：**速度较慢，导入时可能会出现格式不兼容的突发情况，无法做增量备份和累计增量备份。
提供三种级别的备份，表级，库级和全库级
**逻辑备份：**备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。
如何保证数据一致，在备份的时候进行锁表会自动锁表。锁住之后再备份。

远程备份语法: # mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql
本地备份语法: # mysqldump  -u用户名  -p密码   数据库名  > 备份文件.sql
#备份文件后缀无特别要求,可根据需求保存到相关路径
#mysqldump命令是MySQL自带，不需要另外安装

1.常用备份选项

-A, --all-databases
备份所有库
-B, –databases bbs test mysql
备份多个数据库
–no-data，-d
不导出任何数据，只导出数据库表结构。

备份表

备份单个表
语法: # mysqldump -u root -p1 db1  t1 > /db1.t1.bak	
备份多个表：
语法: mysqldump -u root -p1 db1 t1   t2 > /db1.t1_t2.bak

备份库

备份一个库：相当于将这个库里面的所有表全部备份。
语法: # mysqldump -u root -p1 db1 > /db1.bak
备份多个库（-B）：
语法：mysqldump  -u root -p1 -B  db1 db2 db3 > /db123.bak
备份所有的库：
语法：# mysqldump  -u root -p1 -A > /alldb.bak

恢复数据库和表

为保证数据一致性，应在恢复数据之前停止数据库对外的服务,停止binlog日志 因为binlog使用binlog日志恢复数据时也会产生binlog日志。
恢复库

#恢复的时候要注意库要存在
mysql -uroot -p1 company < /db1.bak

恢复表

方式一:
mysql> set sql_log_bin=0;   #停止binlog日志（可选）
Query OK, 0 rows affected (0.00 sec)
mysql> source /home/back/company.employee5.bak;  -------加路径和备份的文件 
恢复方式二：
# mysql -u root -p1  db1  < db1.t1.bak
                     库名    备份的文件路径

备份及恢复表结构

1.备份表结构：
语法：mysqldump  -uroot -p123456 -d databasename tablename > dump.sql
恢复表结构：
登陆数据库创建一个库
mysql> create database t1;
语法：# mysql -u root -p1 -D db1  < db1.t1.bak

在库里的数据导入导出（不含表结构）

mysql> show variables like "secure_file_priv";  ----查询导入导出的目录。（保证数据安全做共享） ,如果没有目录需要手工创建
修改安全文件目录：
1.创建一个目录：mkdir  路径目录
[root@mysql-server ~]# mkdir /sql
2.修改权限
[root@mysql-server ~]# chown mysql.mysql /sql
3.编辑配置文件：
vim /etc/my.cnf
在[mysqld]里追加
secure_file_priv=/sql
4.重新启动mysql.

#导出数据，需要放到安全目录中
mysql> select * from t3 into outfile '/sql/test.t3.bak';
添加修饰的：
mysql> select * from t3 into outfile '/sql/test.t3.bak1' fields terminated by ',' lines terminated by '\n';
注：
fields terminated by ',' ：字段以逗号分割
lines terminated by '\n'：结尾换行

#导入数据，先将原来表里面的数据清除掉，只保留表结构
mysql> delete from t3;
mysql> load data infile '/sql/test.t3.bak' into table t3;
#如果将数据导入别的表，需要创建这个表并创建相应的表结构。

二、binlog日志恢复数据

需要开启binlog日志功能，用于记录数据库的修改
修改/etc/my.conf,添加server_id和指定binlog日志目录，如下图
注意修改完成需要重启数据库，否则不生效

mysql> flush logs;				#刷新binlog日志，使下面的语句存放到下一个binlog日志中
mysql> create table testdb.t1(id int);

[root@mysql-1 mysql]# mysqlbinlog mysql-bin.000001			#查看binlog日志内容

[root@mysql-1 mysql]# mysqlbinlog --start-position 219 --stop-position  321 mysql-bin.000002 |mysql -uroot -p123		#恢复语句

三、物理备份

物理备份： 直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到不同的MySQL版本。
完全备份-----完整备份：
每次都将所有数据（不管自第一次备份以来有没有修改过），进行一次完整的复制，备份后会清除文件的存档属性，方便日后增量备份或者差异备份进行版本比较。
**特点：**占用空间大，备份速度慢，但恢复时一次恢复到位，恢复速度快。

**增量备份: **每次备份上一次备份到现在产生的新数据
​ 在第一次完整备份之后，第二次开始每次都将添加了存档属性的文件进行备份，并且在备份之后再把这些存档属性清除。为什么要清除存档属性呢？这就是为了下一次备份的时候判断是否有文件变化，因为用户在每次备份以后修改这些被清除存档属性的文件，存档属性就会自动加上，相当于用户告诉系统，这些文件有变化，你下一次就备份这些文件，其他没有存档属性的就不需要备份，这就是增量备份的工作机制。
**特点：**因每次仅备份自上一次备份（注意是上一次，不是第一次）以来有变化的文件，所 以备份体积小，备份速度快，但是恢复的时候，需要按备份时间顺序，逐个备份版本进行恢 复，恢复时间长。

**差异备份：**只备份跟完整备份不一样的
在第一次完整备份之后，第二次开始每次都将所有文件与第一次完整备份的文件做比较，把自第一次完整备份以来所有修改过的文件进行备份，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。因此，差异备份在备份完毕之后不需要清除文件的存档属性，因为这些文件和下一次备份没有什么关系，它仅仅和第一次完整备份的数据进行比较（第一次完整备份之后是清除存档属性的）。
**特点：**占用空间比增量备份大，比完整备份小，恢复时仅需要恢复第一个完整版本和最后一次的差异版本，恢复速度介于完整备份和增量备份之间。
简单的讲，完整备份就是不管三七二十一，每次都把指定的备份目录完整的复制一遍，不管目录下的文件有没有变化；增量备份就是每次将之前（第一次、第二次、直到前一次）做过备份之后有变化的文件进行备份；差异备份就是每次都将第一次完整备份以来有变化的文件进行备份。

热备份
数据库启动同时给客户端提供服务的情况下
冷备份
数据库要关掉或者不能给客户端提供服务
使用xtrabackup工具进行物理备份，官网：www.percona.com

完整备份

创建备份目录：
[root@mysql-server ~]# mkdir /xtrabackup/full -p
备份：
[root@mysql-server ~]# innobackupex --user=root --password='123' /xtrabackup/full

#恢复过程
可以查看一下:
[root@mysql-server ~]# cd /xtrabackup/full/
[root@mysql-server full]# ls
2022-04-11_20-47-49
=======================================================
完全备份恢复流程
1. 停止数据库
2. 清理环境
3. 重演回滚－－> 恢复数据
4. 修改权限
5. 启动数据库

1.关闭数据库：
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
[root@mysql-server ~]# rm -rf /var/log/mysqld.log			#可选操作
[root@mysql-server ~]# rm -rf /var/log/mysql-slow/slow.log  #可选操作
2.恢复之前的验证恢复:
[root@mysql-server ~]# innobackupex --apply-log /xtrabackup/full/2022-04-11_20-47-49
3.确认数据库目录：
恢复之前需要确认配置文件内有数据库目录指定，不然xtrabackup不知道恢复到哪里
# cat /etc/my.cnf
[mysqld]
datadir=/var/lib/mysql
4.恢复数据：
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/full/2022-04-11_20-47-49
5.修改权限：
[root@mysql-server ~]# chown mysql.mysql  /var/lib/mysql  -R
启动数据库:
[root@mysql-server ~]# systemctl start mysqld

增量备份

mysql> insert into testdb.t1 values(2);  #第一次增加数据
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2022-04-11_20-47-49/
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2022-04-11_20-47-49  2022-04-11_20-53-49    ---相当于上次的增量备份

mysql> insert into testdb.t1 values(3);   #第二次增加数据
[root@mysql-server ~]# innobackupex --user=root --password='123' --incremental /xtrabackup/ --incremental-basedir=/xtrabackup/2022-04-11_20-53-49/      #基于前一天的备份为目录
[root@mysql-server ~]# cd /xtrabackup/
[root@mysql-server xtrabackup]# ls
2022-04-11_20-47-49  2022-04-11_20-53-49  2022-04-11_21-01-31   ---相当于上次的增量备份

增量备份恢复流程
1. 停止数据库
2. 清理环境
3. 依次重演回滚redo log－－> 恢复数据
4. 修改权限
5. 启动数据库
6. binlog恢复
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
依次重演回滚redo log:
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2022-04-11_20-47-49
按顺序重演回滚
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2022-04-11_20-47-49 --incremental-dir=/xtrabackup/2022-04-11_20-53-49
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2022-04-11_20-47-49 --incremental-dir=/xtrabackup/2022-04-11_21-01-31/
恢复数据:
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2022-04-11_20-47-49/
修改权限
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld

差异备份
就是特殊的增量备份，每次仅在上次的全量备份的基础上备份

语法: # innobackupex --user=root --password=888 --incremental /xtrabackup --incremental-basedir=/xtrabackup/完全备份目录

差异备份恢复流程
1. 停止数据库
2. 清理环境
3. 重演回滚redo log－－> 恢复数据
4. 修改权限
5. 启动数据库
6. binlog恢复
停止数据库
[root@mysql-server ~]# systemctl stop mysqld
[root@mysql-server ~]# rm -rf /var/lib/mysql/*
1.恢复全量的redo log
语法: # innobackupex --apply-log --redo-only /xtrabackup/完全备份目录
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2022-04-11_20-47-49/
2.恢复差异的redo log
语法:# innobackupex --apply-log --redo-only /xtrabackup/完全备份目录 --incremental-dir=/xtrabacku/某个差异备份
[root@mysql-server ~]# innobackupex --apply-log --redo-only /xtrabackup/2022-04-11_20-47-49/ --incremental-dir=/xtrabackup/2022-04-11_20-53-49/
3.恢复数据
语法:# innobackupex --copy-back /xtrabackup/完全备份目录
[root@mysql-server ~]# innobackupex --copy-back /xtrabackup/2022-04-11_20-47-49/
修改权限：
[root@mysql-server ~]# chown -R mysql.mysql /var/lib/mysql
[root@mysql-server ~]# systemctl start mysqld