189 8069 5689
MySQL备份与恢复
数据备份的类型
1、物理备份
冷备份：在数据库关闭状态下进行的备份操作。
热备份：在数据库处于运行状态时进行备份操作，该方法依赖于数据库的日志文件。
温备份：数据库锁定表格（不可写入但可读）的状态下进行的备份操作。
2、逻辑备份
对数据库逻辑组件（如表等数据库对象）的备份，表示为逻辑数据库结构（create database，create table语句）和
内容（insert语句或分割文本文件）的信息，这种类型的备份适用于可以编辑数据值或表结构较小的数据量，或者在不
同的机器体系结构上重新创建数据
3、从数据库备份策略角度，数据库的备份可分为完整备份、差异备份和增量备份。
完整备份：每次对数据库进行完整的备份，即对整个数据库的备份，数据库结构和文件结构的整体备份，是差异备份和增量备份的基础，
差异备份：备份从上次完整备份之后被修改的所有文件，备份的时间点是上次完整备份起，备份数据量会越来越大，恢复数据时，只需
恢复上次的完整备份与最近的一次差异备份
增量备份：只有在那些上次完整备份或增量备份后被修改的文件才会被备份，以上次完整备份或增量备份的时间为时间点，仅备份这之
间的数据变化因而备份的数据量小，占用的空间小，备份速度快，但恢复时需要将上一次完整备份到最后一次增量备份一次
恢复
数据库常见备份方法
物理冷备份
tar命令
专用备份工具
mysqldump
Mysqlhotcopy
二进制日志
第三方工具备份
Percona XtraBackup
Xtrabackup
成都创新互联公司主营保德网站建设的网络公司,主营网站建设方案,app开发定制,保德h5小程序定制开发搭建,保德网站营销推广欢迎保德等地区企业咨询
一、数据库完全备份操作
1、物理冷备份与恢复；使用tar命令直接打包数据库文件夹，在备份之前需要使用停止mysql服务
备份数据库；创建一个/backup目录作为备份数据库路径，使用tar创建备份文件。整个数据库文件夹备份属于完全备份
[root@localhost ~]# systemctl stop mysqld.service
[root@localhost ~]# mkdir /backup
[root@localhost ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
[root@localhost ~]# ls -l /backup/
总用量 736
-rw-r--r-- 1 root root 751542 8月 15 08:40 mysql_all-2018-08-15.tar.gz
恢复数据库；执行下面操作将数据库文件/usr/local/mysql/data/转移至bak目录下，模拟故障。
[root@localhost ~]# mkdir /bak
[root@localhost ~]# mv /usr/local/mysql/data/ /bak/
执行下面操作可以从备份文件恢复数据
[root@localhost ~]# mkdir restore/
[root@localhost ~]# tar zxf /backup/mysql_all-2018-08-15.tar.gz -C restore/
[root@localhost ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/
[root@localhost ~]# systemctl start mysqld.service2、mysqldump备份与恢复
备份数据库；使用mysqldump命令导出数据时，默认会直接在终端显示，若要保存到文件，还需要结合shell的‘>’重定向
输出操作。命令格式如下。
格式1；备份指定数据库中的部分表
mysqldump [选项] 库名 [表名1] [表名2] ... > /备份路径/备份文件名
格式2；备份一个或多个完整的库（包含其中所有的表）
mysqldump [选项] --databases 库名1 [库名2] ... > /备份路径/备份文件名
格式3；备份mysql数据库中的所有库
mysqldump [选项] --all-databases > /备份路径/备份文件名
其中，常用的选项包括“-u”“-p”，分别用于指定数据库的用户名，密码。
例如，以下操作分别使用格式1，格式2，将mysql库中的user表导出为mysql-user.sql，将整个benet库导出
为benet.sql文件，所有操作均以root用户身份验证
[root@localhost ~]# mysqldump -u root -p mysql user >mysql-user.sql
Enter password: ##mysql中root密码
[root@localhost ~]# mysqldump -u root -p --databases benet > benet.sql
Enter password: ##mysql中root密码
若需要备份整个mysql服务器中的所有库，应使用格式3。当导出的数据量较大时，可以添加“--opt”选项以优化执行速度。
例如；执行以下操作将创建备份文件all-data.sql，其中包含MySQL服务器中的所有库。
[root@localhost ~]# mysqldump -u root -p --opt --all-databases > all-databases.sql
Enter password: ##mysql中root密码
查看备份文件
通过mysqldump工具导出的SQL脚本文本文件，其中“/*...*/”部分或以--开头的行表示注释信息。使用grep、less、cat等文本工
具都可以查看文件内容。
例如；执行以下操作可以过滤出benet.sql脚本中的数据库操作语句
[root@localhost ~]# grep -v "^--" benet.sql | grep -v "^/" | grep -v "^$"
CREATE DATABASE /*!32312 IF NOT EXISTS*/ `benet` /*!40100 DEFAULT CHARACTER SET utf8 */;
USE `benet`;
DROP TABLE IF EXISTS `users`;
CREATE TABLE `users` (
`user_name` char(15) NOT NULL,
`user_passwd` char(100) DEFAULT '',
PRIMARY KEY (`user_name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
LOCK TABLES `users` WRITE;
INSERT INTO `users` VALUES ('lisi','123123'),('zhangsan','*E56A114692FE0DE073F9A1DD68A00EEB9703F3F1');
UNLOCK TABLES;
恢复数据库；使用mysqldump命令导出的SQL备份脚本，在需要恢复时可以通过mysql命令对其进行导入操作，
命令格式如下。
mysql [选项] [库名] [表名] < /备份路径/备份文件名
当备份文件中只包含表的备份，而不包含创建库的语句时，执行导入操作时必须制定库名，且目标库必须存在。
例如；以下操作可以从备份文件mysql-user.sql中将表导入test库。其中“-e”选项是用于指定连接MySQL执行后的命令，
命令执行后自动退出。
[root@localhost ~]# mysql -u root -p test < mysql-user.sql
Enter password: ##mysql中root密码
[root@localhost ~]# mysql -u root -p -e ' show tables from test;'
Enter password: ##mysql中root密码
+----------------+
| Tables_in_test |
+----------------+
| user |
+----------------+
若备份文件中已经包含完整的数据库信息，则执行导入操作时无需指定库名。
例如指定一下操作可以从备份文件benet.sql恢复到benet库。
[root@localhost ~]# mysql -u root -p -e ' drop database benet;' ##删除benet库，模拟数据库故障
Enter password:
[root@localhost ~]# mysql -u root -p -e ' show databases;' ##查看到benet库已经不存在了
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| test |
+--------------------+
[root@localhost ~]# mysql -u root -p < ~/benet.sql
Enter password:
[root@localhost ~]# mysql -u root -p -e ' show databases;'
Enter password:
+--------------------+
| Database |
+--------------------+
| information_schema |
| benet |
| mysql |
| performance_schema |
| test |
+--------------------+ 二、增量备份
特点；与完整备份不同，增量备份没有重复数据，备份量不大，时间短，但恢复麻烦，需要上次完整备份及完整备份之
后的增量备份才能恢复，而且要对所有增量备份进行逐个反推恢复，mysql中没有提供直接增量备份的方法，可以通过
MySQL提供的我二进制日志（binary logs）间接实现增量备份。
二进制日志
要进行MySQL的增量备份，首先要开启二进制功能，开启MySQL的二进制日志功能的实现方法有很多种，最常用的是在MySQL
配置文件的mysqld项下加入“log-bin=/文件路径/文件名”前缀，如；log-bin=/usr/local/mysql/mysql-bin,然后重启mysqld.service
服务，就可以看到指定路径下的二进制文件文件了。默认情况下，二进制日志文件的扩展名是一个六位的数字，如；mysql-bin.000001
[root@localhost ~]# vim /etc/my.cnf ##修改配置文件，启用二进制功能
log-bin=/usr/local/mysql/mysql-bin
:wq
[root@localhost ~]# systemctl restart mysqld.service
[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 120 8月 15 10:35 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 34 8月 15 10:35 /usr/local/mysql/mysql-bin.indexMySQL增量恢复
常用的增量恢复方法有三种；一般恢复，基于位置的恢复，基于时间点的恢复
一般恢复；将所有备份的二进制日志内容全部恢复，格式命令如下
mysqlbinlog [--no-defaults] 增量备份文件 | mysql -u root -p
基于位置的恢复；数据库管理员在操作数据库时可能在同一时间点既有错误的操作也有正确的操作，通过基于位置进行恢复可以更加精准，
命令格式如下所示。
格式一；恢复数据到指定位置
mysqlbinlog --stop-position='459' 二进制日志 | mysql -u root -p ******
格式二；从指定的位置开始恢复数据
mysqlbinlog --start-position='459' 二进制日志 | mysql -u root -p ******
基于时间点的恢复；跳过某个发生错误的时间点实现数据恢复，而基于时间点的恢复可以分成三种情况。
格式1；从日志开头截止到某个时间点恢复
mysqlbinlog [--no-defaults] --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户 -p 密码
格式2；从某个时间点到日志结尾恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户 -p 密码
格式3；从某个时间点到某个时间点恢复
mysqlbinlog [--no-defaults] --start-datetime='年-月-日 小时:分钟:秒' --stop-datetime='年-月-日 小时:分钟:秒' 二进制日志 | mysql -u 用户 -p 密码
下面我们通过一个企业级的备份案例讲解增量备份与恢复的具体操作。三、企业备份案例
需求描述；北京移动公司的用户信息数据库为client，用户资费数据表为user_info，每周需要进行完整备份，每天需要进行增量备份，
新增加的用户信息表如下
-----------------------------------------------------------------------
××× 姓名 性别 用户ID号 资费
-----------------------------------------------------------------------
000006 张三 男 016 10
000007 李四 女 017 19
000008 王五 女 018 23
000009 赵六 男 019 37
000010 孙七 男 020 36
-----------------------------------------------------------------------一般恢复
1）添加数据库，表，录入信息
[root@localhost ~]# mysql -u root -p
Enter password:
mysql> create database client;
Query OK, 1 row affected (0.00 sec
mysql> use client;
Database changed
mysql> create table user_info(××× char(20) not null,姓名 char(20) not null,性别 char(4),用户ID号 char(10) not null,资费 int(10));
Query OK, 0 rows affected (0.01 sec)
mysql> insert into user_info values ('000006','张三','男','016','10');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user_info values ('000007','李四','女','017','91');
Query OK, 1 row affected (0.01 sec)
mysql> insert into user_info values ('000008','王五','女','018','23');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
+-----------+--------+--------+-------------+--------+
3 rows in set (0.00 sec)2）先进行一次完整备份
为方便验证二进制日志增量的恢复能力，在插入三行数据的client.user_info表进行一次完全备份，然后在Linux系统命令下
执行"mysqladmin -uroot -p123123 flush-logs"命令或在"mysql>"命令提示符下执行"flush logs"生成新的二进制日志。
[root@localhost ~]# mysqldump -uroot -p client user_info >/mysql_bak/client_userinfo-$(date +%F).sql
Enter password:
[root@localhost ~]# ls /mysql_bak/
client_userinfo-2018-08-15.sql
[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 1252 8月 15 11:50 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 120 8月 15 11:50 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql 68 8月 15 11:50 /usr/local/mysql/mysql-bin.index3）继续录入新的数据并进行增量备份
新增两行数据，并执行"mysqladmin -uroot -p flush-logs"命令刷新二进制日志进行增量备份，
如此；二进制日志文件mysql-bin.000003中就会保留插入两个用户数据的操作
mysql> use client;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> insert into user_info values('000009','赵六','男','019','37');
Query OK, 1 row affected (0.00 sec)
mysql> insert into user_info values('000010','孙七','男','020','36');
Query OK, 1 row affected (0.00 sec)
mysql> select * from user_info;
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
| 000010 | 孙七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
5 rows in set (0.00 sec)
[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw---- 1 mysql mysql 1252 8月 15 11:50 /usr/local/mysql/mysql-bin.000001
-rw-rw---- 1 mysql mysql 679 8月 15 12:02 /usr/local/mysql/mysql-bin.000002
-rw-rw---- 1 mysql mysql 120 8月 15 12:02 /usr/local/mysql/mysql-bin.000003
-rw-rw---- 1 mysql mysql 102 8月 15 12:02 /usr/local/mysql/mysql-bin.index4）模拟误操作删除user_info表
[root@localhost ~]# mysql -uroot -p -e 'drop table client.user_info;'
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'select * from client.user_info;'
Enter password:
ERROR 1146 (42S02) at line 1: Table 'client.user_info' doesn't exist
5）恢复操作
在执行恢复操作时，需要先执行完整恢复，在进行增量恢复。
[root@localhost ~]# mysql -u root -p client < /backup_bak/client_userinfo-2018-08-18.sql
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
+-----------+--------+--------+-------------+--------+
一般恢复
[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
| 000010 | 孙七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
基于位置恢复
[root@localhost ~]# mysqlbinlog --no -defaults /usr/local/mysql/mysql-bin.000002 ##查看二进制文件中的内容以便恢复
mysqlbinlog: unknown option '--no'[root@localhost ~]# mysqlbinlog --no-defaults /usr/local/mysql/mysql-bin.000002
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=1/;
/!40019 SET @@session.max_insert_delayed_threads=0/;
/!50003 SET @OLD_COMPLETION_TYPE=@@COMPLETION_TYPE,COMPLETION_TYPE=0/;
DELIMITER /!/;
# at 4
#180818 14:16:21 server id 1 end_log_pos 120 CRC32 0xf20e69c2 Start: binlog v 4, server v 5.6.36-log created 180818 14:16:21
BINLOG '
tbl3Ww8BAAAAdAAAAHgAAAAAAAQANS42LjM2LWxvZwAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
AAAAAAAAAAAAAAAAAAAAAAAAEzgNAAgAEgAEBAQEEgAAXAAEGggAAAAICAgCAAAACgoKGRkAAcJp
DvI=
'/!/;
# at 120
#180818 14:17:53 server id 1 end_log_pos 203 CRC32 0x122cbd0b Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1534573073/!/;
SET @@session.pseudo_thread_id=4/!/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/!/;
SET @@session.sql_mode=1075838976/!/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/!/;
/!\C utf8 //!/;
SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33/!/;
SET @@session.lc_time_names=0/!/;
SET @@session.collation_database=DEFAULT/!/;
BEGIN
/!/;
# at 203
#180818 14:17:53 server id 1 end_log_pos 345 CRC32 0xa4c70118 Query thread_id=4 exec_time=0 error_code=0
use client/!/;
SET TIMESTAMP=1534573073/!/;
insert into user_info values('000009','赵六','男','019','37')
/!/;
# at 345
#180818 14:17:53 server id 1 end_log_pos 376 CRC32 0x7599a729 Xid = 46
COMMIT/!/;
# at 376
#180818 14:18:04 server id 1 end_log_pos 459 CRC32 0x1ff3f3f2 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1534573084/!/;
BEGIN
/!/;
# at 459
#180818 14:18:04 server id 1 end_log_pos 601 CRC32 0xea02a0b2 Query thread_id=4 exec_time=0 error_code=0
SET TIMESTAMP=1534573084/!/;
insert into user_info values('000010','孙七','男','020','36')
/!/;
# at 601
#180818 14:18:04 server id 1 end_log_pos 632 CRC32 0xc9b66df7 Xid = 47
COMMIT/!/;
# at 632
#180818 14:19:23 server id 1 end_log_pos 679 CRC32 0x2bf94bf2 Rotate to mysql-bin.000003 pos: 4
DELIMITER ;
# End of log file
ROLLBACK / added by mysqlbinlog /;
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE/;
/!50530 SET @@SESSION.PSEUDO_SLAVE_MODE=0/;
基于位置恢复：方法一
[root@localhost ~]# mysqlbinlog --no-defaults --stop-position='459' /usr/local/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
+-----------+--------+--------+-------------+--------+
基于位置恢复：方法二
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password:
[root@localhost ~]# mysql -u root -p client < /backup_bak/client_userinfo-2018-08-18.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no-defaults --start-position='459' /usr/local/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000010 | 孙七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+
基于时间点
方法一：从日志开头截止到某个时间点恢复
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password:
[root@localhost ~]# mysql -u root -p client < /backup_bak/client_userinfo-2018-08-18.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime='2018-08-18 14:18:04' /usr/local/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000009 | 赵六 | 男 | 019 | 37 |
+-----------+--------+--------+-------------+--------+
方法三：时间点到时间点
[root@localhost ~]# mysql -u root -p -e 'drop table client.user_info;'
Enter password:
[root@localhost ~]# mysql -u root -p client < /backup_bak/client_userinfo-2018-08-18.sql
Enter password:
[root@localhost ~]# mysqlbinlog --no-defaults --start-datetime='2018-08-18 14:18:04' --stop-datetime='2018-08-18 14:19:23' /usr/local/mysql/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost ~]# mysql -u root -p -e 'select * from client.user_info;'
Enter password:
+-----------+--------+--------+-------------+--------+
| ××× | 姓名 | 性别 | 用户ID号 | 资费 |
+-----------+--------+--------+-------------+--------+
| 000006 | 张三 | 男 | 016 | 10 |
| 000007 | 李四 | 女 | 017 | 91 |
| 000008 | 王五 | 女 | 018 | 23 |
| 000010 | 孙七 | 男 | 020 | 36 |
+-----------+--------+--------+-------------+--------+