Not the answer you need?
Register and ask your own question!

Percona xtraDB cluster 5.7 and 8.0.19 collation

DBA100DBA100 Current User Role Patron
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?

Answers

  • vinicius.grippavinicius.grippa Percona Percona Staff Role
    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=utf8
    collation-server=utf8_unicode_ci

    And to check: show global variables like '%char%';
  • DBA100DBA100 Current User Role Patron
    so by saying this,  the new and old percona server has to use the same collation for backup and restore to work ?
  • DBA100DBA100 Current User Role Patron
    edited August 12
    this is our collation

    character_set_client latin1
    character_set_connection latin1
    character_set_database latin1
    character_set_filesystem binary
    character_set_results latin1
    character_set_server latin1
    character_set_system utf8
    character_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 ?


Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.