Public service announcement: Check your MySQL backup!

[Sunka]

Actually to be on safe side you'd want to do mysql import with

mysql --default-character-set=utf8mb4

as your may have uf8mb4 mysql table and field character set/collations but your mysql client connection my not be utf8mb4 based and mysql server default client might be a different character set and collation from utf8mb4 i.e. utf8

So, for backup database, this should be it?

/usr/bin/mysqldump --opt xenforo --default-character-set=utf8mb4 > /home/nginx/domains/example.com/backup/ssh_database/ssh_backup.sql

But regarding your import setting, would it be like this or...?

mysql --default-character-set=utf8mb4 NewDatabaseName < /home/nginx/domains/example.com/backup/ssh_database/ssh_backup.sql

 

[eva2000]

@Sunka yup

 

[freakyshiat]

just did restore of a db (dumped with utf8mb4) WITHOUT default char set option in import, and it seems to be fine. But I guess its better to use the import char set option just in case?

 

[x3sphere]

Not a charset issue but sorta related -- I was testing some recent backups done with mysqldump and found that I could not restore them properly due to issues with the VARBINARY fields that XF uses.

From what I've gathered, it seems to be recommended to use the --hex-blob option if you have binary data in your database. I tried this and I can now import my backups without any issues.

When dumping tables through some other tools like phpMyAdmin, it seems to use the hex format by default, so not an issue when exporting there. At any rate I'm glad I caught this before I actually had to use one of the backups. The only downside of using hex-blob is that it seems to increase the size of the backups, but since it's only used for smaller fields in XF not an issue really (correct me if I'm wrong on this).

Anyway, just posting this in case anyone else runs into the same issue.

 

[freakyshiat]

Not a charset issue but sorta related -- I was testing some recent backups done with mysqldump and found that I could not restore them properly due to issues with the VARBINARY fields that XF uses.
.....

Anyway, just posting this in case anyone else runs into the same issue.

what kind of issues were you having? I have restored without hex blob and dont have any issues, unless I'm missing something obvious.

 

[x3sphere]

what kind of issues were you having? I have restored without hex blob and dont have any issues, unless I'm missing something obvious.

Without the hex blob option, the dump was just corrupted in some way, I would get duplicate key errors on several of the VARBINARY fields when trying to import it on a different server.

It shouldn't have been an issue with how I transferred the dump either, used rsync and also verified the file checksum.

 

[freakyshiat]

would you mind sharing your dump options?

mine are
--default-character-set=utf8mb4 --single-transaction --skip-lock-tables

I restore a test backup via an automated script every day, for the previous day, and it works fine every time. The restore has no issues, all the data is there and I have verified it. Thanks for sharing your experience.

 

[x3sphere]

would you mind sharing your dump options?

mine are
--default-character-set=utf8mb4 --single-transaction --skip-lock-tables

I restore a test backup via an automated script every day, for the previous day, and it works fine every time. The restore has no issues, all the data is there and I have verified it. Thanks for sharing your experience.

The full command I'm using is:

mysqldump -u user --hex-blob --default-character-set=utf8mb4 --single-transaction database > database.sql

Btw, I've dumped databases before without using hex blob and was able to restore fine, this is the first time I've ran into this. The setup on the new server is different, running a newer version of Ubuntu but the MySQL config is nearly the same. Perhaps I would be able to restore the old backups it was the same exact setup (haven't tested), but that is not good, it should restore fine even on a different system.

 

[freakyshiat]

I am running latest Ubuntu LTS as well with latest mysql.
what is your max_allowed_packet setting for MySQL?

 

[x3sphere]

I am running latest Ubuntu LTS as well with latest mysql.
what is your max_allowed_packet setting for MySQL?

max_allowed_packet = 512M

 

[Bimble]

Can you simply convert existing tables from utf8 to utf8mb4 without any impact?

And would it be a problem for XF1.x if you made the server use utf8mb4 by default?

[mysqld]
character-set-server = utf8mb4
collation-server = utf8mb4_unicode_ci

[mysql]
default-character-set = utf8mb4

 

[alexm]

I've got this on Cpanel:

-- MySQL dump 10.16  Distrib 10.2.22-MariaDB, for Linux (x86_64)
--
-- Host: localhost    Database: #######
-- ------------------------------------------------------
-- Server version    10.2.22-MariaDB

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */;
/*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */;
/*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */;
/*!40101 SET NAMES utf8mb4 */;
/*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */;
/*!40103 SET TIME_ZONE='+00:00' */;
/*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */;
/*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */;
/*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */;
/*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

Does this mean I'm ok?

thanks,

Alex

 

[alexm]

Anyone?

 

[beerForo]

I did an import from vB in 2019 and did a restore to a XF install with preferred settings first. This should be okay because the importer set the charset correct? A visual check of the db shows all tables are utf8mb4_general_ci.

But I believe I have this issue if my backup contains the below correct?

DROP TABLE IF EXISTS `xf_connected_account_provider`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;

So question: What is the best fix? I use a Perl script to run a mysql command, here:

#!/usr/bin/perl

$My_Date=`date +%A`;
chomp ($My_Date);
system ("mysqldump --opt -u *** -p*** *** | /bin/gzip > /***/***/backup_'$My_Date'.sql.gz");

What would I add and where?

And what about our host. They do daily backups. Those are likely safe because they are server images correct?

Thanks!

 

[Rhody]

Thanks for posting this. I was just thinking about switching to UNICODE and glad I saw it.

 

[beerForo]

I did an import from vB in 2019 and did a restore to a XF install with preferred settings first. This should be okay because the importer set the charset correct? A visual check of the db shows all tables are utf8mb4_general_ci.

But I believe I have this issue if my backup contains the below correct?

DROP TABLE IF EXISTS `xf_connected_account_provider`;
/*!40101 SET @saved_cs_client     = @@character_set_client */;
/*!40101 SET character_set_client = utf8 */;

So question: What is the best fix? I use a Perl script to run a mysql command, here:

#!/usr/bin/perl

$My_Date=`date +%A`;
chomp ($My_Date);
system ("mysqldump --opt -u *** -p*** *** | /bin/gzip > /***/***/backup_'$My_Date'.sql.gz");

What would I add and where?

And what about our host. They do daily backups. Those are likely safe because they are server images correct?

Thanks!

Would anyone know?

 

[Chris D]

The first post really answers your question:

To fix it, either add the argument --default-character-set=utf8mb4 to your mysqldump command, or else update the appropriate MySQL config file with the following:

[mysqldump]
default-character-set=utf8mb4

This public service update was brought to you by the letter

 

[beerForo]

Right, I just need a little assistance formatting the command I use if possible. I don't know where to put the argument.

 

[Chris D]

Sorry, I thought you missed the instruction. This should do it:

system ("mysqldump --opt --default-character-set=utf8mb4 -u *** -p*** *** | /bin/gzip > /***/***/backup_'$My_Date'.sql.gz");

 

[beerForo]

Great! Thanks