MySQL 从全备中提取单表表结构以及数据
全量备份:
[root@hankyoon ]# mysqldump -uroot -p -S /data/mysql/3306/proc/mysql.sock --single-transaction --set-gtid-purged=off --all-databases > all.sql
mysql> show databases; +--------------------+ | Database | +--------------------+ | information_schema | | mask | | mysql | | performance_schema | | sys | | tnotice | | yoon | +--------------------+ mysql> use mask; mysql> select count(*) from push_template; +----------+ | count(*) | +----------+ | 62 | +----------+ 1 row in set (0.00 sec)
提取表结构:
[root@hankyoon ]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `push_template`/!d;q' all.sql > push_template_schema.sql
sed -e'/./{H;$!d;}' -e 'x;/account_info/!d;q' all.sql
1、-e'/./{H;$!d;}' 通过正则匹配单个内容,H将模式空间中的内容追加至保持空间,$!d 最后一行不删除
2、-e 'x;/account_info/!d;q' x是把模式空间和保持空间内容进行互换, !d 不删除
#sed -i 's/push_template/push_template_bak/g' push_template_schema.sql
提取数据:
[root@hankyoon ]# grep 'INSERT INTO `push_template`' all.sql > push_template_data.sql #sed -i 's/push_template/push_template_bak/g' push_template_data.sql