MySQL数据备份与还原介绍

MySQL数据备份与还原

MySQL备份，分为逻辑备份和物理备份。

逻辑备份

Logical backups save information represented as logical database structure (CREATE DATABASE, CREATE TABLE statements) and content (INSERT statements or delimited-text files). This type of backup is suitable for smaller amounts of data where you might edit the data values or table structure, or recreate the data on a different machine architecture.

逻辑备份保存的信息表示为逻辑数据库结构（创建数据库，创建表语句）和内容（插入语句或分隔文本文件）。这种类型的备份适用于较小的数据量，您可以编辑数据值或表结构，或者在不同的机器架构上重新创建数据。

 

速度比较慢，但可移植性教好。

典型的方法：

1.mysqldump程序可以把对远程/本地数据库进行逻辑备份。

2.SELECT ..... INTO OUTFILE可以把数据进行逻辑备份，但备份文件只能存储在mysql-server的机器上。

 

物理备份

Physical backups consist of raw copies of the directories and files that store database contents. This type of backup is suitable for large, important databases that need to be recovered quickly when problems occur.

物理备份包括存储数据库内容的目录和文件的原始副本。这种类型的备份适用于大型的，重要的数据库，当问题发生时需要快速恢复。

 

一般备份比逻辑备份快，因为纯属是文件复制，不用像逻辑备份那样，需要做sql的转换；

通用性差，采用不通存储引擎的数据库的所用文件不一样，需要了解，且不适合备份memory存储引擎的数据，因为memory的数据不是存储在硬盘的；

典型方法：

1.操作系统的文件系统命令

2.备份软件

 

mysql常见的备份方式有：mysqldump、mysqlhotcopy、BACKUP TABLE 、SELECT INTO OUTFILE，或备份二进制日志（binlog），直接复制（copy拷贝）数据文件和相关的配置文件。

 

直接复制（copy拷贝）数据库

要求：

1、数据库类型必须为MYISAM类型

2、关闭数据库

 

下面给出具体实例：

假设：数据库是安装在/usr/local/mysql目录下，数据目录是/usr/local/mysql/data，要备份数据库test，备份文件存放在/backup/目录下，则按以下几步即可完成。

a、关闭数据库：/usr/local/mysql/bin/mysqladmin -uroot -pxxx shutdown

   xxx为你的密码.

b、拷贝并压缩数据库test文件夹

   tar -zcvf /backup/test.tar.gz /usr/local/mysql/data/test

c、重新启动数据库服务器

   /usr/local/mysql/bin/mysqld_safe --user=mysql&

 

上面方法拷贝时要停止数据库服务器的运行，这在很多情况下会影响用户的使用，因此并不常用。使用mysqlhotcopy可以在不用停止mysql服务器的情况下进行数据备份。这种方法也非常简单，只要下面一条语句即可实现把test数据库备份到/backup/目录下

    /usr/local/mysql/bin/mysqlhotcopy -uroot -pxxx test /backup/

    mysqlhotcopy在执行前将会锁定数据库，执行完毕后将自定释放锁定。

然后在进行压缩：tar -zcvf /backup/test.tar.gz /backup/test

 

MySQLdump的使用

MySQLdump是MySQL提供的一个非常有用的数据库备份工具。MySQL数据库备份和恢复命令在mysql安装目录中的bin目录下，使用MySQLdump需要进入到DOS命令窗口，进入bin目录（这儿是 \wamp\bin\mysql\mysql5.7.11\bin），运行MySQLdump -? 可了解该命令语法，因信息量非常大，回车键一敲，一下滚到了最底端，为此可以在命令的尾部加入|more，如：

MySQLdump -?|more

这时，敲下回车键之后，信息满了一屏则会暂停。如果想再往下查看，只需再敲回车键即可。这时，每敲一下回车，就会多显示一行。如下图所示：

 

 

若要成功运行mysqldump，要注意mysqldump.exe文件所在的路径，或先将其路径先添加“系统环境变量”，或先用cd命令进入其路径。

把数据库db1 导出到 liongg.sql 文件中

mysqldump -u root -p db1>g:\liongg.sql

已新建数据库db2，把liongg.sql导入

mysqldump -u root -p db2<g:\liongg.sql

使用mysqldump从数据库导出数据 格式：

mysqldump [OPTIONS] database [tables]

mysqldump [OPTIONS] --databases [OPTIONS] DB1 [DB2 DB3...]

mysqldump [OPTIONS] --all-databases [OPTIONS]

选项（OPTIONS）很多，详见联机帮助，现仅给出比较常用的一般格式：

mysqldump -h链接ip -P(大写)端口 -u用户名 -p密码 数据库名 表名 >备份文件名.sql

 示例：mysqldump -h132.72.192.432 -P3307 -uroot -p8888 test_db > bak.sql;

（导出数据库test_db到bak.sql文件）

注：备份文件名，文件名前面可以加上一个绝对路径。通常将数据库备份成一个后缀名为sql的文件。提示输入密码时，输入该数据库用户名的密码。

 

导出数据库一个表的例子：

mysqldump -u wcnc -p test_db users> test_users.sql

（导出数据库test_db 的表users到test_users.sql 文件）

 

将导出的本地文件导入到指定数据库

使用mysqldump 命令备份的.sql文件中，可以使用Mysql命令将数据还原，还原语法格式：

一般格式：mysql -h链接ip -P(大写)端口 -u用户名 -p密码 数据库名 < d:XX.sql 

mysql –u username –p password db1 < bak.sql

或使用source 命令还原，需要使用use命令来选择数据库，再用该指令进行还原。

use db1;

source bak.sql;

 

 

MySQL系统支持的存储引擎较多，因此MySQL数据备份与还原方式方法较多，很难一一详述，就不多写了，感兴趣的朋友可以参照其他资料。