共计 2306 个字符,预计需要花费 6 分钟才能阅读完成。
在 MySQL dba 的日常实际工作中,一个实例下有多个库,而我们常见的备份就是全库备份。那么问题就来了,如果需要恢复单个库或者单个表,怎么办了,网上有很多人都有多种方法,今天,我自己结合众多资料,将实践记录下来,以便供参考。
基本情况介绍:
MySQL 版本:mysql-5.5.36.tar.gz
操作系统:CentOS release 6.8 x64 (Final)
一、全库备份

[root@pre ~]# cat backup.sh | |
#!/bin/bash | |
/home/mysql/mysql55_3310/bin/mysqldump --defaults-file=/usr/local/local.cnf -E --triggers -e --max_allowed_packet=16777216 --net_buffer_length=16384 --master-data=2 --single-transaction --all-databases --quick | gzip >/home/dbbackup/all_database_bak_471_`date +%Y-%m-%d_%H_%M_%S`.sql.gz | |
[root@pre ~]# cat /usr/local/local.cnf | |
[client] | |
host=localhost | |
port=3310 | |
user=root | |
password=123456 | |
socket=/home/mysql/mysql55_3310/data/mysql.sock | |
[root@pre ~]# sh backup.sh |
二、显示当前库

mysql> show databases; | |
+--------------------+ | |
| Database | | |
+--------------------+ | |
| information_schema | | |
| db_log | | |
| db_users | | |
| mysql | | |
| performance_schema | | |
+--------------------+ | |
5 rows in set (0.00 sec) | |
mysql> use db_log; | |
Database changed | |
mysql> show tables; | |
Empty set (0.00 sec) |
三、现在从全备文件中恢复 db_log 单个库
# 从全备份文件中将需要的库的建表语句和 INSERT 数据拿出来,然后再导入

[root@pre ~]# sed -n '/^-- Current Database: `db_log`/,/^-- Current Database: `/p' all_database_bak_471_2017-12-04_15_36_38.sql > db_log.sql & | |
# 导入库中 | |
[root@pre ~]# /home/mysql/mysql55_3310/bin/mysql -uroot -p < db_log.sql & | |
mysql> use db_log; | |
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 | |
mysql> show tables; | |
+------------------------+ | |
| Tables_in_db_log | | |
+------------------------+ | |
| api_online_logs | | |
| onlinegame | | |
| onlinegame_gamestats | | |
| onlinegame_playerstats | | |
| onlinegame_type | | |
| osa_menu_url | | |
| osa_module | | |
| osa_quick_note | | |
| osa_sys_log | | |
| osa_system | | |
| osa_user | | |
| osa_user_group | | |
| sample | | |
| user_online | | |
| user_psw_audit | | |
+------------------------+ | |
15 rows in set (0.00 sec) | |
mysql> | |
# 数据已经恢复了 |
四、现在模拟恢复单个表的数据
# 先删除一个表:user_online

mysql> drop table user_online; | |
Query OK, 0 rows affected (0.01 sec) | |
# 1、从全备份中提取出该表的建表语句 | |
[root@pre ~]# sed -e'/./{H;$!d;}' -e 'x;/CREATE TABLE `user_online`/!d;q' all_database_bak_471_2017-12-04_15_36_38.sql > user_online.sql & | |
# 2、提取该表的 insert into 语句 | |
[root@pre ~]# grep -i 'INSERT INTO `user_online`' all_database_bak_471_2017-12-04_15_36_38.sql >> user_online.sql & | |
# 3、导入到对应的库中 | |
[root@pre ~]# /home/mysql/mysql55_3310/bin/mysql -uroot -p <user_online.sql & | |
# 4、查看数据 | |
mysql> select count(*) from user_online; | |
+----------+ | |
| count(*) | | |
+----------+ | |
| 9 | | |
+----------+ | |
1 row in set (0.01 sec) | |
# 已经恢复完毕 |
本文永久更新链接地址 :http://www.linuxidc.com/Linux/2017-12/149496.htm
正文完
星哥玩云-微信公众号
