MySQL 5.6 New Replication Features: Webinar followup Q&AStephane Combaudon
I want to thank all attendees of my webinar, “MySQL 5.6 New Replication Features: Benefits, Challenges and Limitations“. We had questions that I didn’t have the time to answer:
Q: If I run on Amazon’s RDS, do I need to worry about enabling crash-safe slaves, or is that already in place?
A: Crash-safe replication is already configured for read replicas using MySQL 5.6.
Q: How the relay log purge will manage in case of multiple db’s replication running on multiple threads?
A: Same thing as with single-threaded replication: when all event in a relay log file have been executed, the relay log is removed. The only difference is that executing the last event of a file no longer means that all previous events have been executed, because execution gaps can happen with multi-threaded replication. However a checkpoint is performed from time to time (see
slave_checkpoint_period setting, default is 300ms) to make sure that no event is left behind. In practice, you should not notice any difference between the purge of relay logs with single-threaded replication and with multi-threaded replication.
Q: How can we make slave only for read purpose similar to AWS RDS MASTER- SLAVE Replication where slave is read only?
A: You can set
read_only = 1 on the slave. The slave won’t be strictly read-only because all users with the SUPER privilege will be able to write to the slave anyway. But if you don’t grant the SUPER privilege to your users, the slave will act as a true read-only server. This is what happened on RDS read replicas: you don’t have the SUPER privilege so you can’t write on replicas.
Q: How can binlog position be higher on a slave compared to the master – for position-based replication?
A: Each time you restart MySQL, a new binary log file will be created. So let’s say that we start a master and its slave (the slave being configured with
log_slave_updates to write events received by replication in its own binary logs), both binary logs will be mysql-bin.0000001. If we now restart the slave twice, the slave will write in mysql-bin.0000003 while the master will still be writing on mysql-bin.0000001.
Q: Can we change statement-based replication to mixed replication after 1000+ transactions or not? What will be the impact?
A: You can easily switch to mixed replication as the
binlog_format setting is a dynamic variable. Mixed replication means using statement-based replication by default and switching to row-based replication only for queries that are not safe for statement-based replication. So it is usually quite safe to switch from statement-based to mixed replication.
Q: server_id and server UUID – what is the difference? Do we need both simultaneously?
server_uuid are both identifiers of each server in a replication topology.
server_id is used by replication and
server_uuid is used to generate GTIDs (
server_uuid has been added in MySQL 5.6). You need both.
Q: How to clarify compatibility of GTID and multi-threaded; is there any plans to fix it by Percona?
A: GTID and multi-threaded replication are compatible. The only concern is that the monitoring tools (
SHOW SLAVE STATUS and the
mysql.slave_relay_log_info table) are confusing when using both. This could make quite hard to fix replication if you have a replication error. We don’t have any plan to fix that at the moment.
Q: What will happen if GTID is used and a transaction that creates a temporary table is run on the master?
enforce-gtid-consistency is set to ON, temporary tables are only allowed if
autocommit = 1. Otherwise this will generate an error.
Q: How could remote binary log be used for backups?
A: It can allow you to mirror the binary logs in real time. However this doesn’t replace backups. You can find an example here.
Q: What is the difference between semi-synchrous replication and gtid replication?
A: By default, replication is asynchronous and doesn’t use GTIDs. Optionally, you can enable semi-sync replication to make sure at least one slave has acknowledged the writes from the master, and you can also enable GTIDs (with or without semi-sync replication) to make replication reconfiguration easier.