# Physical Backup ----------------------------------------
# first stop the MySQL server
systemctl stop mysql
# Create a backup directory to store the backup files
mkdir /var/backups/mysql
# Copy the MySQL data directory to the backup directory
cp -r /var/lib/mysql /var/backups/mysql
# Start the MySQL server
systemctl start mysql
# Logical Backup-------------------------------------------
# create a backup of the "mydatabase" database
mysqldump -u root -p mydatabase > mydatabase_backup.sql
# Point in time Backup-------------------------------------
# create a backup of the "mydatabase" database up to January 1st, 2022
mysqldump -u root -p --single-transaction --set-gtid-purged=off --skip-lock-tables --flush-logs --master-data=2 --before-datetime='2022-01-01 12:00:00' mydatabase > mydatabase_backup.sql
# Differential Backup--------------------------------------
# take a full backup of the database
mysqldump --user=username --password dbname > full_backup.sql
# take a differential backup
mysqldump --user=username --password dbname --differential > diff_backup.sql
# Incremental Backup---------------------------------------
# take a full backup
mysqldump -u username -p --single-transaction --routines --triggers --all-databases > full_backup.sql
# take an incremental backup
mysqldump -u username -p --single-transaction --routines --triggers --all-databases --incremental --flush-logs --master-data=2 > incremental_backup.sql