Transfering Very Large MySql Databases From A Server To Another (Direct Mysql Dump Into Said Server)

In an attempt to improve the infrastructure of CyberSports, recently I have been doing lots of database transfers. The said databases have sizes between 300MB and the largest, CyberDunk, 150 GB.

In this article I will explain how I did it, (read learned via trial & error, like everything else). In the past, I had the help of a friend of mine, but seeing he is now in jail (not related ), I have to do it myself.

Apparently a faster way would be to solicit the hoster a cd burn or an external harddrive with the database. But let’s be honest, Hostgator and Softlayer are not gonna help with that.

Here’s one of the fastest ways to transfer databases between servers when you don’t have hardware access nor deep linux know-how (like I don’t), but you do have SSH access.

By comparison with the mysqldump into file and then move and then import, this is 6-7 times faster. Dump directly into the new server >:D

METHOD HERE

1. Get putty.exe, type in your server ip or hostname.
2. Login with your root account, or whatever account has the permissions we need.
3. If you know the database’s exact name you can root into mysql (everything after “//” double dashes is a comment that will not be written into the command line):

# mysql -u root // type this in the command line black command line
# show databases;
# use omnem_database_i_think_it_is;
# show tables; //and repeat this "use database, show tables" cycle until you find it, then:
# exit;


4. Once you know the database’s name, type:

mysqldump -u [username] -p [password] [databasename] > [root@server_destination_ip] [destination_database_name]

In our particular case (if you don’t have a root pw skip the “-p password” argument):

mysqldump -u root forum_talkdep > root@74.53.26.34 new_talkdep

Now, sometimes I am in a situation where I have a 30 GB storage table that is not 100% needed for the running of the game. In order to keep the lowest downtime possible. What I do is I ask mysqldump to ignore that table, and I return after it later. That way I save 30GB (which is about 12hours) of transfer time. I do that as follows:

mysqldump -u root -p mySEXYpassword forum_talkdep > find_me_sir_talkdep.sql --ignore table forum_talkdep.big_ass_table

If you want mysql to ignore two huge tables this time, do this:

mysqldump -u root -p mySEXYpassword forum_talkdep > find_me_sir_talkdep.sql --ignore table forum_talkdep.big_ass_table --ignore table forum_talkdep.big_ass_table2

This will take forever. The only way to monitor the progress. Log in to the new root server, go to the mysql database directory. Type:

ls -la

And it will you show you the size of the table or of the tables you have moved. They need to match the ones on the original server *if same versions*.

What I did was move individual tables. And disabled parts of my site. That way i was able to keep downtime to a bare minimum and could have kept it a lot shorter if i really tried my hardest.

Helpful links:

Where I found this method.
Import/Exporting a database link (first google result when searching how to import/export
Dumping into a file and then moving is faster if you have physical access to the destination server or the severs have slow network between them.