Copy MySQL database into new one

# Copy / Migrate MySQL database 'olddb' into new one 'newdb'
# 'user' is admin user. 'olduser' and 'newuser' are the users of olddb and newdb schemas
# www.fduran.com

# dump olddb
mysqldump --compress -u user -pPassword -h db.example.com --skip-quote-names --databases olddb > olddb.dump

# dump stored procedures too
mysqldump --compress -u user -pPassword -h db.example.com --routines --no-create-info --no-data --no-create-db --skip-opt olddb  > olddb_routines.dump

# create new user and permissions if needed
CREATE USER 'newuser' IDENTIFIED BY 'newPAssword';
GRANT ALL ON `newdb`.* TO 'newuser'@'%';

- replace in both dumps olddb -> newdb , olduser -> newuser
- take out DEFINER from dumps:
  perl -p -i.bak -e "s/DEFINER=\`\w.*\`@\`\d[0-3].*[0-3]\`//g" olddb_routines.dump
- add USE newdb; on top of routines dump

# create new db
cp olddb.dump olddb.sql
cp olddb_routines.dump olddb_routines_routines.sql

mysql -u user -pPassword -h db.example.com < olddb.sql
mysql -u user -pPassword -h db.example.com < olddb_routines_routines.sql