MySQL Database Backup: Reliable Protection of Your Data

Why is backup necessary?

1. In case of unforeseen circumstances: cyberattacks, accidental user errors, or system failures can lead to complete data loss. In such cases, backup allows for the recovery of lost data.
2. When making changes and updates: Before making any changes to the database, it is advisable to create a backup. If an error or failure occurs, changes can be quickly undone.
3. To comply with storage requirements and regulations: Some industries have specific data storage requirements. Regular backups help easily comply with these regulatory standards.

Backup using the command line (mysqldump)

The mysqldump tool is widely used to create backups of MySQL databases. Using SQL script commands, you can export both the database structure and data to successfully restore information.

1. Backup of a single database

   Execute the following command in the terminal:

   mysqldump -u [username] -p [database_name] > [path_to_file]/backup.sql

   After execution, the system will prompt for the MySQL user password.

2. Backup of multiple databases

   To create backups of multiple databases, use the --databases parameter:

   mysqldump -u [username] -p --databases [database1] [database2] > [path_to_file]/backup.sql

3. Backup of all databases

   To create a backup of all databases on the server, use the --all-databases parameter:

   mysqldump -u [username] -p --all-databases > [path_to_file]/all_databases_backup.sql

4. Compressed backup

   To save disk space, you can compress the backup file using gzip:

   mysqldump -u [username] -p [database_name] | gzip > [path_to_file]/backup.sql.gz

5. Adding a timestamp to the backup file

   To avoid overwriting files and maintain a backup history, add a timestamp to the file name:

   mysqldump -u [username] -p [database_name] > [path_to_file]/backup_$(date +%Y%m%d).sql

Backup using phpMyAdmin

phpMyAdmin provides a convenient interface for managing MySQL databases. To create a backup using phpMyAdmin, follow these steps:

1. Log into phpMyAdmin via the hosting control panel.
2. Select the database for export from the left panel.
3. Perform the database export.

   At this stage, select the export method:

   • Quick: Exports the entire database with basic settings.
   • Custom: Allows selecting export configuration options.
4. Click "Go" to download the backup file

Backup using MySQL Workbench

MySQL Workbench offers a graphical interface for database management, including backups. To create a backup, follow these steps:

1. Connect MySQL Workbench to the MySQL server.
2. In the top menu, go to "Server" > "Data Export" and configure the export settings. Select the "Dump structure and data" method to save both structure and data.
3. Choose the storage location for the backup and click "Start Export."

After completion, the backup file will be saved to the specified location

Database recovery

1. Recovery via command line

   Execute the following command in the terminal:

   mysql -u [username] -p [database_name] < [path_to_file]/backup.sql

2. Recovery using phpMyAdmin

   After logging in, select the target database in phpMyAdmin, choose the backup file for import, and click "Go" to start the recovery process.

3. Recovery using MySQL Workbench

   After connecting to the server, go to "Server" > "Data Import," select the backup file, and click "Start Import."

Backup recommendations

• Ensure regular backups while considering data update frequency.
• Store backup files securely. Use encryption, for example:

  openssl enc -aes-256-cbc -salt -in backup.sql.gz -out backup.sql.gz.enc -k "password"

• Regularly verify backup integrity
• Automate the backup process using cron:

  0 2 * * * mysqldump -u [username] -p[password] [database_name] | gzip > /backup/mysql_backup_$(date +\%Y\%m\%d).sql.gz

  This command will run every night at 2:00 AM, saving the backup in a compressed format.

Your data protection is a priority

Every organization values data as its most critical corporate resource. Since backup systems attract malicious actors, data protection is a top priority.

For maximum data security, check out our article "MySQL Security on Hosting: A Comprehensive Approach to Protecting Your Database".