Exporting and Importing Large MySQL Databases

A question for the more experienced server administrator…

What is the best way to export and import large MySQL databases (i.e.]200MB)?

I tried using phpmyadmin to export, but the browser screen timed out to a blank screen. I don’ think it has finished exporting and compressing. The sql file is only 20MB.

Does anyone know of a good way to migrate large mysql databases?

Many thanks in advance.

Howdy A,

There are a couple of ways to do it. The easiest being to use the Webmin MySQL module. It’ll dump to a file on the server itself, so no chance of the browser timing out waiting for it to finish.

Browse to the MySQL module: If the database owner is under Virtualmin, select the domain, click Edit Databases, click Manage… beside the database you’d like to dump. If the database is not under control of Virtualmin, you can still use Webmin…just browse to Servers:MySQL Database Server and click on the database you want to dump.

Once looking at the database page, click on Backup Database, and fill out the form.

One of these days I’m going to figure out how to convince people that Webmin’s MySQL module is cooler than phpMyAdmin. :wink:

Thanks Joe.

The server that I am trying to export databases from does not have VMpro installed on it. I am migrating to a new server with VMpro.

I really ought to check out Webmin’s MySQL module. I don’t even know where it is!

SOLUTION:


TO EXPORT


  1. SSH login as root.

  2. Run command

mysqldump -a -u USERNAME --password=XXX DATABASE] FILENAME.mysql

USERNAME = MySQL user that has privilege to the database, e.g. root
XXX = Password of the MySQL user
DATABASE = Name of database you want to export, e.g. forum_tno
FILENAME = Choose a name for the MySQL dump file


TO IMPORT


  1. FTP upload MySQL dump file.

  2. Run command

mysql -u USERNAME --password=XXX DATABASE[ FILENAME.mysql