【MySQL】数据备份和恢复

目录

14.1  数据备份

14.1.1  使用mysqldump命令备份

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

14.1.3  使用mysqlhotcopy工具快速备份

14.2  数据还原

14.2.1  使用mysql命令还原

14.2.2  直接复制到数据库目录

14.2.3  mysqlhotcopy快速恢复

14.3数据库迁移

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

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

14.3.3  不同数据库之间迁移

14.4  表的导出和导入

14.4.1  用SELECT…INTO OUTFILE导出文本文件，需有写权限，且新文件不存在。

 

14.4.2  用mysqldump命令导出文本文件

14.4.3  用mysql命令导出文本文件

14.4.4  用LOAD DATA INFILE方式导入文本文件

14.4.5  用mysqlimport命令导入文本文件

---

 

14.1  数据备份

14.1.1  使用mysqldump命令备份

mysqldump是MySQL提供的一个非常有用的数据库备份工具。在命令行中，使用mysqldump命令执行时，可以将数据库备份成一个文本文件，该文件中实际上包含了多个CREATE和INSERT语句，使用这些语句可以重新创建表和插入数据。

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

备份多个数据库，需要使用—databases,数据库之间用空格隔开：

mysqldump  –u user –h host –ppassword –databases [dbname dbname]> filename.sql

 

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

因为MySQL表保存为文件方式，所以可以直接复制mysql数据库的存储目录及文件进行备份。最好先停止服务，对表LOCK TABLES,在进行FLUSH TABLES，可以允许其他用户继续查询表。

InnoDB存储引擎的表不适用。使用这种方法备份的数据最好还原到相同版本的服务器中，因为不同的版本可能不兼容。

 

14.1.3  使用mysqlhotcopy工具快速备份

mysqlhotcopy是一个Perl脚本，它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。

只可以备份MyISAM类型的表

Mysqlhotcopy db_name1,…db_namen /path/to/new_directory

 

14.2  数据还原

14.2.1  使用mysql命令还原

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

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

已经登录MySQL则可以先use 数据库，再source filename导入。

 

14.2.2  直接复制到数据库目录

如果数据库通过复制数据库文件备份，可以直接复制备份的文件到到MySQL数据目录下实现还原。通过这种方式还原时，必须保存备份数据的数据库和待还原的数据库服务器的主版本号相同。而且这种方式只对MyISAM引擎的表有效。对于InnoDB引擎的表不可用。

 

14.2.3  mysqlhotcopy快速恢复

mysqlhotcopy备份后的文件也可以用来恢复数据库，在MySQL服务器停止运行时，将备份的数据库文件拷贝到MySQL存放数据的位置（MySQL的Data文件夹中），重新启动MySQL服务即可。如果以根用户执行该操作，必须指定数据库文件的所有者。

Chown -r mysql.mysql /var/lib/mysql/dbname14.3 

 

14.3数据库迁移

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

相同版本的MySQL数据库之间的迁移就是在主版本号相同的MySQL数据库之间进行数据库移动。迁移过程其实就是在源数据库备份和目标数据库还原过程的组合。

 

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

因为数据库升级等原因，需要将较旧版本MySQL数据库中的数据迁移到的较新版本的数据库中。备份，卸载，安装新版本，恢复。InnoDB多使用MySQLdump导出和恢复。

 

14.3.3  不同数据库之间迁移

不同类型的数据库之间的迁移，是指从把MySQL的数据库转移到其他类型的数据库，例如从MySQL迁移到ORACLE，从ORACLE迁移到MySQL和从MySQL迁移到sqlserver等。

需要了解不同数据库间的架构差异，对不同的语句进行映射处理。

例如WINDOWS下面，可使用myODBC实现MySQL和SQLserver之间的迁移。或者MySQL官方的mysql migration toolkit可以在个数据库间迁移。

 

14.4  表的导出和导入

14.4.1  用SELECT…INTO OUTFILE导出文本文件，需有写权限，且新文件不存在。

MySQL数据库导出数据时，允许使用包含导出定义的SELECT语句进行数据的导出操作。该文件被创建到服务器主机上。

SELECT columnlist  FROM table

       WHERE condition

      INTO OUTFILE ‘filename’  [OPTIONS]

 

14.4.2  用mysqldump命令导出文本文件

mysqldump工具不仅可以将数据导出为包含CREATE、INSERT的sql文件，也可以导出为纯文本文件。

mysqldump -T path-u root -p

       dbname [tables] [OPTIONS]

 

14.4.3  用mysql命令导出文本文件

mysql是一个功能丰富的工具命令，还可以在命令行模式下执行SQL指令将查询结果导入到文本文件中。可以在客户机上导出到文本文件。

mysql -u root -p

       --execute=”SELECT 语句” dbname

       > filename.txt

 

14.4.4  用LOAD DATA INFILE方式导入文本文件

LOAD DATA INFILE语句用于高速地从一个文本文件中读取行，并装入一个表中。文件名称必须为一个文字字符串。

LOAD DATA  INFILE 'filename.txt'

       INTO TABLE tablename [OPTIONS]   [IGNORE number LINES]

 

14.4.5  用mysqlimport命令导入文本文件

mysqlimport可以导入文本文件，使用mysqlimport不需要登录MySQL客户端。使用mysqlimport语句需要指定所需的选项、导入的数据库名称以及导入的数据文件的路径和名称。

mysqlimport –u root –p

       dbname filename.txt [OPTIONS]