mysql配置的种种疑问

linjia828

原帖由 ban_ping 于 2011-5-18 11:37 发表
建议先搞清二进制日志和重做日志的区别

谢谢楼上。
二进制日志会记录所有与MySQL有关的日志记录，包括InnoDB、MyISAM等其他存储引擎的日志。
重做日志只记录InnoDB有关其本身的事务日志。
且两者记录的内容也是不一样的。

经过试验发现在myisam引擎下是执行语句后同时写入二进制日志，而且不存在事务提交的概念，因此在执行一个大的insert操作中止的话依然有插入部分数据是正常的。
在Innodb引擎下，执行完语句在不提交的情况下，已经更新了重做日志，但是未写入二进制日志。只有当提交以后才会写入二进制日志。而且提交是以一个语句为最小事务单位，所以我不明白的是为什么两个数据库，同样是innodb的引擎，同样aotocommit=on的情况下，执行一个大的insert操作然后中止。有一个是没有数据，也就是回滚了，有一个却依然插入了部分数据。所以我在想是不是又有其他什么参数起来作用？？？

Steven1981

对于第一点：
　　BINLOG 必须在ＣＯＭＭＩＴ返回前写完。 而不是commit后写BINLOG。

Steven1981

另外，你的测试引擎是MYISAM，即时提交；

如果楼主想要达到想像中的效果，请用INNODB引擎来测试

linjia828

原帖由 Steven1981 于 2011-5-18 22:20 发表
对于第一点：
　　BINLOG 必须在ＣＯＭＭＩＴ返回前写完。 而不是commit后写BINLOG。

你错了，是在提交后才写入二进制日志的。请看下面实验：

mysql> show create table T2;
+-------+--------------------------------------------------------------------------------------+
| Table | Create Table                                                                         |
+-------+--------------------------------------------------------------------------------------+
| T2    | CREATE TABLE `T2` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-------+--------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000056 |     1611 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> insert into  T2 values (1);
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000056 |     1611 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000056 |     1793 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

devilkin0312

你测试过在innodb下，执行一个大的insert，出现的部分数据的情况么？

ban_ping

innodb，没有提交不可能有数据的。
另外，二进制日志是先写buffer再写磁盘的，buffer大小由binlog_cache_size决定。
sync_binlog参数会控制二进制日志的写入磁盘，默认为0.
如果设置为1，则写入buffer后会马上同步写入磁盘，这时候事务有可能还未提交。

linjia828

原帖由 ban_ping 于 2011-5-20 11:22 发表
innodb，没有提交不可能有数据的。
另外，二进制日志是先写buffer再写磁盘的，buffer大小由binlog_cache_size决定。
sync_binlog参数会控制二进制日志的写入磁盘，默认为0.
如果设置为1，则写入buffer后会马上同步写入磁盘，这时候事务有可能还未提交。

但是在Innodb下我怎么测试都是只有提交完才会写二进制日志，不提交的话二进制日志是不会写入的，而不是缓存写入磁盘的关系。

mysql> show create table T2;
+-------+------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table                                                                                                           |
+-------+------------------------------------------------------------------------------------------------------------------------+
| T2    | CREATE TABLE `T2` (
  `id` int(11) DEFAULT NULL,
  `name` char(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> set GLOBAL sync_binlog=0;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000067 |     1057 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> insert into  T2 values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000067 |     1057 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000067 |     1247 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> set GLOBAL sync_binlog=1;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000067 |     1247 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

mysql> insert into  T2 values (1,1);
Query OK, 1 row affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000067 |     1247 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.01 sec)

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000067 |     1437 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

ban_ping

原帖由 linjia828 于 2011-5-23 14:33 发表


但是在Innodb下我怎么测试都是只有提交完才会写二进制日志，不提交的话二进制日志是不会写入的，而不是缓存写入磁盘的关系。

是提交事务触发的写二进制日志没错，
这里的写二进制日志实际是写缓冲，
写二进制日志缓冲和提交事务也是有先后顺序的，是写完二进制日志缓冲再提交事务的。
也就是说有可能写了二进制缓冲，但是事务提交未完成的时候发生崩溃。
这时的情况就是：
二进制日志缓冲中有该事务的数据，redo中肯定也有，但是这个事务没有提交成功。
如果sync_binlog是1，那么这时会导致二进制日志缓冲中的数据写入到磁盘，也就是实际的binlog文件。
而对binlog物理文件的写入，一般就是不会更改的.
那么重启系统后，这个未提交的事务会回滚掉，
这时只有打开了innodb_support_xa参数，才会去同时修改掉binlog中未提交的事务，
否则，binlog是会出现错误数据的。

你测试的过程反映不了事情的经过，因为commit提交最终失败的场景不大好模拟出来。

linjia828

原帖由 ban_ping 于 2011-5-25 14:00 发表


是提交事务触发的写二进制日志没错，
这里的写二进制日志实际是写缓冲，
写二进制日志缓冲和提交事务也是有先后顺序的，是写完二进制日志缓冲再提交事务的。
也就是说有可能写了二进制缓冲，但是事务提交未完成的时候发生崩溃。
这时的情况就是：
二进制日志缓冲中有该事务的数据，redo中肯定也有，但是这个事务没有提交成功。
如果sync_binlog是1，那么这时会导致二进制日志缓冲中的数据写入到磁盘，也就是实际的binlog文件。
而对binlog物理文件的写入，一般就是不会更改的.
那么重启系统后，这个未提交的事务会回滚掉，
这时只有打开了innodb_support_xa参数，才会去同时修改掉binlog中未提交的事务，
否则，binlog是会出现错误数据的。

你测试的过程反映不了事情的经过，因为commit提交最终失败的场景不大好模拟出来。

我想我知道你的意思了，就是说提交事务这个动作会触发写入二进制日志，但是可能会出现提交失败但是又已经出现写入二进制的情况。
其实我当时就只是想求证写二进制日志的这个动作是不是在发出commit指令以后....

另show master status出来的pos是缓冲里面的还是已经写入磁盘的？这个还真不好测试.....

[ 本帖最后由 linjia828 于 2011-5-25 14:41 编辑 ]