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:

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'

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:

SET AUTOCOMMIT = 0;
SET FOREIGN_KEY_CHECKS=0;

post.sql:

SET FOREIGN_KEY_CHECKS = 1;
COMMIT;
SET AUTOCOMMIT = 1;

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

cat pre.sql mysql-dump.sql post.sql | mysql -pPassWord --default-character-set=latin1

and that's it.

enjoy, ;)