mysql-idb文件批量恢复多库的数据

#前提是表结构是存在的

shell文件

#!/bin/bash
base_dir=$(cd `dirname $0`; pwd)
user='mysql'
sql_e="mysql -N -uroot -p123456"
#得到所有的数据库，除去'information_schema|mysql|performance_schema|sys'这四个库
db_s=($(${sql_e} -e 'SHOW DATABASES;' | egrep -v 'information_schema|mysql|performance_schema|sys'))
#需要恢复的库地址
mysql_dir='/mnt/mysql/mysql57/data'
#idb文件备份的地址
back_dir='/mnt/mysql/bk/'
for (( i=0; i<${#db_s[@]}; i++ ))
do
  tables_list=($(${sql_e} -e "SELECT table_name FROM information_schema.tables WHERE table_schema=\"${db_s[i]}\";"))
  db_name=${db_s[i]}
  for (( table=0; table<${#tables_list[@]}; table++ ))
  do
    ${sql_e} -e "alter table \`${db_s[i]}\`.${tables_list[table]} discard tablespace;"