mysql数据库的完全备份_mysql全库备份

mysqldump备份与恢复

MySQL自带的备份工具，可方便实现对MySQL的备份
可以将指定的库、表导出为SQL脚本
使用命令mysql导入备份的数据

mysqldump -u root -p --all-databses > all-data-$(date +%F).sql ###备份所有数据库
mysqldump -u root -p -databases auth mysql > auth-mysql.sql ###备份auth和mysql库
mysqldump -u root -p auth > auth-$(data +%F).sql ###备份auth数据库
mysqldump -u root -p mysql user > mysql-user-$(date +%F).sql ###备份mysql的user表
mysqldump -u root -p -d mysql user > /tmp/desc-mysql-user.sql ###备份mysql库user表的结构

1
2
3
4
5
6

mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| myadm              |
| mysql              |
| performance_schema |
| student            |
| sys                |
| tom                |
+--------------------+
7 rows in set (0.00 sec)
[root@server3 opt]# mysqldump -u root -p tom > /opt/tom.sql
Enter password: 
[root@server3 opt]# ls
tom.sql   # 导出的备份文件

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18

对所有库进行完全备份

[root@server3 opt]# mysqldump -uroot -pabc123 --all-databases > /backup/all.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

1
2
3

mysqldump备份数据表

musqldump可针对库内特定的表进行备份
使用mysqldump备份表的操作

mysqldump -u 用户名 -p 【密码】【选项】选项库名 表名 > /备份路径/备份文件名

1
2

示例：

mysql> use tom;
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_tom |
+---------------+
| chengji       |
+---------------+
1 row in set (0.00 sec)

mysql> select * from chengji;
+----------+-------+
| name     | point |
+----------+-------+
| xiaowang |    77 |
| xiaoli   |    75 |
+----------+-------+
2 rows in set (0.00 sec)
#复制tom表 name字段 张三内容 生成一张新表pp
mysql> create table pp as select * from chengji where name='xiaowang';
Query OK, 1 row affected (0.03 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| chengji       |
| pp            |
+---------------+
2 rows in set (0.00 sec)
#新生成表
mysql> select *from pp;
+----------+-------+
| name     | point |
+----------+-------+
| xiaowang |    77 |
+----------+-------+
1 row in set (0.00 sec)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43

[root@server3 ~]# mysql -u root -p123123 chengji pp > /opt/pp.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
[root@server3 ~]# ls /opt
pp.sql

1
2
3
4
5

恢复数据库

1、使用mysqldump导出的脚本，可使用导入的方法：

1）source命令【作用于mysql模式下】
2）mysql命令【作用于于linux模式下】

2、使用source恢复数据库的步骤
登录到mysql数据库

执行source备份sql脚本的路径

source恢复的示例

MYSQL[(none)]> source /backup/all-data.sql

1
2

模拟删除表

mysql> use tom;
Database changed
#删除表
mysql> drop table chengji;
Query OK, 0 rows affected (0.02 sec)

mysql> drop table pp;
Query OK, 0 rows affected (0.01 sec)

1
2
3
4
5
6
7
8
9

进行恢复

mysql> use tom;

Database changed
mysql> use tom;
Database changed
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| tom           |
+---------------+
1 row in set (0.00 sec)

mysql> select * from tom;
+----+----------+----------+
| id | name     | address  |
+----+----------+----------+
|  1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)

mysql> insert into tom (name,address) values('lisi','wuxi');
Query OK, 1 row affected (0.00 sec)

mysql> select * from tom;
+----+----------+----------+
| id | name     | address  |
+----+----------+----------+
|  1 | zhangsan | hangzhou |
|  2 | lisi     | wuxi     |
+----+----------+----------+
2 rows in set (0.00 sec)

mysql> create table pp as select * from tom where name='zhangsan';
Query OK, 1 row affected (0.02 sec)
Records: 1  Duplicates: 0  Warnings: 0

mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| pp            |
| tom           |
+---------------+
2 rows in set (0.00 sec)

mysql> select * from pp;
+----+----------+----------+
| id | name     | address  |
+----+----------+----------+
|  1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)

mysql> Ctrl-C -- exit!
Aborted
[root@server3 opt]# mysqldump -u root -p tom pp > /opt/pp.sql
Enter password: 
[root@server3 opt]# ls /opt/
all.sql  opt.sql  pp.sql  rh  tom.tom
[root@server3 opt]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 11
Server version: 5.6.36-log Source distribution

Copyright (c) 2000, 2017, 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> drop table tom;
ERROR 1046 (3D000): No database selected
mysql> use tom;
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> drop table tom;
Query OK, 0 rows affected (0.01 sec)

mysql> drop table pp;
Query OK, 0 rows affected (0.00 sec)
```java
mysql> show tables;
Empty set (0.00 sec)
#恢复
mysql> source /opt/all.sql;
..省略内容
mysql> use tom;
Database changed
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| tom           |
+---------------+
1 row in set (0.00 sec)

mysql> source /opt/pp.sql;
mysql> show tables;
+---------------+
| Tables_in_tom |
+---------------+
| pp            |
| tom           |
+---------------+
2 rows in set (0.00 sec)

mysql> select * from pp;
+----+----------+----------+
| id | name     | address  |
+----+----------+----------+
|  1 | zhangsan | hangzhou |
+----+----------+----------+
1 row in set (0.00 sec)

#已经恢复  这边我们是恢复所有数据库  
#也可以单独的对标进行备份恢复

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122