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.
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 ?)
@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.
@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…
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 in a text editor to delete the initial line containing the number of lines. “