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.


  • 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, 2010 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.


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.

  • You need to have the data on the remote server for this to work.
    – Jon Haddad
    Apr 27, 2010 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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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