mysql select into outfile 意表备份还原方式

最新推荐文章于 2023-01-08 22:47:36 发布

原创最新推荐文章于 2023-01-08 22:47:36 发布 · 1.7k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #备份

<pre name="code" class="sql">USE dsp_ad_center;
-- 生成备份语句
SELECT CONCAT( 'select *  into outfile ''/tmp/',
		a.TABLE_SCHEMA,
		'.',
		a.TABLE_NAME ,
		'.txt'' fields terminated by '','' optionally enclosed by ''"'' lines terminated by ''\\n'' ',
		' from ',
		DATABASE(),
		'.',
		a.TABLE_NAME ,
		';')  AS bkSql 
FROM  information_schema.`TABLES` a 
WHERE a.`TABLE_SCHEMA` = DATABASE();
-- 生成还原语句
SELECT CONCAT( 'load data infile ''/tmp/',
		a.TABLE_SCHEMA,
		'.',
		a.TABLE_NAME ,
		'.txt'' into table ',
		DATABASE(),
		'.',
		a.TABLE_NAME ,
		' fields terminated by '','' optionally enclosed by ''"'' lines terminated by ''\\n'' ',
		';')  AS bkSql 
FROM  information_schema.`TABLES` a 
WHERE a.`TABLE_SCHEMA` = DATABASE();


mysqldump -uroot -p123456 -h172.16.1.194 dsp_ad_center > dsp_ad_center.txt;