MySQL data migration

mysql_backup_restore.md

Migrating MySQL databases via dump/restore

Guidance on migrating MySQL data between servers (and potentially releases) using database dump/restore.

Definitive MySQL backup command

$ mysqldump --single-transaction --quick --skip-set-charset --default-character-set=utf8 --add-drop-table --routines --events --all-databases --force

(Note: --routines invalid pre-5.1; --events invalid pre-5.5)

Restore script

    #!/bin/sh
    # restore MySQL DB from dump file
    
    for f in $@; do
      log=`basename $f .sql.gz`.log
      (
      echo "SET AUTOCOMMIT=0;"
      echo "SET FOREIGN_KEY_CHECKS=0;"
      zcat $f
      echo "SET FOREIGN_KEY_CHECKS=1;"
      echo "COMMIT;"
      echo "SET AUTOCOMMIT=1;"
      ) | mysql --force > $log 2>&1
    done

Potential issues

• Watch out for "failed on view" comments in the dump file (usually caused by referenced tables or columns missing).
• Don't forget to run mysql_upgrade --force
• If you receive "ERROR 1267 ... Illegal mix of collations" errors when attempting restore, edit the dump file and ensure all character sets are changed to utf8_general_ci.
• Migration from older releases with previous password hash formats may require the following in the MySQL config (unless you can reset all the affected passwords):

[mysqld]
secure_auth=off
[mysql]
secure_auth=off

Later versions of MySQL Community Edition have problems with secure_auth (see bug #75425) so you may need to go with MariaDB.

• If repeating a restore following failures, you may first need to drop any created databases (e.g. if character set encodings were wrong originally).
• Potentially worth commenting the restore of mysql.proc out of the dump file and relying on the routine dumps to recreate.
• Bear in mind that migrating between significant MySQL release jumps is often only supported if done via intermediate releases. May need a VM running older OS releases or repositories like Red Hat/CentOS SCL to gain access to older releases (e.g. 5.1).
• Compare between databases by running the following query on each and diff'ing the outputs: select TABLE_SCHEMA,TABLE_NAME,TABLE_TYPE from information_schema.tables (allowing for additional tables between releases)
• Increasing MySQL FD limits requires changes in the systemd unit configuration if used.