??資料對于企業的重要性，不言而喻，所以在使用Mysql資料庫的程序中，經常需要使用到資料庫的備份和匯出功能，非常重要！！！
文章目錄
- 一：備份的分類
- 1：物理備份 檔案
- 2：邏輯備份
- 二：熱備份的重要性
- 三：常見的備份方法
- 3.1：物理冷備
- 3.2：專用備份工具mydump或mysqlhotcopy
- 3.3：啟用二進制日志進行增量備份
- 3.4：第三方工具備份
- 四：MySQL完全備份
- 4.1：什么是完全備份？
- 4.11：優點：
- 4.12：缺點
- 五：資料庫完全備份分類
- 5.1：物理冷備份與恢復
- 5.2：mysqldump備份與恢復
- 5.3：對所有庫進行完全備份
- 5.4：mysqldump備份資料表
- 六：恢復資料庫
- 6.1：使用mysqldump匯出的腳本，可使用匯入的方法
- 6.2：使用source恢復資料庫的步驟
- 七：MYSQL增量備份
- 7.1：使用mysqldump命令進行完全備份存在的問題
- 7.2：增量備份優缺點
- 7.3：如何實作MySQL增量備份？
- 7.4：增量備份的方法
- 7.41：一般恢復
一：備份的分類
1：物理備份 檔案
物理備份:對資料庫作業系統的物理檔案(如資料檔案、日志檔案等)
- 冷備份(脫機備份) :是在關閉資料庫的時候進行的
- 熱備份(聯機備份) :資料庫處于運行狀態，依賴于資料庫的日志檔案
- 溫備份:資料庫鎖定表格(不可寫入但可讀)的狀態下進行備份操作
2：邏輯備份
邏輯備份:對資料庫邏輯組件(如: 表等資料庫物件)的
備份
從資料庫的備份策略角度，備份可分為
- 完全備份：每次對資料進行完整的備份
- 差異備份：備份那些自從上次完全備份之后被修改過的檔案
- 增量備份：只有那些在上次完全備份或者增量備份后被修改的檔案才會被備份
二：熱備份的重要性
在生產環境中，資料的安全性至關重要
任何資料的丟失都可能產生嚴重的后果:
2.1：造成資料丟失的原因
- 程式錯誤
- 人為操作錯誤
- 運算錯誤
- 磁盤故障
- 災難（如火災、地址）和盜竊
三：常見的備份方法
3.1：物理冷備
- 備份時資料庫處于關閉狀態，直接打包資料庫檔案
- 備份速度快，恢復時也是最簡單的
3.2：專用備份工具mydump或mysqlhotcopy
- mysqldump常用的邏輯備份工具
- mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表
3.3：啟用二進制日志進行增量備份
- 進行增量備份，需要重繪_二進制日志
3.4：第三方工具備份
- 免費的MySQl熱備份軟體Percona XtraBackup
四：MySQL完全備份
4.1：什么是完全備份？
完全備份是對整個資料庫的備份、資料庫結構和檔案結構的備份
完全備份保存的是備份完成時刻的資料庫
完全備份是增量備份的基礎
4.11：優點：
安全性高
備份與恢復操作簡單方便
4.12：缺點
資料存在大量的重復
占用大量的備份空間，空間利用率低
備份與恢復時間長
五：資料庫完全備份分類
5.1：物理冷備份與恢復
關閉MySQL資料庫
使用tar命令直接打包資料庫檔案夾
直接替換現有MySQL目錄即可
mysql> use tom;
Database changed
mysql> create table chengji (name VARCHAR(10),point INT(10));
Query OK, 0 rows affected (0.02 sec)
mysql> desc chengji;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| name | varchar(10) | YES | | NULL | |
| point | int(10) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
2 rows in set (0.01 sec)
mysql> insert into chengji values('xiaowang',77),('xiaoli',75);
Query OK, 2 rows affected (0.01 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> select * from chengji
-> ;
+----------+-------+
| name | point |
+----------+-------+
| xiaowang | 77 |
| xiaoli | 75 |
+----------+-------+
2 rows in set (0.00 sec)
- 先關閉資料庫服務，再打包備份
[root@server3 ~]# tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/
tar: 從成員名中洗掉開頭的“/”
[root@server3 ~]# cd /backup/
[root@server3 backup]# ll
總用量 1376
-rw-r--r--. 1 root root 1406517 10月 13 14:48 mysql_all-2020-10-13.tar.gz
- 將原來的資料移走到備份檔案夾中，解壓剛才備份的tar包到/restore目錄下，再移動到mysql服務的檔案夾中
[root@server1 ~]# mkdir bak
[root@server1 ~]# mv /usr/local/mysql/data/ /bak #將資料庫的檔案移動至/bak檔案夾中
[root@server1 ~]# mkdir restore
[root@server1 ~]# tar zxf /backup/mysql_all-2020-10-23.tar.gz -C restore
[root@server1 ~]# mv restore/usr/local/mysql/data/ /usr/local/mysql/ #將之前備份的檔案移動至mysql服務的檔案夾中
- 重啟mysql服務，登錄mysql，查看資料是否恢復
mysql> use tom;
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 chengji;
+----------+-------+
| name | point |
+----------+-------+
| xiaowang | 77 |
| xiaoli | 75 |
+----------+-------+
2 rows in set (0.00 sec)
5.2：mysqldump備份與恢復
MySQL自帶的備份工具，可方便實作對MySQL的備份
可以將指定的庫、表匯出為SQL腳本
使用命令mysql匯入備份的資料
mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###備份所有資料庫
mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###備份auth和mysql庫
mysqldump -u root -p auth > auth-$(data +%F).sql ###備份auth資料庫
mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###備份mysql的user表
mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###備份mysql庫user表的結構
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| myadm |
| mysql |
| performance_schema |
| student |
| sys |
| tom |
+--------------------+
7 rows in set (0.00 sec)
[root@server3 opt]# mysqldump -u root -p tom > /opt/tom.sql
Enter password:
[root@server3 opt]# ls
tom.sql # 匯出的備份檔案
5.3：對所有庫進行完全備份
[root@server3 opt]# mysqldump -uroot -pabc123 --all-databases > /backup/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
5.4：mysqldump備份資料表
- musqldump可針對庫內特定的表進行備份
- 使用mysqldump備份表的操作
mysqldump -u 用戶名 -p 【密碼】【選項】選項庫名 表名 > /備份路徑/備份檔案名
示例：
mysql> use tom;
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_tom |
+---------------+
| chengji |
+---------------+
1 row in set (0.00 sec)
mysql> select * from chengji;
+----------+-------+
| name | point |
+----------+-------+
| xiaowang | 77 |
| xiaoli | 75 |
+----------+-------+
2 rows in set (0.00 sec)
#復制tom表 name欄位 張三內容 生成一張新表pp
mysql> create table pp as select * from chengji where name='xiaowang';
Query OK, 1 row affected (0.03 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| chengji |
| pp |
+---------------+
2 rows in set (0.00 sec)
#新生成表
mysql> select *from pp;
+----------+-------+
| name | point |
+----------+-------+
| xiaowang | 77 |
+----------+-------+
1 row in set (0.00 sec)
[root@server3 ~]# mysql -u root -p123123 shuai pp > /opt/pp.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@server3 ~]# ls /opt
pp.sql
六：恢復資料庫
6.1：使用mysqldump匯出的腳本，可使用匯入的方法
source命令【作用于mysql模式下】
mysql命令【作用于于linux模式下】
6.2：使用source恢復資料庫的步驟
登錄到mysql資料庫
執行source備份sql腳本的路徑
source恢復的示例
MYSQL[(none)]> source /backup/all-data.sql
模擬洗掉表
mysql> use tom;
Database changed
#洗掉表
mysql> drop table chengji;
Query OK, 0 rows affected (0.02 sec)
mysql> drop table pp;
Query OK, 0 rows affected (0.01 sec)
進行恢復
mysql> use tom;
Database changed
mysql> use tom;
Database changed
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| tom |
+---------------+
1 row in set (0.00 sec)
mysql> select * from tom;
+----+----------+----------+
| id | name | address |
+----+----------+----------+
| 1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> insert into tom (name,address) values('lisi','wuxi');
Query OK, 1 row affected (0.00 sec)
mysql> select * from tom;
+----+----------+----------+
| id | name | address |
+----+----------+----------+
| 1 | zhangsan | hangzhou |
| 2 | lisi | wuxi |
+----+----------+----------+
2 rows in set (0.00 sec)
mysql> create table pp as select * from tom where name='zhangsan';
Query OK, 1 row affected (0.02 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| pp |
| tom |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from pp;
+----+----------+----------+
| id | name | address |
+----+----------+----------+
| 1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)
mysql> Ctrl-C -- exit!
Aborted
[root@server3 opt]# mysqldump -u root -p tom pp > /opt/pp.sql
Enter password:
[root@server3 opt]# ls /opt/
all.sql opt.sql pp.sql rh tom.tom
[root@server3 opt]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.36-log Source distribution
Copyright (c) 2000, 2017, 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> drop table tom;
ERROR 1046 (3D000): No database selected
mysql> use tom;
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 tom;
Query OK, 0 rows affected (0.01 sec)
mysql> drop table pp;
Query OK, 0 rows affected (0.00 sec)
```java
mysql> show tables;
Empty set (0.00 sec)
#恢復
mysql> source /opt/all.sql;
..省略內容
mysql> use tom;
Database changed
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| tom |
+---------------+
1 row in set (0.00 sec)
mysql> source /opt/pp.sql;
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| pp |
| tom |
+---------------+
2 rows in set (0.00 sec)
mysql> select * from pp;
+----+----------+----------+
| id | name | address |
+----+----------+----------+
| 1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)
#已經恢復 這邊我們是恢復所有資料庫
#也可以單獨的對標進行備份恢復
七：MYSQL增量備份
7.1：使用mysqldump命令進行完全備份存在的問題
備份資料中有重復資料
備份時間與恢復時間長
7.2：增量備份優缺點
優點：
沒有重復資料，效率高，空間利用率最大化
備份量不大，時間短
缺點：
恢復麻煩：需要上次完全備份及完全備份之后所有的增量備份才能恢復，而且要對所有增量備份進行逐個反推恢復
安全性較低
7.3：如何實作MySQL增量備份？
MySQL沒有提供直接的增量備份方法
可以通過 MySQL提供的二進制日志（ binary logs）間接實作增量備份
二進制日志保存了所有更新或者可能更新資料庫的操作
二進制日志在啟動MySQL服務器后開始記錄，并在檔案達到
max_binlog_size所設定的大小或者接收到flush logs命令后重新
創建新的日志檔案
只需定時執行flush logs方法重新創建新的日志，生成二進制文
件序列，并及時把這些日志保存到安全的地方就完成了一個時間
段的增量備份
7.4：增量備份的方法
7.41：一般恢復
mysqbinlog [--no-defaults] 增量備份檔案 | mysql -u 用戶名 -p
#顯示表cc
mysql> select * from cc;
+------+-------+
| id | name |
+------+-------+
| 1 | tom |
| 2 | jerry |
+------+-------+
2 rows in set (0.00 sec)
[root@localhost ~]# mysqldump -u root -p --all-databases > all-data.sql
Enter password:
[root@localhost ~]# ll
總用量 48464
-rw-r--r--. 1 root root 780979 11月 4 17:26 all-data.sql
[root@localhost ~]# vi /etc/my.cnf
#末行添加
log_bin=/usr/local/mysql/data/mysql_bin #開啟增量備份
[root@localhost ~]# cd /usr/local/mysql/data/
[root@localhost data]# systemctl restart mysqld.service
[root@localhost data]# ls
auto.cnf ib_logfile1 server3.err
ibdata1 mysql mysql-bin.index test
ib_logfile0 mysql-bin.000001 performance_schema tom
[root@localhost data]# mysqladmin -uroot -p flush-logs
#查看日志
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002
mysql> insert into cc values(3,'qiaozhi');
Query OK, 1 row affected (0.01 sec)
mysql> delete from cc where id =1;
Query OK, 1 row affected (0.00 sec)
mysql> insert into cc values(4,'suxi');
Query OK, 1 row affected (0.01 sec)
mysql> select * from cc;
+------+---------+
| id | name |
+------+---------+
| 2 | jerry |
| 3 | qiaozhi |
| 4 | suxi |
+------+---------+
3 rows in set (0.00 sec)
[root@localhost data]# mysqladmin -uroot -p flush-logs
Enter password:
[root@localhost data]# ls
auto.cnf ib_logfile0 mysql_bin.000001 performance_schema
bb ib_logfile1 mysql_bin.000002 sys
ib_buffer_pool ibtmp1 mysql_bin.000003
ibdata1 mysql mysql_bin.index
[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002
mysql> drop table cc; #刪表
Query OK, 0 rows affected (0.02 sec)
[root@localhost data]# mysql -u root -p bb < /root/all-data.sql #恢復表
mysql> select * from cc;
+------+-------+
| id | name |
+------+-------+
| 1 | tom |
| 2 | jerry |
+------+-------+
2 rows in set (0.00 sec)
#恢復
[root@localhost data]# mysqlbinlog --no-defaults --stop-datetime='2020-11-04 17:32:41' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p
Enter password:
[root@localhost data]# mysqlbinlog --no-defaults --start-datetime='2020-11-04 17:32:53' /usr/local/mysql/data/mysql_bin.000002 | mysql -u root -p
Enter password:
mysql> select * from bb.cc;
+------+---------+
| id | name |
+------+---------+
| 1 | tom |
| 2 | jerry |
| 3 | qiaozhi |
| 4 | suxi |
+------+---------+
4 rows in set (0.00 sec)
#已經跳過錯誤操作
- 位置點恢復
mysql> delete from cc where id=1;
Query OK, 1 row affected (0.01 sec)
mysql> delete from cc where id=2;
Query OK, 1 row affected (0.01 sec)
[root@server1 ~]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000003 ###查詢該二進制日志內容
[root@localhost data]# mysqlbinlog --no-defaults --stop-position='765210' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p
Enter password:
[root@localhost data]# mysqlbinlog --no-defaults --start-position='765589' /usr/local/mysql/data/mysql_bin.000003 | mysql -u root -p
Enter password:
[root@localhost data]# mysql
mysql> select * from bb.cc;
+------+---------+
| id | name |
+------+---------+
| 1 | tom |
| 2 | jerry |
| 3 | qiaozhi |
| 4 | suxi |
+------+---------+
4 rows in set (0.00 sec)
如果對你有用的話，收藏一下吧
轉載請註明出處，本文鏈接：https://www.uj5u.com/qita/205288.html
標籤：其他
上一篇：共享汽車管理系統設計軟便件研究
下一篇：單例模式5種實作方式