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.
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.
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.
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. Here are the estimated maximum file sizes per operating system:
|Win32 w/ FAT/FAT32
|Win32 w/ NTFS
||2TB (possibly larger)
|Linux 2.2-Intel 32-bit
||2GB (LFS: 4GB)
||(using ext3 file system) 4TB
|MacOS X w/ HFS+
|NetWare w/NSS file system
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 ***************************
Create_time: 2009-11-24 16:53:38
Update_time: 2009-11-24 16:53:38
Check_time: 2009-11-24 16:53:38
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.