{"id":25173,"date":"2026-01-19T13:05:07","date_gmt":"2026-01-19T04:05:07","guid":{"rendered":"https:\/\/labs.gree.jp\/blog\/?p=25173"},"modified":"2026-01-19T13:03:08","modified_gmt":"2026-01-19T04:03:08","slug":"mysql-%e3%81%a7-super_read_onlyon-%e3%81%aa%e3%81%ae%e3%81%ab-gtid-%e3%81%8c%e5%89%b2%e3%82%8a%e5%bd%93%e3%81%a6%e3%82%89%e3%82%8c%e3%82%8b-statement-%e3%81%aa%e3%81%a9%e3%81%ab%e3%81%a4%e3%81%84","status":"publish","type":"post","link":"https:\/\/labs.gree.jp\/blog\/2026\/01\/25173\/","title":{"rendered":"MySQL \u3067 super_read_only=on \u306a\u306e\u306b GTID \u304c\u5272\u308a\u5f53\u3066\u3089\u308c\u308b statement \u306a\u3069\u306b\u3064\u3044\u3066"},"content":{"rendered":"<p>\u3053\u3093\u306b\u3061\u308f\u3002\u305b\u3058\u307e\u3067\u3059\u3002<\/p>\n<p>2025-10-21 \u306b\u30ea\u30ea\u30fc\u30b9\u3055\u308c\u305f MySQL 9.5.0\u3001\u500b\u4eba\u7684\u306b\u306f\u4e45\u3057\u3076\u308a\u306b Innovation Release \u3060\u306a\u3068\u601d\u308f\u3055\u308c\u308b\u5909\u66f4\u304c\u5165\u3063\u3066\u307e\u3057\u305f\u3002<\/p>\n<div class=\"blog-card\">\n<div class=\"blog-card-body-outer\">\n<div class=\"blog-card-body\">\n<h5 class=\"blog-card-title\">\n\t\t\t\t\t\t\t\t<a href=\"https:\/\/dev.mysql.com\/doc\/relnotes\/mysql\/9.5\/en\/news-9-5-0.html#mysqld-9-5-0-replication\">MySQL :: MySQL 9.6 Release Notes :: Changes in MySQL 9.5.0 (2025-10-21)<\/a><br \/>\n\t\t\t\t\t\t\t<\/h5>\n<div class=\"blog-card-site-title\">\n\t\t\t\t\t\t\t<a href=\"http:\/\/dev.mysql.com\"><br \/>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/labs.mysql.com\/common\/themes\/sakila\/favicon.ico\" width=\"16\" height=\"16\" alt=\"\" ><br \/>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\thttp:\/\/dev.mysql.com\t\t\t\t\t\t\t<\/a>\n\t\t\t\t\t\t<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<blockquote><p>\n  To enable enhanced replication capabilities by default, the default values of gtid_mode has been changed to ON. Additionally, the default value of enforce_gtid_consistency has been changed from ON.\n<\/p><\/blockquote>\n<p>\u3064\u3044\u306b\u3001GTID\u304c\u30c7\u30d5\u30a9\u30eb\u30c8\u3067\u6709\u52b9\u5316\u3055\u308c\u308b\u3053\u3068\u306b\u306a\u308a\u307e\u3057\u305f\u3002<\/p>\n<p>\u307f\u306a\u3055\u3093\u3001GTID\u4f7f\u3063\u3066\u307e\u3059\u304b\uff1f <a href=\"https:\/\/bugs.mysql.com\/bug.php?id=88720\">Bug #88720<\/a>\u3067Affects Me\u306e\u30dc\u30bf\u30f3\u306f\u62bc\u3057\u307e\u3057\u305f\u304b\uff1f MySQL 8.4.1 \u3067 fix \u3055\u308c\u305f<a href=\"https:\/\/github.com\/mysql\/mysql-server\/commit\/33096a61daa96392bd350dd78346f0c1920203e1\">Bug #35665076\u306e commit log<\/a> \u306f\u8aad\u307e\u308c\u307e\u3057\u305f\u304b\uff1f<\/p>\n<p>Bug #88720\u3082Bug #35665076\u3082\u3054\u5b58\u77e5\u306e\u65b9\u306f\u3001\u3067\u304d\u308c\u3070\u3054\u540c\u50da\u3084\u3054\u53cb\u4eba\u306e\u65b9\u3005\u306b\u3082\u304a\u8a98\u3044\u5408\u308f\u305b\u306e\u4e0a\u3001Bug #88720\u3067Affects Me\u3092\u304a\u9858\u3044\u3044\u305f\u3057\u307e\u3059\u3002<\/p>\n<p>\u305d\u3046\u3067\u306a\u3044\u65b9\u306f\u3001\u3053\u308c\u304b\u3089\u3053\u308c\u3089\u306e\u30d0\u30b0\u306a\u3069\u306b\u3064\u3044\u3066\u89e3\u8aac\u3057\u307e\u3059\u306e\u3067\u3001\u8aad\u307f\u7d42\u308f\u3063\u305f\u3089\u3067\u304d\u308c\u3070Bug #88720\u3067Affects Me\u306e\u3054\u5354\u529b\u3092\u304a\u9858\u3044\u3044\u305f\u3057\u307e\u3059\u3002<\/p>\n<p>\u3067\u306f\u306f\u3058\u3081\u307e\u3059\u3002<\/p>\n<h2>\u306f\u3058\u3081\u306b<\/h2>\n<p>MySQL\u3067GTID\u3092\u4f7f\u3063\u305freplication\u3092\u884c\u3046\u3068\u304d\u3001<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/server-system-variables.html#sysvar_super_read_only\">super_read_only<\/a>\u30aa\u30d7\u30b7\u30e7\u30f3\u3092\u6709\u52b9\u306b\u3059\u308b\u3053\u3068\u304c\u826f\u3044\u3068\u3055\u308c\u3066\u3044\u307e\u3059\u3002\uff08\u3082\u3046\u5341\u5e74\u4ee5\u4e0a\u524d\u306e\u8a18\u4e8b\u306b\u306a\u308a\u307e\u3059\u304c\uff09Percona Database Performance Blog\u306e<a href=\"https:\/\/www.percona.com\/blog\/super_read_only-gtid-replication\/\">super_read_only and GTID replication<\/a>\u306e\u6642\u70b9\u3067\u3059\u3067\u306b\u8a00\u53ca\u3055\u308c\u3066\u3044\u307e\u3059\u3002<\/p>\n<p>MySQL\u306ereplication\u304c\u958b\u59cb\u3067\u304d\u306a\u3044\u30b1\u30fc\u30b9\u306e\u4e00\u3064\u3068\u3057\u3066\u3001\u6b21\u306e\u3088\u3046\u306a\u3082\u306e\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n<ul>\n<li><code>CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION=1; START REPLICA;<\/code> \u3057\u305f\u969b\u3001source \u306e gtid_purged \u304c replica \u306e gtid_executed \u306e\u30b5\u30d6\u30bb\u30c3\u30c8\u306b\u306a\u3063\u3066\u306a\u304b\u3063\u305f\u3089\u3001<a href=\"https:\/\/dev.mysql.com\/doc\/mysql-errors\/8.4\/en\/server-error-reference.html#error_er_source_has_purged_required_gtids\">ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS<\/a>\uff08\u3042\u308b\u3044\u306f<a href=\"https:\/\/dev.mysql.com\/doc\/mysql-errors\/8.0\/en\/server-error-reference.html#error_er_master_has_purged_required_gtids\">ER_MASTER_HAS_PURGED_REQUIRED_GTIDS<\/a>\uff09\u304c\u767a\u751f\u3059\u308b\u3002<\/li>\n<\/ul>\n<p>\u3053\u308c\u3092\u9632\u3050\u305f\u3081\u306e\u6709\u52b9\u306a\u624b\u6bb5\u3068\u3057\u3066\u3001 SOURCE_AUTO_POSITION=1 \u3067 replication \u3059\u308b\u969b\u306f replica \u3067 super_read_only=on \u306b\u3059\u308b\u3053\u3068\u304c\u671b\u307e\u3057\u3044\u3068\u8003\u3048\u3089\u308c\u307e\u3059\u3002<\/p>\n<p>\u3067\u306f\u3001 replica \u3067 super_read_only=on \u306b\u3059\u308b\u3053\u3068\u3067\u4f55\u3092\u9632\u3052\u308b\u306e\u304b\u3092\u898b\u3066\u3044\u304d\u307e\u3057\u3087\u3046\u3002<\/p>\n<h2>ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS\u304c\u767a\u751f\u3059\u308b\u72b6\u6cc1<\/h2>\n<p>MySQL 8.0\u516c\u5f0f\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u306e<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/replication-gtids-auto-positioning.html\">17.1.3.3 GTID \u81ea\u52d5\u914d\u7f6e<\/a>\u3067\u3001ER_MASTER_HAS_PURGED_REQUIRED_GTIDS(ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS)\u304c\u767a\u751f\u3059\u308b\u30b1\u30fc\u30b9\u306b\u3064\u3044\u3066\u8a00\u53ca\u3055\u308c\u3066\u3044\u308b\u306e\u3067\u3059\u304c\u3001OSS\u306a\u306e\u3067\u751f\u6210AI\u306b\u805e\u3044\u305f\u308a\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u3092\u8aad\u3093\u3060\u308a\u3059\u308b\u3088\u308a\u3082\u3001\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u8aad\u3093\u3060\u307b\u3046\u304c\u3088\u308a\u7406\u89e3\u3067\u304d\u308b\u30b1\u30fc\u30b9\u304c\u3057\u3070\u3057\u3070\u3042\u308a\u307e\u3059\u3002\u306a\u306e\u3067\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u3092\u8aad\u3093\u3067\u3044\u304d\u307e\u3059\u3002<\/p>\n<p><code>CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION=1; START REPLICA;<\/code> \u3059\u308b\u3068\u3001MySQL\u306e\u30d7\u30ed\u30c8\u30b3\u30eb\u7684\u306b\u306f replica \u304b\u3089 COM_BINLOG_DUMP_GTID \u304c\u6295\u3052\u3089\u308c\u308b\u306e\u3067\u3059\u304c\u3001COM_BINLOG_DUMP_GTID \u6295\u3052\u308b\u969b\u3001 replica \u304b\u3089\u306f gtid_executed \u3092 source \u306b\u9001\u4fe1\u3057\u3066\u3044\u307e\u3059\u3002<\/p>\n<p><a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_replica.cc#L4188-L4215\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_replica.cc#L4188-L4215<\/a><\/p>\n<p>source \u306f replica \u304b\u3089 COM_BINLOG_DUMP_GTID \u3092\u53d7\u3051\u53d6\u308b\u3068\u3001BinlogSender\u30af\u30e9\u30b9\u306e\u30aa\u30d6\u30b8\u30a7\u30af\u30c8\u3092\u751f\u6210\u3059\u308b\u306e\u3067\u3059\u304c<\/p>\n<p><a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_source.cc#L985-L1043\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_source.cc#L985-L1043<\/a><br \/>\n<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_source.cc#L1054-L1059\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_source.cc#L1054-L1059<\/a><\/p>\n<p>BinlogSender\u30af\u30e9\u30b9\u306e\u30b3\u30f3\u30b9\u30c8\u30e9\u30af\u30bf\u3092\u898b\u308b\u3068\u3001replica \u304b\u3089\u6e21\u3055\u308c\u305f gtid_executed \u306f\u3001exclude_gtids \u3068\u3044\u3046\u5909\u6570\u540d\u306b\u306a\u3063\u3066\u3044\u307e\u3059\u3002<br \/>\n<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L234-L241\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L234-L241<\/a><\/p>\n<pre class=\"start-line:234 lang:c++\"><code>Binlog_sender::Binlog_sender(THD *thd, const char *start_file,\n                             my_off_t start_pos, Gtid_set *exclude_gtids,\n                             uint32 flag)\n    : m_thd(thd),\n      m_packet(*thd->get_protocol_classic()->get_output_packet()),\n      m_start_file(start_file),\n      m_start_pos(start_pos),\n      m_exclude_gtid(exclude_gtids),<\/code><\/pre>\n<p>Binlog_sender::run() \u2192 Binlog_sender::init() \u2192 Binlog_sender::check_start_file() \u3068\u898b\u3066\u3044\u304d\u307e\u3059\u3068<\/p>\n<p><a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L379-L382\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L379-L382<\/a><br \/>\n<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L257-L298\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L257-L298<\/a><br \/>\n<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L854\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L854<\/a><\/p>\n<p>Binlog_sender::check_start_file() \u3067\u3069\u306e binary log \u304b\u3089\u9001\u4fe1\u3059\u308b\u3079\u304d\u304b\u5224\u5b9a\u3057\u3066\u3044\u308b\u306e\u3067\u3059\u304c<\/p>\n<p><code>i.e., throw error if slave's GTID executed set is not a superset of Master's purged GTID set.<\/code><\/p>\n<p>slave(replica) \u306e gtid_executed \u304c master(source) \u306e gtid_purged \u306e\u30b9\u30fc\u30d1\u30fc\u30bb\u30c3\u30c8\u3067\u306a\u3051\u308c\u3070\u3001 master \u306f error \u3092\u8fd4\u3059\u3068\u3044\u3063\u305f\u610f\u306e\u30b3\u30e1\u30f3\u30c8\u304c\u3042\u308a\u307e\u3059\u3002<\/p>\n<p><a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L899-L930\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L899-L930<\/a><\/p>\n<pre class=\"start-line:899 lang:c++\"><code>    \/*\n      Setting GTID_PURGED (when GTID_EXECUTED set is empty i.e., when\n      previous_gtids are also empty) will make binlog rotate. That\n      leaves first binary log with empty previous_gtids and second\n      binary log's previous_gtids with the value of gtid_purged.\n      In find_first_log_not_in_gtid_set() while we search for a binary\n      log whose previous_gtid_set is subset of slave_gtid_executed,\n      in this particular case, server will always find the first binary\n      log with empty previous_gtids which is subset of any given\n      slave_gtid_executed. Thus Master thinks that it found the first\n      binary log which is actually not correct and unable to catch\n      this error situation. Hence adding below extra if condition\n      to check the situation. Slave should know about Master's purged GTIDs.\n      If Slave's GTID executed + retrieved set does not contain Master's\n      complete purged GTID list, that means Slave is requesting(expecting)\n      GTIDs which were purged by Master. We should let Slave know about the\n      situation. i.e., throw error if slave's GTID executed set is not\n      a superset of Master's purged GTID set.\n      The other case, where user deleted binary logs manually\n      (without using 'PURGE BINARY LOGS' command) but gtid_purged\n      is not set by the user, the following if condition cannot catch it.\n      But that is not a problem because in find_first_log_not_in_gtid_set()\n      while checking for subset previous_gtids binary log, the logic\n      will not find one and an error ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS\n      is thrown from there.\n    *\/\n    if (!gtid_state->get_lost_gtids()->is_subset(m_exclude_gtid)) {\n      mysql_bin_log.report_missing_purged_gtids(m_exclude_gtid, errmsg);\n      global_sid_lock->unlock();\n      set_fatal_error(errmsg.c_str());\n      return 1;\n    }<\/code><\/pre>\n<p>\u5b9f\u614b\u306f<\/p>\n<pre class=\"start-line:925 lang:c++\"><code>    if (!gtid_state->get_lost_gtids()->is_subset(m_exclude_gtid)) {<\/code><\/pre>\n<p>\u306a\u306e\u3067\u3001\u30b5\u30d6\u30bb\u30c3\u30c8\u3067\u306a\u3044\u304b\u3069\u3046\u304b\u3092\u898b\u3066\u3044\u308b\u308f\u3051\u3067\u3059\u306d\u3002<\/p>\n<p>replica \u306e gtid_executed \u306b\u542b\u307e\u308c\u306a\u3044 GTID\u3001replica \u304c\u5b9f\u884c\u3057\u3066\u3044\u306a\u3044 GTID \u306e binlog event \u3092source\u304c\u6301\u3063\u3066\u3044\u305f\u306a\u3089\u3070\u3001\u305d\u306e GTID \u304c\u3069\u308c\u304f\u3089\u3044\u53e4\u304f\u3066\u3082\u3001 replica \u306b\u9001\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002\u305d\u3046\u3057\u306a\u3051\u308c\u3070\u3001 source \u3068 replica \u306f\u540c\u671f\u304c\u53d6\u308c\u3066\u3044\u308b\u3053\u3068\u3092\u4fdd\u8a3c\u3067\u304d\u307e\u305b\u3093\u3002\uff08\u5927\u5909\u3056\u3063\u304f\u308a\u3057\u305f\u4f8b\u3092\u3042\u3052\u308b\u3068\u3001source\u4e0a\u3067\u3069\u308c\u307b\u3069\u6614\u306b\u767a\u884c\u3055\u308c\u305fINSERT\u3067\u3042\u3063\u3066\u3082\u3001\u305d\u308c\u304creplica\u3067\u5b9f\u884c\u3055\u308c\u3066\u3044\u306a\u3051\u308c\u3070\u3001source\u3068replica\u306e\u30c6\u30fc\u30d6\u30eb\u306e\u72b6\u614b\u306f\u4e00\u81f4\u3057\u306a\u3044\u3067\u3059\u3088\u306d\uff09<br \/>\n\u3053\u306e\u3088\u3046\u306b\u8003\u3048\u3066\u3044\u304f\u3068\u3001replica \u306e gtid_executed \u306f\u3001\u8a00\u3044\u63db\u3048\u308b\u3068\u3001 source \u304b\u3089\u9001\u4fe1\u3059\u3079\u304d binlog event \u304b\u3089\u9664\u5916\u3067\u304d\u308b GTID \u306e\u96c6\u5408\u3068\u3082\u8a00\u3048\u307e\u3059\u3002\uff08\u305d\u308c\u3067BinlogSender\u306e\u30bd\u30fc\u30b9\u30b3\u30fc\u30c9\u4e2d\u3067\u306f exclude_gtids \u3068\u3044\u3046\u5909\u6570\u540d\u304c\u7528\u3044\u3089\u308c\u3066\u3044\u308b\u306e\u3067\u306f\u306a\u3044\u304b\u3068\u601d\u308f\u308c\u307e\u3059\u3002\uff09<\/p>\n<p>\u6700\u7d42\u7684\u306b\u3001 source \u306e gtid_executed \u3067\u5b9f\u884c\u6e08\u307f\u306e GTID \u306b\u7d10\u3065\u304f binlog event \u3092\u3001\u3059\u3079\u3066 replica \u3067\u3082\u5b9f\u884c\u3059\u308b\u5fc5\u8981\u304c\u3042\u308a\u307e\u3059\u3002source\u304b\u3089replica\u304c\u5fc5\u8981\u3068\u3059\u308bGTID\uff08\u306b\u7d10\u3065\u304fbinlog event\uff09\u3092\u3059\u3079\u3066\u9001\u4fe1\u3067\u304d\u308b\u304b\u306e\u5224\u5b9a\u3092\u3059\u308b\u3068\u304d\u3001 source \u306f Gtid_state::get_lost_gtids() \u3092\u547c\u3093\u3067\u3001\u30b5\u30d6\u30bb\u30c3\u30c8\u304b\u3069\u3046\u304b\u8abf\u3079\u3066\u307e\u3059\u3002 get_lost_gtids() \u306f\u3001 source \u306e binlog \u304b\u3089\u5931\u308f\u308c\u305f GTID\u3001\u3053\u308c\u3089\u306e GTID \u3092\u542b\u3080 binlog \u306f\u3059\u3067\u306b purge \u3055\u308c\u3066\u3044\u308b\u3068\u3044\u3046\u3053\u3068\u3067\u3059\u3002<\/p>\n<p>\u3061\u306a\u307f\u306b gtid_purged \u306f\u3001Gtid_state::get_lost_gtids() \u306e\u5024\u3092\u53c2\u7167\u3057\u3066\u3044\u307e\u3059\u3002<br \/>\n<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/sys_vars.h#L2640-L2698\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/sys_vars.h#L2640-L2698<\/a><\/p>\n<p>\u3068\u3044\u3046\u308f\u3051\u3067\u3001source \u306e gtid_purged \u304c replica \u306e gtid_executed \u306e\u30b5\u30d6\u30bb\u30c3\u30c8\u306b\u306a\u3063\u3066\u306a\u304b\u3063\u305f\u3089\u30a8\u30e9\u30fc\u306b\u306a\u308b\u308f\u3051\u3067\u3059\u3002<\/p>\n<p>\u6700\u7d42\u7684\u306bMYSQL_BIN_LOG::report_missing_gtids() \u3067ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS\u306e errmsg \u3092\u751f\u6210\u3057\u3066\u8fd4\u3059\u3053\u3068\u306b\u306a\u308a\u307e\u3059\u3002<br \/>\n<a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/binlog.cc#L9213-L9253\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/binlog.cc#L9213-L9253<\/a><\/p>\n<h2>ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS \u3092\u4e88\u9632\u3059\u308b\u305f\u3081\u306e super_read_only=on<\/h2>\n<p>\u3088\u3063\u3066\u3001\u6700\u521d\u306b\u3042\u3052\u305f<\/p>\n<ul>\n<li><code>CHANGE REPLICATION SOURCE TO ... SOURCE_AUTO_POSITION=1; START REPLICA;<\/code> \u3057\u305f\u969b\u3001source \u306e gtid_purged \u304c replica \u306e gtid_executed \u306e\u30b5\u30d6\u30bb\u30c3\u30c8\u306b\u306a\u3063\u3066\u306a\u304b\u3063\u305f\u3089\u3001<a href=\"https:\/\/dev.mysql.com\/doc\/mysql-errors\/8.4\/en\/server-error-reference.html#error_er_source_has_purged_required_gtids\">ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS<\/a>\uff08\u3042\u308b\u3044\u306f<a href=\"https:\/\/dev.mysql.com\/doc\/mysql-errors\/8.0\/en\/server-error-reference.html#error_er_master_has_purged_required_gtids\">ER_MASTER_HAS_PURGED_REQUIRED_GTIDS<\/a>\uff09\u304c\u767a\u751f\u3059\u308b\u3002<\/li>\n<\/ul>\n<p>\u3068\u306a\u308b\u308f\u3051\u3067\u3001\u3053\u308c\u3092\u9632\u3050\u305f\u3081\u306b\u306f replica \u3067 super_read_only=on \u3092\u8a2d\u5b9a\u3059\u308b\u3053\u3068\u304c\u6709\u52b9\u306a\u624b\u6bb5\u3068\u306a\u308a\u307e\u3059\u3002<\/p>\n<p>\u5177\u4f53\u7684\u306b\u3069\u306e\u3088\u3046\u306a\u52b9\u679c\u304c\u3042\u308b\u304b\u3068\u3044\u3044\u307e\u3059\u3068\u3001\u6b21\u306e\u3088\u3046\u306a\u72b6\u6cc1\u3092\u9632\u3052\u307e\u3059\u3002<\/p>\n<ol>\n<li>\u4f8b\u3048\u3070\u3001source \u306b\u5bfe\u3057\u3066 replica \u304c2\u53f0\u3076\u3089\u4e0b\u304c\u3063\u3066\u3044\u308b\u69cb\u6210\u304c\u3042\u308b\u3068\u3057\u307e\u3059\u3002replica\u306f\u305d\u308c\u305e\u308cnode_a,node_b\u3068\u3057\u307e\u3059\u3002<\/li>\n<li>\u3042\u308b\u65e5node_a\u3067super_read_only=off\u306e\u72b6\u614b\u3067node_a\u3067\u306e\u307fADD INDEX\u306a\u3069\u5b9f\u884c\u3057\u305f\u5f8c\u3001\u6708\u65e5\u304c\u6d41\u308cADD INDEX\u3057\u305f\u3068\u304d\u306ebinlog\u304cpurge\u3055\u308c\u305f\u3068\u3057\u307e\u3059\u3002<\/li>\n<li>source\u306e\u30a4\u30f3\u30b9\u30bf\u30f3\u30b9\u304c\u843d\u3061\u3066 node_a\u3092\u65b0\u3057\u3044source\u3068\u3057\u3066\u6607\u683c\u3055\u305b\u308b\u305f\u3081\u3001node_b\u3067 CHANGE REPLICATION SOURCE TO ... SOURCE_HOST=node_a, SOURCE_AUTO_POSITION=1; START REPLICA; \u3057\u307e\u3059\u3002<\/li>\n<li>node_a\u3067\u306e\u307fADD INDEX\u3057\u305f\u3068\u304d\u306ebinlog\u304cpurge\u3067\u5931\u308f\u308c\u3001ADD INDEX\u306b\u95a2\u9023\u3057\u305fGTID\u304cnode_a\u306egtid_purged\u306b\u542b\u307e\u308c\u3066\u3044\u308b\u72b6\u614b\u306a\u306e\u3067\u3001\u65b0\u3057\u3044source \u306e gtid_purged \u304c \u65b0\u3057\u3044replica \u306e gtid_executed \u306e\u30b5\u30d6\u30bb\u30c3\u30c8\u306b\u306a\u3089\u306a\u3044\uff08ADD INDEX\u3057\u305f\u3068\u304d\u306eGTID\u304c\u65b0\u3057\u3044source\u3067\u3042\u308bnode_a\u306egtid_purged\u306b\u306e\u307f\u542b\u307e\u308c\u3066\u304a\u308a\u3001\u65b0\u3057\u3044replica\u306enode_b\u306egtid_executed\u306b\u5b58\u5728\u3057\u306a\u3044\uff09\u306e\u3067\u3001ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS\u304c\u767a\u751f\u3057\u307e\u3059\u3002<\/li>\n<\/ol>\n<p>replica\u304csuper_read_only=on\u3067\u3042\u308c\u3070\u30012.\u306e\u6642\u70b9\u3067ADD INDEX\u3067\u304d\u306a\u3044\u306e\u3067\u3001\u3053\u3046\u3044\u3063\u305f\u72b6\u6cc1\u3092\u9632\u3052\u308b\u308f\u3051\u3067\u3059\u306d\u3002<\/p>\n<h2>ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS\u304c\u5b9f\u969b\u306b\u767a\u751f\u3057\u305f\u969b\u306e error message \u306e\u88dc\u8db3\u4e8b\u9805<\/h2>\n<p>ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS\u306f\u3001\u975e\u5e38\u306b\u731b\u70c8\u306b\u308f\u304b\u308a\u306b\u304f\u3044\u90e8\u5206\u304c\u3042\u308a\u307e\u3059\u306e\u3067\u88dc\u8db3\u3057\u307e\u3059\u3002<\/p>\n<p>\u5177\u4f53\u7684\u306b\u8a66\u305b\u308b\u3088\u3046\u3001\u53c2\u8003\u307e\u3067\u306b docker-compose.yml \u3084\u521d\u671f\u5316\u7528\u306escript\u3092\u7528\u610f\u3057\u3066\u307f\u307e\u3059\u3002<\/p>\n<pre><code>$ cat docker-compose.yml\nservices:\n\n  mysql-source:\n    image: container-registry.oracle.com\/mysql\/community-server:8.0\n    command:\n      - --enforce_gtid_consistency=ON\n      - --gtid_mode=ON\n      - --server_id=1\n    environment: &environment\n      TZ: Asia\/Tokyo\n      MYSQL_ALLOW_EMPTY_PASSWORD: 1\n      MYSQL_DATABASE: test\n      MYSQL_USER: test\n      MYSQL_PASSWORD: pass\n      MYSQL_REPLICATION_USER: repl\n      MYSQL_REPLICATION_PASSWORD: pass\n    volumes:\n      - .\/init_source.sh:\/docker-entrypoint-initdb.d\/init_source.sh:ro\n    ports:\n      - \"13306:3306\"\n    networks:\n      app_net:\n        ipv4_address: 172.30.0.2\n\n  mysql-replica:\n    image: container-registry.oracle.com\/mysql\/community-server:8.0\n    depends_on:\n      - mysql-source\n    command:\n      - --enforce_gtid_consistency=ON\n      - --gtid_mode=ON\n      - --server_id=2\n    environment:\n      <<: *environment\n      MYSQL_REPLICATION_HOST: mysql-source\n    volumes:\n      - .\/init_replica.sh:\/docker-entrypoint-initdb.d\/init_replica.sh:ro\n    ports:\n      - \"23306:3306\"\n    networks:\n      app_net:\n        ipv4_address: 172.30.0.3\n\n  mysql-replica2:\n    image: container-registry.oracle.com\/mysql\/community-server:8.0\n    depends_on:\n      - mysql-replica\n    command:\n      - --enforce_gtid_consistency=ON\n      - --gtid_mode=ON\n      - --server_id=3\n    environment:\n      <<: *environment\n      MYSQL_REPLICATION_HOST: mysql-source\n    volumes:\n      - .\/init_replica.sh:\/docker-entrypoint-initdb.d\/init_replica.sh:ro\n    ports:\n      - \"33306:3306\"\n    networks:\n      app_net:\n        ipv4_address: 172.30.0.4\n\nnetworks:\n  app_net:\n    driver: bridge\n    ipam:\n      driver: default\n      config:\n        - subnet: 172.30.0.0\/24\n$ cat init_source.sh\n#!\/bin\/bash\n\nif [ -v MYSQL_REPLICATION_USER -a -v MYSQL_REPLICATION_PASSWORD ]; then\n  mysql -u root -v mysql <<SQL\n    CREATE USER '$MYSQL_REPLICATION_USER'@'%' IDENTIFIED BY '$MYSQL_REPLICATION_PASSWORD';\n    GRANT REPLICATION SLAVE ON *.* TO '$MYSQL_REPLICATION_USER'@'%';\nSQL\nfi\n$ cat init_replica.sh\n#!\/bin\/bash\n\nif [ -v MYSQL_REPLICATION_USER -a -v MYSQL_REPLICATION_PASSWORD -a -v MYSQL_REPLICATION_HOST ]; then\n  mysql -u root -v <<SQL\n    CHANGE REPLICATION SOURCE TO\n      SOURCE_HOST = '$MYSQL_REPLICATION_HOST',\n      SOURCE_USER = '$MYSQL_REPLICATION_USER',\n      SOURCE_PASSWORD = '$MYSQL_REPLICATION_PASSWORD',\n      SOURCE_SSL = 1,\n      SOURCE_AUTO_POSITION = 1;\nSQL\n  mysqladmin ping --wait=5 -h \"$MYSQL_REPLICATION_HOST\"\n  mysql -u root -v -e \"START SLAVE;\"\nfi\n$<\/code><\/pre>\n<p>\u3053\u308c\u3067<\/p>\n<ol>\n<li>docker-compose up -d \u3059\u308b<\/li>\n<li>docker-compose exec mysql-replica mysql \u3057\u3066 <code>USE test;CREATE TABLE test (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, val INT NOT NULL);<\/code><\/li>\n<li>docker-compose exec mysql-replica mysql \u3057\u3066 <code>FLUSH BINARY LOGS; PURGE BINARY LOGS BEFORE DATE_ADD(NOW(), INTERVAL 1 SECOND);<\/code><\/li>\n<li>docker-compose exec mysql-replica2 mysql \u3057\u3066 <code>STOP REPLICA; CHANGE REPLICATION SOURCE TO SOURCE_HOST='mysql-replica', SOURCE_USER='repl', SOURCE_PASSWORD='pass', SOURCE_SSL=1, SOURCE_AUTO_POSITION=1; START REPLICA;<\/code> \u3059\u308b\u3068\u3001ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS \u304c\u8fd4\u3063\u3066\u304f\u308b<\/li>\n<\/ol>\n<p>\u3068\u3044\u3063\u305f\u3053\u3068\u3092\u78ba\u8a8d\u3067\u304d\u307e\u3059\u3002\u305f\u3060<\/p>\n<pre><code>$ docker-compose exec mysql-replica2 mysql -e 'show replica status\\G'| grep -i err\n                   Last_Errno: 0\n                   Last_Error:\n                Last_IO_Errno: 13114\n                Last_IO_Error: Got fatal error 1236 from source when reading data from binary log: 'Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. The GTID set sent by the replica is '624769cb-f1e0-11f0-a025-52a98c1c970c:1-2', and the missing transactions are '6273818c-f1e0-11f0-8a08-0e5e9e614d1d:1''\n               Last_SQL_Errno: 0\n               Last_SQL_Error:\n      Last_IO_Error_Timestamp: 260115 16:07:59\n     Last_SQL_Error_Timestamp:\n$ perror --version\nperror  Ver 8.0.44 for Linux on x86_64 (MySQL Community Server - GPL)\n$ perror 13114\nMySQL error code MY-013114 (ER_SERVER_SOURCE_FATAL_ERROR_READING_BINLOG): Got fatal error %d from source when reading data from binary log: '%-.512s'\n$ perror 1236\nMySQL error code MY-001236 (ER_SOURCE_FATAL_ERROR_READING_BINLOG): Got fatal error %d from source when reading data from binary log: '%-.512s'\n$ perror 1789\nMySQL error code MY-001789 (ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS): Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. %s\n$<\/code><\/pre>\n<p>\u3042\u308c\uff1f<a href=\"https:\/\/dev.mysql.com\/doc\/mysql-errors\/8.0\/en\/server-error-reference.html#error_er_source_has_purged_required_gtids\">Error number: 1789<\/a>\u3058\u3083\u306a\u304f\u3066<a href=\"https:\/\/dev.mysql.com\/doc\/mysql-errors\/8.0\/en\/server-error-reference.html#error_er_source_fatal_error_reading_binlog\">Error number: 1236<\/a>\u306a\u306e\u3067\u3059\uff1f\u3068\u601d\u308f\u308c\u308b\u3067\u3057\u3087\u3046\u304c\u3001\u3088\u304f\u3088\u304f\u8aad\u3093\u3067\u307f\u308b\u3068<\/p>\n<p><code>'Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. The GTID set sent by the replica is 'e24b415e-f1d7-11f0-88eb-066225a28251:1-4', and the missing transactions are 'e2855d1a-f1d7-11f0-8b58-e6f3098eb441:1''<\/code><\/p>\n<p>\u3053\u306e\u90e8\u5206\u304c<a href=\"https:\/\/dev.mysql.com\/doc\/mysql-errors\/8.0\/en\/server-error-reference.html#error_er_source_has_purged_required_gtids\">ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS<\/a>\u306e<\/p>\n<blockquote><p>\n  Message: Cannot replicate because the source purged required binary logs. Replicate the missing transactions from elsewhere, or provision a new replica from backup. Consider increasing the source's binary log expiration period. %s\n<\/p><\/blockquote>\n<p>\u306a\u3093\u3067\u3059\u306d\u3002ER_SOURCE_HAS_PURGED_REQUIRED_GTIDS\u306fER_SOURCE_FATAL_ERROR_READING_BINLOG\u3067\u30e9\u30c3\u30d7\u3055\u308c\u3066\u8fd4\u3063\u3066\u304d\u3066\u3044\u308b\u3068\u3044\u3046\u308f\u3051\u3067\u3059\u306d\u3002<\/p>\n<p>\u5177\u4f53\u7684\u306b\u306f<\/p>\n<p><a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L926-L928\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.cc#L926-L928<\/a><\/p>\n<pre class=\"start-line:926 lang:c++\"><code>      mysql_bin_log.report_missing_purged_gtids(m_exclude_gtid, errmsg);\n      global_sid_lock->unlock();\n      set_fatal_error(errmsg.c_str());<\/code><\/pre>\n<p><a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.h#L434-L436\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.h#L434-L436<\/a><\/p>\n<pre class=\"start-line:434 lang:c++\"><code>  inline void set_fatal_error(const char *errmsg) {\n    set_error(ER_SOURCE_FATAL_ERROR_READING_BINLOG, errmsg);\n  }<\/code><\/pre>\n<p><a href=\"https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.h#L423-L428\">https:\/\/github.com\/mysql\/mysql-server\/blob\/mysql-8.0.44\/sql\/rpl_binlog_sender.h#L423-L428<\/a><\/p>\n<p>set_fatal_error()\u3067 Error number: 1236 ER_SOURCE_FATAL_ERROR_READING_BINLOG \u3092\u8a2d\u5b9a\u3057\u3066\u3044\u308b\u304b\u3068\u3002<\/p>\n<h2>\u3057\u304b\u3057super_read_only=on\u3067\u3082\u751f\u6210\u3055\u308c\u308bGTID\u304c\u3042\u308b<\/h2>\n<p>\u3053\u3053\u304b\u3089\u304c\u672c\u984c\u3067\u3059\u3002<\/p>\n<p>\u4f8b\u3048\u3070\u3001gtid_mode=on\u304b\u3064super_read_only=on \u306b\u8a2d\u5b9a\u3057\u305fMySQL8.0.44\u306ereplica\u3067\u6b21\u306eSQL\u3084\u30b3\u30de\u30f3\u30c9\u3092\u5b9f\u884c\u3057\u305f\u5834\u5408\u3001GTID\u304c\u767a\u884c\u3055\u308c\u308b\u3082\u306e\u306f\u3069\u308c\u3067\u3057\u3087\u3046\u304b\uff1f<\/p>\n<ol>\n<li>FLUSH ERROR LOGS;<\/li>\n<li>mysqladmin flush-logs slow<\/li>\n<li>mysqldump --single-transaction<\/li>\n<\/ol>\n<p>\u7b54\u3048\u306f 1\u301c3 \u305c\u3093\u3076\u3067\u3059\u3002<\/p>\n<p>\u300c\u304a\u3044\u304a\u3044\u30de\u30b8\u304b\u3088\uff1f\uff01\u300d\u3068\u601d\u308f\u308c\u307e\u3057\u305f\u304b\uff1f\u521d\u3081\u3066\u77e5\u3063\u305f\u3068\u304d\u3001\u79c1\u306f\u305d\u3046\u601d\u3044\u307e\u3057\u305f\u3002<\/p>\n<p>read only \u306a replica\u3067mysqldump\u3092\u53e9\u304f\u306e\u306f\u81f3\u6975\u5f53\u7136\u3067\u3057\u3087\u3046\u3057\u3001error log\u3084slow query log\u3092replica\u3054\u3068\u306b\u500b\u5225\u306b\u30ed\u30fc\u30c6\u30fc\u30c8\u3057\u3066\u3082\u3044\u3044\u3068\u601d\u3046\u3058\u3083\u306a\u3044\u3067\u3059\u304b\u3002\u3067\u3082\u3001\u3053\u308c\u3089\u3092\u305d\u306e\u307e\u307e\u5b9f\u884c\u3059\u308b\u3068binlog\u306bFLUSH\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u304c\u51fa\u529b\u3055\u308c\u3001\u305d\u308c\u306b\u4f34\u3046GTID\u304c\u751f\u6210\u3055\u308c\u307e\u3059\u3002<\/p>\n<p>FLUSH \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306b\u306f <code>[NO_WRITE_TO_BINLOG | LOCAL]<\/code> \u3068\u3044\u3063\u305f\u30aa\u30d7\u30b7\u30e7\u30f3\u304c\u3042\u308b\u306e\u3067\u3059\u304c\u3001<\/p>\n<div class=\"blog-card\">\n<div class=\"blog-card-body-outer\">\n<div class=\"blog-card-body\">\n<h5 class=\"blog-card-title\">\n\t\t\t\t\t\t\t\t<a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/flush.html\">MySQL :: MySQL 8.0 \u30ea\u30d5\u30a1\u30ec\u30f3\u30b9\u30de\u30cb\u30e5\u30a2\u30eb :: 13.7.8.3 FLUSH \u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8<\/a><br \/>\n\t\t\t\t\t\t\t<\/h5>\n<div class=\"blog-card-site-title\">\n\t\t\t\t\t\t\t<a href=\"http:\/\/dev.mysql.com\"><br \/>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t<img decoding=\"async\" loading=\"lazy\" src=\"https:\/\/labs.mysql.com\/common\/themes\/sakila\/favicon.ico\" width=\"16\" height=\"16\" alt=\"\" ><br \/>\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\thttp:\/\/dev.mysql.com\t\t\t\t\t\t\t<\/a>\n\t\t\t\t\t\t<\/div>\n<\/p><\/div>\n<\/p><\/div>\n<\/p><\/div>\n<blockquote><p>\n  \u6ce8\u8a18<br \/>\n  \u30ec\u30d7\u30ea\u30ab\u306b\u30ec\u30d7\u30ea\u30b1\u30fc\u30c8\u3059\u308b\u3068\u554f\u984c\u304c\u767a\u751f\u3059\u308b\u305f\u3081\u3001FLUSH LOGS, FLUSH BINARY LOGS, FLUSH TABLES WITH READ LOCK (\u30c6\u30fc\u30d6\u30eb\u30ea\u30b9\u30c8\u306e\u6709\u7121\u306b\u304b\u304b\u308f\u3089\u305a)\u3001\u304a\u3088\u3073 FLUSH TABLES tbl_name ... FOR EXPORT \u306f\u30d0\u30a4\u30ca\u30ea\u30ed\u30b0\u306b\u66f8\u304d\u8fbc\u307e\u308c\u307e\u305b\u3093\u3002\n<\/p><\/blockquote>\n<p>\u3068\u3044\u3063\u305f\u3054\u304f\u4e00\u90e8\u306e\u4f8b\u5916\u3092\u9664\u304d\u3001 <code>[NO_WRITE_TO_BINLOG | LOCAL]<\/code> \u3092\u6307\u5b9a\u3057\u306a\u3044\u3068FLUSH\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306fbinlog\u306b\u5410\u304b\u308c\u3066\u3057\u307e\u3044\u3001GTID\u304c\u751f\u6210\u3055\u308c\u3066\u3057\u307e\u3046\u306e\u3067\u3059\u3002<\/p>\n<p>\u4f8b\u3048\u3070 MySQL 8.0.44 \u3092\u30ed\u30fc\u30ab\u30eb\u3067 docker \u3067\u7acb\u3061\u4e0a\u3052\u3066 set global super_read_only=on; \u3057\u305f\u5f8c\u3001 mysqldump --single-transaction \u3059\u308b\u3068 FLUSH TABLES \u304c\u767a\u884c\u3055\u308c\u3001GTID\u304c\u751f\u6210\u3055\u308c\u307e\u3059\u3002<\/p>\n<pre><code>$ docker run -p 127.0.0.1:13306:3306 -e MYSQL_ROOT_HOST=172.17.0.1 -e MYSQL_ROOT_PASSWORD=sejima --name mysql80 -d container-registry.oracle.com\/mysql\/community-server:8.0 --gtid-mode=on --enforce-gtid-consistency=on\n2b75e3ce43fd05a85523d5b3639cae5e42e927aec2d7f05a0bbf5666a926b659\n$ mysql -u root -h 127.0.0.1 -P 13306 -p\nEnter password:\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 8\nServer version: 8.0.44 MySQL Community Server - GPL\n\nCopyright (c) 2000, 2025, Oracle and\/or its affiliates.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nmysql> show binary logs;\n+---------------+-----------+-----------+\n| Log_name      | File_size | Encrypted |\n+---------------+-----------+-----------+\n| binlog.000001 |       180 | No        |\n| binlog.000002 |       157 | No        |\n+---------------+-----------+-----------+\n2 rows in set (0.00 sec)\n\nmysql> show binlog events in 'binlog.000002';\n+---------------+-----+----------------+-----------+-------------+-----------------------------------+\n| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                              |\n+---------------+-----+----------------+-----------+-------------+-----------------------------------+\n| binlog.000002 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.44, Binlog ver: 4 |\n| binlog.000002 | 126 | Previous_gtids |         1 |         157 |                                   |\n+---------------+-----+----------------+-----------+-------------+-----------------------------------+\n2 rows in set (0.00 sec)\n\nmysql> create database test;\nQuery OK, 1 row affected (0.01 sec)\n\nmysql> use test\nDatabase changed\nmysql> create table test (id int not null auto_increment primary key, val int not null);\nQuery OK, 0 rows affected (0.03 sec)\n\nmysql> insert into test values (NULL, 0);\nQuery OK, 1 row affected (0.01 sec)\n\nmysql> show binlog events in 'binlog.000002';\n+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+\n| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                      |\n+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+\n| binlog.000002 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.44, Binlog ver: 4                                                                         |\n| binlog.000002 | 126 | Previous_gtids |         1 |         157 |                                                                                                           |\n| binlog.000002 | 157 | Gtid           |         1 |         234 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:1'                                         |\n| binlog.000002 | 234 | Query          |         1 |         342 | create database test \/* xid=5 *\/                                                                          |\n| binlog.000002 | 342 | Gtid           |         1 |         419 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:2'                                         |\n| binlog.000002 | 419 | Query          |         1 |         587 | use `test`; create table test (id int not null auto_increment primary key, val int not null) \/* xid=10 *\/ |\n| binlog.000002 | 587 | Gtid           |         1 |         666 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:3'                                         |\n| binlog.000002 | 666 | Query          |         1 |         741 | BEGIN                                                                                                     |\n| binlog.000002 | 741 | Table_map      |         1 |         792 | table_id: 90 (test.test)                                                                                  |\n| binlog.000002 | 792 | Write_rows     |         1 |         836 | table_id: 90 flags: STMT_END_F                                                                            |\n| binlog.000002 | 836 | Xid            |         1 |         867 | COMMIT \/* xid=11 *\/                                                                                       |\n+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+\n11 rows in set (0.00 sec)\n\nmysql> set global super_read_only=on;\nQuery OK, 0 rows affected (0.00 sec)\n\nmysql> select @@super_read_only;\n+-------------------+\n| @@super_read_only |\n+-------------------+\n|                 1 |\n+-------------------+\n1 row in set (0.00 sec)\n\nmysql> select @@GLOBAL.gtid_executed;\n+------------------------------------------+\n| @@GLOBAL.gtid_executed                   |\n+------------------------------------------+\n| fc700b9f-f11c-11f0-91f5-a64484b688e0:1-3 |\n+------------------------------------------+\n1 row in set (0.01 sec)\n\nmysql> exit\nBye\n$ mysqldump --version\nmysqldump  Ver 8.0.44 for Linux on x86_64 (MySQL Community Server - GPL)\n$ mysqldump -u root -h 127.0.0.1 -P 13306 -p --single-transaction test test > test.sql\nEnter password:\nWarning: A partial dump from a server that has GTIDs will by default include the GTIDs of all transactions, even those that changed suppressed parts of the database. If you don't want to restore GTIDs, pass --set-gtid-purged=OFF. To make a complete dump, pass --all-databases --triggers --routines --events.\n$ mysql -u root -h 127.0.0.1 -P 13306 -p\nEnter password:\nWelcome to the MySQL monitor.  Commands end with ; or \\g.\nYour MySQL connection id is 10\nServer version: 8.0.44 MySQL Community Server - GPL\n\nCopyright (c) 2000, 2025, Oracle and\/or its affiliates.\n\nOracle is a registered trademark of Oracle Corporation and\/or its\naffiliates. Other names may be trademarks of their respective\nowners.\n\nType 'help;' or '\\h' for help. Type '\\c' to clear the current input statement.\n\nmysql> show binlog events in 'binlog.000002';\n+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+\n| Log_name      | Pos | Event_type     | Server_id | End_log_pos | Info                                                                                                      |\n+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+\n| binlog.000002 |   4 | Format_desc    |         1 |         126 | Server ver: 8.0.44, Binlog ver: 4                                                                         |\n| binlog.000002 | 126 | Previous_gtids |         1 |         157 |                                                                                                           |\n| binlog.000002 | 157 | Gtid           |         1 |         234 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:1'                                         |\n| binlog.000002 | 234 | Query          |         1 |         342 | create database test \/* xid=5 *\/                                                                          |\n| binlog.000002 | 342 | Gtid           |         1 |         419 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:2'                                         |\n| binlog.000002 | 419 | Query          |         1 |         587 | use `test`; create table test (id int not null auto_increment primary key, val int not null) \/* xid=10 *\/ |\n| binlog.000002 | 587 | Gtid           |         1 |         666 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:3'                                         |\n| binlog.000002 | 666 | Query          |         1 |         741 | BEGIN                                                                                                     |\n| binlog.000002 | 741 | Table_map      |         1 |         792 | table_id: 90 (test.test)                                                                                  |\n| binlog.000002 | 792 | Write_rows     |         1 |         836 | table_id: 90 flags: STMT_END_F                                                                            |\n| binlog.000002 | 836 | Xid            |         1 |         867 | COMMIT \/* xid=11 *\/                                                                                       |\n| binlog.000002 | 867 | Gtid           |         1 |         944 | SET @@SESSION.GTID_NEXT= 'fc700b9f-f11c-11f0-91f5-a64484b688e0:4'                                         |\n| binlog.000002 | 944 | Query          |         1 |        1022 | FLUSH TABLES                                                                                              |\n+---------------+-----+----------------+-----------+-------------+-----------------------------------------------------------------------------------------------------------+\n13 rows in set (0.00 sec)\n\nmysql> select @@super_read_only;\n+-------------------+\n| @@super_read_only |\n+-------------------+\n|                 1 |\n+-------------------+\n1 row in set (0.00 sec)\n\nmysql> select @@GLOBAL.gtid_executed;\n+------------------------------------------+\n| @@GLOBAL.gtid_executed                   |\n+------------------------------------------+\n| fc700b9f-f11c-11f0-91f5-a64484b688e0:1-4 |\n+------------------------------------------+\n1 row in set (0.00 sec)\n\nmysql> exit\nBye\n$<\/code><\/pre>\n<p>\u5e78\u3044\u306b\u3057\u3066\u3001mysqldump\u3067GTID\u304c\u751f\u6210\u3055\u308c\u3066\u3057\u307e\u3046\u306e\u306f <a href=\"https:\/\/github.com\/mysql\/mysql-server\/commit\/33096a61daa96392bd350dd78346f0c1920203e1\">Bug #35665076<\/a> \u3068\u3057\u3066\u8a8d\u3081\u3089\u308c\u305f\u3088\u3046\u3067\u3001MySQL 8.4.1 \u3067 fix \u3055\u308c\u307e\u3057\u305f\u30028.4.1\u4ee5\u964d\u306eMySQL\u306b\u4ed8\u5c5e\u3059\u308bmysqldump\u3092\u4f7f\u3048\u3070\u3001\u3053\u306e\u554f\u984c\u306f\u56de\u907f\u3067\u304d\u307e\u3059\u3002<\/p>\n<p>\u4e00\u65b9\u3001FLUSH ERROR LOGS \u3084 mysqladmin flush-logs slow \u3067GTID\u304c\u751f\u6210\u3055\u308c\u308b\u554f\u984c\u306b\u3064\u3044\u3066\u306f\u3001<\/p>\n<p><a href=\"https:\/\/bugs.mysql.com\/bug.php?id=88720\">MySQL Bugs: #88720: Inconsistent and unsafe FLUSH behavior in terms of replication<\/a><\/p>\n<p>\u30672017\u5e74\u306bPercona\u306e\u65b9\u304c\u30d0\u30b0\u30ec\u30dd\u30fc\u30c8\u3057\u3066\u4ee5\u6765\u305d\u306e\u307e\u307e\u3089\u3057\u304f\u3001\u672a\u3060\u306b\u4fee\u6b63\u3055\u308c\u3066\u3044\u307e\u305b\u3093\u3002<\/p>\n<p>\u3053\u308c\u306f\u3082\u3046\u307f\u3093\u306a\u3067 Affects Me \u3057\u3066\u3001MySQL\u306e\u4e2d\u306e\u4eba\u306b\u300c\u56f0\u3063\u3066\u307e\u3059\uff01\u300d\u3068\u4f1d\u3048\u308b\u3057\u304b\u306a\u3044\u3058\u3083\u306a\u3044\u3067\u3059\u304b\u3002<\/p>\n<h2>\u77e5\u3089\u305a\u77e5\u3089\u305a\u306e\u3046\u3061\u306breplica\u3067\u767a\u884c\u3055\u308c\u305fGTID\u3092\u898b\u3064\u3051\u308b\u305f\u3081\u306b\u306f\u3069\u3046\u3057\u305f\u3089\uff1f<\/h2>\n<p>\u3053\u306e\u3088\u3046\u306a\u8a71\u3092\u3059\u308b\u3068\u3001<\/p>\n<p>\u300csuper_read_only=on\u306b\u3057\u3066\u5b89\u5fc3\u3057\u3066\u3044\u305f\u3051\u3069\u3001\u30a6\u30c1\u306eMySQL\u306b\u3082\u3001\u77e5\u3089\u306a\u3044\u3046\u3061\u306breplica\u3067\u751f\u6210\u3055\u308c\u305fGTID\u304c\u3042\u308b\u304b\u3082\u2026\u300d<\/p>\n<p>\u3068\u601d\u3046\u65b9\u3082\u3044\u3089\u3063\u3057\u3083\u308b\u304b\u3082\u3057\u308c\u307e\u305b\u3093\u3002\u79c1\u306f\u521d\u3081\u3066\u77e5\u3063\u305f\u3068\u304d\u305d\u3046\u601d\u3063\u3066\u3001\u3056\u3063\u304f\u308a\u8abf\u67fb\u3059\u308b\u305f\u3081\u306eSQL\u3092\u8003\u3048\u307e\u3057\u305f\u3002<\/p>\n<p>\u81f3\u3063\u3066\u30b7\u30f3\u30d7\u30eb\u3067\u3059\u3002replica\u3067\u6b21\u306eSQL\u3092\u5b9f\u884c\u3057\u3066\u307f\u3066\u304f\u3060\u3055\u3044\u3002<\/p>\n<p><code>SELECT @@GLOBAL.gtid_executed LIKE CONCAT('%', @@server_uuid, '%');<\/code><\/p>\n<p>\u516c\u5f0f\u30c9\u30ad\u30e5\u30e1\u30f3\u30c8\u306e <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/replication-gtids-concepts.html\">17.1.3.1 GTID \u5f62\u5f0f\u304a\u3088\u3073\u683c\u7d0d<\/a>\u306b\u3042\u308b\u3088\u3046\u306b\u3001\u30c7\u30d5\u30a9\u30eb\u30c8\u3067\u306f<\/p>\n<blockquote><p>\n  GTID \u306f\u5ea7\u6a19\u306e\u30da\u30a2\u3068\u3057\u3066\u8868\u73fe\u3055\u308c\u3001\u6b21\u306b\u793a\u3059\u3088\u3046\u306b\u3001\u30b3\u30ed\u30f3\u6587\u5b57 (:) \u3067\u533a\u5207\u3089\u308c\u307e\u3059\u3002<\/p>\n<p>  GTID = source_id:transaction_id<\/p>\n<p>  source_id \u306f\u767a\u751f\u5143\u30b5\u30fc\u30d0\u30fc\u3092\u8b58\u5225\u3057\u307e\u3059\u3002 \u901a\u5e38\u3001\u3053\u306e\u76ee\u7684\u306b\u306f\u30bd\u30fc\u30b9 server_uuid \u304c\u4f7f\u7528\u3055\u308c\u307e\u3059\u3002\n<\/p><\/blockquote>\n<p>server_uuid\u304c\u542b\u307e\u308c\u307e\u3059\u3002\u5177\u4f53\u7684\u306b\u306f\u6b21\u306e\u3088\u3046\u306b<\/p>\n<pre><code>mysql> SELECT @@server_uuid, @@GLOBAL.gtid_purged, @@GLOBAL.gtid_executed;\n+--------------------------------------+------------------------------------------+------------------------------------------+\n| @@server_uuid                        | @@GLOBAL.gtid_purged                     | @@GLOBAL.gtid_executed                   |\n+--------------------------------------+------------------------------------------+------------------------------------------+\n| fc700b9f-f11c-11f0-91f5-a64484b688e0 | fc700b9f-f11c-11f0-91f5-a64484b688e0:1-4 | fc700b9f-f11c-11f0-91f5-a64484b688e0:1-4 |\n+--------------------------------------+------------------------------------------+------------------------------------------+\n1 row in set (0.01 sec)\n\nmysql> SELECT CONCAT('%', @@server_uuid, '%');\n+----------------------------------------+\n| CONCAT('%', @@server_uuid, '%')        |\n+----------------------------------------+\n| %fc700b9f-f11c-11f0-91f5-a64484b688e0% |\n+----------------------------------------+\n1 row in set (0.00 sec)\n\nmysql> SELECT @@GLOBAL.gtid_executed LIKE CONCAT('%', @@server_uuid, '%');\n+-------------------------------------------------------------+\n| @@GLOBAL.gtid_executed LIKE CONCAT('%', @@server_uuid, '%') |\n+-------------------------------------------------------------+\n|                                                           1 |\n+-------------------------------------------------------------+\n1 row in set (0.01 sec)\n\nmysql><\/code><\/pre>\n<p>\u4f8b\u3048\u3070\u3001MySQL\u306f <a href=\"https:\/\/dev.mysql.com\/doc\/refman\/8.0\/ja\/miscellaneous-functions.html#function_uuid\">UUID()\u95a2\u6570<\/a>\u3067UUID\u3092\u751f\u6210\u3067\u304d\u307e\u3059\u304c\u3001UUID\u3092\u751f\u6210\u3057\u3064\u3064LIKE\u306e\u6bd4\u8f03\u6f14\u7b97\u5b50\u3067\u8a55\u4fa1\u3092\u884c\u3063\u3066\u3082\u3001<code>@@GLOBAL.gtid_executed<\/code> \u306b\u8a72\u5f53\u3059\u308bUUID\u306f\u5b58\u5728\u3057\u306a\u3044\u306e\u3067 0 \u304c\u8fd4\u3063\u3066\u304d\u307e\u3059\u3002<\/p>\n<pre><code>mysql> select UUID();\n+--------------------------------------+\n| UUID()                               |\n+--------------------------------------+\n| bb7d718b-f128-11f0-98d6-a64484b688e0 |\n+--------------------------------------+\n1 row in set (0.00 sec)\n\nmysql> SELECT CONCAT('%', UUID(), '%');\n+----------------------------------------+\n| CONCAT('%', UUID(), '%')               |\n+----------------------------------------+\n| %bf872620-f128-11f0-98d6-a64484b688e0% |\n+----------------------------------------+\n1 row in set (0.00 sec)\n\nmysql> SELECT @@GLOBAL.gtid_executed LIKE CONCAT('%', UUID(), '%');\n+------------------------------------------------------+\n| @@GLOBAL.gtid_executed LIKE CONCAT('%', UUID(), '%') |\n+------------------------------------------------------+\n|                                                    0 |\n+------------------------------------------------------+\n1 row in set (0.01 sec)\n\nmysql><\/code><\/pre>\n<p>replica\u306egtid_executed\u306e\u4e2d\u306b\u3001source_id\u306breplica\u306eserver_uuid\u304c\u542b\u307e\u308c\u3066\u3044\u308b\u3082\u306e\u304c\u3042\u308c\u3070\u3001\u305d\u306eGTID\u306freplica\u3067\u4f5c\u6210\u3055\u308c\u305f\u3082\u306e\u3060\u308d\u3046\u3001\u3068\u3044\u3046\u8a71\u3067\u3059\u3002<\/p>\n<h2>\u304a\u308f\u308a\u306b<\/h2>\n<p>\u9577\u3089\u304fMySQL\u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u3067\u3042\u3063\u305fGTID\u3092\u4f7f\u308f\u306a\u3044replication\u3067\u3042\u308c\u3070\u3001\u3053\u306e\u3088\u3046\u306aFLUSH\u30b9\u30c6\u30fc\u30c8\u30e1\u30f3\u30c8\u306a\u3069\u306b\u60a9\u307e\u3055\u308c\u308b\u3053\u3068\u3082\u306a\u304b\u3063\u305f\u304b\u3068\u601d\u3044\u307e\u3059\u304c\u3001MySQL 9.5.0\u306f\u6d41\u77f3Innovation Release\u3001\u4eca\u307e\u3067\u306e\u524d\u63d0\u304c\u8986\u3063\u3066\u304d\u307e\u3057\u305f\u3002MySQL 9.7.0 LTS\uff08\u4e88\u5b9a\uff09\u3067\u3082\u5f15\u304d\u7d9a\u304dgtid_mode=off\u3084gtid_mode=off_permissive\u304c\u6307\u5b9a\u3067\u304d\u308b\u306a\u3089\u3001\u5f15\u304d\u7d9a\u304dGTID\u3092\u4f7f\u308f\u306a\u3044\u3067\u3084\u3063\u3066\u3044\u3051\u307e\u3059\u304c\u3001\u3053\u3046\u3044\u3063\u305fGTID\u306b\u95a2\u9023\u3059\u308b\u554f\u984c\u306a\u3069\u4eca\u306e\u3046\u3061\u304b\u3089\u8abf\u3079\u3064\u3064\u3001MySQL\u306e\u30c7\u30d5\u30a9\u30eb\u30c8\u306b\u6e96\u62e0\u3057\u3066\u3044\u3051\u308b\u3088\u3046\u3001\u3084\u3063\u3066\u3044\u304d\u305f\u3044\u3082\u306e\u3067\u3059\u3002<\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u3053\u3093\u306b\u3061\u308f\u3002\u305b\u3058\u307e\u3067\u3059\u3002 2025-10-21 \u306b\u30ea\u30ea\u30fc\u30b9\u3055\u308c\u305f MySQL 9.5.0\u3001\u500b\u4eba\u7684\u306b\u306f\u4e45\u3057\u3076\u308a\u306b Innovation Release \u3060\u306a\u3068\u601d\u308f\u3055\u308c\u308b\u5909\u66f4\u304c\u5165\u3063\u3066\u307e\u3057\u305f\u3002 MySQL :: MySQL 9 [&hellip;]<\/p>\n","protected":false},"author":137,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[9],"tags":[17],"class_list":["post-25173","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-info","tag-mysql"],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"_links":{"self":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/25173","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/users\/137"}],"replies":[{"embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/comments?post=25173"}],"version-history":[{"count":11,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/25173\/revisions"}],"predecessor-version":[{"id":25175,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/posts\/25173\/revisions\/25175"}],"wp:attachment":[{"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/media?parent=25173"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/categories?post=25173"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/labs.gree.jp\/blog\/wp-json\/wp\/v2\/tags?post=25173"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}