SQL Backup DB

SQL BACKUP DATABASE

A backup is a copy of your database that allows you to restore your data if something goes wrong. Backups are essential before running destructive operations like DROP, large UPDATEs, or migrations.

What Should You Back Up?

• All tables and data
• Table structure (schema)
• Views, procedures, and triggers (if used)
• User privileges (sometimes managed separately)

MySQL / MariaDB: Backup with mysqldump

One of the most common ways to back up a MySQL/MariaDB database is using mysqldump from the command line.

mysqldump -u username -p database_name > backup.sql

This creates a SQL file that contains commands to recreate your database and data.

Backup a Single Table

mysqldump -u username -p database_name table_name > table_backup.sql

Backup Structure Only (No Data)

mysqldump -u username -p --no-data database_name > schema_only.sql

Backup Data Only (No Structure)

mysqldump -u username -p --no-create-info database_name > data_only.sql

Restore from Backup

To restore a backup file into a database:

mysql -u username -p database_name < backup.sql

phpMyAdmin Export

If you use phpMyAdmin, you can export your database using the Export feature. This is a convenient option for local development or smaller databases.

Best Practices

• Backup before running DROP DATABASE, DROP TABLE, or mass UPDATE/DELETE
• Store backups outside the server (or at least outside the database directory)
• Use clear filenames with dates (for example, company_db_2026-02-13.sql)
• Test restore occasionally to ensure backups are valid

Common Mistakes

• Creating backups but never testing restores
• Overwriting backups without keeping versions
• Storing backups on the same disk as production data

Next Step

Continue with SQL CREATE TABLE to learn how to define tables and columns in a database.