MySQL的mysqldump命令等

MySQL的mysqldump命令等

1.用mysqldump对MySQL数据库进行数据备份与恢复

下面假设要备份tm这个数据库：

Shell>mysqldump  -uroot  –p123456  tm > tm_050519.sql

 

这时可以利用gzip压缩数据，命令如下：

Shell>mysqldump  -uroot  -p123456  tm | gzip > tm_050519.sql.gz

 

恢复数据：

Shell>mysql  -uroot  -p123456  tm < tm_050519.sql

 

从压缩文件直接恢复：

Shell>gzip < tm_050519.sql.gz | mysql  -uroot  -p123456  tm

2.关于使用mysqldump的一些参数选项

(1)直接使用mysqldump -uroot -pxxxx tetratest > "d:/data1.sql"

导出数据时，没有建库语句，这时你可以手动创建一个库，然后使用：

mysql -uroot -pabcd mydatabase < "d:/data1.sql"，即将一个数据库“复制”到一个不同名称的数据库下。同时，这种情况下，存储过程及函数并没有转储到文件中。

 

(2)如果你未使用--quick或者--opt选项，那么mysqldump将在转储结果之前把全部内容载入到内存中。这在你转储大数据量的数据库时将会有些问题。该选项默认是打开的，但可以使用--skip-opt来关闭它。

 

(3)使用--skip-comments可以去掉导出文件中的注释语句

 

(4)使用--compact选项可以只输出最重要的语句，而不输出注释及删除表语句等等

 

(5)使用--database或-B选项，可以转储多个数据库，在这个选项名后的参数都被认定为数据库名
mysqldump -uroot -paaa --database db1  db2 >"d:/mydata.sql"。同时，使用该参数会使用导出文件中增加创建库的语句。如不带该选项，则第二个参数将被认定为表名，即：mysqldump -uroot -paaa  my1 mytable1 >"d:/mydata.sql"，将导出表mytable1的结构及数据。

 

(6)--tables ，在此选项之后的参数都被认定为表名。

 

(7)--no-create-db

 

(8)--no-create-info

 

(9)--no-data

 

(10)--routines, -R 将使存储过程、函数也转储到文件中来。
(更多请参考MySQL手册)

 

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客户程序很容易就能把数据导入了：
　　
　　mysqladmin create target_db_name

 

3.MySQL中的临时表及HEAP表

给正常的CREATE TABLE语句加上TEMPORARY关键字：

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

)

 

临时表将在你连接MySQL期间存在。当你断开时，MySQL将自动删除表并释放所用的空间。当然你可以在仍然连接的时候删除表并释放空间。

DROP TABLE tmp_table

 

如果你声明临时表是一个HEAP表，MySQL也允许你指定在内存中创建它：

CREATE TEMPORARY TABLE tmp_table (

name VARCHAR(10) NOT NULL,

value INTEGER NOT NULL

) TYPE = HEAP

 

HEAP表格使用一个哈希索引并且存储在内存中，这使他们更快，但是如果MySQL崩溃，你将失去所有存储的数据。HEAP作为临时表很可用！

 

当你使用HEAP表时，这里是你应该考虑的一些事情：

你应该总是在CREATE语句中指定MAX_ROWS以保证你有意不使用所有的内存。

索引将只能与与=和<=>一起使用（但是很快)。

HEAP表使用一个固定的记录长度格式。

HEAP不支持BLOB/TEXT列。

HEAP不支持AUTO_INCREMENT列。

HEAP不支持在一个NULL列上的索引。

你可以在一个HEAP表中有非唯一键(哈希表一般不这样)。

HEAP表格在所有的客户之间被共享(就象任何其他的表)。

HEAP表的数据以小块分配。表是100%动态的(在插入时)，无需溢出区和额外的键空间。删除的行放入一个链接表并且当你把新数据插入到表时，它将被再次使用。

为了释放内存，你应该执行DELETE FROM heap_table或DROP TABLE heap_table。

为了保证你不会偶然做些愚蠢的事情，你不能创建比max_heap_table_size大的HEAP表。

 

4.MySQL的数据库引擎的类型

你能用的数据库引擎取决于mysql在安装的时候是如何被编译的。要添加一个新的引擎，就必须重新编译MYSQL。在缺省情况下，MYSQL支持三个引擎：ISAM、MYISAM和HEAP。另外两种类型INNODB和BERKLEY（BDB），也常常可以使用。

 

ISAM

ISAM是一个定义明确且历经时间考验的数据表格管理方法，它在设计之时就考虑到数据库被查询的次数要远大于更新的次数。因此，ISAM执行读取操作的速度很快，而且不占用大量的内存和存储资源。ISAM的两个主要不足之处在于，它不支持事务处理，也不能够容错：如果你的硬盘崩溃了，那么数据文件就无法恢复了。如果你正在把ISAM用在关键任务应用程序里，那就必须经常备份你所有的实时数据，通过其复制特性，MYSQL能够支持这样的备份应用程序。

 

MYISAM

MYISAM是MYSQL的ISAM扩展格式和缺省的数据库引擎。除了提供ISAM里所没有的索引和字段管理的大量功能，MYISAM还使用一种表格锁定的机制，来优化多个并发的读写操作。其代价是你需要经常运行OPTIMIZE TABLE命令，来恢复被更新机制所浪费的空间。MYISAM还有一些有用的扩展，例如用来修复数据库文件的MYISAMCHK工具和用来恢复浪费空间的MYISAMPACK工具。

MYISAM强调了快速读取操作，这可能就是为什么MYSQL受到了WEB开发如此青睐的主要原因：在WEB开发中你所进行的大量数据操作都是读取操作。所以，大多数虚拟主机提供商和INTERNET平台提供商只允许使用MYISAM格式。

 

HEAP

HEAP允许只驻留在内存里的临时表格。驻留在内存里让HEAP要比ISAM和MYISAM都快，但是它所管理的数据是不稳定的，而且如果在关机之前没有进行保存，那么所有的数据都会丢失。在数据行被删除的时候，HEAP也不会浪费大量的空间。HEAP表格在你需要使用SELECT表达式来选择和操控数据的时候非常有用。要记住，在用完表格之后就删除表格。

 

INNODB和BERKLEYDB

INNODB和BERKLEYDB（BDB）数据库引擎都是造就MYSQL灵活性的技术的直接产品，这项技术就是MYSQL++ API。在使用MYSQL的时候，你所面对的每一个挑战几乎都源于ISAM和MYISAM数据库引擎不支持事务处理也不支持外来键。尽管要比ISAM和MYISAM引擎慢很多，但是INNODB和BDB包括了对事务处理和外来键的支持，这两点都是前两个引擎所没有的。如前所述，如果你的设计需要这些特性中的一者或者两者，那你就要被迫使用后两个引擎中的一个了。