数据库批量建库 删库 导入导出

#!/bin/bash

export IFS=$'\n'
for i in $(cat rds.txt); do

    ip=$(echo $i | awk '{print $1}')
    dbkey=$(echo $i | awk '{print $NF}')
    echo $ip $dbkey
    dbname="prod_mxxl_$dbkey"
    username="mc_prod_mxxl_user"
    password="6aCt35vNj0FQAXp5h_mxxl"
    #mysql -uroot -pMxxl@123 -h$ip mysql -Ne "DROP DATABASE $dbname;"
    mysql -uroot -pMxxl@123 -h$ip mysql -Ne "CREATE DATABASE IF NOT EXISTS $dbname DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;"
    mysql -uroot -pMxxl@123 -h$ip mysql -Ne "grant all privileges on ${dbname}.*  to ${username}@'%' identified by '6aCt35vNj0FQAXp5h_mxxl';"
    mysql -uroot -pMxxl@123 -h$ip mysql -Ne "grant all privileges on mysql.*  to ${username}@'%' identified by '6aCt35vNj0FQAXp5h_mxxl';"
    #mysql -f -umc_prod_mxxl_user -p6aCt35vNj0FQAXp5h_mxxl -h$ip $dbname </mxxl/data/${dbname}_0616.sql &
    mysql -f -umc_prod_mxxl_user -p6aCt35vNj0FQAXp5h_mxxl -h$ip $dbname </root/mysql_proc.sql
done
[root@pekshcsitd28828 ~]# cat rds.txt
10.68.168.6    32核  |  64GB   1000  fi
10.68.169.84   32核  |  64GB   1000  qmc
10.68.170.198  32核  |  64GB   1000  emc
10.68.170.198  32核  |  64GB   1000  bc
10.68.171.85   32核  |  128GB  500   cal
10.68.171.116  32核  |  128GB  500   taxc
10.68.170.247  32核  |  128GB  500   phm
10.68.170.247  32核  |  128GB  500   secd
10.68.169.84   32核  |  128GB  500   epm
10.68.171.137  32核  |  64GB   500   sys
10.68.171.137  32核  |  64GB   500   meta
10.68.171.228  32核  |  64GB   500   drp
10.68.171.228  32核  |  64GB   500   hr
10.68.169.232  8核   |  32GB   300   log
10.68.170.160  8核   |  32GB   300   wfs
10.68.170.37   8核   |  32GB   300   eip
10.68.171.42   8核   |  32GB   300   scm
[root@pekshcsitd28828 ~]#
rds.txt

 https://blog.csdn.net/tjcwt2011/article/details/119864098  导出导入存储过程

#!/bin/bash

TIME=$(date "+%Y%m%d")

if [ $# -eq 0 ];then
  echo "UseAge: sh $0 dbA dbB"
  exit 1
fi

SRC_DBNAME=$1
NEW_DBNAME=$2

mysqldump --socket=/tmp/mysql_3306.sock -ukingdee -p123456 -hlocalhost -P3306 --max-allowed-packet=256M --events --triggers --routines --flush-logs --set-gtid-purged=OFF $SRC_DBNAME > /root/${SRC_DBNAME}.sql

if [ $? -ne 0 ];then
  echo "dump $SRC_DBNAME error"
  exit 2
fi

mysql --socket=/tmp/mysql_3306.sock -ukingdee -p123456 -hlocalhost -P3306 mysql -Ne "CREATE DATABASE IF NOT EXISTS ${NEW_DBNAME} DEFAULT CHARSET utf8mb4 COLLATE utf8mb4_unicode_ci;"

mysql -f --socket=/tmp/mysql_3306.sock -ukingdee -p123456 -hlocalhost -P3306  ${NEW_DBNAME} <  /root/${SRC_DBNAME}.sql

if [ $? -ne 0 ];then
  echo "daoru $NEW_DBNAME error"
  mysql --socket=/tmp/mysql_3306.sock -ukingdee -p123456 -hlocalhost -P3306 mysql -Ne "DROP DATABASE ${NEW_DBNAME};"
  exit 3
fi

 

posted @ 2021-08-03 15:13  冥想心灵  阅读(70)  评论(0编辑  收藏  举报