MySQL知识（二十二）——数据备份、还原与迁移

1 数据备份

　　某些意外的情况可能导致数据库的丢失，因此应该定期地备份数据库。

首先创建测试使用的数据和各个表：

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

1.1 使用mysqldump命令备份

　　mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件包含了多个CREATE和INSERT语句，可以使用这些语句重新创建和插入数据。

mysqldump - u user -h host -ppassword dbname [tbname[,tbname...]]>filename.sql

　　右箭头符号”>”表示将备份数据写入备份文件；filename.sql为备份文件的名称。

1.1.1 备份单个数据库中所有表

　　例如，备份booksdb数据库，打开命令行输入窗口，输入

C:\Users\asus>mysqldump -u root -p booksdb > E:\backup\booksdb_20160229.sql
Enter password: ****

1.1.２ 备份数据库中某个表

　　例如，备份booksdb数据库中的books表，打开命令行输入窗口，输入

C:\Users\asus>mysqldump -u root -p booksdb books > E:\backup\booksdb_books_20160229.sql

1.1.3 备份多个数据库

　　需要使用- -databases参数；多个数据库名称之间用空格隔开；

mysqldump -u user -h host -p --databases [dbname [dbname]...]>filename.sql

　　例如，booksdb和test数据库：

mysqldump -u root -p --databases  booksDB test> E:\backup\books_testDB_20160229.sql

　　另外，可以使用- -all-databases参数指定备份所有数据库：

mysqldump  -u root -p --all-databases > E:\backup\alldbinMySQL.sql

1.2 直接复制整个数据库目录

　　因为MySQL表保存为文件方式，所以可以复制MySQL数据库的存储目录下的data文件夹进行备份。
　　此种方法简单，但不是最好的。因为对InnoDB存储引擎的表不适用，并且不同版本的MySQL可能不兼容。

1.3 使用mysqlhotcopy工具快速备份

　　备份数据库或单个表的最快途径，但只能运行在数据库目录所在的机器上，并且只能备份MyISAM类型的表。

mysqlhotcopy db_name_1,...db_name_n /path/to/new_directory

　　例如，备份test数据库到/usr/backup目录下：

mysqlhotcopy  -u root -p test /usr/backup

2 数据还原

2.1 使用mysql命令还原

　　对已经备份的包含CREATE、INSERT语句的文本文件，可以使用mysql命令导入到数据库中。

mysql -u user -p [dbname] < filename.sql

　　dbname是数据库名，如果filename.sql文件为mysqldump工具创建的包含创建数据库语句的文件，执行的时候不需要指定数据库名。

例如：（必须现在MySQL服务器中创建booksDB数据库）

mysql -u root-p booksDB < E:\backup\booksdb_20160229.sql 

　　如果已经登录MySQL服务器，可以使用source命令导入sql文件，例如：
（注意，在命令行窗口的目录是用反斜杠”\”；而在MySQL服务器时，目录是用斜杠”/”）

mysql> use booksdb;
Database changed
mysql> source E:/database_test_backup/booksdb_20160229.sql;
Query OK, 0 rows affected (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

......

Query OK, 8 rows affected (0.00 sec)
Records: 8  Duplicates: 0  Warnings: 0

Query OK, 0 rows affected (0.00 sec)

......

Query OK, 0 rows affected (0.00 sec)

2.2 直接复制到数据库目录

　　通过这种方式还原时，必须保存备份数据的数据库和待还原的数据库服务器的住版本号相同，而且这种方式只对MyISAM引擎的表有效，对于InnoDB引擎的表不可用。

2.3 mysqlhotcopy快速恢复

　　在MySQL服务器停止运行时，将备份的数据库文件复制到MySQL存放数据的位置，重新启动MySQL服务器即可。
　　在Linux/Unix系统下，如果以根用户执行该操作，必须指定数据库文件的所有者，输入语句：

chown -R mysql.mysql /var/lib/mysql/dbname

例如，从mysqlhotcopy复制备份恢复数据库：

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

3 数据库迁移

3.1 相同版本的数据库之间迁移

　　直接复制，只适用于MyISAM引擎的表，所以最常用和最安全的方式是使用mysqldump命令导出数据，然后使用mysql命令导入，例如将www.abc.com主机的数据迁移到www.bcd.com主机上：

mysqldump -h www.bac.com -uroot -ppassword  dbname | 
mysql -hwww.bcd.com -uroot -ppassword

数据的传输通过管道符“|”实现。

2.3 不同版本的数据库之间迁移

　　从新版本向旧版本MySQL迁移数据时要特别小心，最好使用mysqldump命令导出，然后倒入目标数据库中。

2.3 不同数据库之间的迁移

　　较复杂，借助工具实现，比如Navicat Premium。