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.