Suppose you have a virtual server with a massive database that you need to move from one Linux server to another. Let’s also assume that you don’t have copious amounts of bandwidth at your disposal to download and upload the SQL files. I have found that the simplest method to move large files between two servers, is by using an application called rsync.
rsync is a software application and network protocol for Unix, Linux and Windows systems which synchronizes files and directories from one location to another while minimizing data transfer using delta encoding when appropriate.
Simply follow the steps below to transfer your database to a new Linux server.
1. Backup your database.
mysqldump -u root -p database_name > dumpfilename.sql
Once complete, type “ls -al” to view the details of your SQL file for interest’s sake.
2. Compress your file.
Take a look at this site for more information on compressing files in Linux
3. Install rsync on BOTH servers.
sudo yum install rsync
4. Transfer the file to your new server.
rsync -v -e ssh dumpfilename.sql.gz root@ipaddress_of_your_server:~
“dumpfilename.sql.gz” represents the zipped file that we just compressed using step 2 above.
“~” represents the home directory.
“root” represents your SSH username
“ipaddress_of_your_server” represents the, you guessed it, IP address of your server.
5. Decompress the file on the new server
gzip -d dumpfilename.sql.gz
6. Create your mysql database on the new server
Remember to keep the name the same as the old server’s database as we will be attempting to import it to a database with the same name.
7. Import your SQL file
mysql -u root -p dbname < dumpfilename.sql
Your database move is complete!
Let me know if you need help or get stuck by using the comment section below.