shell 提取msyql指定資料庫下表建立語句為單檔案

標籤：blog   io   ar   sp   for   檔案   資料   on   2014   

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/$dbwhile true;doif [ $ii -lt $ct ]then p=$ii;let ii++;echo p=$p echo ii=$iitable_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_namesleep 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.sqlecho ";" >> /chenenhui/$db/$table_name.sqlecho $table_nameelse echo '_while finished';rm /chenenhui/$db/create_table_help.tsv`$dbcn -N -e "DROP TABLE IF  EXISTS test.create_table_help;"`exit 0;fidone

shell 提取msyql指定資料庫下表建立語句為單檔案