文章目录
前言
在生产环境中,数据的安全性是至关重要的,任何数据的丢失都可能产生严重的后果,所以我们要及时做好备份的工作
在使用数据库的过程中,有多种原因造成数据的丢失:
程序错误
人为错误(大部分原因)
计算机失败
磁盘失败
灾难(如火灾,地震)和偷窃
一. 数据库备份的分类
1.1 从物理与逻辑的角度,备份可分为
物理备份:对数据库操作系统的物理文件(如数据文件、日志文件等)的备份
物理备份的方法:
冷备份(脱机备份):是在关闭数据库的时候进行的
热备份(联机备份):数据库处于运行状态,依赖于数据库的日志文件
温备份(联机备份):数据库锁定表格(不可写入但可读)的状态下进行备份操作
数据库在物理层面分成了三类文件,每一个文件的名字以表的名字开始,扩展名指出文件类型:
表结构文件:.frm文件存储表定义
表数据文件的扩展名为.MYD( MYData)
表索引文件的扩展名是.MYI( MYIndex)
逻辑备份:对数据库逻辑组件(如:表等数据库对象)的备份
1.2 从数据库的备份策略角度,备份可分为
完全备份:每次对数据库进行完整的备份
差异备份:备份自从上次完全备份之后被修改过的文件
增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份
2. 常见的备份方法
物理冷备
备份时数据库处于关闭状态,直接打包数据库文件
备份速度快,恢复时也是最简单的
专用备份工具mydump或mysqlhotcopy
mysqldump常用的逻辑备份工具
mysqlhotcopy仅拥有备份MyISAM和ARCHIVE表
启用二进制日志进行增量备份
进行增量备份,需要刷新二进制日志
第三方工具备份
免费的MySQL热备份软件Percona XtraBackup
3. MySQL完全备份与分类
3.1 完全备份概述
完全备份是对整个数据库的备份、数据库结构和文件结构的备份
完全备份保存的是备份完成时刻的数据库
完全备份是增量备份的基础
优点:
安全性高
备份与恢复操作简单方便
缺点:
数据存在大量的重复
占用大量的备份空间,空间利用率低
备份与恢复时间长
二、 数据库完全备份操作
2.1 物理冷备份与恢复
物理冷备份一般用 tar 命令直接打包数据库文件夹,而在进行备份之前需要使用 “systemctl stop mysqld”命令关闭 mysqld 服务。
[root@localhost opt]# tar zcvf /opt/mysql_all_$(date +%F).tar.gz /usr/local/mysql/data ##将mysql做冷备份到/opt目录下
做好备份后将备份过的原来数据库中的数据删掉
[root@localhost opt]# cd /usr/local/mysql/
[root@localhost mysql]# rm -rf data/
这时候我们尝试登录数据库,被拒绝
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# mysql -u root -pabc123
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
开始还原
在刚刚测试过不能登录后,会重新生成一个/usr/local/mysql/data文件,首先要删除,然后才能还原。
[root@localhost mysql]# cd /usr/local/mysql/
[root@localhost mysql]# ls
bin data include man README support-files
COPYING docs lib mysql-test share usr
[root@localhost mysql]# rm -rf data/
[root@localhost mysql]# cd /opt
[root@localhost opt]# ls
boost_1_59_0.tar.gz mysql-5.7.17.tar.gz mysql-boost-5.7.20.tar.gz
mysql-5.7.17 mysql_all_2020-08-23.tar.gz rh
[root@localhost opt]# tar zxvf mysql_all_2020-08-23.tar.gz
[root@localhost opt]# cd /opt/usr/local/mysql/
[root@localhost mysql]# ls
data
[root@localhost mysql]# mv data/ /usr/local/mysql/ ##将原来的数据库文件还原
[root@localhost mysql]# systemctl start mysqld
[root@localhost mysql]# mysql -uroot -pabc123 ##登录数据库
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.17 Source distribution
Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> ##登陆成功
2.2 mysqldump 备份与恢复
2.2.1 备份单个数据库
mysqldump 命令可以将指定的库、表或全部的库导出为 SQL 脚本,便于该命令 在不同版本的 MySQL 服务器上使用。
创建数据库和一个表,并写入数据
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| home |
| mysql |
| performance_schema |
| sys |
+--------------------+
5 rows in set (0.00 sec)
mysql> create database company;
Query OK, 1 row affected (0.00 sec)
mysql> use company;
Database changed
mysql> create table info (id int(3) not null primary key auto_increment,name varchar(10) not null,address varchar(50) default '未知')engine innodb;
Query OK, 0 rows affected (0.00 sec)
mysql> describe info;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| address | varchar(50) | YES | | 未知 | |
+---------+-------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)
mysql> insert into info (name,address) values ('zhangsan','nj')
-> ,('lisi','bj');
Query OK, 2 rows affected (0.00 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
+----+----------+---------+
2 rows in set (0.00 sec)
重新打开一个终端远程连接虚拟机,使用mysqldump将数据库company备份到/opt目录下
[root@localhost opt]# mysqldump -u root -pabc123 company > /opt/company.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
在/opt目录下就会产生一个.aql的脚本
[root@localhost opt]# vim company.sql
脚本就是创建info表的一系列的sql语句
2.2.2 备份多个数据库
mysql> ceate database home;
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| home |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> create table info(int(3) not null primary key auto_increment,name char(10) nuo null,score int(3),address varchar(50) default '未知',hob char(30))engine innodb;
mysql> desc info;
+---------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+---------+-------------+------+-----+---------+----------------+
| id | int(3) | NO | PRI | NULL | auto_increment |
| name | char(10) | NO | | NULL | |
| score | int(3) | YES | | NULL | |
| address | varchar(50) | YES | | 未知 | |
| hob | char(30) | YES | | NULL | |
+---------+-------------+------+-----+---------+----------------+
在另外一个终端进行备份并查看
[root@localhost ~]# mysqldump -uroot -pabc123 --databases home company > /opt/home_company.sql ##备份多个数据库
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost ~]# cd /opt
[root@localhost opt]# ls
boost_1_59_0.tar.gz mysql-5.7.17 mysql-boost-5.7.20.tar.gz
company.sql mysql-5.7.17.tar.gz rh
home_company.sql mysql_all_2020-08-23.tar.gz
[root@localhost opt]# vim home_company.sql
2.2.3 备份所有数据库
[root@localhost opt]# mysqldump -uroot -pabc123 --all-databases > /opt/all.sql ##备份所有数据库
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls
all.sql home_company.sql mysql_all_2020-08-23.tar.gz
boost_1_59_0.tar.gz mysql-5.7.17 mysql-boost-5.7.20.tar.gz
company.sql mysql-5.7.17.tar.gz rh
[root@localhost opt]# vim all.sql
2.2.4 备份特定的数据表
查看company数据库中的有哪些数据
mysql> use company;
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> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| info |
+-------------------+
1 row in set (0.01 sec)
mysql> select * from info;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
| 2 | lisi | bj |
+----+----------+---------+
2 rows in set (0.00 sec)
将zhangsan的数据拿出来另存为一张表
mysql> create table tmp as select * from info where name='zhangsan';
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> select * from tmp;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
+----+----------+---------+
1 row in set (0.00 sec)
接下来对新建的company数据库中的tmp表进行备份
[root@localhost opt]# mysqldump -uroot -pabc123 company tmp > /opt/company_tmp.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@localhost opt]# ls
all.sql company_tmp.sql mysql-5.7.17.tar.gz rh
boost_1_59_0.tar.gz home_company.sql mysql_all_2020-08-23.tar.gz
company.sql mysql-5.7.17 mysql-boost-5.7.20.tar.gz
[root@localhost opt]# vim company_tmp.sql
2.3 备份恢复
数据恢复有两种方式
source命令–用于数据库中(登录数据库内),后面跟备份表或者库的绝对路径
mysql命令–用于Linux系统环境中
2.3.1 恢复特定表
我们在恢复的过程中,可以将表恢复到任意的数据库中,不一定要在原来数据库中恢复,因为我们恢复就是执行之前创建数据表和写入数据的过程。如果我们在表中修改了部分数据,就会恢复到备份时候的数据,在进行数据恢复的时候要谨慎一些。
就以上面备份的tmp表为例,首先将tmp表从数据库中删除
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| info |
| tmp |
+-------------------+
2 rows in set (0.01 sec)
mysql> drop table tmp;
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| info |
+-------------------+
1 row in set (0.00 sec)
进行数据恢复并查看
mysql> source /opt/company_tmp.sql ##恢复tmp表
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 1 row affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
mysql> show tables;
+-------------------+
| Tables_in_company |
+-------------------+
| info |
| tmp |
+-------------------+
2 rows in set (0.00 sec)
mysql> select * from tmp;
+----+----------+---------+
| id | name | address |
+----+----------+---------+
| 1 | zhangsan | nj |
+----+----------+---------+
1 row in set (0.00 sec)
2.3.2 恢复数据库
在之前我对数据库home和数据库company做过备份,就以恢复这两个数据库为例。
先将这两个数据库删除,测试恢复的功能
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| home |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database company;
Query OK, 2 rows affected (0.01 sec)
mysql> drop database home;
Query OK, 1 row affected (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.00 sec)
进行数据库恢复
[root@localhost opt]# mysql -uroot -pabc123 < home_company.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
进入数据库中查看,数据被恢复
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| company |
| home |
| mysql |
| performance_schema |
| sys |
+--------------------+
6 rows in set (0.00 sec)