How to move a large database from one Linux server to another

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.

ls -al

2. Compress your file.

gzip dumpfilename.sql

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.

Maximum MySQL Database Size?

While developing myScoop, and other experimental projects, I have been thinking about the limitations of a MySQL database, particularly it’s maximum file size. I was amazed that after some research I really have absolutely nothing to worry about. It seems my 112mb database is a “new born baby” in the terms of how big it can still grow.

MySQL LogoWhile developing myScoop, and other experimental projects, I have been thinking about the limitations of a MySQL database, particularly it’s maximum file size. I was amazed that after some research I really have absolutely nothing to worry about. It seems my 112mb database is a “new born baby” in the terms of how big it can still grow. Here are the estimated maximum file sizes per operating system:

Operating System File-size Limit
Win32 w/ FAT/FAT32 2GB/4GB
Win32 w/ NTFS 2TB (possibly larger)
Linux 2.2-Intel 32-bit 2GB (LFS: 4GB)
Linux 2.4+ (using ext3 file system) 4TB
Solaris 9/10 16TB
MacOS X w/ HFS+ 2TB
NetWare w/NSS file system 8TB

This information was taken directly off MySQL.com but this is not necessarily the maximum limitations of your database. A number of methods can help to increase your max file size:

LFS (Large File Support) in Linux
To support files larger than 2GiB on 32-bit Linux systems you would have to use LFS. Although I’m sure the latest operating systems would come out with this already enabled. The standard max file size limitations without LFS enabled are 2^31 bytes(2GiB), but enabling LFS can enable your maximum file size to reach 2^63 bytes (9 223 372 036 854 775 808 bytes). Crazy, I know!

Using the “Alter Table” command
This will come in handy when using the MyISAM storage engine. The simple “Alter Table” in the mysql prompt command can extend your database capacity dramatically.
Example: “alter table ‘weather’ max_rows = 200000000000”
Although keep in mind, the maximum amount of rows in a MySQL table can only be 4.2billion (not so good if you’re thinking of making a search engine!)

Most of this researched information is very old so I decided to run a little check (which I should have done right in the beginning), and by doing so on my local machine, I literally nearly fell of my chair:

mysql> show table status like ‘blog_hits’ \G
*************************** 1. row ***************************
Name: blog_hits
Engine: MyISAM
Version: 10
Row_format: Dynamic
Rows: 15497
Avg_row_length: 247
Data_length: 3835852
Max_data_length: 281474976710655
Index_length: 366592
Data_free: 0
Auto_increment: 15509
Create_time: 2009-11-24 16:53:38
Update_time: 2009-11-24 16:53:38
Check_time: 2009-11-24 16:53:38
Collation: latin1_swedish_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)

This is telling me my max database size can be as much as 281474976710655 bytes which If I’m not mistaken, equates to 256 terabytes. With this being said, I think we are going to run into system limitations rather than MySQL limitations, so therefore, revert back to the table at the top of this post.

For more information on this topic,  Kristian Köhntopp provides more of a technical answer to the above question.