Skip to content

# Thoughts on MySQL 5.6 new replication features

MySQL Configuration InnoDB New Features Opinions Replication October 15, 2012

After playing a little bit with MySQL 5.6 (RC), and following closely on Giuseppe’s MySQL 5.6 replication gotchas (and bugs), I was having some thoughts.

These are shared for a few reasons:

- Maybe I didn’t understand it well, and someone could correct me
- Or I understood it well, and my input could be of service to the developers
- Or it could be of service to the users

#### InnoDB tables in mysql schema

The introduction of InnoDB tables in mysql 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 innodb_flush_logs_at_trx_commit=1 this means replication status is durable and consistent with server data. This is great news!

However, the introduction of InnoDB tables to the mysql schema also breaks some common usage on installation and setup of MySQL servers. You can’t just drop your ib_data1 file upon dump+restore, since it also contains internal data. Giuseppe outlines the workaround for that.

I was thinking: would it be possible to have a completely different tablespace for MySQL’s internal InnoDB tables? That could be a single tablespace file (who cares about file-per-table on a few internal tables). And I’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:

>     [mysqld]
>     innodb_data_file_path=ibdata1:50M;ibdata2:50M:autoextend

Would it be possible to, for example, force the first file in this setup to be the internal database? It would look like:

>     [mysqld]
>     innodb_data_file_path=ibdata_internal_do_not_touch:2M;ibdata1_this_one_is_yours:50M:autoextend

Only the user would not have to actually set this thing up: the internal tablespace would be there by default (and always first).

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 ibdata1 file.

I’m wondering if I’m making sense at all and if this is possible.

#### GTID and settings

The fact that you have to specify both gtid_mode=ON as well as disable-gtid-unsafe-statements is a bit of a bummer. I wouldn’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:

>     mysql> STOP SLAVE;
>     mysql> change master to MASTER_AUTO_POSITION=1;
>     ERROR 1777 (HY000): CHANGE MASTER TO MASTER_AUTO_POSITION = 1 can only be executed when GTID_MODE = ON.

>     -- OK, setting gtid_mode=ON in config file, restarting server.
>     --
>     -- Oooops, server won't restart!
>     -- Getting this error message in log: "--gtid-mode=UPGRADE_STEP_1 or --gtid-mode=UPGRADE_STEP_2 are not yet supported"
>     -- What?

Checking up on Giuseppe’s post I realized I didn’t set the disable-gtid-unsafe-statements param. But this was not mentioned on the above ERROR 1777, and the log error was quite cryptic.

TODO: just mention this other variable.

#### GTID, internal InnoDB tables & wreckage

OK, I managed to completely crash my replication setup. I setup GTID, and then:

>     set global master_info_repository:='table';
>     set global relay_log_info_repository='table';

Then shut down mysql; I wanted to see how reverting back to gtid_mode=OFF works. Oh, I didn’t set the two params in the config file, so their effect was lost.

Starting mysql, I get:

>     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.

The error log says:

>     121015  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.
>     121015  9:38:58 [ERROR] Failed to create or recover replication info repository.
>     121015  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.

What’s interesting is that the data is still in the tables:

>     mysql> select * from mysql.slave_master_info\G
>     *************************** 1. row ***************************
>            Number_of_lines: 23
>            Master_log_name: mysql-bin.000003
>             Master_log_pos: 2623
>                       Host: 127.0.0.1
>                  User_name: rsandbox
>              User_password: rsandbox
>                       Port: 14701
>              Connect_retry: 60
>                Enabled_ssl: 0
>                     Ssl_ca:
>                 Ssl_capath:
>                   Ssl_cert:
>                 Ssl_cipher:
>                    Ssl_key:
>     Ssl_verify_server_cert: 0
>                  Heartbeat: 1800
>                       Bind:
>         Ignored_server_ids: 0
>                       Uuid: 10fa73da-13ac-11e2-bdcd-0024e8cd3122
>                Retry_count: 86400
>                    Ssl_crl:
>                Ssl_crlpath:
>      Enabled_auto_position: 1

I’ve tried restarting, setting variables in the config file, changing them dynamically. To no avail.

No, I haven’t filed a bug report yet.

These are still my first steps into 5.6 replication and my very first impressions.

- Twitter
- LinkedIn
- WhatsApp
- Email
- Facebook
- Print

###
12
Leave a Reply

12 Comment threads
0 Thread replies
0 Followers
Most reacted comment
Hottest comment thread
6 Comment authors
Recent comment authors

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Subscribe
Notify of
Guest
Peter Laursen

I agree that the InnoDB tables (and/or .idb+.frm files for same) are not welcome in the `mysql` database. It may lead to inconsistences like this: http://blog.webyog.com/2012/10/11/bummer/

Guest
Ammon

This was my experience trying out 5.6.6 as well. It seems too easy to paint yourself into horrible little corners where replication status is concerned. I do hope this is made more intuitive, but I imagine it will have to wait until 5.7 or whatever else is next.

Guest
XL

Unfortunately there is no way to separate InnoDB “system” and “user” tables in a way that allows you to drop table spaces for user tables, but not for system tables. The reason is the InnoDB data dictionary and especially how InnoDB is panicking once it finds something that looks like data dictionary corruption. The data dictionary is located in the system table space (if there are multiple ibdata files, it might even be spread about those). Now if you remove an ibd file without touching the DD, then InnoDB will sometimes just refuse to do anything (including deleting the table… Read more »

Author
shlomi

@XL,

Thank you for elaborating. 5.6 presents undo log on its own tablespace. Is this a development change that can allow multiple tablespaces, then?

Guest
Gerry

Could your bug be related to the one I filed a few days ago? See http://bugs.mysql.com/bug.php?id=67246

Further experiments with MySQL 5.6.7-RC: submit your bugs | code.openark.org

[…] Thoughts on MySQL 5.6 new replication features […]

Guest
Avi Vainshtein

Sorry for posting here, but i didn’t find a separate topic for issues about Delayed Replication in 5.6 .

Scenario: Master-Slave replication in 5.6 with a Delay (let’s say 2 hours). The Master fails and isn’t available at all.
The question is : what is the proper way to cause the Slave to execute all the not-yet-applied SQL ?(i.e. to cause SQL_Remaining_Delay to become 0 , without waiting the 2 hours ?)

Author
shlomi

@Avi,

As per http://dev.mysql.com/doc/refman/5.6/en/replication-delayed.html,
just CHANGE MASTER TO MASTER_DELAY=0;
and the slave will just execute whatever it has got in the relay logs.

Guest
Avi Vainshtein

@Shlomi,

This isn’t correct – as per my test.
After the commands:
stop slave;
CHANGE MASTER TO MASTER_DELAY=0;
start slave;
and while the Master is still not available – the IO slave is in “Connecting”State, all the existed before relay logs are deleted, and the SQL changes executed at the Master – were NOT applied at the Slave…

Author
shlomi

The existing relay logs are deleted? Funny/sad this should be the behavior; I haven’t done so myself, so you’re the expert on this. But do try following on the last paragraph in said documentation: ” The relay-log.info file now contains the delay value, so the file format has changed. See Section 16.2.2.2, “Slave Status Logs”. In particular, the first line of the file now indicates how many lines are in the file. If you downgrade a slave server to a version older than MySQL 5.6, the older server will not read the file correctly. To address this, modify the file… Read more »

Author
shlomi

premature submit. Try to rewrite the number in said file (to 0?) while replication is STOPped. I’m curious to learn the answer.

Also try asking on dba.stackexchange.com

Guest
Avi Vainshtein

Thank you, Shlomi, for referring me to dba.stackexchange.com: i’ve got an answer from there, which is :
– stop slave;
– CHANGE MASTER TO MASTER_DELAY = 0, RELAY_LOG_FILE = ‘xxxxx-relay-bin.NNNNNN’, RELAY_LOG_POS = YYYYYYYY ;
(where the RELAY_LOG_FILE and RELAY_LOG_POS values are taken from ‘show slave status;’ output)
– start slave;

I’ve tried that and it worked fine.

As per your suggestion to try relay-log.info file: couldn’t do that because of ‘relay_log_info_repository=TABLE’.

wpDiscuz
Send to Email Address Your Name Your Email Address Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.