/ Register

Want more timely Centmin Mod News Updates?
Become a Member

MariaDB mysqldump recommended parameters for XenForo databases?

Discussion in 'Nginx, PHP-FPM & MariaDB MySQL' started by BigIron, Jul 15, 2019.

Tags:
Previous Thread Next Thread
Loading...
  1. Jul 15, 2019 #1
    BigIron

    BigIron Member

    64
    15
    8
    Sep 18, 2015
    Ratings:
    +19
    Local Time:
    7:58 PM
    I've been looking into the best way use the mysqldump command.

    I've been using this for the past few years without issue but after reading this thread about XenForo character issues I'm revisiting it.

    Code:
    mysqldump -uroot -p --single-transaction --skip-lock-tables db_name > ~/db_name-$(date +%Y.%m.%d).sql
    In the aforementioned thread @eva2000 mentioned adding --default-character-set=utf8mb4 to overcome the character issue. On the Centmin Mod SQL Page he mentions using this command with far more options for backups.

    Code:
    mysqldump -Q -K --max_allowed_packet=256M --net_buffer_length=65536 --routines --events --triggers --hex-blob -u mysqlusername -p mysqldatabasename > /path/to/mysqldatabasename_backup_date.sql
    Hoping to get some clarification on the best way to do this! :)
     
  2. Jul 15, 2019 #2
    eva2000

    eva2000 Administrator Staff Member

    58,893
    12,490
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,122
    Local Time:
    12:58 PM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    That's the tricky part, proper mysqldump usage does require some knowledge from end user i.e. if you know your database has utf8mb4 character set and collations in your MySQL database i.e. Xenforo 2 usage, then you need to append --default-character-set=utf8mb4 to mysqldump commands. Same if you have non-english and non-utf8 character set/collations, you'd need to use the equivalent character set and collations as outlined at
    Otherwise, 99% of English based sites and MySQL databases don't need to set it if their MySQL server defaults to UTF8 character set and collation. FYI, Centmin Mod defaults MariaDB MySQL 10.x to UTF8 character set and collation so mysqldump will dump using that.

    Centmin Mod Premium users have access to dbbackup.sh (as well as borgbackups.sh) in private Premium members forum which is MySQL backup script which does smarter per database character set and collation handling - on mysqldump by inspecting the MySQL database's character set and collations and automatically using the most appropriate one for 99% of cases differentiating mainly between utf8 vs utf8mb4 and non-utf8.
    FYI, I don't know of any existing MySQL database backup script/service or control panel that does what dbbackup.sh does for conditional smarter character set and collation based mysqldump backups. Most will use whatever is the MySQL server default character set and collations set in /etc/my.cnf defaults which depending on MySQL fork/version can either default to latin1, utf8 or utf8mb4. You may say just use utf8mb4 defaults but you'd have to be aware of running into MySQL maximum index length limitations and know how to get around those. Centmin Mod's MariaDB 10.x MySQL defaults have been tailored to workaround some of these limitations too
     
  3. Jul 16, 2019 #3
    Sunka

    Sunka Well-Known Member

    1,150
    325
    83
    Oct 31, 2015
    Pula, Croatia
    Ratings:
    +525
    Local Time:
    4:58 AM
    Nginx 1.17.9
    MariaDB 10.3.22
    I use this
    Code:
    /usr/bin/mysqldump --opt xenforo --default-character-set=utf8mb4 > /home/nginx/domains/path_to_your_backup_folder/ssh_backup_`date +%d-%m-%Y---%H-%M`.sql
     
  4. Jul 16, 2019 #4
    eva2000

    eva2000 Administrator Staff Member

    58,893
    12,490
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,122
    Local Time:
    12:58 PM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    Yeah thinking about this more - been thinking about for new 123.09beta01 installs to set MariaDB 10.3 to default to utf8mb4 instead of utf8 so folks don't run into issues with mysqldump and 3rd party MySQL backup scripts which blindly do backups without taking into account the underlying character set and collation of the database.

    Curious what folks thoughts are and experiences with setting MariaDB 10.3 to default to utf8mb4 instead of utf8 with the possibility still of running into MySQL innodb max index length errors though MariaDB - Index column size too large. The maximum column size is 767 bytes. ?

    MariaDB Setting Character Sets and Collations

    @Matt @BamaStangGuy @rdan @BoostN @deltahf @Xon @wmtech @Revenge @Jimmy @elargento @jscott @pamamolf @EckyBrazzz @gabel @Jcats if any folks have opinions or experiences ? :)
     
  5. Jul 16, 2019 #5
    eva2000

    eva2000 Administrator Staff Member

    58,893
    12,490
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,122
    Local Time:
    12:58 PM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
  6. Jul 16, 2019 #6
    wmtech

    wmtech Active Member

    187
    44
    28
    Jul 22, 2017
    Ratings:
    +139
    Local Time:
    4:58 AM
    We switched our MariaDB 10.3 back to UTF-8 because of the index column size error. But we also don't need utf8mb4. ;-)
     
  7. Jul 19, 2019 #7
    eva2000

    eva2000 Administrator Staff Member

    58,893
    12,490
    113
    May 24, 2014
    Brisbane, Australia
    Ratings:
    +19,122
    Local Time:
    12:58 PM
    Nginx 1.31.x
    MariaDB 10.x/11.4+/12.3+
    yeah that is my primary concern with utf8mb4 defaults vs utf8 existing defaults
     
Previous Thread Next Thread
Loading...

.