How can I backup a MySql Database or export it to a file?

Imagine you have a large MySql database and want to backup it or backup your wordpress blog which also uses a MySql database. There is nothing easier than just using the handsome mysqldump command. It writes all the information to a sql script which you then can use to restore the databases at any machine you want. It is only two steps to create the script and restore the database.

To export the database just use the following command:

[sourcecode]
mysqldump -uroot -pmypassword –all-databases > backup.sql
[/sourcecode]

In that case just all databases will be backed up. Of course the command also offers options to backup single databases.

If you want to restore the database you have backed up later you can doing by the following even shorter command:

[sourcecode]
mysql -u root -p < alldb.sql
[/sourcecode]

One common error mysql dump might throw is the following one:

mysqldump: Couldn’t execute ‘show fields from `general_log`’: Can’t create/write to file ‘/tmp/#sql_63f9_0.MYI’ (Errcode: 2) (1)

If you get it do not panic! It is usually simply a permissions issue or a problem with discspace. You must make sure that the “mysqld” user has the permissions to write its tmp directory (in this example it is /tmp). If not your will get this error.

If you verified that you really have enought disc space and futhermore the permissions are correctly set but exporting your sql database still does not work there might be another cause for the problem:

You might get this error if you are using an older version of mysql in combination with a newer version of a Read Head based linux system (e. g. Fedora). There have been bug reports that the OS does not allow mysql to the directory /tmp. This is really odd but in my case really was the cause of the problem! The solution here is also an easy one, if you find it:
[sourcecode]
vi /etc/my.cnf
[/sourcecode]

Then add the tmpdir variable to the [mysqld] section:
[sourcecode]
[mysqld]

tmpdir=/var/tmp
[/sourcecode]

In a last step just restart the mysql deamon:
[sourcecode]
/sbin/service mysqld restart
[/sourcecode]

You will not believe how smooth the export feature is now working. Enjoy!

Leave a Reply

Your email address will not be published. Required fields are marked *