Exporting/Backing Up a Database

Creating Database Backups

Regular database backups are essential for protecting your website's data. phpMyAdmin makes it easy to export your database to an SQL file that can be stored safely and used for restoration if needed.

Exporting via phpMyAdmin

1. Log in to phpMyAdmin through DirectAdmin.
2. Select the database you want to export from the left sidebar.
3. Click the Export tab at the top.
4. Choose the export method:
   • Quick - Exports the entire database with default settings. Suitable for most backups.
   • Custom - Allows you to select specific tables and configure export options.
5. Set the format to SQL (recommended for full backups).
6. Click Go to download the export file.

Custom Export Options

When using the Custom export method, you can configure:

• Tables - Select which tables to include in the export.
• Output - Choose to save as a file (default) or display on screen.
• Compression - Select gzip or zip to compress the export file and save disk space.
• Add DROP TABLE - Include DROP TABLE IF EXISTS statements so importing the backup will replace existing tables.
• Add CREATE DATABASE - Include the database creation statement in the export.
• Data format - Choose between INSERT statements (standard) or INSERT IGNORE (skip duplicates).

Exporting to CSV

To export data in CSV format (useful for spreadsheets and data analysis):

1. Select the specific table you want to export.
2. Click the Export tab.
3. Change the format from SQL to CSV.
4. Configure delimiters and enclosures if needed.
5. Click Go to download the CSV file.

Automated Backups via Cron

For regular automated backups, set up a cron job with the mysqldump command:

mysqldump -u username -p'password' database_name | gzip > /home/username/backups/db_$(date +%Y%m%d).sql.gz