mysql如何通过全库备份恢复单张表

##查看全库数据量
root@localhost:mysql 10:27:54 >select concat(round(sum(data_length/1024/1024),2),'MB') as data_length_MB,
-> concat(round(sum(index_length/1024/1024),2),'MB') as index_length_MB
-> from information_schema.tables;
+----------------+-----------------+
| data_length_MB | index_length_MB |
+----------------+-----------------+
| 10342.48MB | 0.38MB |
+----------------+-----------------+
1 row in set (0.02 sec)

##查看模拟要恢复的表student
root@localhost:mysql 10:38:22 >use test;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@localhost:test 10:38:28 >show tables;
+----------------+
| Tables_in_test |
+----------------+
| student |
| user_base |
| ywj |
+----------------+
3 rows in set (0.00 sec)

root@localhost:test 10:38:31 >select * from student;
+--------+------+------+
| xuehao | name | age |
+--------+------+------+
| 1001 | aaa | 18 |
| 1002 | bbb | 19 |
| 1003 | ccc | 20 |
| 1004 | dddd | 19 |
| 1008 | ccc | 100 |
+--------+------+------+
5 rows in set (0.01 sec)

##进行全库备份
[root@localhost ~]# /usr/local/mysql/bin/mysqldump -uroot -p --all-databases > /tmp/all.sql

##删除要恢复的表student
root@localhost:test 10:39:28 >drop table student;
Query OK, 0 rows affected (0.01 sec)

root@localhost:test 10:45:05 >show tables;
+----------------+
| Tables_in_test |
+----------------+
| user_base |
| ywj |
+----------------+
2 rows in set (0.00 sec)

##通过输出重定向来获取表结构的SQL文件
[root@localhost ~]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `student`/!d;q' /tmp/all.sql > student_create.sql

##通过输出重定向从全库备份中查询出表的数据
[root@localhost ~]# grep 'INSERT INTO `student`' /tmp/all.sql > /tmp/student_data.sql

##通过生成的SQL文件恢复表结构及数据
root@localhost:mysql 10:45:07 > use test;
root@localhost:test 10:45:14 >source /tmp/student_create.sql
Query OK, 0 rows affected, 1 warning (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.01 sec)

Query OK, 0 rows affected (0.00 sec)

root@localhost:test 10:45:21 >show tables;
+----------------+
| Tables_in_test |
+----------------+
| student |
| user_base |
| ywj |
+----------------+
3 rows in set (0.00 sec)

root@localhost:test 10:45:24 >source /tmp/student_data.sql
Query OK, 5 rows affected (0.02 sec)
Records: 5 Duplicates: 0 Warnings: 0

root@localhost:test 10:45:32 >select * from student;
+--------+------+------+
| xuehao | name | age |
+--------+------+------+
| 1001 | aaa | 18 |
| 1002 | bbb | 19 |
| 1003 | ccc | 20 |
| 1004 | dddd | 19 |
| 1008 | ccc | 100 |
+--------+------+------+
5 rows in set (0.00 sec)

至此恢复完成~