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?
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.
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
FTP upload MySQL dump file.
Run command
mysql -u USERNAME --password=XXX DATABASE[ FILENAME.mysql