mysql备份

备份

https://www.cnblogs.com/feichexia/p/MysqlDataBackup.html

mysql -u ddd -p 123456 auth | gzip > /home/hlvan/sql/auth_$(date +%Y%m%d_%H:%M:%S).sql.gz

https://blog.csdn.net/helloxiaozhe/article/details/77680255

格式：mysqldump -h链接ip -P(大写)端口 -u用户名 -p密码数据库名>d:XX.sql(路劲)

示例：mysqldump -h132.72.192.432 -P3307 -uroot -p8888 htgl > bak.sql;

还原压缩的MySQL数据库

gunzip < backupfile.sql.gz | mysql -uusername -ppassword databasename

http://www.cppblog.com/niewenlong/archive/2014/08/07/207927.aspx

https://www.cnblogs.com/Cherie/p/3309456.html

完整的数据库备份脚本

脚本可分为两个部分：

备份数据库
导入备份的数据文件

#!bin/bash

# 解析简单json
parse_json(){  
	echo "${1//\"/}" | sed "s/.*$2:\([^,}]*\).*/\1/"
}

# 数据库账号账号
user_name="xxx"
passwd="xxx"
host="xxx"
port="3306"
out_path="/kevin/backup/mysql/"

# 备份库
db_1=\{\"db_name\"db_name_1,\"user_name\":${user_name},\"passwd\":${passwd},\"host\":${host},\"port\":${port},\"out_path\":${out_path}\}
db_2=\{\"db_name\":db_name_2,\"user_name\":${user_name},\"passwd\":${passwd},\"host\":${host},\"port\":${port},\"out_path\":${out_path}\}
db_3=\{\"db_name\":db_name_3,\"user_name\":${user_name},\"passwd\":${passwd},\"host\":${host},\"port\":${port},\"out_path\":${out_path}\}
db_4=\{\"db_name\":db_name_4,\"user_name\":${user_name},\"passwd\":${passwd},\"host\":${host},\"port\":${port},\"out_path\":${out_path}\}
db_5=\{\"db_name\":db_name_5,\"user_name\":${user_name},\"passwd\":${passwd},\"host\":${host},\"port\":${port},\"out_path\":${out_path}\}

db_infos=($db_1 $db_2 $db_3 $db_4 $db_5)

# use anlian for test
# db_infos=($db_5 $db_4)

# 导入库账号密码
import_user_name="xxx"
import_passwd="xxx"
import_host="xxx"
import_port="3306"

# 导入库
import_db_1=\{\"db_name\":db_name_1,\"user_name\":${import_user_name},\"passwd\":${import_passwd},\"host\":${import_host},\"port\":${import_port}\}
import_db_2=\{\"db_name\":db_name_2,\"user_name\":${import_user_name},\"passwd\":${import_passwd},\"host\":${import_host},\"port\":${import_port}\}
import_db_3=\{\"db_name\":db_name_3,\"user_name\":${import_user_name},\"passwd\":${import_passwd},\"host\":${import_host},\"port\":${import_port}\}
import_db_4=\{\"db_name\":db_name_4,\"user_name\":${import_user_name},\"passwd\":${import_passwd},\"host\":${import_host},\"port\":${import_port}\}
import_db_5=\{\"db_name\":db_name_5,\"user_name\":${import_user_name},\"passwd\":${import_passwd},\"host\":${import_host},\"port\":${import_port}\}

# 导入远程数据库机器
remote_machine="hlvan@beta.hlvan.cn"

import_db_infos=($import_db_1 $import_db_2 $import_db_3 $import_db_4 $import_db_5)

# use anlian for test
# import_db_infos=($import_db_5 $import_db_4)

# 当前时间
#cur_time=$(date +%Y-%m-%d_%H:%M:%S)
cur_time=$(date +%Y-%m-%d)

for db_info in ${db_infos[@]} 
do

	db_name=$(parse_json $db_info "db_name")
	user_name=$(parse_json $db_info "user_name")
	passwd=$(parse_json $db_info "passwd")
	host=$(parse_json $db_info "host")
	port=$(parse_json $db_info "port")
	out_path=$(parse_json $db_info "out_path")

	echo "=========$db_name==========="
	echo "db_name:"$db_name
	echo "user_name:"$user_name
	echo "passwd:"$passwd
	echo "host:"$host
	echo "port:"$port
	echo "out_path:"$out_path
	echo "=========$db_name==========="
	
	# 备份文件路径
	file_path=$out_path${db_name}/${db_name}_${cur_time}.sql.gz
	
	# 备份文件名称
	file_name=${db_name}_${cur_time}.sql.gz
    
    # 备份
    mysqldump -u$user_name -p$passwd -h$host $db_name -P$port | gzip > $file_path

    
    # 使用本地机器备份的数据库数据远程导入对应的数据库中
    # scp $file_path $remote_machine:/tmp/

    # 导入备份
    #ssh $remote_machine "gunzip < /tmp/$file_name | mysql -u$user_name -p$passwd -h$host $db_name -P$port"
    
    # 立即删除临时文件
    #ssh $remote_machine "rm -rf /tmp/$file_name"

done

for import_db_info in ${import_db_infos[@]} 
do

	import_db_name=$(parse_json $import_db_info "db_name")
	import_user_name=$(parse_json $import_db_info "user_name")
	import_passwd=$(parse_json $import_db_info "passwd")
	import_host=$(parse_json $import_db_info "host")
	import_port=$(parse_json $import_db_info "port")

	echo "=========$import_db_name==========="
	echo "import_db_name:"$import_db_name
	echo "import_user_name:"$import_user_name
	echo "import_passwd:"$import_passwd
	echo "import_host:"$import_host
	echo "import_port:"$import_port
	echo "=========$import_db_name==========="
	
	# 备份文件名称
	#import_file_name=${import_db_name}_${cur_time}.sql.gz

    # 导入备份
    #ssh $remote_machine "gunzip < /tmp/$import_file_name | mysql -u$import_user_name -p$import_passwd -h$import_host $import_db_name -P$import_port"
    gunzip < $out_path${import_db_name}/${import_db_name}_${cur_time}.sql.gz | mysql -u$import_user_name -p$import_passwd -h$import_host $import_db_name -P$import_port

done