Backing up and restoring a MySQL database

Why MySQL Database Backup is a Necessity?

The database is the beating heart of any dynamic website or web application. From online stores to content management systems, everything depends on MySQL. A single query error, an SQL Injection attack, or a hard drive failure can destroy all your data. That's why database backup should be part of every server administrator's daily or weekly routine. In this article, we will thoroughly examine two main methods for creating backups — using phpMyAdmin and the mysqldump command line — and then move on to restoring large databases and troubleshooting common errors.

Method One: Database Backup with phpMyAdmin (Suitable for Beginners)

phpMyAdmin is a web-based tool that allows you to manage MySQL through a graphical interface. If you're not comfortable with the command line or don't have SSH access, this method is the simplest option.

Steps to Create a Backup in phpMyAdmin

1. Log into your hosting control panel (such as cPanel or DirectAdmin) and open phpMyAdmin.
2. From the list on the left, select the desired database.
3. Click on the Export tab.
4. Choose the Quick method (for a fast backup) or Custom for advanced settings.
5. In Custom mode, review the following options:
   • Format: Select SQL.
   • In the Format-specific options section, check Add DROP TABLE / VIEW / PROCEDURE / FUNCTION so that old tables are removed during restoration.
   • Check Add CREATE DATABASE / USE so that the target database is automatically selected.
6. Click Go to download the SQL file.

Important Note: If your database is larger than 50 MB, phpMyAdmin may encounter a timeout error. In this case, it's better to use the command line method.

Method Two: Database Backup with mysqldump (Professional and Reliable)

The mysqldump command is the most powerful tool for database backup. With it, you can create compressed, selective, and scheduled backups.

Basic Structure of the mysqldump Command

mysqldump -u username -p database_name > backup.sql

After execution, the system will ask for your MySQL password. The backup.sql file contains all tables, data, and stored procedures.

Backing Up Multiple Databases Simultaneously

mysqldump -u root -p --databases db1 db2 db3 > multi_backup.sql

Backing Up All Databases on the Server

mysqldump -u root -p --all-databases > all_databases.sql

Backing Up Only Table Structures (Without Data)

mysqldump -u root -p --no-data database_name > structure_only.sql

Backup with Simultaneous Compression

mysqldump -u root -p database_name | gzip > backup.sql.gz

This method reduces the final file size by up to 80% and is essential for large databases.

Backing Up Only Specific Tables

mysqldump -u root -p database_name table1 table2 > partial_backup.sql

Restoring a Database from a Backup File

Restoration is usually simpler than backup, but if the SQL file is large, issues can arise.

Restoration with the mysql Command

mysql -u username -p database_name < backup.sql

If the file is compressed, first decompress it:

gunzip backup.sql.gz
mysql -u username -p database_name < backup.sql

Restoration in phpMyAdmin

1. Log into phpMyAdmin and select the target database.
2. Click on the Import tab.
3. Select the SQL file and click Go.

Limitation: phpMyAdmin usually rejects files larger than 2 MB. To fix this, you need to change PHP settings in the php.ini file:

upload_max_filesize = 128M
post_max_size = 128M
max_execution_time = 300

However, the best approach for large databases is to use the command line.

Common Errors in Database Restoration and Solutions

In this section, we will examine three frequent errors that occur during database backup or restoration.

Error 1: "ERROR 2006 (HY000): MySQL server has gone away"

This error occurs when the SQL file is very large and the MySQL server disconnects before the import finishes. Solution: Increase timeout and max_allowed_packet in MySQL.

# In the my.cnf file (usually at /etc/mysql/my.cnf or /etc/my.cnf)
[mysqld]
max_allowed_packet = 256M
wait_timeout = 600
interactive_timeout = 600

After making changes, restart MySQL:

sudo systemctl restart mysql

Error 2: "ERROR 1045 (28000): Access denied for user"

This error indicates that the MySQL username or password is incorrect. First, check permissions with the following command:

mysql -u root -p -e "SHOW GRANTS FOR 'username'@'localhost';"

If the user doesn't have sufficient permissions, grant them with this command:

GRANT ALL PRIVILEGES ON database_name.* TO 'username'@'localhost';
FLUSH PRIVILEGES;

Error 3: "ERROR 1064 (42000): You have an error in your SQL syntax"

This error usually occurs due to MySQL version incompatibility. For example, if you took a backup from MySQL 8 and try to restore it in MySQL 5.7. Solution: Use the --compatible=mysql40 option in mysqldump:

mysqldump -u root -p --compatible=mysql40 database_name > backup_compat.sql

Automating Database Backup with a Cron Job

To ensure you always have a fresh copy of your database backup, you can write a simple script and place it in cron.

Automated Backup Script

#!/bin/bash
# Variables
DB_USER="root"
DB_PASS="your_password"
DB_NAME="your_database"
BACKUP_DIR="/backup/mysql"
DATE=$(date +%Y%m%d_%H%M%S)

# Create backup directory
mkdir -p $BACKUP_DIR

# Backup with compression
mysqldump -u $DB_USER -p$DB_PASS $DB_NAME | gzip > $BACKUP_DIR/${DB_NAME}_$DATE.sql.gz

# Delete backups older than 7 days
find $BACKUP_DIR -type f -name "*.sql.gz" -mtime +7 -delete

Save the script and give it execute permission:

chmod +x /path/to/backup_script.sh

Then add it to crontab:

0 3 * * * /path/to/backup_script.sh

This command takes a backup every day at 3 AM and deletes backups older than one week.

Security Tips and Best Practices

• Don't store passwords in the script: Instead of writing the password in the script, use the ~/.my.cnf file with 600 permissions.
• Store the backup on another server: If your server fails, a backup on the same server is useless. Use FTP, S3, or cloud services.
• Test the restoration: At least once a month, restore the backup in a test environment to ensure the file isn't corrupted.
• Use incremental backups: For very large databases, instead of a full daily backup, use incremental backups with tools like mysqlbinlog.

If you're looking for a comprehensive solution for server and database management, ServerNet offers web hosting and cloud infrastructure services with 24/7 technical support that can meet your needs.

Summary

MySQL database backup isn't difficult, but it requires precision and planning. By using mysqldump and cron automation, you can protect your data from any disaster. Remember to regularly test backup restoration and always keep a copy off the main server. By following the tips in this article, you'll no longer worry about losing your data.