MySQL 8.0.3 release candidate is out with amazing new replication features, along with changes in couple of replication default options. Thanks to our users for their constant feedback and valuable inputs, MySQL is changing defaults whenever possible to remove configuration overhead for practical deployment of MySQL server. One such long-awaited requirement from the users was to have server in get-go state for replication. So, finally here it is in MySQL 8.0.3.
Introduction
We can broadly categorize use-cases of MySQL server in following three categories as per usage of binary logs:
-
UseCase#1: Replicate (Use binary log, and do replicate).
It is useful for most of the production installations, since it is the base for implementing HA and Scalability. -
UseCase#2: Use binary log, but do not replicate.
It is useful on smaller deployments where the binary log can be used for debugging or point-in-time recovery.
-
UseCase#3: Do not use binary log, and do not replicate.
It is useful for smaller deployments that need to save space.
Traditionally, MySQL provided UseCase#3 by default. Our long-term goal is to tweak the defaults so that UseCase#1 becomes the no-config mode, because the demand for HA and scalability are high nowadays. In 8.0.3, for the first time, the defaults are set for use case UseCase#2: the binary logging is now enabled! This also removes configuration steps for UseCase#1, and we further removed configuration steps for chained replication. So we made a big step forward towards the goal of enabling scalability and HA by default!
About enabling Replication Chains:
Enabling log-slave-updates on a replica means, the replica writes updates that are received from a master to its own binary logs. So a replica can become a intermediate master of another replica. This option is a stepping stone for various simple to complex level replication setups and new technologies like Group Replication.
Example setups:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
|
1. Linear Chained Replication OR Multi-Level Replication: A -> B -> C -> D ## Here B and C require log_slave_updates enabled. 2. Chained Replication with Multi-Source Replication: A ---, | v B -> D -> E ## Here D requires log_slave_updates enabled. ^ | C ---' 3. Circular Replication: A -> B -> C -> A ## Here all the servers require log_slave_updates enabled. 4. Group Replication: [M1, M2, M3] ## Here all the members require log_slave_updates enabled. Notions used:- A -> B: Master -> Replica [A, B]: [Member1, Member2] in a group. |
Worklogs implemented:
WL#10470 – Defaults: Enable Binary Log (log_bin: ON)
WL#10479 – Defaults: Enable Replication Chains (log_slave_updates: ON)
Configuration
Here is how you can configure the above mentioned three use-cases (UseCase#):
1. To replicate, the only command-line option you need now is server-id. The binary logging is enabled by default. We still recommend that you set –log-bin=<BASENAME>, otherwise it uses the hostname, which a system administrator could potentially change. But, it is no longer strictly required to set any option.
For examples:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
|
$ ./bin/mysqld --server-id=10 ... mysql> select @@hostname; +------------------------+ | @@hostname | +------------------------+ | neptune-ThinkPad-XXXXX | +------------------------+ 1 row in set (0.00 sec) mysql> SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ('log_bin','log_bin_basename','log_bin_index','log_slave_updates', 'server_id'); +-------------------+------------------------------------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------+------------------------------------------------------+ | log_bin | ON | | log_bin_basename | /trunk/var/datadir1/neptune-ThinkPad-XXXXX-bin | | log_bin_index | /trunk/var/datadir1/neptune-ThinkPad-XXXXX-bin.index | | log_slave_updates | ON | | server_id | 10 | +-------------------+------------------------------------------------------+ 5 rows in set (0.00 sec) $ ./bin/mysqld --log-bin=binlog --server-id=10 ... mysql> SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ('log_bin','log_bin_basename','log_bin_index','log_slave_updates', 'server_id'); +-------------------+----------------------------------+ | VARIABLE_NAME | VARIABLE_VALUE | +-------------------+----------------------------------+ | log_bin | ON | | log_bin_basename | /trunk/var/datadir1/binlog | | log_bin_index | /trunk/var/datadir1/binlog.index | | log_slave_updates | ON | | server_id | 10 | +-------------------+----------------------------------+ 5 rows in set (0.00 sec) |
Notes:
– It is a good practice to set –log-bin to specific values on servers. Similarly, –relay-log can be specified too, to some specific values.
2. To have binary log enabled without replication, you don’t need to set anything. We still recommend users to set server-id=1 for such cases, in order to silence a warning that is otherwise printed at startup.
For examples:
|
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
|
$ ./bin/mysqld ... ... (In error log) ... [Warning] [004543] You have not provided a mandatory server-id. Servers in a replication topology must have unique server-ids. Please refer to the proper server start-up parameters documentation. ... mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) $ ./bin/mysqld --server-id=1 ... <No warning at server startup> mysql> select @@server_id; +-------------+ | @@server_id | +-------------+ | 1 | +-------------+ 1 row in set (0.00 sec) |
Notes:
– Warning about providing server-id (if not set) appears regardless of whether it is log-bin or skip-log-bin. This is to ensure that user sets unique server-id even for replicas without a binary log. server-id can be set in the configuration file or even changed dynamically. The recommended way is to set it in the configuration file.
3. To disable binary logs and replication (to save space), set –skip-log-bin or –disable-log-bin, and –server-id=1 (to avoid warning).
|
1
2
3
4
5
6
7
8
9
10
|
$ ./bin/mysqld --skip-log-bin --server-id=1 ... mysql> SELECT * FROM performance_schema.global_variables WHERE VARIABLE_NAME IN ('log_bin','log_bin_basename','log_bin_index'); +------------------+----------------+ | VARIABLE_NAME | VARIABLE_VALUE | +------------------+----------------+ | log_bin | OFF | | log_bin_basename | | | log_bin_index | | +------------------+----------------+ 3 rows in set (0.01 sec) |
Upgrade consideration:
There is a minor upgrade impact to the users who don’t want binary logging. They need to start server with –skip-log-bin or –disable-log-bin, and –server-id=1 (to avoid warning). We are keeping binary logging as an opt-out rather than an opt-in thing from MySQL 8.0.3 onwards.
Conclusion
Most production installations have the binary log enabled as it’s used for replication and point-in-time recovery. Enabling binary logging and replication chains by default means users can start prototyping quickly, and can move from prototype of realistic deployment with minimum hassle. It also means, less configurations for replication (async, semi-sync), group replication, and InnoDB Cluster! 🙂
Stay tuned for more blog posts. Note that MySQL 8.0.3 is a release candidate, thus it is not production ready yet. But, please don’t restrict yourselves from providing us with your valuable feedback. You can get to us through: bug reports, feature requests, the replication mailing list or by leaving a comment on this blog post.
Thanks for using MySQL Replication!