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
Written by Ahmed
Related protips
Have a fresh tip? Share with Coderwall community!
Post
Post a tip
Best
#Shell
Authors
Sponsored by #native_company# — Learn More
#native_title#
#native_desc#