重庆思庄Oracle、Redhat认证学习论坛
标题: mysqldump 工具备份数据库 [打印本页]
作者: jiawang 时间: 2022-11-13 18:54
标题: mysqldump 工具备份数据库
使用 mysqldump 工具备份 mysqldump是客户端常用逻辑备份程序，能够产生一组被执行以后再现原始数据库对象定义和表数据的SQL语句。它可以转储一个到多个MySQL数据库，对其进行备份或传输到远程SQL服务器。
mysqldump更为通用，因为它可以备份各种表。
使用 mysqldump 可以更加灵活地控制备份的内容，比如某几个表或库都可以单独备份
创建库[root@sztech ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.11 MySQL CommunityServer - GPL
Copyright (c) 2000, 2018, Oracle and/or itsaffiliates. All rights reserved.
Oracle is a registered trademark of OracleCorporation and/or its
affiliates. Other names may be trademarksof their respective
owners.
Type 'help;' or '\h' for help. Type '\c' toclear the current input statement.
mysql> create database wangjia;
Query OK, 1 row affected (0.42 sec)
mysql> use wangjia;
Database changed
mysql> select database();
+------------+
| database() |
+------------+
| wangjia |
+------------+
1 row in set (1.90 sec)
创建表mysql> create table test ( id int(2) notnull auto_increment, name varchar(10) not null,sex char(5) not null, primarykey (id));
Query OK, 0 rows affected (1.13 sec)
mysql> desc test;
+-------+-------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+----------------+
| id | int(2) | NO | PRI | NULL | auto_increment |
| name | varchar(10) | NO | | NULL | |
| sex | char(5) | NO | | NULL | |
+-------+-------------+------+-----+---------+----------------+
3 rows in set (0.08 sec)
mysql> insert into testvalues(1,'TOM','man');
Query OK, 1 row affected (0.09 sec)
mysql> insert into testvalues(2,'lisa','woman');
Query OK, 1 row affected (0.39 sec)
验证表信息mysql> select * from test;
+----+------+-------+
| id | name | sex |
+----+------+-------+
| 1| TOM | man |
| 2| lisa | woman |
+----+------+-------+
2 rows in set (0.00 sec)
使用mysqldump备份对wangjia库中的test表进行备份mysqldump -u[用户名] -p[密码] [选项] [数据库名] [数据表名] > /备份路径/备份文件名.sql
[root@sztech ~]# mysqldump -uroot -pWangjiawangjia test > /opt/test.sql
mysqldump: [Warning] Using a password onthe command line interface can be insecure.
[root@sztech ~]#
[root@sztech opt]# ll
total 2748
-rw-r--r--. 1 root root 2578748 Oct 2415:50 mysql-2022-10-24.tar.xz
drwxr-xr-x. 2 root root 6 Oct 31 2018 rh
-rw-r--r--. 1 root root 1906 Oct 24 16:48 test.sql
[root@sztech opt]#
分析mysqldump生成的备份内容[root@sztech opt]# cat /opt/test.sql
-- MySQL dump 10.13 Distrib 8.0.11, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: wangjia
--------------------------------------------------------
-- Server version 8.0.11
/*!40101 SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;
/*!40101 SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SETNAMES utf8 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0 */;
/*!40014 SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SETcharacter_set_client = utf8mb4 ;
CREATE TABLE `test` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`sex`char(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULTCHARSET=utf8;
/*!40101 SET character_set_client =@saved_cs_client */;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS*/;
INSERT INTO `test` VALUES(1,'TOM','man'),(2,'lisa','woman');
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;
/*!40014 SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-10-24 16:48:09
对某些库进行完全备份[root@sztech ~]# mysqldump wangjia > /opt/test01.sql
[root@sztech opt]# ll
total 2752
-rw-r--r--. 1 root root 229376 Oct 24 15:55 mysql-2022-10-24.tar
-rw-r--r--. 1 root root 2578748 Oct 2415:50 mysql-2022-10-24.tar.xz
drwxr-xr-x. 2 root root 6 Oct 31 2018 rh
-rw-r--r--. 1 root root 1906 Oct 24 17:10 test01.sql
-rw-r--r--. 1 root root 1906 Oct 24 16:48 test.sql
[root@sztech opt]#
对多个库进行完全备份[root@sztech ~]# mysqldump --databaseswangjia sys > /opt/test02.sql
[root@sztech opt]# ll
total 3044
-rw-r--r--. 1 root root 229376 Oct 24 15:55 mysql-2022-10-24.tar
-rw-r--r--. 1 root root 2578748 Oct 2415:50 mysql-2022-10-24.tar.xz
drwxr-xr-x. 2 root root 6 Oct 31 2018 rh
-rw-r--r--. 1 root root 1906 Oct 24 17:10 test01.sql
-rw-r--r--. 1 root root 298639 Oct 24 17:23 test02.sql
-rw-r--r--. 1 root root 1906 Oct 24 16:48 test.sql
[root@sztech opt]# pwd
/opt
对所有数据库进行完全备份[root@sztech ~]# mysqldump --all-databases> /opt/test03.sql
[root@sztech opt]# ll
total 3896
-rw-r--r--. 1 root root 229376 Oct 24 15:55 mysql-2022-10-24.tar
-rw-r--r--. 1 root root 2578748 Oct 2415:50 mysql-2022-10-24.tar.xz
drwxr-xr-x. 2 root root 6 Oct 31 2018 rh
-rw-r--r--. 1 root root 1906 Oct 24 17:10 test01.sql
-rw-r--r--. 1 root root 298639 Oct 24 17:23 test02.sql
-rw-r--r--. 1 root root 869591 Oct 24 17:26 test03.sql
-rw-r--r--. 1 root root 1906 Oct 24 16:48 test.sql
直接备份表结构[root@sztech ~]# mysqldump -d wangjia test >/opt/test04.sql
查看备份内容[root@sztech opt]# cat test04.sql
-- MySQL dump 10.13 Distrib 8.0.11, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: wangjia
--------------------------------------------------------
-- Server version 8.0.11
/*!40101 SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET@OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SETNAMES utf8 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET@OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0 */;
-- Table structure for table `test`
--
DROP TABLE IF EXISTS`test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SETcharacter_set_client = utf8mb4 ;
CREATE TABLE `test` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`sex` char(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULTCHARSET=utf8;
/*!40101 SET character_set_client =@saved_cs_client */;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SETFOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */;
/*!40014 SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-10-24 17:29:25
[root@sztech opt]#
· 上面使用了 -d 选项，说明只保存数据库的表结构，且表中只有一个库，可以看到先删除了表（同名），再创建了它
· 删除和创建都是最普通的 MySQL 命令，任何一条在 MySQL 中都是可以执行的语句，有了这些语句就可以创建出和现在的表结构相同的表
不加-d参数[root@sztech ~]# mysqldump wangjia test> /opt/test05.sql
[root@sztech opt]# cat test05.sql
-- MySQL dump 10.13 Distrib 8.0.11, for linux-glibc2.12 (x86_64)
--
-- Host: localhost Database: wangjia
--------------------------------------------------------
-- Server version 8.0.11
/*!40101 SET@OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS*/;
/*!40101 SET@OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
SETNAMES utf8 ;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS,UNIQUE_CHECKS=0 */;
/*!40014 SET@OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE,SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES,SQL_NOTES=0 */;
--
-- Table structure for table `test`
--
DROP TABLE IF EXISTS `test`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
SETcharacter_set_client = utf8mb4 ;
CREATE TABLE `test` (
`id` int(2) NOT NULL AUTO_INCREMENT,
`name` varchar(10) NOT NULL,
`sex`char(5) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULTCHARSET=utf8;
/*!40101 SET character_set_client =@saved_cs_client */;
--
-- Dumping data for table `test`
--
LOCK TABLES `test` WRITE;
/*!40000 ALTER TABLE `test` DISABLE KEYS*/;
INSERT INTO `test` VALUES(1,'TOM','man'),(2,'lisa','woman');
/*!40000 ALTER TABLE `test` ENABLE KEYS */;
UNLOCK TABLES;
/*!40103 SET TIME_ZONE=@OLD_TIME_ZONE */;
/*!40101 SET SQL_MODE=@OLD_SQL_MODE */;
/*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS*/;
/*!40014 SETUNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SETCHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SETCHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SETCOLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2022-10-24 17:32:19
[root@sztech opt]#
可以看到与使用 -d 参数的差别是最后出现了 insert into 语句，往数据库中插入了两条数据，也就是说，数据的备份是保存的插入语句操作
| 欢迎光临 重庆思庄Oracle、Redhat认证学习论坛 (http://bbs.cqsztech.com/) |
Powered by Discuz! X3.2 |