MySQL数据备份

小趴菜吖

于 2025-04-17 18:30:03 发布

阅读量551

点赞数 17

文章标签： mysql 数据库

本文链接：https://blog.youkuaiyun.com/posetr/article/details/147310171

版权

一，为什么要进行数据备份

备份：能够防止由于机械故障以及人为误操作带来的数据丢失，例如将数据库文件保存在了其它地方。
冗余：数据有多份冗余，但不等备份，只能防止机械故障带来的数据丢失，例如主备模式、数据库集群。

MySQL数据备份需要重视的内容

备份内容  databases　Binlog   my.conf
所有备份数据都应放在非数据库本地，而且建议有多份副本。
测试环境中做日常恢复演练，恢复较备份更为重要。

备份过程中必须考虑因素：

1. 数据的一致性
2. 服务的可用性

二，备份类型

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

1、热备(hot backup)

在线备份，数据库处于运行状态

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

2、冷备(cold backup)

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

3、温备(warm backup)

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

2.逻辑备份：备份的是建表、建库、插入等操作所执行SQL语句（DDL DML DCL），适用于中小型数据库，效率相对较低。

3.物理和逻辑备份的区别

-	逻辑备份	物理备份
备份方式	备份数据库建表、建库、插入sql语句	备份数据库物理文件
优点	备份文件相对较小,只备份表中的数据与结构	恢复速度比较快
缺点	恢复速度较慢(需要重建索引等)	备份文件相对较大(备份表空间,包含数据与索引)
对业务影响	I/O负载加大	I/O负载加大
代表工具	mysqldump	ibbackup、xtrabackup，mysqlbackup

三，物理备份

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

每次对数据进行完整的备份，即对整个数据库的备份、
数据库结构和文件结构的备份，保存的是备份完成时刻的数据库，
是差异备份与增量备份的基础。

优点：备份与恢复操作简单方便，恢复时一次恢复到位，恢复速度快
缺点：占用空间大，备份速度慢

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

只有那些在上次完全备份或者增量备份后被修改的文件才会被备份。
以上次完整备份或上次的增量备份的时间为时间点，仅备份这之间的数据变化.

特点：因而备份的数据量小，占用空间小，备份速度快。 
但恢复时，需要从上一次的完整备份起按备份时间顺序，逐个备份版本进行恢复，
恢复时间长，如中间某次的备份数据损坏，将导致数据的丢失。

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

备份那些自从第一次次完全备份之后被修改过的所有文件，
备份的时间起点是从第一次次完整备份起，
且以后每次备份都是和第一次完整备份进行比较（注意是第一次，不是上一次），
备份自第一次完整备份以来所有的修改过的文件。备份数据量会越来越大。

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

总结：

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

四，逻辑备份

一，mysqldump 本身为客户端工具:

远程备份数据库语法: 
# mysqldump  -h 服务器  -u用户名  -p密码   数据库名  > 备份文件.sql
本地备份语法: 
# mysqldump  -u用户名  -p密码   数据库名  > 备份文件.sql

常用参数

-A, --all-databases #备份所有库

-B, --databases  #备份多个数据库

-d, --no-data  #不导出任何数据，只导出数据库表结构。

1.备份库

备份一个库：相当于将这个库里面的所有表全部备份。
语法: # mysqldump -u root -p1 db1 > /db1.bak
[root@mysql-server ~]# mysqldump -uroot -p'qf123' company > /home/back/company.bak
备份多个库：
语法：mysqldump  -u root -p1 -B  db1 db2 db3 > /db123.bak
[root@mysql-server ~]# mysqldump -uroot -p'qf123' -B company testdb > /home/back/company_testdb.bak
备份所有的库：
语法：# mysqldump  -u root -p1 -A > /alldb.bak
[root@mysql-server ~]# mysqldump -uroot -p'qf123' -A > /home/back/allbase.bak

2.备份表

语法: # mysqldump -u root -p1 db1  t1 > /db1.t1.bak
[root@mysql-server ~]# mkdir /home/back  #创建备份目录
[root@mysql-server ~]# mysqldump -uroot -p'qf123' company employee5 > /home/back/company.employee5.bak
备份多个表：
语法: mysqldump -u root -p1 db1 t1   t2 > /db1.t1_t2.bak
[root@mysql-server ~]# mysqldump -uroot -p'qf123' company new_t1 new_t2  > /home/back/company.new_t1_t2.bak

3.备份表结构：

语法：mysqldump  -uroot -p123456 -d database table > 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

二，数据恢复

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

恢复库和表

登陆mysql创建一个库
mysql> create database company;
恢复：
[root@mysql-server ~]# mysql -uroot -p'qf123' company < /home/back/company.bak
#新创建的库必须和原库名一致

恢复表
方法一，进入mysql数据库恢复
mysql> use 所删除表的所在库
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备份表结构，恢复时先恢复表结构，再导入数据。

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.
systemctl restart mysqld

二，导出数据

登陆数据查看数据
mysql> show databases;    #找到test库
mysql> use test   #进入test库
mysql> show tables;  #找到它t3表
mysql> select * from t3 into outfile '/sql/test.t3.bak';
#把t3表的数据导出到/sql下的test.t3.bak文件。

三，数据的导入

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