tnb0/db/exec_tnb_init.sh

#!/bin/bash

sql_dir='/home/reghao/Dropbox/sql/'
# host='127.0.0.1'
host='192.168.0.211'
username='test'
password='Test_123456'
env_dest='rdb'
env_src='rdb'

db1='tnb_account_'${env_dest}
db2='tnb_content_'${env_dest}
db3='tnb_oss_'${env_dest}
db4='nacos_config'

#######################################################################################################################
# 删除已存在的数据库
#######################################################################################################################
sql1="DROP DATABASE IF EXISTS ${db1};"
sql2="DROP DATABASE IF EXISTS ${db2};"
sql3="DROP DATABASE IF EXISTS ${db3};"
sql4="DROP DATABASE IF EXISTS ${db4};"

echo 'drop database if exists...'
# mysql -h${host} -u${username} -p${password} << EOF
# ${sql1}
# ${sql2}
# ${sql3}
# ${sql4}
# EOF
docker exec -it mysql80 mysql -h${host} -u${username} -p${password} -e "${sql1}"
docker exec -it mysql80 mysql -h${host} -u${username} -p${password} -e "${sql2}"
docker exec -it mysql80 mysql -h${host} -u${username} -p${password} -e "${sql3}"
docker exec -it mysql80 mysql -h${host} -u${username} -p${password} -e "${sql4}"

#######################################################################################################################
# 创建数据库
#######################################################################################################################
sql1="CREATE DATABASE ${db1} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;"
sql2="CREATE DATABASE ${db2} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;"
sql3="CREATE DATABASE ${db3} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;"
sql4="CREATE DATABASE ${db4} DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_bin;"

echo 'create database...'
# mysql -h${host} -u${username} -p${password} << EOF
# ${sql1}
# ${sql2}
# ${sql3}
# ${sql4}
# EOF
docker exec -it mysql80 mysql -h${host} -u${username} -p${password} -e "${sql1}"
docker exec -it mysql80 mysql -h${host} -u${username} -p${password} -e "${sql2}"
docker exec -it mysql80 mysql -h${host} -u${username} -p${password} -e "${sql3}"
docker exec -it mysql80 mysql -h${host} -u${username} -p${password} -e "${sql4}"

#######################################################################################################################
# 初始化数据库
#######################################################################################################################
cd ${sql_dir}
db11='tnb_account_'${env_src}
db12='tnb_content_'${env_src}
db13='tnb_oss_'${env_src}
db14='nacos_config'

echo 'import database data...'
# mysql -h${host} -u${username} -p${password} ${db11} < ${db1}.sql
# mysql -h${host} -u${username} -p${password} ${db21} < ${db2}.sql
# mysql -h${host} -u${username} -p${password} ${db31} < ${db3}.sql
# mysql -h${host} -u${username} -p${password} ${db41} < ${db4}.sql
docker exec -i mysql80 mysql -h${host} -u${username} -p${password} ${db1} < ${db11}.sql
docker exec -i mysql80 mysql -h${host} -u${username} -p${password} ${db2} < ${db12}.sql
docker exec -i mysql80 mysql -h${host} -u${username} -p${password} ${db3} < ${db13}.sql
docker exec -i mysql80 mysql -h${host} -u${username} -p${password} ${db4} < ${db14}.sql

#######################################################################################################################
# 清空数据表
#######################################################################################################################
sql1a="select CONCAT('truncate table tnb_account_tdb.',TABLE_NAME,';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='tnb_account_tdb'"
sql2a="select CONCAT('truncate table tnb_content_tdb.',TABLE_NAME,';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='tnb_content_tdb'"
sql3a="select CONCAT('truncate table reghao_bnt_tdb.',TABLE_NAME,';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='reghao_bnt_tdb'"
sql4a="select CONCAT('truncate table reghao_oss_tdb.',TABLE_NAME,';') FROM information_schema.TABLES WHERE TABLE_SCHEMA='reghao_oss_tdb'"

echo 'truncate database tables...'
result1a=$(mysql -h${host} -u${username} -p${password} -s -e "${sql1a}")
mysql -h${host} -u${username} -p${password} -s -e "${result1a}"

result2a=$(mysql -h${host} -u${username} -p${password} -s -e "${sql2a}")
mysql -h${host} -u${username} -p${password} -s -e "${result2a}"

result3a=$(mysql -h${host} -u${username} -p${password} -s -e "${sql3a}")
result3a1="SET FOREIGN_KEY_CHECKS = 0;"${result3a}"SET FOREIGN_KEY_CHECKS = 1;"
mysql -h${host} -u${username} -p${password} -s -e "${result3a1}"

result4a=$(mysql -h${host} -u${username} -p${password} -s -e "${sql4a}")
mysql -h${host} -u${username} -p${password} -s -e "${result4a}"

#######################################################################################################################
# 还原表数据到数据库
#######################################################################################################################
echo 'import table data...'
mysql -h${host} -u${username} -p${password} ${db11} < user_registry.sql

mysql -h${host} -u${username} -p${password} ${db21} < file_store_config.sql
mysql -h${host} -u${username} -p${password} ${db21} < vod_video_category.sql

mysql -h${host} -u${username} -p${password} ${db31} < file_store_node.sql
mysql -h${host} -u${username} -p${password} ${db31} < file_store_volumes.sql
mysql -h${host} -u${username} -p${password} ${db31} < file_upload_channel.sql
mysql -h${host} -u${username} -p${password} ${db31} < file_user_key.sql
mysql -h${host} -u${username} -p${password} ${db31} < file_user_node.sql
mysql -h${host} -u${username} -p${password} ${db31} < file_meta.sql

echo 'database init done...'