Backup and Restore in MySQL

Backing up and restoring MySQL databases are essential tasks in database administration. Regular backups ensure that data can be recovered in case of accidental deletion, hardware failure, or other issues.

1. Types of Backups in MySQL

MySQL supports different types of backups based on the method and data consistency. These include:

1. Logical Backup: This backup involves exporting database schemas and data as SQL statements. Tools like mysqldump are commonly used for logical backups.
2. Physical Backup: Physical backups copy the actual database files (data files, log files, etc.). These are often faster but require more storage space.

2. Logical Backup Using mysqldump

The mysqldump utility is a popular tool for creating logical backups by generating SQL statements that recreate the database, tables, and data.

Backup a Single Database:

mysqldump -u username -p database_name > backup_file.sql

Example:

mysqldump -u root -p employees > employees_backup.sql

Backup All Databases:

mysqldump -u username -p --all-databases > all_databases_backup.sql

Options for mysqldump:

• -u: Specify the username.
• -p: Prompt for password.
• --no-data: Exports only the table structures without data.
• --single-transaction: Creates a consistent backup of InnoDB tables without locking them.

3. Physical Backup Using File Copy

A physical backup involves copying MySQL's actual data files. This can be done while the server is running (hot backup) or when the server is stopped (cold backup).

Steps for Physical Backup:

Stop the MySQL server:

sudo systemctl stop mysql

Copy the data directory (usually located at /var/lib/mysql):

sudo systemctl start mysql

4. Restoring a Backup

Restoring from a Logical Backup (mysqldump):

Restore a Single Database:

mysql -u username -p database_name < backup_file.sql

Example:

mysql -u root -p employees < employees_backup.sql

Restore All Databases:

mysql -u username -p < all_databases_backup.sql