数据对于企业的重要性，不言而喻。所以在使用Mysql数据库的过程中，经常需要使用到数据库的备份和导出功能，非常重要！！！

文章目录

一：备份的分类1：物理备份 文件2：逻辑备份 二：热备份的重要性三：常见的备份方法3.1：物理冷备3.2：专用备份工具mydump或mysqlhotcopy3.3：启用二进制日志进行增量备份3.4：第三方工具备份 四：MySQL完全备份4.1：什么是完全备份？4.11：优点：4.12：缺点 五：数据库完全备份分类5.1：物理冷备份与恢复5.2：mysqldump备份与恢复5.3：对所有库进行完全备份5.4：mysqldump备份数据表 六：恢复数据库6.1：使用mysqldump导出的脚本，可使用导入的方法6.2：使用source恢复数据库的步骤 七：MYSQL增量备份7.1：使用mysqldump命令进行完全备份存在的问题7.2：增量备份优缺点7.3：如何实现MySQL增量备份？7.4：增量备份的方法7.41：一般恢复

一：备份的分类

1：物理备份 文件

物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)

冷备份(脱机备份) :是在关闭数据库的时候进行的热备份(联机备份) :数据库处于运行状态，依赖于数据库的日志文件温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作

2：逻辑备份

逻辑备份:对数据库逻辑组件(如: 表等数据库对象)的

备份

从数据库的备份策略角度，备份可分为

完全备份：每次对数据进行完整的备份差异备份：备份那些自从上次完全备份之后被修改过的文件增量备份：只有那些在上次完全备份或者增量备份后被修改的文件才会被备份

二：热备份的重要性

在生产环境中，数据的安全性至关重要

任何数据的丢失都可能产生严重的后果:

2.1：造成数据丢失的原因

程序错误人为操作错误运算错误磁盘故障灾难（如火灾、地址）和盗窃

三：常见的备份方法

3.1：物理冷备

备份时数据库处于关闭状态，直接打包数据库文件备份速度快，恢复时也是最简单的

3.2：专用备份工具mydump或mysqlhotcopy

mysqldump常用的逻辑备份工具mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表

3.3：启用二进制日志进行增量备份

进行增量备份，需要刷新_二进制日志

3.4：第三方工具备份

免费的MySQl热备份软件Percona XtraBackup

四：MySQL完全备份

4.1：什么是完全备份？

完全备份是对整个数据库的备份、数据库结构和文件结构的备份

完全备份保存的是备份完成时刻的数据库

完全备份是增量备份的基础

4.11：优点：

安全性高

备份与恢复操作简单方便

4.12：缺点

数据存在大量的重复

占用大量的备份空间，空间利用率低

备份与恢复时间长

五：数据库完全备份分类

5.1：物理冷备份与恢复

关闭MySQL数据库

使用tar命令直接打包数据库文件夹

直接替换现有MySQL目录即可

mysql> use tom;Database changedmysql> create table chengji (name VARCHAR(10),point INT(10));Query OK, 0 rows affected (0.02 sec)mysql> desc chengji;+-------+-------------+------+-----+---------+-------+| Field | Type | Null | Key | Default | Extra |+-------+-------------+------+-----+---------+-------+| name | varchar(10) | YES || NULL | || point | int(10)| YES || NULL | |+-------+-------------+------+-----+---------+-------+2 rows in set (0.01 sec)mysql> insert into chengji values('xiaowang',77),('xiaoli',75);Query OK, 2 rows affected (0.01 sec)Records: 2 Duplicates: 0 Warnings: 0mysql> select * from chengji-> ;+----------+-------+| name| point |+----------+-------+| xiaowang | 77 || xiaoli | 75 |+----------+-------+2 rows in set (0.00 sec)

先关闭数据库服务，再打包备份

[root@server3 ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/tar: 从成员名中删除开头的“/”[root@server3 ~]# cd /backup/[root@server3 backup]# ll总用量 1376-rw-r--r--. 1 root root 1406517 10月 13 14:48 mysql_all--10-13.tar.gz

将原来的数据移走到备份文件夹中，解压刚才备份的tar包到/restore目录下，再移动到mysql服务的文件夹中

[root@server1 ~]# mkdir bak[root@server1 ~]# mv /usr/local/mysql/data/ /bak #将数据库的文件移动至/bak文件夹中[root@server1 ~]# mkdir restore[root@server1 ~]# tar zxf /backup/mysql_all--10-23.tar.gz -C restore[root@server1 ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/ #将之前备份的文件移动至mysql服务的文件夹中

重启mysql服务，登录mysql，查看数据是否恢复

mysql> use tom;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> select * from chengji;+----------+-------+| name| point |+----------+-------+| xiaowang | 77 || xiaoli | 75 |+----------+-------+2 rows in set (0.00 sec)

5.2：mysqldump备份与恢复

MySQL自带的备份工具，可方便实现对MySQL的备份

可以将指定的库、表导出为SQL脚本

使用命令mysql导入备份的数据

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###备份所有数据库mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库mysqldump -u root -p auth > auth-$(data +%F).sql ###备份auth数据库mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构

mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || myadm || mysql || performance_schema || student || sys|| tom|+--------------------+7 rows in set (0.00 sec)[root@server3 opt]# mysqldump -u root -p tom > /opt/tom.sqlEnter password: [root@server3 opt]# lstom.sql # 导出的备份文件

5.3：对所有库进行完全备份

[root@server3 opt]# mysqldump -uroot -pabc123 --all-databases > /backup/all.sqlmysqldump: [Warning] Using a password on the command line interface can be insecure.

5.4：mysqldump备份数据表

musqldump可针对库内特定的表进行备份使用mysqldump备份表的操作

mysqldump -u 用户名 -p 【密码】【选项】选项库名 表名 > /备份路径/备份文件名

示例：

mysql> use tom;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> show tables;+---------------+| Tables_in_tom |+---------------+| chengji |+---------------+1 row in set (0.00 sec)mysql> select * from chengji;+----------+-------+| name| point |+----------+-------+| xiaowang | 77 || xiaoli | 75 |+----------+-------+2 rows in set (0.00 sec)#复制tom表 name字段 张三内容 生成一张新表ppmysql> create table pp as select * from chengji where name='xiaowang';Query OK, 1 row affected (0.03 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> show tables;+---------------+| Tables_in_tom |+---------------+| chengji || pp |+---------------+2 rows in set (0.00 sec)#新生成表mysql> select *from pp;+----------+-------+| name| point |+----------+-------+| xiaowang | 77 |+----------+-------+1 row in set (0.00 sec)

[root@server3 ~]# mysql -u root -p123123 chengji pp > /opt/pp.sqlmysql: [Warning] Using a password on the command line interface can be insecure.[root@server3 ~]# ls /optpp.sql

六：恢复数据库

6.1：使用mysqldump导出的脚本，可使用导入的方法

source命令【作用于mysql模式下】

mysql命令【作用于于linux模式下】

6.2：使用source恢复数据库的步骤

登录到mysql数据库

执行source备份sql脚本的路径

source恢复的示例

MYSQL[(none)]> source /backup/all-data.sql

模拟删除表

mysql> use tom;Database changed#删除表mysql> drop table chengji;Query OK, 0 rows affected (0.02 sec)mysql> drop table pp;Query OK, 0 rows affected (0.01 sec)

进行恢复

mysql> use tom;Database changedmysql> use tom;Database changedmysql> show tables;+---------------+| Tables_in_tom |+---------------+| tom |+---------------+1 row in set (0.00 sec)mysql> select * from tom;+----+----------+----------+| id | name| address |+----+----------+----------+| 1 | zhangsan | hangzhou |+----+----------+----------+1 row in set (0.00 sec)mysql> insert into tom (name,address) values('lisi','wuxi');Query OK, 1 row affected (0.00 sec)mysql> select * from tom;+----+----------+----------+| id | name| address |+----+----------+----------+| 1 | zhangsan | hangzhou || 2 | lisi| wuxi|+----+----------+----------+2 rows in set (0.00 sec)mysql> create table pp as select * from tom where name='zhangsan';Query OK, 1 row affected (0.02 sec)Records: 1 Duplicates: 0 Warnings: 0mysql> show tables;+---------------+| Tables_in_tom |+---------------+| pp || tom |+---------------+2 rows in set (0.00 sec)mysql> select * from pp;+----+----------+----------+| id | name| address |+----+----------+----------+| 1 | zhangsan | hangzhou |+----+----------+----------+1 row in set (0.00 sec)mysql> Ctrl-C -- exit!Aborted[root@server3 opt]# mysqldump -u root -p tom pp > /opt/pp.sqlEnter password: [root@server3 opt]# ls /opt/all.sql opt.sql pp.sql rh tom.tom[root@server3 opt]# mysqlWelcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 11Server version: 5.6.36-log Source distributionCopyright (c) 2000, , Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> drop table tom;ERROR 1046 (3D000): No database selectedmysql> use tom;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> drop table tom;Query OK, 0 rows affected (0.01 sec)mysql> drop table pp;Query OK, 0 rows affected (0.00 sec)```javamysql> show tables;Empty set (0.00 sec)#恢复mysql> source /opt/all.sql;..省略内容mysql> use tom;Database changedmysql> show tables;+---------------+| Tables_in_tom |+---------------+| tom |+---------------+1 row in set (0.00 sec)mysql> source /opt/pp.sql;mysql> show tables;+---------------+| Tables_in_tom |+---------------+| pp || tom |+---------------+2 rows in set (0.00 sec)mysql> select * from pp;+----+----------+----------+| id | name| address |+----+----------+----------+| 1 | zhangsan | hangzhou |+----+----------+----------+1 row in set (0.00 sec)#已经恢复 这边我们是恢复所有数据库 #也可以单独的对标进行备份恢复

七：MYSQL增量备份

7.1：使用mysqldump命令进行完全备份存在的问题

备份数据中有重复数据

备份时间与恢复时间长

7.2：增量备份优缺点

优点：

没有重复数据，效率高，空间利用率最大化

备份量不大，时间短

缺点：

恢复麻烦：需要上次完全备份及完全备份之后所有的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复

安全性较低

7.3：如何实现MySQL增量备份？

MySQL没有提供直接的增量备份方法

可以通过 MySQL提供的二进制日志（ binary logs）间接实现增量备份

二进制日志保存了所有更新或者可能更新数据库的操作

二进制日志在启动MySQL服务器后开始记录，并在文件达到

max_binlog_size所设置的大小或者接收到flush logs命令后重新

创建新的日志文件

只需定时执行flush logs方法重新创建新的日志，生成二进制文

件序列，并及时把这些日志保存到安全的地方就完成了一个时间

段的增量备份

7.4：增量备份的方法

7.41：一般恢复

mysqbinlog [--no-defaults] 增量备份文件 | mysql -u 用户名 -p

#显示表ccmysql> select * from cc;+------+-------+| id | name |+------+-------+| 1 | tom || 2 | jerry |+------+-------+2 rows in set (0.00 sec)[root@localhost ~]# mysqldump -u root -p --all-databases > all-data.sqlEnter password: [root@localhost ~]# ll总用量 48464-rw-r--r--. 1 root root 780979 11月 4 17:26 all-data.sql[root@localhost ~]# vi /etc/f#末行添加log_bin=/usr/local/mysql/data/mysql_bin #开启增量备份[root@localhost ~]# cd /usr/local/mysql/data/[root@localhost data]# systemctl restart mysqld.service [root@localhost data]# fib_logfile1 server3.erribdata1mysql mysql-bin.indextestib_logfile0 mysql-bin.000001 performance_schema tom[root@localhost data]# mysqladmin -uroot -p flush-logs#查看日志[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002mysql> insert into cc values(3,'qiaozhi');Query OK, 1 row affected (0.01 sec)mysql> delete from cc where id =1;Query OK, 1 row affected (0.00 sec)mysql> insert into cc values(4,'suxi');Query OK, 1 row affected (0.01 sec)mysql> select * from cc;+------+---------+| id | name |+------+---------+| 2 | jerry || 3 | qiaozhi || 4 | suxi |+------+---------+3 rows in set (0.00 sec)[root@localhost data]# mysqladmin -uroot -p flush-logsEnter password: [root@localhost data]# f ib_logfile0 mysql_bin.000001 performance_schemabb ib_logfile1 mysql_bin.000002 sysib_buffer_pool ibtmp1 mysql_bin.000003ibdata1 mysql mysql_bin.index[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002

mysql> drop table cc; #删表Query OK, 0 rows affected (0.02 sec)[root@localhost data]# mysql -u root -p bb < /root/all-data.sql #恢复表mysql> select * from cc;+------+-------+| id | name |+------+-------+| 1 | tom || 2 | jerry |+------+-------+2 rows in set (0.00 sec)#恢复[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='-11-04 17:32:41' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -pEnter password: [root@localhost data]# mysqlbinlog --no-defaults --start-datetime='-11-04 17:32:53' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -pEnter password: mysql> select * from ;+------+---------+| id | name |+------+---------+| 1 | tom|| 2 | jerry || 3 | qiaozhi || 4 | suxi |+------+---------+4 rows in set (0.00 sec)#已经跳过错误操作

位置点恢复

mysql> delete from cc where id=1;Query OK, 1 row affected (0.01 sec)mysql> delete from cc where id=2;Query OK, 1 row affected (0.01 sec)[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查询该二进制日志内容

[root@localhost data]# mysqlbinlog --no-defaults --stop-position='765210' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -pEnter password: [root@localhost data]# mysqlbinlog --no-defaults --start-position='765589' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -pEnter password: [root@localhost data]# mysqlmysql> select * from ;+------+---------+| id | name |+------+---------+| 1 | tom|| 2 | jerry || 3 | qiaozhi || 4 | suxi |+------+---------+4 rows in set (0.00 sec)

如果对你有用的话，收藏一下吧

如果觉得《学习MySQL备份一篇就够了！！！（完全备份 增量备份 备份恢复）》对你有帮助，请点赞、收藏，并留下你的观点哦！