临时表
临时表是指使用create temprary table创建的表.临时表可以使用任何存储引擎,临时表只在单个连接中可见,当连接断开时,临时表也会消失.MySQL最初会将临时表创建在内存中,当数据变的太大后,就会转储到磁盘上.
内存表
内存表是指用memory引擎创建的表.表结构存在于磁盘,数据放在内存中.以哈希表的结构存储,重启之后数据会清空,内存表满后,会提示数据满错误.
数据库版本
[root@master ~]# mysql -V mysql Ver 14.14 Distrib 5.1.73, for redhat-linux-gnu (x86_64) using readline 5.1
测试
注:测试时主从复制关联正常
1.测试临时表支持
a.主服务器创建临时表
mysql> create temporary table test1(id int(11) not null); Query OK, 0 rows affected (0.01 sec) mysql> insert into test1(id) values(1); Query OK, 1 row affected (0.00 sec) mysql> select * from test1; +----+ | id | +----+ | 1 | +----+ 1 row in set (0.00 sec) mysql>
b.从服务器查看临时表
mysql> select * from test1; ERROR 1146 (42S02): Table 'test.test1' doesn't exist
c.主服务器删除临时表,测试主从复制是否正常
mysql> drop table test1; Query OK, 0 rows affected (0.00 sec) mysql> select * from `000`; +-----+------+ | id | name | +-----+------+ | 127 | 0 | +-----+------+ 1 row in set (0.00 sec) mysql> INSERT INTO `mydb`.`000` (`id` ,`name`)VALUES (NULL , '1'); Query OK, 1 row affected (0.00 sec)
d.从服务器查看主从复制正常
mysql> select * from `000`; +-----+------+ | id | name | +-----+------+ | 129 | 1 | | 127 | 0 | +-----+------+ 2 rows in set (0.00 sec)
总结:主从同步不支持临时表,但也不影响主从关联
2.测试内存表支持
a.主服务器创建内存表
mysql> CREATE TABLE test2 (id int(11) not null, name varchar(200) ) ENGINE=MEMORY ; Query OK, 0 rows affected (0.01 sec) mysql> INSERT INTO `mydb`.`test2` (`id` ,`name`)VALUES ('0', '0'); Query OK, 1 row affected (0.00 sec) mysql> select * from test2; +----+------+ | id | name | +----+------+ | 0 | 0 | +----+------+ 1 row in set (0.00 sec)
b.从服务器查看内存表
mysql> select * from test2; +----+------+ | id | name | +----+------+ | 0 | 0 | +----+------+ 1 row in set (0.00 sec)
c.主服务器删除内存表,测试主从复制是否正常
mysql> drop table test2; Query OK, 0 rows affected (0.00 sec) mysql> select * from `000`; +-----+------+ | id | name | +-----+------+ | 129 | 1 | | 127 | 0 | +-----+------+ 2 rows in set (0.00 sec) mysql> INSERT INTO `mydb`.`000` (`id` ,`name`)VALUES (NULL , '2'); Query OK, 1 row affected (0.00 sec)
d.从服务器查看主从复制正常
mysql> select * from test2; ERROR 1146 (42S02): Table 'mydb.test2' doesn't exist mysql> select * from `000`; +-----+------+ | id | name | +-----+------+ | 133 | 2 | | 129 | 1 | | 127 | 0 | +-----+------+ 3 rows in set (0.00 sec)
总结:主从同步支持内存表,主从关联正常
参考来源:http://blog.chinaunix.net/uid-20539097-id-80425.html
本文链接:https://jeff.xin/post/36.html
--EOF--
Comments
发表评论:
◎欢迎参与讨论,请在这里发表您的看法、交流您的观点。