Public service announcement: Check your MySQL backup!

gerryvz

Well-known member
This is the command I use as a cron job for a daily rolling backup of my main XF database. Each day it replaces the day of the week as a new backup, and writes over the backup done the same day the previous week. Everything is automatically backed up once a day via my VPS provider (Linode), and once a week I download a copy to my local hard drive (for cloud and two other types of backup).

This is with Ubuntu 18.04.3.

(uncompressed)
mysqldump --opt --default-character-set=utf8mb4 -u'ADMINACCT' -p'PASSWORD' xenforo > /var/www/html/MYDOMAIN/mysql_backups/xenforo-backup-$(date +\%F).sql

(compressed)
mysqldump --opt --default-character-set=utf8mb4 -u'ADMINACCT' -p'PASSWORD' xenforo | gzip -c > /var/www/html/MYDOMAIN/mysql_backups/xenforo-backup-$(date +\%F).sql.gz

Works a charm.

ADMINACCT = your admin login account name
PASSWORD = your admin password
MYDOMAIN = domain where database operates

Here's the output.
Screen Shot 2020-02-19 at 3.49.14 PM.jpg
 

alexm

Member
Right,

My server is running cPanel 86.0.14 (MariaDB 10.3.22). Like some people, I tried doing a backup both with and without this added to my.cnf (and MySQL rebooted):
Code:
[mysqldump]
default-character-set=utf8mb4
Either way it adds in:

/*!40101 SET character_set_client = utf8 */;

When I check to see if the Emoji is there, it is, both ways! The collation is still utf8mb4_general_ci too.

1584537674277.png

So do you think it was a bug that is now fixed? And if so, what are the chances of this error happening again?

cheers,

Alex
 
Top