Last Updated: February 25, 2016
·
377
· aleemb

Migrate MySQL Dumps across RDS Instances

Migrate

# Piped Import/Export
mysqldump -h XXXXX.celcywoewbya.XXXXX.rds.amazonaws.com -u XXXXX -pXXXXX DB_NAME | mysql -h XXXXX.celcywoewbya.XXXXX.rds.amazonaws.com -u XXXXX -pXXXXX DB_NAME

Migrate Users

This may need to be rerun after databases/tables are imported. That's because certain users may have permissions only on certain databases or even certain tables within databases and the user creation will fail unless these databases/tables exist.

# Migrate Users
# Bash function
mygrants()
{
  mysql -B -N $@ -e "SELECT DISTINCT CONCAT(
    'SHOW GRANTS FOR \'', user, '\'@\'', host, '\';'
    ) AS query FROM mysql.user" | \
  mysql $@ | \
  sed 's/\(GRANT .*\)/\1;/;s/^\(Grants for .*\)/## \1 ##/;/##/{x;p;x;}'
}

# User Dump
mygrants  -h XXXXX.celcywoewbya.XXXXX.rds.amazonaws.com -u XXXXX -pXXXXX mysql

# 1. Execute output of User Dump in SQL Editor
# 2. Run "FLUSH PRIVILEGES"

Migrate without DEFINER

If you get a warning about DEFINER or TRIGGERS, you can remove the DEFINER if needed. However, it is better to copy the users over directly using the mygrants below.

# Piped Import/Export with DEFINER removed
mysqldump -h XXXXX.celcywoewbya.XXXXX.rds.amazonaws.com -u XXXXX -pXXXXX DB_NAME | sed -e 's/DEFINER[ ]*=[ ]*[^*]*\*/\*/' | mysql -h XXXXX.celcywoewbya.XXXXX.rds.amazonaws.com -u XXXXX -pXXXXX DB_NAME