We want to transfer a complete database to another server. All we need is ssh access on both servers. On the old server we start a dump of our database:
mysqldump --single-transaction -h HOSTNAME -u USERNAME -pPASSWORD DATABSE_NAME sqlbkp_`date+"%Y%m%d"`.bak\
Be careful, your password is readable in your bash history. The database is saved as ‘sqlbkp_’ with current date. Now we send the file to the new server into the database:
mysql -h NEW_HOSTNAME -u USERNAME -pPASSWORD DATABASE_NAME < FILE_NAME.bak
Safe all databases in a file per db and gzip that file:
for db in $(mysql -e 'show databases' -s --skip-column-names); do mysqldump $db | gzip > "/backups/mysqldump-$(hostname)-$db-$(date +%Y%m%d).gz"; done
That’s it.