patternsqlMinor
Should I enable binlog binary logging on slave server?
Viewed 0 times
loggingenableslavebinaryshouldserverbinlog
Problem
What are the benefits of enabling binary logging in MySQL slave server? I'm thinking that doing a switchover from master to slave and back to master would be easier in this way?
Solution
That really depends on the size of your system and the level of automation you're using. What you can do with log_bin and log_slave_updates:
It's not hard job to find the most up-to-date slave promote and determine the position on the other slaves and point them to the new promoted master. There are tools to help your there.
Since MySQL 5.6 and the different flavours of it provides different implementation of GTID which makes it even less painful.
Without binlogs being enabled a restart is required which (again depending on your setup) can take significant time (hours even).
You can take a base backup of your slave with mysqldump, percona xtrabackup or any tool you prefer and make sure you don't run out of binlogs in case of a recovery you can just replay those logs.
In case of statement based replication you can process those binlogs and gather useful statistics about which tables are the most written to, updated etc.
+1 Scalability
Once you reach that point you will already have the tools and configs for it but it worth to be mentioned: you can move your slave around easily, change the topology whenever you feel so.
- Quick failover
It's not hard job to find the most up-to-date slave promote and determine the position on the other slaves and point them to the new promoted master. There are tools to help your there.
Since MySQL 5.6 and the different flavours of it provides different implementation of GTID which makes it even less painful.
Without binlogs being enabled a restart is required which (again depending on your setup) can take significant time (hours even).
- Incremental backups and point in time recovery
You can take a base backup of your slave with mysqldump, percona xtrabackup or any tool you prefer and make sure you don't run out of binlogs in case of a recovery you can just replay those logs.
- Analytical capabilities
In case of statement based replication you can process those binlogs and gather useful statistics about which tables are the most written to, updated etc.
+1 Scalability
Once you reach that point you will already have the tools and configs for it but it worth to be mentioned: you can move your slave around easily, change the topology whenever you feel so.
Context
StackExchange Database Administrators Q#114452, answer score: 3
Revisions (0)
No revisions yet.