mysql binlog 常用技巧

mysql binlog 常用技巧

    要想通过日志恢复数据库，在你的my.cnf文件里应该有如下的定义，log-bin=mysql-bin，这个是必须的.binlog-do-db=db_test,这个是指定哪些数据库需要日志，
如果有多个数据库就每行一个，如果不指定的话默认就是所有数据库.

1.开启二进制日志
[mysqld]
log-bin=mysql-bin
binlog-do-db=db_test
binlog-do-db=db_test2

2.删除二进制日志:

    (1). reset master   (清空所有的二进制日志文件)
    (2). purge master logs to 'bintest.000006'; (删除bintest.000006之前的二进制日志文件)
    (3). purge master logs before '2007-08-10 04:07:00'  (删除该日期之前的日志)
    (4). 在my.cnf 配置文件中[mysqld]中添加:expire_logs_day=30  设置日志的过期天数,过了指定的天数,会自动删除.

特别提示，mysql每次启动都会重新生成一个新的日志文件：例如：mysql-bin.000003.

技巧1 :
mysqlbinlog –stop-date='2005-04-20 9:59:59' /var/log/mysql/mysql-bin.000001  | mysql -u root -pmypwd 
这样只能指定一个日志文件，那能不能指定多个呢？

mysqlbinlog --stop-date='2005-04-20 9:59:59'  var/log/mysql/mysql-bin.0* | mysql -u root -pmypwd

当然你也可以用这种写法更完美:
mysqlbinlog –stop-date=”2005-04-20 9:59:59″ /var/log/mysql/mysql-bin.[0-9]* | mysql -u root -pmypwd
看到[0-9]*这个东东了吧，它表示以数字开头的任何字符.

技巧2:
你可以通过–one-database 参数选择性的恢复单个数据库，example在下面，爽吧。
mysqlbinlog --stop-date='2005-04-20 9:59:59' /var/log/mysql/mysql-bin.000001  | mysql -u root -pmypwd –one-database db_test

mysql> reset master;   //删除master的binlog
mysql> reset slave;    //删除slave的中继日志
mysql> reset slave all;     //清除slave复制信息：
mysql> purge master logs before '2012-03-30 17:20:00';  //删除指定日期以前的日志索引中binlog日志文件
mysql> purge master logs to 'binlog.000002';   //删除指定日志文件的日志索引中binlog日志文件

mysql> set sql_log_bin=1/0; //如果用户有super权限，可以启用或禁用当前会话的binlog记录
mysql> show master logs; //查看master的binlog日志
mysql> show binary logs; //查看master的binlog日志
mysql> show master status; //用于提供master二进制日志文件的状态信息
mysql> show slave hosts; //显示当前注册的slave的列表。不以--report-host=slave_name选项为开头的slave不会显示在本列表中

 

技巧3：

最好不要这样恢复：
shell> mysqlbinlog binlog.000001 | mysql -u root -p
shell> mysqlbinlog binlog.000002 | mysql -u root -p
处理二进制日志使用的服务器不同的连接这样可能会导致问题，如果第一个日志文件包含一个CREATE TEMPORARY TABLE语句和第二日志包含使用临时表的语句。
当第一个mysql进程终止时，服务器会删除临时表。当第二个mysql进程尝试使用该表时，服务器报告“unknown table”。
为了避免这样的问题，使用一个连接来执行想要处理的所有二进制日志中的内容。这里就是这样做的一种方法：

shell> mysqlbinlog binlog.000001 binlog.000002 | mysql -u root -p

另一种方法是写的所有日志到一个文件中，然后处理文件：
shell> mysqlbinlog binlog.000001 >  /tmp/statements.sql
shell> mysqlbinlog binlog.000002 >> /tmp/statements.sql
shell> mysql -u root -p -e "source /tmp/statements.sql"

 

几个重要的参数参数直接影响mysql的性能和完整性
sync_binlog=0：
当事务提交后，Mysql仅仅是将binlog_cache中的数据写入Binlog文件，但不执行fsync之类的磁盘      同步指令通知文件系统将缓存刷新到磁盘，而让Filesystem自行决定什么时候来做同步，这个是性能最好的。
sync_binlog=n，在进行n次事务提交以后，Mysql将执行一次fsync之类的磁盘同步指令，同志文件系统将Binlog文件缓存刷新到磁盘。
Mysql中默认的设置是sync_binlog=0，即不作任何强制性的磁盘刷新指令，这时性能是最好的，但风险也是最大的。一旦系统绷Crash，在文件系统缓存中的所有Binlog信息都会丢失

max_binlog_cache_size
此参数表示binlog使用的内存最大的尺寸

binlog_cache_size
此参数表示binlog使用的内存大小，可以通过状态变量binlog_cache_use和binlog_cache_disk_use来帮助测试。

binlog_cache_use：使用二进制日志缓存的事务数量
binlog_cache_disk_use:使用二进制日志缓存但超过binlog_cache_size值并使用临时文件来保存事务中的语句的事务数量

max_binlog_size
Binlog最大值，最大和默认值是1GB，该设置并不能严格控制Binlog的大小，尤其是Binlog比较靠近最大值而又遇到一个比较大事务时，为了保证事务的完整性，
不可能做切换日志的动作，只能将该事务的所有SQL都记录进当前日志，直到事务结束.

日志是否开启gtid。
#gtid_mode = on
#gtid强制一致性校验
#enforce_gtid_consistency = 1
崩溃恢复
binlog_gtid_simple_recovery=1

#binlog中记录事物SQL
binlog_rows_query_log_events
日志格式，默认都采用row。
binlog_format = row

binlog-do-db：该binlog-do-db=test参数表示的是，二进制日志记录test库。
binlog-ignore-db：该binlog-ignore-db=test，表示忽略test库，不写入二进制日志。

二进制日志格式比较：

1.Statement：每一条会修改数据的sql都会记录在binlog中。
优点：不需要记录每一行的变化，减少了binlog日志量，节约了IO，提高性能。(相比row能节约多少性能与日志量，这个取决于应用的SQL情况，正常同一条
  记录修改或者插入row格式所产生的日志量还小于Statement产生的日志量，但是考虑到如果带条件的update操作，以及整表删除，alter表等操作，
  ROW格式会产生大量日志，因此在考虑是否使用ROW格式日志时应该跟据应用的实际情况，其所产生的日志量会增加多少，以及带来的IO性能问题。)
缺点：由于记录的只是执行语句，为了这些语句能在slave上正确运行，因此还必须记录每条语句在执行的时候的一些相关信息，以保证所有语句能在slave得到
  和在master端执行时候相同的结果。另外mysql 的复制,像一些特定函数功能，slave可与master上要保持一致会有很多相关问题
  (如sleep()函数， last_insert_id()，以及user-defined functions(udf)会出现问题)。
还有一点，statement只记录了SQL语句，我们通过日志也只能看到他执行的SQL，但无法获知SQL修改之前的数据。

使用以下函数的语句也无法被复制：
  LOAD_FILE()、 UUID()、 USER()、 FOUND_ROWS()、 SYSDATE() ,now()(除非启动时启用了 --sysdate-is-now 选项)
  同时在INSERT ...SELECT 会产生比 RBR 更多的行级锁。

2.Row:不记录sql语句上下文相关信息，仅保存哪条记录被修改。此时可以将InnoDB的事务隔离基本设为READ COMMITTED，以获得更好的并发性。

优点： binlog中可以不记录执行的sql语句的上下文相关的信息，仅需要记录那一条记录被修改成什么了。所以rowlevel的日志内容会非常清楚的记录下每一行 数据修改的细节。而且不会出现某些特定情况下的存储过程，或function，以及trigger的调用和触发无法被正确复制的问题。

缺点:所有的执行的语句当记录到日志中的时候，都将以每行记录的修改来记录，这样可能会产生大量的日志内容,比如一条update语句，修改多条记录，  则binlog中每一条修改都会有记录，这样造成binlog日志量会很大，新版本的MySQL中对row level模式也被做了优化，并不是所有的修改都会以row来记录，  像遇到表结构变更的时候就会以statement模式来记录。

至于update或者delete等修改数据的语句，还是会记录所有行的变更。

3.Mixed: 是以上两种level的混合使用，一般的语句修改使用statment格式保存binlog，如一些函数，statement无法完成主从复制的操作，  则采用row格式保存binlog,MySQL会根据执行的每一条具体的sql语句来区分对待记录的日志形式，也就是在Statement和Row之间选择一种。