MySQL数据库中备份相关的两种工具--mysqlbinlog和mysqldump使用详解

一、mysqldump

对数据库做完整备份命令格式：

#mysqldump  -h服务器ip  -u用户名  -p密码备份对象> /dbdir/文件名.sql  

说明：

1、不指定路径存储在当前目录下；

2、/dbdir需提前创建；

3、文件名要有标识性，备份文件不能重名，否则会覆盖。做计划任务时用date获取日期做文件名:

00 02 * * 1 mysqldump  -hlocalhost -uroot  -p123  --all- databases> /dbdir/db.a-$(date  +\%Y\%m\%d).sql

逻辑完整备份的恢复命令格式：

#mysql -h服务器ip  -u用户名  -p密码 [数据库名]< /路径/文件名.sql

说明：

1、备份文件中有建库或使用库的命令时数据库名不要写，会报错，否则要写；

2、使用完整备份文件，只能把数据恢复到备份时的状态

mysqldump支持下面的选项：

---help，-？   显示帮助消息并退出。

--add-drop—database  在每个CREATE DATABASE语句前添加DROP DATABASE语句。

--add-drop-tables   在每个CREATE TABLE语句前添加DROP TABLE语句。

--add-locking  用LOCK TABLES和UNLOCK TABLES语句引用每个表转储。重载转储文件时插入得更快。

--all--database，-A  转储所有数据库中的所有表。与使用---database选项相同，在命令行中命名所有数据库。

--allow-keywords  允许创建关键字列名。应在每个列名前面加上表名前缀。

---comments[={0|1}]  如果设置为 0，禁止转储文件中的其它信息，例如程序版本、服务器版本和主机。--skip—comments与---comments=0的结果相同。默认值为1，即包括额外信息。

--compact  产生少量输出。该选项禁用注释并启用--skip-add-drop-tables、--no-set-names、--skip-disable-keys和--skip-add-locking选项。

--compatible=name 产生与其它数据库系统或旧的MySQL服务器更兼容的输出。值可以为ansi、mysql323、mysql40、postgresql、oracle、mssql、db2、maxdb、no_key_options、no_tables_options或者no_field_options。要使用几个值，用逗号将它们隔开。这些值与设置服务器SQL模式的相应选项有相同的含义。该选项不能保证同其它服务器之间的兼容性。它只启用那些目前能够使转储输出更兼容的SQL模式值。例如，--compatible=oracle 不映射Oracle类型或使用Oracle注释语法的数据类型。

--complete-insert，-c使用包括列名的完整的INSERT语句。

--compress，-C  压缩在客户端和服务器之间发送的所有信息（如果二者均支持压缩）。

--create-option 在CREATETABLE语句中包括所有MySQL表选项。

---database，-B  转储几个数据库。通常情况，mysqldump将命令行中的第1个名字参量看作数据库名，后面的名看作表名。使用该选项，它将所有名字参量看作数据库名。CREATEDATABASE IF NOT EXISTS db_name和USEdb_name语句包含在每个新数据库前的输出中。

---debug[=debug_options]，-#[debug_options]  写调试日志。debug_options字符串通常为'd:t:o,file_name'。

--default-character-set=charset 使用charsetas默认字符集。如果没有指定，mysqldump使用utf8。

--delayed-insert   使用INSERT DELAYED语句插入行。

--delete-master-logs  在主复制服务器上，完成转储操作后删除二进制日志。该选项自动启用--master-data。

--disable-keys，-K 对于每个表，用/*!40000 ALTER TABLE tbl_nameDISABLE KEYS */;和/*!40000ALTER TABLE tbl_name ENABLE KEYS */;语句引用INSERT语句。这样可以更快地装载转储文件，因为在插入所有行后创建索引。该选项只适合MyISAM表。

--extended-insert，-e  使用包括几个VALUES列表的多行INSERT语法。这样使转储文件更小，重载文件时可以加速插入。

--fields-terminated-by=...，--fields-enclosed-by=...，--fields-optionally-enclosed-by=...，--fields-escaped-by=...，--行-terminated-by=...    这些选项结合-T选项使用，与LOAD DATA INFILE的相应子句有相同的含义。

--first-slave，-x  不赞成使用，现在重新命名为--lock-all-tables。

--flush-logs，-F  开始转储前刷新MySQL服务器日志文件。该选项要求RELOAD权限。请注意如果结合--all--database(或-A)选项使用该选项，根据每个转储的数据库刷新日志。例外情况是当使用--lock-all-tables或--master-data的时候：在这种情况下，日志只刷新一次，在所有表被锁定后刷新。如果你想要同时转储和刷新日志，应使用--flush-logs连同--lock-all-tables或--master-data。

--force，-f   在表转储过程中，即使出现SQL错误也继续。

--host=host_name，-hhost_name  从给定主机的MySQL服务器转储数据。默认主机是localhost。

--hex-blob  使用十六进制符号转储二进制字符串列(例如，'abc' 变为0x616263)。影响到的列有BINARY、VARBINARY、BLOB。

--lock-all-tables，-x   所有数据库中的所有表加锁。在整体转储过程中通过全局读锁定来实现。该选项自动关闭--single-transaction和--lock-tables。

--lock-tables，-l   开始转储前锁定所有表。用READLOCAL锁定表以允许并行插入MyISAM表。对于事务表例如InnoDB和BDB，--single-transaction是一个更好的选项，因为它不根本需要锁定表。请注意当转储多个数据库时，--lock-tables分别为每个数据库锁定表。因此，该选项不能保证转储文件中的表在数据库之间的逻辑一致性。不同数据库表的转储状态可以完全不同。

--master-data[=value]   该选项将二进制日志的位置和文件名写入到输出中。该选项要求有RELOAD权限，并且必须启用二进制日志。如果该选项值等于1，位置和文件名被写入CHANGE MASTER语句形式的转储输出，如果你使用该SQL转储主服务器以设置从服务器，从服务器从主服务器二进制日志的正确位置开始。如果选项值等于2，CHANGE MASTER语句被写成SQL注释。如果value被省略，这是默认动作。--master-data选项启用--lock-all-tables，除非还指定--single-transaction(在这种情况下，只在刚开始转储时短时间获得全局读锁定。又见--single-transaction。在任何一种情况下，日志相关动作发生在转储时。该选项自动关闭--lock-tables。

--no-create-db，-n   该选项禁用CREATEDATABASE /*!32312 IF NOT EXISTS*/ db_name语句，如果给出---database或--all--database选项，则包含到输出中。

--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。它可以给出很快的转储操作并产生一个可以很快装入MySQL服务器的转储文件。该选项默认开启，但可以用--skip-opt禁用。要想只禁用确信用-opt启用的选项，使用--skip形式；例如，--skip-add-drop-tables或--skip-quick。

--password[=password]，-p[password]    连接服务器时使用的密码。如果你使用短选项形式(-p)，不能在选项和密码之间有一个空格。如果在命令行中，忽略了--password或-p选项后面的密码值，将提示你输入一个。

--port=port_num，-Pport_num   用于连接的TCP/IP端口号。

--protocol={TCP | SOCKET | PIPE | MEMORY}   使用的连接协议。

--quick，-q    该选项用于转储大的表。它强制mysqldump从服务器一次一行地检索表中的行而不是检索所有行并在输出前将它缓存到内存中。

--quote-names，-Q    用‘`’字符引用数据库、表和列名。如果服务器SQL模式包括ANSI_QUOTES选项，用‘"’字符引用名。默认启用该选项。可以用--skip-quote-names禁用，但该选项应跟在其它选项后面，例如可以启用--quote-names的--compatible。

--result-file=file，-rfile    将输出转向给定的文件。该选项应用在Windows中，因为它禁止将新行‘\n’字符转换为‘\r\n’回车、返回/新行序列。

--routines，-R    在转储的数据库中转储存储程序(函数和程序)。使用---routines产生的输出包含CREATE PROCEDURE和CREATE FUNCTION语句以重新创建子程序。但是，这些语句不包括属性，例如子程序定义者或创建和修改时间戳。这说明当重载子程序时，对它们进行创建时定义者应设置为重载用户，时间戳等于重载时间。如果你需要创建的子程序使用原来的定义者和时间戳属性，不使用--routines。相反，使用一个具有mysql数据库相应权限的MySQL账户直接转储和重载mysql.proc表的内容。该选项在MySQL 5.1.2中添加进来。在此之前，存储程序不转储。

--set-charset   将SET NAMES default_character_set加到输出中。该选项默认启用。要想禁用SETNAMES语句，使用--skip-set-charset。

--single-transaction    该选项从服务器转储数据之前发出一个BEGINSQL语句。它只适用于事务表，例如InnoDB和BDB，因为然后它将在发出BEGIN而没有阻塞任何应用程序时转储一致的数据库状态。当使用该选项时，应记住只有InnoDB表能以一致的状态被转储。例如，使用该选项时任何转储的MyISAM或HEAP表仍然可以更改状态。--single-transaction选项和--lock-tables选项是互斥的，因为LOCK TABLES会使任何挂起的事务隐含提交。要想转储大的表，应结合--quick使用该选项。

--socket=path，-S path   当连接localhost(为默认主机)时使用的套接字文件。

--skip—comments   参见---comments选项的描述。

--tab=path，-T path   产生tab分割的数据文件。对于每个转储的表，mysqldump创建一个包含创建表的CREATE TABLE语句的tbl_name.sql文件，和一个包含其数据的tbl_name.txt文件。选项值为写入文件的目录。默认情况，.txt数据文件的格式是在列值和每行后面的新行之间使用tab字符。可以使用--fields-xxx和--行--xxx选项明显指定格式。注释：该选项只适用于mysqldump与mysqld服务器在同一台机器上运行时。你必须具有FILE权限，并且服务器必须有在你指定的目录中有写文件的许可。

--tables   覆盖---database或-B选项。选项后面的所有参量被看作表名。

--triggers    为每个转储的表转储触发器。该选项默认启用；用--skip-triggers禁用它。

--tz-utc   在转储文件中加入SET TIME_ZONE='+00:00'以便TIMESTAMP列可以在具有不同时区的服务器之间转储和重载。(不使用该选项，TIMESTAMP列在具有本地时区的源服务器和目的服务器之间转储和重载）。--tz-utc也可以保护由于夏令时带来的更改。--tz-utc默认启用。要想禁用它，使用--skip-tz-utc。该选项在MySQL 5.1.2中加入。

--user=user_name，-uuser_name   连接服务器时使用的MySQL用户名。

--verbose，-v   冗长模式。打印出程序操作的详细信息。

--version，-V   显示版本信息并退出。

--where='where-condition', -w 'where-condition'    只转储给定的WHERE条件选择的记录。请注意如果条件包含命令解释符专用空格或字符，一定要将条件引用起来。例如：

"--where=user='jimf'" "-wuserid>1""-wuserid<1"

--xml，-X   将转储输出写成XML。

还可以使用--var_name=value选项设置下面的变量：

·        max_allowed_packet

客户端/服务器之间通信的缓存区的最大大小。最大为1GB。

·        net_buffer_length

客户端/服务器之间通信的缓存区的初始大小。当创建多行插入语句时(如同使用选项--extended-insert或--opt)，mysqldump创建长度达net_buffer_length的行。如果增加该变量，还应确保在MySQL服务器中的net_buffer_length变量至少这么大。

二、mysqlbinlog

1、关于binlog日志

1）、启用binlog日志记录用户连接数据库后执行的除查询以外的SQL语句，binlog日志默认没有开启

2）、Binlog日志（二进制日志）记录客户端连接数据库后执行的除查询之外的sql语句，默认没有开启

3）、开启binlog日志：

Vim /etc/my.cnf

[ mysqld]

log_bin

:wq

4）、Binlog日志文件默认存储在数据库库目录下，日志名默认：主机名-bin.000001，大于500M时生成主机名-bin000002，依次类推，新的sql语句写在文件名编号最大的文件中。也有时默认命名为进程名（mysqld）.bin.000001,

5）、svr6-bin.index:binlog日志的索引文件，记录当前已有的binlog日志文件名

6）、mysqlbinlog命令格式：

#mysqlbinlog [选项]   binlog日志名

mysqlbinlog支持下面的选项：

—help，-？显示帮助消息并退出。

—database=db_name，-d db_name 只列出该数据库的条目(只用本地日志)。

–force-read，-f 使用该选项，如果mysqlbinlog读它不能识别的二进制日志事件，它会打印警告，忽略该事件并继续。没有该选项，如果mysqlbinlog读到此类事件则停止。

–hexdump，-H 在注释中显示日志的十六进制转储。该输出可以帮助复制过程中的调试。在MySQL 5.1.2中添加了该选项。

–host=host_name，-h host_name  获取给定主机上的MySQL服务器的二进制日志。

–local-load=path，-l pat  为指定目录中的LOAD DATA INFILE预处理本地临时文件。

–offset=N，-o N   跳过前N个条目。

–password[=password]，-p[password]  当连接服务器时使用的密码。如果使用短选项形式(-p)，选项和 密码之间不能有空格。如果在命令行中–password或-p选项后面没有密码值，则提示输入一个密码。
–port=port_num，-P port_num  用于连接远程服务器的TCP/IP端口号。

–position=N，-j N  不赞成使用，应使用–start-position。

–protocol={TCP | SOCKET | PIPE |-position 使用的连接协议。

–read-from-remote-server，-R 从MySQL服务器读二进制日志。如果未给出该选项，任何连接参数选项将被忽略。这些选项是–host、–password、–port、–protocol、–socket和–user。

–result-file=name, -r name  将输出指向给定的文件。

–short-form，-s 只显示日志中包含的语句，不显示其它信息。

–socket=path，-S path  用于连接的套接字文件。

–start-datetime=datetime   从二进制日志中第1个日期时间等于或晚于datetime参量的事件开始读取。datetime值相对于运行mysqlbinlog的机器上的本地时区。该值格式应符合DATETIME或TIMESTAMP数据类型。例如：shell> mysqlbinlog–start-datetime=”2004-12-25 11:25:56″ binlog.000003该选项可以帮助点对点恢复。

–stop-datetime=datetime  从二进制日志中第1个日期时间等于或晚于datetime参量的事件起停止读。关于datetime值的描述参见–start-datetime选项。该选项可以帮助及时恢复。

–start-position=N  从二进制日志中第1个位置等于N参量时的事件开始读。

–stop-position=N   从二进制日志中第1个位置等于和大于N参量时的事件起停止读。

–to-last-logs，-t  在MySQL服务器中请求的二进制日志的结尾处不停止，而是继续打印直到最后一个二进制日志的结尾。如果将输出发送给同一台MySQL服务器，会导致无限循环。该选项要求–read-from-remote-server。

–disable-logs-bin，-D 禁用二进制日志。如果使用–to-last-logs选项将输出发送给同一台MySQL服务器，可以避免无限循环。该选项在崩溃恢复时也很有用，可以避免复制已经记录的语句。注释：该选项要求有SUPER权限。

–user=user_name，-u user_name  连接远程服务器时使用的MySQL用户名。

–version，-V 显示版本信息并退出。