[[toc]]

背景

客户每个月都需要建对应月份的账期表，大概流程是从上个月复制出来建表语句 ，把表名替换成下个月的, 例如：ljc202405 替换ljc202406

脚本功能

中兴oracle 模式 和mysql模式 导出和导入表结构的

脚本使用示例

1、中兴库上区分什么模式

mysql> show variables like 'parse_mode';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| parse_mode    | 1     |
+---------------+-------+
1 row in set (0.01 sec)

注：1代表mysql 2代表Oracle

2、mysql 模式下导出和导入

#!/bin/bash
STTIME=`date "+%Y-%m-%d %H:%M:%S"`
echo $STTIME
echo "begin create views..."
basemonth=$(date -d "+0 month" +'%Y%m')
billcycletime=$(date -d "+1 month" +'%Y%m')
dbproxy_user="dbproxy"
dbproxy_host="192.168.3.11"
dbproxy_port="8880"
dbproxy_password="Bcv_1308"
sql_file="/goldendb/app/zxdbproxy1/t_name_$basemonth.sql"

# 检查SQL文件是否存在
if [ -f "$sql_file" ]; then
    echo "SQL文件已存在，跳过生成。"
   exit;
else
    echo "生成SQL文件..."
    # 构建MySQL命令
    mysql_command="SELECT table_name FROM information_schema.tables WHERE TABLE_NAME like '%$basemonth%' and CREATE_OPTIONS='DUPLICATE=\"Y\"';"

    # 执行MySQL命令，并将结果写入文件
    mysql -u$dbproxy_user -h$dbproxy_host -p$dbproxy_password -P$dbproxy_port -A -s --tee="$sql_file" -e "$mysql_command" >> t_name_$basemonth.sql
fi


while read table; do
 echo "Dumping table: $table"
 mysqldump -u$dbproxy_user -h$dbproxy_host -p$dbproxy_password -P$dbproxy_port  --add-drop-table=false --compact   --no-data   test "$table" >> "tmp_$billcycletime.sql"
done < t_name_$basemonth.sql

sed -i '/\/\*!401/d'  tmp_$billcycletime.sql 
sed "s/$basemonth/$billcycletime/g" tmp_$billcycletime.sql > c_table_$billcycletime.sql

mysql -u$dbproxy_user -h$dbproxy_host -p$dbproxy_password -P$dbproxy_port -A -Dtest  -e "source c_table_$billcycletime.sql"

3、oracle 模式下导出和导入—python脚本

#!/bin/bash
# usage eg: sh create_table.sh 202102  202103    ----$bm   basemonth  $tm  target month
# created liujichao   LINUX
#0 * 19 * * sh  /goldendb/app/zxdbproxy1/create_table.sh >> /goldendb/app/zxdbproxy1/create_table.log
STTIME=`date "+%Y-%m-%d %H:%M:%S"`
echo $STTIME
echo "begin create views..."
basemonth=$(date -d "+0 month" +'%Y%m')
billcycletime=$(date -d "+2 month" +'%Y%m')
dbproxy_user="dbproxy"
dbproxy_host="192.168.3.11"
dbproxy_port="8880"
dbproxy_password="Bcv_1308"
dbproxy_database="test"
sql_file="/goldendb/app/zxload1/t_name_$basemonth.sql"



# 检查SQL文件是否存在
if [ -f "$sql_file" ]; then
    echo "SQL文件已存在，跳过生成。"
   exit;
else
    echo "生成SQL文件..."
    # 构建MySQL命令
    mysql_command="select group_concat(name separator ' ') as 'tab_name' from (SELECT concat(TABLE_SCHEMA,'.',table_name) as name FROM information_schema.tables WHERE TABLE_NAME like '%$basemonth%' and CREATE_OPTIONS='DUPLICATE=\"Y\"');"

    # 执行MySQL命令，并将结果写入文件
    mysql -u$dbproxy_user -h$dbproxy_host -p$dbproxy_password -P$dbproxy_port -A -s --tee="$sql_file" -e "$mysql_command" >> t_name_$basemonth.sql
fi

grep -v "Logging to file" t_name_$basemonth.sql > t1_name_$basemonth.sql

while read table; do
 echo "Dumping table: $table"
python /goldendb/app/zxload1/bin/batchload.py --host=$dbproxy_host --user=$dbproxy_user --password=$dbproxy_password --port=$dbproxy_port --database=$dbproxy_database --do-tables $table --clusterid=1 --type=out --targetdir=/goldendb/app/zxload1  --parallel=3 --no-data --create-info  >> "dir_$billcycletime.sql"
done < t1_name_$basemonth.sql

tmp_dir=`cat ~/dir_$billcycletime.sql |grep "batch load directory" |awk -F/ '{print $NF}'`
echo $tmp_dir
cat $tmp_dir/$dbproxy_database/"$dbproxy_database"_ddl.sql > tmp_$billcycletime.sql 


sed -i '/CREATE DATABASE/d'  tmp_$billcycletime.sql
sed -i '/USE/d'  tmp_$billcycletime.sql
sed "s/$basemonth/$billcycletime/g" tmp_$billcycletime.sql > c_table_$billcycletime.sql

mysql -u$dbproxy_user -h$dbproxy_host -p$dbproxy_password -P$dbproxy_port -A -D$dbproxy_database  -e "source c_table_$billcycletime.sql"

引用

3、oracle 和mysql模式下导出和导入—show create tables

#!/bin/bash
# usage eg: sh create_table.sh 202102  202103    ----$bm   basemonth  $tm  target month
# created liujichao   LINUX
#0 * 19 * * sh  /goldendb/app/zxdbproxy1/create_table.sh >> /goldendb/app/zxdbproxy1/create_table.log
STTIME=`date "+%Y-%m-%d %H:%M:%S"`
echo $STTIME
echo "begin create views..."
basemonth=$(date -d "+0 month" +'%Y%m')
billcycletime=$(date -d "+1 month" +'%Y%m')
dbproxy_user="dbproxy"
dbproxy_host="10.230.167.42"
dbproxy_port="8880"
dbproxy_password="db1x@NJ+1"
dbproxy_database="billing"
sql_file="/appdata1/goldendb/zxmanager/t_name_$basemonth.sql"


# 检查SQL文件是否存在
if [ -f "$sql_file" ]; then
    echo "SQL文件已存在，跳过生成。"
   exit;
else
    echo "生成SQL文件..."
    # 构建MySQL命令
    mysql_command="SELECT concat('show create table ',TABLE_SCHEMA,'.',table_name,'\G') as name FROM information_schema.tables WHERE TABLE_NAME like '%$basemonth%' and CREATE_OPTIONS='DUPLICATE=\"Y\"';"

    # 执行MySQL命令，并将结果写入文件
    mysql -u$dbproxy_user -h$dbproxy_host -p$dbproxy_password -P$dbproxy_port -A -s --tee="$sql_file" -e "$mysql_command" >> t_name_$basemonth.sql
fi

grep -v "Logging to file" t_name_$basemonth.sql > t1_name_$basemonth.sql

while read table; do
 echo "Dumping table: $table"
      mysql -u$dbproxy_user -h$dbproxy_host -p$dbproxy_password -P$dbproxy_port -A -s -e"$table" >>  tmp_$billcycletime.sql
done < t1_name_$basemonth.sql

sed -i '/1. row/d'  tmp_$billcycletime.sql
sed -i '/sloth_rowid/d' tmp_$billcycletime.sql
sed -i 's/CREATE TABLE: / /' tmp_$billcycletime.sql
sed -i '/       TABLE:/d' tmp_$billcycletime.sql
sed -i '/distributed by duplicate/ s/$/;/' tmp_$billcycletime.sql
sed "s/$basemonth/$billcycletime/g" tmp_$billcycletime.sql > c_table_$billcycletime.sql

mysql -u$dbproxy_user -h$dbproxy_host -p$dbproxy_password -P$dbproxy_port -A -D$dbproxy_database  -e "source c_table_$billcycletime.sql"

问题

1、如果数据库是oracle模式 使用mysql脚本，导出是没有问题的 ，导入会报错

[zxdbproxy1@prigdb1 ~]$ mysql -udbproxy  -P8880 -Dtest   -A   -e "source tmp_ljc.sql"
mysql: [Warning] Using a password on the command line interface can be insecure.
ERROR 10876 (HY000) at line 7 in file: 'tmp_ljc.sql': ERR: User variable does not support func or field or subselect!