Posts Tagged ‘database’

Import & export MySQL database’s via the command line.

If your website, webapp or forum has a particularly large database, backing the database up or restoring it via PhpMyAdmin (or similar tools) can often cease to be an option. PhpMyAdmin for instance will generally struggle with files over 5MB, which isn’t all that helpful when your sites database is in the hundreds of megabytes, if not the gigabytes.

The solution, if shell access is available, is to switch to using shell commands instead. Once logged in to the server (via putty or the terminal of your a linux or OSX user) backing up the database is as simple as running

mysqldump --opt -h yourDBHost -u yourUsername -p yourDBName > backupfile.sql

(obviously with yourDBHost, yourUsername and yourDBName changed to whatever they are for your server)

Restoring the database again can be just as easy, using a command such as

cat file_to_import.sql | mysql -u yourUsername -p yourPassword -h yourDBHost yourDBName

Or if the server is windows based (Useful for importing database’s in to local WAMP or XAMPP databases)

type file_to_import.sql | mysql -u yourUsername -p yourPassword -h yourDBHost yourDBName

will perform much the same action.