Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

I am trying to move a database from 1 provider to another, the problem is that I don't have system access at either end (ie, no ssh), so I cannot use a mysqldump.

I have already tried using MySQL Administrator, the backup took about 45 minutes, but when it came to restoring it was moving at a snails pace, and estimating 12+ hours. This is a live app so I need to keep the downtime to an absolute minimum.

The database consists of 35 tables, a mixture of MyISAM and InnoDB, the whole thing comes to about 4.4GB. The source and destination databases are both running on very powerful servers.

Any suggestions on a quick way of doing this will be gratefully received.

Thanks

share|improve this question
1  
Did you do the migration from a third host, which is not as well connected to the interweb as your two servers (especially upload wise)? –  lepole Apr 27 '10 at 20:30

3 Answers 3

Some old tricks for mass data loading (that works for just about any SQL-based database with advanced features like transactions, UDFs, and triggers):

  1. If your loading mechanism allows for it, roll your InnoDB tables into giant 1000-record transactions. Posting a record-at-a-time creates a large transactional overhead, but posting 1,000 records at a time makes the overhead negligible.
  2. If you have trigger support, disable triggers during the loading process.
  3. Turn off constraints before loading, and apply them after loading. This includes indexes, remove them and rebuild the indexes after the data has loaded. The overhead of maintaining the indexes during a bulk data load will slow things down considerably.

Newer versions of MySQL allow for replication; you could always replicate the data into another machine, then when replication is complete, switch your application to point to the new machine and turn off replication.

share|improve this answer

If you can, put the table contents into CSV or tab-delim, and use the 'LOAD DATA' command -- it's typically much faster for bulk loading data than individual inserts.

share|improve this answer
    
You need to have the data on the remote server for this to work. –  Jonathan Haddad Apr 27 '10 at 19:01

Are you able to run a query against your original database from the new one? If so, create a script (could even be PHP run as a web page with no output) to suck the data from the original server and push it into the new one, eliminating the intermediate system.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.