mysql備份與還原
一扼褪、備份常用操作基本命令
1、備份命令mysqldump格式
格式：mysqldump -h主機名 -P端口 -u用戶名 -p密碼 –database 數(shù)據(jù)庫名 > 文件名.sql
example
mysqldump --opt --single-transaction=TRUE --user=root --password=123456 --host=127.0.0.1 --protocol=tcp --port=3306 --single-transaction=TRUE --routines --events "testdba" > c:\mysql_backup\backup_%Ymd%.sql
2粱栖、備份MySQL數(shù)據(jù)庫為帶刪除表的格式
備份MySQL數(shù)據(jù)庫為帶刪除表的格式话浇，能夠讓該備份覆蓋已有數(shù)據(jù)庫而不需要手動刪除原有數(shù)據(jù)庫。
mysqldump --add-drop-table -uusername -ppassword -database databasename > backupfile.sql
3闹究、直接將MySQL數(shù)據(jù)庫壓縮備份
mysqldump -hhostname -uusername -ppassword -database databasename | gzip > backupfile.sql.gz
4幔崖、備份MySQL數(shù)據(jù)庫某個(些)表
mysqldump -hhostname -uusername -ppassword databasename specific_table1 specific_table2 > backupfile.sql
5、同時備份多個MySQL數(shù)據(jù)庫
mysqldump -hhostname -uusername -ppassword –databases databasename1 databasename2 databasename3 > multibackupfile.sql僅僅備6渣淤、僅備份份數(shù)據(jù)庫結(jié)構(gòu)
mysqldump –no-data –databases databasename1 databasename2 databasename3 > structurebackupfile.sql
7赏寇、備份服務(wù)器上所有數(shù)據(jù)庫
mysqldump –all-databases > allbackupfile.sql
8、還原MySQL數(shù)據(jù)庫的命令
mysql -hhostname -uusername -ppassword databasename < backupfile.sql
9价认、還原壓縮的MySQL數(shù)據(jù)庫
gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename
10嗅定、將數(shù)據(jù)庫轉(zhuǎn)移到新服務(wù)器
mysqldump -uusername -ppassword databasename | mysql –host=... -C databasename
11、--master-data 和--single-transaction
在mysqldump中使用--master-data=2用踩，會記錄binlog文件和position的信息 渠退。--single-transaction會將隔離級別設(shè)置成repeatable-commited
12、導入數(shù)據(jù)庫
常用source命令脐彩，用use進入到某個數(shù)據(jù)庫碎乃，mysql>source d:\test.sql，后面的參數(shù)為腳本文件惠奸。
13梅誓、查看binlog日志
查看binlog日志可用用命令 mysqlbinlog binlog日志名稱|more
14、general_log
General_log記錄數(shù)據(jù)庫的任何操作佛南，查看general_log 的狀態(tài)和位置可以用命令show variables like "general_log%" ,開啟general_log可以用命令set global general_log=on
二梗掰、增量備份
小量的數(shù)據(jù)庫可以每天進行完整備份，因為這也用不了多少時間共虑，但當數(shù)據(jù)庫很大時愧怜，就不太可能每天進行一次完整備份了，這時候就可以使用增量備份妈拌。增量備份的原理就是使用了mysql的binlog志拥坛。
1蓬蝶、首先做一次完整備份：
mysqldump -h10.6.208.183 -utest2 -p123 -P3310 --single-transaction --master-data=2 test>test.sql這時候就會得到一個全備文件test.sql
在sql文件中我們會看到：
-- CHANGE MASTER TO MASTER_LOG_FILE='bin-log.000002', MASTER_LOG_POS=107;是指備份后所有的更改將會保存到bin-log.000002二進制文件中。
2猜惋、在test庫的t_student表中增加兩條記錄丸氛，然后執(zhí)行flush logs命令。這時將會產(chǎn)生一個新的二進制日志文件bin-log.000003著摔，bin-log.000002則保存了全備過后的所有更改缓窜，既增加記錄的操作也保存在了bin-log.00002中。
3谍咆、再在test庫中的a表中增加兩條記錄禾锤，然后誤刪除t_student表和a表。a中增加記錄的操作和刪除表a和t_student的操作都記錄在bin-log.000003中摹察。

三恩掷、恢復(fù)
1、首先導入全備數(shù)據(jù)
mysql -h10.6.208.183 -utest2 -p123 -P3310 < test.sql供嚎，也可以直接在mysql命令行下面用source導入
example登入mysql客戶端
use databaseName;
mysql> source C:\mysql_backup\backup_20201026172232.sql;
2黄娘、恢復(fù)bin-log.000002
mysqlbinlog bin-log.000002 |mysql -h10.6.208.183 -utest2 -p123 -P3310
example
進入C:\Program Files\MySQL\MySQL Server 8.0\bin下進行編輯
mysqlbinlog "C:\ProgramData\MySQL\MySQL Server 8.0\Data\F1335291-B2B-bin.000052">C:\Users\F1335291\Desktop\a\a.txt；
登入mysql客戶端
source C:\Users\F1335291\Desktop\a\a.txt;
3克滴、恢復(fù)部分 bin-log.000003
在general_log中找到誤刪除的時間點逼争，然后更加對應(yīng)的時間點到bin-log.000003中找到相應(yīng)的position點，需要恢復(fù)到誤刪除的前面一個position點劝赔。
可以用如下參數(shù)來控制binlog的區(qū)間
--start-position 開始點 --stop-position 結(jié)束點
--start-date 開始時間 --stop-date 結(jié)束時間
找到恢復(fù)點后誓焦，既可以開始恢復(fù)。
mysqlbinlog mysql-bin.000003 --stop-position=208 |mysql -h10.6.208.183 -utest2 -p123 -P3310

4增量備份知識點
show master logs; #檢視資料庫所有日誌檔案望忆。
show binlog events \g; #檢視當前使用的binlog檔案資訊罩阵。
show binlog events in 'mysql-bin.000016'; #檢視指定的binlog檔案資訊
flush logs; #將記憶體中l(wèi)og日誌寫磁碟，儲存在當前binlog檔案中启摄，併產(chǎn)生一個新的binlog日誌檔案稿壁。
flush logs; reset master; #刪除所有二進位制日誌，並重新（mysql-bin.000001）開始記錄歉备。
flush tables with read lock; 備份前需要將資料庫加讀鎖傅是，防止資料在備份時寫入。
flush logs; 通過命令flush logs;將log日誌刷盤蕾羊，寫入當前binlog（mysql-bin.000001）喧笔，在生成一個新的binlog（mysql-bin.000002）為增備做準備。
unlock tables; 解除表鎖龟再。

目錄
一书闸、簡介（）
1、增量備份
增量備份 是指在一次全備份或上一次增量備份後利凑，以後每次的備份只需備份與前一次相比增加或者被修改的檔案浆劲。這就意味著嫌术，第一次增量備份的物件是進行全備後所產(chǎn)生的增加和修改的檔案；第二次增量備份的物件是進行第一次增量備份後所產(chǎn)生的增加和修改的檔案牌借，如此類推度气。這種備份方式最顯著的優(yōu)點就是：沒有重複的備份資料，因此備份的資料量不大膨报，備份所需的時間很短磷籍。但增量備份的資料恢復(fù)是比較麻煩的。您必須具有上一次全備份和所有增量備份磁帶（一旦丟失或損壞其中的一個增量现柠，就會造成恢復(fù)的失斣毫臁），並且它們必須沿著從全備份到依次增量備份的時間順序逐個反推恢復(fù)晒旅，因此這就極大地延長了恢復(fù)時間栅盲。
假如我們有一個資料庫，有20G的資料废恋，每天會增加10M的資料，資料庫每天都要全量備份一次扒寄，這樣的話伺服器的壓力比較大鱼鼓，因此我們只需要備份增加的這部分資料，這樣減少伺服器的負擔该编。
2迄本、binlog簡介
binlog日誌由配置檔案的 log-bin 選項負責啟用，MySQL伺服器將在資料根目錄建立兩個新文 件XXX-bin.001和xxx-bin.index课竣，若配置選項沒有給出檔名嘉赎，Mysql將使用主機名稱命名這兩個檔案，其中.index檔案包含一份全體日誌檔案的清單于樟。
Mysql會把使用者對所有資料庫的內(nèi)容和結(jié)構(gòu)的修改情況記入XXX-bin.n檔案公条，而不會記錄 SELECT和沒有實際更新的UPDATE語句。
當MySQL資料庫停止或重啟時迂曲，伺服器會把日誌檔案記入下一個日誌檔案靶橱，Mysql會在重啟時生成一個新的binlog日誌檔案，檔案序號遞增路捧，此外关霸，如果日誌檔案超過max_binlog_size系統(tǒng)變數(shù)配置的上限時，也會生成新的日誌檔案杰扫。
二队寇、binlog日誌操作
2.1、開啟binlog日誌
修改 MySQL 的配置檔案my.cnf 如下：
1
2
3 [mysqld]
log-bin = mysql-bin
binlog_format = row
其中：log-bin若不顯示指定儲存目錄章姓，則預(yù)設(shè)儲存在mysql的data目錄下
binlog_format的幾種格式：（STATEMENT佳遣，ROW和MIXED）炭序，
STATEMENT：基於SQL語句的複製(statement-based replication, SBR)
ROW：基於行的複製(row-based replication, RBR)
MIXED：混合模式複製(mixed-based replication, MBR)
啟動後會產(chǎn)生mysql-bin.*這樣的檔案，每啟動一次苍日，就會增加一個或者多個惭聂。

? 檢視binlog開啟情況

1
2 mysql> show variables like 'log_bin%';

2. 08.mysql> show variables like 'binlog%';

2.2、檢視binlog日誌內(nèi)容
這裡我們碰到了mysqlbinlog的一個bug相恃，解決方法有兩個：
? 方法一：使用–no-defaults選項

? 方法二：將my.cnf中[client]選項組中default-character-set=utf8選項臨時遮蔽掉（該選項即時生效辜纲，不用重啟資料庫）,使用完mysqlbinlog命令時在恢復(fù)。因為使用mysqlbinlog工具檢視二進位制日誌時會重新讀取的mysql的配置檔案my.cnf(windows下是my.ini),而不是伺服器已經(jīng)載入進記憶體的配置檔案拦耐。

1
2
3
4
5
6
SET TIMESTAMP=1404573835/!/;
create database test
/!/;
DELIMITER ;
ROLLBACK /* added by mysqlbinlog /;
/!50003 SET COMPLETION_TYPE=@OLD_COMPLETION_TYPE*/;
包含以下要素：
Position:位於檔案中的位置耕腾，即第一行的（#at 4）和第二行的（log_pos 4），說明該事件記錄從檔案第4位元組開始杀糯。
Timestamp:事件發(fā)生的時間戳扫俺，即第二行的（#070813 14:16:36）
Exec_time:事件的執(zhí)行花費時間
Error_code:錯誤碼
Type 事件型別：
Master ID:建立二進位制事件的主機伺服器ID
Master Pos:事件在原始二進位制檔案中的位置
Flags:標誌資訊

2.3 一些常用操作
1
2
3
4
5 mysql> show master logs; #檢視資料庫所有日誌檔案。
mysql> show binlog events \g; #檢視當前使用的binlog檔案資訊固翰。
mysql> show binlog events in 'mysql-bin.000016'; #檢視指定的binlog檔案資訊狼纬。
mysql> flush logs; #將記憶體中l(wèi)og日誌寫磁碟，儲存在當前binlog檔案中骂际，併產(chǎn)生一個新的binlog日誌檔案疗琉。
mysql> flush logs; reset master; #刪除所有二進位制日誌，並重新（mysql-bin.000001）開始記錄歉铝。

三盈简、MySQL備份例項（全備 基於 binlog的增備）
示例採用小資料量進行模擬，包含一份全備及兩份增備太示，主要演示下備份還原過程柠贤，工程中可根據(jù)資料實際情況進行備份還原策略調(diào)整。
3.1 檢視當前資料庫binlog檔案
? 通過mysql客戶端檢視:
1 mysql> show master logs;
? 通過linux命令列直接檢視（mysql資料目錄data）
目前只有一個binlog檔案mysql-bin.000001。

3.2 準備全量資料

1
2
3
4 mysql> create database backup_full;
mysql> create table full (c1 int(10), c2 varchar(20)) engine=innodb;
mysql> insert into full values (1, 'full1'),(2, 'full2'),(3, 'full3'),(4, 'full4'),
(5, 'full5'),(6, 'full6'),(7, 'full7'),(8, 'full8'),(9, 'full9'),(10, 'full10');
3.3 將全量資料進行備份
步驟如下：
a.備份前需要將資料庫加讀鎖，防止資料在備份時寫入毯欣。
1 mysql> flush tables with read lock;
b.通過命令flush logs;將log日誌刷盤，寫入當前binlog（mysql-bin.000001）坚俗，在生成一個新的binlog（mysql-bin.000002）為增備做準備。
1 mysql> flush logs;
c.進行資料備份岸裙。在linux命令列下執(zhí)行：
1
2 [root]mysqldump -u使用者名稱 -p密碼 -hIP地址 -P埠 資料庫名 > /tmp/backup_full.sql
[root]mysqldump -uroot -pjesse -h127.0.0.1 -P3355 buckup_full > /tmp/backup_full.sql
d.解除表鎖猖败。
1 mysql> unlock tables;
至此全量備份全部結(jié)束，將全量資料檔案buckup_full.sql儲存即可降允。資料庫再有新的資料更新會記錄在新的binlog（mysql-bin.000002）裡面恩闻。

3.4 準備第一份增量資料

1
2
3
4 mysql> create database backup_increment;
mysql> use backup_increment;
mysql> create table increment (c1 int(10), c2 varchar(20)) engine=innodb;
mysql> insert into increment values (11, 'increment1'),(12, 'increment2'),(13, 'increment3'),(14, 'increment4'),(15, 'increment5');

3.5 將第一份增量資料進行備份
步驟如下：
a.備份前需要將資料庫加讀鎖，防止資料在備份時寫入剧董。
1 mysql> flush tables with read lock;
b.通過命令flush logs;將log日誌刷盤幢尚，寫入當前binlog（mysql-bin.000002）破停，在生成一個新的binlog（mysql-bin.000003）為下次增備做準備。
1 mysql> flush logs;
c.將binlog第一個增備檔案mysql-bin.000002直接複製儲存即可尉剩。 也可以將二進位制檔案匯出到文字檔案儲存真慢，在linux命令列下執(zhí)行
1 [root ]mysqlbinlog mysql-bin.000002 > /tmp/increment1.txt
d.解除表鎖。
1 mysql> unlock tables;
至此第一個增量備份全部結(jié)束理茎，將增量binlog檔案mysql-bin.000002或者有binlog檔案匯出的文字檔案/tmp/increment1.txt儲存即可黑界。資料庫再有新的資料更新會記錄在新的binlog（mysql-bin.000003）裡面。

3.6 準備第二份增量資料
1
2 mysql> use backup_increment;
mysql> insert into increment values (16, 'increment16'),(17, 'increment17'),(18, 'increment18'),(19, 'increment19'),(20, 'increment20');

3.7 將第二份增量資料進行備份
步驟如下：
a.備份前需要將資料庫加讀鎖皂林，防止資料在備份時寫入朗鸠。
1 mysql> flush tables with read lock;
b.通過命令flush logs;將log日誌刷盤，寫入當前binlog（mysql-bin.000003）础倍，在生成一個新的binlog（mysql-bin.000004）為下次增備做準備烛占。
1 mysql> flush logs;
c.將binlog第一個增備檔案mysql-bin.000003直接複製儲存即可。 也可以將二進位制檔案匯出到文字檔案儲存沟启，在linux命令列下執(zhí)行
1 [root]mysqlbinlog mysql-bin.000003 > /tmp/increment2.txt
d.解除表鎖忆家。
1 mysql> unlock tables;
至此第二個增量備份全部結(jié)束，將增量binlog檔案mysql-bin.000003或者有binlog檔案匯出的文字檔案/tmp/increment2.txt儲存即可美浦。資料庫再有新的資料更新會記錄在新的binlog（mysql-bin.000004）裡面弦赖。

四、mysql還原例項分析（全備還原 基於binlog的增備還原）
模擬資料庫故障浦辨，即刪除全備資料及增備資料庫。
1
2 mysql> drop table backup_full.full;
mysql> drop database backup_increment;
此時資料庫資料被清空沼沈。

1
2
3
4
5
6 mysql> show databases;
mysql> select * from backup_full.full;
ERROR 1146 (42S02): Table 'backup_full.full' doesn't exist
mysql> select * from backup_increment.increment;
ERROR 1146 (42S02): Table 'backup_increment.increment' doesn't exist
4.1 還原全備資料
? 方法1：進入資料庫流酬，通過source
1
2
3 mysql> use backup_full;
mysql> source /tmp/backup_full.sql;
mysql> select * from backup_full.full;
全量資料還原成功。
? 方法2：直接還原資料檔案列另。
1
2
3
4
5 mysql -u使用者名稱 -p密碼 -hIP地址 -P埠 資料庫名 < /tmp/buckup_full.sql
mysql -uroot -pjesse -h127.0.0.1 -P3355 backup_full < /tmp/buckup_full.sql
mysql> select * from backup_full.full;

4.2 還原第一個增備檔案
? 方法一：通過文字檔案還原

mysql> source /tmp/increment1.txt; 

mysql> select * from backup_increment.increment;

? 方法二：通過binlog直接還原
在linux命令列下執(zhí)行：
1
2 [root]mysqlbinlog binlog檔名 | mysql -u使用者名稱 -p密碼 -hIP地址 -P埠
[root]mysqlbinlog mysql-bin.000002 | mysql -uroot -pjesse -h127.0.0.1 -P3355
檢視資料：
mysql> select * from backup_increment.increment;

第一份增量資料還原成功芽腾！
4.3 還原第二個增備檔案（方法同上）
在linux命令列下執(zhí)行：
1
2 [root]mysqlbinlog binlog檔名 | mysql -u使用者名稱 -p密碼 -hIP地址 -P埠
[root]mysqlbinlog mysql-bin.000003 | mysql -uroot -pjesse -h127.0.0.1 -P3355
檢視資料：
mysql> select * from backup_increment.increment;
至此資料全部還原成功！
全量備份和增量備份 腳本 僅供參考
全量備份腳本（https://www.cnblogs.com/bulijojodibulido/p/11982393.html）

1. @echo off

2. rem ******MySQL backup start******
   

3. forfiles /p "c:\mysql_backup" /m backup_*.sql -d -7 /c "cmd /c del /f @path"
   

4. set "Ymd=%date:~0,4%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%%time:~6,2%"
   

5. cd "C:\Program Files\MySQL\MySQL Server 8.0\bin"
   

6.  C:
   

7. mysqldump --opt --single-transaction=TRUE --user=root --password=123456 --host=127.0.0.1 --protocol=tcp --port=3306 --single-transaction=TRUE --routines --events "testdba" > c:\mysql_backup\backup_%Ymd%.sql
   

8. rem ******MySQL backup end******
   

9. @echo on

全量備份

10. @echo "1页衙、填寫下載WINRAR的目錄"
11. set PATH=C:\Program Files\WINRAR;%PATH%
12. @echo "2摊滔、填寫mysql安裝目錄"
13. set MYSQLPATH="D:\Program Files\MySQL\MySQL Server 5.6"
14. @echo "3、填寫備份目錄"
15. set BAKPATH=E:\backup
16. @echo "4店乐、如果您的mysql是5.6.x以下 填寫（其它見博客）"
17. set USERNAME=root
18. set PASSWORD=123456
19. @echo "5艰躺、設(shè)置my.ini文件位置(mysql高版本)"
20. set MYINI="D:\ProgramData\MySQL\MySQL Server 5.6\my.ini"
21. set DB=test
22. set DT=%date:_0,4%%date:5,2%%date:_8,2%%time:0,2%%time:_3,2%%time:6,2%
23. set DT=%DT: =0%
25. IF NOT EXIST "%BAKPATH%\bk-full" MD "%BAKPATH%\bk-full"
26. IF NOT EXIST "%BAKPATH%\data" MD "%BAKPATH%\data"
27. pause
28. @echo "mysql 5.6.x以下,本人是其他版本所以將此行注釋"
29. @rem %MYSQLPATH%\bin\mysqldump -u%USERNAME% -p%PASSWORD% --single-transaction --default-character-set=utf8 --databases %DB% --flush-logs --master-data=2 --delete-master-logs > %BAKPATH%\data\dataset6c_%DT%.sql
30. %MYSQLPATH%\bin\mysqldump --defaults-file=%MYINI% --single-transaction --default-character-set=utf8 --databases %DB% --flush-logs --master-data=2 --delete-master-logs > %BAKPATH%\data\dataset6c_%DT%.sql
31. cd %BAKPATH%\data
32. rar a -ag %BAKPATH%\bk-full\ *.sql
33. cd %BAKPATH%\bk-inc
34. rar a -ag ./ logbin.**
35. del logbin.**
36. rmdir /s /q %BAKPATH%\data\
37. @echo %date% %time% full bakup finish today: %today% >> E:\backup\bk.log
38. Pause
    全量備份

39. !/bin/bash

40. 在使用之前，請?zhí)崆皠?chuàng)建以下各個目錄

41. 獲取當前時間

42. date_now=$(date "+%Y%m%d-%H%M%S")
43. backUpFolder=/home/db/backup/mysql
44. username="root"
45. password="123456"
46. db_name="zone"

47. 定義備份文件名

48. fileName="{date_now}.sql"

49. 定義備份文件目錄

50. backUpFileName="{fileName}"
51. echo "starting backup mysql {date_now}."
52. /usr/bin/mysqldump -u{password} --lock-all-tables --flush-logs {backUpFileName}

53. 進入到備份文件目錄

54. cd ${backUpFolder}

55. 壓縮備份文件

56. tar zcvf {fileName}

58. use nodejs to upload backup file other place

59. NODE_ENV=backUpFileName node /home/tasks/upload.js

60. date_end=$(date "+%Y%m%d-%H%M%S")
61. echo "finish backup mysql database {date_end}."

增量備份

62. set PATH=C:\Program Files\WINRAR;%PATH%
63. set MYSQLPATH="D:\Program Files\MySQL\MySQL Server 5.6"
64. set BAKPATH=E:\backup
65. set USERNAME=root
66. set PASSWORD=123456
67. IF NOT EXIST "%BAKPATH%\bk-inc" MD "%BAKPATH%\bk-inc"
68. %MYSQLPATH%\bin\mysqladmin -u%USERNAME% -p%PASSWORD% flush-logs
69. xcopy /c /h /y %BAKPATH%\logbin.0* %BAKPATH%\bk-inc
70. @echo %date% %time% dIncremental bakup finish >> E:\backup\bk.log
71. Pause