逻辑备份示例：
1.先导出表中的数据(里面存放的仅仅是文本数据)
mysql> select * into outfile '/tmp/course.txt' from course;
当然，也可以在from后加上where字句筛选表中匹配的数据，用来导入其他数据库的其他表中；
注意：如果导出至/tmp目录下没有问题，但如果是其他目录会出现ERROR 1 (HY000): Can't create/write to file '/mnt/course.txt' (Errcode: 13 - Permission denied)问题；
[root@www ~]# cat /tmp/course.txt
1 Linux
2 C++
3 C
4 sql
5 A
2.恢复数据也不能使用source导入了，而是使用load data infile ‘文件’ into table tb_name的方式来导入该纯文本数据，
所以要使要导入表的表结构和原表一致，才能使恢复后和原来一样；
2.1仿照要备份的表创建一个表结构一致的空表：
mysql> use mydb;
mysql> desc course;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| cno | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| cname | varchar(30) | NO | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
mysql> create table courses like course;
mysql> desc courses;
+-------+---------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+---------------------+------+-----+---------+----------------+
| cno | tinyint(3) unsigned | NO | PRI | NULL | auto_increment |
| cname | varchar(30) | NO | | NULL | |
+-------+---------------------+------+-----+---------+----------------+
2.2删除course表来模拟表丢失：
mysql> drop table course;
2.3导入数据：
mysql> select * from courses;
mysql> load data infile '/tmp/course.txt' into table courses;
mysql> select * from courses;
+-----+-------+
| cno | cname |
+-----+-------+
| 1 | Linux |
| 2 | C++ |
| 3 | C |
| 4 | sql |
| 5 | A |
+-----+-------+
3.而且还原数据的过程也不会记录到二进制日志文件中，以为执行的不是DML语句
mysql> show master logs;
+----------------+-----------+
| Log_name | File_size |
+----------------+-----------+
| www-bin.000001 | 744 |
+----------------+-----------+
mysql> mysql> show binlog events in 'www-bin.000001';
+----------------+-----+--------------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+----------------+-----+--------------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| www-bin.000001 | 4 | Format_desc | 1 | 120 | Server ver: 5.6.11-log, Binlog ver: 4 |
| www-bin.000001 | 120 | Query | 1 | 219 | use `mydb`; create table courses like course |
| www-bin.000001 | 219 | Query | 1 | 338 | use `mydb`; DROP TABLE `course` /* generated by server */ |
| www-bin.000001 | 338 | Query | 1 | 417 | BEGIN |
| www-bin.000001 | 417 | Begin_load_query | 1 | 472 | ;file_id=1;block_len=28 |
| www-bin.000001 | 472 | Execute_load_query | 1 | 713 | use `mydb`; LOAD DATA INFILE '/tmp/course.txt' INTO TABLE `courses` FIELDS TERMINATED BY '\t' ENCLOSED BY '' ESCAPED BY '\\' LINES TERMINATED BY '\n' (`cno`, `cname`) ;file_id=1 |
| www-bin.000001 | 713 | Xid | 1 | 744 | COMMIT /* xid=715 */ |
+----------------+-----+--------------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
阅读(479) | 评论(0) | 转发(0) |
