Exporting/Backing Up a Database

<h3>Creating Database Backups</h3> <p>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.</p> <h3>Exporting via phpMyAdmin</h3> <ol> <li>Log in to phpMyAdmin through DirectAdmin.</li> <li>Select the database you want to export from the left sidebar.</li> <li>Click the <strong>Export</strong> tab at the top.</li> <li>Choose the export method: <ul> <li><strong>Quick</strong> - Exports the entire database with default settings. Suitable for most backups.</li> <li><strong>Custom</strong> - Allows you to select specific tables and configure export options.</li> </ul></li> <li>Set the format to <strong>SQL</strong> (recommended for full backups).</li> <li>Click <strong>Go</strong> to download the export file.</li> </ol> <h3>Custom Export Options</h3> <p>When using the Custom export method, you can configure:</p> <ul> <li><strong>Tables</strong> - Select which tables to include in the export.</li> <li><strong>Output</strong> - Choose to save as a file (default) or display on screen.</li> <li><strong>Compression</strong> - Select <code>gzip</code> or <code>zip</code> to compress the export file and save disk space.</li> <li><strong>Add DROP TABLE</strong> - Include <code>DROP TABLE IF EXISTS</code> statements so importing the backup will replace existing tables.</li> <li><strong>Add CREATE DATABASE</strong> - Include the database creation statement in the export.</li> <li><strong>Data format</strong> - Choose between <code>INSERT</code> statements (standard) or <code>INSERT IGNORE</code> (skip duplicates).</li> </ul> <h3>Exporting to CSV</h3> <p>To export data in CSV format (useful for spreadsheets and data analysis):</p> <ol> <li>Select the specific table you want to export.</li> <li>Click the <strong>Export</strong> tab.</li> <li>Change the format from SQL to <strong>CSV</strong>.</li> <li>Configure delimiters and enclosures if needed.</li> <li>Click <strong>Go</strong> to download the CSV file.</li> </ol> <h3>Automated Backups via Cron</h3> <p>For regular automated backups, set up a cron job with the <code>mysqldump</code> command:</p> <pre><code>mysqldump -u username -p'password' database_name | gzip > /home/username/backups/db_$(date +%Y%m%d).sql.gz</code></pre> <div class="alert alert-info">Store backup files in a location outside your <code>public_html</code> directory to prevent them from being accessible via the web. Consider also downloading copies to your local computer or an external storage service.</div> <div class="alert alert-warning">Database backups contain all your website's data, potentially including user passwords and personal information. Treat backup files as sensitive and store them securely.</div>