MySQL 5.6 GTID Replication – Is log_slave_updates Required?

MySQL

I've read the docs saying log_slave_updates is required. It makes sense for a general promotion situation.

But is that really still required for something you're intending to be a read only slave that will never get promoted?

Best Answer

Yes, you must set it when using gtid, otherwise mysql won't start, providing you the following error:

[ERROR] --gtid-mode=ON or UPGRADE_STEP_1 or UPGRADE_STEP_2 requires 
--log-bin and --log-slave-updates

The slave requires its own executed binary logs in order to auto-position itself in the case of a stop (for example, if it crashes) or a master change (the hardware on the master fails without a possibility of a fix and another slave is promoted as the new slave), even if it is not going to be a master itself never. Although I suppose that can be controlled exclusively in-memory when running, I would bet it needs its own binary logs as a failsafe in case of a crash.

You may think that that is a lot of trouble, but it is a small burden in exchange for not having to deal with binlog files and binlog pos addresses anymore. Also, binary logs on the slave was always a good idea. You can still purge them automatically with expire-logs-days/max-binlog-size if the size is the problem. And with group-commit in 5.6, the performance penalty should be minimal. The other thing why I did not used log_slave_updates before (checking that no writes have been done on the slaves) can now be controlled with gtid_executed values.

In a nutshell, set it up, and do not worry on most cases.

Related Solutions

MySQL Replication – Introduce new Slave to replication

Is this an issue with the fact now() is being in a query, which common sense tells me would cause issues if the inserts on that table were frequent enough??!?!

Yes, I believe this is the cause of your errors. Your method for introducing the new slave seems to be correct. It is quite strange in my opinion to define a table with a DATETIME field as the Primary Key. As you've quite rightly pointed out, the slave gets the replicated queries from the master and they will use the now() keyword in the queries, which will grab the timestamp from the local server.

Really, the table should be defined with some other data type for the PK (such as INT or BIGINT) which can be guaranteed to be unique, unlike a timestamp inserted with now().

MySql – Read only on slave

Here are the grants you just mentioned

GRANT RELOAD, PROCESS ON *.* TO 'my_app'@'%' IDENTIFIED BY PASSWORD '*'
GRANT ALL PRIVILEGES ON `my_app`.* TO 'my_app'@'%'

Based on this, you should not be able to write to the Slave because SUPER is a system level privilege (SUPER only appears in mysql.user as Super_priv) and not a DB level privilege (SUPER does not appear in mysql.db). The first GRANT lacks SUPER privilege. The second GRANT does not have a SUPER privilege context at all.

Having SUPER privilege has a lot of firepower because you can run the following

CHANGE MASTER TO
KILL
SET GLOBAL
PURGE BINARY LOGS

Not every user needs this. Giving the SUPER privilege to just anyone can actually hamper a DBA from logging into mysql if max_connections is reached.

Best Answer

Related Solutions

MySQL Replication – Introduce new Slave to replication

MySql – Read only on slave

Related Question