MySQL数据库备份与还原：mysqldump工具实操指南

mysql如何备份和还原数据库

目前支持MySQL的免费备份工具包括mysqldump和mysqlhotcopy。
您还可以使用SQL语法进行备份（BACKUPTABLE或SELECTINTOOUTFILE），备份二进制日志（binlog），或者直接复制数据文件和关联的配置文件。
由于MyISAM表以文件格式存储，因此备份相对容易。
您可以使用上面提到的几种方法。
Innodb中的所有表都存储在同一个数据文件ibdata1中（可能是多个文件，也可能是独立的表空间文件），备份起来相对困难。
有诸如复制数据文件和备份二进制日志之类的事情。
。
1、mysqldump1.1备份mysqldump采用SQL级别的备份机制，将数据表导出到SQL脚本文件中。
这也是最常用的备份方法。
现在我们来谈谈mysqldump的一些关键参数。
•--compatibility=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_pa​​cket参数的影响，可能会导致插入失败。
因此，应谨慎使用该参数。
至少我不推荐它。
•--default-character-set=charset指定导出数据时使用的字符集。
如果您的数据表不使用默认的latin1字符集，则在导出时必须指定该选项。
否则导入后会出现乱码。
又来数据了•--disable-keys告诉mysqldump在INSERT语句的开头和结尾包含/*!40000ALTERTABLEDISABLEKEYS*/；和/*!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等事务表。
锁表选项是互斥的，因为此选项和​​--LOCKTABLES会导致隐式提交待处理事务。
要导出大型表，必须使用--quick选项的组合。
•--触发器也会发出触发器。
该选项默认启用，可以使用--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要备份Innodb表，请使用以下SQL：/usr/local/mysql/bin/mysqldump-uyejr-pyejr--default-character-set=utf8--opt--extended-insert=false\--triggers-R--hex-blob--single-transactiondb_name>db_name.sql1.2恢复mysqldump备份的文件需要直接输入SQL使用脚本检索数据有两种方法：•直接使用mysql客户端。
示例：/usr/local/mysql/bin/mysql-uyejr-pyejrdb_name语法，而是mysql客户端提供的函数（例如SOURCE/tmp/db_name.sql），您必须在其中指定文件的绝对路径，该文件必须是用户mysqld运行时使用的文件（例如none）。
您有阅读权限。
2.mysqlhotcopy2.1备份mysqlhotcopy是一个PERL程序，最初由TimBunce编写。
使用LOCKTABLES、FLUSHTABLES以及cp或scp快速备份数据库。
它是备份数据库或单个表的最快方法，但它只能在包含数据库文件（包括数据表定义文件、数据文件和索引文件）的机器上运行。
mysqlhotcopy只能用于备份MyISAM，并且只能运行在类Unix和NetWare系统上。
mysqlhotcopy支持一次复制多个数据库，还支持正则表达式。
以下是一些示例：root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejrdb_name/tmp(复制数据库目录db_name到/tmp)root#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejrdb_name_1...db_name_n/tmproot#/usr/local/mysql/bin/mysqlhotcopy-h=localhost-u=yejr-p=yejrdb_name./regex//tmp详细使用方法，请参阅手册，或获取有关mysqlhotcopy的帮助，请调用以下命令：perldoc/usr/local/mysql/bin/mysqlhotcopymysqlhotcopy需要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/(maketheownerofthedb_name运行mysqld的用户目录）3.备份SQL语法3.1备份BACKUPTABLE语法其实和mysqlhotcopy的工作原理类似。
。
锁定表，然后复制数据文件。
可以进行在线备份，但不推荐，效果不太理想。
由于只复制表结构文件和数据文件，没有同时复制索引文件，恢复速度慢。
示例：BACKTABLEtbl_nameTO'/tmp/db_name/';您必须具有FILE权限才能运行此SQL，并且/tmp/db_name/目录必须可由mysqld用户写入。
为了避免安全，导出的文件不能覆盖现有文件。
问题。
SELECTINTOOUTFILE将数据导出到纯文本文件。
为了便于处理这些数据，可以自定义字段间距。
示例：SELECTINTOOUTFILE'/tmp/db_name/tbl_name.txt'FROMtbl_name;要运行此SQL，您必须具有FILE权限，并且/tmp/db_name/tbl_name.txt文件必须可由mysqld用户写入，并且导出的文件不能覆盖现有文件以避免安全问题。
3.2使用BACKUPTABLE方法恢复备份文件，可以通过执行RESTORETABLE语句来恢复数据表。
示例：RESTORETABLEFROM'/tmp/db_name/';权限要求与上述类似。
对于使用SELECTINTOOUTFILE方法备份的文件，可以通过运行LOADDATAINFILE语句来恢复数据表。
示例：LOADDATAINFILE'/tmp/db_name/tbl_name.txt'INTOTABLEtbl_name权限要求与上述类似。
导入数据之前，必须先创建数据表（如果存在）。
如果您担心数据重复，可以添加REPLACE关键字来替换现有记录或使用IGNORE关键字来忽略它们。
补充：shell>mysqldump--quickdb_name|gzip>db_name.contents.gz（本例生成的文件为压缩格式）。
恢复/转移到另一台机器的命令是：shell>gunzip4、启用二进制日志（binlog）binlog的方法相对较多。
它非常灵活，可以减少忧虑和精力，甚至可以支持增量备份。
启用binlog后必须重新启动Mysqld。
首先关闭mysqld，打开my.cnf并添加以下行：server-id=1log-bin=binloglog-bin-index=binlog.index然后启动mysqld。
操作过程中会创建binlog.000001和binlog.index。
前者是一个文件，将mysqld的所有更新操作记录到数据中，而后者是所有binlog的索引，不能轻易删除。
有关binlog的更多信息，请参阅文档。
如果需要备份，首先运行一条SQL语句来阻止mysqld写入当前的binlog，然后您可以直接备份文件。
这样就可以达到增量备份的目的了。
由于它是备份复制系统的从属系统，因此您还应该备份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语句作为输入来执行。
如果您有闲置的机器，也可以使用此方法进行备份。
由于对从机的性能要求较低，因此成本较低。
增量备份可以低成本实现，并分担部分数据查询压力。
5、数据文件直接备份与以往的方法相比，数据文件备份是最直接、快捷、方便的。
缺点是默认情况下无法进行增量备份。
为了保证数据的一致性，在备份文件之前必须执行以下SQL语句：这意味着内存中的所有数据都被刷新到磁盘，同时数据表被锁定。
这是要确保在复制过程中没有写入新数据的时候。
通过这种方式备份的数据恢复起来也非常简单。
只需将其复制回原始数据库目录即可。
对于Innodb类型的表，还必须备份相应的日志文件，即ib_logfile*文件。
这是因为如果您的Innodb表损坏，您可以使用这些日志文件来恢复它。
6、备份策略对于中型系统，备份策略可以确定为初始全备份、每天一次增量备份、每周一次全备份等。
关键和繁忙的系统可能需要每天一次完整备份，或者每小时一次或更频繁的增量备份。
在不影响线上业务的情况下，实现在线备份和增量备份的最佳方式是使用主从复制机制（replication）在从系统上进行备份。
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有关此参数的更多信息，请参阅文档。
有点长，我觉得慢慢看会对你有帮助。

mysqldump导出sql文件

mysqldump是官方支持的MySQL数据库备份和恢复工具，它可以以sql文件格式导出MySQL数据库。
mysqldump可以指定导出的sql文件的格式；可以导出所有数据库；还可以指定导出数据库中的表；它甚至可以设置要导出的表的某些字段。
您还可以将备份文件设置为压缩为zip和gzip等格式。