22/01/2008
Pipe Magic! Migrate from one MySQL server to another
New job, new problems, new hacks! The next hack tries to take all the juice from linux’s pipes.
The task which I had to do was a database migration, from one mysql server to another one. The first problem were different versions, so hot copy wasn’t an option (actually few times is an option, unless it’s not in production ;)). The next big issue, were the size of the data, its big, really big (from my point of view) some DB reach 800M, for me is big enough to be a big problem 
Mysqldump + mysql but this is good for 2 DB? I wanted to be selective in which DB to migrate, plus data should travel encrypted and transfer should delay as minimum as possible.
The script I got is this one (showing main loop, what’s interesting):
for db in `cat sm2-hf-dbs-kk.txt`
do
echo "Creant database a ${remote_sql}..."
ssh $remote_sql "echo \"create database if not exists ${db}\" | mysql -u ${db_user_rw} -p${db_pass_rw}"
echo "Fet"
echo "Donant permisos sobre la BD a rw..."
ssh $remote_sql "echo \"grant select, insert, update, lock tables, create, drop, alter on *.* to '${db_user_rw}'@'localhost';flush privileges\" | mysql -u ${remote_admin} -p${remote_admin_pass}"
echo "Fet"
echo "Fent dump gzipat i enviant a ${remote_sql}"
mysqldump --single-transaction -u ${db_user_r} -p${db_pass_r} ${db} | gzip | ssh $remote_sql "gunzip - | mysql -u ${db_user_rw} -p${db_pass_rw} ${db}"
echo "Fet"
done
The new server was empty, so we first ssh to it and from there we create a database. The next step was to give perms over the new db… I see now it can be done in one single line.
After this comes the magic. The script makes a dump of the db, it passes through gzip, this way we dramatically improve speed over the net. This pipe stream is passed through ssh to the other server unzip the info and put into the new mysql server. Pipe Magic rules!
I prefer ssh the machine rather than setting a tunnel because the tunnel should set before the script, this way we don’t care. The other point is that the ssh should have a valid pair of keys to access it.
That’s all folks! Hope this enlightens someone…
