MySQL-数据备份

爱吃排骨！

已于 2022-08-29 17:44:39 修改

阅读量124

点赞数 1

分类专栏：运维 Linux 文章标签： mysql 数据库 sql

于 2022-08-27 16:31:17 首次发布

本文链接：https://blog.csdn.net/qq_45729646/article/details/126555912

版权

运维同时被 2 个专栏收录

14 篇文章 0 订阅

订阅专栏

Linux

14 篇文章 0 订阅

订阅专栏

提示：文章写完后，目录可以自动生成，如何生成可参考右边的帮助文档

一、逻辑备份

逻辑备份：备份的是建表，建库，插入等操作所执行的SQL语句，适用于中小型数据库，效率相对较低。
mysqldump 是 MySQL 自带的逻辑备份工具。可以保证数据的一致性和服务的可用性。
本质：导出的是SQL语句文件
优点：不论是什么存储引擎，都可以用MySQLdump备份成sql语句
缺点：速度较慢，导入时可能会出现格式不兼容的突发情况，无法做到增量备份和累计增量备份。
提供三种级别的备份，表级，库级和全库级

语法

语法：
远程备份语法：
mysqldump -h 服务器（IP） -u用户名 -p密码 数据库名 > 备份文件.sql
本地备份语法：
mysqldump -u用户名 -p密码 数据库名 > 备份文件.sql
常用选项
-A, --all-databas #备份所有库
-B, --databases #备份多个数据库
-F, --flush-logs #备份之前刷新binlog日志
--default-character-set #指定导出数据时采用何种字符集，如果数据表不是采用默认的latin1字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。
--no-data，-d #不导出任何数据，只导出数据库表结构。
--lock-tables #备份前，锁定所有数据库表
--single-transaction #保证数据的一致性和服务的可用性。
-f, --force #即使在一个表到处期间得到一个SQL错误，继续。

注意：
使用mysqldump 备份数据库时避免锁表：
对一个正在运行的数据库进行备份慎重！
如果一定要，在服务运行期间备份，可以选择添加 --single-transaction选项（保证备份数据一致性和可行性）

类似执行： mysqldump --single-transaction -u root -p123456 dbname >
mysql.sql

事务和锁表

在这里说一下两个方面：
事务：
在计算机术语中是指访问并可能更新数据库中各种数据项的一个程序执行单元(unit)。
相当于正在执行一条生产链中的其中一个很小的环节（个人理解）。

锁表：
1、锁表发生在insert update 、delete 中
2、锁表的原理是
数据库使用独占式封锁机制，当执行上面的语句时，对表进行锁住，直到发生commite 或者回滚或者退出数据库用户
3、锁表的原因
第一、 A程序执行了对 tableA 的 insert ，并还未 commite（事务提交）时，B程序也对tableA 进行insert
则此时会发生资源正忙的异常就是锁表
第二、锁表常发生于并发而不是并行（并行时，一个线程操作数据库时，另一个线程是不能操作数据库的，cpu 和i/o 分配原则）
4、减少锁表的概率：
减少insert 、update 、delete 语句执行到 commite
（事务提交）之间的时间。具体点批量执行改为单个执行、优化sql自身的非执行速度如果异常对事物进行回滚

备份表

语法：# mysqldump -u root -p'Qf@123456' db1 t1> /dab1.t1.bak

在这里插入图片描述

备份库

语法：# mysqldump -u root -p'Qf@123456' db1 > /dab1.t1.bak

在这里插入图片描述

备份多个库
# 语法：mysqldump  -u root -p1 -B  db1 db2 db3 > /db123.bak

恢复数据库和表

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

binlog日志

binlog日志
即binarylog，是二进制日志文件。它有两个作用，一是增量备份，即只备份新增的内容；二是用于主从复制等，即主节点维护了一个binlog日志文件，从节点从binlog中同步数据。我们可以通过binlog日志恢复数据。

先将之前备份的库和表删除。

在这里插入图片描述
删除数据库
#drop database db1;

恢复库

登录MySQL创建一个数据库
#create database db1;
恢复：
在这里插入图片描述
mysql -uroot -p’Qf@123456’ db1 < /mysql.bak/db1.txt

查看已经恢复

在这里插入图片描述

恢复表

一种是在数据库中进行恢复，一种是在终端进行恢复，都是讲数据库或者表重新导入恢复。

登录数据库将库中的一个表删除
开始恢复:
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

备份及恢复表结构

备份表结构：

语法：mysqldump -uroot -p密码 -d databasename tablename > dump.sql

[root@mysql-server ~]# mysqldump -uroot -p’qf123’ -d company employee5 > /home/back/emp.bak
恢复表结构：
登陆数据库创建一个库
mysql> create database t1;
语法：# mysql -u root -p1 -D db1 < db1.t1.bak
[root@mysql-server ~]# mysql -uroot -p’qf123’ -D t1 < /home/back/emp.bak

在库里数据的导入和导出没有表结构

表的导出和导入只备份表内记录，不会备份表结构，需要通过mysqldump备份表结构，恢复时先恢复表结构，再导入数据
mysql> show variables like “secure_file_priv”;
（保证数据安全做共享）
查询全局变量的一个，其中的值是可以修改的
可以用set 变量=值来进行修改。

修改安全文件目录：
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.

1.导出数据
登陆数据查看数据
mysql> show databases;    #找到test库
mysql> use test   #进入test库
mysql> show tables;  #找到它t3表
mysql> select * from t3 into outfile '/sql/test.t3.bak';
这个是将t3表中的数据导出来，必须导出在刚才创建的安全目录，必须在MySQL配置文件中做定义。
添加修饰的：
mysql> select * from t3 into outfile '/sql/test.t3.bak1' fields terminated by ',' lines terminated by '\n';
注：
fields terminated by ',' ：字段以逗号分割
lines terminated by '\n'：结尾换行

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

二、MySQL bin-log日志数据恢复

开启bin-log日志：

[root@mysql-server ~]# vim /etc/my.cnf
log-bin=/var/log/sql-bin/mylog
server-id=1

在这里插入图片描述
创建目录并修改权限

[root@mysql-server ~]# mkdir /var/log/sql-bin
[root@mysql-server ~]# chown mysql.mysql /var/log/sql-bin
[root@mysql-server ~]# systemctl restart mysqld

在这里插入图片描述
mysql> flush logs; #刷新binlog日志会截断产生新的日志文件

mysql> create table db1.t2(id int); #创建一个表
根据位置恢复
找到要恢复的sql语句的起始位置、结束位置
在这里插入图片描述 [root@mysql-server sql-bin]# mysqlbinlog mylog.000002

测试：

[root@mysql-server ~]# mysql -uroot -p'qf123'
mysql> drop table db1.t2;   #将这个表删除
Query OK, 0 rows affected (0.01 sec)
恢复：
[root@mysql-server ~]# cd /var/log/sql-bin/
[root@mysql-server sql-bin]# mysqlbinlog --start-position 219 --stop-position 315 mylog.000002 | mysql -uroot -p'qf123'
mysql: [Warning] Using a password on the command line interface can be insecure.

在这里插入图片描述

查看：
在这里插入图片描述

三、物理备份

物理备份：直接复制数据库文件，适用于大型数据库环境，不受存储引擎的限制，但不能恢复到MySQL的各个版本。

1、热备(hot backup)

在线备份，数据库处于运行状态，这种备份方法依赖于数据库的日志文件

对应用基本无影响(但是性能还是会有下降,所以尽量不要在主上做备份,在从库上做)

2、冷备(cold backup)

备份数据文件,需要停机，是在关闭数据库的时候进行的

3、温备(warm backup)

针对myisam的备份(myisam不支持热备),备份时候实例只读不可写，数据库锁定表格（不可写入但可读）的状态下进行的
对应用影响很大
通常加一个读锁

物理备份的方式

1.完全备份-----完整备份：

每次都将所有数据（不管自第一次备份以来有没有修改过），进行一次完整的复制，备份后会清除文件的存档属性，方便日后增量备份或者差异备份进行版本比较。

特点：占用空间大，备份速度慢，但恢复时一次恢复到位，恢复速度快。

个人理解：每一次都将数据从头到尾的备份一次。

2.增量备份: 每次备份上一次备份到现在产生的新数据

在第一次完整备份之后，第二次开始每次都将添加了存档属性的文件进行备份，并且在备份之后再把这些存档属性清除。为什么要清除存档属性呢？这就是为了下一次备份的时候判断是否有文件变化，因为用户在每次备份以后修改这些被清除存档属性的文件，存档属性就会自动加上，相当于用户告诉系统，这些文件有变化，你下一次就备份这些文件，其他没有存档属性的就不需要备份，这就是增量备份的工作机制。

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

个人理解：只备份居于上一次备份产生新变化的文件。

3. 差异备份:只备份跟完整备份不一样的

在第一次完整备份之后，第二次开始每次都将所有文件与第一次完整备份的文件做比较，把自第一次完整备份以来所有修改过的文件进行备份，且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），备份自第一次完整备份以来所有的修改过的文件。因此，差异备份在备份完毕之后不需要清除文件的存档属性，因为这些文件和下一次备份没有什么关系，它仅仅和第一次完整备份的数据进行比较（第一次完整备份之后是清除存档属性的）。

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

个人理解：只要和第一次完整备份相比有变化，就备份，但是会出现冗余。

简单的讲，完整备份就是不管三七二十一，每次都把指定的备份目录完整的复制一遍，不管目录下的文件有没有变化；增量备份就是每次将之前（第一次、第二次、直到前一次）做过备份之后有变化的文件进行备份；差异备份就是每次都将第一次完整备份以来有变化的文件进行备份。