数据库备份类型:
· 冷备份:在数据库关闭状态下进行备份操作
· 热备份:在数据库处于运行状态时进行备份操作
· 温备份:数据库锁定表格(不可写入但可读取)的状态下进行备份操作
数据库完全备份操作
1. 物理冷备份与恢复 使用tar命令
1)备份数据库
[root@localhost /]# systemctl stop MySQLd
[root@localhost /]# mkdir /backup
[root@localhost /]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
tar: 从成员名中删除开头的"/"
· /usr/local/mysql/data/:mysql的数据库文件夹
[root@localhost /]# ls -l /backup/
总用量 724
-rw-r--r--. 1 root root 741318 8月 10 08:29 mysql_all-2020-08-10.tar.gz
2) 恢复数据库
执行以下操作将数据库文件夹移除到bak目录,模拟丢失文件故障
[root@localhost /]# mkdir /bak
[root@localhost /]# mv /usr/local/mysql/data/ /bak/
执行以下操作从备份文件恢复数据
[root@localhost /]# mkdir /restore
[root@localhost /]# tar zxf /backup/mysql_all-2020-08-10.tar.gz -C /restore/
[root@localhost /]# mv /restore/usr/local/mysql/data/ /usr/local/mysql/
[root@localhost /]# systemctl start mysqld
[root@localhost /]# netstat -anput |grep 3306
tcp6 0 0 :::3306 :::* LISTEN 3970/mysqld
2. mysqldump备份与恢复
通过此命令可以将指定的库,表或全部的库导出为SQL脚本,便于该命令在不同版本的MySQL服务器上使用。例如,当需要升级mysql服务器时,可以先使用mysqldump命令将原有库信息导出,然后直接在升级后的MySQL服务器导入即可。
[root@localhost /]# mysqladmin -u root -p password '123456'
1) 备份数据库
格式一:将mysql库中的user表导出为mysql-user.sql
[root@localhost /]# mysqldump -u root -p mysql user > mysql-user.sql
Enter password: 输入密码
格式二:将整个test库导出为test.sql尾巴文件,所有操作均以root用户身份验证
[root@localhost /]# mysqldump -u root -p --databases test > test.sql
Enter password:
格式三:备份导出整个MySQL服务器中的库。使用—opt优化执行速度
[root@localhost /]# mysqldump -u root -p --opt --all-databases > all-data.sql
Enter password:
2) 查看备份文件
[root@localhost /]# grep -v "^–" 备份完成的名称
3) 恢复数据库
使用sqldump命令导出的SQL备份脚本,在恢复时通过mysql命令对其进行导入操作。
格式:mysql 【选项】【库名】【表名】< /备份路径/备份文件名
模拟故障:删除上述三个备份的原文件,使用dump导入恢复。
mysql> drop table user;
Query OK, 0 rows affected (0.00 sec)
[root@localhost /]# mysql -u root -p mysql < /mysql-user.sql
Enter password:
备份的文件
数据库
查询是否恢复
mysql> show tables;
+---------------------------+
| Tables_in_mysql |
+---------------------------+ |
………
| user |
+---------------------------+
28 rows in set (0.00 sec)
模拟故障:删除test数据库,再导入恢复。
mysql> drop database test;
Query OK, 0 rows affected (0.01 sec)
导入备份数据恢复
3. MySQL的增量备份与恢复(重点)
增量备份没有重要数据,备份量不大,时间短;但恢复及其麻烦,需要上次完全备份+之后所有的增量备份才能恢复,需要对所有增量备份进行逐个反推恢复。Mysql没有提供直接的增量备份办法,可以通过mysql提供的二进制日志间接实现增量备份。
Mysql的增量备份需要开启二进制功能。
[root@localhost /]# vim /etc/my.cnf
log-bin=/usr/local/mysql/mysql-bin 添加
[root@localhost /]# systemctl restart mysqld
[root@localhost /]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw----. 1 mysql mysql 120 8月 10 09:43 /usr/local/mysql/mysql-bin.000001
-rw-rw----. 1 mysql mysql 34 8月 10 09:43 /usr/local/mysql/mysql-bin.index
MySQL企业备份案例
1、 一般恢复
1) 添加数据库、表、录入信息
mysql> create database local;
mysql> use local;
Database changed
mysql> create table test(id int);
Query OK, 0 rows affected (0.00 sec)
mysql> insert into test values(1);
Query OK, 1 row affected (0.00 sec)
mysql> insert into test values(2);
Query OK, 1 row affected (0.01 sec)
2) 先进行一次完全备份
[root@localhost ~]# mkdir /mysql_back
[root@localhost ~]# mysqldump -uroot -p local test >/mysql_back/local_test-$(date +%F).sql
Enter password:
[root@localhost ~]# ls /mysql_back/
local_test-2020-08-10.sql
[root@localhost ~]# mysqladmin -uroot -p flush-logs 生产新的二进制文件
Enter password:
[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw----. 1 mysql mysql 894 8月 10 10:03 /usr/local/mysql/mysql-bin.000001
-rw-rw----. 1 mysql mysql 120 8月 10 10:03 /usr/local/mysql/mysql-bin.000002
-rw-rw----. 1 mysql mysql 68 8月 10 10:03 /usr/local/mysql/mysql-bin.index
3) 继续录入新的数据并进行增量备份
mysql> insert into test values(3);
Query OK, 1 row affected (0.01 sec)
mysql> insert into test values(4);
Query OK, 1 row affected (0.00 sec)
[root@localhost ~]# mysqladmin -uroot -p flush-logs
Enter password:
[root@localhost ~]# ls -l /usr/local/mysql/mysql-bin.*
-rw-rw----. 1 mysql mysql 894 8月 10 10:03 /usr/local/mysql/mysql-bin.000001
-rw-rw----. 1 mysql mysql 595 8月 10 10:06 /usr/local/mysql/mysql-bin.000002
-rw-rw----. 1 mysql mysql 120 8月 10 10:06 /usr/local/mysql/mysql-bin.000003
-rw-rw----. 1 mysql mysql 102 8月 10 10:06 /usr/local/mysql/mysql-bin.index
[root@localhost ~]# cp /usr/local/mysql/mysql-bin.000002 /mysql_back/
如此00003就保留插入2个数据的操作。
4) 模拟误操作删除test表
[root@localhost ~]# mysql -uroot -p -e 'drop table local.test;' 删除
Enter password:
[root@localhost ~]# mysql -uroot -p -e 'select * from local.test;' 查询
Enter password:
ERROR 1146 (42S02) at line 1: Table 'local.test' doesn't exist
5) 恢复操作
在执行恢复操作时,需要先恢复完全备份,然后恢复增量备份。
[root@localhost mysql_back]# mysql -uroot -p local </mysql_back/local_test-2020-08-10.sql
Enter password:
[root@localhost mysql_back]# mysql -uroot -p -e ' select * from local.test;'
Enter password:
+------+
| id |
+------+
| 1 |
| 2 |
+------+ 注意:缺少3.4数据使用增量备份恢复
[root@localhost mysql_back]# mysqlbinlog --no-defaults /mysql_back/mysql-bin.000002 | mysql -u root -p
Enter password:
[root@localhost mysql_back]# mysql -uroot -p -e ' select * from local.test;'
Enter password:
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 4 |
+------+
2、 给予位置恢复
由于前面做过备份操作,接下来直接进行模拟故障与数据恢复的操作。
+------+
[root@localhost mysql_back]# mysql -uroot -p -e 'drop table local.test;'
Enter password: //模拟误操作删除test表
[root@localhost mysql_back]# mysql -uroot -p -e 'select * from local.test;'
Enter password: //查看test表是否存在
ERROR 1146 (42S02) at line 1: Table 'local.test' doesn't exist
[root@localhost mysql_back]# mysql -uroot -p local </mysql_back/local_test-2020-08-10.sql
Enter password: //恢复完全备份
想要实现基于位置或时间点恢复数据,必须先通过查看二进制日志文件确定恢复的位置或时间点。
[root@localhost /]# mysqlbinlog --no-defaults --stop-position='548' /mysql_back/mysql-bin.000002 | mysql -uroot -p
Enter password:
[root@localhost /]# mysql -uroot -p -e ' select * from local.test;'
Enter password:
+------+
| id |
+------+
| 1 |
| 2 |
| 3 |
| 3 |
| 4 |
+------+
3、 基于时间点恢复 只恢复10:05:19的数据,即不恢复4的信息
mysql> drop tables test; 删除test表
Query OK, 0 rows affected (0.00 sec)
[root@localhost ~]# mysqlbinlog --no-defaults --stop-datetime='2020-8-10 10:05:21' /mysql_back/mysql-bin.000002 | mysql -uroot -p
Enter password:
执行以上操作就是恢复在10:05:21之前的所有数据。
4、 制定企业备份策略的思路