# Replication from percona 8.0.34 to Mariadb 10.232 & Mariadb 10.6

**URL:** <https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830>\
**Category:** Percona XtraBackup\
**Tags:** percona\
**Created:** [July 26, 2024, 8:59am UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830 "2024-07-26T08:59:13Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![anjaneyarajus](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/anjaneyarajus/32/15125_2.png) [@anjaneyarajus](https://forums.percona.com/u/anjaneyarajus)\
**Post date:** [July 26, 2024, 8:59am UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830/1 "2024-07-26T08:59:13Z")

</div>

we are testing the migration from Mariadb to Percona 8.0.34. i was able to setup replication between Mariadb (source) to Percona 8.0.34.

To test rollback plan, I have setup replication between percona8.0.34 to Mariadb 10.2.32 and Mariadb 10.6.16.

try to drop the test database from percona 8.0.34 that was replicated from mariadb 10.2.32

Replication was broken with below error on both Mariadb 10.2.32 and 10.6.16,  
Last\_SQL\_Error: Error ‘Character set ‘#255’ is not a compiled character set and is not specified in the ‘/usr/share/mysql/charsets/Index.xml’ file’ on query. Default database: ‘test’. Query: ‘drop database if exists test’

is there a fix to make replication compatible by changing character set on percona.

character set on 10.2.32  
±---------±----------------------------±--------------------±-------+  
| Charset | Description | Default collation | Maxlen |  
±---------±----------------------------±--------------------±-------+  
| big5 | Big5 Traditional Chinese | big5\_chinese\_ci | 2 |  
| dec8 | DEC West European | dec8\_swedish\_ci | 1 |  
| cp850 | DOS West European | cp850\_general\_ci | 1 |  
| hp8 | HP West European | hp8\_english\_ci | 1 |  
| koi8r | KOI8-R Relcom Russian | koi8r\_general\_ci | 1 |  
| latin1 | cp1252 West European | latin1\_swedish\_ci | 1 |  
| latin2 | ISO 8859-2 Central European | latin2\_general\_ci | 1 |  
| swe7 | 7bit Swedish | swe7\_swedish\_ci | 1 |  
| ascii | US ASCII | ascii\_general\_ci | 1 |  
| ujis | EUC-JP Japanese | ujis\_japanese\_ci | 3 |  
| sjis | Shift-JIS Japanese | sjis\_japanese\_ci | 2 |  
| hebrew | ISO 8859-8 Hebrew | hebrew\_general\_ci | 1 |  
| tis620 | TIS620 Thai | tis620\_thai\_ci | 1 |  
| euckr | EUC-KR Korean | euckr\_korean\_ci | 2 |  
| koi8u | KOI8-U Ukrainian | koi8u\_general\_ci | 1 |  
| gb2312 | GB2312 Simplified Chinese | gb2312\_chinese\_ci | 2 |  
| greek | ISO 8859-7 Greek | greek\_general\_ci | 1 |  
| cp1250 | Windows Central European | cp1250\_general\_ci | 1 |  
| gbk | GBK Simplified Chinese | gbk\_chinese\_ci | 2 |  
| latin5 | ISO 8859-9 Turkish | latin5\_turkish\_ci | 1 |  
| armscii8 | ARMSCII-8 Armenian | armscii8\_general\_ci | 1 |  
| utf8 | UTF-8 Unicode | utf8\_general\_ci | 3 |  
| ucs2 | UCS-2 Unicode | ucs2\_general\_ci | 2 |  
| cp866 | DOS Russian | cp866\_general\_ci | 1 |  
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2\_general\_ci | 1 |  
| macce | Mac Central European | macce\_general\_ci | 1 |  
| macroman | Mac West European | macroman\_general\_ci | 1 |  
| cp852 | DOS Central European | cp852\_general\_ci | 1 |  
| latin7 | ISO 8859-13 Baltic | latin7\_general\_ci | 1 |  
| utf8mb4 | UTF-8 Unicode | utf8mb4\_general\_ci | 4 |  
| cp1251 | Windows Cyrillic | cp1251\_general\_ci | 1 |  
| utf16 | UTF-16 Unicode | utf16\_general\_ci | 4 |  
| utf16le | UTF-16LE Unicode | utf16le\_general\_ci | 4 |  
| cp1256 | Windows Arabic | cp1256\_general\_ci | 1 |  
| cp1257 | Windows Baltic | cp1257\_general\_ci | 1 |  
| utf32 | UTF-32 Unicode | utf32\_general\_ci | 4 |  
| binary | Binary pseudo charset | binary | 1 |  
| geostd8 | GEOSTD8 Georgian | geostd8\_general\_ci | 1 |  
| cp932 | SJIS for Windows Japanese | cp932\_japanese\_ci | 2 |  
| eucjpms | UJIS for Windows Japanese | eucjpms\_japanese\_ci | 3 |  
±---------±----------------------------±--------------------±-------+

on mariadb 10.6.16  
±---------±----------------------------±--------------------±-------+  
| Charset | Description | Default collation | Maxlen |  
±---------±----------------------------±--------------------±-------+  
| big5 | Big5 Traditional Chinese | big5\_chinese\_ci | 2 |  
| dec8 | DEC West European | dec8\_swedish\_ci | 1 |  
| cp850 | DOS West European | cp850\_general\_ci | 1 |  
| hp8 | HP West European | hp8\_english\_ci | 1 |  
| koi8r | KOI8-R Relcom Russian | koi8r\_general\_ci | 1 |  
| latin1 | cp1252 West European | latin1\_swedish\_ci | 1 |  
| latin2 | ISO 8859-2 Central European | latin2\_general\_ci | 1 |  
| swe7 | 7bit Swedish | swe7\_swedish\_ci | 1 |  
| ascii | US ASCII | ascii\_general\_ci | 1 |  
| ujis | EUC-JP Japanese | ujis\_japanese\_ci | 3 |  
| sjis | Shift-JIS Japanese | sjis\_japanese\_ci | 2 |  
| hebrew | ISO 8859-8 Hebrew | hebrew\_general\_ci | 1 |  
| tis620 | TIS620 Thai | tis620\_thai\_ci | 1 |  
| euckr | EUC-KR Korean | euckr\_korean\_ci | 2 |  
| koi8u | KOI8-U Ukrainian | koi8u\_general\_ci | 1 |  
| gb2312 | GB2312 Simplified Chinese | gb2312\_chinese\_ci | 2 |  
| greek | ISO 8859-7 Greek | greek\_general\_ci | 1 |  
| cp1250 | Windows Central European | cp1250\_general\_ci | 1 |  
| gbk | GBK Simplified Chinese | gbk\_chinese\_ci | 2 |  
| latin5 | ISO 8859-9 Turkish | latin5\_turkish\_ci | 1 |  
| armscii8 | ARMSCII-8 Armenian | armscii8\_general\_ci | 1 |  
| utf8mb3 | UTF-8 Unicode | utf8mb3\_general\_ci | 3 |  
| ucs2 | UCS-2 Unicode | ucs2\_general\_ci | 2 |  
| cp866 | DOS Russian | cp866\_general\_ci | 1 |  
| keybcs2 | DOS Kamenicky Czech-Slovak | keybcs2\_general\_ci | 1 |  
| macce | Mac Central European | macce\_general\_ci | 1 |  
| macroman | Mac West European | macroman\_general\_ci | 1 |  
| cp852 | DOS Central European | cp852\_general\_ci | 1 |  
| latin7 | ISO 8859-13 Baltic | latin7\_general\_ci | 1 |  
| utf8mb4 | UTF-8 Unicode | utf8mb4\_general\_ci | 4 |  
| cp1251 | Windows Cyrillic | cp1251\_general\_ci | 1 |  
| utf16 | UTF-16 Unicode | utf16\_general\_ci | 4 |  
| utf16le | UTF-16LE Unicode | utf16le\_general\_ci | 4 |  
| cp1256 | Windows Arabic | cp1256\_general\_ci | 1 |  
| cp1257 | Windows Baltic | cp1257\_general\_ci | 1 |  
| utf32 | UTF-32 Unicode | utf32\_general\_ci | 4 |  
| binary | Binary pseudo charset | binary | 1 |  
| geostd8 | GEOSTD8 Georgian | geostd8\_general\_ci | 1 |  
| cp932 | SJIS for Windows Japanese | cp932\_japanese\_ci | 2 |  
| eucjpms | UJIS for Windows Japanese | eucjpms\_japanese\_ci | 3 |  
±---------±----------------------------±--------------------±-------+

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [July 26, 2024, 2:49pm UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830/2 "2024-07-26T14:49:24Z")

</div>

Hello @anjaneyarajus,

> [@anjaneyarajus](#):
>
> To test rollback plan, I have setup replication between percona8.0.34 to Mariadb 10.2.32 and Mariadb 10.6.16

As you discovered, you cannot replicate this way because MariaDB does not understand the newer UTF character sets and collations found in MySQL 8.0. You will need to ensure that all tables, all columns, are latin1 or are using the generic utf8 and not the specific utf8mb4 found in MySQL 8 by default. You can find several blogs about this.

You are not the first person to experience this issue. For the most part, upgrading to MySQL 8.0 has no rollback plan other than restore from backup. It’s been this way for years. Once you get to 8, you must stay there.

---

<div class="post-metadata">

**Author:** ![anjaneyarajus](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/anjaneyarajus/32/15125_2.png) [@anjaneyarajus](https://forums.percona.com/u/anjaneyarajus)\
**Post date:** [July 28, 2024, 7:23am UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830/3 "2024-07-28T07:23:01Z")

</div>

Hi Mathew,

the tables that are present on percona were replicated from mariadb itself. None of the tables were utf8mb4, they are utf8mb3 after they were replicated to percona.

before loading data from mariadb to percona, if we change below values on percona to values similar mariadb, will the reverse replication work?

character\_set\_client  
character\_set\_connection  
character\_set\_database  
character\_set\_results  
character\_set\_server

---

<div class="post-metadata">

**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\
**Post date:** [July 28, 2024, 7:29am UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830/4 "2024-07-28T07:29:43Z")

</div>

Hi @anjaneyarajus I’ll drag you to MariaDB documentation which should clear any further doubts.

Here: [MariaDB versus MySQL - Compatibility - MariaDB Knowledge Base](https://mariadb.com/kb/en/mariadb-vs-mysql-compatibility/#mysql-80)

> MariaDB Server cannot replicate from a MySQL 8.0 primary server, because MySQL 8.0 has a binary log format that is incompatible.

Thanks,  
K

---

<div class="post-metadata">

**Author:** ![anjaneyarajus](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/anjaneyarajus/32/15125_2.png) [@anjaneyarajus](https://forums.percona.com/u/anjaneyarajus)\
**Post date:** [July 28, 2024, 8:22am UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830/5 "2024-07-28T08:22:40Z")

</div>

This would cause issues when migrating from MariaDB to Percona. For any critical services, we require a rollback plan in case of problems after a few days of cut-over. It will be impossible to rollback a larger database using mysqlump/mydumper, which requires massive downtime.

---

<div class="post-metadata">

**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\
**Post date:** [July 28, 2024, 11:17am UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830/6 "2024-07-28T11:17:30Z")

</div>

That’s right @anjaneyarajus  
…and that’s why you have to be absolutely sure and should do through testing… at times [you need to be tricky](https://www.percona.com/blog/want-to-migrate-from-mariadb-10-4-to-mysql-8-0-but-facing-hurdles-mysql-5-7-to-the-rescue/) handling such migrations.  
If you want a “cutover” you might do a “blue-green” like deployment and rollback would be to point back to the original MariaDB including figuring out how would you get back the writes for the duration.  
TL;DR you have to take a leap of faith in your testing abilities and preparedness

---

<div class="post-metadata">

**Author:** ![matthewb](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/matthewb/32/34_2.png) [@matthewb](https://forums.percona.com/u/matthewb)\
**Post date:** [July 28, 2024, 7:27pm UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830/7 "2024-07-28T19:27:32Z")

</div>

@anjaneyarajus Even within the 8.0 series, you cannot rollback between patch versions. As @kedarpercona has said, you must do all possible testing beforehand. Know that you are ready.

You could put ProxySql between app and db and use proxysql’s mirroring feature to duplicate queries to the different writers.

---

<div class="post-metadata">

**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\
**Post date:** [July 29, 2024, 3:51am UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830/8 "2024-07-29T03:51:59Z")

</div>

and re:proxysql mirroring… I’ve an interesting read

[# How to replicate without binary logs in MySQL](https://kedar.nitty-witty.com/blog/how-to-replicate-without-binary-logs-in-mysql)

---

<div class="post-metadata">

**Author:** ![anjaneyarajus](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/anjaneyarajus/32/15125_2.png) [@anjaneyarajus](https://forums.percona.com/u/anjaneyarajus)\
**Post date:** [July 29, 2024, 6:00am UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830/9 "2024-07-29T06:00:31Z")

</div>

Hi Kedar,

It is an interesting blog; I will definitely check it out.  
Adding a MySQL 5.7 server between Percona and MariaDB to replicate data, will that work?

mysql 8 —\> mysql 5.7 —\> mariadb

---

<div class="post-metadata">

**Author:** ![kedarpercona](https://sea1.discourse-cdn.com/flex019/user_avatar/forums.percona.com/kedarpercona/32/809_2.png) [@kedarpercona](https://forums.percona.com/u/kedarpercona)\
**Post date:** [July 29, 2024, 7:01am UTC](https://forums.percona.com/t/replication-from-percona-8-0-34-to-mariadb-10-232-mariadb-10-6/31830/10 "2024-07-29T07:01:01Z")

</div>

mysql 8 —\> mysql 5.7 → NO.  
mysql 5.7 —\> mariadb → You better test but have no hopes.