MySQL Full and Incremental Backup, Database Deletion, and Restoration Using Percona XtraBackup

1. Full backup operation

Execute a full backup of MySQL using XtraBackup with the appropriate configuration file and target directory that includes a timestamp.

xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=root --password=123456 --port=3306 --backup --target-dir=/backup/mysql/full_`date +%Y-%m-%d-%H-%M-%S`

2. Incremental backup operation

Perform an incremental backup that records changes since the last full backup.

xtrabackup --defaults-file=/etc/my.cnf --host=localhost --user=root --password=123456 --port=3306 --backup --target-dir=/backup/mysql/full_2022-03-09-10-42-52/incr_back/ --incremental-dir=/backup/incremental/incremental_bak_20220309/

3. Accidental deletion handling

List existing databases, then drop the unwanted databases sfqd and students_info .

show databases;

drop database sfqd;

drop database students_info;

After deletion, verify the remaining databases.

show databases;

4. Restoration process

4.1 Stop the MySQL service.

/etc/init.d/mysqld stop

Output: Shutting down MySQL............. SUCCESS!

4.2 Copy the MySQL data directory to a safe location.

mv /data/mysql/mysql /data/mysql/

4.3 Prepare the backup (apply logs only).

xtrabackup --prepare --apply-log-only --target-dir=/backup/mysql/full_2022-03-09-10-42-52

4.4 Copy the prepared backup back to the data directory.

xtrabackup --defaults-file=/etc/my.cnf --copy-back --target-dir=/backup/mysql/full_2022-03-09-10-42-52

4.5 Adjust ownership and permissions.

chown -R mysql.mysql /data/mysql/

4.6 Start the MySQL service.

/etc/init.d/mysqld start

Output: Starting MySQL...... SUCCESS!

4.7 Verify the restored data.

After the verification, the restoration is complete.