linux下mysql如何自动备份shell脚本

linux 服务器上的程序每天都在更新 mysql 数据库，于是就想起写一个 shell 脚本，结合 crontab，定时备份数据库。其实非常简单，主要就是使用 mysql 自带的 mysqldump 命令。

 #!/bin/bash 
# shell script to backup mysql database  
# to backup nysql databases file to /backup dir and later pick up by your  
# script. you can skip few databases from backup too. 
# for more info please see (installation info): 
# http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/01/mysql-backup-script.html  
# last updated: aug - 2005 
# -------------------------------------------------------------------- 
# this is a free shell script under gnu gpl version 2.0 or above 
# copyright (c) 2004, 2005 nixcraft project 
# feedback/comment/suggestions : http://cyberciti.biz/fb/ 
# ------------------------------------------------------------------------- 
# this script is part of nixcraft shell script collection (nssc) 
# visit http://bash.cyberciti.biz/ for more information. 
# ------------------------------------------------------------------------- 
myuser="set-mysql-user-name"   # username 
mypass="set-password"    # password  
myhost="localhost"     # hostname 
# linux bin paths, change this if it can not be autodetected via which command 
mysql="$(which mysql)" 
mysqldump="$(which mysqldump)" 
chown="$(which chown)" 
chmod="$(which chmod)" 
gzip="$(which gzip)" 
# backup dest directory, change this if you have someother location 
dest="/backup" 
# main directory where backup will be stored 
mbd="$dest/mysql" 
# get hostname 
host="$(hostname)" 
# get data in dd-mm-yyyy format 
now="$(date +"%d-%m-%y")" 
# file to store current backup file 
file="" 
# store list of databases  
dbs="" 
# do not backup these databases 
iggy="test" 
[ ! -d $mbd ] && mkdir -p $mbd || : 
# only root can access it! 
$chown 0.0 -r $dest 
$chmod 0600 $dest 
# get all database list first 
dbs="$($mysql -u $myuser -h $myhost -p$mypass -bse 'show databases')" 
for db in $dbs 
do 
  skipdb=-1 
  if [ "$iggy" != "" ]; 
  then 
  for i in $iggy 
  do 
    [ "$db" == "$i" ] && skipdb=1 || : 
  done 
  fi 
  if [ "$skipdb" == "-1" ] ; then 
  file="$mbd/$db.$host.$now.gz" 
  # do all inone job in pipe, 
  # connect to mysql using mysqldump for select mysql database 
  # and pipe it out to gz file in backup dir :) 
    $mysqldump -u $myuser -h $myhost -p$mypass $db | $gzip -9 > $file 
  fi 
done

保存后将以上脚本加入crontab调度。如：每天早上四点半备份：30 4 * * * /data/backup-db.sh

如果你使用mysql5.1，可能会提示mysqldump 错误:

mysqldump: couldn't execute 'show create table `general_log`': table 'mysql.general_log' doesn't exist


mysqldump: couldn't execute 'show create table `slow_log`': table 'mysql.slow_log' doesn't exist

原因是mysql库中没有show_log表和general_log表，需要手动创建：

create table if not exists general_log ( 
 event_time timestamp not null default current_timestamp on update current_timestamp, 
 user_host mediumtext not null, 
 thread_id int(11) not null, 
 server_id int(10) unsigned not null, 
 command_type varchar(64) not null, 
 argument mediumtext not null 
) engine=csv default charset=utf8 comment='general log'; 
create table if not exists slow_log ( 
 start_time timestamp not null default current_timestamp on update current_timestamp, 
 user_host mediumtext not null, 
 query_time time not null, 
 lock_time time not null, 
 rows_sent int(11) not null, 
 rows_examined int(11) not null, 
 db varchar(512) not null, 
 last_insert_id int(11) not null, 
 insert_id int(11) not null, 
 server_id int(10) unsigned not null, 
 sql_text mediumtext not null 
) engine=csv default charset=utf8 comment='slow log';

方法二：

注意：

dumpfile=db$(date +%y%m%d)如果设置为这样一定要将此脚本放备份目录下才行。
dumpfile="$backuppath"db$(date +%y%m%d) 如果设置为这样，日志中会有这样的提示tar: removing leading `/' from member names 是因为备份的目录使用的是绝对路径，不过这样不影响数据，可以根据自己习惯而定。
-------------------------------------------------------------------start
#!/bin/bash
#this is a shellscript for auto db backup
#powered by aspbiz
#2004-09
#setting
#设置数据库名，数据库登录名，密码，备份路径，日志路径，数据文件位置，以及备份方式
#默认情况下备份方式是tar，还可以是mysqldump,mysqldotcopy
#默认情况下，用root(空)登录mysql数据库，备份至/root/dbxxxxx.tgz
dbname=mysql
dbuser=root
dbpasswd=
backuppath=/root/
logfile=/root/db.log
dbpath=/var/lib/mysql/
#backupmethod=mysqldump
#backupmethod=mysqlhotcopy
#backupmethod=tar
#setting end

newfile="$backuppath"db$(date +%y%m%d).tgz
dumpfile="$backuppath"db$(date +%y%m%d)
oldfile="$backuppath"db$(date +%y%m%d --date='5 days ago').tgz
echo "-------------------------------------------" >> $logfile
echo $(date +"%y-%m-%d %h:%m:%s") >> $logfile
echo "--------------------------" >> $logfile
#delete old file
if [ -f $oldfile ]
then
rm -f $oldfile >> $logfile 2>&1
echo "[$oldfile]delete old file success!" >> $logfile
else
echo "[$oldfile]no old backup file!" >> $logfile
fi
if [ -f $newfile ]
then
echo "[$newfile]the backup file is exists,can't backup!" >> $logfile
else
case $backupmethod in
mysqldump)
if [ -z $dbpasswd ]
then
mysqldump -u $dbuser --opt $dbname > $dumpfile
else
mysqldump -u $dbuser -p$dbpasswd --opt $dbname > $dumpfile
fi
tar czvf $newfile $dumpfile >> $logfile 2>&1
echo "[$newfile]backup success!" >> $logfile
rm -rf $dumpfile
;;
mysqlhotcopy)
rm -rf $dumpfile
mkdir $dumpfile
if [ -z $dbpasswd ]
then
mysqlhotcopy -u $dbuser $dbname $dumpfile >> $logfile 2>&1
else
mysqlhotcopy -u $dbuser -p $dbpasswd $dbname $dumpfile >>$logfile 2>&1
fi
tar czvf $newfile $dumpfile >> $logfile 2>&1
echo "[$newfile]backup success!" >> $logfile
rm -rf $dumpfile
;;
*)
/etc/init.d/mysqld stop >/dev/null 2>&1
tar czvf $newfile $dbpath$dbname >> $logfile 2>&1
/etc/init.d/mysqld start >/dev/null 2>&1
echo "[$newfile]backup success!" >> $logfile
;;
esac
fi
echo "-------------------------------------------" >> $logfile
---------------------------------------------------------------------------------------------end

以上内容就是本文给大家介绍的linux下mysql如何自动备份shell脚本，希望大家喜欢。