MySQL数据库中常用的备份之一，MySQLdump

以下的文章主要介绍的是MySQL数据库中我们大家经常使用的备份方法之一即，MySQLdump和在MySQLdump在实际运行中我们应注意的相关事项的描述，以下就是具体方案的描述，希望在你今后的学习中会对你有所帮助。

MySQLdump

基本语法：

Shell> MySQLdump [OPTIONS] database [tables] > data_backup.sql （不指定MySQL数据库名表示全部备份）

例如：

MySQLdump -uroot -p default-character-set=cp932 opt extended-insert=false hex-blob -R -x MySQL > E:\MySQL.sql

输出文件的开头看起来象这样：

# MySQL Dump 6.0
#
# Host: localhost Database: samp_db
#-
# Server version 3.23.2-alpha-log
#
# Table structure for table 'absence'
#
Create TABLE absence(
student_id int(10) unsigned DEFAULT '0' NOT NULL,
date date DEFAULT '0000-00-00' NOT NULL,
PRIMARY KEY (student_id,date)
);
#
# Dumping data for table 'absence'
#
Insert INTO absence VALUES (3,'1999-09-03');
Insert INTO absence VALUES (5,'1999-09-03');
Insert INTO absence VALUES (10,'1999-09-08');
......

文件剩下的部分有更多的Insert和Create TABLE语句组成。例：

%MySQLdump samp_db >/opt/MySQLdatabak/samp_db.2006-5-15
%MySQLdump samp_db | gzip >/usr/archives/MySQL/samp_db.1999-10-02.gz #产生压缩备份
%MySQLdump samp_db student score event absence >grapbook.sql #备份数据库的某些表
%MySQLadmin -h boa.snake.net create samp_db
%MySQLdump samp_db | MySQL -h boa.snake.net samp_db

直接恢复到另一个服务器上使用add-drop-table选项告诉服务器将Drop TABLE IF EXISTS语句写入备份文件，这样当我们以后用来恢复MySQL数据库时，如果表已经存在，你不会得到一个错误。

%MySQLdump add-drop-table samp_db | MySQL -h boa.snake.net samp_db

MySQLdump其它有用的选项包括：

flush- logs和lock-tables组合将对你的数据库检查点有帮助。lock-tables锁定你正在倾倒的所有表，而flush-logs关闭并重新打 开更新日志文件，新的更新日志将只包括从备份点起的修改数据库的查询。这将设置你的更新日志检查点位备份时间。（然而如果你有需要执行个更新的客户，锁定 所有表对备份期间的客户访问不是件好事。）

如果你使用flush-logs设置检查点到备份时，有可能最好是倾倒整个数据库。如果你倾倒单独的文件，较难将更新日志检查点与备份文件同步。在恢复期间，你通常按数据库为基础提取更新日志内容，对单个表没有提取更新的选择，所以你必须自己提取它们。

缺 省地，MySQLdump在写入前将一个表的整个内容读进内存。这通常确实不必要，并且实际上如果你有一个大表，几乎是失败的。你可用quick选项告诉 MySQLdump只要它检索出一行就写出每一行。为了进一步优化倾倒过程，使用opt而不是quick。opt选项打开其它选项，加速数据的倾倒和把它 们读回。

用opt实施备份可能是最常用的方法，因为备份速度上的优势。然而，要警告你，opt选项确实有代价，opt优化的是你的备份过程，不是其他客户对数据库的访问。opt选项通过一次锁定所有表阻止任何人更新你正在倾倒的任何表。你可在一般数据库访问上很容易看到其效果。

一个具有opt的相反效果的选项是dedayed。该选项使得MySQLdump写出Insert DELAYED语句而不是Insert语句。如果你将数据文件装入另一个数据库并且你想使这个操作对可能出现在该数据库中的查询的影响最小，delayed对此很有帮助。

compress选项在你拷贝数据库到另一台机器上时很有帮助，因为它减少网络传输字节的数量。下面有一个例子，注意到compress对与远端主机上的服务器通信的程序才给出，而不是对与本地主机连接的程序：

%MySQLdump opt samp_db | MySQL compress -h boa.snake.net samp_db

MySQLdump有很多其它选项，主要参数：

compatible=name

它 告诉 MySQLdump，导出的数据将和哪种MySQL数据库或哪个旧版本的 MySQL 服务器相兼容。值可以为 ansi、MySQL323、MySQL40、postgresql、oracle、mssql、db2、maxdb、no_key_options、 no_tables_options、no_field_options 等，要使用几个值，用逗号将它们隔开。当然了，它并不保证能完全兼容，而是尽量兼容。

complete-insert，-c

导出的数据采用包含字段名的完整 Insert 方式，也就是把所有的值都写在一行。这么做能提高插入效率，但是可能会受到 max_allowed_packet 参数的影响而导致插入失败。因此，需要谨慎使用该参数，至少我不推荐。

extended-insert = true|false

默认情况下，MySQLdump 开启 complete-insert 模式，因此不想用它的的话，就使用本选项，设定它的值为 false 即可。

default-character-set=charset

指定导出数据时采用何种字符集，如果数据表不是采用默认的 latin1 字符集的话，那么导出时必须指定该选项，否则再次导入数据后将产生乱码问题。

disable-keys

告 诉 MySQLdump 在 Insert 语句的开头和结尾增加 /*!40000 Alter TABLE table DISABLE KEYS */; 和 /*!40000 Alter TABLE table ENABLE KEYS */; 语句，这能大大提高插入语句的速度，因为它是在插入完所有数据后才重建索引的。该选项只适合 MyISAM 表。

hex-blob

使用十六进制格式导出二进制字符串字段。如果有二进制数据就必须使用本选项。影响到的字段类型有 BINARY、VARBINARY、BLOB。

lock-all-tables，-x

在开始导出之前，提交请求锁定所有数据库中的所有表，以保证数据的一致性。这是一个全局读锁，并且自动关闭 single-transaction 和 lock-tables 选项。

lock-tables

它和 lock-all-tables 类似，不过是锁定当前导出的数据表，而不是一下子锁定全部库下的表。本选项只适用于 MyISAM 表，如果是 Innodb 表可以用 single-transaction 选项。

no-create-info，-t

只导出数据，而不添加 Create TABLE 语句。

no-data，-d

不导出任何数据，只导出数据库表结构。

opt

这 只是一个快捷选项，等同于同时添加 add-drop-tables add-locking create-option disable-keys extended-insert lock-tables quick set-charset 选项。本选项能让 MySQLdump 很快的导出数据，并且导出的数据能很快导回。该选项默认开启，但可以用 skip-opt 禁用。注意，如果运行 MySQLdump 没有指定 quick 或 opt 选项，则会将整个结果集放在内存中。如果导出大数据库的话可能会出现问题。

quick，-q

该选项在导出大表时很有用，它强制 MySQLdump 从服务器查询取得记录直接输出而不是取得所有记录后将它们缓存到内存中。

routines，-R

导出存储过程以及自定义函数。

single-transaction

该选项在导出数据之前提交一个 BEGIN SQL语句，BEGIN 不会阻塞任何应用程序且能保证导出时MySQL数据库的一致性状态。它只适用于事务表，例如 InnoDB 和 BDB。

本选项和 lock-tables 选项是互斥的，因为 LOCK TABLES 会使任何挂起的事务隐含提交。

要想导出大表的话，应结合使用 quick 选项。

triggers

同时导出触发器。该选项默认启用，用 skip-triggers 禁用它。

---------------------------------------------------------------------------------------------

mysqldump使用小结

导出要用到MySQL的mysqldump工具，基本用法是：   

shell> mysqldump [OPTIONS] database [tables]   

如果你不给定任何表，整个数据库将被导出。   

通过执行mysqldump --help，你能得到你mysqldump的版本支持的选项表。   

注意，如果你运行mysqldump没有--quick或--opt选项，mysqldump将在导出结果前装载整个结果集到内存中，如果你正在导出一个大的数据库，这将可能是一个问题。   

mysqldump支持下列选项：   

--add-locks   
在每个表导出之前增加LOCK TABLES并且之后UNLOCK TABLE。(为了使得更快地插入到MySQL)。   
--add-drop-table   
在每个create语句之前增加一个drop table。   
--allow-keywords   
允许创建是关键词的列名字。这由表名前缀于每个列名做到。   
-c, --complete-insert   
使用完整的insert语句(用列名字)。   
-C, --compress   
如果客户和服务器均支持压缩，压缩两者间所有的信息。   
--delayed   
用Insert DELAYED命令插入行。   
-e, --extended-insert   
使用全新多行Insert语法。（给出更紧缩并且更快的插入语句）   
-#, --debug[=option_string]   
跟踪程序的使用(为了调试)。   
--help   
显示一条帮助消息并且退出。   
--fields-terminated-by=...   
   
--fields-enclosed-by=...   
   
--fields-optionally-enclosed-by=...   
   
--fields-escaped-by=...   
   
--fields-terminated-by=...   
这些选择与-T选择一起使用，并且有相应的LOAD DATA INFILE子句相同的含义。   
LOAD DATA INFILE语法。   
-F, --flush-logs   
在开始导出前，洗掉在MySQL服务器中的日志文件。   
-f, --force,   
即使我们在一个表导出期间得到一个SQL错误，继续。   
-h, --host=..   
从命名的主机上的MySQL服务器导出数据。缺省主机是localhost。   
-l, --lock-tables.   
为开始导出锁定所有表。   
-t, --no-create-info   
不写入表创建信息(Create TABLE语句）   
-d, --no-data   
不写入表的任何行信息。如果你只想得到一个表的结构的导出，这是很有用的！   
--opt   
同--quick --add-drop-table --add-locks --extended-insert --lock-tables。   
应该给你为读入一个MySQL服务器的尽可能最快的导出。   
-pyour_pass, --password[=your_pass]   
与服务器连接时使用的口令。如果你不指定“=your_pass”部分，mysqldump需要来自终端的口令。   
-P port_num, --port=port_num   
与一台主机连接时使用的TCP/IP端口号。（这用于连接到localhost以外的主机，因为它使用 Unix套接字。）   
-q, --quick   
不缓冲查询，直接导出至stdout；使用mysql_use_result()做它。   
-S /path/to/socket, --socket=/path/to/socket   
与localhost连接时（它是缺省主机)使用的套接字文件。   
-T, --tab=path-to-some-directory   
对于每个给定的表，创建一个table_name.sql文件，它包含SQL Create 命令，和一个table_name.txt文件，它包含数据。 注意：这只有在mysqldump运行在mysqld守护进程运行的同一台机器上的时候才工作。.txt文件的格式根据--fields-xxx和 --lines--xxx选项来定。   
-u user_name, --user=user_name   
与服务器连接时，MySQL使用的用户名。缺省值是你的Unix登录名。   
-O var=option, --set-variable var=option设置一个变量的值。可能的变量被列在下面。   
-v, --verbose   
冗长模式。打印出程序所做的更多的信息。   
-V, --version   
打印版本信息并且退出。   
-w, --where='where-condition'   
只导出被选择了的记录；注意引号是强制的！   
"--where=user='jimf'" "-wuserid>1" "-wuserid<1"  

最常见的mysqldump使用可能制作整个数据库的一个备份：  

mysqldump --opt database > backup-file.sql   

但是它对用来自于一个数据库的信息充实另外一个MySQL数据库也是有用的：   

mysqldump --opt database | mysql --host=remote-host -C database   

由于mysqldump导出的是完整的SQL语句，所以用mysql客户程序很容易就能把数据导入了：   

shell> mysqladmin create target_db_name   
shell> mysql target_db_name < backup-file.sql  
就是  
shell> mysql 库名 < 文件名  

------------------------------------------------------------------------------------- 

mysql备份导入数据库手记 

用mysqldump 
把一个库导出到一个SQL文件 
mysqldump db_name > /path/name.sql -uroot -p 
如果数据库太大，压缩导出 
mysqldump db_name | gzip > /path/name.sql -u root -p 

备份全部的库 
mysqldump --all-databases > /path/name.sql -u root -p 
mysqldump --all-databases | gzip > /path/name.gz -uroot -p 
备份一个单独或者几个表 
mysqldump db_name tab_name > /path/sqlname.sql -u root -p 
恢复数据 导入数据 
mysql db_name < backup-file.sql -u root -p 
有一种用mysql db_name < backup-file.sql -u root -p 
第二种方法： 
mysql -u root -p 
use db_name 
. /path/dbname.sql 
这种方法保险点，因为有时我用 db_name < dbname.sql有问题 
用第二种方法可以的 
………………………………………………………… 
添加mysql用户mysqlname 密码为password ，赋予dbname数据库所有权限 
mysql> grant all on dbname.* to mysqlname@localhost identified by 'password'; 
mysql > grant select,insert,update,delete,create,drop on 数据库名.* to 用户(新建)@localhost identified by '密码'; 
或者直接修改mysql表 
mysql > insert into user(host,user,password) values('%','user_name',password("you password")); 
mysql > set password for user_name=password("you_password") 
以上两种必须进行重载授权表(./scripts/mysql_install_db) 
或者在shell环境下 
mysqladmin -u root password "you password" 
 

恢复

1. /usr/local/mysql/bin/mysql -uroot -p db_name < db_name.sql  
2. D:\AppServ\MySQL\bin>mysql -uroot -p db_name < gong_data.sql

如果导入出错，请点击

 

转载请注明本文出处：http://www.infocool.net/kb/Mysql/201607/161977.html