MySQL备份与恢复（6）source命令恢复和mysql恢复数据⼀、恢复数据库实践
1、利⽤source命令恢复数据库
进⼊mysql数据库控制台，mysql -uroot -p登录后
mysql>use 数据库
然后使⽤source命令，后⾯参数为脚本⽂件（如这⾥⽤到的 .sql）
mysql>source oldboy_db.sql #这个⽂件是系统路径，默认是登陆mysql前的系统路径
[root@localhost ~]# mysql -uroot -pdubin
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is75
Server version: 5.5.32-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy_gbk |北京旅游景点排名
| oldboy_utf8 |
| performance_schema |
七喜客服+--------------------+
6 rows in set (0.00 sec)
mysql> drop database oldboy;
Query OK, 4 rows affected (7.34 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy_gbk |
| oldboy_utf8 |
| performance_schema |
+--------------------+
5 rows in set (0.00 sec)
mysql> system ls /opt
all_ mysql_bak_B_compact.sql mysql_ oldboy_bak1.sql
mysql_bak_B.sql mysqlbin_oldboy.000001 oldboy_bak.sql oldboy_
bak mysql_bak.sql oldboy_ table.sql
mysql> source /opt/mysql_bak_B.sql
Query OK, 0 rows affected (0.00 sec)
…………
Query OK, 0 rows affected (0.00 sec)
mysql> show databases;
+--------------------+地质灾害
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy_gbk |
| oldboy_utf8 |
| performance_schema |
+--------------------+
6 rows in set (0.00 sec)
mysql> select *st;
+----+---------+
| id | name |
+----+---------+
| 1 | oldboy |
| 2 | oldgirl |
| 3 | inca |
数据库恢复| 4 | zuma |
| 5 | kaka |
+----+---------+
5 rows in set (0.00 sec)
2、利⽤mysql命令恢复（标准）
问题：分库分表备份的数据如何快速恢复呢？
还是通过脚本指定的库和表，调⽤mysql命令恢复。[root@localhost ~]# cd /opt/
[root@localhost opt]# ll bak/
总⽤量156
-rw-r--r--. 1 root root 144569 9⽉2112:12
-rw-r--r--. 1 root root 534 9⽉2112:12 oldboy_
-rw-r--r--. 1 root root 1370 9⽉2112:12
-rw-r--r--. 1 root root 533 9⽉2112:12 oldboy_
[root@localhost opt]# mysql -uroot -pdubin
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is77
Server version: 5.5.32-log Source distribution
Copyright (c) 2000, 2013, Oracle and/or its affiliates. All rights reserved.
Oracle is a registered trademark of Oracle Corporation and/or its affiliates. Other names may be trademarks of their respective owners.
Type 'help;' or '\h'for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy_gbk |
| oldboy_utf8 |
| performance_schema |借钱马上到账
+--------------------+
6 rows in set (0.00 sec)
mysql> drop database oldboy;
Query OK, 4 rows affected (0.12 sec)
mysql> drop database mysql;
Query OK, 24 rows affected, 2 warnings (0.14 sec)
mysql> drop database oldboy_gbk;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> drop database oldboy_utf8;
Query OK, 0 rows affected, 2 warnings (0.00 sec)
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
+--------------------+
2 rows in set (0.00 sec)
mysql> quit
Bye
[root@localhost opt]# cd bak/
[root@localhost bak]# ll
总⽤量156
-rw-r--r--. 1 root root 144569 9⽉2112:12
-rw-r--r--. 1 root root 534 9⽉2112:12 oldboy_
-rw-r--r--. 1 root root 1370 9⽉2112:12
-rw-r--r--. 1 root root 533 9⽉2112:12 oldboy_
[root@localhost bak]# ls *.gz|awk -F "_"'{print $1}'
oldboy
1公顷等于多少平方千米
oldboy
[root@localhost bak]# ls *.gz|awk -F "."'{print $1}' #得到数据库名
mysql
oldboy_gbk
oldboy
oldboy_utf8
[root@localhost bak]#
[root@localhost bak]# gzip -d * #解压数据库
[root@localhost bak]# ls *.sql|awk -F "."'{print $1}'
mysql
oldboy_gbk
oldboy
oldboy_utf8
[root@localhost bak]# for dbname in `ls *.sql|awk -F "." '{print $1}'`; do mysql -uroot -pdubin < ${dbname}.sql;done #脚本批量恢复
[root@localhost bak]# mysql -uroot -pdubin -e "show databases;"
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| oldboy |
| oldboy_gbk |
| oldboy_utf8 |
| performance_schema |
+--------------------+
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论