hi,
as we are doing percona xtraDB cluster 5.7x to 8.0.19 migration, and we use mysqldump to export data in order to restore to 8.0.19. what if some of our table has chinese characters, when it restore the dump file it will have error message, right?
Anyway to let mysqldump also take care of it in the dump file so that restore is ok ?
this is my mysqldump command:
mysqldump
-uroot -p --host=<DB host we want to back from >
–all-databases --events --routines --triggers --replace
–master-data=2 > <dump_file.sql>
how to make it handle language code/collation per database exported.
and I am wondering how to check my collation of Percona xtraDB cluster 8.0.19 and 5.7.23, any idea? how can I change back the collation of 8.0.19 to match 5.7.23?
You can set the charset before applying the dump.
This is demonstrated on this blog post:
https://www.percona.com/blog/2018/08/07/replicating-mysql-8-0-mysql-5-7/
For example:
[client]default-character-set=utf8
[mysqld]character-set-server=utf8collation-server=utf8_unicode_ci
And to check:
show global variables like ‘%char%’;
so by saying this, the new and old percona server has to use the same collation for backup and restore to work ?
this is our collation
character_set_client latin1character_set_connection latin1character_set_database latin1character_set_filesystem binarycharacter_set_results latin1character_set_server latin1character_set_system utf8character_sets_dir /usr/share/percona-xtradb-cluster/charsets/ft_query_extra_word_chars OFF
so you mean only modify my.cnf with this on each of the cluster nodes and restart the node one by one, then it takes effect? no need to reinstall percona xtraDB cluster?
what should be the my.cnf now ?
can I set it by global variable ? what will be the command ?