MySQL数据库的备份可以采用很多种方式,如直接打包数据库文件(物理冷备份)、专用备份工具(mysqldump)、二进制日志增量备份、第三方工具备份等。
1、物理冷备份物理冷备份时需要在数据库处于关闭状态下,能够较好地保证数据库的完整性。物理冷备份一般用于非核心业务,这类业务一般都允许中断,物理冷备份的特点就是速度快,恢复时也是最为简单。
2、专用备份工具mysqldump或mysqlhotcopymysqldump程序和mysqlhotcopy都可以做备份。mysqqldump是客户端常用逻辑备份程序,能够产生一组被执行以再现原始数据库对象定义和表数据的SQL语句。它可以转储一个到多个MySQL数据库,对其进行备份或传输到远程SQL服务器。mysqldump更为通用,因为它可以备份各种表。mysqlhotcopy仅适用于某些存储引擎。
3、通过启用二进制日志进行增量备份MySQL支持增量备份,进行增量备份时必须启用二进制日志。二进制日志文件为用户提供复制,对执行备份点后进行的数据库更改所需的信息进行恢复。如果进行增量备份(包含自上次完全备份或增量备份以来发生的数据修改),需要刷新二进制日志。
四、数据库完全备份操作 1、备份前准备[root@CentOS01 ~]# mysqladmin -u root password !--mysql数据库设置密码-- New password: !--输入密码-- Confirm new password: !--确认密码-- [root@centos01 ~]# mysql -uroot -ppwd@123 !--登录MySQL数据库-- mysql create database benet; !--创建benet数据库-- mysql use benet; !--切换到benet数据库创建表-- mysql create table 一班学生成绩 (姓名 char(3),班级 char(5),学号 char(8),语文 char(3), 数学char(3),英语 char(3),理综 char(3), primary key (学号)); !--创建表,表名字为一班学生成绩,第一列名字是姓名,第二列名字为班级,第三列名字 为学号,第四列名字为语文,第五列名字为数学,第六列名字为英语,第七列名字为理综-- mysql insert into 一班学生成绩 value ('张三','一班','20170822','110','105','92','235'); !--表中插入数据-- mysql insert into 一班学生成绩 value ('李四','一班','20170820','95','115','110','260'); !--表中插入数据-- mysql insert into 一班学生成绩 value ('王五','一班','20170818','95','103','108','270'); !--表中插入数据-- mysql insert into 一班学生成绩 value ('赵六','一班','20170816','100','109','112','265'); !--表中插入数据-- mysql select * from benet.一班学生成绩; !--查看一班学生成绩表中所有数据--
物理冷备份一般用tar命令直接打包数据库文件夹,而在进行备份之前需要使用 systemctl stop mysqld 命令关闭mysql服务。
1)备份数据库创建一个/bak目录作为备份数据存储路径,使用tar创建备份文件。整个数据库文件夹备份属于完全备份。
[root@centos01 ~]# systemctl stop mysqld !--停止mysql服务-- [root@centos01 ~]mkdir /bak/ !--创建存储备份目录-- [root@centos01 ~]# tar zcf /bak/mysql_all-$(date +%F).mysql.gz /usr/local/mysql/data/ !--直接tar打包数据库文件-- [root@centos01 ~]# ls /bak/ !--查看备份的数据-- -rw-r--r-- 1 root root 766598 10月 31 03:57 /bak/mysql_all-2019-10-31.mysql.gz2)恢复数据库
[root@centos01 ~]mkdir test !--创建恢复数据目录-- [root@centos01 ~]# tar zxvf /bak/mysql_all-2019-10-31.mysql.gz -C ./test/ !--解压缩备份数据到恢复目录-- [root@centos01 data]# cd /usr/local/mysql/data/ !--进入数据原始位置-- [root@centos01 data]# rm -rf ./* !--删除数据-- [root@centos01 ~]# cd ./test/usr/local/mysql/data/ !--切换到恢复目录-- [root@centos01 date]#mv ./* /usr/local/mysql/data/ !--将恢复目录数据恢复到原始位置-- [root@centos01 ~]# systemctl start mysqld !--启动mysql服务--3、mysqldump备份与恢复
通过mysqldump命令可以将指定的库、表或全部的库导出为SQL脚本,便于该命令在不同版本的MySQL服务器上使用。
1)备份恢复所有数据库[root@centos01 ~]# mysqldump -uroot -ppwd@123 --opt --all-databases ./test/benet_databases.sql !--备份所有库,opt选项是优化执行速度-- [root@centos01 ~]# mysql -uroot -p !--登录数据库-- Enter password: !--数据密码-- mysql show databases; !--查看所有数据库-- +--------------------+ | Database | +--------------------+ | information_schema | | benet | | mysql | | performance_schema | | test | +--------------------+ mysql drop database benet; !--删除benet数据库-- mysql show databases; !--查看数据库是否删除-- +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | performance_schema | | test | +--------------------+ [root@centos01 ~]# mysql -u root -p ./test/benet_databases.sql Enter password: !--恢复所有数据库-- mysql show databases; !--查看数据库是否恢复-- +--------------------+ | Database | +--------------------+ | information_schema | | benet | | mysql | | performance_schema | | test | +--------------------+ mysql source ./test/benet_databases.sql !--也可以通过这种方法恢复误删除的数据库--2)备份恢复数据库中的表
[root@centos01 ~]# mysqldump -uroo t -ppwd@123 benet 一班学生成绩 ./test/benet_一班学生成绩.sql !--备份数据库下的表-- [root@centos01 ~]# mysql -uroot -p !--登录数据库-- Enter password: !--输入密码-- mysql use benet; !--切换到benet数据库-- mysql drop table 一班学生成绩; !--删除一班学生成绩表-- mysql show tables; !--查看表是否删除-- Empty set (0.00 sec) [root@centos01 ~]# mysql -uroot -p benet ./test/benet_一班学生成绩.sql !--恢复误删除的表-- [root@centos01 ~]# mysql -uroot -p !--登录数据库-- Enter password: !--输入密码-- mysql use benet; !--切换到benet数据库-- Database changed mysql show tables; !--查看误删除的表是否恢复-- +--------------------+ | Tables_in_benet | +--------------------+ | 一班学生成绩 | +--------------------+ 1 row in set (0.00 sec)五、MySQL增量备份与恢复
使用mysqldump进行完全备份,备份的数据中有重复数据,备份时间与恢复时间过长。而增量备份就是备份自上一次备份之后增加或改变的文件或内容。
1、MySQL增量备份的特点与完全备份不同,增量备份没有重复数据,备份量不大,时间短;但其恢复麻烦,需要上次完全备份及完全备份之后所有的增量备份才能恢复,而且要对所有增量备份进行逐个反推恢复。可以通过MySQL提供的二进制日志间接实现增量备份。
2、MySQL增量备份与恢复二进制日志保存了所有更新或者可能更新数据库的操作。二进制日志在启动MySQL服务器后开始记录,并在文件达到二进制日志所设置的??大值或者接收到flush logs命令后重新创建新的日志文件,生成二进制文件序列,并及时把这些日志保存到安全的存储位置,即可完成一个时间段的增量备份。
要进行MySQL的增量备份,首先要开启二进制日志功能,开启MySQL的二进制日志功能的实现方法如下:
[root@centos01 ~]# vim /etc/my.cnf !--进入MySQL配置文件-- ....... !--此处省略部分内容-- log-bin=mysql-bin !--开启二进制日志功能-- [root@centos01 ~]# systemctl restart mysqld !--重启MySQL服务-- [root@centos01 ~]# ls -l /usr/local/mysql/data/ ...... !--此处省略部分内容-- -rw-rw---- 1 mysql mysql 27299 10月 31 00:00 mysql-bin.000001 -rw-rw---- 1 mysql mysql 1031892 10月 31 00:00 mysql-bin.000002 -rw-rw---- 1 mysql mysql 1574 10月 31 14:13 mysql-bin.000003 -rw-rw---- 1 mysql mysql 507535 11月 1 09:37 mysql-bin.000004 -rw-rw---- 1 mysql mysql 507229 11月 1 09:40 mysql-bin.000005 -rw-rw---- 1 mysql mysql 95 11月 1 09:37 mysql-bin.index drwx------ 2 mysql mysql 4096 10月 31 00:00 performance_schema drwxr-xr-x 2 mysql mysql 20 10月 30 23:56 test1)增量备份
[root@centos01 ~]# mysqladmin -uroot -ppwd@123 flush-logs !--刷新二进制日志-- [root@centos01 ~]# ls -l /usr/local/mysql/data/ !--查看二进制日志文件-- ...... !--此处省略部分内容-- -rw-rw---- 1 mysql mysql 27299 10月 31 00:00 mysql-bin.000001 -rw-rw---- 1 mysql mysql 1031892 10月 31 00:00 mysql-bin.000002 -rw-rw---- 1 mysql mysql 1574 10月 31 14:13 mysql-bin.000003 -rw-rw---- 1 mysql mysql 507535 11月 1 09:37 mysql-bin.000004 -rw-rw---- 1 mysql mysql 507272 11月 1 09:49 mysql-bin.000005 -rw-rw---- 1 mysql mysql 107 11月 1 09:49 mysql-bin.000006 -rw-rw---- 1 mysql mysql 114 11月 1 09:49 mysql-bin.index drwx------ 2 mysql mysql 4096 10月 31 00:00 performance_schema drwxr-xr-x 2 mysql mysql 20 10月 30 23:56 test [root@centos01 ~]# mysql -uroot -ppwd@123 !--登录mysql数据库-- mysql use benet; !--切换到benet数据库-- mysql insert into 一班学生成绩 value ('李宁','二班','20170824','92','98','105','235'); !--录入新的数据-- Query OK, 1 row affected (0.01 sec) mysql insert into 一班学生成绩 value ('陈铭','二班','20170826','111','107','96','204'); !--录入新的数据-- Query OK, 1 row affected (0.00 sec) mysql select *from 一班学生成绩; !--查看新数据是否录入-- +--------+--------+----------+--------+--------+--------+--------+ | 姓名 | 班级 | 学号 | 语文 | 数学 | 英语 | 理综 | +--------+--------+----------+--------+--------+--------+--------+ | 赵六 | 一班 | 20170816 | 100 | 109 | 112 | 265 | | 王五 | 一班 | 20170818 | 95 | 103 | 108 | 270 | | 李四 | 一班 | 20170820 | 95 | 115 | 110 | 260 | | 张三 | 一班 | 20170822 | 110 | 105 | 92 | 235 | | 李宁 | 二班 | 20170824 | 92 | 98 | 105 | 235 | | 陈铭 | 二班 | 20170826 | 111 | 107 | 96 | 204 | +--------+--------+----------+--------+--------+--------+--------+ 6 rows in set (0.00 sec) [root@centos01 ~]# mysqladmin -uroot -ppwd@123 flush-logs !--刷新二进制日志-- [root@centos01 ~]# ls -l /usr/local/mysql/data/ !--查看二进制日志文件-- ...... !--此处省略部分内容-- -rw-rw---- 1 mysql mysql 27299 10月 31 00:00 mysql-bin.000001 -rw-rw---- 1 mysql mysql 1031892 10月 31 00:00 mysql-bin.000002 -rw-rw---- 1 mysql mysql 1574 10月 31 14:13 mysql-bin.000003 -rw-rw---- 1 mysql mysql 507535 11月 1 09:37 mysql-bin.000004 -rw-rw---- 1 mysql mysql 507272 11月 1 09:49 mysql-bin.000005 -rw-rw---- 1 mysql mysql 649 11月 1 09:58 mysql-bin.000006 -rw-rw---- 1 mysql mysql 107 11月 1 09:58 mysql-bin.000007 -rw-rw---- 1 mysql mysql 133 11月 1 09:58 mysql-bin.index drwx------ 2 mysql mysql 4096 10月 31 00:00 performance_schema drwxr-xr-x 2 mysql mysql 20 10月 30 23:56 test [root@centos01 ~]# cp /usr/local/mysql/data/mysql-bin.000006 /root/test/ !--复制二进制日志--2)模拟误操作删除一班学生成绩表
[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop table benet.一班学生成绩;' !--删除一班学生成绩表-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看表是否删除-- ERROR 1146 (42S02) at line 1: Table 'benet.一班学生成绩' doesn't exist3)恢复误删除的表
[root@centos01 ~]# mysql -uroot -ppwd@123 ./test/benet_databases.sql !--恢复完全备份-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看完全备份数据是否恢复-- +--------+--------+----------+--------+--------+--------+--------+ | 姓名 | 班级 | 学号 | 语文 | 数学 | 英语 | 理综 | +--------+--------+----------+--------+--------+--------+--------+ | 赵六 | 一班 | 20170816 | 100 | 109 | 112 | 265 | | 王五 | 一班 | 20170818 | 95 | 103 | 108 | 270 | | 李四 | 一班 | 20170820 | 95 | 115 | 110 | 260 | | 张三 | 一班 | 20170822 | 110 | 105 | 92 | 235 | +--------+--------+----------+--------+--------+--------+--------+ [root@centos01 ~]# mysqlbinlog --no-defaults /root/test/mysql-bin.000006 |mysql -u root -p !--恢复增量备份-- Enter password: !--输入密码-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看增量恢复数据是否恢复-- +--------+--------+----------+--------+--------+--------+--------+ | 姓名 | 班级 | 学号 | 语文 | 数学 | 英语 | 理综 | +--------+--------+----------+--------+--------+--------+--------+ | 赵六 | 一班 | 20170816 | 100 | 109 | 112 | 265 | | 王五 | 一班 | 20170818 | 95 | 103 | 108 | 270 | | 李四 | 一班 | 20170820 | 95 | 115 | 110 | 260 | | 张三 | 一班 | 20170822 | 110 | 105 | 92 | 235 | | 李宁 | 二班 | 20170824 | 92 | 98 | 105 | 235 | | 陈铭 | 二班 | 20170826 | 111 | 107 | 96 | 204 | +--------+--------+----------+--------+--------+--------+--------+3、基于位置恢复
[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop table benet.一班学生成绩;' !--删除一班学生成绩表-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看表是否删除-- ERROR 1146 (42S02) at line 1: Table 'benet.一班学生成绩' doesn't exist [root@centos01 ~]# mysql -uroot -ppwd@123 ./test/benet_databases.sql !--恢复完全备份-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看完全备份是否恢复-- +--------+--------+----------+--------+--------+--------+--------+ | 姓名 | 班级 | 学号 | 语文 | 数学 | 英语 | 理综 | +--------+--------+----------+--------+--------+--------+--------+ | 赵六 | 一班 | 20170816 | 100 | 109 | 112 | 265 | | 王五 | 一班 | 20170818 | 95 | 103 | 108 | 270 | | 李四 | 一班 | 20170820 | 95 | 115 | 110 | 260 | | 张三 | 一班 | 20170822 | 110 | 105 | 92 | 235 | +--------+--------+----------+--------+--------+--------+--------+ [root@centos01 ~]# mysqlbinlog --no-defaults /root/test/mysql-bin.000006 !--查看二进制日志文件确认恢复的位置或时间点-- ...... !--此处省略部分内容-- # at 176 !--at就是我们称之为操作ID,下面紧跟的是时间标记-- #191101 9:55:33 server id 1 end_log_pos 329 Query thread_id=9 exec_time=0 error_code=0 use benet/*!*/; SET TIMESTAMP=1572573333/*!*/; insert into 一班学生成绩 value ('李宁','二班','20170824','92','98','105','235') /*!*/; # at 329 #191101 9:55:33 server id 1 end_log_pos 356 Xid = 278 COMMIT/*!*/; # at 356 #191101 9:55:43 server id 1 end_log_pos 425 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1572573343/*!*/; BEGIN /*!*/; # at 425 !--at就是我们称之为操作ID,下面紧跟的是时间标记-- #191101 9:55:43 server id 1 end_log_pos 579 Query thread_id=9 exec_time=0 error_code=0 SET TIMESTAMP=1572573343/*!*/; insert into 一班学生成绩 value ('陈铭','二班','20170826','111','107','96','204') /*!*/; [root@centos01 ~]# mysqlbinlog --no-defaults --stop-position='425' /root/test/mysql-bin.000006 |mysql -uroot -p !--基于ID恢复增量备份-- Enter password: !--输入密码-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看数据是否恢复-- +--------+--------+----------+--------+--------+--------+--------+ | 姓名 | 班级 | 学号 | 语文 | 数学 | 英语 | 理综 | +--------+--------+----------+--------+--------+--------+--------+ | 赵六 | 一班 | 20170816 | 100 | 109 | 112 | 265 | | 王五 | 一班 | 20170818 | 95 | 103 | 108 | 270 | | 李四 | 一班 | 20170820 | 95 | 115 | 110 | 260 | | 张三 | 一班 | 20170822 | 110 | 105 | 92 | 235 | | 李宁 | 二班 | 20170824 | 92 | 98 | 105 | 235 | +--------+--------+----------+--------+--------+--------+--------+
上述命令中 --stop-position 指定的是停止的位置,如果仅恢复 陈铭 的信息,跳过 李宁 的信息恢复,可以使用 --start-position 选项指定开始恢复数据的位置。这时所恢复的数据是从指定位置开始直到二进制日志文件的最后。
[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop table benet.一班学生成绩;' !--删除一班学生成绩表-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看表是否删除-- ERROR 1146 (42S02) at line 1: Table 'benet.一班学生成绩' doesn't exist [root@centos01 ~]# mysql -uroot -ppwd@123 ./test/benet_databases.sql !--恢复完全备份-- [root@centos01 ~]# mysqlbinlog --no-defaults --start-position='425' /root/test/mysql-bin.000006 |mysql -uroot -p !--基于ID恢复增量备份-- Enter password: !--输入密码-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看数据是否恢复-- +--------+--------+----------+--------+--------+--------+--------+ | 姓名 | 班级 | 学号 | 语文 | 数学 | 英语 | 理综 | +--------+--------+----------+--------+--------+--------+--------+ | 赵六 | 一班 | 20170816 | 100 | 109 | 112 | 265 | | 王五 | 一班 | 20170818 | 95 | 103 | 108 | 270 | | 李四 | 一班 | 20170820 | 95 | 115 | 110 | 260 | | 张三 | 一班 | 20170822 | 110 | 105 | 92 | 235 | | 陈铭 | 二班 | 20170826 | 111 | 107 | 96 | 204 | +--------+--------+----------+--------+--------+--------+--------+4、基于时间点恢复
基于时间点恢复数据所使用的选项是 --stop-datetime ,指定的时间同样也是查询二进制日志所得。执行一下操作可以实现仅恢复到9:55:43之前的数据,即不恢复 陈铭 的信息。
[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop table benet.一班学生成绩;' !--删除一班学生成绩表-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看表是否删除-- ERROR 1146 (42S02) at line 1: Table 'benet.一班学生成绩' doesn't exist [root@centos01 ~]# mysql -uroot -ppwd@123 ./test/benet_databases.sql !--恢复完全备份-- [root@centos01 ~]# mysqlbinlog --no-defaults --stop-datetime='2019-11-01 9:55:43' /root/test/mysql-bin.000006 |mysql -uroot -p !--基于时间点恢复增量备份-- Enter password: !--输入密码-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看数据是否恢复-- +--------+--------+----------+--------+--------+--------+--------+ | 姓名 | 班级 | 学号 | 语文 | 数学 | 英语 | 理综 | +--------+--------+----------+--------+--------+--------+--------+ | 赵六 | 一班 | 20170816 | 100 | 109 | 112 | 265 | | 王五 | 一班 | 20170818 | 95 | 103 | 108 | 270 | | 李四 | 一班 | 20170820 | 95 | 115 | 110 | 260 | | 张三 | 一班 | 20170822 | 110 | 105 | 92 | 235 | | 李宁 | 二班 | 20170824 | 92 | 98 | 105 | 235 | +--------+--------+----------+--------+--------+--------+--------+
执行以下操作可以实现仅恢复 陈铭 的信息,跳过 李宁 的信息恢复
[root@centos01 ~]# mysql -uroot -ppwd@123 -e 'drop table benet.一班学生成绩;' !--删除一班学生成绩表-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看表是否删除-- ERROR 1146 (42S02) at line 1: Table 'benet.一班学生成绩' doesn't exist [root@centos01 ~]# mysql -uroot -ppwd@123 ./test/benet_databases.sql !--恢复完全备份-- [root@centos01 ~]# mysqlbinlog --no-defaults --start-datetime='2019-11-01 9:55:43' /root/test/mysql-bin.000006 |mysql -uroot -p !--基于时间恢复增量备份-- Enter password: !--输入密码-- [root@centos01 ~]# mysql -uroot -ppwd@123 -e 'select * from benet.一班学生成绩;' !--查看数据是否恢复-- +--------+--------+----------+--------+--------+--------+--------+ | 姓名 | 班级 | 学号 | 语文 | 数学 | 英语 | 理综 | +--------+--------+----------+--------+--------+--------+--------+ | 赵六 | 一班 | 20170816 | 100 | 109 | 112 | 265 | | 王五 | 一班 | 20170818 | 95 | 103 | 108 | 270 | | 李四 | 一班 | 20170820 | 95 | 115 | 110 | 260 | | 张三 | 一班 | 20170822 | 110 | 105 | 92 | 235 | | 陈铭 | 二班 | 20170826 | 111 | 107 | 96 | 204 | +--------+--------+----------+--------+--------+--------+--------+
