MYSQL教程之Mysql的备份与复原

一些典型的RDBMS功能并不总是在DBaaS系统中可用。例如MySQL学习教程，WindowsAzureSQLDatabase(以前的SQLAzure)是微软的DBaaS产品，提供了一个类似于SQLServer的数据库平台。MySQL备份和恢复
周六,2006/09/30-14:21―yejr

MySQL备份和恢复

作/译者：叶金荣（Email:），http://imysql.cn，转载请说明作/译者和出处，而且不克不及用于贸易用处，背者必究。

日期：2006/10/01

本文会商MySQL的备份和恢复机制，和怎样保护数据表，包含最次要的两种表范例：MyISAM和Innodb，文中计划的MySQL版本为5.0.22。

今朝MySQL撑持的收费备份工具有：mysqldump、mysqlhotcopy，还能够用SQL语法举行备份：BACKUPTABLE大概SELECTINTOOUTFILE，又大概备份二进制日记（binlog），还能够是间接拷贝数据文件和相干的设置文件。MyISAM表是保留成文件的情势，因而绝对对照简单备份，下面提到的几种办法都可使用。Innodb一切的表都保留在统一个数据文件ibdata1中（也多是多个文件，大概是自力的表空间文件），绝对来讲对照欠好备份，收费的计划能够是拷贝数据文件、备份binlog，大概用mysqldump。

1、mysqldump
1.1备份
mysqldump是接纳SQL级其余备份机制，它将数据表导成SQL剧本文件，在分歧的MySQL版本之间晋级时绝对对照符合，这也是最经常使用的备份办法。
如今来说一下mysqldump的一些次要参数：

--compatible=name
它告知mysqldump，导出的数据将和哪一种数据库或哪一个旧版本的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参数的影响而招致拔出失利。因而，必要审慎利用该参数，最少我不保举。

--default-character-set=charset
指定导出数据时接纳何种字符集，假如数据表不是接纳默许的latin1字符集的话，那末导出时必需指定该选项，不然再次导进数据后将发生乱码成绩。

--disable-keys
告知mysqldump在INSERT语句的开首和开头增添/*!40000ALTERTABLEtableDISABLEKEYS*/;和/*!40000ALTERTABLEtableENABLEKEYS*/;语句，这能年夜年夜进步拔出语句的速率，由于它是在拔出完一切数据后才重修索引的。该选项只合适MyISAM表。

--extended-insert=true|false
默许情形下，mysqldump开启--complete-insert形式，因而不想用它的的话，就利用本选项，设定它的值为false便可。

--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
只导出数据，而不增加CREATETABLE语句。

--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
该选项在导出数据之条件交一个BEGINSQL语句，BEGIN不会堵塞任何使用程序且能包管导出时数据库的分歧性形态。它只合用于事件表，比方InnoDB和BDB。
本选项和--lock-tables选项是互斥的，由于LOCKTABLES会使任何挂起的事件隐含提交。
要想导出年夜表的话，应分离利用--quick选项。

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

其他参数概况请参考手册，我一般利用以下SQL来备份MyISAM表：

/usr/local/mysql/bin/mysqldump-uyejr-pyejr
--default-character-set=utf8--opt--extended-insert=false
--triggers-R--hex-blob-xdb_name>db_name.sql
利用以下SQL来备份Innodb表：

/usr/local/mysql/bin/mysqldump-uyejr-pyejr
--default-character-set=utf8--opt--extended-insert=false
--triggers-R--hex-blob--single-transactiondb_name>db_name.sql
别的，假如想要完成在线备份，还可使用--master-data参数来完成，以下：

/usr/local/mysql/bin/mysqldump-uyejr-pyejr
--default-character-set=utf8--opt--master-data=1
--single-transaction--flush-logsdb_name>db_name.sql
它只是在一入手下手的刹时哀求锁表，然后就革新binlog了，尔后在导出的文件中到场CHANGEMASTER语句来指定以后备份的binlog地位，假如要把这个文件恢复到slave里往，就能够接纳这类办法来做。

1.2复原
用mysqldump备份出来的文件是一个能够间接倒进的SQL剧本，有两种办法能够将数据导进。

间接用mysql客户端
比方：

/usr/local/mysql/bin/mysql-uyejr-pyejrdb_name<db_name.sql
用SOURCE语法
实在这不是尺度的SQL语法，而是mysql客户端供应的功效，比方：

SOURCE/tmp/db_name.sql;
这里必要指定文件的相对路径，而且必需是mysqld运转用户(比方nobody)有权限读取的文件。

2、mysqlhotcopy
2.1备份
mysqlhotcopy是一个PERL程序，最后由TimBunce编写。它利用LOCKTABLES、FLUSHTABLES和cp或scp来疾速备份数据库。它是备份数据库或单个表的最快的路子，但它只能运转在数据库文件（包含数据表界说文件、数据文件、索引文件）地点的呆板上。mysqlhotcopy只能用于备份MyISAM，而且只能运转在类Unix和NetWare体系上。

mysqlhotcopy撑持一次性拷贝多个数据库，同时还撑持正则表达。以下是几个例子：

root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr
db_name/tmp(把数据库目次db_name
拷贝到/tmp
下)
root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr
db_name_1...db_name_n/tmp
root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejr
db_name./regex//tmp
更具体的利用办法请检察手册，大概挪用上面的命令来检察mysqlhotcopy的匡助：

perldoc/usr/local/mysql/bin/mysqlhotcopy
注重，想要利用mysqlhotcopy，必需要有SELECT、RELOAD(要实行FLUSHTABLES)权限，而且还必需要可以有读取datadir/db_name目次的权限。

2.2复原
mysqlhotcopy备份出来的是全部数据库目次，利用时能够间接拷贝到mysqld指定的datadir(在这里是/usr/local/mysql/data/)目次下便可，同时要注重权限的成绩，以下例：

root#cp-rfdb_name/usr/local/mysql/data/
root#chown-Rnobody:nobody/usr/local/mysql/data/(将db_name目次的属主改成mysqld
运转用户)
3、SQL语法备份
3.1备份
BACKUPTABLE语法实在和mysqlhotcopy的事情道理差未几，都是锁表，然后拷贝数据文件。它能完成在线备份，可是效果不睬想，因而不保举利用。它只拷贝表布局文件和数据文件，分歧时拷贝索引文件，因而恢复时对照慢。
例子：

BACKTABLEtbl_nameTO/tmp/db_name/;
注重，必需要有FILE权限才干实行本SQL，而且目次/tmp/db_name/必需能被mysqld用户可写，导出的文件不克不及掩盖已存在的文件，以免平安成绩。

SELECTINTOOUTFILE则是把数据导出来成为一般的文本文件，能够自界说字段距离的体例，便利处置这些数据。
例子：

SELECT*INTOOUTFILE/tmp/db_name/tbl_name.txtFROMtbl_name;
注重，必需要有FILE权限才干实行本SQL，而且文件/tmp/db_name/tbl_name.txt必需能被mysqld用户可写，导出的文件不克不及掩盖已存在的文件，以免平安成绩。

3.2恢复
用BACKUPTABLE办法备份出来的文件，能够运转RESTORETABLE语句来恢单数据表。
例子：

RESTORETABLEFROM/tmp/db_name/;
权限请求相似下面所述。

用SELECTINTOOUTFILE办法备份出来的文件，能够运转LOADDATAINFILE语句来恢单数据表。
例子：

LOADDATAINFILE/tmp/db_name/tbl_name.txtINTOTABLEtbl_name;
权限请求相似下面所述。倒进数据之前，数据表要已存在才行。假如忧虑数据会产生反复，能够增添REPLACE关头字来交换已有纪录大概用IGNORE关头字来疏忽他们。

4、启用二进制日记(binlog)
接纳binlog的办法绝对来讲更天真，费心省力，并且还能够撑持增量备份。

启用binlog时必需要重启mysqld。起首，封闭mysqld，翻开my.cnf，到场以下几行：

server-id=1
log-bin=binlog
log-bin-index=binlog.index
然后启动mysqld就能够了。运转过程当中会发生binlog.000001和binlog.index，后面的文件是mysqld纪录一切对数据的更新操纵，前面的文件则是一切binlog的索引，都不克不及容易删除。关于binlog的信息请检察手册。

必要备份时，能够先实行一下SQL语句，让mysqld停止对以后binlog的写进，就能够把文件间接备份，如许的话就可以到达增量备份的目标了：

FLUSHLOGS;假如是备份复制体系中的从服务器，还应当备份master.info和relay-log.info文件。

备份出来的binlog文件能够用MySQL供应的工具mysqlbinlog来检察，如：

/usr/local/mysql/bin/mysqlbinlog/tmp/binlog.000001
该工具同意你显现指定的数据库下的一切SQL语句，而且还能够限制工夫局限，相称的便利，具体的请检察手册。

恢复时，能够接纳相似以下语句来做到：

/usr/local/mysql/bin/mysqlbinlog/tmp/binlog.000001|mysql-uyejr-pyejrdb_name
把mysqlbinlog输入的SQL语句间接作为输出来实行它。

假如你有余暇的呆板，无妨接纳这类体例来备份。因为作为slave的呆板功能请求绝对不是那末高，因而本钱低，用低本钱就可以完成增量备份并且还能分管一部分数据查询压力，何乐而不为呢？

5、间接备份数据文件
相较前几种办法，备份数据文件最为间接、疾速、便利，弱点是基础上不克不及完成增量备份。为了包管数据的分歧性，必要在靠背文件前，实行以下SQL语句：

FLUSHTABLESWITHREADLOCK;也就是把内存中的数据都革新到磁盘中，同时锁定命据表，以包管拷贝过程当中不会有新的数据写进。这类办法备份出来的数据恢复也很复杂，间接拷贝回本来的数据库目次下便可。

注重，关于Innodb范例表来讲，还必要备份其日记文件，即ib_logfile*文件。由于当Innodb表破坏时，就能够依托这些日记文件来恢复。

6、备份战略
关于中品级别营业量的体系来讲，备份战略能够这么定：第一次全量备份，天天一次增量备份，每周再做一次全量备份，云云一向反复。而关于主要的且忙碌的体系来讲，则大概必要天天一次全量备份，每小时一次增量备份，乃至更频仍。为了不影响线上营业，完成在线备份，而且能增量备份，最好的举措就是接纳主从复制机制(replication)，在slave呆板上做备份。

7、数据保护和劫难恢复
作为一位DBA(我今朝还不是，呵呵)，最主要的事情内容之一是包管数据表能平安、不乱、高速利用。因而，必要按期保护你的数据表。以下SQL语句就很有效：

CHECKTABLE或REPAIRTABLE，反省或保护MyISAM表
OPTIMIZETABLE，优化MyISAM表
ANALYZETABLE，剖析MyISAM表
固然了，下面这些命令肇端都能够经由过程工具myisamchk来完成，在这里不作胪陈。

Innodb表则能够经由过程实行以下语句来收拾碎片，进步索引速率：

ALTERTABLEtbl_nameENGINE=Innodb;
这实际上是一个NULL操纵，外表上看甚么也不做，实践上从头收拾碎片了。

一般利用的MyISAM表能够用下面提到的恢复办法来完成。假如是索引坏了，能够用myisamchk工具来重修索引。而关于Innodb表来讲，就没这么间接了，由于它把一切的表都保留在一个表空间了。不外Innodb有一个反省机制叫含混反省点，只需保留了日记文件，就可以依据日记文件来修复毛病。能够在my.cnf文件中，增添以下参数，让mysqld在启动时主动反省日记文件：

innodb_force_recovery=4
关于该参数的信息请检察手册。

8、总结
做好数据备份，定只好符合的备份战略，这是一个DBA所办事情的一小部分，万事开首难，就从如今入手下手吧！也许最好的策略是以不变应万变：给客户他们所需要的，不多也不少。如果MySQL学习教程适合他们，他们就不应该买别的工具。事实上，云计算产业一直推崇自助服务，但提供这些服务的公司已经开始认识到解决方案提供商推销他们商品的价值。

发几份SQL课件，以飨阅者

这是一个不错的新特性。虽然索引的附加字段没有索引键值效率高，但是相对映射到数据表中效率还是提高了很多。我做过试验，在我的实验环境中会比映射到表中提高30%左右的效率。

where子句的作用是在对查询结果进行分组前，将不符合where条件的行去掉，即在分组之前过滤数据，条件中不能包含聚组函数，使用where条件显示特定的行。

而SQLServer如果能像Oracle一样可以为登陆分配如：5%的cpu，10%的内存。就可以解决这个漏洞。

可能有的朋友会抱怨集成的orderby，其实如果使用ranking函数,Orderby是少不了的。如果担心Orderby会影响效率，可以为orderby的字段建立聚集索引，查询计划会忽略orderby操作（因为本来就是排序的嘛）。

发几份SQL课件，以飨阅者

多走走一此相关论坛，多看一些实例开发，多交流0经验，没什么的，我也是刚学没多久!加油