以下備份腳本可一鍵導出MySQL數據庫數據盒刚，操作步驟如下（使用的是MySQL 5.7.21版本）：
1、將腳本保存至~/bin/dbdump.sh
2倒庵、在Linux ~/.profile或者.bash_profile用戶環(huán)境初始化文件中增加如下內容（環(huán)境變量內容視情況而定）：
附：導出腳本如下褒墨，請復制并保存至~/bin/dbdump.sh
#!/usr/bin/env bash
### file name:dbdump.sh,save?to ~/bin,and add $HOME/bin PATH to ~/.bash_profile or ~/.profile
### usage:?dbdump.sh?all or?1:with data,dbdump.sh?none or?2:without data
####################################################################
## add these environment parameters to ~/.bash_profile or ~/.profile
# export ENV=數據庫
# export db_host=數據庫地址
# export db_port=數據庫端口
# export db_user=數據庫用戶名
# export db_schema= 數據庫用戶名
# export BACKUP_PATH=/data/docker/container/mysql/backup
# export PATH=$HOME/bin:$PATH
####################################################################
date=`date?"+%Y%m%d"`
error_log=${ENV}_dbdump_error.log
mkdir -p $BACKUP_PATH
cd $BACKUP_PATH
exec_dump() {
????echo
????echo?"-- ${ENV} `date +%F\ %T` Database ${db_schema} dump to ${dump_file} start ..."
????if?[ -f ${dump_file} ]; then
????????rm -f ${dump_file}
????fi
????if?[?"v${no_data}"?==?"v"?]; then
????????echo?"mysqldump --add-drop-table --set-gtid-purged=OFF -h ${db_host} -u ${db_user} -P ${db_port} -p -R ${db_schema} 2> ${error_log} | gzip > ${dump_file}"
????????mysqldump --add-drop-table --set-gtid-purged=OFF -h ${db_host} -u ${db_user} -P ${db_port} -p -R ${db_schema}?2> ${error_log} | gzip > ${dump_file}
????else
????????echo?"mysqldump ${no_data} --add-drop-table --set-gtid-purged=OFF -h ${db_host} -u ${db_user} -P ${db_port} -p -R ${db_schema} 2> ${error_log} | sed 's/AUTO_INCREMENT=[0-9]*\s*//g' | gzip > ${dump_file}"
????????mysqldump ${no_data} --add-drop-table --set-gtid-purged=OFF -h ${db_host} -u ${db_user} -P ${db_port} -p -R ${db_schema}?2> ${error_log} | sed?'s/AUTO_INCREMENT=[0-9]*\s*//g'?| gzip > ${dump_file}
????fi
????if?[ -s ${error_log} ] ; then
????????rm -f ${dump_file}
????????cat ${error_log}
????????echo
????????echo?"-- ${ENV} `date +%F\ %T` Database ${db_schema} dump error."
????????exit?1
????else
????????rm -f ${link_file}
????????ln -s ${dump_file} ${link_file}
????????echo
????????echo?"-- ${ENV} `date +%F\ %T` Database ${db_schema} dump to ${dump_file} finished ."
????fi
}
exec_dump_without_data() {
????no_data='--no-data'
????exec_dump
}
if?[ x$1?!= x ]; then
????dump_type=$1
else
????read -p "
????Which dump type?do?you want?
????1> all (with data)
????2> none structure only (no data)
????Input dump type: [1|2] > " dump_type
fi
case?${dump_type} in
????1|all)
????????link_file=${db_schema}_dump_with_data.sql.gz
????????dump_file=${date}_${link_file}
????????exec_dump
????;;
????2|none)
????????link_file=${db_schema}_dump_without_data.sql.gz
????????dump_file=${date}_${link_file}
????????exec_dump_without_data
????;;
????*)
????????echo?"dump type error, nothing going to do."
????????exit?1
????;;
esac
執(zhí)行導出操作：
#導出數據庫全部數據
dbdump.sh?all
#導出數據結構（不包含數據）
dbdump.sh?none
輸入密碼后數據庫備份文件將導出至BACKUP_PATH目錄中。
用dbdump.sh all 生成一個.gz數據壓縮文件
sftp IP地址 連接到對應IP數據庫服務器上擎宝，將gz壓縮文件傳輸過去
gunzip *.gz文件? 解壓數據文件
如果默認存儲目錄容量不夠
修改數據庫數據存儲目錄
mysql -uroot -p 登錄mysql
select @@datadir; 查看當前數據存儲目錄
sudo service mysqld stop?
停止數據庫服務
在內容空間充足的目錄下? 創(chuàng)建存儲目錄郁妈，例如:/data
cp /var/lib/mysql /data
vim /etc/my.cnf
[client]
port=3306
socket=/data/mysql/mysql.sock
[mysqld]
datadir=/data/mysql
socket=/data/mysql/mysql.sock
啟動服務
sudo service mysqld start?
再次登錄mysql中? 驗證存儲目錄是否更改
更改成功后，在解壓縮的sql目錄下 執(zhí)行這個命令
nohup mysql -u數據庫賬號 -p'數據庫密碼' 數據庫 < *.sql &
ps -ef | grep mysql??
查看數據庫進程绍申，會有進程在跑
