Importing large databases in MySQL

One of the things I like about PhpMyAdmin is its ability to export an entire database as a series of SQL statements (CREATE and INSERT), a feature I miss in SQL Server.

On the other hand the same PhpMyAdmin has a very serious limitation when it comes to import back SQL files: it wouldn’t import any files larger than 2Mb. I’m not sure what are the technical reasons for this limitation. This is due to the PHP configuration (upload_max_filesize and post_max_filesize directives in PHP.ini). If you are using a sared server, you may not be able to change these values.

Fortunately I’ve discovered a very simple tool called BigDump. This little PHP script allows for arbitrary-sized sql files to be imported in the MySql database with no headaches. It’s pretty simple to configure, files can be uploaded via ftp and it’ll show them in a list for easy restore. It supports both text (*.sql) and compressed (*.gz) files.

Update: As Joe Limonada (hi Ami ) has said, if you have access to the shell, you can write

mysqldump -u MYSQL_USERNAME -p -h MYSQL_SERVER_ADDRESS DATABASE_NAME > dump.sql

to backup the database and

mysql -u MYSQL_USERNAME -p -h MYSQL_SERVER_ADDRESS DATABASE_NAME < dump.sql

to restore.