galera timezone import

The mysql.time_zone* tables are MyISAM. This causes issues for Galera/Xtrabackup replication. Really, the problem seems to be that the session variables are not properly accessible for the replicated statements. The fix:

  1. Import tz data, but disable the non-functional “workaround” [which mysql_tzinfo_to_sql automatically enables] with sed:
    $ mysql_tzinfo_to_sql /usr/share/zoneinfo |sed -e 's@^SET SESSION wsrep_replicate_myisam=ON;@@g;' |mysql mysql
  2. Export the tz data from the same host you just imported it on:
    $ mysqldump --single-transaction --quick mysql time_zone time_zone_name time_zone_leap_second time_zone_transition time_zone_transition_type >> time_zone_inout.sql
  3. Edit the time_zone_inout.sql file that you just created, to include the following at the top (as the first SQL statement in the file) - NOTE: this requires that your version of galera has session support for the wsrep_replicate_myisam parameter:
    -- enable myiasm replication for the session --
    SET wsrep_replicate_myisam = ON;
  4. Re-imoprt the data on the same node that you performed all the previous steps from:
    mysql mysql < time_zone_inout.sql
  5. Profit!

Extra info

linux/server/galera_timezone_import.txt · Last modified: 2018/02/08 21:29 by john
 
Except where otherwise noted, content on this wiki is licensed under the following license: CC Attribution-Noncommercial-Share Alike 3.0 Unported
Recent changes RSS feed Donate Powered by PHP Valid XHTML 1.0 Valid CSS Driven by DokuWiki