数据库移行脚本

迁移数据库用的脚本。

pt-show-grants和mysqlpump需要另外安装。

用于导出用户和权限信息

#!/bin/bash
# 读入env-export.txt文件里1,2,3行的用户名， 密码， db url信息
content="user"
{
read -r db_username
read -r db_pwd
read -r db_url
} < env-export.txt

output_file="execution-log.txt"
action="export"
echo "---------------------------------------------">>$output_file
echo "START "$action" "$content>>$output_file
date1=$(date)
TZ=Asia/Shanghai date "+%Y-%m-%d %H:%M:%S">>$output_file

# 执行的语句导出mysql.user表里的用户信息和权限信息
pt-show-grants --charset=utf8 --ignore 'mysql.infoschema'@'localhost','mysql.sys'@'localhost','rdsadmin'@'localhost','rdsrepladmin'@'%' -u $db_username -p$db_pwd -h $db_url > $content"-dump.sql"

echo "END "$action" "$content>>$output_file
date2=$(date)
TZ=Asia/Shanghai date "+%Y-%m-%d %H:%M:%S">>$output_file

timestamp1=$(date -d "$date1" +%s)
timestamp2=$(date -d "$date2" +%s)
diff=$(($timestamp2 - $timestamp1))
hours=$(($diff/3600))
minutes=$(($diff%3600 / 60))
seconds=$(($diff%60))

echo "执行所花时间：$hours時間$minutes分$seconds秒.">>$output_file

用于导出bi数据库信息。

#!/bin/bash
# 读入env-export.txt文件里1,2,3行的用户名， 密码， db url信息
content="user"
{
read -r db_username
read -r db_pwd
read -r db_url
} < env-export.txt

output_file="execution-log.txt"
action="export"
echo "---------------------------------------------">>$output_file
echo "START "$action" "$content>>$output_file
date1=$(date)
TZ=Asia/Shanghai date "+%Y-%m-%d %H:%M:%S">>$output_file

# 执行的语句 mysqlpump比mysqldump更好用的地方在于可以通过--default-parallelism=10指定多线程执行导出
mysqlpump --databases bi --set-gtid-purged=OFF --default-parallelism=10 -u $db_username -p$db_pwd -h $db_url > $content"-dump.sql"

echo "END "$action" "$content>>$output_file
date2=$(date)
TZ=Asia/Shanghai date "+%Y-%m-%d %H:%M:%S">>$output_file

timestamp1=$(date -d "$date1" +%s)
timestamp2=$(date -d "$date2" +%s)
diff=$(($timestamp2 - $timestamp1))
hours=$(($diff/3600))
minutes=$(($diff%3600 / 60))
seconds=$(($diff%60))

echo "执行所花时间：$hours時間$minutes分$seconds秒.">>$output_file

导入user数据脚本：

#!/bin/bash
# 读入env-export.txt文件里1,2,3行的用户名， 密码， db url信息
content="user"
{
read -r db_username
read -r db_pwd
read -r db_url
} < env-export.txt

output_file="execution-log.txt"
action="export"
echo "---------------------------------------------">>$output_file
echo "START "$action" "$content>>$output_file
date1=$(date)
TZ=Asia/Shanghai date "+%Y-%m-%d %H:%M:%S">>$output_file

# 导入user数据语句
mysql -u $db_username -p$db_pwd -h $db_url --force < $content"-dump.sql"

echo "END "$action" "$content>>$output_file
date2=$(date)
TZ=Asia/Shanghai date "+%Y-%m-%d %H:%M:%S">>$output_file

timestamp1=$(date -d "$date1" +%s)
timestamp2=$(date -d "$date2" +%s)
diff=$(($timestamp2 - $timestamp1))
hours=$(($diff/3600))
minutes=$(($diff%3600 / 60))
seconds=$(($diff%60))

echo "执行所花时间：$hours時間$minutes分$seconds秒.">>$output_file