Shulou(Shulou.com)05/31 Report--

What is the table of mysql replication and memory engine? in order to solve this problem, this article introduces the corresponding analysis and solution in detail, hoping to help more partners who want to solve this problem to find a more simple and easy way.

When the primary server shuts down and restarts, its memory table becomes empty. To copy this effect to the secondary server, the first time a given memory table is used after the primary server starts, it logs an event informing the secondary server that it must empty the table by writing the DELETE statement to the binary log.

When the secondary server shuts down and restarts, its memory table becomes empty. This can cause the slave device to be out of sync with the master device and may cause other failures or cause the slave device to stop:

Row format updates and deletions received from the host may fail because the record cannot be found in the memory Table.

Statement, such as INSERT INTO. Select the\ u table from memory to insert a different set of rows on the master and slave devices.

When the tables of the memory engine are used on the main library, the main library restarts for some reason. After startup, when you open the memory engine table of the main library, the contents will be cleared first. So the master-slave state is normal at this time.

However, when the slave library is restarted, the master-slave error may occur because the content of the memory is gone.

Experiment:

An abnormal shutdown of the main library:

Create a table on the main library

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

Main library:

Mysql > insert into b values (10), (20)

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

From the library:

Mysql > select * from a.b

+-- +

| | id |

+-- +

| | 10 |

| | 20 |

+-- +

After restarting the main library:

From the library:

Mysql > select * from a.b

+-- +

| | id |

+-- +

| | 10 |

| | 20 |

+-- +

2 rows in set (0.00 sec)

At this time, go to the main library to observe:

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 | 169454186 | 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-11e7Mub3fafafafafa24' |

| | mysql-bin.000012 | 259 | Query | 169454186 | 327 | BEGIN |

| | mysql-bin.000012 | 327 | Query | 169454186 | 409 | DELETE FROM `a`.`b` |

-+

6 rows in set (0.00 sec)

Observe from the library again:

Mysql > select * from a.b

Empty set (0.00 sec)

At this time, the state of the master and subordinate is also normal.

Second, restart from Kuqichang:

On the main library:

Mysql > insert into a.b values (10), (20)

Query OK, 2 rows affected (0.00 sec)

Records: 2 Duplicates: 0 Warnings: 0

From the library:

Mysql > select * from a.b

+-- +

| | id |

+-- +

| | 10 |

| | 20 |

+-- +

2 rows in set (0.00 sec)

After restarting the slave library:

Mysql > select * from a.b

Empty set (10.00 sec)

Main library:

Mysql > delete from a.b where id=10

Query OK, 1 row affected (0.00 sec)

From the library:

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 Murb3famuri 525400199b09V 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

Analysis:

When you restart the main library and access the A.B table, the following is recorded in 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`

/ *! * /

Through tracking, we can know:

# 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

Is implemented in the sql/sql_base.cc:open_table_entry_fini function to add delete

Specifically, it is embodied in:

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}

The delete statement is written directly in mysqlbinlog.

For tables with a memory engine, there may be problems with master and slave:

1 logical backup: dump the contents of the memory table at that time, but it does not support transactions, so it is possible that the data is different

2 physical backup: the table of memory only contains frm files, so the contents must be empty

So neither way can guarantee that it can be made directly from the library.

The best way is not to use memory engine tables in master-slave replication. If you really want to use it, you need to understand his influence.

The answer to the question about mysql replication and memory engine tables is shared here. I hope the above content can be of some help to you. If you still have a lot of doubts to be solved, you can follow the industry information channel to learn more about it.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.