Moving big mysql database from another VPS to Linode

What size Linode are you working with?

Linode 2880

After you do the DB dump - make a MD5 checksum, then after you copy it to your new server, make sure the MD5 verifies.

If it does and you still can't successful load the DB file - perhaps try replicating it from the old DB server to the new DB server. Then once it's up and running on the new DB server, remove the replication relation.

http://dev.mysql.com/doc/refman/5.0/en/replication.html

Thanks - I will try those solutions as well. I am off on holiday for the next 5 days but will report back to the forum once I have more info.

You could also try splitting the dump file into pieces (say one per table).

Also try checking /var/log/.err for errors (note the file maybe located elsewhere but that's where mine goes, it's the mysql error log).

Are you running out of memory and swapping hard for some reason? (Not likely on a 2880, but it could be configuration error.) Open another terminal window and run top or htop to monitor CPU and memory usage over time…

Are you running out of disk space? (With 2880 you only have 128GB.) Is the size of the database 20GB before gzipping or after? What is the size of the live database?

Hi

I have checked the MD5 sum and all is fine.

I also checked what's happening during the import. CPU load is around 1 and no swap is used.

I tried importing a version of the file that was created using –skip-extended-insert. This means that it has a separate INSERT statement from every row.

The SQL data file is 20Gb unzipped.

I guess all I have left is the replication. I will read more about it and try to set it up.

Thanks

Georgios

You could also try using maatkit (http://www.maatkit.org/) I use it for backups/restores, it will split the database files into smaller chunks for you, you can install it via apt/yum/whatever your package manager is.

Hi Favorious,

> At the same point I am also logged out from my ssh.

Have you tried running the job under nohup or screen? If it's your net connection dropping or your laptop going to sleep, then nohup or screen will save the day.

@droidy - Thanks, screen seems to be working fine as I have been loading data to mysql for the last 12 hours with no problems.

@obs - thanks for the maatkit tip. I didn't use it for this but it looks like a good tool for various tasks so I will give it a go.

You may already have solved this, but just in case: older versions of Mysql had a default 4GB limit on some types of tables; dumping those tables didn't necessarily include the ALTER statements to increase the maximum number of rows.

Also, which file system are you using? Any limits on file sizes (though if the MD5 is ok, then presumably all's well).

I am using the latest MySQL as this is a fresh install done few weeks ago. Filesystem is ext3. I didn't change anything there since I don't know enough to start messing with filesystems.

The import hasn't finished yet. It's been a day and a half and I am estimating it needs another 2-3 days. The total size of the main mysql data file will be around 40Gb.

Damn..that's a long import, let us know how it goes, if it fails again I'd defiantly go down the replication route.

For many table types you can just copy the raw data, as long as the server is shut down. This should cut it down to less than an hour with a rsync.