MySQL备份多个数据库

  • A+
所属分类：Database

更多内容请参考：《MYSQL数据库备份、还原操作》：

MySQL
0
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| bbs                |
| blog               |
| mysql              |
| performance_schema |
| test               |
| www                |
+--------------------+
7 rows in set (0.31 sec)
[root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' -B test|gzip >/backup/test-B-$(date +%F).sql.gz   #备份test数据库      
[root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' -B mysql|gzip >/backup/mysql-B-$(date +%F).sql.gz    #备份mysql数据库    
[root@Mysql ~]# /usr/local/mysql/bin/mysqldump -uroot -p'admin' -B mysql test|gzip >/backup/mysql-test-B-$(date +%F).sql.gz    #同时备份test和mysql数据库
[root@Mysql ~]# ll /backup
total 484
-rw-r--r--. 1 root root 163367 Jun  5 21:41 mysql-B-2014-06-05.sql.gz
-rw-r--r--. 1 root root 163417 Jun  5 21:41 mysql-test-B-2014-06-05.sql.gz
-rw-r--r--. 1 root root    508 Jun  5 21:41 test-B-2014-06-05.sql.gz

10.0.24-MariaDB:

MySQL
0
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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
MariaDB [byrd]> select * from test;                    
+----+----------+
| id | name     |
+----+----------+
|  1 | 赵云     |
|  2 | zhangfei |
|  3 | liubei   |
|  4 | guanyu   |
|  5 | 曹操     |
|  6 | caopei   |
|  7 | 孙策     |
+----+----------+
 
[root@Test ~]# mysqldump -usystem -p'admin123' byrd >/tmp/$(date +%F).sql    #必须数据库存在
[root@Test ~]# egrep -v "^-|\*|^$" /tmp/2014-04-19.sql
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
  `id` int(4) NOT NULL AUTO_INCREMENT,
  `name` char(20) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8;
LOCK TABLES `test` WRITE;
INSERT INTO `test` VALUES (1,'赵云'),(2,'zhangfei'),(3,'liubei'),(4,'guanyu'),(5,'曹操'),(6,'caopei'),(7,'孙策');
UNLOCK TABLES;
 
MariaDB [(none)]> delete from byrd.test;                                    
 
MariaDB [(none)]> select * from byrd.test;
 
[root@Test ~]# mysql -usystem -p'admin123' byrd </tmp/2014-04-19.sql
[root@Test ~]# mysql -usystem -p'admin123'
 
MariaDB [(none)]> select * from byrd.test;
+----+----------+
| id | name     |
+----+----------+
|  1 | 赵云     |
|  2 | zhangfei |
|  3 | liubei   |
|  4 | guanyu   |
|  5 | 曹操     |
|  6 | caopei   |
|  7 | 孙策     |
+----+----------+
[root@Test ~]# mysqldump -usystem -p'admin123' -B byrd >/tmp/$(date +%F).sql
 
[root@Test ~]# diff /tmp/2014-04-19*
19,26d18
< -- Current Database: `byrd`
< CREATE DATABASE /*!32312 IF NOT EXISTS*/ `byrd` /*!40100 DEFAULT CHARACTER SET utf8 */;
< USE `byrd`;
MariaDB [(none)]> drop database byrd;
 
MariaDB [(none)]> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
 
[root@Test ~]# mysql -usystem -p'admin123' </tmp/2014-04-19.sql      
ERROR 1046 (3D000) at line 22: No database selected
[root@Test ~]# mysql -usystem -p'admin123' byrd </tmp/2014-04-19.sql  
ERROR 1049 (42000): Unknown database 'byrd'
[root@Test ~]# mysql -usystem -p'admin123' byrd </tmp/2014-04-19B.sql
ERROR 1049 (42000): Unknown database 'byrd'
[root@Test ~]# mysql -usystem -p'admin123' </tmp/2014-04-19B.sql
 
MariaDB [(none)]> show databases;    
+--------------------+
| Database           |
+--------------------+
| byrd               |
| information_schema |
| mysql              |
| performance_schema |
+--------------------+
 
MariaDB [(none)]> select * from byrd.test;
+----+----------+
| id | name     |
+----+----------+
|  1 | 赵云     |
|  2 | zhangfei |
|  3 | liubei   |
|  4 | guanyu   |
|  5 | 曹操     |
|  6 | caopei   |
|  7 | 孙策     |
+----+----------+
[root@Test /]# mysqldump -usystem -p'admin123' -B byrd >/tmp/$(date +%F).sql
[root@Test /]# mysqldump -usystem -p'admin123' -B byrd|gzip >/tmp/$(date +%F).sql.gz
[root@Test /]# mysqldump -usystem -p'admin123' -B byrd|bzip2 >/tmp/$(date +%F).sql.bz2
[root@Test /]# mv /tmp/2016-04-19B.sql /tmp/2016-04-19.sql
[root@Test /]# ll /tmp/
total 12
-rw-r--r--. 1 root root 2094 Apr 19 21:23 2014-04-19.sql
-rw-r--r--. 1 root root  953 Apr 19 21:25 2014-04-19.sql.bz2
-rw-r--r--. 1 root root  835 Apr 19 21:24 2014-04-19.sql.gz
 
MariaDB [byrd]> CREATE TABLE `dns` (                          
    -> `id` int(10) unsigned NOT NULL primary key auto_increment,
    -> `zone`  varchar(255)  NOT  NULL,
    -> `host` varchar(255) NOT NULL default '@',
    -> `type`  enum('MX','CNAME','NS','SOA','A','PTR')  NOT  NULL
    -> );
MariaDB [byrd]> show tables;
+----------------+
| Tables_in_byrd |
+----------------+
| dns            |
| test           |
+----------------+
 
MariaDB [byrd]> INSERT INTO dns(zone,host,type) VALUES
    -> ('t4x.org',  '@',  'SOA');
mysql>select * from dns;
+----+---------+------+------+
| id | zone    | host | type |
+----+---------+------+------+
|  1 | t4x.org | @    | SOA  |
+----+---------+------+------+
 
[root@Test /]# mysqldump -usystem -p'admin123'  byrd dns >/tmp/dns.sql    #备份byrd数据库的dns表
[root@Test /]# egrep -v "^-|^$|\*" /tmp/dns.sql                        
DROP TABLE IF EXISTS `dns`;
CREATE TABLE `dns` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `zone` varchar(255) NOT NULL,
  `host` varchar(255) NOT NULL DEFAULT '@',
  `type` enum('MX','CNAME','NS','SOA','A','PTR') NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;
LOCK TABLES `dns` WRITE;
INSERT INTO `dns` VALUES (1,'t4x.org','@','SOA');
UNLOCK TABLES;

申明：本文由BYRD原创(基于Mysql5.6.16、10.0.24-MariaDB)，未经许可禁止转载！

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: