刚学bash练手, 用来备份指定表
-h 数据库ip -u 用户名 -p 密码 -d 数据库 -P 端口 -f 匹配表前缀 -o 输出目录
数据库为mysql
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 #!/bin/bash source_host=127.0.0.1 source_port=3306 source_db=xxx-admin source_username=root source_password= table_prefix= document_name="`date +%Y%m%d`" if [[ $# -lt 1 ]];then echo "USAGE:`basename $0 ` [-h host] [-u username] [-p password] [-P port]" echo " [-d database] [-f prefix] [-o output_document]" echo " -h host (127.0.0.1)" echo " -u username (root)" echo " -p password ()" echo " -d database (xxx-admin)" echo " -P port (3306)" echo " -f prefix () default all" echo " -o output document() default current date" exit 1 fi while getopts :h:u:p:P:d:f:o: namedo case $name in h)source_host=$OPTARG ;; u)source_username=$OPTARG ;; d)source_db=$OPTARG ;; p)source_password=$OPTARG ;; P)source_port=$OPTARG ;; f)table_prefix=$OPTARG ;; o)document_name=$OPTARG ;; \?) echo "Invalid option :`basename $0 ` [-h host] [-u username] [-p password] [-P port] [-d database] [-f prefix] [-o output_document]" exit 1 ;; :) echo "$0 :Must supply an argument to -$OPTARG ." exit 1 ;; esac done if [ ! -d "./" ${document_name} ]; then mkdir ${document_name} fi sql_query_table='select table_name from information_schema.tables where table_schema="' ${source_db} '" and table_name like "' ${table_prefix} '%";' mysql_params='-u' ${source_username} ' -P' ${source_port} ' -h' ${source_host} ' ' ${source_db} if [ $source_password ];then mysql_params=${mysql_params} ' -p' ${source_password} fi query_params=${mysql_params} ' -e ' "'" ${sql_query_table} "'" dump_params=${mysql_params} echo "Params: " $dump_params echo "connecting mysql ..." eval mysql $query_params | awk '{if (NR>1){print $0}}' | sed s/[[:space:]]//g | while read table_name; do echo "dumping table: " ${table_name} save_path='./' ${document_name} '/' ${table_name} '.sql' echo "saving to " $save_path eval mysqldump $dump_params ' ' ${table_name} '>' $save_path done echo "**************************************" echo " dumping data finished." echo "**************************************"