====== UTF-8 mysqldump and restore ======

To help spread the load when doing a mysqldump backup to migrate to new hardware I make sure the production server is used only to read from the MySQL server and then send the data directly to the new server for writing into a dump file. To save a bit of bandwidth I gzip the stream on it's way to the new server as well.  The problem with this approach is that mysqldump, by default, sends an ASCII stream which mangles UTF-8 encoded characters. To get around that I use the --default-character-set=latin1 and -N options to mysqldump:

<file>mysql -pPassWord --batch --skip-column-names --execute="show databases" | grep -v mysql | xargs -I{} mysqldump -pPassWord --compact --replace --default-character-set=latin1 -N --databases {} | gzip -c | ssh root@new-server.domain.tld 'cat > /var/mysql-dump.sql.gz'</file>

You will notice the mysql execution of show databases being piped to grep to filter out the mysql database. This is because mysqldump doesn't offer an --exclude-database option and the mysql DB will be done separately to ensure nothing goes amiss.

Once the dump is finished you just need to unpack it with gzip -d and then feed it into mysql. To make sure that all goes well, especially if you happen to have foreign keys and such I use the follow pre.sql and post.sql files as standard for all my restores. This way I don't have to edit the dumps (which can be quite large at times) and I never forget an option. ;)

pre.sql:<file>
SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;
</file>

post.sql:<file>
SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;
</file>

Once these 2 files are ready just issue the following to restore the databases:

<file>
cat pre.sql mysql-dump.sql post.sql | mysql -pPassWord --default-character-set=latin1
</file>

and that's it.

enjoy,
;)