埋头苦干多年一直没写过文章,今天突发狂想,为LNMP阵营贡献一些力量。就从平时工作过程中的心得和一些技巧分享出来。今天就猿们最熟悉的Mysql开始宅鸟的开篇博客
埋头苦干多年一直没写过文章,今天突发狂想,为LNMP阵营贡献一些力量。就从平时工作过程中的心得和一些技巧分享出来。今天就猿们最熟悉的Mysql开始宅鸟的开篇博客文章。欢迎猿们拍砖、转载。
注意:宅鸟的测试环境和生产环境为ubuntu
Mysql是程序猿和运维猿最关心的开发利器之一，今天就来谈谈Mysql的日常备份之宅鸟见.
常见的Mysql备份方法很多在此不在赘述。直上干货！
本shell脚本运行需要安装
git
ssh
要求读者对mysql,git,ssh,shell有一定了解
本脚本功能：把远程mysqlserver通过脚本配置的白名单和黑名单把指定数据库sql文件利用git版本控制备份到localserver指定目录下，
通过版本控制git查看数据库表数据的变化
预先配置好从localserver->mysqlserver的免密码登录(配置过程)
localserver(192.168.1.110)
db_backup_local.sh
#!/bin/bash
#created by lihuibin
#date 2013-8-30
#desc backup mysqlsql file from mysqlserver to localserver
tar_path="/root/mysql_backup"
ssh root@192.168.1.120 /root/shell/db_backup.sh
[ -d $tar_path ] && cd $tar_path; git pull || git clone root@192.168.1.20:/root/shell/mysql_backup.git
mysqlserver(192.168.1.120)
假设mysqlserver服务器上有testdb1,testdb2,exdb1,exdb2四个数据库
脚本位置:/root/shell/db_backup.sh
#!/bin/bash
#created by lihuibin
#date 2013-8-30
#desc exec mysqldump mysqlsql file from mysqlserver to git
time=`date '+%F %R'`
mysql_user="backup_user"
mysql_password="backup_passwd"
mysql_host="127.0.0.1"
mysql_port="3306"
tar_path=/root/shell/mysql_backup
backup_db_arr=("testdb1" "testdb2") #需要备份的数据库列表
#backup_db_arr=()
exclude_db_arr=("exdb1" "exdb2")
#排除备份的数据库列表 当backup_db_arr为空的时候,exclude_db_arr生效,不为空时仅backup_db_arr有效
[ -d $tar_path ]
is_first_backup=$?
in_array() {
local hay needle=$1
shift
for hay; do
[[ $hay == $needle ]] && return 0
done
return 1
}
backup_length=${#backup_db_arr[@]}
#echo $backup_length
exclude_length=${#exclude_db_arr[@]}
#echo $exclude_length;
#in_array "dopool_blog" ${backup_db_arr[@]} && echo hit || echo miss
for dbname in `mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "show databases" |sed '1,2d'`
do
if [ $backup_length -gt 0 ]; then
in_array $dbname ${backup_db_arr[@]} || continue
elif [ $backup_length -eq 0 -a $exclude_length -gt 0 ]; then
in_array $dbname ${exclude_db_arr[@]} && continue
fi
for tablename in `mysql -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password -e "show tables from $dbname" |sed '1d'`
do
mkdir -p $tar_path/$dbname/
/usr/bin/mysqldump --lock-tables=TRUE --extended-insert=FALSE --complete-insert=TRUE -h$mysql_host -P$mysql_port -u$mysql_user -p$mysql_password $dbname $tablename > $tar_path/$dbname/$tablename.sql
done
done
init_git(){
tar_path=`dirname $1`
dir_name=`basename $1`
cd $1
git init
git add .
git commit -a -m "init $time"
cd $tar_path
git clone --bare $dir_name/
rm -rf $dir_name
git clone $dir_name.git
}
[ $is_first_backup -eq 1 ] && init_git $tar_path || {
cd $tar_path;
git add .;
git commit -a -m " back up $time";
git push;
}
脚本执行后,就可以结合git的tig、diff命令查看每次备份的中数据库表中版本变化
干活吐槽结束 有不足之处,欢迎拍砖!!!
本文出自 “宅鸟” 博客，请务必保留此出处
，
Statement：The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn
