MySQL dump & import

For large databases we recommend dumping and importing trough the commandline

EXPORT

mysqldump --single-transaction --triggers --routines --events -y DBNAME > DBNAME.sql

IMPORT

mysql DBNAME < DBNAME.sql

 

Potential errors

  • ERROR 1227 (42000) at line xxx: Access denied; you need (at least one of) the SUPER privilege(s) for this operation
  • SQLSTATE[42000]: Syntax error or access violation: 1142 TRIGGER command denied to user 'xxx'@'%' for table 'xxx', query was: ...
  • ERROR 1227 (42000) at line xxx: Access denied; you need (at least one of) the SUPER or SET_USER_ID privilege(s) for this operation

    This is most likely because your database has routines/views, make sure to strip the definer owner during export:
    mysqldump --routines --single-transaction --triggers --routines --events -y DBNAME | sed -e 's/DEFINER=[^*]*\*/\*/g' > DBNAME.sql
    Or if you have already a dump file, you can also remove it during import:
    cat DBNAME.sql | sed -e 's/DEFINER=[^*]*\*/\*/g' | mysql DBNAME
  • mysqldump: Error: 'Access denied; you need (at least one of) the PROCESS privilege(s) for this operation' when trying to dump tablespaces

    If you see this error, make sure to use the "-y" argument as in the above example when dumping MySQL database (This is the no-tablespaces option), your dump will still contain everything needed.


Was this article helpful?

mood_bad Dislike 0
mood Like 2
visibility Views: 919