Memory mysql 教程_mysql memory存储引擎简单测试

Auth: jin

Date: 20140423

mysql> CREATE TABLE `t4` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(25) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MEMORY;

Query OK, 0 rows affected (0.02 sec)

mysql> insert into t4(name) value('diege1');

Query OK, 1 row affected (0.00 sec)

mysql> insert into t4(name) value('diege');

Query OK, 1 row affected (0.00 sec)

mysql> select * from t4;

+----+--------+

| id | name |

+----+--------+

| 1 | diege1 |

| 2 | diege |

+----+--------+

2 rows in set (0.00 sec)

binlog有记录创建表结构的

# mysqlbinlog /var/lib/mysql/mysql-bin.000001 |grep 'CREATE TABLE `t4`'

CREATE TABLE `t4` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(25) NOT NULL, PRIMARY KEY (`id`), UNIQUE KEY `name` (`name`) ) ENGINE=MEMORY

查看slave数据

mysql> select * from t4;

+----+--------+

| id | name |

+----+--------+

| 1 | diege |

| 2 | diege1 |

+----+--------+

2 rows in set (0.00 sec

可见不见记录了create table 还记录了insert，所以binlog和存储引擎无关

重启mysql后查看数据

# /etc/init.d/mysqld restart

mysql> select * from t4;

Empty set (0.00 sec)

没有数据，数据保留在内存中

问题

在master-salve结构中尽量不要使用memory引擎，

当master重启后，master和slave数据不一致了，前端查询落到master和slave结果不一样，除master设置不容许查询，只写入数据。

当有多个slave时 有slave重启过时，前端查询也不一致。所以，不要在master-slave结构中使用memory存储引擎简