在 MySQL dba 的日常实际工作中,一个实例下有多个库,而我们常见的备份就是全库备份。那么问题就来了,如果需要恢复单个库或者单个表,怎么办了,网上有很多人都有多种方法,今天,我自己结合众多资料,将实践记录下来,以便供参考。
基本情况介绍:
MySQL 版本:mysql-5.5.36.tar.gz
操作系统: CentOS release 6.8 x64 (Final)
View Code
- [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
View Code
- 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)
# 从全备份文件中将需要的库的建表语句和 INSERT 数据拿出来,然后再导入
View Code
- [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