mysql - 跟踪 MySQL 更改历史记录的简单方法？

Question

我想在我的 MySQL 数据库中记录所有更改(插入/更新/删除)。

我可能还希望此记录易于执行，即如果我选择执行所有记录的语句，它可以以某种方式用作备份工具。

我的理解是，MySQL 的日志格式可能包含不必要的内容，我需要额外的处理才能使语句批量可执行。

创建一个简单的文本文件并将 SQL 语句写入其中是个好主意吗？或者 MySQL 中是否有一些 native 功能已经支持这个？或者这是我可以利用其他软件(如 Redis)来创建更高效​​解决方案的一种方式吗？

Answer 1

"My understanding is, the logs format by MySQL would probably contain unnecessary stuff and I would need extra processing to make the statements bulk executable."

当您谈论 MySQL General Log 时，这是有道理的。

使用MySQL Bin Log，您不需要转换日志格式。

##先查看binlog_format##

有3种binlog_format。 http://dev.mysql.com/doc/refman/5.5/en/binary-log-setting.html

使用基于 STATEMENT 的二进制日志，您可以获得 INSERT/DELETE/UPDATE/etc 语句

mysql> SHOW VARIABLES LIKE 'binlog_format';
+---------------+-----------+
| Variable_name | Value     |
+---------------+-----------+
| binlog_format | STATEMENT |
+---------------+-----------+
1 row in set (0.00 sec)

##GET UPDATE 语句使用 mysqlbinlog##

通过mysqlbinlog和mysql二进制日志文件，可以得到更新语句如下:

$ mysqlbinlog mysql-bin.000002
# at 2112
#131223 19:48:48 server id 1  end_log_pos 2233  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1387795728/*!*/;
DELETE ... /* generated by server */
/*!*/;
# at 2233
#131223 19:48:48 server id 1  end_log_pos 2352  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1387795728/*!*/;
INSERT .... /j* generated by server */
/*!*/;
# at 2352
#131223 19:48:48 server id 1  end_log_pos 2468  Query   thread_id=1     exec_time=0     error_code=0
SET TIMESTAMP=1387795728/*!*/;
DROP  /* generated by server */

##mysqlbinlog 的选项##

这里有一些关于如何获取特定日期(或位置)日志的选项

  --start-datetime=name
                      Start reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).
  -j, --start-position=#
                      Start reading the binlog at position N. Applies to the
                      first binlog passed on the command line.
  --stop-datetime=name
                      Stop reading the binlog at first event having a datetime
                      equal or posterior to the argument; the argument must be
                      a date and time in the local time zone, in any format
                      accepted by the MySQL server for DATETIME and TIMESTAMP
                      types, for example: 2004-12-25 11:25:56 (you should
                      probably use quotes for your shell to set it properly).
  --stop-position=#   Stop reading the binlog at position N. Applies to the
                      last binlog passed on the command line.