Practical MySQL Backup and Restore Techniques
MySQL Backup and Recovery [20 exercises with solution]
1. Full Database Backup using mysqldump
Write a MySQL command to back up an entire database named "MyDatabase" using mysqldump.
2. Backup a Specific Table using mysqldump
Write a MySQL command to back up only the "Employees" table from the "HRDB" database using mysqldump.
3. Backup all Databases using mysqldump
Write a MySQL command to back up all databases on the MySQL server.
4. Backup Database with Compression
Write a MySQL command to back up the "MyDatabase" database and compress the backup file.
5. Restore a Full Database from Backup
Write a MySQL command to restore the "MyDatabase" database from a backup file named "MyDatabase_backup.sql".
6. Restore a Specific Table from Backup
Write a MySQL command to restore only the "Employees" table in the "HRDB" database from a backup file named "Employees_backup.sql".
7. Backup Stored Procedures and Triggers
Write a MySQL command to back up the "MyDatabase" database including stored procedures and triggers.
8. Backup Using mysqlpump for Parallel Processing
Write a MySQL command to back up the "MyDatabase" database using mysqlpump with parallel processing.
9. Export Data to a CSV File Using SELECT INTO OUTFILE
Write a MySQL query to export all rows from the "Employees" table into a CSV file for backup purposes.
10. Import Data from a CSV File using LOAD DATA INFILE
Write a MySQL query to import data from a CSV file into the "Employees" table.
11. Backup User Privileges from the mysql Database
Write a MySQL command to back up user privileges by exporting the mysql.user table.
12. Backup Multiple Databases using mysqldump with --databases Option
Write a MySQL command to back up multiple databases (e.g., "HRDB" and "FinanceDB") in a single command.
13. Backup Database Incrementally Using Binary Logs
Write a MySQL command to enable binary logging for incremental backup and point-in-time recovery.
14. Restore Database using Binary Log for Point-In-Time Recovery
Write a MySQL command to apply binary logs to restore a database to a specific point in time.
15. Schedule Regular Backups Using a Cron Job
Write a command-line entry to schedule a nightly backup of "MyDatabase" using mysqldump.
16. Backup Database Using MySQL Enterprise Backup
Write a command to back up "MyDatabase" using MySQL Enterprise Backup (if available).
17. Backup a Table to a Text File Using SELECT ... INTO OUTFILE
Write a MySQL query to back up the "Orders" table data into a text file.
18. Restore Data from a Text File Using LOAD DATA INFILE
Write a MySQL query to restore the "Orders" table data from a previously exported text file.
19. Verify Backup File Integrity Using Checksum
Write a command to generate a checksum for the backup file "MyDatabase_backup.sql" to verify its integrity.
20. Restore user Privileges from a Backup File
Write a MySQL command to restore the user privileges from a backup file of the mysql.user table.
More to Come !
Do not submit any solution of the above exercises at here, if you want to contribute go to the appropriate exercise page.