MySQL數據庫備份的安全與重要性

發布時間：2020-04-22 15:07:19 來源：億速云閱讀：730 作者：三月欄目：系統運維

下文內容主要給大家帶來MySQL數據庫備份的安全與重要性，所講到的知識，與書籍略有不同，都是億速云專業技術人員在與用戶接觸過程中，總結出來的，具有一定的經驗分享價值，希望給廣大讀者帶來幫助。

數據備份的重要性

在生產環境中，數據的安全性是至關重要的，任何數據的丟失都可能產生嚴重的后果
造成數據丟失的原因
- 程序錯誤
- 人為錯誤
- 計算機失敗
- 磁盤失敗
- 災難（如起火、地震）和偷竊

數據庫備份的分類

從物理與邏輯的角度，備份可分為

物理備份：對數據庫操作系統的物理文件（如數據文件、日志文件等）的備份
- 物理備份又可以分為脫機備份（冷備份）和聯機備份（熱備份）
- 冷備份：是關閉數據庫的時候進行的
- 熱備份：數據庫處于運行狀態，這種備份方法依賴于數據庫的日志文件
邏輯備份：對數據庫邏輯組件（如表等數據庫對象）的備份

從數據庫的備份策略角度，備份可分為

完全備份：每次對數據進行完整的備份
差異備份：備份那些自從上次完全備份之后被修改過的文件
增量備份：只有那些在上次完全備份或者增量備份后修改的文件才會被備份

MySQL完全備份

完全備份是對整個數據庫的備份、數據庫結構和文件結構的備份
完全備份保存的是備份完成時刻的數據庫
完全備份是增量備份的基礎

完全備份的優點

備份與恢復操作簡單方便

完全備份的缺點

數據存在大量的重復
占用大量的備份空間
備份與恢復時間長

mysqldump備份數據庫

MySQL數據庫的備份可以采用多種方式
- 直接打包數據庫文件夾，如/usr/local/mysql/data
- 使用專用備份工具 mysqldump
mysqldump命令
- MySQL自帶的備份工具，相當方便對MySQL進行備份
- 通過該命令工具可以將指定的庫、表或全部的庫導出為SQL0腳本，在需要恢復時可進行數據恢復
mysqldump命令對單個庫進行完全備份
- `mysqldump -u 用戶名 -p [密碼] [選項] [數據庫名] > /備份路徑/備份文件名
單庫備份例子
- mysqldump -u root -p auth > /backup/auth.sql
mysqldump命令對多個庫進行完全備份
- mysqldump -u 用戶名 -p [密碼] [選項] --databases 庫名 1 [庫名2] ... > /備份路徑/備份文件名
多庫備份例子
- mysqldump -u root -p --databases autth mysql > /backup/databases-auth-mysql.sql
對所有庫進行完全備份
- mysqldump -u 用戶名 -p [密碼] [選項] --all-databases > /備份路徑/備份文件名
所有庫備份例子
- mysqldump -u root -p --opt --all-databases > /backup/all-data.sql

mysqldump備份表

在實際生產環境中，存在對某個特定表的維護操作，此時mysqldump同樣發揮重大作用
使用mysqldump備份表的操作
- mysqldump -u 用戶名 -p [密碼] [選項] 數據庫名表命 > /備份路徑/備份文件名
備份表的例子
- mysqldump -u root -p mysql user > /backup/mysql-user.sql

恢復數據庫

使用mysqldump命令導出的SQL備份腳本，在進行數據恢復時可以使用以下方法導入
- source命令
- mysql命令
使用source恢復數據庫的步驟
- 登錄到MySQL數據庫
- 執行source 備份sql腳本的路徑
source恢復例子
- MySQL[(none)]>source /backup/all-data.sql
使用mysql命令恢復數據
- mysql -u 用戶名 -p [密碼] < 庫備份腳本的路徑
mysql命令恢復例子
- mysql -u root -p < /backup/all-data.sql

恢復表的操作

恢復表時同樣可以使用source或者mysq|命令進行
source恢復表的操作與恢復庫的操作相同
當備份文件中只包含表的備份，而不包括創建庫的語句時，必須指定庫名，且目標庫必須存在
- mysq| -u 用戶名 -p [密碼] < 表備份腳本的路徑
- mysql -u root -P mysq| < /backup/mysql-user.sql
在生產環境中，可以使用shell腳本自動實現定期備份

MySQL備份思路

定期實施備份，制定備份計劃或者策略，并嚴格遵守
除了進行完全備份，開啟MySQL云服務器的日志功能是很重要的
- 完全備份加上日志，可以對MySQL進行最大化還原
使用統一的和易理解的備份文件名稱
- 不要使用backup1、backup2等這樣沒有意義的名字
- 不推薦使用庫名或者表名加上時間的命名規則

MySQL增量備份

使用mysqldump進行完全備份的存在的問題

備份數據中有重復數據
備份時間與恢復時間長

增量備份就是備份自.上一次備份之后增加或變化的文件或者內容

增量備份的特點

沒有重復數據，備份量不大，時間短
恢復麻煩:需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且要對所有增量備份進行逐個反推恢復

MySQL沒有提供直接的增量備份方法

可以通過MySQL提供的二進制日志（binary logs）間接實現增量備份

MySQL二進制日志對備份的意義

二進制日志保存了所有更新或者可能更新數據庫的操作
二進制日志在啟動MySQL服務器后開始記錄，并在文件達到max_binlog_size所設置的大小或者接收到flush logs命令后重新創建新的日志文件
只需定時執行flush logs方法重新創建新的日志，生成二進制文件序列，并及時把這些舊的日志保存到安全的地方就完成了一個時間段的增量備份

MySQL數據庫增量恢復

一般恢復
- 添加數據→進行完全備份→錄入新的數據→進行增量備份→模擬故障→恢復操作
基于位置恢復
- 就是將某個起始時間的二進制日志導入數據庫中，c從而跳過某個發生錯誤的時間點實現數據的恢復
基于時間點的恢復
- 使用基于時間點的恢復，可能會出現在一個時間點里既同時存在正確的操作又存在錯誤的操作，所以我們需要一種更為精確的恢復方式
增量恢復的方法
- 一般恢復
- mysqlbinlog [--no-defaults] 增量備份文件 | mysql -u 用戶名 -p
基于位置的恢復
- 恢復數據到指定位置
- mysqlbinlog --stop-position='操作 id' 二進制日志 | mysql -u 用戶名 -p 密碼
- 從指定的位置開始恢復數據
- mysqlbinlog --start-position='操作 id' 二進制日志 | mysql -u 用戶名 -p 密碼
基于時間點的恢復
- 從日志開頭截止到某個時間點的恢復
- mysqlbinlog [-no-defaults] --stop-datetime='年-月-日小時:分鐘:秒' 二進制日志 | mysql -u 用戶名-p 密碼
- 從某個時間點到日志結尾的恢復
- mysqlbinlog [-no-defaults] --start-datetime='年-月-日小時:分鐘:秒' 二進制日志 | mysql -u 用戶名 -p 密碼
- 從某個時間點到某個時間點的恢復
- mysqlbinlog [-no-defaults] --start-datetime='年-月-日小時:分鐘:秒' --stop-datetime='年-月-日小時:分鐘:秒' 二進制日志 | mysql -u 用戶名 -p 密碼

操作實例

創建數據庫、編輯表數據

[root@master2 ~]# mysql -uroot -p      //進入數據庫
Enter password: 

mysql> create database school;      //創建數據庫
Query OK, 1 row affected (0.01 sec)

mysql> use school;     //使用數據庫
Database changed
mysql> create table info(       //創建表
        -> id int(3) not null primary key auto_increment,
        -> name varchar(10) not null,
        -> score decimal(4,1) not null);
Query OK, 0 rows affected (0.02 sec)

mysql> desc info;       //查看表結構
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(3)       | NO   | PRI | NULL    | auto_increment |
| name  | varchar(10)  | NO   |     | NULL    |                |
| score | decimal(4,1) | NO   |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

mysql> insert into info (name,score) values ('stu01',88),('stu02',77);        //插入表數據
Query OK, 2 rows affected (0.02 sec)
Records: 2  Duplicates: 0  Warnings: 0

mysql> select * from info;      //查看表內容
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
|  2 | stu02 |  77.0 |
+----+-------+-------+
2 rows in set (0.01 sec)

mysql> select * from info limit 1;      //只顯示表中的前1行
+----+-------+-------+
| id | name  | score |
+----+-------+-------+
|  1 | stu01 |  88.0 |
+----+-------+-------+
1 row in set (0.00 sec)

完整備份操作

[root@master2 ~]# cd /usr/local/mysql/data/     //切換到數據庫的數據目錄下
[root@master2 data]# ls
auto.cnf        ibdata1      ib_logfile1  mysql               school  test
ib_buffer_pool  ib_logfile0  ibtmp1       performance_schema  sys
[root@master2 data]# cd school/
[root@master2 school]# ls         //數據中的文件
db.opt  info.frm  info.ibd
[root@master2 school]# cd ..
[root@master2 data]# tar Jcvf /opt/mysql-$(date +%F).tar.xz /usr/local/mysql/data/    //用xz格式壓縮
[root@master2 data]# cd /opt/
[root@master2 opt]# ls
mysql-2019-11-26.tar.xz  mysql-5.7.20  rh

單個數據庫邏輯備份

[root@master2 opt]# mysqldump -uroot -p school > /opt/school.sql    //邏輯備份單個數據庫
Enter password: 
[root@master2 opt]# ls
mysql-2019-11-26.tar.xz  mysql-5.7.20  rh  school.sql
[root@master2 opt]# vim school.sql        //查看備份數據庫腳本

...
CREATE TABLE `info` (
    `id` int(3) NOT NULL AUTO_INCREMENT,
    `name` varchar(10) NOT NULL,
    `score` decimal(4,1) NOT NULL,
    PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
...
LOCK TABLES `info` WRITE;
/*!40000 ALTER TABLE `info` DISABLE KEYS */;
INSERT INTO `info` VALUES (1,'stu01',88.0),(2,'stu02',77.0);
...

多個數據庫備份

[root@master2 opt]# mysqldump -uroot -p --databases school mysql > /opt/db_school_mysql.sql    //備份多個數據庫
Enter password: 
[root@master2 opt]# ls
db_school_mysql.sql  mysql-2019-11-26.tar.xz  mysql-5.7.20  rh  school.sql

完全備份

[root@master2 opt]# mysqldump -uroot -p --opt --all-databases > /opt/all.sql    //完全備份
Enter password: 
[root@master2 opt]# ls
all.sql              mysql-2019-11-26.tar.xz  rh
db_school_mysql.sql  mysql-5.7.20             school.sql

數據庫中的表進行備份

[root@master2 opt]# mysqldump -uroot -p school info > /opt/school_info.sql     //對數據庫中的表進行備份
Enter password: 
[root@master2 opt]# ls
all.sql              mysql-2019-11-26.tar.xz  rh               school.sql
db_school_mysql.sql  mysql-5.7.20             school_info.sql

表結構備份

[root@master2 opt]# mysqldump -uroot -p -d school info > /opt/school_info_desc.sql    //對表結構進行備份
Enter password: 
[root@master2 opt]# ls
all.sql                  mysql-5.7.20          school_info.sql
db_school_mysql.sql      rh                    school.sql
mysql-2019-11-26.tar.xz  school_info_desc.sql

基于腳本恢復數據庫

[root@master2 opt]# mysql -uroot -p     //進入數據庫
Enter password: 

mysql> show databases;   //查看數據庫
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| school             |
| sys                |
| test               |
+--------------------+
6 rows in set (0.00 sec)

mysql> use school;    //使用數據庫
Database changed

mysql> show tables;   //查看表
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> drop table info;    //刪除表
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;        //查看表
Empty set (0.00 sec)

mysql> source /opt/school.sql  //恢復數據庫腳本文件

mysql> show tables;    //查看表
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

基于外部MySQL命令恢復數據庫

mysql> drop table info;   //刪除表
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;   //查看表
Empty set (0.00 sec)

mysql> quit   //退出
Bye
[root@master2 opt]# mysql -uroot -p123123 school < /opt/school.sql   //利用mysql命令進行恢復
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 opt]# mysql -uroot -p123123    //進入數據庫
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;    //使用數據庫
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_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

MySQL增量備份及恢復

開啟二進制日志文件

[root@master2 opt]# vim /etc/my.cnf  //開啟二進制日志文件
[mysqld]
user = mysql
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
port = 3306
character_set_server=utf8
pid-file = /usr/local/mysql/mysql.pid
socket = /usr/local/mysql/mysql.sock
log-bin=mysql-bin   //開啟二進制日志文件
server-id = 1
[root@master2 opt]# systemctl restart mysqld.service   //重啟mysql服務
[root@master2 opt]# cd /usr/local/mysql/data/   //切換到mysql站點
[root@master2 data]# ls     //查看二進制日志文件
auto.cnf        ib_logfile0  mysql             performance_schema  test
ib_buffer_pool  ib_logfile1  mysql-bin.000001  school
ibdata1         ibtmp1       mysql-bin.index   sys

進行完全備份

[root@master2 data]# mysqldump -uroot -p123123 school > /opt/school.sql      //一次完全備份
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@master2 data]# ls
auto.cnf        ib_logfile0  mysql             performance_schema  test
ib_buffer_pool  ib_logfile1  mysql-bin.000001  school
ibdata1         ibtmp1       mysql-bin.index   sys
[root@master2 data]# mysqladmin -uroot -p123123 flush-logs    //刷新二進制日志文件
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@master2 data]# ls         //生成新的二進制日志文件，接下來的操作會保存在mysql-bin.000002中
auto.cnf        ib_logfile0  mysql             mysql-bin.index     sys
ib_buffer_pool  ib_logfile1  mysql-bin.000001  performance_schema  test
ibdata1         ibtmp1       mysql-bin.000002  school

模擬誤操作

[root@master2 data]# mysql -uroot -p123123  ##進入數據庫
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;    ##使用數據庫
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> select * from info;      //查看表
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> insert into info (name,score) values ('by01',66);   //正確操作
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql> delete from info where name='st01';   //錯誤操作
Query OK, 1 row affected (0.00 sec)

mysql> insert into info (name,score) values ('by02',99);      //正確操作
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;
+----+------+-------+
| id | name | score |
+----+------+-------+
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
|  4 | by02 |  99.0 |
+----+------+-------+
3 rows in set (0.00 sec)

[root@master2 data]# mysqladmin -uroot -p123123 flush-logs    //刷新二進制日志文件        
mysqladmin: [Warning] Using a password on the command line interface can be insecure.
[root@master2 data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v mysql-bin.000002 > /opt/bak.txt           //用64位解碼器查看二進制日志文件，并生成一個文件
[root@master2 data]# cd /opt/
[root@master2 opt]# ls
bak.txt  mysql-5.7.20  rh  school.sql
[root@master2 opt]# vim bak.txt    //查看二進制日志文件

# at 1084
#191127 20:14:01 server id 1  end_log_pos 1132 CRC32 0xdcc90eb5         Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `school`.`info`   //第一次正確操作的時間和位置
### SET
###   @1=3
###   @2='by01'
###   @3=66.0
...
# at 1302   //停止位置點
#191127 20:14:46 server id 1  end_log_pos 1357 CRC32 0x6648509a         Table_map: `school`.`info` mapped to number 221
# at 1357
#191127 20:14:46 server id 1  end_log_pos 1405 CRC32 0x1eeb752b         Delete_rows: table id 221 flags: STMT_END_F
### DELETE FROM `school`.`info`   //第二次執行錯誤操作的時間和位置191127 20:14:46
### WHERE
###   @1=1
###   @2='st01'
###   @3=88.0
# at 1405  //開始位置點
#191127 20:14:46 server id 1  end_log_pos 1436 CRC32 0xf1c8d903         Xid = 54
...
# at 1630
#191127 20:15:16 server id 1  end_log_pos 1678 CRC32 0x08d9b0f4         Write_rows: table id 221 flags: STMT_END_F
### INSERT INTO `school`.`info`   //第二次正確操作的時間和位置191127 20:15:16
### SET
###   @1=4
###   @2='by02'
###   @3=99.0

基于時間點進行斷點恢復

[root@master2 opt]# mysql -uroot -p123123   //進入數據庫
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;  //進入庫
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> drop table info;   //刪除數據庫
Query OK, 0 rows affected (0.01 sec)

mysql> select * from info;   //查看表
ERROR 1146 (42S02): Table 'school.info' doesn't exist
mysql> source /opt/school.sql   //恢復完全備份數據庫腳本
...
mysql> show tables;   //查看表
+------------------+
| Tables_in_school |
+------------------+
| info             |
+------------------+
1 row in set (0.00 sec)

mysql> select * from info;  //查看表數據
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
+----+------+-------+
2 rows in set (0.00 sec)

[root@master2 opt]# mysqlbinlog --no-defaults --stop-datetime='2019-11-27 20:14:46' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123          //恢復bin.000002中前一個正確的執行語句（從第二個錯誤語句時間點停止）
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 opt]# mysql -uroot -p123123      //進入數據庫
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;  //使用數據庫
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> select * from info;       //查看表數據，恢復了第一次正確操作
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
+----+------+-------+
3 rows in set (0.00 sec)

[root@master2 opt]# mysqlbinlog --no-defaults --start-datetime='2019-11-27 20:15:16' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123        //跳過錯誤節點，恢復最后一個正確的操作（從最后一個正確的操作時間點開始）
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@master2 opt]# mysql -uroot -p123123  //進入數據庫
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;   //使用數據庫
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> select * from info;      //查看表數據，恢復了第二次正確操作，跳過了錯誤的操作
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
|  4 | by02 |  99.0 |
+----+------+-------+
4 rows in set (0.00 sec)

基于位置點進行斷點恢復

mysql> delete from info where name='by01';    //為實驗方便直接刪除
Query OK, 1 row affected (0.01 sec)

mysql> delete from info where name='by02';    //刪除
Query OK, 1 row affected (0.00 sec)

mysql> select * from info;    //完全備份的初始狀態
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
+----+------+-------+
2 rows in set (0.00 sec)

mysql> quit
Bye

[root@master2 opt]# mysqlbinlog --no-defaults --stop-position='1302' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123                 //跳過錯誤操作的位置點從上一個位置點開始
[root@master2 opt]# mysql -uroot -p123123   //進入數據庫
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;    //使用數據庫
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> select * from info;   //查看表數據，恢復了第一次正確的操作
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
+----+------+-------+
3 rows in set (0.00 sec)

mysql> quit
Bye

[root@master2 opt]# mysqlbinlog --no-defaults --start-position='1405' /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123      //從錯誤的位置后一個位置點開始，跳過錯誤操作的位置點
[root@master2 opt]# mysql -uroot -p123123   //進入數據庫
mysql: [Warning] Using a password on the command line interface can be insecure.

mysql> use school;   ##使用數據庫
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> select * from info;    //查看表數據，跳過錯誤操作，恢復第二次正確操作數據
+----+------+-------+
| id | name | score |
+----+------+-------+
|  1 | st01 |  88.0 |
|  2 | st02 |  77.0 |
|  3 | by01 |  66.0 |
|  4 | by02 |  99.0 |
+----+------+-------+
4 rows in set (0.00 sec)

增量備份全部恢復

[root@master2 opt]# mysqlbinlog --no-defaults /usr/local/mysql/data/mysql-bin.000002 | mysql -uroot -p123123 //全部增量恢復

對于以上關于MySQL數據庫備份的安全與重要性，如果大家還有更多需要了解的可以持續關注我們億速云的行業推新，如需獲取專業解答，可在官網聯系售前售后的，希望該文章可給大家帶來一定的知識更新。

向AI問一下細節

中文字幕av专区_日韩电影在线播放_精品国产精品久久一区免费式_av在线免费观看网站