数据库多表多库备份-导入

DB多表多库备份--->MySQL单实例

#!/bin/bash
dbBackup=/data/backup
dbDump=`mysql -e "show databases;" 2>/dev/null|egrep -v "Database|_schema"`
[ ! -d $dbBackup ] && mkdir -p /data/backup
for i in $dbDump
  do
  mkdir -p $dbBackup/$i
  mysqldump -B $i|gzip >> $dbBackup/$i/${i}-$(date +%F).sql.gz
  dbFileDump=`mysql -e "use $i;show tables from $i;" 2>/dev/null|egrep -v "Tables_in_mysql"`
   for a in $dbFileDump
     do
     mysqldump -F --single-transaction -R $i $a|gzip >> $dbBackup/$i/${a}.$(date +%F).sql.gz
   done
done

备份结果　

[root@db01-51 ~]# tree /data/
/data/
└── backup
    ├── mysql
    │   ├── columns_priv.2017-04-06.sql.gz
    │   ├── db.2017-04-06.sql.gz
    │   ├── event.2017-04-06.sql.gz
    │   ├── func.2017-04-06.sql.gz
    │   ├── general_log.2017-04-06.sql.gz
    │   ├── help_category.2017-04-06.sql.gz
    │   ├── help_keyword.2017-04-06.sql.gz
    │   ├── help_relation.2017-04-06.sql.gz
    │   ├── help_topic.2017-04-06.sql.gz
    │   ├── host.2017-04-06.sql.gz
    │   ├── mysql-2017-04-06.sql.gz
    │   ├── ndb_binlog_index.2017-04-06.sql.gz
    │   ├── plugin.2017-04-06.sql.gz
    │   ├── proc.2017-04-06.sql.gz
    │   ├── procs_priv.2017-04-06.sql.gz
    │   ├── servers.2017-04-06.sql.gz
    │   ├── slow_log.2017-04-06.sql.gz
    │   ├── tables_priv.2017-04-06.sql.gz
    │   ├── time_zone.2017-04-06.sql.gz
    │   ├── time_zone_leap_second.2017-04-06.sql.gz
    │   ├── time_zone_name.2017-04-06.sql.gz
    │   ├── time_zone_transition.2017-04-06.sql.gz
    │   ├── time_zone_transition_type.2017-04-06.sql.gz
    │   └── user.2017-04-06.sql.gz
    └── test
        └── test-2017-04-06.sql.gz

3 directories, 25 files

批量导入

#!/bin/bash
cd /data/backup
#查找/data/backup目录下的所有文件名称(DBname)
dbpath=`/bin/ls -ll /data/backup/ | awk -F '[ ]+' '{print $NF}'|grep -v "268"`
for a in $dbpath
  do
#找出表名
  dbname_sql=`/bin/ls -ll /data/backup/$a | awk -F "[ ]+" '{print $NF}'`
#统计DBname是否存在，存在则不执行创建
  num=`mysql -uroot -p123456 -e "show databases;" | grep $a|wc -l`
#数据库不存在，执行创建数据库
  if [ "$num" -ne "1" ];then
    mysql -uroot -p123456 -e "create database $a;"
    echo "创建成功"
  else
    echo "存在$a"
  fi
#获取表名导入MySQL库
  for i in $dbname_sql
    do
      zcat $a/$i | mysql -uroot -p123456 $a
  done
done