mysql数据库提取语句_shell 提取mysql指定数据库下表创建语句为单文件

dbcn="mysql -h172.16.1.194 -uroot -p123456";

db=dsp_ad_center;

ii=0;

ct=`$dbcn -N -e " SELECT COUNT(1) FROM information_schema.TABLES AS t WHERE t.TABLE_SCHEMA = '$db' AND t.TABLE_TYPE = 'BASE TABLE' ; "`;

mkdir -p /chenenhui/$db

while true;

do

if [ $ii -lt $ct ]

then

p=$ii;

let ii++;

echo p=$p

echo ii=$ii

table_name=`$dbcn -N -e " SELECT CONCAT( t.TABLE_SCHEMA,'.',t.TABLE_NAME ) FROM information_schema.TABLES AS t where t.TABLE_SCHEMA = '$db' AND t.TABLE_TYPE = 'BASE TABLE' limit $p,1;"`;

echo table_name=$table_name

sleep 0;

ss=`$dbcn -N -e "SHOW CREATE TABLE $table_name ;"`

echo -e "$ss" > /chenenhui/$db/create_table_help.tsv

`$dbcn -e " CREATE TABLE IF NOT EXISTS test.create_table_help

( table_name VARCHAR(1500) DEFAULT '',

table_script VARCHAR(1500) DEFAULT '',

xh INT AUTO_INCREMENT,PRIMARY KEY(xh)

) CHARSET = utf8 ;

truncate table test.create_table_help;

LOAD DATA LOCAL INFILE '//chenenhui//$db//create_table_help.tsv' INTO TABLE test.create_table_help CHARACTER SET utf8;"`

ss_script=`$dbcn -N -e "SELECT concat(CASE WHEN xh = 1 THEN concat('USE ','$db;',char(10) ) ELSE table_name END , table_script ) FROM test.create_table_help ;"`

echo -e "$ss_script" > /chenenhui/$db/$table_name.sql

echo ";" >> /chenenhui/$db/$table_name.sql

echo $table_name

else

echo '_while finished';

rm /chenenhui/$db/create_table_help.tsv

`$dbcn -N -e "DROP TABLE IF EXISTS test.create_table_help;"`

exit 0;

fi

done