mysql備份恢復(fù)(mysqldump備份 mysqlbinlog恢復(fù))

這篇具有很好參考價值的文章主要介紹了mysql備份恢復(fù)(mysqldump備份 mysqlbinlog恢復(fù))。希望對大家有所幫助。如果存在錯誤或未考慮完全的地方，請大家不吝賜教，您也可以點擊"舉報違法"按鈕提交疑問。

mysql備份恢復(fù)(mysqldump備份 mysqlbinlog恢復(fù))

一、備份的分類

物理備份

? 物理備份:對數(shù)據(jù)庫操作系統(tǒng)的物理文件(如數(shù)據(jù)文件、日志文件等)
- 冷備份(脫機備份) :是在關(guān)閉數(shù)據(jù)庫的時候進行的
- 熱備份(聯(lián)機備份) :數(shù)據(jù)庫處于運行狀態(tài)，依賴于數(shù)據(jù)庫的日志文件
- 溫備份:數(shù)據(jù)庫鎖定表格(不可寫入但可讀)的狀態(tài)下進行備份操作
邏輯備份

邏輯備份:對數(shù)據(jù)庫邏輯組件(如: 表等數(shù)據(jù)庫對象)的備份

從數(shù)據(jù)庫的備份策略角度，備份可分為
- 完全備份：每次對數(shù)據(jù)進行完整的備份
- 差異備份：差異備份是指在一次全備份后到進行差異備份的這段時間內(nèi)，對那些增加或者修改文件的備份。在進行恢復(fù)時，我們只需對第一次全備份和最后一次差異備份進行恢復(fù)。
- 增量備份：增量備份是指在一次全備份或上一次增量備份后，以后每次的備份只需備份與前一次相比增加和者被修改的文件。這就意味著，第一次增量備份的對象是進行全備后所產(chǎn)生的增加和修改的文件；第二次增量備份的對象是進行第一次增量備份后所產(chǎn)生的增加和修改的文件，如此類推。

二、常見的備份方法

物理冷備
- 備份時數(shù)據(jù)庫處于關(guān)閉狀態(tài)，直接打包數(shù)據(jù)庫文件
- 備份速度快，恢復(fù)時也是最簡單的
- 直接復(fù)制數(shù)據(jù)庫文件，適用于大型數(shù)據(jù)庫環(huán)境，不受存儲引擎的限制，但不能恢復(fù)到不同的MySQL版本。
專用備份工具mydump或mysqlhotcopy
- mysqldump常用的邏輯備份工具
- mysqlhotcopy僅擁有備份MyISAM和ARCHIVE表
啟用二進制日志進行增量備份
- 進行增量備份，需要刷新_二進制日志
第三方工具備份
- 免費的MySQl熱備份軟件Percona XtraBackup

三、mysql完全備份

定義

完全備份是對整個數(shù)據(jù)庫的備份、數(shù)據(jù)庫結(jié)構(gòu)和文件結(jié)構(gòu)的備份
完全備份保存的是備份完成時刻的數(shù)據(jù)庫
完全備份是增量備份的基礎(chǔ)
優(yōu)點

安全性高
備份與恢復(fù)操作簡單方便
缺點

數(shù)據(jù)存在大量的重復(fù)
占用大量的備份空間，空間利用率低
備份與恢復(fù)時間長

四、數(shù)據(jù)庫完全備份分類

4.1 物理備份/冷備份-tar (備份與恢復(fù))

關(guān)閉MySQL數(shù)據(jù)庫
使用tar命令直接打包數(shù)據(jù)庫文件夾
直接替換現(xiàn)有MySQL目錄即可

準備好數(shù)據(jù)庫(當然可以自己準備數(shù)據(jù)庫，主要演示對數(shù)據(jù)的增刪改)

進入mysql 創(chuàng)建kings數(shù)據(jù)庫
create database kings;

use kings;

創(chuàng)建tanks表
tanks | CREATE TABLE `tanks` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `skills` varchar(100) NOT NULL,
  `price` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 

插入數(shù)據(jù)
insert into tanks(name,skills,price) values('豬八戒','像一個肉球一樣彈',12888);

select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 豬八戒    | 像一個肉球一樣彈         | 12888 |
+----+-----------+--------------------------+-------+
1 row in set (0.00 sec)

先退出mysql，關(guān)閉數(shù)據(jù)庫服務(wù)，再打包備份

tar zcf /backup/mysql_all-$(date +%F).tar.gz /usr/local/mysql/data/

cd /backup/

ll
總用量 760
-rw-r--r-- 1 root root 776114 8月   7 01:21 mysql_all-2023-08-07.tar.gz

將原來的數(shù)據(jù)移走到備份文件夾中，解壓剛才備份的tar包到/restore目錄下，再移動到mysql服務(wù)的文件夾中

mkdir /bak

將現(xiàn)有mysql的data 移走
mv /usr/local/mysql/data/ /bak

mkdir /restore
 
將備份文件解壓到restore
tar -zxf /backup/mysql_all-2023-08-07.tar.gz -C /restore/

將解壓的備份文件data 移動到mysql中
mv restore/usr/local/mysql/data/ /usr/local/mysql

重啟mysql服務(wù)，登錄mysql，查看數(shù)據(jù)是否恢復(fù)

mysql>use kings;

mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks           |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 豬八戒    | 像一個肉球一樣彈         | 12888 |
+----+-----------+--------------------------+-------+
1 row in set (0.00 sec)

4.2 邏輯備份/熱備份-mysqldump備份

MySQL自帶的備份工具，可方便實現(xiàn)對MySQL的備份
可以將指定的庫、表導(dǎo)出為SQL腳本
mysqldump命令備份過程，實際上是把數(shù)據(jù)庫、表，以SQL語句的形式，輸出為文件的備份過程，這種方式稱之為邏輯備份。
但是這種方式效率并不高，以SQL導(dǎo)出，在海量數(shù)據(jù)下，例如幾十G的場景，備份、恢復(fù)的時間都會過長。
備份的是建表、建庫、插入等操作所執(zhí)行SQL語句（DDL DML DCL），適用于中小型數(shù)據(jù)庫。

mysqldump命令用于備份MySQL數(shù)據(jù)庫

語法:

mysqldump -u username -p password [option] database_name table_name > backup_file.sql

常用參數(shù):

--all-databases：備份所有數(shù)據(jù)庫。
--single-transaction：在備份期間使用單個事務(wù)，確保數(shù)據(jù)的一致性(不停機備份)。
--master-data：
  --master-data=1會將change master 語句寫入dump文件中，在從庫導(dǎo)入后，配置主從無需再指定文件名和位置
  --master-data=2會將change master 語句寫入dump文件中，只不過會被注釋掉，在從庫導(dǎo)入后，配置主從需要指定文件名和位置，建議使用該值
--delete-master-logs：在master上備份后刪除其二進制日志。
--flush-logs：切斷日志，做一次日志輪轉(zhuǎn)。
--skip-lock-tables：備份時跳過鎖定表，適用于大型數(shù)據(jù)庫。
--no-create-db：備份時不包含創(chuàng)建數(shù)據(jù)庫語句。
--no-create-info：備份時不包含創(chuàng)建表的語句。
--no-data：備份時不包含數(shù)據(jù)。
--add-drop-database：備份時在創(chuàng)建數(shù)據(jù)庫之前添加DROP DATABASE語句。
--add-drop-table：備份時在創(chuàng)建表之前添加DROP TABLE語句。
--result-file=file_name：將備份結(jié)果輸出到指定文件中。
--compress：在備份過程中使用壓縮。

常用命令:

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###備份所有數(shù)據(jù)庫
mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###備份auth和mysql庫
mysqldump -u root -p auth > auth-$(data +%F).sql ###備份auth數(shù)據(jù)庫
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表的結(jié)構(gòu)

mysqldump -u root -p --all-databases --single-transaction --master-data=2 --flush-logs >/backup/`date +%F-%H`mysql_all.sql ###備份所有庫，添加change master并注釋，并切斷日志

對單個表備份

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kings              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

[root@localhost ~]# mysqldump -u root -p kings  > /backup/kings-$(date +%F).sql
Enter password: 
[root@localhost ~]# cd /backup/
[root@localhost backup]# ls
kings-2023-08-07.sql  mysql_all-2023-08-07.tar.gz

對所有庫進行備份

[root@localhost ~]# mysqldump -u root -p --all-databases > /backup/all-$(date +%F).sql
Enter password: 
[root@localhost ~]# cd /backup/
[root@localhost backup]# ls
all-2023-08-07.sql  kings-2023-08-07.sql  mysql_all-2023-08-07.tar.gz

備份表

[root@localhost backup]# mysqldump -u root -p kings tanks > /backup/kings.tanks-$(date +%F).sql
[root@localhost backup]# ls
all-2023-08-07.sql  kings-2023-08-07.sql  kings.tanks-2023-08-07.sql  mysql_all-2023-08-07.tar.gz

4.2.1使用source命令恢復(fù)數(shù)據(jù)庫

source命令【作用于mysql模式下】

使用source恢復(fù)數(shù)據(jù)庫

模擬刪除表恢復(fù)表

mysql> drop table tanks;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

mysql> source /backup/kings.tanks-2023-08-07.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)
...

mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks           |
+-----------------+
1 row in set (0.00 sec)

4.2.2使用mysql命令恢復(fù)數(shù)據(jù)庫

mysql命令【作用于于linux模式下】

模擬刪表恢復(fù)

mysql> drop table tanks;
Query OK, 0 rows affected (0.01 sec)

mysql> show tables;
Empty set (0.00 sec)

kings為數(shù)據(jù)庫名字 恢復(fù)導(dǎo)入表需要指定數(shù)據(jù)庫！
[root@localhost ~]# mysql -uroot -p kings < /backup/kings.tanks-2023-08-07.sql 
Enter password: 

mysql> show tables;
+-----------------+
| Tables_in_kings |
+-----------------+
| tanks           |
+-----------------+
1 row in set (0.00 sec)

五、二進制日志備份、恢復(fù)

mysqlbinlog工具來讀取和分析binlog文件

語法：

mysqlbinlog [options] logfile1 logfile2

常用參數(shù):

--database=name，僅顯示指定數(shù)據(jù)庫的轉(zhuǎn)儲內(nèi)容。
--short-form，使用簡單格式。
--start-datetime=name，轉(zhuǎn)儲日志的起始時間。
--stop-datetime=name，轉(zhuǎn)儲日志的截止時間。
--start-position=#，轉(zhuǎn)儲日志的起始位置。
--stop-position=#，轉(zhuǎn)儲日志的截止位置。
--no-defaults：指定不加載或使用默認配置文件，以確保按照命令行參數(shù)指定的配置選項進行操作。
--base64-output=decode-rows：將讀取的二進制數(shù)據(jù)解碼為可讀格式，即將Base64編碼的內(nèi)容解碼為原始數(shù)據(jù)。
-v：輸出詳細信息，包括讀取的日志事件和相關(guān)數(shù)據(jù)。

常用命令：

查看日志
mysqlbinlog --no-defaults --base64-output=decode-rows -v 

恢復(fù)數(shù)據(jù)
mysqlbinlog --stop-position=xxx [mysql-bin.0000xx] | mysql -uroot -p
mysqlbinlog --start-position=xxx --stop-position=xxx [mysql-bin.0000xx] | mysql -uroot -p
mysqlbinlog --start-datetime=xxx --stop-datetime=xxx [mysql-bin.0000xx] | mysql -uroot -p

5.1基于position(位置點)恢復(fù)

1.開啟mysql服務(wù)器的二進制日志功能

[root@localhost ~]# tail -15 /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /usr/local/mysql/data
socket = /usr/local/mysql/mysql.sock
port = 3306
pid-file = /usr/local/mysql/mysql.pid
user = mysql
skip-name-resolve
server-id = 1   // 設(shè)置服務(wù)器標識
log-bin = /usr/local/mysql/mysql-bin  //開啟二進制功能

查看日志文件
[root@localhost ~]# ll /opt/data/
-rw-r-----. 1 mysql mysql      154 2月  22 16:37 mysql_bin.000001
-rw-r-----. 1 mysql mysql       19 2月  22 16:37 mysql_bin.index

2.對數(shù)據(jù)庫進行完全備份

mysqldump -uroot -p --single-transation --master-data=2 --flush-logs --all-databases --delete-master-logs > /backup/all-`date +%F-%H`.sql

可以看到all-2023-08-07-16.sql已經(jīng)創(chuàng)建
[root@localhost backup]# ls
all-2023-08-07-16.sql  kings-2023-08-07.sql        mysql_all-2023-08-07.tar.gz
all-2023-08-07.sql     kings.tanks-2023-08-07.sql

3.在數(shù)據(jù)數(shù)據(jù)庫中增加類容

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 豬八戒    | 像一個肉球一樣彈         | 12888 |
+----+-----------+--------------------------+-------+
1 row in set (0.00 sec)

mysql> insert into tanks(name,skills,price) values('鐘馗','鉤子英雄',12888);
Query OK, 1 row affected (0.01 sec)

mysql> select * from tanks;                                              
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 豬八戒    | 像一個肉球一樣彈         | 12888 |
|  3 | 鐘馗      | 鉤子英雄                 | 12888 |
+----+-----------+--------------------------+-------+
2 rows in set (0.00 sec)

4.模擬刪庫

mysql> drop database kings;
Query OK, 1 row affected (0.01 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

5.刷新二進制文件

[root@localhost mysql]# mysqladmin -uroot -p flush-logs

[root@localhost mysql]# ls /usr/local/mysql/
mysql-bin.000002  mysql-bin.000003  ...

6.恢復(fù)完全備份
查看tanks表內(nèi)容，可以發(fā)現(xiàn)備份之后修改的內(nèi)容丟失

[root@localhost mysql]# mysql -uroot -p < /backup/all-2023-08-07-16.sql
Enter password: 

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+
4 rows in set (0.00 sec)

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| kings              |
| mysql              |
| performance_schema |
| test               |
+--------------------+
5 rows in set (0.00 sec)

mysql> use kings;
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_kings |
+-----------------+
| tanks           |
+-----------------+
1 row in set (0.00 sec)

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 豬八戒    | 像一個肉球一樣彈         | 12888 |
+----+-----------+--------------------------+-------+
1 row in set (0.00 sec)

7.查看binlog 日志,發(fā)現(xiàn)414為刪庫前，所以恢復(fù)到414

mysql> show binlog events in 'mysql-bin.000002'\G

*************************** 4. row ***************************
   Log_name: mysql-bin.000002
        Pos: 233
 Event_type: Query
  Server_id: 1
End_log_pos: 383
       Info: use `kings`; insert into tanks(name,skills,price) values('鐘馗','鉤子英雄',12888)
*************************** 5. row ***************************
   Log_name: mysql-bin.000002
        Pos: 383
 Event_type: Xid
  Server_id: 1
End_log_pos: 414
       Info: COMMIT /* xid=965 */
*************************** 6. row ***************************
   Log_name: mysql-bin.000002
        Pos: 414
 Event_type: Query
  Server_id: 1
End_log_pos: 509
       Info: drop database kings
       
恢復(fù)到刪庫前
退出mysql 執(zhí)行mysqlbinlog --stop-position=
[root@localhost ~]# mysqlbinlog --stop-position=414 /usr/local/mysql/mysql-bin.000003 | mysql -uroot -p
Enter password: 

進入mysql 查看tanks表
mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 豬八戒    | 像一個肉球一樣彈         | 12888 |
|  3 | 鐘馗      | 鉤子英雄                 | 12888 |
+----+-----------+--------------------------+-------+
2 rows in set (0.00 sec)

同理也可以恢復(fù)到插入'id=3'語句之前

對二進制日志查詢還可以用

mysql> show binlog events in 'mysql-bin.000002';
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------+
| Log_name         | Pos | Event_type  | Server_id | End_log_pos | Info                                                                                    |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------+
| mysql-bin.000023 |   4 | Format_desc |         1 |         120 | Server ver: 5.6.40-log, Binlog ver: 4                                                   |
| mysql-bin.000023 | 120 | Query       |         1 |         201 | BEGIN                                                                                   |
| mysql-bin.000023 | 201 | Intvar      |         1 |         233 | INSERT_ID=3                                                                             |
| mysql-bin.000023 | 233 | Query       |         1 |         383 | use `kings`; insert into tanks(name,skills,price) values('鐘馗','鉤子英雄',12888)       |
| mysql-bin.000023 | 383 | Xid         |         1 |         414 | COMMIT /* xid=965 */                                                                    |
| mysql-bin.000023 | 414 | Query       |         1 |         509 | drop database kings                                                                     |
| mysql-bin.000023 | 509 | Rotate      |         1 |         556 | mysql-bin.000024;pos=4                                                                  |
+------------------+-----+-------------+-----------+-------------+-----------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)

5.2基于datetime(時間點)恢復(fù)

1.刪庫（之前添加的id=3 數(shù)據(jù) 這里不進行修改表了）

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 豬八戒    | 像一個肉球一樣彈         | 12888 |
|  3 | 鐘馗      | 鉤子英雄                 | 12888 |
+----+-----------+--------------------------+-------+
2 rows in set (0.01 sec)

Database changed
mysql> drop database kings;
Query OK, 1 row affected (0.00 sec)

2.恢復(fù)完全備份（因為之前有備份過就不需要進行mysqldump備份了，直接使用備份文件完全恢復(fù)就行）

mysql -uroot -p  < /backup/all-2023-08-07-16.sql

3.查看表此時已經(jīng)恢復(fù)到了完全備份的數(shù)據(jù)狀態(tài)

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 豬八戒    | 像一個肉球一樣彈         | 12888 |
+----+-----------+--------------------------+-------+
1 row in set (0.01 sec)

4.使用mysqlbinlog查詢時間點

[root@localhost data]# mysqlbinlog --no-defaults --base64-output=decode-rows -v /usr/local/mysql/data/mysql_bin.000002

找到有關(guān)的信息
# at 233
#230807 17:01:37 server id 1  end_log_pos 233 CRC32 0xf9822b3a  Intvar
SET INSERT_ID=3/*!*/;
#230807 17:01:37 server id 1  end_log_pos 383 CRC32 0xc6524022  Query   thread_id=9     exec_time=0    error_code=0
use `kings`/*!*/;
SET TIMESTAMP=1691398897/*!*/;
insert into tanks(name,skills,price) values('鐘馗','鉤子英雄',12888)
/*!*/;
# at 383
#230807 17:01:37 server id 1  end_log_pos 414 CRC32 0x090e26df  Xid = 965
COMMIT/*!*/;
# at 414
#230807 17:02:35 server id 1  end_log_pos 509 CRC32 0xd7c5718b  Query   thread_id=9     exec_time=0    error_code=0
SET TIMESTAMP=1691398955/*!*/;
drop database kings
/*!*/;

5.使用mysqlbinlog時間點恢復(fù)文章來源地址http://www.zghlxwxcb.cn/news/detail-631538.html

[root@localhost 3306]#  mysqlbinlog --no-defaults --start-datetime='2023-08-07 17:01:37' --stop-datetime='2023-08-07 17:02:35' /usr/local/mysql/mysql-bin.000023 | mysql -uroot -p 

mysql> select * from tanks;
+----+-----------+--------------------------+-------+
| id | name      | skills                   | price |
+----+-----------+--------------------------+-------+
|  2 | 豬八戒    | 像一個肉球一樣彈         | 12888 |
|  3 | 鐘馗      | 鉤子英雄                 | 12888 |
+----+-----------+--------------------------+-------+
2 rows in set (0.00 sec)

到了這里，關(guān)于mysql備份恢復(fù)(mysqldump備份 mysqlbinlog恢復(fù))的文章就介紹完了。如果您還想了解更多內(nèi)容，請在右上角搜索TOY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！

国产无码综合区,色欲AV无码国产永久播放,无码天堂亚洲国产AV,国产日韩欧美女同一区二区

Toy模板網(wǎng)

mysql備份恢復(fù)(mysqldump備份 mysqlbinlog恢復(fù))