制作目的:备份所有mysql数据库,要实现一个数据库备份出一个sql脚本,而不是所有的库都导成一个sql脚本(因为这样不好恢复单个库),查了一下 mysqldump 并没有此功能。
#!/bin/bash
cat /dev/null > mysqlback.txt
connmsg=`/home/mysql/bin/mysql -uroot -p963852 <<EOF
show databases;
exit
EOF`
echo "$connmsg" >mysqlback.txt
dw=`date -d today +"%Y-%m-%d_%H-%M-%S"`
while read line
do
if [ "$line" != "Database" ]; then
/home/mysql/bin/mysqldump -uroot -p963852 "$line" >/zzy/mysqlback/"$line"_back_"$dw".sql
fi
done < mysqlback.txt
效果如下
[root@localhost mysqlback]# ll
总计 672
-rw-r--r-- 1 root root 252637 10-09 15:22 bbs_back_2008-10-09_15-22-07.sql
-rw-r--r-- 1 root root 1224 10-09 15:22 information_schema_back_2008-10-09_15-22-07.sql
-rw-r--r-- 1 root root 397630 10-09 15:22 mysql_back_2008-10-09_15-22-07.sql
-rw-r--r-- 1 root root 1210 10-09 15:22 test_back_2008-10-09_15-22-07.sql
15:58:37 2009年12月14日
[root@sql backup0]# pwd
/etc/backup0
[root@sql backup0]# cat backup
#!/bin/sh
dw=`date -d today +"%Y-%m-%d_%H-%M-%S"`
cd /usr/local/mysql/backup
rm -rf *.tar.gz
#cd /usr/local/mysql
tar zcvf mysqldata"$dw".tar.gz /usr/local/mysql/var
#mv mysqldata.tar.gz /usr/local/mysql/backup
/etc/backup/ftp.sh
cd /usr/local/mysql/backup
rm -rf *.tar.gz
/usr/local/mysql/backdatasql.sh
tar zcvf backdatasql"$dw".tar.gz /usr/local/mysql/backupsql
/etc/backup/ftp.sh
====================================================
在/usr/local/mysql/创建backdatasql.sh
cat /usr/local/mysql/backdatasql.sh 里面写入:
#!/bin/bash
cat /dev/null > mysqlback.txt
connmsg=`/usr/local/mysql/bin/mysql -uroot -p密码 <<EOF
show databases;
exit
EOF`
echo "$connmsg" >mysqlback.txt
while read line
do
if [ "$line" != "Database" ]; then
/usr/local/mysql/bin/mysqldump -uroot -p密码 "$line" >/usr/local/mysql/backupsql/"$line".sql
fi
done < mysqlback.txt
=================================================
/usr/local/mysql/ 目录下面创建 backupsql
mkdir backupsql