MySQL 8.0 to MySQL 5.7 backward replication

MIN KIM
MIN KIM

Categories

Tags

MySQL 8.0 to MySQL 5.7 backward replication

Officially backward replication is not supported.

However, we still need rollback plan.

Failure main reason

The default value of the collation_server and collation_database system variables has changed from latin1_swedish_ci to utf8mb4_0900_ai_ci.

  • You will get error, if you tried to replicate from MySQL 8.0 to MySQL 5.7 
    Last_SQL_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/home1/mysql/share/charsets/Index.xml' file' on query. Default database: 'test'. Query: 'BEGIN'
  • What is “Character set ‘#255’”?
    • It means utf8mb4_0900_ai_ci
    • MySQL writes character_set_client, collation_connection, collation_server inforamation on binary log. 
      # at 506176532
#201103 12:49:38 server id 544615150  end_log_pos 506176615 CRC32 0xcd189591    Query   thread_id=1982497       exec_time=0     error_code=0
SET TIMESTAMP=1604375378/*!*/;
SET @@session.pseudo_thread_id=1982497/*!*/;
SET @@session.foreign_key_checks=1, @@session.sql_auto_is_null=0, @@session.unique_checks=1, @@session.autocommit=1/*!*/;
SET @@session.sql_mode=1142947840/*!*/;
SET @@session.auto_increment_increment=1, @@session.auto_increment_offset=1/*!*/;
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=46/*!*/;  ------<<<< HERE
SET @@session.time_zone='+09:00'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;

Workaround : Avoid collation incompatibility

1. If your data doesn’t contain UTF8MB4 character, set the collation and the character set to utf8

  • Please refer to https://www.percona.com/blog/2018/08/07/replicating-mysql-8-0-mysql-5-7/

2. SET NAMES utf8mb4 COLLATE {mysql5.7_awareable_collation}

MySQL 8.0 binary log test
  • MySQL 8.0 with default collation_connection=utf8mb4_0900_ai_ci
mysql>show variables like 'coll%';
+----------------------+--------------------+
| Variable_name        | Value              |
+----------------------+--------------------+
| collation_connection | utf8mb4_0900_ai_ci |
| collation_database   | utf8mb4_bin        |
| collation_server     | utf8mb4_bin        |
+----------------------+--------------------+
3 rows in set (0.01 sec)


mysql>insert into test.t1 values(now());
Query OK, 1 row affected (0.01 sec)
  • Binlog 
    ...
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=255,@@session.collation_connection=255,@@session.collation_server=46/*!*/; -----<<<< HERE
SET @@session.time_zone='+09:00'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=45*//*!*/;
BEGIN
/*!*/;
# at 506181545
#201104 16:32:51 server id 544615150  end_log_pos 506181602 CRC32 0x52556f8d    Rows_query
# insert into test.t1 values(now())
...
  • MySQL 8.0 with collation_connection=utf8mb4_bin or utf8mb4_general_ci
mysql> SET NAMES utf8mb4 COLLATE utf8mb4_bin; ------<<<< HERE
Query OK, 0 rows affected (0.01 sec)

mysql>show variables like 'coll%';
+----------------------+-------------+
| Variable_name        | Value       |
+----------------------+-------------+
| collation_connection | utf8mb4_bin |
| collation_database   | utf8mb4_bin |
| collation_server     | utf8mb4_bin |
+----------------------+-------------+
3 rows in set (0.00 sec)

mysql>insert into test.t1 values(now());
Query OK, 1 row affected (0.01 sec)
  • Binlog 
    ...
/*!\C utf8mb4 *//*!*/;
SET @@session.character_set_client=46,@@session.collation_connection=46,@@session.collation_server=46/*!*/;  ------<<<< HERE
SET @@session.time_zone='+09:00'/*!*/;
SET @@session.lc_time_names=0/*!*/;
SET @@session.collation_database=DEFAULT/*!*/;
/*!80011 SET @@session.default_collation_for_utf8mb4=45*//*!*/;
BEGIN
/*!*/;
# at 506181881
#201104 16:37:08 server id 544615150  end_log_pos 506181938 CRC32 0x75831250    Rows_query
# insert into test.t1 values(now())
  • character_set_client=46 : utf8mb4_bin
  • character_set_client=45 : utf8mb4_general_ci
MySQL 8.0 to 5.7 replication

You can successfully replicate from MySQL 8.0 to MySQL 5.7 with following configuration.

  • MySQL 8.0, my.cnf 
    character_set_server= utf8mb4
collation-server = utf8mb4_bin
init_connect='SET NAMES utf8mb4 COLLATE utf8mb4_bin'
skip_character_set_client_handshake

NOTE: SUPER user do not execute init_connect. If you make change with SUPER user, you need to execute ‘SET NAMES utf8mb4 COLLATE utf8mb4_bin’ before making changes.

LinkedIn Reddit