{"id":5187,"date":"2017-03-29T09:13:34","date_gmt":"2017-03-29T06:13:34","guid":{"rendered":"http:\/\/skeletor.org.ua\/?p=5187"},"modified":"2022-08-26T12:30:39","modified_gmt":"2022-08-26T09:30:39","slug":"mysql-skip-replication-with-gtid","status":"publish","type":"post","link":"https:\/\/skeletor.org.ua\/?p=5187","title":{"rendered":"[mysql] skip replication with GTID"},"content":{"rendered":"

\u0421\u043c\u043e\u0442\u0440\u0438\u043c \u0432<\/p>\n

mysql>show slave status\\G;
\n...
\nExecuted_Gtid_Set: 1136d9c4-0ecf-11e5-be7e-28d2445d8ed0:1-373587249,
\n53d58b0f-eb6f-11e6-9215-392e3ca483a1:1-1081544,
\nbcdd5c11-9d7b-11e2-8070-49715e847bda<\/span>:1-40596661<\/span>
\n...<\/code><\/p>\n

\u0412 \u0434\u0430\u043d\u043d\u043e\u043c \u0441\u043b\u0443\u0447\u0430\u0435 ID<\/strong>-\u0448\u043d\u0438\u043a \u043d\u0435\u043f\u043e\u0441\u0440\u0435\u0434\u0441\u0442\u0432\u0435\u043d\u043d\u043e\u0433\u043e \u043c\u0430\u0441\u0442\u0435\u0440\u0430 bcdd5c11-9d7b-11e2-8070-49715e847bda<\/strong><\/span>. \u0417\u043d\u0430\u0447\u0438\u0442\u00a0gtid_next<\/strong> \u0437\u0430\u0434\u0430\u0451\u0442\u0441\u044f \u0432 \u0444\u043e\u0440\u043c\u0430\u0442\u0435 ID:position<\/strong><\/span><\/em>, \u0430 \u0432 executed_gtid_set<\/strong>\u00a0\u0432\u044b\u0433\u043b\u044f\u0434\u0438\u0442 \u043a\u0430\u043a ID:1-position<\/strong><\/span><\/em>:<\/p>\n

<\/p>\n

mysql> stop slave;
\nmysql> set gtid_next=\"bcdd5c11-9d7b-11e2-8070-49715e847bda<\/span>:40596661<\/span>\";
\nQuery OK, 0 rows affected (0.00 sec)<\/code><\/p>\n

\u0432\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u043c \u043f\u0443\u0441\u0442\u0443\u044e \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044e \u0438 \u0432\u044b\u0441\u0442\u0430\u0432\u043b\u044f\u0435\u043c NEXT POSITION<\/strong> \u0432 AUTO<\/strong>, \u043f\u043e\u0441\u043b\u0435 \u0441\u0442\u0430\u0440\u0442\u0443\u0435\u043c slave<\/strong>:<\/p>\n

mysql> begin; commit;
\nmysql> set gtid_next = \"AUTOMATIC\";
\nmysql> start slave;<\/code><\/p>\n

\u041f\u0440\u0438\u043c\u0435\u0447\u0430\u043d\u0438\u0435.<\/strong><\/em><\/span><\/p>\n

\u041f\u043e\u043a\u0430\u0436\u0435\u043c \u044d\u0442\u043e \u043d\u0430 \u0440\u0435\u0430\u043b\u044c\u043d\u043e\u043c \u043f\u0440\u0438\u043c\u0435\u0440\u0435. \u0414\u043e\u043f\u0443\u0441\u0442\u0438\u043c, \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044f \u043e\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u043b\u0430\u0441\u044c \u0441 \u043e\u0448\u0438\u0431\u043a\u043e\u0439:<\/p>\n

Error 'Operation DROP USER failed for 'repl-user'@'10.10.22.21'' on query. Default database: 'mysql'. Query: 'drop user 'repl-user'@'10.10.22.21''<\/code><\/p>\n

\u041a\u043e\u0433\u0434\u0430 \u0443 \u0432\u0430\u0441 \u0432\u0441\u0435\u0433\u043e \u043e\u0434\u043d\u0430 \u0441\u0432\u044f\u0437\u043a\u0430 \u0441\u0435\u0440\u0432\u0435\u0440\u043e\u0432 – \u0442\u043e \u0434\u0435\u043b\u0430\u0439\u0442\u0435 \u043a\u0430\u043a \u0432 \u043e\u0431\u044a\u044f\u0441\u043d\u0435\u043d\u0438\u0438 \u0432\u044b\u0448\u0435. \u041d\u043e \u043a\u043e\u0433\u0434\u0430 \u0443 \u0432\u0430\u0441 \u043a\u0430\u0441\u043a\u0430\u0434\u043d\u0430\u044f \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044f + \u043d\u0435\u0441\u043a\u043e\u043b\u044c\u043a\u043e source’\u043e\u0432, \u0438 \u0432 \u0438\u0442\u043e\u0433\u0435 Executed_Gtid_Set<\/strong> \u0432\u044b\u0433\u043b\u044f\u0434\u0438\u0442 \u043a\u0430\u043a-\u0442\u043e \u0442\u0430\u043a:<\/p>\n

Executed_Gtid_Set: c1e6c877-0b28-11e8-ac90-00259029542c:1-10244393,
\na9445ca7-0c35-31e8-9813-74e0078275c5:1-2632716216,
\nb6ed606b-233e-e394-9344-3424ac29df21:1-723531812,
\nd2bcb314-0031-12ab-bd34-836473a34c3a:1-62548120<\/code><\/p>\n

\u041a\u0430\u043a \u043f\u043e\u043d\u044f\u0442\u044c, \u0447\u0442\u043e \u043f\u0438\u0441\u0430\u0442\u044c \u0432 gtid_next<\/strong>? \u041e\u0447\u0435\u043d\u044c \u043f\u0440\u043e\u0441\u0442\u043e:<\/p>\n

1) \u043e\u0442\u043a\u0440\u044b\u0432\u0430\u0435\u043c relay-log<\/strong> \u043d\u0430 \u0441\u043b\u0435\u0439\u0432\u0435, \u043a\u043e\u0442\u043e\u0440\u044b\u0439 \u043e\u0442\u0432\u0435\u0447\u0430\u0435\u0442 \u0442\u043e\u043c\u0443 \u043a\u0430\u043d\u0430\u043b\u0443 \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u0438, \u043d\u0430 \u043a\u043e\u0442\u043e\u0440\u043e\u043c \u043e\u043d\u0430 \u043e\u0441\u0442\u0430\u043d\u043e\u0432\u0438\u043b\u0430\u0441\u044c
\n2) \u0438\u0449\u0435\u043c \u0441\u0430\u043c \u0437\u0430\u043f\u0440\u043e\u0441 \u0438 \u043f\u043e\u0434\u043d\u0438\u043c\u0430\u0435\u043c\u0441\u044f \u0447\u0443\u0442\u044c \u0432\u044b\u0448\u0435 \u0434\u043e \u043f\u0435\u0440\u0432\u043e\u0433\u043e SET @@SESSION.GTID_NEXT<\/strong> ; \u0432\u044b\u0433\u043b\u044f\u0434\u0438\u0442 \u044d\u0442\u043e \u043a\u0430\u043a-\u0442\u043e \u0442\u0430\u043a:<\/p>\n

#180206  9:07:35 server id 51  end_log_pos 525244062 CRC32 0x244fb3fd   GTID    last_committed=771256   sequence_number=771257\nSET @@SESSION.GTID_NEXT= 'a9445ca7-0c35-31e8-9813-74e0078275c5:2632716217'\/*!*\/;\n# at 525244307\n#180206  9:07:35 server id 51  end_log_pos 525244186 CRC32 0xe2b02729   Query   thread_id=16476934      exec_time=0     error_code=0\nuse `mysql`\/*!*\/;\nSET TIMESTAMP=1517908055\/*!*\/;\nSET @@session.auto_increment_increment=2, @@session.auto_increment_offset=2\/*!*\/;\n\/*!\\C utf8 *\/\/*!*\/;\nSET @@session.character_set_client=33,@@session.collation_connection=33,@@session.collation_server=33\/*!*\/;\ndrop user 'repl-user'@'10.10.22.21'\n\/*!*\/;\n<\/pre>\n
3) \u0443\u043a\u0430\u0437\u044b\u0432\u0430\u0435\u043c \u0438\u043c\u0435\u043d\u043d\u043e \u044d\u0442\u043e \u0437\u043d\u0430\u0447\u0435\u043d\u0438\u0435, \u0434\u0435\u043b\u0430\u0435\u043c \u043f\u0443\u0441\u0442\u0443\u044e \u0442\u0440\u0430\u043d\u0437\u0430\u043a\u0446\u0438\u044e \u0438 \u0437\u0430\u043f\u0443\u0441\u043a\u0430\u0435\u043c \u0440\u0435\u043f\u043b\u0438\u043a\u0430\u0446\u0438\u044e:<\/p>\n
mysql> stop slave for channel 'repl1';set gtid_next='a9445ca7-0c35-31e8-9813-74e0078275c5:2632716217<\/span>';begin; commit;set gtid_next = \"AUTOMATIC\";start slave for channel 'repl1';<\/code><\/p>\n
\u041c\u043e\u0436\u043d\u043e \u0442\u0430\u043a \u0436\u0435 \u0432\u043e\u0441\u043f\u043e\u043b\u044c\u0437\u043e\u0432\u0430\u0442\u044c\u0441\u044f mysql-utilites<\/a>, \u0430 \u0442\u043e\u0447\u043d\u0435\u0435\u00a0mysqlslavetrx<\/a>:<\/p>\n
# mysqlslavetrx --slaves={USER}:{PASS}@localhost --dryrun --gtid-set=bcdd5c11-9d7b-11e2-8070-49715e847bda:69294946<\/code><\/p>\n
\u043d\u043e \u043f\u0440\u0438 \u044d\u0442\u043e\u043c \u043d\u0443\u0436\u043d\u043e \u0434\u043e\u043f\u043e\u043b\u043d\u0438\u0442\u0435\u043b\u044c\u043d\u043e \u0432\u044b\u043f\u043e\u043b\u043d\u0438\u0442\u044c stop\/start slave \u043f\u0435\u0440\u0435\u0434\/\u043f\u043e\u0441\u043b\u0435.<\/p>\n","protected":false},"excerpt":{"rendered":"
\u0421\u043c\u043e\u0442\u0440\u0438\u043c \u0432 mysql>show slave status\\G; … Executed_Gtid_Set: 1136d9c4-0ecf-11e5-be7e-28d2445d8ed0:1-373587249, 53d58b0f-eb6f-11e6-9215-392e3ca483a1:1-1081544, bcdd5c11-9d7b-11e2-8070-49715e847bda:1-40596661 … \u0412 \u0434\u0430\u043d\u043d\u043e\u043c \u0441\u043b\u0443\u0447\u0430\u0435 ID-\u0448\u043d\u0438\u043a \u043d\u0435\u043f\u043e\u0441\u0440\u0435\u0434\u0441\u0442\u0432\u0435\u043d\u043d\u043e\u0433\u043e \u043c\u0430\u0441\u0442\u0435\u0440\u0430 bcdd5c11-9d7b-11e2-8070-49715e847bda. \u0417\u043d\u0430\u0447\u0438\u0442\u00a0gtid_next \u0437\u0430\u0434\u0430\u0451\u0442\u0441\u044f \u0432 \u0444\u043e\u0440\u043c\u0430\u0442\u0435 ID:position, \u0430 \u0432 executed_gtid_set\u00a0\u0432\u044b\u0433\u043b\u044f\u0434\u0438\u0442 \u043a\u0430\u043a ID:1-position:<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[9],"tags":[],"class_list":["post-5187","post","type-post","status-publish","format-standard","hentry","category-databases"],"_links":{"self":[{"href":"https:\/\/skeletor.org.ua\/index.php?rest_route=\/wp\/v2\/posts\/5187","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/skeletor.org.ua\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/skeletor.org.ua\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/skeletor.org.ua\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/skeletor.org.ua\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=5187"}],"version-history":[{"count":6,"href":"https:\/\/skeletor.org.ua\/index.php?rest_route=\/wp\/v2\/posts\/5187\/revisions"}],"predecessor-version":[{"id":5971,"href":"https:\/\/skeletor.org.ua\/index.php?rest_route=\/wp\/v2\/posts\/5187\/revisions\/5971"}],"wp:attachment":[{"href":"https:\/\/skeletor.org.ua\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=5187"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/skeletor.org.ua\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=5187"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/skeletor.org.ua\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=5187"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}