以前通过 家里ADSL上网无固定外网IP的群晖NAS安全实现与公网MySQL服务器主从同步 配置之后，群晖自带的 MariaDB 10.3.21 可以非常流畅的与服务器上的 MySQL 7.x 版本进行主从同步。
前两天系统从 ubuntu 18.04 升级到 ubuntu 20.04 (MySQL 8.x)之后，发现已经无法进行主从同步。
报告如下错误：
$ mysql -u root -p -e "show slave status\G;"
Enter password:
*************************** 1. row ***************************
Slave_IO_State:
Master_Host: 10.8.0.1
Master_User: repl1
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001242
Read_Master_Log_Pos: 8350259
Relay_Log_File: mysqld10-relay-bin.000510
Relay_Log_Pos: 424
Relay_Master_Log_File: mysql-bin.001222
Slave_IO_Running: No
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 22
Last_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/local/mariadb10/share/mysql/charsets/Index.xml' file' on query. Default database: 'wordpress'. Query: 'BEGIN'
Skip_Counter: 0
Exec_Master_Log_Pos: 125
Relay_Log_Space: 137841279
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: /var/packages/MariaDB10/etc/ssl/ca.pem
Master_SSL_CA_Path: /var/packages/MariaDB10/etc/ssl
Master_SSL_Cert: /var/packages/MariaDB10/etc/ssl/client-cert.pem
Master_SSL_Cipher:
Master_SSL_Key: /var/packages/MariaDB10/etc/ssl/client-key.pem
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 22
Last_SQL_Error: Error 'Character set '#255' is not a compiled character set and is not specified in the '/usr/local/mariadb10/share/mysql/charsets/Index.xml' file' on query. Default database: 'wordpress'. Query: 'BEGIN'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 1
Master_SSL_Crl: /var/packages/MariaDB10/etc/ssl/ca.pem
Master_SSL_Crlpath: /var/packages/MariaDB10/etc/ssl
Using_Gtid: No
Gtid_IO_Pos:
Replicate_Do_Domain_Ids:
Replicate_Ignore_Domain_Ids:
Parallel_Mode: conservative
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State:
Slave_DDL_Groups: 0
Slave_Non_Transactional_Groups: 0
Slave_Transactional_Groups: 0
网上查询很久，找到原因 MySQL 8.016 master with MariaDB 10.2 slave on AWS RDS, Character set '#255' is not a compiled character set
This could be a serious problem when replicating between MySQL 8.0 and MariaDB 10.x.
The default (for good technical reasons) `COLLATION` for the 8.0 is `utf8mb4_0900_ai_ci`. Note the "255" associated with it. MariaDB has not yet adopted the Unicode 9.0 collations.
Furthermore, Oracle (MySQL 8.0) did a major rewrite of the collation code, thereby possibly making collation tables incompatible.
A probable fix is to switch to the next best general-purpose collation, `utf8mb4_unicode_520_ci` (246) (based on Unicode 5.20). This would require `ALTERing` all the columns' collations. `ALTER TABLE .. CONVERT TO ..` might be the fastest way. Those could be generated via a `SELECT .. information_schema.tables ...`.
大致原因就是MySql 8.0默认使用了最新的utf8mb4_0900_ai_ci字符集，然而MariaDB 10.3.x版本不支持这个字符集，导致无法同步。
8.0.17:
mysql> show collation like 'utf8mb4%'; +----------------------------+---------+-----+---------+----------+---------+---------------+ | Collation | Charset | Id | Default | Compiled | Sortlen | Pad_attribute | +----------------------------+---------+-----+---------+----------+---------+---------------+ | utf8mb4_0900_ai_ci | utf8mb4 | 255 | Yes | Yes | 0 | NO PAD | | utf8mb4_0900_as_ci | utf8mb4 | 305 | | Yes | 0 | NO PAD | | utf8mb4_0900_as_cs | utf8mb4 | 278 | | Yes | 0 | NO PAD | | utf8mb4_0900_bin | utf8mb4 | 309 | | Yes | 1 | NO PAD | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | PAD SPACE | | utf8mb4_croatian_ci | utf8mb4 | 245 | | Yes | 8 | PAD SPACE | | utf8mb4_cs_0900_ai_ci | utf8mb4 | 266 | | Yes | 0 | NO PAD | | utf8mb4_cs_0900_as_cs | utf8mb4 | 289 | | Yes | 0 | NO PAD | | utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | PAD SPACE | | utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | PAD SPACE | | utf8mb4_da_0900_ai_ci | utf8mb4 | 267 | | Yes | 0 | NO PAD | | utf8mb4_da_0900_as_cs | utf8mb4 | 290 | | Yes | 0 | NO PAD | | utf8mb4_de_pb_0900_ai_ci | utf8mb4 | 256 | | Yes | 0 | NO PAD | | utf8mb4_de_pb_0900_as_cs | utf8mb4 | 279 | | Yes | 0 | NO PAD | | utf8mb4_eo_0900_ai_ci | utf8mb4 | 273 | | Yes | 0 | NO PAD | | utf8mb4_eo_0900_as_cs | utf8mb4 | 296 | | Yes | 0 | NO PAD | | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | PAD SPACE | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | PAD SPACE | | utf8mb4_es_0900_ai_ci | utf8mb4 | 263 | | Yes | 0 | NO PAD | | utf8mb4_es_0900_as_cs | utf8mb4 | 286 | | Yes | 0 | NO PAD | | utf8mb4_es_trad_0900_ai_ci | utf8mb4 | 270 | | Yes | 0 | NO PAD | | utf8mb4_es_trad_0900_as_cs | utf8mb4 | 293 | | Yes | 0 | NO PAD | | utf8mb4_et_0900_ai_ci | utf8mb4 | 262 | | Yes | 0 | NO PAD | | utf8mb4_et_0900_as_cs | utf8mb4 | 285 | | Yes | 0 | NO PAD | | utf8mb4_general_ci | utf8mb4 | 45 | | Yes | 1 | PAD SPACE | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | PAD SPACE | | utf8mb4_hr_0900_ai_ci | utf8mb4 | 275 | | Yes | 0 | NO PAD | | utf8mb4_hr_0900_as_cs | utf8mb4 | 298 | | Yes | 0 | NO PAD | | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | PAD SPACE | | utf8mb4_hu_0900_ai_ci | utf8mb4 | 274 | | Yes | 0 | NO PAD | | utf8mb4_hu_0900_as_cs | utf8mb4 | 297 | | Yes | 0 | NO PAD | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | PAD SPACE | | utf8mb4_is_0900_ai_ci | utf8mb4 | 257 | | Yes | 0 | NO PAD | | utf8mb4_is_0900_as_cs | utf8mb4 | 280 | | Yes | 0 | NO PAD | | utf8mb4_ja_0900_as_cs | utf8mb4 | 303 | | Yes | 0 | NO PAD | | utf8mb4_ja_0900_as_cs_ks | utf8mb4 | 304 | | Yes | 24 | NO PAD | | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | PAD SPACE | | utf8mb4_la_0900_ai_ci | utf8mb4 | 271 | | Yes | 0 | NO PAD | | utf8mb4_la_0900_as_cs | utf8mb4 | 294 | | Yes | 0 | NO PAD | | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | PAD SPACE | | utf8mb4_lt_0900_ai_ci | utf8mb4 | 268 | | Yes | 0 | NO PAD | | utf8mb4_lt_0900_as_cs | utf8mb4 | 291 | | Yes | 0 | NO PAD | | utf8mb4_lv_0900_ai_ci | utf8mb4 | 258 | | Yes | 0 | NO PAD | | utf8mb4_lv_0900_as_cs | utf8mb4 | 281 | | Yes | 0 | NO PAD | | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | PAD SPACE | | utf8mb4_pl_0900_ai_ci | utf8mb4 | 261 | | Yes | 0 | NO PAD | | utf8mb4_pl_0900_as_cs | utf8mb4 | 284 | | Yes | 0 | NO PAD | | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | PAD SPACE | | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | PAD SPACE | | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | PAD SPACE | | utf8mb4_ro_0900_ai_ci | utf8mb4 | 259 | | Yes | 0 | NO PAD | | utf8mb4_ro_0900_as_cs | utf8mb4 | 282 | | Yes | 0 | NO PAD | | utf8mb4_ru_0900_ai_ci | utf8mb4 | 306 | | Yes | 0 | NO PAD | | utf8mb4_ru_0900_as_cs | utf8mb4 | 307 | | Yes | 0 | NO PAD | | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | PAD SPACE | | utf8mb4_sk_0900_ai_ci | utf8mb4 | 269 | | Yes | 0 | NO PAD | | utf8mb4_sk_0900_as_cs | utf8mb4 | 292 | | Yes | 0 | NO PAD | | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | PAD SPACE | | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | PAD SPACE | | utf8mb4_sl_0900_ai_ci | utf8mb4 | 260 | | Yes | 0 | NO PAD | | utf8mb4_sl_0900_as_cs | utf8mb4 | 283 | | Yes | 0 | NO PAD | | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | PAD SPACE | | utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | PAD SPACE | | utf8mb4_sv_0900_ai_ci | utf8mb4 | 264 | | Yes | 0 | NO PAD | | utf8mb4_sv_0900_as_cs | utf8mb4 | 287 | | Yes | 0 | NO PAD | | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | PAD SPACE | | utf8mb4_tr_0900_ai_ci | utf8mb4 | 265 | | Yes | 0 | NO PAD | | utf8mb4_tr_0900_as_cs | utf8mb4 | 288 | | Yes | 0 | NO PAD | | utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | PAD SPACE | | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | PAD SPACE | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | PAD SPACE | | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | PAD SPACE | | utf8mb4_vi_0900_ai_ci | utf8mb4 | 277 | | Yes | 0 | NO PAD | | utf8mb4_vi_0900_as_cs | utf8mb4 | 300 | | Yes | 0 | NO PAD | | utf8mb4_zh_0900_as_cs | utf8mb4 | 308 | | Yes | 0 | NO PAD | +----------------------------+---------+-----+---------+----------+---------+---------------+ 75 rows in set (0.01 sec)
MariaDB 10.2.30:
mysql> SHOW COLLATION LIKE 'utf8mb4%'; +------------------------------+---------+------+---------+----------+---------+ | Collation | Charset | Id | Default | Compiled | Sortlen | +------------------------------+---------+------+---------+----------+---------+ | utf8mb4_general_ci | utf8mb4 | 45 | Yes | Yes | 1 | | utf8mb4_bin | utf8mb4 | 46 | | Yes | 1 | | utf8mb4_unicode_ci | utf8mb4 | 224 | | Yes | 8 | | utf8mb4_icelandic_ci | utf8mb4 | 225 | | Yes | 8 | | utf8mb4_latvian_ci | utf8mb4 | 226 | | Yes | 8 | | utf8mb4_romanian_ci | utf8mb4 | 227 | | Yes | 8 | | utf8mb4_slovenian_ci | utf8mb4 | 228 | | Yes | 8 | | utf8mb4_polish_ci | utf8mb4 | 229 | | Yes | 8 | | utf8mb4_estonian_ci | utf8mb4 | 230 | | Yes | 8 | | utf8mb4_spanish_ci | utf8mb4 | 231 | | Yes | 8 | | utf8mb4_swedish_ci | utf8mb4 | 232 | | Yes | 8 | | utf8mb4_turkish_ci | utf8mb4 | 233 | | Yes | 8 | | utf8mb4_czech_ci | utf8mb4 | 234 | | Yes | 8 | | utf8mb4_danish_ci | utf8mb4 | 235 | | Yes | 8 | | utf8mb4_lithuanian_ci | utf8mb4 | 236 | | Yes | 8 | | utf8mb4_slovak_ci | utf8mb4 | 237 | | Yes | 8 | | utf8mb4_spanish2_ci | utf8mb4 | 238 | | Yes | 8 | | utf8mb4_roman_ci | utf8mb4 | 239 | | Yes | 8 | | utf8mb4_persian_ci | utf8mb4 | 240 | | Yes | 8 | | utf8mb4_esperanto_ci | utf8mb4 | 241 | | Yes | 8 | | utf8mb4_hungarian_ci | utf8mb4 | 242 | | Yes | 8 | | utf8mb4_sinhala_ci | utf8mb4 | 243 | | Yes | 8 | | utf8mb4_german2_ci | utf8mb4 | 244 | | Yes | 8 | | utf8mb4_croatian_mysql561_ci | utf8mb4 | 245 | | Yes | 8 | | utf8mb4_unicode_520_ci | utf8mb4 | 246 | | Yes | 8 | <-- | utf8mb4_vietnamese_ci | utf8mb4 | 247 | | Yes | 8 | | utf8mb4_croatian_ci | utf8mb4 | 608 | | Yes | 8 | | utf8mb4_myanmar_ci | utf8mb4 | 609 | | Yes | 8 | | utf8mb4_thai_520_w2 | utf8mb4 | 610 | | Yes | 4 | | utf8mb4_general_nopad_ci | utf8mb4 | 1069 | | Yes | 1 | | utf8mb4_nopad_bin | utf8mb4 | 1070 | | Yes | 1 | | utf8mb4_unicode_nopad_ci | utf8mb4 | 1248 | | Yes | 8 | | utf8mb4_unicode_520_nopad_ci | utf8mb4 | 1270 | | Yes | 8 | +------------------------------+---------+------+---------+----------+---------+ 33 rows in set (0.00 sec)
目前暂时没打算修改主服务器上的数据库，暂时等 MariaDB 更新吧。
参考链接
-
- Character set 'utf8mb4' is not a compiled character set
- 设置MySQL/MariaDB 数据库默认编码为utf8mb4 （推荐）
- 家里ADSL上网无固定外网IP的群晖NAS安全实现与公网MySQL服务器主从同步
- 如何更改MySQL数据库的编码为utf8mb4
- 一次修改多个mysql表 alter multi tables one time for mysql
- How to convert an entire MySQL database characterset and collation to UTF-8?
- MySQL 8.016 master with MariaDB 10.2 slave on AWS RDS, Character set '#255' is not a compiled character set
- 【Mysql-字符集】软件开发没看上去那么简单，从utf8mb4_0900_ai_ci说起
- 8.0.17 bug: unknown collation utf8mb4_0900_ai_ci
- Why is table CHARSET set to utf8mb4 and COLLATION to utf8mb4_unicode_520_ci
- MariaDB versus MySQL: Compatibility
- utf8mb4_0900_ai_ci, MySQL 8.0 => MariaDB 10.3.27
- buildbot upgrade step and MySQL 8.0
- Replication Stopped Due To: Error "'Character set '#255' is not a compiled character set and is not specified in the...." (Doc ID 2748591.1)
- Replicating from MySQL 8.0 to MySQL 5.7
- 数据库utf8mb4_unicode_ci、utf8mb4_general_ci、utf8mb4_0900_ai_ci编码
- Supported Character Sets and Collations Contents Character Sets Collations NO PAD collations Changes See Also Character Sets
- MySQL与MariaDB核心特性比较详细版v1.0（覆盖mysql 8.0/mariadb 10.3，包括优化、功能及维护）