Last Updated: February 25, 2016
·
631
· ahmedmhmd

Easily migrate heavy databases between servers

Sometimes you get to migrate to another server with some heavy databases in your hand and nobody is free to wait for all those tarballs to get downloaded to your PC and then uploaded to the new server

I ran into this before with a case of having to transfer some tables alone to get restored on their as I need to run some custom command on all the other tables of less weight

Dump all your old data

1- At your old server

1.1- Run this to get some table dumped alone

mysqldump -u database_user -p database_name table_1 table_2 table_3 table_4 > heavy_tables.out

2.2- Run this to dump all the other tables

ie; All tables here are prefixed with database name in dot notation style

mysqldump -u database_user -p database_name --ignore-table=database_name.table_1 --ignore-table=database_name.table_2 --ignore-table=database_name.table_3 --ignore-table=database_name.table_4 > other_tables.out

2.3- Transfer all the files to the new server

scp heavy_tables.out other_tables.out root@new-server.com:/bkb/

Restoring data

3- At your new server

3.1- Run these commands to restore data into your new database

mysql -u database_user -p database_name < /bkb/heavy_tables.out 
mysql -u database_user -p database_name < /bkb/other_tables.out