mysql复制和内存引擎的表是怎么样的，针对这个问题，这篇文章详细介绍了相对应的分析和解答，希望可以帮助更多想解决这个问题的小伙伴找到更简单易行的方法。
当主服务器关闭并重新启动时，其内存表将变为空。要将此效果复制到从属服务器，在主服务器启动后第一次使用给定内存表时，它会记录一个事件，通知从属服务器必须通过将该表的DELETE语句写入二进制日志来清空该表。
当从属服务器关闭并重新启动时，其内存表将变为空。这会导致从属设备与主设备不同步，并可能导致其他故障或导致从属设备停止：
从主机接收的行格式更新和删除可能会失败，因为在“内存表”中找不到记录。
语句，例如INSERT INTO。。。从内存中选择\u表可以在主设备和从设备上插入一组不同的行。
当主库上使用内存引擎的表时，主库因为某些原因重启了。启动好之后，再打开主库的内存引擎表时，会先把里面的内容清空。所以这个时候主从状态是正常的。
但当从库被重启时，因为memory的内容没有了，所以可能会出现主从报错的现象。
实验：
一 主库异常关闭：
在主库上建表
mysql> show create table b;
+-------+-------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+-------+-------------------------------------------------------------------------------------------------------+
| b | CREATE TABLE `b` (
`id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MEMORY DEFAULT CHARSET=utf8
主库：
mysql> insert into b values (10),(20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
从库：
mysql> select * from a.b;
+----+
| id |
+----+
| 10 |
| 20 |
+----+
重启主库后：
从库：
mysql> select * from a.b;
+----+
| id |
+----+
| 10 |
| 20 |
+----+
2 rows in set (0.00 sec)
这时再去主库上观察：
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000012 | 194 | | | 48dfe7f5-3ab5-11e7-b3fa-525400199b09:1-23 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> select *from a.b;
Empty set (0.00 sec)
mysql> show master status;
+------------------+----------+--------------+------------------+-------------------------------------------+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------------------------------+
| mysql-bin.000012 | 478 | | | 48dfe7f5-3ab5-11e7-b3fa-525400199b09:1-24 |
+------------------+----------+--------------+------------------+-------------------------------------------+
1 row in set (0.00 sec)
mysql> show binlog events in 'mysql-bin.000012';
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| Log_name | Pos | Event_type | Server_id | End_log_pos | Info |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
| mysql-bin.000012 | 4 | Format_desc | 169454186 | 123 | Server ver: 5.7.16.k1-ucloudrel1-log, Binlog ver: 4 |
| mysql-bin.000012 | 123 | Previous_gtids | 169454186 | 194 | 48dfe7f5-3ab5-11e7-b3fa-525400199b09:1-23 |
| mysql-bin.000012 | 194 | Gtid | 169454186 | 259 | SET @@SESSION.GTID_NEXT= '48dfe7f5-3ab5-11e7-b3fa-525400199b09:24' |
| mysql-bin.000012 | 259 | Query | 169454186 | 327 | BEGIN |
| mysql-bin.000012 | 327 | Query | 169454186 | 409 | DELETE FROM `a`.`b` |
| mysql-bin.000012 | 409 | Query | 169454186 | 478 | COMMIT |
+------------------+-----+----------------+-----------+-------------+--------------------------------------------------------------------+
6 rows in set (0.00 sec)
再次去从库观察：
mysql> select * from a.b;
Empty set (0.00 sec)
这时主从的状态也是正常的。
二 从库启常重启：
主库上：
mysql> insert into a.b values (10),(20);
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
从库：
mysql> select * from a.b;
+----+
| id |
+----+
| 10 |
| 20 |
+----+
2 rows in set (0.00 sec)
重启从库后：
mysql> select *from a.b;
Empty set (10.00 sec)
主库：
mysql> delete from a.b where id=10;
Query OK, 1 row affected (0.00 sec)
从库：
mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 10.25.170.106
Master_User: ucloudbackup
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.000012
Read_Master_Log_Pos: 1049
Relay_Log_File: mysql-relay.000024
Relay_Log_Pos: 360
Relay_Master_Log_File: mysql-bin.000012
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1032
Last_Error: Coordinator stopped because there were error(s) in the worker(s). The most recent failure being: Worker 0 failed executing transaction '48dfe7f5-3ab5-11e7-b3fa-525400199b09:26' at master log mysql-bin.000012, end_log_pos 980. See error log and/or performance_schema.replication_applier_status_by_worker table for more details about this failure or others, if any.
Skip_Counter: 0
分析：
重启主库，并且访问a.b表时， mysqlbinlog中会记录下面的内容：
# at 327
#170518 19:16:07 server id 10 end_log_pos 410 CRC32 0xc908da54 Query thread_id=5 exec_time=499 error_code=0
SET TIMESTAMP=1495106167;
DELETE FROM `a`.`b`
;
通过跟踪可知：
#0 open_table_entry_fini (thd=0x7f3f1c000d80, share=0x7f3f14045b50, entry=0x7f3f1c00eef0) at /data/mysql-5.7.17/sql/sql_base.cc:4325
#1 0x00000000014968ac in open_table (thd=0x7f3f1c000d80, table_list=0x7f3f1c006580, ot_ctx=0x7f3f40092370) at /data/mysql-5.7.17/sql/sql_base.cc:3551
#2 0x000000000149912c in open_and_process_table (thd=0x7f3f1c000d80, lex=0x7f3f1c003078, tables=0x7f3f1c006580, counter=0x7f3f1c003138, flags=0, prelocking_strategy=0x7f3f40092470,
has_prelocking_list=false, ot_ctx=0x7f3f40092370) at /data/mysql-5.7.17/sql/sql_base.cc:5108
#3 0x000000000149a1ce in open_tables (thd=0x7f3f1c000d80, start=0x7f3f40092430, counter=0x7f3f1c003138, flags=0, prelocking_strategy=0x7f3f40092470) at /data/mysql-5.7.17/sql/sql_base.cc:5719
#4 0x000000000149b4fb in open_tables_for_query (thd=0x7f3f1c000d80, tables=0x7f3f1c006580, flags=0) at /data/mysql-5.7.17/sql/sql_base.cc:6494
#5 0x00000000015208fe in execute_sqlcom_select (thd=0x7f3f1c000d80, all_tables=0x7f3f1c006580) at /data/mysql-5.7.17/sql/sql_parse.cc:5166
#6 0x000000000151a193 in mysql_execute_command (thd=0x7f3f1c000d80, first_level=true) at /data/mysql-5.7.17/sql/sql_parse.cc:2794
#7 0x00000000015218f6 in mysql_parse (thd=0x7f3f1c000d80, parser_state=0x7f3f40093690) at /data/mysql-5.7.17/sql/sql_parse.cc:5611
#8 0x000000000151709c in dispatch_command (thd=0x7f3f1c000d80, com_data=0x7f3f40093df0, command=COM_QUERY) at /data/mysql-5.7.17/sql/sql_parse.cc:1461
#9 0x0000000001515f8e in do_command (thd=0x7f3f1c000d80) at /data/mysql-5.7.17/sql/sql_parse.cc:999
#10 0x0000000001645460 in handle_connection (arg=0x43de0e0) at /data/mysql-5.7.17/sql/conn_handler/connection_handler_per_thread.cc:300
#11 0x0000000001cbe494 in pfs_spawn_thread (arg=0x4474250) at /data/mysql-5.7.17/storage/perfschema/pfs.cc:2188
#12 0x00007f3f4e629dc5 in start_thread () from /lib64/libpthread.so.0
#13 0x00007f3f4d4f473d in clone () from /lib64/libc.so.6
是在sql/sql_base.cc:open_table_entry_fini函数中实现的添加delete
具体是体现在：
4345 if (mysql_bin_log.is_open())
4346 {
4347 bool error= false;
4348 String temp_buf;
4349 error= temp_buf.append("DELETE FROM ");
4350 append_identifier(thd, &temp_buf, share->db.str, strlen(share->db.str));
4351 error= temp_buf.append(".");
4352 append_identifier(thd, &temp_buf, share->table_name.str,
4353 strlen(share->table_name.str));
4354 if (mysql_bin_log.write_dml_directly(thd, temp_buf.c_ptr_safe(),
4355 temp_buf.length()))
4356 return TRUE;
4357 if (error)
4358 {
4359
4364 sql_print_error("When opening HEAP table, could not allocate memory "
4365 "to write 'DELETE FROM `%s`.`%s`' to the binary log",
4366 share->db.str, share->table_name.str);
4367 delete entry->triggers;
4368 return TRUE;
4369 }
直接在mysqlbinlog中写了delete 语句。
对于有memory引擎的表，做主从时可能会有的问题：
1 逻辑备份：会dump出来当时memory表里有的内容，但他不支持事务，所以有可能数据是不一样的
2 物理备份：memory的表只有frm文件，所以里面的内容一定是空的
所以两种方式都不能保证可以直接做出来从库。
最好的方法是：在主从复制中还是不要使用memory引擎的表。真要用的话，需要了解他的影响。
关于mysql复制和内存引擎的表是怎么样的问题的解答就分享到这里了，希望以上内容可以对大家有一定的帮助，如果你还有很多疑惑没有解开，可以关注亿速云行业资讯频道了解更多相关知识。
