How to Export a MySQL Database

There are a few reasons why you might need to export a MySQL database. If you ever need to transfer your data to a new server, backup your databases, or provide a former client with their website content, being able to quickly and easily backup a database is important.

With phpMyAdmin and other web-based management tools for MySQL, you can export in a variety of formats. This is often the easiest and quickest way to get the the job done, but there are some instances when it is easier to use the Linux command line. Larger than average databases, for example, may cause your PHP session to time out or just be impractical to push through a web browser.

In that case, all you have to do is login to your server via SSH and export it manually. Follow these steps:

1.Login to your server via SSH
2.Either make sure you are the user who needs to export or login as root to export another user.
3.Enter the following command string:

mysqldump -u username -ppassword database_name > dump.sql

4.Transfer the file to your computer or another secure location using FTP or SFTP.

The exported SQL file is structured to allow you to easily import it into a database on another server or different location on the same server. If it is a large database that needs to be transferred to a backup server, you might consider using tar/gzip to compress it. Logout from your server, and you are all finished.