mysql手工获取某个库表创建语句

脚本

vi a.sh
mysql -uroot -p123456 -N >a.txt<<!
select concat(concat('show create table ',TABLE_SCHEMA,'.',TABLE_NAME),'\\\G') from  information_schema.tables where TABLE_SCHEMA in ('test'); 
!
while read line
do 
mysql -uroot -p123456 -D test -e  "$line"
done <a.txt|tee b.txt

输出

[root@rds lhh]# sh a.sh 
mysql: [Warning] Using a password on the command line interface can be insecure.
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: t1
Create Table: CREATE TABLE `t1` (
  `TABLE_CATALOG` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
  `TABLE_SCHEMA` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
  `TABLE_NAME` varchar(64) CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
  `TABLE_TYPE` enum('BASE TABLE','VIEW','SYSTEM VIEW') CHARACTER SET utf8mb3 COLLATE utf8mb3_bin NOT NULL,
  `ENGINE` varchar(64) CHARACTER SET utf8mb3 DEFAULT NULL,
  `VERSION` int DEFAULT NULL,
  `ROW_FORMAT` enum('Fixed','Dynamic','Compressed','Redundant','Compact','Paged') CHARACTER SET utf8mb3 COLLATE utf8mb3_bin DEFAULT NULL,
  `TABLE_ROWS` bigint unsigned DEFAULT NULL,
  `AVG_ROW_LENGTH` bigint unsigned DEFAULT NULL,
  `DATA_LENGTH` bigint unsigned DEFAULT NULL,
  `MAX_DATA_LENGTH` bigint unsigned DEFAULT NULL,
  `INDEX_LENGTH` bigint unsigned DEFAULT NULL,
  `DATA_FREE` bigint unsigned DEFAULT NULL,
  `AUTO_INCREMENT` bigint unsigned DEFAULT NULL,
  `CREATE_TIME` timestamp NOT NULL,
  `UPDATE_TIME` datetime DEFAULT NULL,
  `CHECK_TIME` datetime DEFAULT NULL,
  `TABLE_COLLATION` varchar(64) CHARACTER SET utf8mb3,
  `CHECKSUM` bigint DEFAULT NULL,
  `CREATE_OPTIONS` varchar(256) CHARACTER SET utf8mb3 DEFAULT NULL,
  `TABLE_COMMENT` text CHARACTER SET utf8mb3
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
mysql: [Warning] Using a password on the command line interface can be insecure.
*************************** 1. row ***************************
       Table: t2
Create Table: CREATE TABLE `t2` (
  `id` int DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
You have mail in /var/spool/mail/root
[root@rds lhh]# vi a.sh 
mysql -uroot -p123456 -N >a.txt<<!
select concat(concat('show create table ',TABLE_SCHEMA,'.',TABLE_NAME,';'),'\\\G') from  information_schema.tables where TABLE_SCHEMA in ('test');
!
while read line
do
mysql -uroot -p123456 -D test -e  "$line"
done <a.txt|tee b.txt