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 |
mysql数据库迁移命令
| 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
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小时内删除。