mysql複製和記憶體引擎的表
清晰的格式：
https://zhuanlan.zhihu.com/p/26985377
When a master server shuts down and restarts, its MEMORY tables become empty. To replicate this effect to slaves, the first time that the master uses a given MEMORY table after startup, it logs an event that notifies slaves that the table must to be emptied by writing a DELETE statement for that table to the binary log.
When a slave server shuts down and restarts, its MEMORY tables become empty. This causes the slave to be out of synchrony with the master and may lead to other failures or cause the slave to stop:
-
Row-format updates and deletes received from the master may fail with Can't find record in 'memory_table'.
-
Statements such as INSERT INTO ... SELECT FROM memory_table may insert a different set of rows on the master and slave.
當主庫上使用記憶體引擎的表時，主庫因為某些原因重啟了。啟動好之後，再開啟主庫的記憶體引擎表時，會先把裡面的內容清空。所以這個時候主從狀態是正常的。
但當從庫被重啟時，因為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 /*
4360 As replication is maybe going to be corrupted, we need to warn the
4361 DBA on top of warning the client (which will automatically be done
4362 because of MYF(MY_WME) in my_malloc() above).
4363 */
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引擎的表。真要用的話，需要了解他的影響。
來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/25099483/viewspace-2139311/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- mysql最大表記憶體MySql記憶體
- 深入理解 Python 的物件複製和記憶體佈局Python物件記憶體
- 物件的生存期 記憶體 深度複製 複製建構函式 筆記物件記憶體函式筆記
- mysql複製表結構和資料MySql
- Welcome to MySQL Workbench:MySQL 複製表MySql
- mysql 如何複製表結構和資料MySql
- MySQL-37：記憶體臨時表MySql記憶體
- MySQL->複製表[20180509]MySql
- mysql 資料表的複製案例MySql
- MySQL記憶體管理，記憶體分配器和作業系統MySql記憶體作業系統
- openGauss儲存技術（二）——列儲存引擎和記憶體引擎儲存引擎記憶體
- mysql完全複製一個表（結構和資料）MySql
- optee記憶體管理和頁表建立記憶體
- MySQL記憶體管理MySql記憶體
- 開心檔之MySQL 複製表MySql
- delphi記憶體表記憶體
- 記憶體表（FDMEMTABLE）記憶體
- 比memcpy還要快的記憶體複製，老哥瞭解一下？memcpy記憶體
- linux記憶體管理（一）實體記憶體的組織和記憶體分配Linux記憶體
- MySQL複習筆記(05)：MySQL表級鎖和行級鎖MySql筆記
- mysql主從複製的理解和搭建MySql
- Windows 環境下，MySQL 的主從複製和主主複製WindowsMySql
- windows環境下，Mysql的主從複製和主主複製WindowsMySql
- mysql複製中臨時表的運用技巧MySql
- mysql中複製表結構的方法小結MySql
- MYSQL的記憶體管理方法MySql記憶體
- MySQL InnoDB記憶體配置MySql記憶體
- MySQL探祕(三):InnoDB的記憶體結構和特性MySql記憶體
- MySQL複製表結構和內容到另一張表中的SQL語句MySql
- MySQL innodb表使用表空間物理檔案複製表MySql
- mysql資料庫的主從複製和主主複製實踐MySql資料庫
- NodeJS V8引擎的記憶體和垃圾回收器（GC）NodeJS記憶體GC
- 複製資訊記錄表|全方位認識 mysql 系統庫MySql
- 什麼是Java記憶體模型(JMM)中的主記憶體和本地記憶體？Java記憶體模型
- NIO的JVM記憶體和機器記憶體的選擇JVM記憶體
- Java記憶體區域和記憶體模型Java記憶體模型
- 直接記憶體和堆記憶體誰快記憶體
- 記憶體溢位和記憶體洩露記憶體溢位記憶體洩露
- MySQL複製MySql