Compilenix's Blog - Transfer a mysql db

RSS Feed | Find Stuff

Transfer a mysql database beween two instances/hosts in one step.
Procedure: mysqldump -> gzip -> ssh -> gunzip -> mysql

database="db_name"
local_mysql_user="root"
local_mysql_password="password"
remote_mysql_user="root"
remote_mysql_password="password"
remote_host="1.2.3.4"
remote_ssh_user="root"

mysqldump \
--add-locks=FALSE \
--comments=FALSE \
--default-character-set=utf8 \
--events \
--hex-blob=TRUE \
--lock-tables=FALSE \
--max_allowed_packet=1G \
--replace=TRUE \
--routines \
-u $local_mysql_user --password=$local_mysql_password --databases $database | gzip -9 | ssh $remote_ssh_user@$remote_host "gunzip | mysql -u $remote_mysql_user --password=$remote_mysql_password"