逻辑备份(mysqldump/select into outfile)

#mysqldump备份
shell> mysqldump -uroot -p -P4306 sakila actor>E:sakila-actor.sql
shell> mysqldump -uroot -p -P4306 --databases mycatdb1 mycatdb2>E:mycatdb1-2.sql
shell> mysqldump -uroot -p -P4306 --all-databases>E:all4306.sql

#还原
shell> mysql -uroot -p -P4306 test<E:sakila-actor.sql

#迁移数据  备份|还原
shell> mysqldump -uroot -pmysql4306 -P4306 sakila actor|mysql -uroot -pmysql4306 -P4306 test


#select * into outfile导出
mysql> select * from sakila.actor into outfile 'E:/sakila-actor1.txt';
#字段Tab隔开，字符型数据用用引号括起来
mysql> select * from sakila.actor into outfile 'E:/sakila-actor2.txt' FIELDS TERMINATED BY '	' OPTIONALLY ENCLOSED BY '"';

#-T 目标目录，生成记录文本(actor.txt)，和建表脚本(actor.sql)
shell> mysqldump -uroot -pmysql4306 -P4306 -T E: sakila actor "--fields-terminated-by=	" "--fields-optionally-enclosed-by=""
--此行用于注释"
#导出xml格式
shell> mysqldump -uroot -pmysql4306 -P4306 -X sakila actor>E:sakila-actor.xml

#使用mysql命令导出sakila库下actor表的记录，带列名
shell> mysql -uroot -pmysql4306 -P4306 -e"select * from actor" sakila>E:sakila-actor3.txt
shell> mysql -uroot -pmysql4306 -P4306 -e"select * from sakila.actor">E:sakila-actor4.txt


#load data infile导入文本文件
mysql> delete from test.actor;
mysql> load data infile 'E:/sakila-actor1.txt' into table actor;
mysql> delete from test.actor;
mysql> load data infile 'E:/sakila-actor2.txt' into table actor FIELDS TERMINATED BY '	' OPTIONALLY ENCLOSED BY '"';

#用mysqlimport命令导入文本文件
mysql> delete from test.actor;
shell> mysqlimport -uroot -pmysql4306 -P4306 test E:actor.txt "--fields-terminated-by=	" "--fields-optionally-enclosed-by=""
--此行用于注释"