通常备份mysql数据库时使用的命令为:
mysqldump -hhost -uusername -ppsswd databasename > backupfile
敲就太烦人了。这对于经常换系统或升级数据库版本的人来说就更烦了。
以前我也是个经常更换系统的人,从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 */;
由此,接下来的工作就是从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