{"id":5651,"date":"2012-10-15T09:50:39","date_gmt":"2012-10-15T07:50:39","guid":{"rendered":"http:\/\/code.openark.org\/blog\/?p=5651"},"modified":"2012-10-15T09:50:39","modified_gmt":"2012-10-15T07:50:39","slug":"thoughts-on-mysql-5-6-new-replication-features","status":"publish","type":"post","link":"https:\/\/code.openark.org\/blog\/mysql\/thoughts-on-mysql-5-6-new-replication-features","title":{"rendered":"Thoughts on MySQL 5.6 new replication features"},"content":{"rendered":"<p>After playing a little bit with MySQL <strong>5.6<\/strong> (RC), and following closely on Giuseppe&#8217;s <a href=\"http:\/\/datacharmer.blogspot.co.il\/2012\/08\/mysql-56-replication-gotchas-and-bugs.html\">MySQL 5.6 replication gotchas (and bugs)<\/a>, I was having some thoughts.<\/p>\n<p>These are shared for a few reasons:<\/p>\n<ul>\n<li>Maybe I didn&#8217;t understand it well, and someone could correct me<\/li>\n<li>Or I understood it well, and my input could be of service to the developers<\/li>\n<li>Or it could be of service to the users<\/li>\n<\/ul>\n<h4>InnoDB tables in mysql schema<\/h4>\n<p>The introduction of InnoDB tables in <strong>mysql<\/strong> makes for crash-safe replication information: the exact replication position (master log file+pos, relay log file+pos etc.) is updated on InnoDB tables; with <strong>innodb_flush_logs_at_trx_commit=1<\/strong> this means replication status is durable and consistent with server data. This is great news!<\/p>\n<p>However, the introduction of InnoDB tables to the mysql schema also breaks some common usage on installation and setup of MySQL servers. You can&#8217;t just drop your <strong>ib_data1<\/strong> file upon dump+restore, since it also contains internal data. Giuseppe outlines the workaround for that.<\/p>\n<p>I was thinking: would it be possible to have a completely different tablespace for MySQL&#8217;s internal InnoDB tables? That could be a single tablespace file (who cares about file-per-table on a few internal tables). And I&#8217;m throwing an idea without being intimate with the internals: you know how it is possible to span the shared tablespace across multiple files, as in:<!--more--><\/p>\n<blockquote>\n<pre>[mysqld]\r\ninnodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend<\/pre>\n<\/blockquote>\n<p>Would it be possible to, for example, force the first file in this setup to be the internal database? It would look like:<\/p>\n<blockquote>\n<pre>[mysqld]\r\ninnodb_data_file_path=<strong>ibdata_internal_do_not_touch<\/strong>:2M;<strong>ibdata1_this_one_is_yours<\/strong>:50M:autoextend<\/pre>\n<\/blockquote>\n<p>Only the user would not have to actually set this thing up: the internal tablespace would be there by default (and always first).<\/p>\n<p>Then we would be able to drop our own table space as much as we would like to, but never touch the internal tablespace. It would always extend into our own <strong>ibdata1<\/strong> file.<\/p>\n<p>I&#8217;m wondering if I&#8217;m making sense at all and if this is possible.<\/p>\n<h4>GTID and settings<\/h4>\n<p>The fact that you have to specify both <strong>gtid_mode=ON<\/strong> as well as <strong>disable-gtid-unsafe-statements<\/strong> is a bit of a bummer. I wouldn&#8217;t mind as much if error messages would be informative. But as it turned out, when I wanted to test GTID I did the following:<\/p>\n<blockquote>\n<pre>mysql&gt; STOP SLAVE;\r\nmysql&gt; change master to MASTER_AUTO_POSITION=1;\r\nERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when GTID_MODE = ON.\r\n\r\n-- OK, setting <strong>gtid_mode=ON<\/strong> in config file, restarting server.\r\n--\r\n-- <strong>Oooops<\/strong>, server won't restart!\r\n-- Getting this error message in log: <strong>\"--gtid-mode=UPGRADE_STEP_1 or --gtid-mode=UPGRADE_STEP_2 are not yet supported\"<\/strong>\r\n-- What?<\/pre>\n<\/blockquote>\n<p>Checking up on Giuseppe&#8217;s post I realized I didn&#8217;t set the <strong>disable-gtid-unsafe-statements<\/strong> param. But this was not mentioned on the above <strong>ERROR 1777<\/strong>, and the log error was quite cryptic.<\/p>\n<p>TODO: just mention this <em>other<\/em> variable.<\/p>\n<h4>GTID, internal InnoDB tables &amp; wreckage<\/h4>\n<p>OK, I managed to completely crash my replication setup. I setup GTID, and then:<\/p>\n<blockquote>\n<pre>set global master_info_repository:='table';\r\nset global relay_log_info_repository='table';<\/pre>\n<\/blockquote>\n<p>Then shut down mysql; I wanted to see how reverting back to <strong>gtid_mode=OFF<\/strong> works. Oh, I didn&#8217;t set the two params in the config file, so their effect was lost.<\/p>\n<p>Starting mysql, I get:<\/p>\n<blockquote>\n<pre>ERROR 1794 (HY000) at line 1: Slave is not configured or failed to initialize properly. You must at least set --server-id to enable either a master or a slave. Additional error messages can be found in the MySQL error log.<\/pre>\n<\/blockquote>\n<p>The error log says:<\/p>\n<blockquote>\n<pre>121015\u00a0 9:38:58 [ERROR] Error creating master info: Multiple replication metadata repository instances found with data in them. Unable to decide which is the correct one to choose.\r\n121015\u00a0 9:38:58 [ERROR] Failed to create or recover replication info repository.\r\n121015\u00a0 9:38:58 [Note] Check error log for additional messages. You will not be able to start replication until the issue is resolved and the server restarted.<\/pre>\n<\/blockquote>\n<p>What&#8217;s interesting is that the data is still in the tables:<\/p>\n<blockquote>\n<pre>mysql&gt; select * from mysql.slave_master_info\\G\r\n*************************** 1. row ***************************\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Number_of_lines: 23\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Master_log_name: mysql-bin.000003\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Master_log_pos: 2623\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Host: 127.0.0.1\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 User_name: rsandbox\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 User_password: rsandbox\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Port: 14701\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Connect_retry: 60\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Enabled_ssl: 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ssl_ca: \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ssl_capath: \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ssl_cert: \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ssl_cipher: \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ssl_key: \r\nSsl_verify_server_cert: 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Heartbeat: 1800\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Bind: \r\n\u00a0\u00a0\u00a0 Ignored_server_ids: 0\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Uuid: 10fa73da-13ac-11e2-bdcd-0024e8cd3122\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Retry_count: 86400\r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ssl_crl: \r\n\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Ssl_crlpath: \r\n\u00a0Enabled_auto_position: 1<\/pre>\n<\/blockquote>\n<p>I&#8217;ve tried restarting, setting variables in the config file, changing them dynamically. To no avail.<\/p>\n<p>No, I haven&#8217;t filed a bug report yet.<\/p>\n<p>These are still my first steps into 5.6 replication and my very first impressions.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>After playing a little bit with MySQL 5.6 (RC), and following closely on Giuseppe&#8217;s MySQL 5.6 replication gotchas (and bugs), I was having some thoughts. These are shared for a few reasons: Maybe I didn&#8217;t understand it well, and someone could correct me Or I understood it well, and my input could be of service [&hellip;]<\/p>\n","protected":false},"author":2,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"jetpack_post_was_ever_published":false,"_jetpack_newsletter_access":"","_jetpack_dont_email_post_to_subs":false,"_jetpack_newsletter_tier_id":0,"_jetpack_memberships_contains_paywalled_content":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":"","jetpack_publicize_message":"","jetpack_publicize_feature_enabled":true,"jetpack_social_post_already_shared":false,"jetpack_social_options":{"image_generator_settings":{"template":"highway","default_image_id":0,"enabled":false},"version":2}},"categories":[5],"tags":[11,14,89,51,8],"class_list":["post-5651","post","type-post","status-publish","format-standard","hentry","category-mysql","tag-configuration","tag-innodb","tag-new-features","tag-opinions","tag-replication"],"jetpack_publicize_connections":[],"jetpack_featured_media_url":"","jetpack_sharing_enabled":true,"jetpack_shortlink":"https:\/\/wp.me\/p2bZZp-1t9","_links":{"self":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5651","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/users\/2"}],"replies":[{"embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/comments?post=5651"}],"version-history":[{"count":8,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5651\/revisions"}],"predecessor-version":[{"id":5659,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/posts\/5651\/revisions\/5659"}],"wp:attachment":[{"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/media?parent=5651"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/categories?post=5651"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/code.openark.org\/blog\/wp-json\/wp\/v2\/tags?post=5651"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}