MYSQL 基础篇（三） 备份还原

MYSQL 系列文章目录

• MYSQL 基础篇 （一） 安装配置
• MYSQL 基础篇 （二） 版本比较
• MYSQL 基础篇 （三） 备份还原

---

---

1. 前言提要

• 官方文档地址： MySQL 5.7参考手册
• 官方文档地址： MySQL 8.0参考手册

---

2. 创建测试

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);

---

3. 备份数据

1. 使用mysqldump备份服务器中的 所有数据库

mysqldump -u root -p  --all-databases > C:/backup/alldbinMySQL.sql

2. 使用 mysqldump 备份 booksDB 和 test 数据库

mysqldump -u root -p  --databases  booksDB test > C:\backup\books_testDB_20110101.sql

3. 使用 mysqldump 命令备份数据库中的所有表

mysqldump -u root -p booksdb > C:/backup/booksdb_20110101.sql

4. 备份 booksDB 数据库中的books表

mysqldump -u root -p booksDB books  > C:/backup/books_20110101.sql

---

3. 还原数据

1. 使用mysql命令将C:\backup\booksdb_20110101.sql文件中的备份导入到数据库中

mysql -u root-p booksDB < C:/backup/booksdb_20110101.sql

2. 使用souce导入本地的备份文件 booksdb_20110101.sql

use booksdb;	#选择数据库
source C:/backup/booksdb_20110101.sql;	#导入数据到数据库

3. 从 mysqlhotcopy 拷贝的备份恢复数据库

cp –R  /usr/backup/test usr/local/mysql/data

4. 将www.abc.com主机上的MySQL数据库全部迁移到www.bcd.com主机上

mysqldump -h  www.bac.com  -u root  -p password  dbname | 
mysql  -hwww.bcd.com -u root -p password

5. 使用SELECT…INTO OUTFILE 将test数据库中的person表中的记录导出到文本文件

SELECT *  FROM test.person INTO OUTFILE "C:/person0.txt";

6. 使用SELECT…INTO OUTFILE将test数据库中的person表中的记录导出到文本文件，使用FIELDS选项和LINES选项，要求字段之间使用逗号‘,’间隔，所有字段值用双引号括起来，定义转义字符定义为单引号‘\’’.

SELECT * FROM test.person INTO OUTFILE "C:/person1.txt"
  FIELDS 
    TERMINATED BY ','
    ENCLOSED BY '\"'
    ESCAPED BY '\''
  LINES 
    TERMINATED BY '\r\n';

7. 使用SELECT…INTO OUTFILE将test数据库中的person表中的记录导出到文本文件，使用LINES选项，要求每行记录以字符串“> ”开始，以“”字符串结尾.

SELECT * FROM test.person INTO OUTFILE "C:/person2.txt"
  LINES 
    STARTING BY '> '
    TERMINATED BY '<end>';

SELECT * FROM test.person INTO OUTFILE "C:/person2.txt"
  LINES 
    STARTING BY '> '
    TERMINATED BY '<end>\r\n';

8. 使用mysqldump将test数据库中的person表中的记录导出到文本文件

mysqldump -T C:/ test person -u root -p

9. 使用mysqldump命令将test数据库中的person表中的记录导出到文本文件，使用FIELDS选项，要求字段之间使用逗号‘,’间隔，所有字符类型字段值用双引号括起来，定义转义字符定义为问号‘?’，每行记录以回车换行符“\r\n”结尾.

mysqldump -T C:\backup test person -u root -p --fields-terminated-by=,
 --fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n

10. 使用mysql语句导出test数据库中person表中的记录到文本文件

mysql -u root -p --execute="SELECT * FROM person;" test > C:\person3.txt

11. 使用mysql命令导出test数据库中person表中的记录到文本文件，使用–vertical参数显示结果

mysql -u root -p --vertical --execute="SELECT * FROM person;" test > C:\person4.txt

12. 使用mysql命令导出test数据库中person表中的记录到html文件

mysql -u root -p --html --execute="SELECT * FROM person;" test > C:\person5.html

13. 使用mysql命令导出test数据库中person表中的记录到xml文件

mysql -u root -p --xml --execute="SELECT * FROM person;" test > C:\person6.xml

14. 使用LOAD DATA命令将C:\person0.txt文件中的数据导入到test数据库中的person表

LOAD DATA  INFILE 'C:/person0.txt' INTO TABLE test.person;

15. 使用LOAD DATA命令将C:\person1.txt文件中的数据导入到test数据库中的person表，使用FIELDS选项和LINES选项，要求字段之间使用逗号‘,’间隔，所有字段值用双引号括起来，定义转义字符定义为单引号‘\’’.

LOAD DATA  INFILE 'C:/person1.txt' INTO TABLE test.person
  FIELDS 
    TERMINATED BY ','
    ENCLOSED BY '\"'
    ESCAPED BY '\''
  LINES 
    TERMINATED BY '\r\n';

16. 使用mysqlimport命令将C:\backup目录下的person.txt文件内容导入到test数据库中，字段之间使用逗号‘,’间隔，字符类型字段值用双引号括起来，定义转义字符定义为问号‘?’，每行记录以回车换行符“\r\n”结尾.

mysqlimport -u root -p test C:/backup/person.txt --fields-terminated-by=,  
--fields-optionally-enclosed-by=\" --fields-escaped-by=? --lines-terminated-by=\r\n

4. 操作步骤

1. 使用 mysqldump 命令将 suppliers 表备份到文件 C:\bktestdir\suppliers_bk.sql

mysqldump -u root -p test suppliers  > C:\bktestdir\suppliers_bk.sql

2. 使用mysql命令还原suppliers表到test数据库中

DELETE FROM suppliers;
source C:/bktestdir/suppliers_bk.sql;

3. 使用SELECT… INTO OUTFILE语句导出suppliers表中的记录，导出文件位于目录C:\bktestdir下，名称为suppliers_out.txt.

SELECT * FROM test.suppliers  INTO OUTFILE " C:/bktestdir/suppliers_out.txt"
   FIELDS
     TERMINATED BY ','
     ENCLOSED BY '\"'
   LINES
     STARTING BY '<'
     TERMINATED BY '>\r\n';

4. 使用LOAD DATA INFILE语句导入suppliers_out.txt数据到suppliers表.

LOAD DATA INFILE 'C:/bktestdir/suppliers_out.txt' INTO TABLE test.suppliers
  FIELDS
    TERMINATED BY ','
    ENCLOSED BY '\"'
  LINES
    STARTING BY '<'
    TERMINATED BY '>\r\n';

5. 使用musqldump命令将suppliers表中的记录导出到文件C:\bktestdir\suppliers_html.html

mysql -u root -p --html --execute="SELECT * FROM suppliers;" 
test > C:/bktestdir/suppliers_html.html

---

5. 总结概要

未完待续…

---