select … into outfile
select..into语句也是一种逻辑备份的方法，或者更准确地说是导出一张表中的数据
SELECT [column 1],[column2] … INTO OUTFILE'file_name' from table where… …
MySQL> select * into outfile '/root/test1.txt' from test1; //必须是mysql用户可写
ERROR 1 (HY000): Can't create/write to file'/root/test1.txt' (Errcode: 13)
mysql> select * into outfile '/home/mysql/test1.txt' from test1;
Query OK, 7 rows affected (0.00 sec)
mysql> quit
Bye
# cat/home/mysql/test1.txt
1 wu
2 terry
3 tang
4 jack
4 cat
3 \N
3 dog
# mysql -e"select * into outfile '/home/mysql/test_bak' from test1.test1";
# cat/home/mysql/test_bak
1 wu
2 terry
3 tang
4 jack
4 cat
3 \N
3 dog
逻辑备份的恢复:
mysqldump的恢复操作比较简单，因为备份的文件就是导出的SQL语句
在shell命令行导入：
# mysqldump-uroot -p --socket=/var/run/mysqld/mysql5.socket >test1.sql test1
使用source恢复：
mysql> usetest1;
mysql>source /root/test1.sql;
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
… …
注：mysqldump可以导出存储过程、触发器、事件、数据，但是却不能导出视图
