How do I backup or export a MySQL Database?

MySQL databases are primarily managed using either the graphical interface phpMyAdmin or using text-based command line tools. phpMyAdmin makes backing up, or exporting, a MySQL database very easy using a web based interface, however on larger databases it can timeout. When this happens your only choice to backup the database is to use text-based command line tools such as mysqldump.

Typically on the Weberz servers any database larger than 50 megabytes will need to be exported using the text-based command line tools.

Backup/Export using phpMyAdmin

1. Login to the control panel at http://www.weberz.com/
2. Click on the “MySQL DB’s” option from the “Databases” menu
   
3. Click on the name of the database you want to backup.
4. Click the link for phpMyAdmin Control Panel. In the future you can also browse to /phpMyAdmin/ using a web browser and the server name listed on this page. Using the information in picture below the URL to phpMyAdmin would be  http://mysql.weberz.com/phpMyAdmin/.
   
5. On the phpMyAdmin login page, login using the database username and password that is configured for the database in the Weberz Hosting control panel.
6. Once logged in to phpMyAdmin, select the database on the left if you have multiple databases for this database user, and click the “Export” link at the top on the right.
   
7. On the export page click “Select All” to select all of your database tables and choose “SQL” as the export type. Both of these options are in the left hand “Export” box on the page. In the right hand “Options” box on the page make sure that all of the following options are checked:
• Structure  - (exports the database structure such as table names)
• Add Auto_Increment Value – (keeps your database auto increment values)
• Enclose table and field names in backquotes – (solves problems with importing)
• Data – (exports the actual data in the database)
• Complete Inserts – (forces full SQL lines when running the import)
• Extended Inserts – (condenses the number of lines for export)
• Use Hexadecimal for BLOB – (exports binary data into usable format for importing)
8. Once all the above options are set click the “GO” button on the bottom right.
9. If everything exported properly you will be presented with a text-box with your database information in it. The top of the text will read “– phpMyAdmin SQL Dump”. Copy all of the text from the box and paste it into a program like notepad on your computer. Save the text as a file using notepad on your computer. This text file can now be used by phpMyadmin or MySQL command line utilities to re-import your tables and data into a new database.

Backup using mysqldump command line utility
Backing up or exporting your MySQL database using a command line utility is a lot easier than using phpMyAdmin. Most users will opt to use phpMyAdmin regardless of this due to it’s point and click graphical interface. To use the command line utilities you must have SSH access to your Weberz Hosting account or a Linux server with the MySQL utilities installed.

1. Login to your account using SSH.
2. Once logged in type “mysqldump -V” to make sure that the mysqldump utility is installed on the server. If it’s not installed or available then you will need to contact technical support or if using your own server you can install the utility (this is outside the scope of this article).
3. After ensuring that mysqldump is available using the following command to dump your MySQL database to a file. Replace <database> with your actual database name and <server> with the name of the MySQL server your assigned to. You can find the server name in the Weberz Hosting control panel by following the steps 1-3 from above.

mysqldump -h <server> -u <username> -p <database> > <database>.sql

After typing in the above command and pressing enter you will be prompted for the MySQL user’s password. Enter the password and press enter. Your database will then be saved to a file called <database>.sql in your current directory that you can download using FTP. The dump can take a few minutes to complete if you are trying to export a large database. You will be returned to an SSH command prompt once the mysqldump program has completed.

Weberz does take daily backups of all the database on our database servers. These backups are taken to ensure that we can recover from a server failure if needed. Users are encouraged to create their own backups of their databases using the above steps and save copies on their own computers. As per our terms of service, Weberz will not be responsible for any data loss due to customers not keeping backups of their data.

If you are uncomfortable creating backups of your databases or would like assistance doing so, please feel free to contact us or submit a support ticket. Our technical support staff can either assist you in understanding how to do your database backups or can perform the database backup for you. Users requesting large amounts of backups could be asked to pay a service fee.