mysql备份多个库用什么命令_MySQL备份与恢复（2）mysqldump多个命令参数

mysql> select * fromtest;+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

+----+---------+

5 rows in set (0.00sec)

[root@localhost~]# mysqldump -uroot -pdubin oldboy >/opt/mysql_bak.sql

[root@localhost~]# egrep -v "#|\*|--|^$" /opt/mysql_bak.sql

DROP TABLE IF EXISTS `SC`;

CREATE TABLE `SC` (

`SCid`int(12) NOT NULL AUTO_INCREMENT COMMENT '主键',

`Cno`int(10) NOT NULL COMMENT '课程号',

`Sno`int(10) NOT NULL COMMENT '学号',

`Grade` tinyint(2) NOT NULL COMMENT '学生成绩',

PRIMARY KEY (`SCid`)

) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

LOCK TABLES `SC` WRITE;

INSERT INTO `SC` VALUES (1,1001,1,4),(2,1002,1,3),(3,1003,1,1),(4,1004,1,6),(5,1001,2,3),(6,1002,2,2),(7,1003,2,2),(8,1004,2,8),(9,1001,3,4),(10,1002,3,4),(11,1003,3,2),(12,1004,3,8),(13,1001,4,1),(14,1002,4,1),(15,1003,4,2),(16,1004,4,3),(17,1001,5,5),(18,1002,5,3),(19,1003,5,2),(20,1004,5,9);

UNLOCK TABLES;

DROP TABLE IF EXISTS `course`;

CREATE TABLE `course` (

`Cno`int(10) NOT NULL COMMENT '课程号',

`Cname` varchar(64) NOT NULL COMMENT '课程名',

`Ccredit` tinyint(2) NOT NULL COMMENT '学分',

PRIMARY KEY (`Cno`)

) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `course` WRITE;

INSERT INTO `course` VALUES (1001,'Linux中高级运维',3),(1002,'Linux高级架构师',5),(1003,'MySQL高级DBA',4),(1004,'Python运维开发',4),(1005,'Java web 开发',3);

UNLOCK TABLES;

DROP TABLE IF EXISTS `student`;

CREATE TABLE `student` (

`id`int(4) NOT NULL AUTO_INCREMENT,

`name`char(20) NOT NULL,

`age` tinyint(2) NOT NULL DEFAULT '0',

`dept` varchar(16) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `uni_inde_name` (`name`),

KEY `index_dept` (`dept`(8)),

KEY `ind_name_dept` (`name`(8),`dept`(10))

) ENGINE=InnoDB DEFAULT CHARSET=latin1;

LOCK TABLES `student` WRITE;

UNLOCK TABLES;

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=6 DEFAULT CHARSET=latin1;

LOCK TABLES `test` WRITE;

INSERT INTO `test` VALUES (1,'oldboy'),(2,'oldgirl'),(3,'inca'),(4,'zuma'),(5,'kaka');

UNLOCK TABLES;

#备份就是以sql语句的形式导出

[root@localhost~]# mysql -uroot -pdubin -e "use oldboy;drop table test;"[root@localhost~]# mysql -uroot -pdubin -e "select * from oldboy.test"ERROR1146 (42S02) at line 1: Table 'oldboy.test' doesn't exist

[root@localhost ~]# mysql -uroot -pdubin oldboy

[root@localhost~]# mysql -uroot -pdubin -e "select * from oldboy.test"

+----+---------+

| id | name |

+----+---------+

| 1 | oldboy |

| 2 | oldgirl |

| 3 | inca |

| 4 | zuma |

| 5 | kaka |

+----+---------+[root@localhost~]# mysqldump -uroot -pdubin -B oldboy >/opt/mysql_bak_B.sql

[root@localhost~]# ll /opt/总用量28

-rw-r--r--. 1 root root 4651 9月 21 08:43mysql_bak_B.sql-rw-r--r--. 1 root root 4508 9月 21 08:43mysql_bak.sql-rw-r-----. 1 root root 498 9月 20 22:17 mysqlbin_oldboy.000001

-rw-r--r--. 1 root root 203 9月 20 08:46oldboy_bak1.sql-rw-r--r--. 1 root root 2826 9月 20 08:46oldboy_bak.sql

[root@localhost~]# mysqldump -uroot -pdubin -B oldboy >/opt/mysql_bak_B.sql

[root@localhost~]# mysqldump -uroot -pdubin --compact -B oldboy >/opt/mysql_bak_B_compact.sql

[root@localhost~]# ll /opt/总用量32

-rw-r--r--. 1 root root 2371 9月 21 08:45mysql_bak_B_compact.sql-rw-r--r--. 1 root root 4651 9月 21 08:45mysql_bak_B.sql-rw-r--r--. 1 root root 4508 9月 21 08:43mysql_bak.sql-rw-r-----. 1 root root 498 9月 20 22:17 mysqlbin_oldboy.000001

-rw-r--r--. 1 root root 203 9月 20 08:46oldboy_bak1.sql-rw-r--r--. 1 root root 2826 9月 20 08:46oldboy_bak.sql

[root@localhost~]# mysqldump -uroot -pdubin -B oldboy|gzip >/opt/mysql_bak.sql.gz

[root@localhost~]# ll /opt/总用量36

-rw-r--r--. 1 root root 2371 9月 21 08:45mysql_bak_B_compact.sql-rw-r--r--. 1 root root 4651 9月 21 08:45mysql_bak_B.sql-rw-r--r--. 1 root root 4508 9月 21 08:43mysql_bak.sql-rw-r--r--. 1 root root 1355 9月 21 08:47mysql_bak.sql.gz-rw-r-----. 1 root root 498 9月 20 22:17 mysqlbin_oldboy.000001

-rw-r--r--. 1 root root 203 9月 20 08:46oldboy_bak1.sql-rw-r--r--. 1 root root 2826 9月 20 08:46 oldboy_bak.sql

-B 恢复的时候如果没有数据库，不用创建

--compact 以简单的形式呈现，通常用于debugging