常用的MySQL備份/還原 的方法
共 5642字，需瀏覽 12分鐘
·
2024-06-16 18:19
mysql備份數(shù)據(jù)庫
mysql備份單個數(shù)據(jù)庫
#mysql備份某個庫格式：
mysqldump -h主機名 -P端口 -u用戶名 -p"密碼" --database 數(shù)據(jù)庫名 > 文件名.sql
#實例：mysql備份某個庫：
mysqldump -h10.*.*.9 -P3306 -uroot -p"密碼" --single-transaction --master-data=2 --set-gtid-purged=OFF --database -B db_mystorage > mysqldump_db_mystorage_`date +%Y%m%d-%H%M`.sql
mysql備份多個數(shù)據(jù)庫
#mysql備份某個庫格式：
mysqldump -h主機名 -P端口 -u用戶名 -p"密碼" --database 數(shù)據(jù)庫名1 數(shù)據(jù)庫名2 數(shù)據(jù)庫名3 > 文件名.sql
#實例：mysql備份某個庫：
mysqldump -h10.*.*.9 -P3306 -uroot -p"密碼" --single-transaction --master-data=2 --set-gtid-purged=OFF --database -B db_mystorage db_myblogs db_myOA > mysqldump_db_mystorage_db_myblogs_db_myOA_`date +%Y%m%d-%H%M`.sql
mysql備份所有的庫
#mysql備份所有的庫格式：
mysqldump -h主機名 -P端口 -u用戶名 -p"密碼" --all-databases > 文件名.sql
#實例：mysql備份所有的庫：
mysqldump -h10.*.*.9 -P3306 -u用戶名 -p"密碼" --single-transaction --master-data=2 --set-gtid-purged=OFF --all-databases > mysqldump_all_databases_`date +%Y%m%d-%H%M`.sql
注意：如上命令 -A 或者 --all-databases 是備份該用戶名擁有的全部數(shù)據(jù)庫。
mysql備份特定表
#mysql備份某個庫格式：
mysqldump -h主機名 -P端口 -u用戶名 -p"密碼" 數(shù)據(jù)庫名 表名 > 文件名.sql
#實例：備份db_plus_core庫中的tb_doc_permission表
mysqldump -h10.*.*.9 -P3306 -uroot -p"密碼" --single-transaction --master-data=2 --set-gtid-purged=OFF db_plus_core tb_doc_permission > mysqldump_tb_doc_permission_`date +%Y%m%d-%H%M`.sql
mysql備份特定多個表
#mysql備份某個庫格式：
mysqldump -h主機名 -P端口 -u用戶名 -p"密碼" 數(shù)據(jù)庫名 表名1 表名2 > 文件名.sql
#實例：備份db_plus_core庫中的tb_doc_permission表
mysqldump -h10.*.*.9 -P3306 -uroot -p"密碼" --single-transaction --master-data=2 --set-gtid-purged=OFF db_plus_core tb_1 tb_2 > mysqldump_tb_1_tb_2_`date +%Y%m%d-%H%M`.sql
備份命令的 -B 參數(shù)
執(zhí)行備份語句的時候，其中可以加上很多的參數(shù)，用來添加一些備份的時候的特殊要求的，其中有一個-B參數(shù)。執(zhí)行備份語句時，如果加上了-B參數(shù)，那么將來再執(zhí)行數(shù)據(jù)還原的時候，就不需要自己到數(shù)據(jù)庫里面去先創(chuàng)建一個目標庫了，并且執(zhí)行數(shù)據(jù)還原語句的時候也不需要指定目標庫了。如果沒有加-B參數(shù)，就需要自行到數(shù)據(jù)庫中先創(chuàng)建一個目標庫，并且執(zhí)行恢復語句時要指定將數(shù)據(jù)恢復到目標庫。
原因是-B參數(shù)導出的文件中自帶創(chuàng)建數(shù)據(jù)庫和連接數(shù)據(jù)庫的功能：(使用-B參數(shù)備份出來的內(nèi)容自帶create database 庫名和use 庫名的功能)。
備份命令的 | gzip 管道符
【壓縮備份】：實際生產(chǎn)上導出的數(shù)據(jù)有可能比較大，不好備份到遠程，這時候就需要進行壓縮。
mysql壓縮備份：
#mysql壓縮備份格式：
mysqldump -h主機名 -P端口 -u用戶名 -p"密碼" --database 數(shù)據(jù)庫名 | gzip > 文件名.sql.gz
#實例一：mysql壓縮備份
mysqldump -h10.*.*.9 -P3306 -uroot -p"密碼" --single-transaction --master-data=2 --set-gtid-purged=OFF --database db_plus_core | gzip > mysqldump_db_plus_core_`date +%Y%m%d-%H%M`.sql.gz
#實例二：mysql壓縮備份全部數(shù)據(jù)庫：
mysqldump -h10.*.*.9 -P3306 -uroot -p"密碼" --single-transaction --master-data=2 --all-databases | gzip > mysqldump_alldatabases_`%Y%m%d-%H%M`.sql.gz
#實例三：如果mysql開了GTID模式就要用這個命令
mysqldump -h10.*.*.9 -P3306 -uroot -p"密碼" --single-transaction --master-data=2 --set-gtid-purged=OFF --all-databases | gzip > mysqldump_alldatabases_`%Y%m%d-%H%M`.sql.gz
mysql備份數(shù)據(jù)帶出刪除數(shù)據(jù)庫或者表的sql備份
備份時，可以帶出刪除庫的參數(shù)--add-drop-database，帶出刪除表的參數(shù)--add-drop-table
#格式：
mysqldump -h主機名 -P端口 -u用戶名 -p"密碼" --add-drop-table --add-drop-database 數(shù)據(jù)庫名 > 文件名.sql
#示例：
mysqldump -h10.*.*.9 -P3306 -uroot -p"密碼" --add-drop-table --add-drop-database cmdb > /data/backup/mysqldump_cmdb_`%Y%m%d-%H%M`.sql
僅備份數(shù)據(jù)庫結(jié)構(gòu)，不包含數(shù)據(jù)
有時候我們僅僅只想備份出環(huán)境，數(shù)據(jù)庫/表/字段結(jié)構(gòu)，可以加命令參數(shù)--no-data
#格式：
mysqldump -h主機名 -P端口 -u用戶名 -p"密碼" --no-data 數(shù)據(jù)庫名1 數(shù)據(jù)庫名2 > 文件名.sql
#示例：
mysqldump -h10.*.*.9 -P3306 -uroot -p"密碼" --no-data db1 db2 > /data/backup/mysqldump_no_data_db1_db2_`%Y%m%d-%H%M`.sql
mysqldump恢復數(shù)據(jù)備份
#格式：
mysqldump -h主機名 -P端口 -u用戶名 -p"密碼" --no-data 數(shù)據(jù)庫名1 數(shù)據(jù)庫名2 > 文件名.sql
#示例：還原db_plus_core庫中的tb_doc_permission表的備份
mysqldump -uroot -p"密碼" db_plus_core < mysqldump_20180114_tb_doc_permission.sql
#示例：還原所有的庫（保底操作，按需執(zhí)行）
mysqldump -uroot -p"密碼" < mysqldump_20240114_all_databases.sql
通過mysql默認的終端執(zhí)行恢復命令
【通過source命令恢復數(shù)據(jù)庫】
進入mysql數(shù)據(jù)庫控制臺，mysql -uroot -p3306登陸后
mysql>use 數(shù)據(jù)庫;
然后使用source命令，后面參數(shù)為腳本文件(如這里用到的是.sql文件，如果你備份的是.txt文件，那這里寫.txt文件)
mysql>source crm2.sql #這個文件是系統(tǒng)路徑下的，默認是登陸mysql前的系統(tǒng)路徑，在mysql中查看系統(tǒng)路徑的方法是通過system+系統(tǒng)命令來搞的
mysql>system ls
【利用mysql命名恢復(標準)】
如下：mysql -root -p3306 -e "use crm2;drop table student;show tables;" 必須是雙引號mysql -uroot -p3306 crm2<f:\數(shù)據(jù)庫備份練習\crm2.sqlmysql -uroot -p3306 -e "use crm2;show tables;"
注：如果sql文件里面沒有use db這樣的字樣時，在導入時就要指定數(shù)據(jù)庫名了。mysql -uroot -p3306 crm2<.sql文件
建議備份數(shù)據(jù)庫時都指定上-B參數(shù)，效果好。
說明：mysql不光可以恢復mysqldump的備份，只要文件中是sql語句，都可以通過mysql命令執(zhí)行到數(shù)據(jù)庫中
mysql 帶 -e 參數(shù)實現(xiàn)非交互式對話，就是不需要到mysql里面去，在外面執(zhí)行里面的指令的方法,例如：mysql -uroot -p3306 -e "use crm2;show tables;"，但是語句必須是雙引號包裹。
詳情參考鏈接：https://www.cnblogs.com/yanghongtao/p/10897282.html
Mysql備份表及部分數(shù)據(jù)
有時候我們只需要備份某個表里面的部分數(shù)據(jù)，快速恢復關(guān)鍵業(yè)務。
【原理】：使用 like 命令快速復制一個表（結(jié)構(gòu)一模一樣），將要備份的部分數(shù)據(jù)查詢出來備份到新表里面，使用完了以后再恢復回去。
#示例：
mysql -h10.*.*.9 -P3306 -uroot -p"密碼"
use db_mystorage;
#step1：拷貝一份新的表結(jié)構(gòu)
create table tb_staff_bak like tb_staff;
#step2：將要備份的部分數(shù)據(jù)查詢出來備份到新表里面
insert into tb_staff_bk select * from tb_staff where name in ('xiong','zhang');
#step3：清理目標數(shù)據(jù)
delete from tb_staff where name in ('xiong','zhang');
#回滾與恢復：
insert into tb_staff select * from tb_staff_bk;
drop table tb_staff_bk;
實例：mysql自動備份腳本
備份方式：通過mysqldump的方式備份數(shù)據(jù)庫，并保留7天備份數(shù)據(jù)。
#!/bin/bash
backupdir=/data/mysqlbak #備份文件存放路徑
time=`date +%Y%m%d-%H%M` #備份文件名稱時間標記
mysqldump -h10.*.*.9 -P3306 -uroot -p"密碼" --all-databases --single-transaction --default-character-set=utf8 |gzip > $backupdir/mysql$time.sql.gz #備份所有數(shù)據(jù)庫并打包
find $backupdir -name "mysql*.sql.gz" -type f -mtime +7 -exec rm {} \; > /dev/null 2>&1 #刪除7天之外的備份文件
創(chuàng)建備份目錄
mkdir -pv /data/mysqlbak
給備份腳本授權(quán)
chmod +x /data/mysqlbak/mysqlbak.sh
添加計劃任務
crontab -e
00 3 * * * /data/mysqlbak/bakmysql.sh
【恢復使用】
解壓
gzip -d mysql2020703.sql.gz
非壓縮備份文件恢復：
mysql -u用戶名 -p < mysql2020703.sql
從壓縮文件直接恢復：
gzip < mysql22020703.sql.gz | mysql -u 用戶名 -p
鏈接：https://www.cnblogs.com/xiongzaiqiren/p/17965612/mysqldump
（版權(quán)歸原作者所有，侵刪）