0

This may sound like a really simple question, but I've not found a straight answer with a lot of googling!

I have a MySQL database server which has databases with various different character encodings (largely a mix of latin1 and utf8)

Are there any recommendations when using mysqldump to back up the the entire server (one db at a time), when for each individual database I will not know what the character set is that is being used?

(I have a bash script which gets all the DBS on the server, and then runs mysqldump individually on each one - copied below)

I know I can set the default encoding to use with mysqldump by specifiying that as an argument to the mysqldump command - should I just be setting that to UTF-8? Will that be OK for the latin encoded DBs?

#!/bin/bash
mkdir `date -I`;
dblist=`mysql -h myhosthere -u myuser -pmypassword -e "show databases" | sed -n '2,$ p'`
for db in $dblist; do
   mysqldump -h myhosthere -u myuser -pmypassword $db | gzip --best > home/user/`date -I`/$db.sql.gz
done;
  • Even messier -- It is possible to have different columns in a single table using different charsets. – Rick James Feb 15 '17 at 23:00
  • That is true, though thankfully I think I am safe from that one because there is only one route in and out the DB and it's all encoded in the same char set as the DB :) – CRM Dev Feb 16 '17 at 9:04
0

I think the safest thing is to specify dumping in utf8mb4. Hopefully all the characters you have in all the character sets will convert correctly to (and from, when reloading) that charset.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.