一. 使用 mysqldump 命令备份
mysqldump 命令时,可以将数据库备份成一个文本文件,该文件实际上包含了多个 CREATE 和 INSERT 语句,使用这些语句可以重新创建表和插入数据。
mysqldump 备份数据库语句的基本格式:
mysqldump - u user -h host -p password dbname [ tbname, [ tbname ... ] ] > filename.sql
user 表示用户名称; host 表示登录用户的主机名称; password 为登录密码;dbname 为需要备份的数据库名称;tbname 为 dbname 数据库中需要备份的数据表,可以指定多个需要备份的表;右箭头符号“>”表示将备份数据表的定义和数据写入备份文件; filename.sql 为备份文件的名称。
1. 使用 mysqldump 备份单个数据库中的所有表
首先创建 booksDB 数据库和各个表,并插入数据记录。定义如下:
CREATE DATABASE booksDB;
use booksDB;
CREATE TABLE books
(
bk_id INT NOT NULL PRIMARY KEY,
bk_title VARCHAR(50) NOT NULL,
copyright YEAR NOT NULL
);
INSERT INTO books
VALUES (11078, 'Learning MySQL', 2010),
(11033, 'Study Html', 2011),
(11035, 'How to use php', 2003),
(11072, 'Teach youself javascript', 2005),
(11028, 'Learing C++', 2005),
(11069, 'MySQL professional', 2009),
(11026, 'Guide to MySQL 5.5', 2008),
(11041, 'Inside VC++', 2011);
CREATE TABLE authors
(
auth_id INT NOT NULL PRIMARY KEY,
auth_name VARCHAR(20),
auth_gender CHAR(1)
);
INSERT INTO authors
VALUES (1001, 'WriterX' ,'f'),
(1002, 'WriterA' ,'f'),
(1003, 'WriterB' ,'m'),
(1004, 'WriterC' ,'f'),
(1011, 'WriterD' ,'f'),
(1012, 'WriterE' ,'m'),
(1013, 'WriterF' ,'m'),
(1014, 'WriterG' ,'f'),
(1015, 'WriterH' ,'f');
CREATE TABLE authorbook
(
auth_id INT NOT NULL,
bk_id INT NOT NULL,
PRIMARY KEY (auth_id, bk_id),
FOREIGN KEY (auth_id) REFERENCES authors (auth_id),
FOREIGN KEY (bk_id) REFERENCES books (bk_id)
);
INSERT INTO authorbook
VALUES (1001, 11033), (1002, 11035), (1003, 11072), (1004, 11028),
(1011, 11078), (1012, 11026), (1012, 11041), (1014, 11069);
完成后打开操作系统命令行输入窗口,输入备份命令如下:
C:\>mysqldump -u root -p booksDB > f:/backup/booksDB_20180603.sql
Enter password: *************
使用文本查看器打开:
-- MySQL dump 10.13 Distrib 5.7.21, for Win64 (x86_64)
--
-- Host: localhost Database: booksDB
-- ------------------------------------------------------
-- Server version 5.7.21-log
/*!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 */;
/*!40101 SET NAMES 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 `authorbook`
--
DROP TABLE IF EXISTS `authorbook`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `authorbook` (
`auth_id` int(11) NOT NULL,
`bk_id` int(11) NOT NULL,
PRIMARY KEY (`auth_id`,`bk_id`),
KEY `bk_id` (`bk_id`),
CONSTRAINT `authorbook_ibfk_1` FOREIGN KEY (`auth_id`) REFERENCES `authors` (`auth_id`),
CONSTRAINT `authorbook_ibfk_2` FOREIGN KEY (`bk_id`) REFERENCES `books` (`bk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `authorbook`
--
LOCK TABLES `authorbook` WRITE;
/*!40000 ALTER TABLE `authorbook` DISABLE KEYS */;
INSERT INTO `authorbook` VALUES (1012,11026),(1004,11028),(1001,11033),(1002,11035),(1012,11041),(1014,11069),(1003,11072),(1011,11078);
/*!40000 ALTER TABLE `authorbook` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `authors`
--
DROP TABLE IF EXISTS `authors`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `authors` (
`auth_id` int(11) NOT NULL,
`auth_name` varchar(20) DEFAULT NULL,
`auth_gender` char(1) DEFAULT NULL,
PRIMARY KEY (`auth_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `authors`
--
LOCK TABLES `authors` WRITE;
/*!40000 ALTER TABLE `authors` DISABLE KEYS */;
INSERT INTO `authors` VALUES (1001,'WriterX','f'),(1002,'WriterA','f'),(1003,'WriterB','m'),(1004,'WriterC','f'),(1011,'WriterD','f'),(1012,'WriterE','m'),(1013,'WriterF','m'),(1014,'WriterG','f'),(1015,'WriterH','f');
/*!40000 ALTER TABLE `authors` ENABLE KEYS */;
UNLOCK TABLES;
--
-- Table structure for table `books`
--
DROP TABLE IF EXISTS `books`;
/*!40101 SET @saved_cs_client = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;
CREATE TABLE `books` (
`bk_id` int(11) NOT NULL,
`bk_title` varchar(50) NOT NULL,
`copyright` year(4) NOT NULL,
PRIMARY KEY (`bk_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
/*!40101 SET character_set_client = @saved_cs_client */;
--
-- Dumping data for table `books`
--
LOCK TABLES `books` WRITE;
/*!40000 ALTER TABLE `books` DISABLE KEYS */;
INSERT INTO `books` VALUES (11026,'Guide to MySQL 5.5',2008),(11028,'Learing C++',2005),(11033,'Study Html',2011),(11035,'How to use php',2003),(11041,'Inside VC++',2011),(11069,'MySQL professional',2009),(11072,'Teach youself javascript',2005),(11078,'Learning MySQL',2010);
/*!40000 ALTER TABLE `books` 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 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */;
/*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */;
/*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */;
/*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */;
/*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;
-- Dump completed on 2018-06-03 10:44:47
2. 使用 mysqldump 备份数据库中的某个表
语法格式如下:
mysqldump - u user -h host -p dbname [ tbname, [ tbname ... ] ] > filename.sql
tbname 表示数据库的表名,多个表名之间用空格隔开。
【例】 备份 booksDB 数据库中的 books 表。
输入语句:
C:\>mysqldump -u root -p booksDB books > f:/backup/books_20180603.sql
Enter password: *************
3. 使用 mysqldump 备份多个数据库
使用 mysqldump 备份多个数据库,需要使用 --databases 参数;多个数据库名称之间用空格隔开;
语法格式如下:
mysqldump - u user -h host -p --databases [ dbname, [ dbname ... ] ] > filename.sql
【例】使用 mysqldump 备份 booksDB 和 test_db 数据库。
输入语句如下:
C:\>mysqldump -u root -p --databases booksDB test_db > f:/backup/booksDB_testdb_20180603.sql
Enter password: *************
【注】 使用 mysqldump 参数可以备份系统中所有数据库,使用 --all-databases 参数时,不需要指定数据库名称。
语法如下:
mysqldump - u user -h host -p --all-databases > filename.sql
【例】使用 mysqldump 备份服务器中的所有数据库。
输入语句如下:
C:\>mysqldump -u root -p --all-databases >f:/backup/alldbinMySQL.sql
Enter password: *************
二. 直接复制整个数据库目录
因为MySQL表保存为文件方式,所以可以复制MySQL数据库的存储目录下的data文件夹进行备份。
此种方法简单,但不是最好的。因为对InnoDB存储引擎的表不适用,并且不同版本的MySQL可能不兼容。
【注】主版本号相同的 MySQL 数据库的文件格式相同。
三. 使用 mysqlhotcopy 工具快速备份
备份数据库或单个表的最快途径,但只能运行在数据库目录所在的机器上,并且只能备份 MyISAM 和 ARCHIVE 类型的表。 mysqlhotcopy 在 UNIX 系统中运行。
mysqlhotcopy 命令的格式如下:
mysqlhotcopy db_name_1, ... db_name_n /path/to/new_directory
db_name_1, ... db_name_n 为需要备份的数据库的名称。/path/to/new_directory 指定备份文件目录。
【例】备份 test_db 数据库到/usr/backup目录下:
mysqlhotcopy -u root -p test /usr/backup
【注】参考于清华大学出版社《MySQL数据库应用案例课堂》2016年1月第1版