shell备份mysql全部数据库

通常备份mysql数据库时使用的命令为：

mysqldump -hhost -uusername -ppsswd databasename > backupfile

当仅有一两个数据库的时候也许不会有太大的麻烦，但是当数据库中有10个或更多的库时。如果还是一行一行地

敲就太烦人了。这对于经常换系统或升级数据库版本的人来说就更烦了。

以前我也是个经常更换系统的人，从windows到linux, 从32位到64. 无论哪次的更换都要对数据库进行备份。每次

都敲一些重复的命令，我想这对于每一个程序员来说都会有这种想法“让代码为我工作”。

通过观查mysql数据库知道，每个数据库的信息都存储在information_schema库的SCHEMATA表中

LOCK TABLES `SCHEMATA` WRITE;
/*!40000 ALTER TABLE `SCHEMATA` DISABLE KEYS */;
INSERT INTO `SCHEMATA` VALUES (NULL,'information_schema','utf8','utf8_general_ci',NULL),(NULL,'mysql','utf8','utf8_general_ci',NULL),(NULL,'project','latin1','latin1_swedish_ci',NULL),(NULL,'test','utf8','utf8_general_ci',NULL);
/*!40000 ALTER TABLE `SCHEMATA` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;

通过这些信息，我们不难提取出mysql中所存储数据库的信息，包括数据库名等。

由此，接下来的工作就是从SCHEATA中获取我们想要的东西，我相信这对于每个对shell以及正则表达有一点了解的人来说都不是难题。每个人的实现方法有所不同，以下实现仅供参考：

#!/bin/bash
# This script show how to use shell value in sed program

# Seting default host, username and password
host="127.0.0.1"
user="root"
passwd="root"

# backup dir
backupdir="$PWD"

# Print help message 
function help_msg(){
    echo "usage: $0 -h<host> -u<username> -p<password> [-d backupdir]"
    
}

# Get parameter
function get_parameter() {
    opt="$1"
    param="$2"
    if [ ${param:0:1} = "-" ]; then
        echo "-$opt need an argument"
        help_msg;
        exit 1
    else 
        printf "$param"
    fi
}

# Must be root to run this script, otherwise exit
if [ $UID -ne 0 ]; then
    echo "You must be root to run this script !"
    exit 1
fi

# Get options
while getopts ":h:u:p:d:" opt; do
    case $opt in
        h ) host=$(get_parameter 'h' $OPTARG) ;;
        u ) user=$(get_parameter 'u' $OPTARG) ;;
        p ) passwd=$(get_parameter 'p' $OPTARG) ;;
        d ) backupdir=$(get_parameter 'd' $OPTARG) ;;
        : ) echo "$OPTARG need an argument !"; 
            exit 1 ;;
        * ) help_msg;
            exit 1 
            ;;
    esac
done
shift $(($OPTIND - 1))

#echo "$host"
#echo "$user"
#echo "$passwd"

# Get schemata file from database
schemata_file="$$"
mysqldump -h$host -u$user -p$passwd information_schema SCHEMATA > "$$_schemata_file"

# Get schemata file error, exit
if [  "$?" -ne 0 ]; then
    rm -f "$$_schemata_file"
    exit 1
fi

# Get contents of the line from schemata file which contains database name
databaseinfo=$(grep 'VALUES' "$$_schemata_file")
#echo "$databaseinfo"

# Fetch information from databaseinfo and assign back to databaseinfo 
databaseinfo=${databaseinfo#${databaseinfo%%(*}}
#echo "$databaseinfo"

# Get database number 
database_count=$(($(echo "$databaseinfo" | awk -F')' '{print NF}') - 1))
#echo "database_count: $database_count"

# Add a comma prefix to databaseinfo, just make it appropriate for awk to process
array=$(echo ",$databaseinfo" | awk -F')' '{for(i = 1; i < NF; i++) print $i }')

# Process every line of 'array' and fetch database name
echo "$array" | while read line
do
   databasename=$(echo "$line" | awk -F',' '{print $3}' )

   # Remvoe single quotation marks from databasename string
   databasename=${databasename#\'}
   databasename=${databasename%\'}
   #echo "$databasename"
   
   # Generate backup file name
   backupfile="$backupdir/$databasename"".sql"

   # Backup every database but information_schema
   if [ "$databasename" = "information_schema" ]; then
       continue;
   fi

   # Backup now
   printf "backup database $databasename..."
   mysqldump -h$host -u$user -p$passwd "$databasename" > "$backupfile"
   printf "Done\n"
done


# Remove temporary file 
rm -f "$$_schemata_file"
exit 0

转载于:https://my.oschina.net/qyh/blog/52555