更多内容请参考：《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:
|
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)，未经许可禁止转载！ 文 章 源 自 note.t4x.orgByrd's Blog-https://note.t4x.org/database/backup-more-databases/
申明：除非注明Byrd's Blog内容均为原创,未经许可禁止转载!详情请阅读版权申明!
