DBA 必知的 MYSQL 备份与还原方法

说明：MySQL 备份一般采取全库备份加日志备份的方式，例如每天执行一次全备份，每小时执行一次二进制日志备份。这样在 MySQL 故障后可以使用全备份和日志备份将数据恢复到最后一个二进制日志备份前的任意位置或时间。

Binlog 功能默认是关闭的，没有开启。

查看 binlog，用 mysqlbinlog -v mysql-bin.000001

主从同步

恢复数据库

开启 binary log 功能：通过编辑 my.cnf 中的 log-bin 选项可以开启二进制日志；形式如右：log-bin[=DIR/[filename]] ，注释：每次重启 mysql 服务或运行 mysql> flush logs;都会生成一个新的二进制日志文件，这些日志文件的 number 会不断地递增，除了生成上述的文件外还会生成一个名为 filename.index 的文件。这个文件中存储所有二进制日志文件的清单又称为二进制文件的索引。

说明：bin-log 因为是二进制文件，不能通过文件内容查看命令直接打开查看，mysql 提供两种方式查看方式。

查看指定的二进制日志中的事件：

该命令还包含其他选项以便灵活查看：

总结：上述方式可以查看到服务器上存在的二进制日志文件及文件中的事件，但是想查看到文件中具体的内容并应于恢复场景还得借助 mysqlbinlog 这个工具。

语法格式：mysqlbinlog [options] log_file ...

输出内容会因日志文件的格式以及 mysqlbinlog 工具使用的选项不同而略不同。

mysqlbinlog 的可用选项可参考 man 手册。

说明：无论是本地二进制日志文件还是远程服务器上的二进制日志文件，无论是行模式、语句模式还是混合模式的二进制日志文件，被 mysqlbinlog 工具解析后都可直接应用与MySQL Server 进行基于时间点、位置或数据库的恢复。

下面我们就来演示如何使用 binlog 恢复之前删除数据（id=2那条记录）

注意：在实际生产环境中，如果遇到需要恢复数据库的情况，不要让用户能访问到数据库，以避免新的数据插入进来，以及在主从的环境下，关闭主从。

delete from bdqn.test where id=2

# cd/usr/local/mysql/data/

# mysqlbinlog -v mysql-bin.000002

显示结果如下：（复制的日志）

# at 219

#170316 21:52:28 server id 1 end_log_pos 287 CRC32 0xff83a85b Query thread_id=2 exec_time=0 error_code=0

SET TIMESTAMP=1489672348/*!*/;

SET @@session.pseudo_thread_id=2/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;

/*!C utf8 *//*!*/;

SET@@session.character_set_client=33,@@session.collation_connection=33,

@@session.collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 287

#170316 21:52:28 server id 1 end_log_pos 337 CRC32 0x343e7343 Table_map: `bdqn`.`test` mapped to number 108

# at 337

#170316 21:52:28 server id 1 end_log_pos 382 CRC32 0xa3d1ce0d Delete_rows: table id 108 flags: STMT_END_F

BINLOG ‘

nJjKWBMBAAAAMgAAAFEBAAAAAGwAAAAAAAEABGJkcW4ABHRlc3QAAgMPAjwAAkNzPjQ

=nJjKWCABAAAALQAAAH4BAAAAAGwAAAAAAAEAAgAC//wCAAAABGxpc2kNztGj

‘/*!*/;

### DELETE FROM `bdqn`.`test`

### WHERE

### @1=2

### @2=‘lisi‘

# at 382

#170316 21:52:28 server id 1 end_log_pos 413 CRC32 0x257e7073 Xid = 10

COMMIT/*!*/;

说明：可以从上图可以看出来 delete 时间发生 position 是 287，事件结束 position 是413。

②恢复流程：直接用 bin-log 日志将数据库恢复到删除位置 287 前,然后跳过故障点,再进行恢复下面所有的操作,命令如下

由于之前没有做过全库备份，所以要使用所有 binlog 日志恢复，所以生产环境中需要很长时间恢复，导出相关 binlog 文件。

③删除 bdqn 数据库（删除 bdqn 和恢复数据之前，要关闭 binlog 功能）

作用：mysqldump 是 mysql 自带的备份和数据转移的工具。

它只产生 sql 语句（即 sql 命令）封装在文件，而不是真实的数据。

Mysqldump 是逻辑备份，不是物理备份，备份的是 SQL 语句，而不是数据文件。

Mysqldump 适用于小型数据库，数据容量一般是在几个 G 大小，当数据量很大的情况下，不建议使用 mysqldump。

导出对象：可以针对单个表、多个表、单个数据库、多个数据库、所有数据库。

#mysqldump [选项] 库名 [表名1] [表名2] … > /备份路径/备份文件名

//导出指定数据库的单个或多个表

#mysqldump [选项] --databases 库名1 [库名2] … > /备份路径/备份文件名

//导出指定的数据库或多个数据库

#mysqldump [选项] --all-databases > /备份路径/备份文件名

//导出所有的数据库

#mysqldump -uroot -p123456 --flush-logs bdqn > /opt/bdqn.sql

//导出数据库bdqn,其中“—flush-logs”这个选项是完整备份完毕后开启一个新的binlog

#mysql -uroot -p123456 bdqn < /opt/bdqn.sql

//从备份文件导入数据库bdqn

下面用一个具体的实验说明用mysqldump实现全库备份+binlog的数据恢复

4）开始全库备份（注意：全库备份不会备份 binlog 日志文件）

5）备份 mysqldump 全库备份之前的所有的 binlog 日志文件（注意：真是生产环境中可能不止一个 binlog 文件）

6）因为全库备份之前的 binlog 已经备份了，现在就删除它们（即新产生的 binlog 之前的所有的 binlog 删除）

①使用 mysqldump 的备份进行全库恢复（即恢复到全部备份时候的所有数据）

②分析新开启的 binlog 日志文件（我这里是 mysql-bin.000002）里面误操作的事件的起始位置和终止位置，只要跳过这一段事件即可

复制的日志：

# at 219

#170318 21:14:42 server id 1 end_log_pos 291 CRC32 0xddbf8eff Query thread_id=5 exec_time=0 error_code=0

SET TIMESTAMP=1489842882/*!*/;

SET @@session.pseudo_thread_id=5/*!*/;

SET @@session.foreign_key_checks=1,@@session.sql_auto_is_null=0, @@session.unique_checks=1,@@session.autocommit=1/*!*/;

SET @@session.sql_mode=1075838976/*!*/;

SET @@session.auto_increment_increment=1,@@session.auto_increment_offset=1/*!*/;

/*!C utf8 *//*!*/;

SET @@session.character_set_client=33,@@session.collation_connection=33,@@session.

collation_server=33/*!*/;

SET @@session.lc_time_names=0/*!*/;

SET @@session.collation_database=DEFAULT/*!*/;

BEGIN

/*!*/;

# at 291

#170318 21:14:42 server id 1 end_log_pos 339 CRC32 0x4a9ec8f2 Table_map: `bdqn`.`it` mapped to number 108

# at 339

#170318 21:14:42 server id 1 end_log_pos 388 CRC32 0x2e8a3da8 Delete_rows: table id 108 flags: STMT_END_F

BINLOG ‘

wjLNWBMBAAAAMAAAAFMBAAAAAGwAAAAAAAEABGJkcW4AAml0AAIDDwI8AALyyJ5K

wjLNWCABAAAAMQAAAIQBAAAAAGwAAAAAAAEAAgAC//wBAAAACHpoYW5nc2FuqD2KLg

==‘/*!*/;

### DELETE FROM `bdqn`.`it`

### WHERE

### @1=1

### @2=‘zhangsan‘

# at 388

#170318 21:14:42 server id 1 end_log_pos 419 CRC32 0xa1c06a4f Xid = 43

COMMIT/*!*/;

③开始使用全库备份后的增量备份的 binlog 日志文件备份文件进行对全库恢复后的增量数据的恢复

总结：从上图显示可以看出数据恢复到正常状态，实际生产环境中 mysql 数据库的备份是周期性重复操作，所有通常是要编写脚本实现，通过 crond 计划任务周期性执行备份脚本。

周日凌晨1点全库备份；

周一到周六凌晨每隔4个小时增量备份一次

设置 crontab 任务，每天执行备份脚本：

2）编写 mysqlfullbackup.sh 脚本（即 mysql 全库备份脚本）

3）编写 mysqldailybackup.sh 脚本（即 mysql 增量备份脚本）

发送邮件测试：

安装 libmysqlclient.so.18

再次测试：

二使用 xtrabackup 进行 MySQL 数据库备份

前面介绍mysqldump 备份方式是采用逻辑备份，其最大的缺陷就是备份和恢复速度都慢，对于一个小于 50G 的数据库而言，这个速度还是能接受的，但如果数据库非常大，那再使用 mysqldump 备份就不太适合了。

这时就需要一种好用又高效的工具，xtrabackup就是其中一款，号称免费版的 InnoDB HotBackup。

目前主流的有两个工具可以实现物理热备：ibbackup 和 xtrabackup；

ibbackup 是商业软件，需要授权，非常昂贵。

xtrabackup 功能比 ibbackup 还要强大，但却是开源的。因此我们这里就来介绍 xtrabackup 的使用。

xtrabackup：专用于备份 InnoDB 和 XtraDB 引擎的数据；

innobackupex：这是一个 perl 脚本，在执行过程中会调用 xtrabackup 命令，这样用该命令即可以实现备份 InnoDB，也可以备份 MyISAM 引擎的对象。

(1)备份过程快速、可靠；

(2)备份过程不会打断正在执行的事务；

(3)能够基于压缩等功能节约磁盘空间和流量；

(4)自动实现备份检验；

(5)还原速度快。

官方链接地址：

http://www.percona.com/software/percona-xtrabackup；

可以下载源码编译安装，也可以下载适合的 RPM 包或使用 yum 进行安装或者下载二进制源码包。

wget https://www.percona.com/downloads/XtraBackup/Percona-XtraBackup-2.4.4/binary/tarball/percona-xtrabackup-2.4.4-Linux-x86_64.tar.gz

Xtrabackup 中主要包含两个工具：

xtrabackup：是用于热备份 innodb,xtradb 表中数据的工具，支持在线热备份，可以在不加锁的情况下备份 Innodb 数据表，不过此工具不能操作 Myisam 引擎表；

innobackupex：是将 xtrabackup 进行封装的 perl 脚本，能同时处理 Innodb 和 Myisam，但在处理 Myisam 时需要加一个读锁。

由于操作 Myisam 时需要加读锁，这会堵塞线上服务的写操作，而 Innodb 没有这样的限制，所以数据库中 Innodb 表类型所占的比例越大，则越有利。

#wget https://www.percona.com/downloads/percona-toolkit/2.2.19/RPM/percona-toolkit-2.2.19-1.noarch.rpm

至此就完成了xtrabackup的安装，下面就可以启动备份了。

2）创建备份用的目录（full：全备存放的目录；inc：增量备份存放的目录）

在使用 innobackupex 进行备份时，还可以使用 --no-timestamp 选项来阻止命令自动创建一个以时间命名的目录；如此一来，innobackupex 命令将会创建一个 BACKUP-DIR 目录来存储备份数据。

还可以加 —database 选项指定要备份的数据库，这里指定的数据库只对 MyISAM 表有效，对于 InnoDB 数据来说都是全备（所有数据库中的 InnoDB 数据都进行了备份，不是只备份指定的数据库，恢复时也一样）。

下面我们大体看一下这几个文件：

说明：xtrabackup_binlog_pos_innodb 和 xtrabackup_binary 在这个版本里面没有了，因为版本较新，这两个文件在新版给删除了，只存在于老版本。

我这里直接使用删除数据目录文件来模拟损坏。

一般情况下，在备份完成后，数据尚且不能用于恢复操作，因为备份的数据中可能会包含尚未提交的事务或已经提交但尚未同步至数据文件中的事务。因此，此时数据文件仍处理不一致状态。“准备”的主要作用正是通过回滚未提交的事务及同步已经提交的事务至数据文件也使得数据文件处于一致性状态。

在准备（prepare）过程结束后，InnoDB 表数据已经前滚到整个备份结束的点，而不是回滚到 xtrabackup 刚开始时的点。

innobakupex 命令的 --apply-log 选项可用于实现上述功能。如下面的命令：

--apply-log 指明是将日志应用到数据文件上，完成之后将备份文件中的数据恢复到数据库中：

在实现“准备”的过程中，innobackupex通常还可以使用--use-memory选项来指定其可以使用的内存的大小，默认通常为100M。如果有足够的内存可用，可以多划分一些内存给prepare的过程，以提高其完成速度。

说明：innobackupex 命令的 --copy-back 选项用于执行恢复操作，其通过复制所有数据相关的文件至 mysql 服务器 DATADIR 目录中来执行恢复过程。innobackupex 通过backup-my.cnf 来获取 DATADIR 目录的相关信息。

⑧正式开始还原增量备份

⑨重新启动二进制日志并验证还原数据

附：Xtrabackup 的“流”及“备份压缩”功能

作用：Xtrabackup 对备份的数据文件支持“流”功能，即可以将备份的数据通过 STDOUT 传输给 tar 程序进行归档，而不是默认的直接保存至某备份目录中。

要使用此功能，仅需要使用 --stream 选项即可。如：

看不清截图的可以看下面复制粘贴的命令：

# innobackupex --user=root --password="123456"--stream=tar /opt/mysqlbackup/full/ | gzip >/opt/mysqlbackup/full/full_`date+%F_%H%M%S`.tar.gz

（再补充一句，现实生产环境中，基本上都要用流与备份压缩功能，因为这样可以很大程度上节省空间）

链接：http://zpf666.blog.51cto.com/11248677/1913451

DBA 必知的 MYSQL 备份与还原方法

标签：nmf   adb   自动   编译   方法   测试   最大   cti   hwm   

小编还为您整理了以下内容，可能对您也有帮助：

MySQL如何备份与还原

使用MYSQL数据库的朋友，对MySQL进行备份和还原是比较重要的。重要的事简单做，今天介绍我们使用mysqlmp和source命令来备份还原，分为Windows下和Linux下的MYSQL备份与还原操作。
　　一、Win32系统下MySQL的备份还原方法
　　备份：在“运行”中输入“cmd
”，利用“cd
/Program
Files/MySQL/MySQL
Server
5.0/bin”进入bin文件夹，输入“mysqlmp
-u
用户名
-p
databasename
>exportfilename”导出数据库到文件，如mysqlmp
-u
root
-p
voice>voice.sql，然后输入密码即可开始导出MYSQL数据，实现备份操作。
　　还原：进入MySQL
Command
Line
Client，输入密码，进入到“mysql>”，输入命令"show
databases;"，回车，看看有些什么数据库;建立你要还原的数据库，输入"create
database
voice;"，回车;切换到刚建立的数据库，输入"use
voice;"，回车;导入数据，输入"source
voice.sql;"，回车，开始导入，再次出现"mysql>"并且没有提示错误即还原成功。
　　二、Linux下MySQL的备份与还原方法：
　　备份：[root@localhost
~]#
cd
/var/lib/mysql
(进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)
　　[root@localhost
mysql]#
mysqlmp
-u
root
-p
voice>voice.sql，输入密码即可。
　　还原：有两种方法可选：
　　第一种方法：[root@localhost
~]#
mysql
-u
root
-p
回车，输入密码，进入MySQL的控制台"mysql>"，同1.2还原。
　　第二种方法：
　　[root@localhost
~]#
cd
/var/lib/mysql
(进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)
　　[root@localhost
mysql]#
mysql
-u
root
-p
voice

MySQL如何备份与还原

使用MYSQL数据库的朋友，对MySQL进行备份和还原是比较重要的。重要的事简单做，今天介绍我们使用mysqlmp和source命令来备份还原，分为Windows下和Linux下的MYSQL备份与还原操作。
　　一、Win32系统下MySQL的备份还原方法
　　备份：在“运行”中输入“cmd
”，利用“cd
/Program
Files/MySQL/MySQL
Server
5.0/bin”进入bin文件夹，输入“mysqlmp
-u
用户名
-p
databasename
>exportfilename”导出数据库到文件，如mysqlmp
-u
root
-p
voice>voice.sql，然后输入密码即可开始导出MYSQL数据，实现备份操作。
　　还原：进入MySQL
Command
Line
Client，输入密码，进入到“mysql>”，输入命令"show
databases;"，回车，看看有些什么数据库;建立你要还原的数据库，输入"create
database
voice;"，回车;切换到刚建立的数据库，输入"use
voice;"，回车;导入数据，输入"source
voice.sql;"，回车，开始导入，再次出现"mysql>"并且没有提示错误即还原成功。
　　二、Linux下MySQL的备份与还原方法：
　　备份：[root@localhost
~]#
cd
/var/lib/mysql
(进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)
　　[root@localhost
mysql]#
mysqlmp
-u
root
-p
voice>voice.sql，输入密码即可。
　　还原：有两种方法可选：
　　第一种方法：[root@localhost
~]#
mysql
-u
root
-p
回车，输入密码，进入MySQL的控制台"mysql>"，同1.2还原。
　　第二种方法：
　　[root@localhost
~]#
cd
/var/lib/mysql
(进入到MySQL库目录，根据自己的MySQL的安装情况调整目录)
　　[root@localhost
mysql]#
mysql
-u
root
-p
voice

实现MySQL数据库的备份与还原的几种方法

MyISAM 表是保存成文件的形式，因此相对比较容易备份，上面提到的几种方法都可以使用。Innodb 所有的表都保存在同一个数据文件 ibdata1 中（也可能是多个文件，或者是的表空间文件），相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqlmp。1. 使用直接拷贝数据库备份 典型的如cp、tar或cpio实用程序当你使用直接备份方法时，必须保证表不在被使用。如果服务器在你正在拷贝一个表时改变它，拷贝就失去意义。保证你的拷贝完整性的最好方法是关闭服务器，拷贝文件，然后重启服务器。 如果你不想关闭服务器，要在执行表检查的同时锁定服务器。如果服务器在运行，相同的制约也适用于拷贝文件，应该使用相同的锁定协议让服务器“安静下来”。当你完成了MySQL数据库备份时，需要重启服务器（如果关闭了它）或释放加在表上的锁定（如果你让服务器运行）。 要用直接拷贝文件把一个数据库从一台机器拷贝到另一台机器上，只是将文件拷贝到另一台服务器主机的适当数据目录下即可。要确保文件是MyIASM格式或两台机器有相同的硬件结构，否则你的数据库在另一台主机上有奇怪的内容。你也应该保证在另一台机器上的服务器在你正在安装数据库表时不访问它们。2. 使用mysqlmp数据库备份mysqlmp 是采用SQL级别的备份机制，它将数据表导成 SQL 脚本文件，在不同的 MySQL 版本之间升级时相对比较合适，这也是最常用的备份方法。mysqlmp程序备份数据库较慢，但它生成的文本文件便于移植。mysqlmp 的一些主要参数：1）--compatible=name它告诉 mysqlmp，导出的数据将和哪种数据库或哪个旧版本的 MySQL 服务器相兼容。值可以为 ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options、no_field_options 等，要使用几个值，用逗号将它们隔开。当然了，它并不保证能完全兼容，而是尽量兼容。2）--complete-insert，-c导出的数据采用包含字段名的完整 INSERT 方式，也就是把所有的值都写在一行。这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此，需要谨慎使用该参数，至少我不推荐。3）--default-character-set=charset指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。4）--disable-keys告诉mysqlmp 在 INSERT 语句的开头和结尾增加 ; 和 ; 语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。5）--extended-insert = true|false默认情况下，mysqlmp 开启 --complete-insert 模式，因此不想用它的的话，就使用本选项，设定它的值为 false 即可。6）--hex-blob使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。7）--lock-all-tables，-x在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 --single-transaction 和 --lock-tables 选项。8）--lock-tables它和--lock-all-tables 类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表，如果是 Innodb 表可以用 --single-transaction 选项。9）--no-create-info，-t只导出数据，而不添加 CREATE TABLE 语句。10）--no-data，-d不导出任何数据，只导出数据库表结构。11）--opt这只是一个快捷选项，等同于同时添加 --add-drop-tables --add-locking --create-option --disable-keys --extended-insert --lock-tables --quick --set-charset 选项。本选项能让 mysqlmp 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 --skip-opt 禁用。注意，如果运行 mysqlmp 没有指定 --quick 或 --opt 选项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。12）--quick，-q该选项在导出大表时很有用，它强制 mysqlmp 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。13）--routines，-R导出存储过程以及自定义函数。14）--single-transaction该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。本选项和 --lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。要想导出大表的话，应结合使用 --quick 选项。--triggers同时导出触发器。该选项默认启用，用 --skip-triggers 禁用它。备份：使用mysqlmp备份数据库其实就是把数据库转储成一系列CREATE TABLE和INSERT语句，通过这些语句我们就可重新生成数据库。使用mysqlmp的方法如下：% mysqlmp --opt testdb | gzip > /data/backup/testdb.bak#--opt选项会对转储过程进行优化，生成的备份文件会小一点，后的管道操作会进行数据压缩% mysqlmp --opt testdb mytable1,mytable2 | gzip > /data/backup/testdb_mytable.bak#可在数据库后接数据表名，只导出指定的数据表，多个数据表可用逗号分隔--opt选项还可激活--add-drop-table选项，它将会在备份文件的每条CREATE TABLE前加上一条DROP TABLE IF EXISTS语句。这可方便进行数据表的更新，而不会发生“数据表已存在”的错误。用mysqlmp命令还可直接把数据库转移到另外一台服务器上，不用生成备份文件。重复执行可定期更新远程数据库。% mysqladmin -h remote_host create testdb% mysqlmp --opt testdb | mysql -h remote_host testdb另外还可通过ssh远程调用服务器上的程序，如：% ssh remote_host mysqladmin create testdb% mysqlmp --opt testdb | ssh remote_host mysql testdb 通过直接拷贝系统文件的方式备份数据库，在备份时，要确保没有人对数据库进行修改操作。要做到这点，最好关闭服务器。如果不能关闭的，要以只读方试锁定有关数据表。下面是一些示例：% cp -r db /backup/db #备份db数据库到/backup/db目录% cp table_name.* /backup/db #只备份table_name数据表% scp -r db remotehot:/usr/local/mysql/data #用scp把数据库直接拷贝到远程服务器，在把数据库直接拷贝到远程主机时，应注意两台机器必须有同样的硬件结构，或者将拷贝的数据表全部是可移植数据表类型。或者/usr/local/mysql/bin/mysqlmp -uroot -proot \--default-character-set=utf8 --opt --extended-insert=false \--triggers -R --hex-blob -x testdb > testdb.sql使用以下 SQL 来备份 Innodb 表：/usr/local/mysql/bin/mysqlmp -uroot -proot \--default-character-set=utf8 --opt --extended-insert=false \--triggers -R --hex-blob --single-transaction testdb > testdb.sql另外，如果想要实现在线备份，还可以使用 --master-data 参数来实现，如下：/usr/local/mysql/bin/mysqlmp -uroot -proot \--default-character-set=utf8 --opt --master-data=1 \--single-transaction --flush-logs testdb > testdb.sql它只是在一开始的瞬间请求锁表，然后就刷新binlog了，而后在导出的文件中加入CHANGE MASTER 语句来指定当前备份的binlog位置，如果要把这个文件恢复到slave里去，就可以采用这种方法来做。 还原：用mysqlmp 备份出来的文件是一个可以直接倒入的 SQL 脚本，有两种方法可以将数据导入。直接用 mysql 客户端例如：/usr/local/mysql/bin/mysql -uroot -proot testdb < testdb.sql用SOURCE 语法其实这不是标准的 SQL 语法，而是 mysql 客户端提供的功能，例如：SOURCE /tmp/testdb.sql;这里需要指定文件的绝对路径，并且必须是 mysqld 运行用户(例如 nobody)有权限读取的文件。 3. 使用mysqlhotcopy数据库备份 使用mysqlhotcopy工具，它是一个Perl DBI脚本，可在不关闭服务器的情况下备份数据库，mysqlhotcopy 是一个 PERL 程序，最初由Tim Bunce编写。它使用 LOCK TABLES、FLUSH TABLES 和 cp 或 scp 来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库文件（包括数据表定义文件、数据文件、索引文件）所在的机器上。mysqlhotcopy 只能用于备份 MyISAM，并且只能运行在 类Unix 和 NetWare 系统上。它主要的优点是：它直接拷贝文件，所以它比mysqlmp快。可自动完成数据锁定工作，备份时不用关闭服务器。能刷新日志，使备份文件和日志文件的检查点能保持同步。备份：mysqlhotcopy 支持一次性拷贝多个数据库，同时还支持正则表达。以下是几个例子：/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb /tmp (把数据库目录 testdb 拷贝到 /tmp 下)/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb_1 testdb_2 testdb_n /tmp/usr/local/mysql/bin/mysqlhotcopy -h=localhost -u=root -p=root \testdb./regex/ /tmp 还原：mysqlhotcopy 备份出来的是整个数据库目录，使用时可以直接拷贝到 mysqld 指定的 datadir (在这里是 /usr/local/mysql/data/)目录下即可，同时要注意权限的问题，如下例：cp -rf testdb /usr/local/mysql/data/chown -R nobody:nobody /usr/local/mysql/data/ (将 testdb 目录的属主改成 mysqld 运行用户) 4. 使用SQL语句数据库备份 BACKUP TABLE 语法其实和 mysqlhotcopy 的工作原理差不多，都是锁表，然后拷贝数据文件。它能实现在线备份，但是效果不理想，因此不推荐使用。它只拷贝表结构文件和数据文件，不同时拷贝索引文件，因此恢复时比较慢。备份：BACK TABLE tbl_test TO '/tmp/testdb/'; #把tbl_test数据库备份到/tmp/testdb/目录里，会自动创建一个testdb目录 为了执行该语句，你必须拥有那些表的FILE权限和SELECT权限，备份目录还必须是服务器可写的。该语句执行时，会先把内存中的数据写入磁盘，再把各个数据表的.frm(表结构定义文件)、.MYD(数据)文件从数据目录拷贝到备份目录。它不拷贝.MYI(索引)文件，因为它能用另外两个文件重建。BACKUP TABLE语句备份时，依次锁定数据表，当同时备份多个数据表时，数据表可能会被修改，所以备份0完成时，备份文件中的数据和现时数据表中的数据可能会有差异，为了消除该差异，我们可用只读方式锁定数据表，在备份完成后再解锁。如：mysql> LOCK TABLES tb1 READ,tb2 READ;mysql> BACKUP TABLE tb1,tb2 TO 'backup/db';mysql> UNLOCK TABLES;使用BACKUP TABLE语句备份的数据表可用RESTORE TABLE重新加载到服务器。注意，必须要有 FILE 权限才能执行本SQL，并且目录 /tmp/testdb/ 必须能被 mysqld 用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。SELECT INTO OUTFILE 则是把数据导出来成为普通的文本文件，可以自定义字段间隔的方式，方便处理这些数据。例子：SELECT * INTO OUTFILE '/tmp/testdb/tbl_test.txt' FROM tbl_test;注意，必须要有 FILE 权限才能执行本SQL，并且文件 /tmp/testdb/tbl_test.txt 必须能被 mysqld 用户可写，导出的文件不能覆盖已经存在的文件，以避免安全问题。还原：用BACKUP TABLE 方法备份出来的文件，可以运行 RESTORE TABLE 语句来恢复数据表。例子：RESTORE TABLE FROM '/tmp/testdb/';权限要求类似上面所述。用SELECT INTO OUTFILE 方法备份出来的文件，可以运行 LOAD DATA INFILE 语句来恢复数据表。例子：LOAD DATA INFILE '/tmp/testdb/tbl_name.txt' INTO TABLE tbl_name;权限要求类似上面所述。倒入数据之前，数据表要已经存在才行。

MySQL数据库如何导入导出（备份还原）

本文适用范围：全面阐述MySQL数据库的各种操作，分虚拟主机和服务器两种情况。

虚拟主机

1、通过PHPMyAdmin的导入导出功能，这个软件一般只支持几兆数据的导出，太大的数据可能会超时。

2、通过程序自带的数据库备份还原功能来操作，一些常见的PHP程序如DZ论坛等，后台都有数据库还原和备份的功能，方便我们转移空间数据。

3、如果您的数据库在朝暮数据购买，我们的管理面板支持一键备份和还原。点击备份按钮后，您可以到数据库对应的空间上通过FTP方式下载。

服务器或VPS

首先我们远程到服务器上（Windows或Linux均可），并准备好root密码或具体数据库的用户名密码。

一、数据库的导入（还原）

1、source命令

1）打开开始菜单->程序->MySQL->MySQL Command Line Client，输入MySQL的root密码。

2）然后输入use 数据库名;进入对应的数据库，再用source 备份文件具体路径;命令导入备份文件。注意，在这里的命令后面都要加;分号，截图如下：

2、mysql命令

1）如果是Windows系统，请在命令行模式下进入mysql的bin目录，如D:Programmysql41bin。如果是Linux系统，请直接看下面的步骤。

2）执行mysql命令导入数据库，命令格式为：

mysql -u用户名 -p密码 数据库名<备份文件具体路径

比如：mysql -uroot -pabc123 mydb<file.sql，其中用户名和密码也可以是具体数据库的，不一定要root的。截图如下：

二、数据库的导出（备份）

一般使用mysqlmp命令

1）如果是Windows系统，请在命令行模式下进入mysql的bin目录，如D:Programmysql41bin。如果是Linux系统，请直接看下面的步骤。

2）执行mysqlmp命令导入数据库，命令格式为：

mysqlmp -u用户名 -p密码 数据库名>备份文件具体路径

比如：mysqlmp -uroot -pabc123 mydb>file.sql，其中用户名和密码也可以是具体数据库的，不一定要root的。如果出现when using LOCK TABLES错误，请在database后面加上--skip-lock-tables就不会报错了。截图如下：您可能感兴趣的文章:MySQL 文本文件的导入导出数据的方法Mysql 导入导出csv 中文乱码问题的解决方法mysql 导入导出数据库、数据表的方法MySQL导入导出.sql文件及常用命令小结Mysql导入导出工具Mysqlmp和Source命令用法详解mysql 导入导出数据库以及函数、存储过程的介绍mysqlmp命令导入导出数据库方法与实例汇总MYSQL命令行导入导出数据库详解MYSQL导入导出sql文件简析HeidiSQL工具导出导入MySQL数据