Chapter 4. Backup and Recovery
Backup and recovery are two distinct but related concepts in data protection. Backup is the process of making a copy of data and storing it in a safe location. Recovery, on the other hand, is the process of restoring data from a backup in the event of a data loss. The goal of backup and recovery is to ensure that data is available and usable when needed.
In this chapter, we discuss the fundamental concepts of backup and recovery and then explore the various types available. We also provide guidance on taking a backup, restoring a backup, and managing binary logs. Finally, you will learn how to manage your backup and recovery in order to ensure that your data is safe and easy to retrieve in the event of a disaster.
Before we dive into the specifics, it is essential to understand the basic concepts that underpin these critical activities. As a DBA, ensuring that your data is protected and recoverable in the event of a disaster is crucial. Data loss can occur for a variety of reasons, including hardware failure, human error, natural disasters, or malicious attacks. Without proper backup and recovery mechanisms in place, a significant amount of time, money, and effort may be required to restore lost data or even re-create it from scratch.
Factors to Consider When Choosing a Backup Strategy
Choosing a backup strategy that meets your business requirements can be challenging. Here are some factors to consider:
- Recovery point objective (RPO)
-
The RPO is the maximum amount of data loss that can be tolerated in the event of a disaster. A shorter RPO requires more frequent backups or point-in-time recovery with streaming capabilities. An RPO of a few seconds or 0 typically can be achieved only with frequent backups or points-in-time recovery.
- Recovery time objective (RTO)
-
The RTO is the maximum amount of time it takes to recover from a disaster. A shorter RTO requires a faster recovery mechanism, such as a warm or hot standby system.
- Backup type
-
MySQL supports several backup types, including physical backup, logical backup, incremental backup, and differential backup. Each backup type has its advantages and disadvantages, and the choice of backup type depends on the business requirements.
- Backup storage location
-
Backups should be stored in a secure location that is separate from the production system to avoid the risk of data loss due to a disaster affecting both the production system and the backup storage location.
In summary, we need to back up the entire data directory as a physical backup, including with binary logs. This backup will be essential for critical situations requiring the need to restore the instance or environment.
Difference Between Physical and Logical Backups
Logical backups and physical backups serve distinct purposes and are implemented differently.
A logical backup is a backup of the logical structure and contents of a
database, such as tables, views, stored procedures, triggers, and
data. It is created by dumping the data to a file by using utilities
like mydumper or MySQL Shell’s instance dump utility, schema dump utility, and table dump utility. Logical backups are portable and can be used to migrate a database from one server to another, or to restore specific tables or data to a database. However, they can be slower to create and restore than physical backups.
A physical backup, on the other hand, is a binary copy of the MySQL database files, including the data, indexes, and table structures. It is created by copying the physical files directly from the server to a backup storage device or server. Therefore, they are faster to create and restore than logical backups.
Both types of backups are important for ensuring the availability and integrity of a MySQL database. Logical backups are useful for migrating data between servers, restoring specific tables or data, and for long-term archival purposes. Physical backups are useful for disaster recovery, system-level backups, and for HA solutions such as database replication.
Physical Backups
The two most popular physical backups are MySQL Enterprise Backup and Percona XtraBackup.
MySQL Enterprise Backup
To back up and restore a MySQL database, you can use the MySQL Enterprise Backup tool. Backing up a MySQL instance is crucial for ensuring data recovery in case of any disaster or loss. It is essential to have a backup strategy in place that enables the restoration of data with minimal downtime.
For example, to configure this backup, create a new directory named backupdir:
[root@mysql80~]#mkdir/backupdir[root@mysql80~]#
This backup command instructs the MySQL backup utility (mysqlbackup) to perform a backup operation using the following options:
root@mysql80~]#mysqlbackup--user=root --password --backup-image=/backupdir/my.mbi--backup-dir=/backupdir/backup-tmp backup-to-imageMySQLEnterpriseBackupVer8.0.36-commercialforLinuxonx86_64(MySQLEnterprise-Commercial)Copyright(c)2003,2024,Oracleand/oritsaffiliates.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Startingwithfollowingcommandline...mysqlbackup--user=root--password--backup-image=/backupdir/my.mbi--backup-dir=/backupdir/backup-tmpbackup-to-imageIMPORTANT:Pleasecheckthatmysqlbackupruncompletessuccessfully.Attheendofasuccessful'backup-to-image'runmysqlbackupprints"mysqlbackup completed OK!"Enterpassword:24021611:42:48MAININFO:Establishingconnectiontoserver.WARNING:MYSQL_OPT_RECONNECTisdeprecatedandwillberemovedinafutureversion.24021611:42:48MAININFO:NoSSLoptionsspecified.24021611:42:48MAININFO:MySQLserverversionis'8.0.36'24021611:42:48MAININFO:MySQLservercompileosversionis'Linux'24021611:42:48MAININFO:Gotsomeserverconfigurationinformationfromrunningserver.
From the log, you can validate the server repository and backup configuration options:
24021611:42:48MAININFO:TheMySQLserverhasnoactivekeyring.--------------------------------------------------------------------ServerRepositoryOptions:--------------------------------------------------------------------datadir=/var/lib/mysql/innodb_data_home_dir=/var/lib/mysql/datafile/innodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextendinnodb_log_group_home_dir=/var/lib/mysql/innodb_undo_directory=/var/lib/mysql/innodb_undo_tablespaces=2innodb_buffer_pool_filename=ib_buffer_poolinnodb_page_size=16384innodb_checksum_algorithm=crc32--------------------------------------------------------------------BackupConfigOptions:--------------------------------------------------------------------datadir=/backupdir/backup-tmp/datadirinnodb_data_home_dir=/backupdir/backup-tmp/datadirinnodb_data_file_path=ibdata1:12M;ibdata2:10M:autoextendinnodb_log_group_home_dir=/backupdir/backup-tmp/datadirinnodb_undo_directory=/backupdir/backup-tmp/datadirinnodb_undo_tablespaces=2innodb_buffer_pool_filename=ib_buffer_poolinnodb_page_size=16384innodb_checksum_algorithm=crc32BackupImagePath=/backupdir/my.mbi24021611:42:48MAININFO:Uniquegeneratedbackupidforthisis17080837682941805
Upon the completion of the backup, you receive the status of completion as indicated in the log:
24021611:42:49RDR1INFO:Copying/var/lib/mysql/binlog.000012.24021611:42:49RDR1INFO:Completedthecopyofbinlogfiles...24021611:42:49RDR1INFO:Theserverinstanceisunlockedafter0.236seconds.24021611:42:49RDR1INFO:Readingallglobalvariablesfromtheserver.24021611:42:49RDR1INFO:Completedreadingofall640globalvariablesfromtheserver.24021611:42:49RDR1INFO:Writingserverdefaultsfiles'server-my.cnf'and'server-all.cnf'forserver'8.0.36'in'/backupdir/backup-tmp'.24021611:42:49RDR1INFO:Copyingmetafile/backupdir/backup-tmp/meta/backup_variables.txt.24021611:42:49RDR1INFO:Copyingmetafile/backupdir/backup-tmp/datadir/ibbackup_logfile.24021611:42:49RDR1INFO:Copyingmetafile/backupdir/backup-tmp/server-all.cnf.24021611:42:49RDR1INFO:Copyingmetafile/backupdir/backup-tmp/server-my.cnf.24021611:42:49RDR1INFO:Copyingmetafile/backupdir/backup-tmp/meta/backup_content.xml.24021611:42:49RDR1INFO:Copyingmetafile/backupdir/backup-tmp/meta/image_files.xml.24021611:42:49MAININFO:FullImageBackupoperationcompletedsuccessfully.24021611:42:49MAININFO:Backupimagecreatedsuccessfully.24021611:42:49MAININFO:ImagePath=/backupdir/my.mbi24021611:42:49MAININFO:MySQLbinlogposition:filenamebinlog.000012,position4131.-------------------------------------------------------------ParametersSummary-------------------------------------------------------------StartLSN:37130752LastCheckpointLSN:37130784EndLSN:37204275-------------------------------------------------------------
mysqlbackupcompletedOK![root@mysql80~]#
Here are the details of the various options used in the command:
--user-
Specifies the MySQL user with sufficient privileges to access and back up the database.
--password-
Provides the password for the MySQL user specified in the
--useroption. --backup-image-
Specifies the name and location of the backup file that will be created. This file will contain the entire MySQL instance.
--backup-dir-
Specifies the temporary backup directory where the backup files will be stored during the backup process.
backup-to-image-
Specifies the backup type.
Once the command is executed, the backup process will start, and the
backup files will be stored in the specified backup directory. Upon
completion of the backup process, the message mysqlbackup completed
OK! will be displayed.
Upon completing a backup, it’s essential to verify the backup’s integrity and ensure that all data is correctly backed up. This can help avoid data loss due to corrupted backups.
The following command can be used to validate a backup image:
[root@mysql80~]#mysqlbackup--backup-image=/backupdir/my.mbi validateMySQLEnterpriseBackupVer8.0.36-commercialforLinuxonx86_64(MySQLEnterprise-Commercial)Copyright(c)2003,2024,Oracleand/oritsaffiliates.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Startingwithfollowingcommandline...mysqlbackup--backup-image=/backupdir/my.mbivalidateIMPORTANT:Pleasecheckthatmysqlbackupruncompletessuccessfully.Attheendofasuccessful'validate'runmysqlbackupprints"mysqlbackup completed OK!".24021611:49:26MAININFO:BackupImageMEBversionstring:8.0.3624021611:49:26MAININFO:MySQLserverversionis'8.0.36'24021611:49:26MAININFO:Thebackupimagehasnokeyring.24021611:49:26MAININFO:Creating14bufferseachofsize16777216.24021611:49:26MAININFO:Validateoperationstartswithfollowingthreads1read-threads6process-threads24021611:49:26MAININFO:Validatingimage.../backupdir/my.mbi24021611:49:26PCR6INFO:Validate:[Dir]:meta24021611:49:26PCR5INFO:Validate:[Dir]:datadir/appdb24021611:49:26PCR5INFO:Validate:[Dir]:datadir/datafile24021611:49:26PCR6INFO:Validate:[Dir]:datadir/mydatabase24021611:49:26PCR4INFO:Validate:[Dir]:datadir/mysql24021611:49:26PCR3INFO:Validate:[Dir]:datadir/mytestdb
Upon the completion of the validation, you will receive the backup completion status as displayed in the log:
240216 11:49:26 MAIN INFO: datadir/sakila/ fts_0000000000000442_being_deleted.ibd validated. 240216 11:49:26 MAIN INFO: Validate operation completed successfully. 240216 11:49:26 MAIN INFO: Backup Image validation successful. 240216 11:49:26 MAIN INFO: Source Image Path = /backupdir/my.mbi mysqlbackup completed OK! [root@mysql80 ~]#
Let’s break this down further:
mysqlbackup-
Validates the backup image
--backup-image-
Specifies the path to the backup image
--validate-
Specifies the validation operation
If the validation is successful, the following message will be displayed:
mysqlbackup completed OK!
It’s also essential to check the backup logs to ensure that no errors occurred during the backup process. For example:
[root@mysql80meta]#catMEB_2024-02-16.11-42-48_backup-to-image.log|grep"error"[root@mysql80meta]#
These commands will display the backup-related entries in the backup logfiles.
Depending on the situation, such as a crash, human mistake and data loss, or disaster recovery, restoring a database can be a crucial activity. Before restoring a backup, you need to shut down the MySQL instance. This is essential to avoid any data inconsistency issues. You can use the following command to stop the MySQL server:
[root@mysql80meta]#systemctlstopmysqld[root@mysql80meta]#
After you shut down the MySQL server, delete all the files located in
the data directory. Delete all the files that you can find under the directory specified by the restoration options --innodb_data_home_dir, --innodb_log_group_home_dir, and
--innodb_undo_directory as well if the directories are different from the data
directory.
Prior to executing the rm -rf * command, ensure that you are in the correct current directory and confirm that it is the data directory:
[root@mysql80mysql]#pwd/var/lib/mysql[root@mysql80mysql]#rm-rf*[root@mysql80mysql]#
Once you have deleted all the necessary files, use the
copy-back-and-apply-log option with the mysqlbackup command to restore the backup. This option is used to apply all the incremental changes from the backup to the target server. Here is a sample command for your reference:
[root@mysql80backup-tmp]#mysqlbackup--datadir=/var/lib/mysql--backup-image=/backupdir/my.mbi --backup-dir=/backupdir/backup-tmp1copy-back-and-apply-logMySQLEnterpriseBackupVer8.0.36-commercialforLinuxonx86_64(MySQLEnterprise-Commercial)Copyright(c)2003,2024,Oracleand/oritsaffiliates.OracleisaregisteredtrademarkofOracleCorporationand/oritsaffiliates.Othernamesmaybetrademarksoftheirrespectiveowners.Startingwithfollowingcommandline...mysqlbackup--datadir=/var/lib/mysql--backup-image=/backupdir/my.mbi--backup-dir=/backupdir/backup-tmp1copy-back-and-apply-log...24021612:10:24PCR1INFO:LastMySQLbinlogfileposition04131,filenamebinlog.00001224021612:10:24PCR1INFO:Thefirstdatafileis'/var/lib/mysql/datafile/ibdata1'andthenewcreatedlogfilesareat'/var/lib/mysql'24021612:10:24MAININFO:Apply-logoperationcompletedsuccessfully.24021612:10:24MAININFO:FullBackuphasbeenrestoredsuccessfully.mysqlbackupcompletedOK![root@mysql80backup-tmp]#
The restored files currently have ownership set to the root user:
[root@mysql80mysql]#cd/var/lib/mysql[root@mysql80mysql]#[root@mysql80mysql]#ls-ltrtotal80104-rw-r-----. 1root root56 Feb 16 12:10 backup-auto.cnf-rw-r-----. 1root root14 Feb 16 12:10 backup-mysqld-auto.cnfdrwxr-x---. 2root root28 Feb 16 12:10 sysdrwxr-x---. 2root root91 Feb 16 12:10 worlddrwxr-x---. 2root root4096 Feb 16 12:10 sakiladrwxr-x---. 2root root44 Feb 16 12:10 mydatabase-rw-r-----. 1root root114688 Feb 16 12:10 my_tablespace.ibddrwxr-x---. 2root root164 Feb 16 12:10 appdbdrwxr-x---. 2root root72 Feb 16 12:10 datafile-rw-r-----. 1root root180 Feb 16 12:10 binlog.000011-rw-r-----. 1root root1102 Feb 16 12:10 binlog.000010drwxr-x---. 2root root140 Feb 16 12:10 mytestdbdrwxr-x---. 2root root170 Feb 16 12:10 mysqldrwxr-x---. 2root root8192 Feb 16 12:10 performance_schema-rw-r-----. 1root root4131 Feb 16 12:10 binlog.000012-rw-r-----. 1root root192 Feb 16 12:10 binlog.index-rw-r-----. 1root root660 Feb 16 12:10 server-my.cnf-rw-r-----. 1root root19983 Feb 16 12:10 server-all.cnf-rw-r-----. 1root root16777216 Feb 16 12:10 undo_002-rw-r-----. 1root root16777216 Feb 16 12:10 undo_001-rw-r-----. 1root root16777216 Feb 16 12:10 my_undo_tablespace.ibu-rw-r-----. 1root root27262976 Feb 16 12:10 mysql.ibddrwxr-x---. 2root root23 Feb 16 12:10 'innodb_redo'-rw-r--r--. 1root root723 Feb 16 12:10 backup_variables.txt[root@mysql80mysql]
After restoring the backup, change the ownership of the restored files
to the mysql user. You can use the following command to change the
ownership:
[root@mysql80mysql]#chown-Rmysql.mysql/var/lib/mysql[root@mysql80mysql]#[root@mysql80mysql]#ls-ltrtotal80104-rw-r-----. 1mysql mysql56 Feb 16 12:10 backup-auto.cnf-rw-r-----. 1mysql mysql14 Feb 16 12:10 backup-mysqld-auto.cnfdrwxr-x---. 2mysql mysql28 Feb 16 12:10 sysdrwxr-x---. 2mysql mysql91 Feb 16 12:10 worlddrwxr-x---. 2mysql mysql4096 Feb 16 12:10 sakiladrwxr-x---. 2mysql mysql44 Feb 16 12:10 mydatabase-rw-r-----. 1mysql mysql114688 Feb 16 12:10 my_tablespace.ibddrwxr-x---. 2mysql mysql164 Feb 16 12:10 appdbdrwxr-x---. 2mysql mysql72 Feb 16 12:10 datafile-rw-r-----. 1mysql mysql180 Feb 16 12:10 binlog.000011-rw-r-----. 1mysql mysql1102 Feb 16 12:10 binlog.000010drwxr-x---. 2mysql mysql140 Feb 16 12:10 mytestdbdrwxr-x---. 2mysql mysql170 Feb 16 12:10 mysqldrwxr-x---. 2mysql mysql8192 Feb 16 12:10 performance_schema-rw-r-----. 1mysql mysql4131 Feb 16 12:10 binlog.000012-rw-r-----. 1mysql mysql192 Feb 16 12:10 binlog.index-rw-r-----. 1mysql mysql660 Feb 16 12:10 server-my.cnf-rw-r-----. 1mysql mysql19983 Feb 16 12:10 server-all.cnf-rw-r-----. 1mysql mysql16777216 Feb 16 12:10 undo_002-rw-r-----. 1mysql mysql16777216 Feb 16 12:10 undo_001-rw-r-----. 1mysql mysql16777216 Feb 16 12:10 my_undo_tablespace.ibu-rw-r-----. 1mysql mysql27262976 Feb 16 12:10 mysql.ibddrwxr-x---. 2mysql mysql23 Feb 16 12:10 'innodb_redo'-rw-r--r--. 1mysql mysql723 Feb 16 12:10 backup_variables.txt[root@mysql80mysql]
Once you have changed the ownership, start the MySQL server by using the following command:
[root@mysql80mysql]#systemctlstartmysqld[root@mysql80mysql]#
If you’re restoring a backup on a replication instance, you’ll need the binary logfile name and position from which replication should commence. You can find these details in the backup_variables.txt file located in a meta directory within the backup directory. Open the file and retrieve the most recent binary log position along with the corresponding logfile number, which are both stored within the file. This information will be necessary for configuring the replica server to begin replication from the correct position in the binary log:
[root@mysql80meta]#pwd/backupdir/backup-tmp/meta[root@mysql80meta]#catbackup_variables.txt|grepbinlog_positionbinlog_position=binlog.000012:4131[root@mysql80meta]#
Percona XtraBackup
Percona XtraBackup is an open source tool used for backing up and restoring MySQL databases. Before you start performing backups using Percona XtraBackup, certain prerequisites must be met:
-
Percona XtraBackup must be installed on the server.
-
The MySQL instance being backed up must be running and accessible.
-
Sufficient disk space must be available to store the backup in a local share or in a remote server.
Backing up from a replication server
Completing a full backup from a replica server using Percona XtraBackup is recommended as it reduces impact on the primary server. Initiating a backup on the primary server can impair performance, which can affect the applications that rely on it. By taking the backup from a replica server, you can minimize the impact on the primary server, allowing it to continue serving the applications without interruption.
A full backup, which backs up the entire database, is the most comprehensive type of backup. The backup includes all the data, indexes, tables, views, triggers, and stored procedures. Here’s an example:
[root@mysql80~]#xtrabackup--backup --target-dir=/root/backupdir -u root -p2024-02-20T07:27:13.279554-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--datadir=/var/lib/mysql2024-02-20T07:27:13.279897-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--user=root --password=* --backup=1 --target-dir=/root/backupdir--user=root --passwordEnterpassword:xtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)24022007:27:14version_checkConnectingtoMySQLserverwithDSN'dbi:mysql:;mysql_read_default_group=xtrabackup'as'root'(usingpassword:YES).24022007:27:14version_checkConnectedtoMySQLserver24022007:27:14version_checkExecutingaversioncheckagainsttheserver...#Asoftwareupdateisavailable:24022007:27:15version_checkDone.2024-02-20T07:27:15.257381-00:000[Note][MY-011825][Xtrabackup]ConnectingtoMySQLserverhost:localhost,user:root,password:set,port:notset,socket:notset2024-02-20T07:27:15.269103-00:000[Note][MY-011825][Xtrabackup]Usingserverversion8.0.36..2024-02-20T07:27:19.627144-00:000[Note][MY-011825][Xtrabackup]Writing/root/backupdir/backup-my.cnf2024-02-20T07:27:19.627324-00:000[Note][MY-011825][Xtrabackup]Done:Writingfile/root/backupdir/backup-my.cnf2024-02-20T07:27:19.736769-00:000[Note][MY-011825][Xtrabackup]Writing/root/backupdir/xtrabackup_info2024-02-20T07:27:19.736961-00:000[Note][MY-011825][Xtrabackup]Done:Writingfile/root/backupdir/xtrabackup_info2024-02-20T07:27:19.768177-00:000[Note][MY-011825][Xtrabackup]Transactionlogoflsn(20366453)to(20366463)wascopied.2024-02-20T07:27:19.998055-00:000[Note][MY-011825][Xtrabackup]completedOK![root@mysql80~]#
See the Percona docs for a complete list of variable options available with the tool.
Here are the details of the two options used in the command. To initiate a backup, execute XtraBackup with the --backup option. The option --target-dir=DIRECTORY designates the backup’s destination directory. If the specified directory doesn’t exist, XtraBackup will create it. When the directory exists and is empty, the backup will proceed successfully. However, if the directory contains existing files, XtraBackup won’t overwrite them, resulting in a failure with operating system error 17 (file exists).
Note
The --slave-info option is particularly beneficial when creating
a backup of a replication slave server. It not only displays the name
and binary log position of the master server but also records this
information in the xtrabackup_slave_info file as a CHANGE MASTER
command. This allows you to conveniently set up a new slave server for
the same master by starting a slave server on the backup and running the
CHANGE MASTER command using the binary log position saved in the
xtrabackup_slave_info file.
Restoring a database
This section explains how to restore a backup taken with Percona XtraBackup. However, before restoring the backup, you need to prepare the backup files by using
the xtrabackup --prepare command. This step applies the pending changes to the backup data so that it is in a consistent state:
[root@mysql80~]#xtrabackup--prepare --target-dir=/root/backupdir2024-02-20T07:29:48.539204-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--innodb_checksum_algorithm=crc32 --innodb_log_checksums=1--innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648--innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2--server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0--innodb_undo_log_encrypt=02024-02-20T07:29:48.539575-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--prepare=1 --target-dir=/root/backupdirxtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)2024-02-20T07:29:48.539839-00:000[Note][MY-011825][Xtrabackup]cdto/root/backupdir/2024-02-20T07:29:48.543518-00:000[Note][MY-011825][Xtrabackup]Thistargetseemstobenotpreparedyet.2024-02-20T07:29:48.562638-00:000[Note][MY-011825][Xtrabackup]xtrabackup_logfiledetected:size=8388608,start_lsn=(20366453)..2024-02-20T07:29:49.169641-00:000[Note][MY-011825][Xtrabackup]Completedloadingof2tablespacesintocachein0.00413599seconds2024-02-20T07:29:49.221375-00:000[Note][MY-011825][Xtrabackup]Timetakentobuilddictionary:0.0515769seconds2024-02-20T07:29:49.239865-00:000[Note][MY-011825][Xtrabackup]startingshutdownwithinnodb_fast_shutdown=12024-02-20T07:29:49.240024-00:000[Note][MY-012330][InnoDB]FTSoptimizethreadexiting.2024-02-20T07:29:50.232093-00:000[Note][MY-013072][InnoDB]Startingshutdown...2024-02-20T07:29:50.240493-00:000[Note][MY-013084][InnoDB]Logbackgroundthreadsarebeingclosed...2024-02-20T07:29:50.260550-00:000[Note][MY-012980][InnoDB]Shutdowncompleted;logsequencenumber203668702024-02-20T07:29:50.266082-00:000[Note][MY-011825][Xtrabackup]completedOK![root@mysql80~]#
Stop the MySQL server on the machine where you want to restore the backup:
[root@mysql80~]#sudosystemctlstopmysqld[root@mysql80~]#
Copy the backup files from their original backup location to the destination where you intend to perform the restoration. You can use a tool like rsync or scp to do this efficiently.
Copy the MySQL configuration files from the backup location to the MySQL configuration directory on the machine where you want to restore the backup. The location of the configuration directory may vary depending on your operating system. If the backup wasn’t copied to the data directory, you can use the xtrabackup
--copy-back command to copy the files from the backup directory to the MySQL data directory:
[root@mysql80~]#sudoxtrabackup--copy-back --target-dir=/root/backupdir2024-02-20T07:34:03.208252-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--datadir=/var/lib/mysql2024-02-20T07:34:03.208429-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--user=root --password=* --copy-back=1 --target-dir=/root/backupdirxtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)2024-02-20T07:34:03.208499-00:000[Note][MY-011825][Xtrabackup]cdto/root/backupdir/2024-02-20T07:34:03.212994-00:000[Note][MY-011825][Xtrabackup]Copyingundo_001to/var/lib/mysql/undo_0012024-02-20T07:34:03.372784-00:000[Note][MY-011825][Xtrabackup]Done:Copyingundo_001to/var/lib/mysql/undo_0012024-02-20T07:34:03.379912-00:000[Note][MY-011825][Xtrabackup]Copyingundo_002to/var/lib/mysql/undo_0022024-02-20T07:34:03.481821-00:000[Note][MY-011825][Xtrabackup]Done:Copyingundo_002to/var/lib/mysql/undo_0022024-02-20T07:34:03.488697-00:000[Note][MY-011825][Xtrabackup]Copyingibdata1to/var/lib/mysql/ibdata12024-02-20T07:34:03.557571-00:000[Note][MY-011825][Xtrabackup]Done:Copyingibdata1to/var/lib/mysql/ibdata1...2024-02-20T07:34:04.081817-00:001[Note][MY-011825][Xtrabackup]Copying./ib_buffer_poolto/var/lib/mysql/ib_buffer_pool2024-02-20T07:34:04.082713-00:001[Note][MY-011825][Xtrabackup]Done:Copying./ib_buffer_poolto/var/lib/mysql/ib_buffer_pool2024-02-20T07:34:04.084413-00:001[Note][MY-011825][Xtrabackup]Copying./xtrabackup_infoto/var/lib/mysql/xtrabackup_info2024-02-20T07:34:04.084548-00:001[Note][MY-011825][Xtrabackup]Done:Copying./xtrabackup_infoto/var/lib/mysql/xtrabackup_info2024-02-20T07:34:04.086055-00:001[Note][MY-011825][Xtrabackup]Creatingdirectory./#innodb_redo2024-02-20T07:34:04.086133-00:001[Note][MY-011825][Xtrabackup]Done:creatingdirectory./#innodb_redo2024-02-20T07:34:04.086241-00:001[Note][MY-011825][Xtrabackup]Copying./ibtmp1to/var/lib/mysql/ibtmp12024-02-20T07:34:04.131401-00:001[Note][MY-011825][Xtrabackup]Done:Copying./ibtmp1to/var/lib/mysql/ibtmp12024-02-20T07:34:04.177825-00:000[Note][MY-011825][Xtrabackup]completedOK![root@mysql80~]#
After copying the files, you need to set the ownership and permissions of the data directory to the mysql user:
[root@mysql80~]#sudochown-Rmysql:mysql/var/lib/mysql[root@mysql80~]#
Start the MySQL server with the following command:
[root@mysql80~]#sudosystemctlstartmysqld[root@mysql80~]#
After starting MySQL, you need to verify the log and confirm that the MySQL instance is ready for connection. If you tail the mysqld error log, you should get a similar result:
[root@mysql80~]#tail-2/var/log/mysqld.log2024-02-20T07:37:48.624988Z0[System][MY-011323][Server]XPluginreadyforconnections.Bind-address:'::'port:33060,socket:/var/run/mysqld/mysqlx.sock2024-02-20T07:37:48.625234Z0[System][MY-010931][Server]/usr/sbin/mysqld:readyforconnections.Version:'8.0.36'socket:'/var/lib/mysql/mysql.sock'port:3306MySQLCommunityServer-GPL.[root@mysql80~]#
Using incremental backups
Percona XtraBackup provides support for incremental backups, allowing it to copy only the data that has changed since the previous backup. You can perform multiple incremental backups. This allows you to establish a backup schedule, such as performing a full backup once a week and incremental backups every day, or full backups every day with incremental backups every hour.
The incremental backup process works by leveraging the LSN that is associated with each InnoDB page. The LSN serves as a system version number for the entire database and indicates when a page was last modified. During an incremental backup, only pages that have an LSN that is newer than the previous incremental or full backup’s LSN are copied. An algorithm is used to identify the pages that meet this criteria by reading the data pages and checking their respective LSN values.
To create an incremental backup by using Percona XtraBackup, start with a
regular full backup. After taking the full backup, the xtrabackup
utility writes a file called xtrabackup_checkpoints to the backup’s
target directory. This file contains a line
showing to_lsn, which
represents the database’s LSN at the end of the
backup process. You can take a full backup by running the following command:
[root@mysql8~]#mkdir-p/data/backups/base[root@mysql8~]#xtrabackup--backup --target-dir=/data/backups/base2024-02-23T11:50:13.697883-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--datadir=/var/lib/mysql2024-02-23T11:50:13.698792-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--user=root --password=* --backup=1 --target-dir=/data/backups/basextrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)Can'tlocateEnglish.pmin@INC(youmayneedtoinstalltheEnglishmodule)(@INCcontains:/usr/local/lib64/perl5/5.32/usr/local/share/perl5/5.32/usr/lib64/perl5/vendor_perl/usr/share/perl5/vendor_perl/usr/lib64/perl5/usr/share/perl5)at-line3.BEGINfailed--compilation aborted at - line 3.2024-02-23T11:50:13.728094-00:000[Note][MY-011825][Xtrabackup]ConnectingtoMySQLserverhost:localhost,user:root,password:set,port:notset,socket:notset2024-02-23T11:50:14.135079-00:000[Note][MY-011825][Xtrabackup]Usingserverversion8.0.362024-02-23T11:50:14.149822-00:000[Note][MY-011825][Xtrabackup]ExecutingLOCKINSTANCEFORBACKUP...2024-02-23T11:50:14.151389-00:000[Note][MY-011825][Xtrabackup]usesposix_fadvise().2024-02-23T11:50:14.151443-00:000[Note][MY-011825][Xtrabackup]cdto/var/lib/mysql2024-02-23T11:50:14.151460-00:000[Note][MY-011825][Xtrabackup]openfileslimitrequested0,setto10242024-02-23T11:50:14.174389-00:000[Note][MY-011825][Xtrabackup]usingthefollowingInnoDBconfiguration:2024-02-23T11:50:14.174418-00:000[Note][MY-011825][Xtrabackup]innodb_data_home_dir=.2024-02-23T11:50:14.174429-00:000[Note][MY-011825][Xtrabackup]innodb_data_file_path=ibdata1:12M:autoextend2024-02-23T11:50:14.174470-00:000[Note][MY-011825][Xtrabackup]innodb_log_group_home_dir=./2024-02-23T11:50:14.174482-00:000[Note][MY-011825][Xtrabackup]innodb_log_files_in_group=2....2024-02-23T11:50:32.554338-00:000[Note][MY-011825][Xtrabackup]Done:Writingfile/data/backups/base/backup-my.cnf2024-02-23T11:50:33.071966-00:000[Note][MY-011825][Xtrabackup]Writing/data/backups/base/xtrabackup_info2024-02-23T11:50:33.072211-00:000[Note][MY-011825][Xtrabackup]Done:Writingfile/data/backups/base/xtrabackup_info2024-02-23T11:50:33.188617-00:000[Note][MY-011825][Xtrabackup]Transactionlogoflsn(20289655)to(20289665)wascopied.2024-02-23T11:50:33.405903-00:000[Note][MY-011825][Xtrabackup]completedOK![root@mysql8~]#
After completing the full backup, you can view the xtrabackup_checkpoints file to check the LSN value. The file will contain information similar to the following, depending on the LSN number:
[root@mysql8~]#cat/data/backups/base/xtrabackup_checkpointsbackup_type=full-backupedfrom_lsn=0to_lsn=20289655last_lsn=20289655flushed_lsn=20289655redo_memory=0redo_frames=0[root@mysql8~]#
This output confirms that the full backup was successful and shows the
to_lsn value that will be used as a reference point for the next
incremental backup.
Once you have taken a full backup, you can create an incremental backup
based on it by using the xtrabackup command. The following command can be
used to create an incremental backup:
[root@mysql8~]#xtrabackup--backup --target-dir=/data/backups/inc1 \--incremental-basedir=/data/backups/base -u root -p2024-02-23T12:07:38.147005-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--datadir=/var/lib/mysql2024-02-23T12:07:38.147368-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--user=root --password=* --backup=1 --target-dir=/data/backups/inc1--incremental-basedir=/data/backups/base --user=root --passwordEnterpassword:xtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)Can't locate English.pm in @INC (you may need to install the English module) (@INC contains: /usr/local/lib64/perl5/5.32 /usr/local/share/perl5/5.32 /usr/ lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/ share/perl5) at - line 3. BEGIN failed--compilation aborted at - line 3. 2024-02-23T12:07:39.895793-00:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set 2024-02-23T12:07:39.918181-00:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.36 2024-02-23T12:07:39.935737-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./ 2024-02-23T12:07:52.016205-00:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'binlog.000006', position '157'2024-02-23T12:07:52.016406-00:000[Note][MY-011825][Xtrabackup]Writing/data/backups/inc1/backup-my.cnf2024-02-23T12:07:52.016580-00:000[Note][MY-011825][Xtrabackup]Done:Writingfile/data/backups/inc1/backup-my.cnf2024-02-23T12:07:52.185241-00:000[Note][MY-011825][Xtrabackup]Writing/data/backups/inc1/xtrabackup_info2024-02-23T12:07:52.185445-00:000[Note][MY-011825][Xtrabackup]Done:Writingfile/data/backups/inc1/xtrabackup_info2024-02-23T12:07:53.188689-00:000[Note][MY-011825][Xtrabackup]Transactionlogoflsn(20784993)to(20784993)wascopied.2024-02-23T12:07:53.414360-00:000[Note][MY-011825][Xtrabackup]completedOK![root@mysql8~]#
This command creates an incremental backup in the /data/backups/inc1/ directory, based on the previous full backup taken in /data/backups/base/. The delta files generated by this command represent the changes made to the database since the LSN value of the previous backup. For example, you may see files such as ibdata1.delta and test/table1.ibd.delta in the incremental backup directory.
After completing the incremental backup, you can check the
xtrabackup_checkpoints file in the /data/backups/inc1/ directory. This
file should show the new LSN value, as well as the incremental-basedir
value that was used as the reference point for the backup process. The
content of the xtrabackup_checkpoints file may look similar to the
following:
[root@mysql8~]#cat/data/backups/inc1/xtrabackup_checkpointsbackup_type=incrementalfrom_lsn=20289655to_lsn=20784993last_lsn=20784993flushed_lsn=20784993redo_memory=0redo_frames=0[root@mysql8~]#
The from_lsn value in the xtrabackup_checkpoints file represents the
starting LSN of the incremental backup, and it should be the same as the
to_lsn value of the previous or base backup’s checkpoint file.
After creating the first incremental backup, you can use it as the base for creating another incremental backup. You can use the following command to create an incremental backup based on the previous incremental backup:
[root@mysql8~]#xtrabackup--backup --target-dir=/data/backups/inc2 \--incremental-basedir=/data/backups/inc12024-02-23T13:10:43.005350-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--datadir=/var/lib/mysql2024-02-23T13:10:43.008358-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--user=root --password=* --backup=1 --target-dir=/data/backups/inc2--incremental-basedir=/data/backups/inc1xtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)Can't locate English.pm in @INC (you may need to install the English module) (@INC contains: /usr/local/lib64/perl5/5.32 /usr/local/share/perl5/5.32 /usr/ lib64/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/ share/perl5) at - line 3. BEGIN failed--compilation aborted at - line 3. 2024-02-23T13:10:43.044764-00:00 0 [Note] [MY-011825] [Xtrabackup] Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set 2024-02-23T13:10:43.053579-00:00 0 [Note] [MY-011825] [Xtrabackup] Using server version 8.0.36 2024-02-23T13:10:43.056222-00:00 0 [Note] [MY-011825] [Xtrabackup] Executing LOCK INSTANCE FOR BACKUP ... 2024-02-23T13:10:43.057492-00:00 0 [Note] [MY-011825] [Xtrabackup] incremental backup from 20784993 is enabled. 2024-02-23T13:10:43.057721-00:00 0 [Note] [MY-011825] [Xtrabackup] uses posix_fadvise(). 2024-02-23T13:10:43.057745-00:00 0 [Note] [MY-011825] [Xtrabackup] cd to /var/ lib/mysql 2024-02-23T13:10:43.057757-00:00 0 [Note] [MY-011825] [Xtrabackup] open files limit requested 0, set to 1024 2024-02-23T13:10:43.060579-00:00 0 [Note] [MY-011825] [Xtrabackup] using the following InnoDB configuration: 2024-02-23T13:10:43.060604-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_home_dir = . 2024-02-23T13:10:43.060613-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_data_file_path = ibdata1:12M:autoextend 2024-02-23T13:10:43.060650-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_group_home_dir = ./ 2024-02-23T13:10:43.060662-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_files_in_group = 2 2024-02-23T13:10:43.060672-00:00 0 [Note] [MY-011825] [Xtrabackup] innodb_log_file_size = 50331648 .... 2024-02-23T13:10:50.751443-00:00 0 [Note] [MY-011825] [Xtrabackup] MySQL binlog position: filename 'binlog.000011', position '157'2024-02-23T13:10:50.814250-00:000[Note][MY-011825][Xtrabackup]Writing/data/backups/inc2/backup-my.cnf2024-02-23T13:10:50.814471-00:000[Note][MY-011825][Xtrabackup]Done:Writingfile/data/backups/inc2/backup-my.cnf2024-02-23T13:10:51.014542-00:000[Note][MY-011825][Xtrabackup]Writing/data/backups/inc2/xtrabackup_info2024-02-23T13:10:51.014890-00:000[Note][MY-011825][Xtrabackup]Done:Writingfile/data/backups/inc2/xtrabackup_info2024-02-23T13:10:51.018154-00:000[Note][MY-011825][Xtrabackup]Transactionlogoflsn(21291941)to(21291941)wascopied.2024-02-23T13:10:51.140104-00:000[Note][MY-011825][Xtrabackup]completedOK![root@mysql8~]#
This command creates a new incremental backup in the /data/backups/inc2/ directory, based on the previous incremental backup in /data/backups/inc1/. The xtrabackup_checkpoints file in this directory should show the new LSN value, which represents the endpoint of the new incremental backup.
You can continue creating multiple incremental backups in this way, with each new incremental backup based on the previous one. Each incremental backup will contain only the changes made since the previous backup, which helps reduce the amount of time and space needed for backup and restore operations:
[root@mysql8~]#cat/data/backups/inc2/xtrabackup_checkpointsbackup_type=incrementalfrom_lsn=20784993to_lsn=21291941last_lsn=21291941flushed_lsn=21291941redo_memory=0redo_frames=0[root@mysql8~]#
You’ve now taken a full backup along with two incremental backups, all stored within the backup directory. Each backup is stored in its respective directory within this backup directory. Upon listing the contents of the backup directory by using the
ls -ltr command, you’ll find the following:
[root@mysql8backups]#ls-ltrtotal12drwxr-xr-x.5rootroot4096Feb2311:50basedrwxr-x---. 6 root root 4096 Feb 23 12:07 inc1drwxr-x---. 6 root root 4096 Feb 23 13:10 inc2[root@mysql8backups]#
Preparing incremental backups
The --prepare step for incremental backups is different from that for full backups. In full backups, the --prepare step performs two types of operations to make the database consistent: it replays committed transactions from the logfile against the data files, and it rolls back uncommitted transactions. However, when preparing an incremental backup, you need to skip the rollback of uncommitted transactions. This is because transactions that were uncommitted at the time of your backup may still be in progress and are likely to be committed in the next incremental backup.
To prevent the rollback phase during the --prepare step for incremental
backups, use the --apply-log-only option. This option only
applies the logfiles to the backup to update its pages and does not
roll back any transactions. After applying the logfiles, the backup will be
ready to be restored to the point in time when the backup was taken:
[root@mysql8backups]#xtrabackup--prepare --apply-log-only \--target-dir=/data/backups/base2024-02-23T13:28:08.266142-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--innodb_checksum_algorithm=crc32 --innodb_log_checksums=1--innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648--innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2--server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0--innodb_undo_log_encrypt=02024-02-23T13:28:08.266501-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--prepare=1 --apply-log-only=1 --target-dir=/data/backups/basextrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)2024-02-23T13:28:08.266735-00:000[Note][MY-011825][Xtrabackup]cdto/data/backups/base/2024-02-23T13:28:08.268271-00:000[Note][MY-011825][Xtrabackup]Thistargetseemstobenotpreparedyet.2024-02-23T13:28:08.290071-00:000[Note][MY-011825][Xtrabackup]inititialize_service_handlessuceeded2024-02-23T13:28:08.290782-00:000[Note][MY-011825][Xtrabackup]usingthefollowingInnoDBconfigurationforrecovery:2024-02-23T13:28:08.290856-00:000[Note][MY-011825][Xtrabackup]innodb_data_home_dir=.2024-02-23T13:28:08.290906-00:000[Note][MY-011825][Xtrabackup]innodb_data_file_path=ibdata1:12M:autoextend
After this command completes, the output should end with text similar to this:
2024-02-23T13:28:09.840970-00:000[Note][MY-013072][InnoDB]Startingshutdown...2024-02-23T13:28:09.848432-00:000[Note][MY-013084][InnoDB]Logbackgroundthreadsarebeingclosed...2024-02-23T13:28:09.868724-00:000[Note][MY-012980][InnoDB]Shutdowncompleted;logsequencenumber202896652024-02-23T13:28:09.876150-00:000[Note][MY-011825][Xtrabackup]completedOK![root@mysql8backups]#
To apply the first incremental backup to the full backup, you can run the following command:
[root@mysql8backups]#xtrabackup--prepare --apply-log-only --target-dir=/data/backups/base\--incremental-dir=/data/backups/inc12024-02-23T13:30:43.873233-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--innodb_checksum_algorithm=crc32 --innodb_log_checksums=1--innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648--innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2--server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0--innodb_undo_log_encrypt=02024-02-23T13:30:43.874162-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--prepare=1 --apply-log-only=1 --target-dir=/data/backups/base--incremental-dir=/data/backups/inc1xtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)2024-02-23T13:30:43.877184-00:000[Note][MY-011825][Xtrabackup]incrementalbackupfrom20289655isenabled.2024-02-23T13:30:43.877299-00:000[Note][MY-011825][Xtrabackup]cdto/data/backups/base/2024-02-23T13:30:43.878200-00:000[Note][MY-011825][Xtrabackup]Thistargetseemstobealreadypreparedwith--apply-log-only.2024-02-23T13:30:43.888050-00:000[Note][MY-011825][Xtrabackup]xtrabackup_logfiledetected:size=8388608,start_lsn=(20784993)2024-02-23T13:30:43.888535-00:000[Note][MY-011825][Xtrabackup]usingthefollowingInnoDBconfigurationforrecovery:2024-02-23T13:30:43.888643-00:000[Note][MY-011825][Xtrabackup]innodb_data_home_dir=.2024-02-23T13:30:43.888699-00:000[Note][MY-011825][Xtrabackup]innodb_data_file_path=ibdata1:12M:autoextend2024-02-23T13:30:43.888796-00:000[Note][MY-011825][Xtrabackup]innodb_log_group_home_dir=/data/backups/inc1/2024-02-23T13:30:46.424279-00:000[Note][MY-011825][Xtrabackup]Done:Copying/data/backups/inc1/binlog.000006to./binlog.0000062024-02-23T13:30:46.425801-00:000[Note][MY-011825][Xtrabackup]Copying/data/backups/inc1/binlog.indexto./binlog.index2024-02-23T13:30:46.425940-00:000[Note][MY-011825][Xtrabackup]Done:Copying/data/backups/inc1/binlog.indexto./binlog.index2024-02-23T13:30:46.427799-00:000[Note][MY-011825][Xtrabackup]completedOK![root@mysql8backups]#
This command applies the delta files in /data/backups/inc1 to the files in /data/backups/base, which rolls them forward to the time of the incremental backup. It then applies the redo log as usual to the result. The final data is in /data/backups/base, not in the incremental directory.
When merging incremental backups, the --apply-log-only option should be
used for all the intermediate incremental backups except the last
one. This is because the --apply-log-only option skips the rollback
phase and prepares the backup only for applying the next incremental
backup.
Therefore, for merging the last incremental backup, you should run the following command:
[root@mysql8backups]#xtrabackup--prepare --target-dir=/data/backups/base \--incremental-dir=/data/backups/inc22024-02-23T13:32:54.413264-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--innodb_checksum_algorithm=crc32 --innodb_log_checksums=1--innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648--innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2--server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0--innodb_undo_log_encrypt=02024-02-23T13:32:54.414978-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--prepare=1 --target-dir=/data/backups/base--incremental-dir=/data/backups/inc2xtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)2024-02-23T13:32:54.415995-00:000[Note][MY-011825][Xtrabackup]incrementalbackupfrom20784993isenabled.2024-02-23T13:32:54.416111-00:000[Note][MY-011825][Xtrabackup]cdto/data/backups/base/2024-02-23T13:32:54.416290-00:000[Note][MY-011825][Xtrabackup]Thistargetseemstobealreadypreparedwith--apply-log-only.2024-02-23T13:32:54.426669-00:000[Note][MY-011825][Xtrabackup]xtrabackup_logfiledetected:size=8388608,start_lsn=(21291941)2024-02-23T13:32:54.427121-00:000[Note][MY-011825][Xtrabackup]usingthefollowingInnoDBconfigurationforrecovery:2024-02-23T13:32:54.427198-00:000[Note][MY-011825][Xtrabackup]innodb_data_home_dir=.2024-02-23T13:32:54.427266-00:000[Note][MY-011825][Xtrabackup]innodb_data_file_path=ibdata1:12M:autoextend2024-02-23T13:32:54.427333-00:000[Note][MY-011825][Xtrabackup]innodb_log_group_home_dir=/data/backups/inc2/2024-02-23T13:32:57.169249-00:000[Note][MY-011825][Xtrabackup]Done:Copying/data/backups/inc2/binlog.000011to./binlog.0000112024-02-23T13:32:57.170686-00:000[Note][MY-011825][Xtrabackup]Copying/data/backups/inc2/binlog.indexto./binlog.index2024-02-23T13:32:57.170819-00:000[Note][MY-011825][Xtrabackup]Done:Copying/data/backups/inc2/binlog.indexto./binlog.index2024-02-23T13:32:57.173092-00:000[Note][MY-011825][Xtrabackup]completedOK![root@mysql8backups]#
Since this is the last incremental backup, you can run --prepare without
the
--apply-log-only option. The backup will still be consistent, but
the server will perform the rollback phase. After the preparation, the
backup is ready to be used.
Logical Backups
The most popular logical backup tools are the MySQL shell dump utility, mydumper, and mysqldump. In this section, we cover the MySQL Shell dump utility and for mysqldump, explaining how to perform a full backup and restore.
We cover mydumper and myloader in “Migrate Large Databases to Azure Database for MySQL”. Return to “Converting Tables from MyISAM to InnoDB” to find more on table-level migration.
The MySQL Shell Dump Utility
MySQL Shell is an advanced command-line client and scripting interface for MySQL. It provides a powerful set of utilities for database management and administration, including the dump utility, which is used for backing up and restoring databases.
The dump utility is a feature-rich tool provided by MySQL Shell for backing up MySQL databases. It enables you to perform full instance backups or partial backups of selected schemas or tables. You can also customize the backup process by using various options available in the utility.
After you have logged in, you can use the dump utility to perform backups, as shown in this example:
[root@mysql80~]#mysqlsh--uri root@localhost:3306MySQLShell8.0.36Copyright(c)2016,2023,Oracleand/oritsaffiliates.OracleisaregisteredtrademarkofOracleCorporationand/oritsServerversion:8.0.36MySQLCommunityServer-GPLNodefaultschemaselected;type\use<schema>tosetone.MySQLlocalhost:3306sslJS>
Before running the actual backup, perform a dry-run
procedure to validate the backup process. The following command, util.dumpInstance, can be used for the dry run:
MySQLlocalhost:3306sslJS>util.dumpInstance("/mysqlsh/backuputildump/",{dryRun:"true"})dryRunenabled,nolockswillbeacquiredandnofileswillbecreated.AcquiringglobalreadlockGlobalreadlockacquiredInitializing-done1outof5schemaswillbedumpedandwithinthem1table,0views.1outof4userswillbedumped.Gatheringinformation-doneAlltransactionshavebeenstartedLockinginstanceforbackupGlobalreadlockhasbeenreleasedWritingglobalDDLfilesWritingusersDDLWritingDDL-doneStartingdatadump0%(0rows/~20rows),0.00rows/s,0.00B/suncompressed,0.00B/scompressedMySQLlocalhost:3306sslJS>
A full instance backup ensures that you have a backup of the entire database instance in case of any unforeseen circumstances. You can use the following command to perform a full instance backup:
MySQLlocalhost:3306sslJS>util.dumpInstance("/backuputildump")AcquiringglobalreadlockGlobalreadlockacquiredInitializing-done1outof5schemaswillbedumpedandwithinthem1table,0views.1outof4userswillbedumped.Gatheringinformation-doneAlltransactionshavebeenstartedLockinginstanceforbackupGlobalreadlockhasbeenreleasedWritingglobalDDLfilesWritingusersDDLRunningdatadumpusing4threads.NOTE:Progressinformationusesestimatedvaluesandmaynotbeaccurate.Writingschemametadata-doneWritingDDL-doneWritingtablemetadata-doneStartingdatadump100%(20rows/~20rows),0.00rows/s,0.00B/suncompressed,0.00B/scompressedDumpduration:00:00:00sTotalduration:00:00:00sSchemasdumped:1Tablesdumped:1Uncompresseddatasize:727bytesCompresseddatasize:258bytesCompressionratio:2.8Rowswritten:20Byteswritten:258bytesAverageuncompressedthroughput:727.00B/sAveragecompressedthroughput:258.00B/sMySQLlocalhost:3306sslJS>
The backup is stored in the following directory:
[root@mysql80~]#ls-ltr/backuputildumptotal44-rw-r-----. 1 root root 240 Feb 20 10:37 @.sql-rw-r-----. 1 root root 240 Feb 20 10:37 @.post.sql-rw-r-----. 1 root root 774 Feb 20 10:37 @.json-rw-r-----. 1 root root 1965 Feb 20 10:37 @.users.sql-rw-r-----. 1 root root 301 Feb 20 10:37 mytestdb.json-rw-r-----. 1 root root 575 Feb 20 10:37 mytestdb.sql-rw-r-----. 1 root root 784 Feb 20 10:37 mytestdb@test_table.sql-rw-r-----. 1 root root 649 Feb 20 10:37 mytestdb@test_table.json-rw-r-----. 1 root root 8 Feb 20 10:37 mytestdb@test_table@@0.tsv.zst.idx-rw-r-----. 1 root root 258 Feb 20 10:37 mytestdb@test_table@@0.tsv.zst-rw-r-----. 1 root root 228 Feb 20 10:37 @.done.json[root@mysql80~]#
To run the instance dump with more advanced options, use the following command:
MySQLlocalhost:3306sslJS>util.dumpInstance(\"/backuputildump/ mysqlinstance",{threads:8,maxRate:\"100M",consistent:true,chunking:true,bytesPerchunk:\"64M",compression:\"zstd"})AcquiringglobalreadlockGlobalreadlockacquiredInitializing-done1outof5schemaswillbedumpedandwithinthem1table,0views.1outof4userswillbedumped.Gatheringinformation-doneAlltransactionshavebeenstartedLockinginstanceforbackupGlobalreadlockhasbeenreleasedWritingglobalDDLfilesWritingusersDDLRunningdatadumpusing8threads.NOTE:Progressinformationusesestimatedvaluesandmaynotbeaccurate.Writingschemametadata-doneWritingDDL-doneWritingtablemetadata-doneStartingdatadump100%(20rows/~20rows),0.00rows/s,0.00B/suncompressed,0.00B/scompressedDumpduration:00:00:00sTotalduration:00:00:00sSchemasdumped:1Tablesdumped:1Uncompresseddatasize:727bytesCompresseddatasize:258bytesCompressionratio:2.8Rowswritten:20Byteswritten:258bytesAverageuncompressedthroughput:727.00B/sAveragecompressedthroughput:258.00B/sMySQLlocalhost:3306sslJS>
In this command, you can specify various options to control and enhance the dump process. For example, you can specify the number of threads to be used, the maximum rate at which the backup should be written, whether to perform a consistent backup, whether to perform chunking of the backup data, the size of each chunk, and the compression algorithm to be used.
Use the schema dump utility to perform partial backups of chosen schemas. You can use the following command, util.dumpSchemas, to back up specified schemas:
MySQLlocalhost:3306sslJS>util.dumpSchemas([\"employees"],\"/backupdir/employees",{threads:2})AcquiringglobalreadlockGlobalreadlockacquiredInitializing-done1schemaswillbedumpedandwithinthem2tables,0views.Gatheringinformation-doneAlltransactionshavebeenstartedLockinginstanceforbackupGlobalreadlockhasbeenreleasedWritingglobalDDLfilesRunningdatadumpusing2threads.NOTE:Progressinformationusesestimatedvaluesandmaynotbeaccurate.Writingschemametadata-doneWritingDDL-doneWritingtablemetadata-doneStartingdatadump100%(8rows/~8rows),0.00rows/s,0.00B/suncompressed,0.00B/scompressedDumpduration:00:00:00sTotalduration:00:00:00sSchemasdumped:1Tablesdumped:2Uncompresseddatasize:224bytesCompresseddatasize:182bytesCompressionratio:1.2Rowswritten:8Byteswritten:182bytesAverageuncompressedthroughput:224.00B/sAveragecompressedthroughput:182.00B/sMySQLlocalhost:3306sslJS>
In this command, you can specify the backup location and the names of
the schemas to be backed up. The backup is stored in the following directory, which is only for the employees database:
[root@mysql80~]#ls-ltr/backupdir/employeestotal64-rw-r-----. 1 root root 240 Feb 20 10:51 @.sql-rw-r-----. 1 root root 240 Feb 20 10:51 @.post.sql-rw-r-----. 1 root root 771 Feb 20 10:51 @.json-rw-r-----. 1 root root 362 Feb 20 10:51 employees.json-rw-r-----. 1 root root 716 Feb 20 10:51 employees@salaries.sql-rw-r-----. 1 root root 631 Feb 20 10:51 employees@salaries.json-rw-r-----. 1 root root 798 Feb 20 10:51 employees@employees.sql-rw-r-----. 1 root root 657 Feb 20 10:51 employees@employees.json-rw-r-----. 1 root root 581 Feb 20 10:51 employees.sql-rw-r-----. 1 root root 8 Feb 20 10:51 employees@salaries@0.tsv.zst.idx-rw-r-----. 1 root root 8 Feb 20 10:51 employees@employees@@0.tsv.zst.idx-rw-r-----. 1 root root 52 Feb 20 10:51 employees@salaries@0.tsv.zst-rw-r-----. 1 root root 8 Feb 20 10:51 employees@salaries@@1.tsv.zst.idx-rw-r-----. 1 root root 121 Feb 20 10:51 employees@employees@@0.tsv.zst-rw-r-----. 1 root root 9 Feb 20 10:51 employees@salaries@@1.tsv.zst-rw-r-----. 1 root root 344 Feb 20 10:51 @.done.json[root@mysql80~]#
You can use the table dump utility to perform partial backups of chosen tables. Use the following command, util.dumpTables, to back up individual tables:
MySQLlocalhost:3306sslJS>util.dumpTables(\"employees",[\"salaries"],\"/backupdir/employees/salaries",{threads:2})AcquiringglobalreadlockGlobalreadlockacquiredInitializing-done1tablesand0viewswillbedumped.Gatheringinformation-doneAlltransactionshavebeenstartedLockinginstanceforbackupGlobalreadlockhasbeenreleasedWritingglobalDDLfilesRunningdatadumpusing2threads.NOTE:Progressinformationusesestimatedvaluesandmaynotbeaccurate.Writingschemametadata-doneWritingDDL-doneWritingtablemetadata-doneStartingdatadump100%(4rows/~4rows),0.00rows/s,0.00B/suncompressed,0.00B/scompressedDumpduration:00:00:00sTotalduration:00:00:00sSchemasdumped:1Tablesdumped:1Uncompresseddatasize:88bytesCompresseddatasize:61bytesCompressionratio:1.4Rowswritten:4Byteswritten:61bytesAverageuncompressedthroughput:88.00B/sAveragecompressedthroughput:61.00B/sMySQLlocalhost:3306sslJS>
The backup is stored in the following directory, which is only for the employees.salaries table:
[root@mysql80~]#ls-ltr/backupdir/employees/salariestotal48-rw-r-----. 1 root root 240 Feb 20 11:05 @.sql-rw-r-----. 1 root root 240 Feb 20 11:05 @.post.sql-rw-r-----. 1 root root 770 Feb 20 11:05 @.json-rw-r-----. 1 root root 236 Feb 20 11:05 employees.json-rw-r-----. 1 root root 478 Feb 20 11:05 employees.sql-rw-r-----. 1 root root 631 Feb 20 11:05 employees@salaries.json-rw-r-----. 1 root root 716 Feb 20 11:05 employees@salaries.sql-rw-r-----. 1 root root 8 Feb 20 11:05 employees@salaries@0.tsv.zst.idx-rw-r-----. 1 root root 8 Feb 20 11:05 employees@salaries@@1.tsv.zst.idx-rw-r-----. 1 root root 52 Feb 20 11:05 employees@salaries@0.tsv.zst-rw-r-----. 1 root root 9 Feb 20 11:05 employees@salaries@@1.tsv.zst-rw-r-----. 1 root root 266 Feb 20 11:05 @.done.json[root@mysql80~]#
In this command, you can specify the backup location and the names of the tables to be backed up, along with their respective schema names.
After backing up the database, you’ll want to restore the data to another or the same database. MySQL Shell includes a data load utility for this purpose. This utility helps you import data from a backup file into a database.
Before restoring the data, we recommend performing a dry-run
process to validate the backup file. Use this simple loadDump command to perform a dry run:
MySQLlocalhost:3306sslJS>util.loadDump(\"/backuputildump/",{dryRun:true})LoadingDDLandDatafrom'/backuputildump/'using4threads.Openingdump...dryRunenabled,nochangeswillbemade.TargetisMySQL8.0.36.DumpwasproducedfromMySQL8.0.36Scanningmetadata-doneCheckingforpre-existingobjects...ExecutingcommonpreambleSQLExecutingDDL-doneExecutingviewDDL-doneStartingdataloadExecutingcommonpostambleSQL0%(0bytes/727bytes),0.00B/s,1/1tablesdoneRecreatingindexes-doneNodataloaded.0warningswerereportedduringtheload.MySQLlocalhost:3306sslJS>
Similarly, you can test the command (depending on the requirement) before loading the data to ensure that it works properly. The following example shows how to use this utility for backup restoration:
MySQLlocalhost:3306sslJS>util.loadDump(\"/backuputildump/",{progressFile:\"/backuputildump/backuplog.json",threads:4,backgroundThreads:4,maxBytesPerTransaction:"4096"})LoadingDDLandDatafrom'/backuputildump/'using4threads.Openingdump...TargetisMySQL8.0.36.DumpwasproducedfromMySQL8.0.36Scanningmetadata-doneCheckingforpre-existingobjects...ExecutingcommonpreambleSQLExecutingDDL-doneExecutingviewDDL-doneStartingdataloadExecutingcommonpostambleSQL100%(727bytes/727bytes),0.00B/s,1/1tablesdoneRecreatingindexes-done1chunks(20rows,727bytes)for1tablesin1schemaswereloadedin0sec(avgthroughput727.00B/s)0warningswerereportedduringtheload.MySQLlocalhost:3306sslJS>
The preceding command will restore the data from the /backuputildump/ directory backup and use four threads and four background threads during the restore process. It will also create a progress file at /backuputildump/backuplog.json and set the maximum bytes per transaction to 4,096.
mysqldump
MySQL provides the mysqldump command-line utility for creating logical database backups. In this section, we will explore how to use mysqldump effectively. But before we dive into the backup process, it’s important to understand
the mysqldump command and its options. The easiest way to access the
mysqldump documentation is through the command-line help:
[root@mysql80~]#manmysqldump
This command will bring up the mysqldump manual, which provides detailed information on the command’s syntax, options, and usage.
Backing up individual tables
Backing up individual tables is useful when you need to restore only
specific data sets. To use the mysqldump command to create backups
of individual tables in a database, use the following syntax:
mysqldumpdatabase_nametable_name>backup_file.sql
For example, to create a backup of the city table in the world
database, use the following command:
[root@mysql80backup]#mysqldump-uroot-pworldcity>city.sqlEnterpassword:[root@mysql80backup]#ls-ltrtotal176-rw-r--r--. 1 root root 179263 Feb 20 12:59city.sql[root@mysql80backup]#
Use the --where option to back up only a subset of data from a table. For example, to back up only the rows in the city table where CountryCode is 5, execute the following command:
[root@mysql80backup]#mysqldumpworld-uroot-pcity--where=\"CountryCode='USA'" > city_USA.sqlEnterpassword:[root@mysql80backup]#ls-ltr-rw-r--r--. 1 root root 14060 Feb 20 13:01city_USA.sql[root@mysql80backup]#
Use the --ignore-table option to exclude specific tables from your backup. For example, you can use the following command to back up all tables in the world database except the city table:
[root@mysql80backup]#mysqldump-uroot-pworld--ignore-table=world.city > ignore_city_world.sqlEnterpassword:[root@mysql80backup]#ls-ltr-rw-r--r--. 1 root root 66071 Feb 20 13:08ignore_city_world.sql[root@mysql80backup]#
To create a whole database backup, use the mysqldump command with the database name as an argument:
mysqldumpdatabase_name>backup_file.sql
For example, to back up the world database, use the following
command:
[root@mysql80backup]#mysqldump-uroot-pworld>world.sqlEnterpassword:[root@mysql80backup]#ls-ltr-rw-r--r--. 1 root root 244085 Feb 20 13:05world.sql[root@mysql80backup]#
Backing up multiple databases
To back up several databases, specify them as a comma-separated list:
mysqldump--databasesdatabase1,database2>backup_file.sql
To back up the mytestdb sakila world_x and example_db1 databases, for example, execute the following command:
[root@mysql80backup]#mysqldump-uroot-p--databases mytestdb employeessakila>all_db_backup.sqlEnterpassword:[root@mysql80backup]#ls-ltr-rw-r--r--. 1 root root 3390832 Feb 20 13:27all_db_backup.sql[root@mysql80backup]#
Backing up all databases
To back up all databases on the MySQL server, use the --all-databases option:
[root@mysql80backup]#mysqldump-uroot-p--all-databases>all_databases_backup.sqlEnterpassword:[root@mysql80backup]#ls-ltr-rw-r--r--. 1 root root 4937167 Feb 20 13:29all_databases_backup.sql[root@mysql80backup]#
This will create a single backup file containing all databases on the server.
The mysqldump command-line tool creates a logical backup of a database by generating a set of SQL statements that can be used to re-create the database objects and data. The following example creates a backup of the sakila database with several options for a more efficient and consistent backup:
[root@mysql80backup]#mysqldump-uroot-p--single-transaction --quick--lock-tables=false --routines --events --triggers--default-character-set=utf8mb4 sakila >backup.sqlEnterpassword:[root@mysql80backup]#ls-ltr-rw-r--r--. 1 root root 3397023 Feb 20 13:31backup.sql[root@mysql80backup]#
Let’s break down each option in the command. This command tells mysqldump to create a backup of the specified database (dbname) and save it to a file called backup.sql:
--single-transaction-
Ensures that the backup is consistent, even if other transactions are occurring on the database at the same time.
--quick-
Tells
mysqldumpto retrieve rows one at a time instead of retrieving the entire result set at once, which can help to reduce memory usage. --lock-tables=false-
Ensures that the tables are not locked during the backup process, which allows the database to continue functioning normally.
--routines,--events, and--triggers-
Tells
mysqldumpto include stored routines, events, and triggers in the backup, respectively. --default-character-set=utf8mb4-
Allows you to enforce the use of the utf8mb4 character set in the generated SQL script. This can be useful when you need to ensure that the backup will support all the characters used in your database, especially if it includes emojis or other special characters that are outside the scope of the standard UTF-8 character set.
Once you’ve created a logical backup using mysqldump, you can use it
to re-create the database on another server or to restore the database if it becomes corrupted. To restore a logical backup, you would simply use the MySQL client to run the SQL statements contained in the backup file.
To back up and restore a replica node, you can utilize either the --source-data or
--master-data option. Use --source-data for MySQL 8.0.26 and newer, or use
--master-data for prior versions. Both options have the same purpose, which is to generate a dump file that can be used to set up another server as a replica of the source server during replication.
When these options are used, the resulting dump output includes a CHANGE REPLICATION SOURCE TO statement (introduced in MySQL 8.0.23) or CHANGE MASTER TO statement (used before MySQL 8.0.23). This statement specifies the binary log coordinates (filename and position) of the dumped server, indicating where the replication source server should start replicating from after loading the dump file into the
replica.
If the --source-data or --master-data options are set to 2, the CHANGE REPLICATION SOURCE TO or CHANGE MASTER TO statement in the dump file is commented out, preventing it from taking effect when the dump file is reloaded. The option value 1 will write the statement uncommented and take effect when the dump file is reloaded. If no option value is explicitly supplied, the default value of 1 is used.
Setting up a backup schedule
Create a shell script that will take the MySQL backup using Percona XtraBackup. You can create a new file by using any text editor (for example, nano or Vim), and save it with the .sh extension. In this script, you need to include the following commands:
[root@mysql80backup]#catbackup_script.sh#!/bin/bash#SettheMySQLusernameandpasswordMYSQL_USER="root"MYSQL_PASSWORD='D@#NJU#$@MK28#nM'#SetthebackupdirectoryBACKUP_DIR="/root/backupdir/newbackup"#TakeafullbackupusingPerconaXtraBackupxtrabackup--backup --user=$MYSQL_USER --password=$MYSQL_PASSWORD--target-dir=$BACKUP_DIR/full_backup_$(date +%Y-%m-%d_%H-%M-%S)[root@mysql80backup]#
In this script, you need to replace the values for MYSQL_USER, MYSQL_PASSWORD, and BACKUP_DIR with the appropriate values for your system. Also, make sure to give execute permissions to this script by using the command:
[root@mysql80backup]#chmod+xbackup_script.sh[root@mysql80backup]#ls-ltrtotal4-rwxr-xr-x.1rootroot344Feb2013:35backup_script.sh[root@mysql80backup]#
The command ./backup_script.sh executes a shell script named backup_script.sh that is located in the current directory:
[root@mysql80backup]#./backup_script.sh2024-02-20T13:42:56.095800-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--datadir=/var/lib/mysql2024-02-20T13:42:56.096132-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--user=root --password=* --socket=/var/lib/mysql/mysql.sock--backup=1 --user=root --password=*--target-dir=/root/backupdir/newbackup/full_backup_2024-02-20_13-42-55xtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)24022013:42:56version_checkConnectingtoMySQLserverwithDSN'dbi:mysql:; mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock'as'root'(usingpassword:YES).24022013:42:56version_checkConnectedtoMySQLserver24022013:42:56version_checkExecutingaversioncheckagainsttheserver...24022013:42:56version_checkDone.2024-02-20T13:42:56.369830-00:000[Note][MY-011825][Xtrabackup]ConnectingtoMySQLserverhost:localhost,user:root,password:set,port:notset,socket:/var/lib/mysql/mysql.sock2024-02-20T13:42:59.708031-00:000[Note][MY-011825][Xtrabackup]Done:Writingfile/root/backupdir/newbackup/full_backup_2024-02-20_13-42-55/xtrabackup_info2024-02-20T13:43:00.711702-00:000[Note][MY-011825][Xtrabackup]Transactionlogoflsn(32549485)to(32549485)wascopied.2024-02-20T13:43:00.831082-00:000[Note][MY-011825][Xtrabackup]completedOK![root@mysql80backup]#
Setting up a backup schedule is crucial to ensure that your database is backed up regularly. To set up a backup schedule, use cron jobs or other scheduling tools. Here is an example of a cron job that runs a backup every day at 2 A.M.:
crontab-e02***/root/backupdir/newbackup/backup_script.sh
To set up a backup schedule, you can use a combination of crontab and your chosen backup method. Here’s an example configuration for setting up a daily backup schedule using mysqldump:
-
Create a backup directory to store the backup files:
[root@mysql80~]#sudomkdir-p/mnt/backups/mysql/[root@mysql80~]# -
Grant the necessary permissions to the backup directory:
[root@mysql80~]#sudochown-Rmysql:mysql/mnt/backups/mysql/[root@mysql80~]# -
Use
sudo chmod -R 700 /mnt/backups/mysql/to modify the permissions of the directory /mnt/backups/mysql/ and all its subdirectories and files:[root@mysql80~]#sudochmod-R700/mnt/backups/mysql/[root@mysql80~]# -
Create a shell script:
[root@mysql80mysql]#cat/mnt/backups/mysql/backup_mysqldump.sh#!/bin/bash#Setthebackupdirectorybackup_dir="/mnt/backups/mysql"#SettheMySQLcredentialsmysql_user="root"mysql_password='D@#NJU#$@MK28#nM'#Createthebackupfilenamewithdatestampbackup_filename="$backup_dir/daily_backup_$(date +%Y%m%d).sql"#Executethemysqldumpcommandandsaveoutputtofilemysqldump--all-databases --user="$mysql_user" --password="$mysql_password">"$backup_filename"[root@mysql80mysql]# -
Test the script by executing it:
[root@mysql80mysql]#/mnt/backups/mysql/backup_mysqldump.sh/mnt/backups/mysql/daily_backup_20240220.sql[root@mysql80mysql]#ls-ltrtotal4828-rwxr-xr-x.1rootroot472Feb2014:18backup_mysqldump.sh-rw-r--r--. 1 root root 4937167 Feb 20 14:18 daily_backup_20240220.sql[root@mysql80mysql]# -
Open the crontab configuration:
crontab-e -
Add the following line to the crontab file to schedule a daily backup at 2 A.M.:
02***/mnt/backups/mysql/backup_mysqldump.sh -
Save and close the crontab file.
By following this example configuration, you can use mysqldump to set up a daily backup
schedule for a MySQL database and ensure that the
backup files are stored securely and can be restored when needed. The provided backup scripts are basic examples. Depending on your requirements, you may need to write scripts using scripting languages such as Python or a shell script.
Selecting backup options
When taking a backup, select various backup options to customize the backup process. Here are some essential backup options:
- Backup type
-
Select either a full backup or an incremental backup. Full backups create a complete backup of the database, while incremental backups back up only changes made since the last backup.
- Compression
-
Compress the backup file to save disk space. XtraBackup,
mydumper, andmysqldumpsupport compression. - Encryption
-
Encrypt the backup file to protect sensitive data. XtraBackup supports encryption via the
--encryptoption.
Monitoring the backup process
It is essential to monitor the backup process to ensure that it completes successfully. Here are some ways to monitor this process:
-
Check the backup logfile for any errors or warnings.
-
Check the backup file size to ensure that it is not too small or too large.
-
To ensure the backup file’s validity, periodically perform restores by using the production backup on the testing system.
Setting up the restore environment
After selecting a restore method, set up the environment for the restore process. Ensure that the server configuration is compatible with the backup files, including the version of MySQL and the location of the data directory. If necessary, create a new MySQL instance with the same configuration as the original instance.
Assuming that the backup was taken from a MySQL 8.0.23 instance and you want to restore it to a new MySQL 8.0.25 instance, you can set up the environment for the restore process by using the following steps:
-
Install MySQL 8.0.25 on the server where you want to restore the database.
-
Stop the MySQL service by using the command
systemctl stop mysqld. -
Copy the configuration file from the original instance to the new instance by using a command like
scp. -
Edit the configuration file /etc/my.cnf to match the configuration of the original instance, including the location of the data directory.
-
Depending on the backup, the restore process will vary.
-
Start the MySQL service by using the command
systemctl start mysqld.
When choosing a backup method, consider the size of your database, the
amount of data changes, and the RTO and
RPO of your organization. For example, XtraBackup is an efficient backup method for large databases that require quick recovery time, while mydumper takes more time compared to XtraBackup, and the mysqldump is suitable for smaller databases that can afford longer recovery times.
Point-in-Time Recovery
Point-in-time recovery (PITR) is a technique used to restore a database to a specific point in time, typically to recover from accidental deletion, data corruption, or other types of data loss. It involves restoring a database to a previous state by replaying transaction logs that were captured at specific points in time.
PITR is necessary when you need to recover your database to a specific point in time. For example, if you accidentally deleted important data from the database and realized it only a few hours later, you could use PITR to recover the database to a point in time just before the data was deleted. Similarly, if the database becomes corrupted, you can use PITR to restore it to a point in time before the corruption occurred.
PITR is necessary in the following situations:
- Disaster recovery
-
If you experience data loss due to a disaster such as hardware failure, power outages, or a natural calamity, PITR helps you restore the data to a specific point in time before the disaster occurred.
- Human error
-
If you accidentally delete or modify data, PITR allows you to restore the data to the point in time before the error occurred.
- Compliance
-
PITR is often required to meet compliance regulations such as the Health Insurance Portability and Accountability Act (HIPAA), Sarbanes-Oxley Act (SOX), and General Data Protection Regulation (GDPR). These regulations may require that you keep backups of data and that you are able to restore the data to a specific point in time.
Instance-Level Recovery
This recovery method allows you to recover the entire MySQL instance, including databases, tables, schemas, and configurations, to a consistent state. Here’s a straightforward example of how to execute PITR. However, the steps may differ based on the backup tool used (such as XtraBackup, MySQL Shell dump utility, or mydumper), and the backup and scenario of the production environment, so it’s essential to take appropriate actions accordingly.
Let’s say you have a database for an online store and accidentally deleted a customer’s order. You want to recover that order but don’t want to lose any data that has been added since the order was deleted.
Here’s how you could use PITR to recover the order up to the moment before it was deleted:
-
To ensure regular backups of the database, it is important to schedule them at specific intervals. One way to perform a full backup of the database is by utilizing the
xtrabackupcommand, which was already detailed in “Percona XtraBackup”. -
Create a binary log backup of the database by using the
mysqlbinlogcommand. To ensure regular backups, it is important to create a script and schedule them at specific intervals.When backing up a server’s binary logfiles with
mysqlbinlog, you need to provide the actual filenames that exist on the server. You can use theSHOW BINARY LOGSstatement to see the current names if you are not sure what they are:mysql>SHOWBINARYLOGS;+---------------+-----------+-----------+|Log_name|File_size|Encrypted|+---------------+-----------+-----------+|binlog.000001|35421|No||binlog.000002|12416|No||binlog.000003|52273|No|+---------------+-----------+-----------+To create a static backup of binlog.000001 through binlog.000003, you can use either of these commands:
mysqlbinlog--read-from-remote-server --host=host_name --rawbinlog.000001binlog.000002binlog.000003mysqlbinlog--read-from-remote-server --host=host_name --raw--to-last-log binlog.000001The first command explicitly lists every filename, while the second names only the first file and uses
--to-last-logto read through the last. One difference between these commands is that if the server opens binlog.000133 beforemysqlbinlogreaches the end of binlog.000003, the first command does not read it, but the second command does.To create a live backup, where
mysqlbinlogstarts with binlog.000001 to copy existing logfiles and then stays connected to copy new events as the server generates them, you can use the following:mysqlbinlog--read-from-remote-server --host=host_name --raw--stop-never binlog.000001With
--stop-never, it is not necessary to specify all the binary logfiles that already exist. -
Restore the full backup. (See “Percona XtraBackup” for the steps required to restore the full backup made by XtraBackup.)
-
Apply incremental backups to restore the database up to the desired point in time:
mysqlbinlog--start-datetime='2023-03-01 12:00:00'--stop-datetime='2023-03-01 13:00:00' /var/log/mysql/binlog.000001 |mysql-uroot-pmydatabase
Note
This example demonstrates the use of XtraBackup. Depending on the backup
system employed in production, you have the option to utilize either
MySQL Shell dump utility, mydumper, XtraBackup, or MySQL Enterprise Backup.
In this example, we are restoring the database up to a specific hour on March 1, 2023, which is the hour before the order was deleted.
By following these steps, you can recover the deleted order without losing any data that has been added since the deletion. PITR gives you greater control over your data recovery process and can be a valuable tool in disaster recovery scenarios.
Table-Level Recovery
To restore the table alone to a new instance, ensure that you have a valid backup of the table’s tablespace files. Once you’ve done that, you’re ready to follow these steps to create a table-level recovery:
-
Connect to the MySQL instance where you want to restore the table and create the table structure:
mysql>useadddb;Databasechangedmysql>CREATETABLE`authors`(->`id`intNOTNULLAUTO_INCREMENT,->`first_name`varchar(50)COLLATEutf8mb3_unicode_ciNOTNULL,->`last_name`varchar(50)COLLATEutf8mb3_unicode_ciNOTNULL,->`email`varchar(100)COLLATEutf8mb3_unicode_ciNOTNULL,->`birthdate`dateNOTNULL,->`added`timestampNOTNULLDEFAULTCURRENT_TIMESTAMP,->PRIMARYKEY(`id`)->)ENGINE=InnoDBAUTO_INCREMENT=72639469DEFAULTCHARSET=utf8mb4COLLATE=utf8mb4_0900_ai_ci;QueryOK,0rowsaffected,3warnings(0.03sec) -
Discard the tablespace of the
authorstable:mysql>selectcount(*)fromauthors;+----------+|count(*)|+----------+|0|+----------+1rowinset(0.01sec)mysql>ALTERTABLEauthorsDISCARDTABLESPACE;QueryOK,0rowsaffected(0.01sec)mysql>When a tablespace is discarded, the association between the table and its underlying tablespace is removed. The table structure and metadata remain intact, but the tablespace files are no longer associated with the table. This operation is typically performed when you want to replace the tablespace files with a different set of files or when you want to restore the table from a backup.
-
Copy the tablespace from the backup:
root@UbuntuMysql8:~#scp/var/lib/mysql/adddb/authors.ibd172.31.84.219:/var/lib/mysql/adddb/authors.ibd100%544KB49.3MB/s00:00root@MyUbuntuMysql8:~# -
Change the ownership of the file authors.ibd located in the /var/lib/mysql/adddb directory. The following command assigns the user and group
mysqlas the new owners of the file:[root@mysql80~]#chownmysql.mysql/var/lib/mysql/adddb/authors.ibd[root@mysql80~]# -
Import the tablespace. The statement
ALTER TABLE authors IMPORT TABLESPACE;is used in MySQL to import a tablespace for theauthorstable within theadddbdatabase. This command allows you to restore or replace the tablespace of theauthorstable by using a previously exported or backup tablespace file (typically with an .ibd extension). By executing this statement, you instruct MySQL to associate the imported tablespace file with theauthorstable in the specified database.Importing the tablespace enables you to recover or migrate a table along with its data and index structures. It establishes the link between the table definition and the underlying tablespace file, allowing the table to be fully accessible and operational within the MySQL database instance:
[root@mysql80mysql]#mysqlWelcometotheMySQLmonitor.Commandsendwith;or\g.mysql>mysql>ALTERTABLEadddb.authorsIMPORTTABLESPACE;QueryOK,0rowsaffected,1warning(0.05sec)mysql> -
Following the import of the tablespace for the
authorstable, verify the data in the table:selectcount(id)fromadddb.authors;mysql>selectcount(id)fromadddb.authors;+-----------+|count(id)|+-----------+|6000|+-----------+1rowinset(0.03sec)
After importing the table as part of the PITR process, you’ll need to use the binlog backups to restore the remaining data from the time of the initial backup to the current time. To accomplish this, follow the steps outlined here:
-
Copy the required binlog and create the index:
sudocporscpmysql-bin.*/data/sudochownmysql.mysql/data/mysql-bin.* -
Update the my.cnf file:
relay-log=mysql-bin -
Prepare the index by using the copied binlogs:
ls./mysql-bin.0*>mysql-bin.indexWith
IMPORT TABLESPACE, you have already imported the table from the existing backup. -
Configure this table as a dummy replica:
mysql>CHANGEREPLICATIONSOURCETORELAY_LOG_FILE='mysql-bin.000001',RELAY_LOG_POS=4,SOURCE_HOST='dummy';QueryOK,0rowsaffected(0.08sec)mysql>CHANGEREPLICATIONFILTERREPLICATE_DO_TABLE=(adddb.authors);QueryOK,0rowsaffected(0.00sec) -
Validate the output and start the replication thread (only the SQL thread is needed):
mysql>STARTREPLICASQL_THREAD;QueryOK,0rowsaffected(0.01sec)
PITR is a crucial process that requires expertise from a DBA. In MySQL, it empowers you to restore your database to a precise point in time during critical situations, including accidental data deletion, data corruption, human errors, and disaster recovery. The skillful implementation of PITR ensures data integrity and minimizes potential data loss in these critical scenarios.
By enabling binary logging, taking a full backup, restoring the backup to a new location, determining the point in time to recover to, and recovering the database to that time, you can ensure data integrity and protect against data loss.
Management of Binary Logs
MySQL provides a robust and reliable logging mechanism for tracking changes made to the database, known as binary logging. In this section, we discuss the significance of binary logs and provide a comprehensive guide on managing them.
Binary logs are a type of logfile generated by MySQL to record all changes made to the database. These changes include any data modifications, schema alterations, and administrative operations such as user management. Binary logs are stored in a binary format, which makes them platform independent and allows for easy replication of changes across multiple servers.
Binary logs are a critical component of MySQL’s HA and disaster-recovery mechanisms. They are used to restore the database in the event of a server crash, data corruption, or user error. Binary logs are also required for replication, to synchronize data changes between source and replica servers.
Enabling Binary Logging
In previous versions of MySQL, binary logging had to be manually enabled by specifying the --log-bin option since it was disabled by default. However, as of MySQL 8, binary logging is enabled by default
regardless of whether the --log-bin option is specified.
The only exception occurs when initializing the data directory manually by using mysqld and the --initialize or --initialize-insecure option, which disables binary logging by default. To enable binary logging in this case, the --log-bin option must be specified. Enabling binary logging sets the log_bin system variable to ON, indicating the status of binary logging on the server.
To enable binary logging, add the following line to the MySQL configuration file (my.cnf or my.ini):
[mysqld]log-bin=mysql-bin
This configuration will create a binary logfile named mysql-bin in the data directory specified in the MySQL configuration file.
Configuring Binary Logging
MySQL provides a range of configuration options for binary logging.
These options can be set in the MySQL configuration file or dynamically by using the SET GLOBAL command. Some of the key configuration options for binary logging include the
following:
binlog_format-
Specifies the format used for binary logging. MySQL supports three formats:
ROW,STATEMENT, andMIXED. The default format isROW. expire_logs_days-
Sets the number of days after which binary logs will be automatically purged. The default value is
0, which means that binary logs will not be automatically deleted. max_binlog_size-
Specifies the maximum size of a binary logfile before a new file is created. The default value is 1,073,741,824 bytes (1 GB).
binlog_row_image-
Specifies the format used for binary logging of row-based events. MySQL supports three formats:
FULL,MINIMALandNOBLOB. The default format isFULL.
For example, to set the binlog_format option to ROW, add the following
line to the MySQL configuration file:
[mysqld]binlog_format=ROW
Purging Binary Logs
Over time, binary logs can consume a significant amount of disk space. To prevent this, MySQL provides the option to purge old binary logs. You can purge binary logs in two ways: manually or automatically.
To manually purge binary logs, use the PURGE BINARY LOGS command, which will purge all binary logs up to and including the file
mysql-bin.000003:
PURGEBINARYLOGSTO'mysql-bin.000003';
To automatically purge binary logs, set the expire_logs_days option in
the MySQL configuration file.
Here, 604,800 seconds are equivalent to seven days (60 seconds/minute × 60 minutes/hour × 24 hours/day × 7 days). This configuration will automatically purge binary logs that are older than seven days:
[mysqld]binlog_expire_logs_seconds=604800;
Here’s an example of how to use the PURGE BINARY LOGS command to delete
old binary logfiles in MySQL:
-
Use the MySQL client to connect to the MySQL server:
mysql-uroot-p -
List the binary logfiles currently in use:
mysql>SHOWBINARYLOGS;+---------------+-----------+-----------+|Log_name|File_size|Encrypted|+---------------+-----------+-----------+|binlog.000003|157|No||binlog.000004|201|No||binlog.000005|157|No||binlog.000006|201|No||binlog.000007|1357|No||binlog.000008|201|No||binlog.000009|157|No||binlog.000010|905|No||binlog.000011|1743|No||binlog.000012|2097859|No||binlog.000013|201|No||binlog.000014|1402|No|+---------------+-----------+-----------+12rowsinset(0.01sec)mysql>Along with the binary logfiles currently in use, this command will display their sizes and creation timestamps.
-
Use the
PURGE BINARY LOGScommand to delete old binary logfiles. For example, to delete all binary logfiles older than seven days, use the following command:mysql>PURGEBINARYLOGSBEFOREDATE_SUB(NOW(),INTERVAL7DAY);QueryOK,0rowsaffected(0.02sec)You can adjust the interval as needed to delete files older or newer than a certain number of days.
-
Verify that the old binary logfiles have been deleted by running the
SHOW BINARY LOGScommand again:mysql>SHOWBINARYLOGS;+---------------+-----------+-----------+|Log_name|File_size|Encrypted|+---------------+-----------+-----------+|binlog.000013|201|No||binlog.000014|1402|No|+---------------+-----------+-----------+2rowsinset(0.00sec)Now you’ll see the binary logfiles currently in use, excluding any files that were deleted by the
PURGE BINARY LOGScommand.
By using the PURGE BINARY LOGS command, you can keep your binary logfiles under control and prevent them from consuming too much disk space
over time. Binary logs are a critical component of MySQL’s HA and
disaster-recovery mechanisms. They provide a reliable way to track
changes made to the database and allow for easy replication of changes
across multiple servers. By following the guidelines outlined in this
section, you can ensure that your binary logs are properly configured
and
managed.
Best Practices for Backup and Recovery
In the world of databases, backup and recovery are essential to ensure the integrity of data. A single database failure can lead to loss of important data, which can impair business operations. Therefore, it is important to implement best practices for backup and recovery in MySQL.
Regular backups are critical to ensure that your data is safe and recoverable. A regular backup schedule depends on the size of your data and the rate of change in the data. A general rule of thumb is to perform backups daily for high-volume transactional databases, and weekly or monthly for databases with lower data-change rates. Here are some other backup best practices:
- Create a backup strategy
-
Define a backup strategy that meets your recovery requirements, such as backup frequency, backup types (full or incremental), and retention periods.
- Back up from a replica node
-
Taking a MySQL backup from a replica node is recommended because it reduces the load on the primary node and ensures a consistent backup. By taking a backup from a replica node, the primary node is not burdened with additional workload, which can impact the live system’s performance. Additionally, since the replica node is in sync with the primary node, taking a backup from it guarantees consistency and reduces the risk of data corruption or loss. Overall, taking backups from a replica node is a best practice for MySQL DBAs to ensure the availability and integrity of their data.
Confirming the integrity of the data in the replica is also very important and is not necessarily guaranteed solely by having replication running. It is the DBA’s job to ensure that it is an exact copy by running tools such as
pt-table-checksum. - Back up the MySQL binlog, or binary log
-
A binary log is a logfile that records all modifications made to a database, including insertions, updates, and deletions. Backing up the MySQL binlog is essential because it can help recover data from a specific point in time, which can be useful in several scenarios, such as disaster recovery, replication, and auditing. In the case of a disaster, such as a hardware failure or data corruption, the binlog can be used to restore the database to a specific point in time before the disaster occurred, minimizing data loss. Additionally, backing up the binlog is critical for replication, as it enables replica databases to synchronize with the primary database. Finally, the binlog can be used for auditing purposes, providing a detailed record of all modifications made to the database, which can be useful for compliance and security. Overall, backing up the MySQL binlog is an essential practice for ensuring data availability, integrity, and security.
- Use MySQL Enterprise Backup
-
MySQL Enterprise Backup is a commercial backup solution that provides enterprise-grade backup and recovery features for MySQL databases. It offers fast, reliable, and scalable backups, along with support for PITR and partial backups.
- Use Percona XtraBackup
-
XtraBackup is a popular open source tool used for backing up and restoring MySQL databases. One of the primary reasons to use XtraBackup is that it performs hot backups of InnoDB and XtraDB storage engines, which means it can take backups without locking the database or causing any downtime. XtraBackup also offers several other useful features, such as parallel compression and incremental backups, which can further improve backup performance and efficiency. Additionally, XtraBackup is highly customizable, allowing DBAs to tailor backups to their specific needs. Overall, XtraBackup is a reliable and flexible tool that simplifies the backup and recovery process for MySQL databases.
- MySQL Shell dump utility
-
MySQL Shell dump is a command-line tool used to back up and restore MySQL databases. One of the key benefits of using the MySQL Shell dump utility is that it provides a more comprehensive backup solution than traditional backup methods. With MySQL Shell dump, it is possible to back up not only the database schema and data but also stored routines, triggers, and user accounts. MySQL Shell dump also offers support for parallel backups, enabling faster and more efficient backups of large databases. Additionally, MySQL Shell dump is highly customizable, allowing DBAs to specify backup options such as compression and selective backups. Overall, MySQL Shell dump is a powerful and flexible tool that simplifies the backup and recovery process for MySQL databases.
- Use
mysqldumpfor small databases -
For small databases,
mysqldumpis a reliable and cost-effective backup solution. It can back up MySQL databases in SQL format, making it easy to restore them in case of data loss. - Test your backups
-
Backing up data is not sufficient if you haven’t verified that the backups are restorable. Therefore, it is important to test your backups regularly to ensure that they can be restored in the event of a disaster. You can test your backups by restoring them to a test environment and checking the data for accuracy and consistency.
- Store your backups offsite
-
Storing backups offsite is important in the event of a disaster like fire, theft, or natural calamities that may affect your primary data center. Keeping backups in a different location ensures that they are safe and recoverable. You can store backups in different cloud storage solutions such as Amazon Simple Storage Service (S3), Google Cloud Storage, or Microsoft Azure, or use physical storage devices such as tapes or external hard drives.
- Use cloud storage
-
Cloud storage providers like Amazon S3, Google Cloud Storage, and Microsoft Azure offer reliable and secure offsite storage options for your backups. When using S3, you can use the Amazon Web Services (AWS) CLI to automate the backup and restore process. For example, you can use the following command to upload your backup to the S3 bucket named mybucket/backups:
awss3cp/backup/full/backup-to-images3://mybucket/backups/ - Use secure transport protocols
-
Use secure transport protocols like SSL or Secure Shell (SSH) to protect your backups from unauthorized access. The AWS CLI employs SSL as its default communication protocol, and for every SSL connection, SSL certificates are verified by the AWS CLI. The following command will transfer your backup image to S3 via SSL:
awss3cp/backup/full/backup-to-images3://mybucket/backups/--sse
The --sse parameter specifies server-side encryption for an object in S3
and can accept the values AES256 or aws:kms. If no value is provided for
the parameter, the default AES256 encryption is used.
Encryption to Protect Your Backups
Encrypting backups ensures that they are secure and protected from
unauthorized access. You can use various encryption methods, such as
symmetric or asymmetric encryption, to encrypt your backups.
MySQL provides encryption options such as the --ssl* option for
mysqldump or the --encrypt option for MySQL Enterprise Backup. Here are some best practices for encrypting your
backups:
- Use MySQL Enterprise Backup
-
MySQL Enterprise Backup offers encryption features that allow you to encrypt your backups with AES-256 encryption. It also supports using key management systems like Oracle Key Vault.
- Use third-party encryption tools
-
You can use third-party encryption tools like GNU Privacy Guard (GnuPG) or OpenSSL to encrypt your backups before storing them offsite.
- Use encryption plug-ins
-
MySQL Enterprise Backup provides encryption plug-ins that you can use to encrypt your backups. For example, you can use the AES-256 encryption plug-in to encrypt your backups via the following command:
mysqlbackup--backup-dir=/backup/full--backup-image=/backup/full/backup-to-image \--encrypt=AES256 --key-file=/path/to/keyfile --host=mydb.example.com \--port=3306 --user=admin --password backup-to-image
This command encrypts your backup image using AES-256 encryption and the key file located at /path/to/key.
XtraBackup Encryption
Since MySQL 8 and Percona Server 8.0 are not supported by Percona XtraBackup 2.4.x, you’ll need to use Percona XtraBackup 8.0.x for backups and restores. This section provides the updated steps to install Percona XtraBackup 8.0.x.
As mentioned before, you’ll need to add the Percona repository to your package manager’s list of available repositories. Open a terminal or command prompt and run the commands in this section.
For Debian/Ubuntu, download the Debian package file by using wget:
root@MyUbuntuMysql8:~# wget https://repo.percona.com/apt/p ercona-release_latest.generic_all.deb --2024-02-20 14:57:01-- https://repo.percona.com/apt/ percona-release_latest.generic_all.deb Resolving repo.percona.com (repo.percona.com)... 147.135.54.159, Length: 11804 (12K) [application/x-debian-package] Saving to: 'percona-release_latest.generic_all.deb' percona-release_latest.generic_all.deb 100% [============================================>] 11.53K --.-KB/s in 0s 2024-02-20 14:57:01 (196 MB/s) - 'percona-release_latest.generic_all.deb' saved [11804/11804] root@MyUbuntuMysql8:~#
The command sudo dpkg is used to install the Debian package:
root@MyUbuntuMysql8:~#sudodpkg-ipercona-release_latest.generic_all.debSelectingpreviouslyunselectedpackagepercona-release.<*>Alldone!*EnablingthePerconaReleaserepository<*>Alldone!Forexample,toenablethePerconaServer8.0repositoryuse:percona-releasesetupps80Note:Toavoidconflictswitholderproductversions,thepercona-releaseroot@MyUbuntuMysql8:~#
Update the package lists for available software repositories:
root@MyUbuntuMysql8:~# sudo apt-get update Hit:1 http://us-east-1.ec2.archive.ubuntu.com/ubuntu jammy InRelease Get:2 http://us-east-1.ec2.archive.ubuntu.com/ubuntu jammy-updates InRelease [119 kB] Hit:3 http://us-east-1.ec2.archive.ubuntu.com/ubuntu jammy-backports InRelease Get:11 http://repo.percona.com/prel/apt jammy/main amd64 Packages [415 B] Fetched 3088 kB in 2s (1713 kB/s) Reading package lists... Done root@MyUbuntuMysql8:~#
To install version 80 of the Percona XtraBackup tool on a system, execute the following command:
root@MyUbuntuMysql8:~#sudoapt-getinstallpercona-xtrabackup-80Readingpackagelists...DoneBuildingdependencytree...DoneReadingstateinformation...DoneThefollowingadditionalpackageswillbeinstalled:libcurl4-openssl-devlibev4Suggestedpackages:libcurl4-doclibidn11-devlibkrb5-devlibldap2-devlibrtmp-devlibssh2-1-devlibssl-devpkg-configzlib1g-devThefollowingNEWpackageswillbeinstalled:libcurl4-openssl-devlibev4percona-xtrabackup-80Processingtriggersforlibc-bin(2.35-0ubuntu3.6)...Scanningprocesses...Runningkernelseemstobeup-to-date.Restartingservices...Servicerestartsbeingdeferred:systemctlrestartsystemd-logind.serviceNocontainersneedtoberestarted.Nousersessionsarerunningoutdatedbinaries.NoVMguestsarerunningoutdatedhypervisor(qemu)binariesonthishost.root@MyUbuntuMysql8:~#
Verify the version of the installed XtraBackup:
root@MyUbuntuMysql8:~#xtrabackup--version2024-02-20T15:02:37.633932-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--innodb_directories=/var/lib/user_defined_general_tablespacextrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)root@MyUbuntuMysql8:~#
To create an encrypted backup using Percona XtraBackup, you can follow these steps:
-
Determine the encryption algorithm. Percona XtraBackup supports three encryption algorithms: AES-128, AES-192, and AES-256. Choose the algorithm you want to use for encryption.
-
Generate an encryption key. You have two options to specify the encryption key when creating encrypted backups:
xtrabackup --encrypt-keyandxtrabackup--encrypt-key-file. Here’s an example command to generate an encryption key:root@MyUbuntuMysql8:~#opensslrand-base6424P/Fu9cZo9gzkzpmtvUKFku3u4ONTrj5Zroot@MyUbuntuMysql8:~#Here’s an example of the XtraBackup command utilizing the
xtrabackup --encrypt-keyoption:root@MyUbuntuMysql8:~#mkdir/root/backups/root@MyUbuntuMysql8:~#xtrabackup--backup --target-dir=/root/backups/--encrypt=AES256 --encrypt-key=""P/Fu9cZo9gzkzpmtvUKFku3u4ONTrj5Z""2024-02-20T15:04:59.723781-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--innodb_directories=/var/lib/user_defined_general_tablespace2024-02-20T15:04:59.724228-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--ssl-mode=REQUIRED --ssl-cert=/var/lib/mysql/client-cert.pem--ssl-key=/var/lib/mysql/client-key.pem --backup=1--target-dir=/root/backups/ --encrypt=AES256 --encrypt-key=*xtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)24022015:04:59version_checkConnectingtoMySQLserverwithDSN'dbi:mysql:; mysql_read_default_group=xtrabackup'(usingpassword:NO).24022015:05:00version_checkConnectedtoMySQLserver24022015:05:00version_checkExecutingaversioncheckagainsttheserver... -
After completing the backup, check the log to confirm its successful completion. You should get a log similar to this:
2024-02-20T15:05:03.706837-00:000[Note][MY-011825][Xtrabackup]Encrypting/root/backups/xtrabackup_info.xbcrypt2024-02-20T15:05:03.707047-00:000[Note][MY-011825][Xtrabackup]Done:Encryptingfile/root/backups/xtrabackup_info.xbcrypt2024-02-20T15:05:04.717140-00:000[Note][MY-011825][Xtrabackup]Transactionlogoflsn(42914523)to(42914533)wascopied.2024-02-20T15:05:04.826343-00:000[Note][MY-010733][Server]Shuttingdownplugin'keyring_file'2024-02-20T15:05:04.828326-00:000[Note][MY-010733][Server]Shuttingdownplugin'daemon_keyring_proxy_plugin'2024-02-20T15:05:04.831369-00:000[Note][MY-011825][Xtrabackup]completedOK!root@MyUbuntuMysql8:~#
In the backup directory, all files are in .xbcrypt format, representing encrypted versions of MySQL database files:
root@MyUbuntuMysql8:~#ls-ltr/root/backups/total76232-rw-r----- 1 root root 12600576 Feb 20 15:05 ibdata1.xbcryptdrwxr-x--- 2 root root 4096 Feb 20 15:05 sysdrwxr-x--- 2 root root 4096 Feb 20 15:05 mydatabasedrwxr-x--- 2 root root 4096 Feb 20 15:05 mytestschemadrwxr-x--- 2 root root 4096 Feb 20 15:05 unencryptedschema-rw-r----- 1 root root 114872 Feb 20 15:05user_defined_general_tablespace.ibd.xbcrypt-rw-r----- 1 root root 114872 Feb 20 15:05user_defined_general_tablespace_1.ibd.xbcryptdrwxr-x--- 2 root root 4096 Feb 20 15:05 encryptedschemadrwxr-x--- 2 root root 4096 Feb 20 15:05 mytestdrwxr-x--- 2 root root 4096 Feb 20 15:05 mytestdbdrwxr-x--- 2 root root 4096 Feb 20 15:05 adddb-rw-r----- 1 root root 31501440 Feb 20 15:05 mysql.ibd.xbcrypt-rw-r----- 1 root root 16800768 Feb 20 15:05 undo_002.xbcrypt-rw-r----- 1 root root 16800768 Feb 20 15:05 undo_001.xbcryptdrwxr-x--- 2 root root 12288 Feb 20 15:05 performance_schemadrwxr-x--- 2 root root 4096 Feb 20 15:05 testdrwxr-x--- 2 root root 4096 Feb 20 15:05 mytestdb_innodrwxr-x--- 2 root root 4096 Feb 20 15:05 mysqldrwxr-x--- 2 root root 4096 Feb 20 15:05 d1-rw-r----- 1 root root 249 Feb 20 15:05 binlog.000076.xbcrypt-rw-r----- 1 root root 108 Feb 20 15:05 binlog.index.xbcrypt-rw-r----- 1 root root 110 Feb 20 15:05 xtrabackup_binlog_info.xbcrypt-rw-r----- 1 root root 2744 Feb 20 15:05 xtrabackup_logfile.xbcrypt-rw-r----- 1 root root 134 Feb 20 15:05 xtrabackup_checkpoints-rw-r----- 1 root root 4600 Feb 20 15:05 ib_buffer_pool.xbcrypt-rw-r----- 1 root root 567 Feb 20 15:05 backup-my.cnf.xbcrypt-rw-r----- 1 root root 597 Feb 20 15:05 xtrabackup_info.xbcrypt-rw-r----- 1 root root 131 Feb 20 15:05 xtrabackup_tablespaces.xbcryptroot@MyUbuntuMysql8:~#
How to Decrypt Encrypted Backups
The --decrypt option in Percona XtraBackup allows for the decryption of backups:
root@MyUbuntuMysql8:~/backups/mysql#xtrabackup--decrypt=AES256--encrypt-key="P/Fu9cZo9gzkzpmtvUKFku3u4ONTrj5Z" --target-dir=/root/backups/--remove-original2024-02-20T15:12:08.321590-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--innodb_directories=/var/lib/user_defined_general_tablespace2024-02-20T15:12:08.322065-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--ssl-mode=REQUIRED --ssl-cert=/var/lib/mysql/client-cert.pem--ssl-key=/var/lib/mysql/client-key.pem --decrypt=AES256 --encrypt-key=*--target-dir=/root/backups/ --remove-original=1xtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)2024-02-20T15:12:08.354574-00:000[Note][MY-011825][Xtrabackup]decrypting./performance_schema/replication_conn_162.sdi.xbcrypt
After the decryption process, examine the log to ensure that it has been successfully completed. You should observe a log resembling this:
2024-02-20T15:12:09.727703-00:000[Note][MY-011825][Xtrabackup]decrypting./xtrabackup_binlog_info.xbcrypt2024-02-20T15:12:09.731566-00:000[Note][MY-011825][Xtrabackup]removing./xtrabackup_binlog_info.xbcrypt2024-02-20T15:12:09.733903-00:000[Note][MY-011825][Xtrabackup]completedOK!root@MyUbuntuMysql8:~/backups/mysql#
Use a secure key-management solution to store and manage your encryption keys. For example, you can use the AWS Key Management Service (KMS) to generate and manage your encryption keys.
How to Prepare the Decrypted Backups
The command xtrabackup --prepare is used to prepare the backup files in the specified directory:
root@MyUbuntuMysql8:~#xtrabackup--prepare --target-dir=/root/backups/2024-02-20T15:15:10.126368-00:000[Note][MY-011825][Xtrabackup]recognizedserverarguments:--innodb_checksum_algorithm=crc32 --innodb_log_checksums=1--innodb_data_file_path=ibdata1:12M:autoextend --innodb_log_file_size=50331648--innodb_page_size=16384 --innodb_undo_directory=./ --innodb_undo_tablespaces=2--server-id=0 --innodb_log_checksums=ON --innodb_redo_log_encrypt=0--innodb_undo_log_encrypt=02024-02-20T15:15:10.126706-00:000[Note][MY-011825][Xtrabackup]recognizedclientarguments:--prepare=1 --target-dir=/root/backups/xtrabackupversion8.0.35-30basedonMySQLserver8.0.35Linux(x86_64)(revisionid:6beb4b49)2024-02-20T15:15:10.127029-00:000[Note][MY-011825][Xtrabackup]cdto/root/backups/2024-02-20T15:15:10.127237-00:000[Note][MY-011825][Xtrabackup]Thistargetseemstobenotpreparedyet.
After completing the preparation process, review the log to confirm its successful execution:
2024-02-20T15:15:12.162295-00:000[Note][MY-012980][InnoDB]Shutdowncompleted;logsequencenumber429148382024-02-20T15:15:12.166062-00:000[Note][MY-010733][Server]Shuttingdownplugin'keyring_file'2024-02-20T15:15:12.166261-00:000[Note][MY-010733][Server]Shuttingdownplugin'daemon_keyring_proxy_plugin'2024-02-20T15:15:12.166814-00:000[Note][MY-011825][Xtrabackup]completedOK!root@MyUbuntuMysql8:~#
mysqldump Encryption
mysqldump encryption secures database backups by converting data into unreadable ciphertext, preventing unauthorized access. A decryption key is needed to revert the ciphertext back to its original, readable form.
You can use the command mysqldump to create a backup of a MySQL database named mytestdb and save it to a file named backup.sql:
root@MyUbuntuMysql8:~/backups#mysqldump-uroot-pmytestdb>backup.sqlEnterpassword:root@MyUbuntuMysql8:~/backups#ls-ltrtotal8-rw-r--r-- 1 root root 5714 Feb 20 15:22 backup.sqlroot@MyUbuntuMysql8:~/backups#
After taking the backup, use the openssl command to encrypt a file named backup.sql with the AES-256-CBC encryption algorithm:
root@MyUbuntuMysql8:~/backups#opensslaes-256-cbc-salt-inbackup.sql-outbackup.sql.encenterAES-256-CBCencryptionpassword:Verifying-enterAES-256-CBCencryptionpassword:root@MyUbuntuMysql8:~/backups#
This code will create a backup of the mydatabase database by using the
mysqldump command and encrypt it using OpenSSL with the AES-256-CBC
algorithm.
To decrypt the backup, you can use the following command:
root@MyUbuntuMysql8:~/backups#opensslaes-256-cbc-d-inbackup.sql.enc-outbackup.sqlenterAES-256-CBCdecryptionpassword:root@MyUbuntuMysql8:~/backups#
This code will decrypt the backup file backup.sql.enc by using the AES-256-CBC algorithm and output the decrypted file to backup.sql.
Conclusion
Backup and recovery are critical activities in any MySQL environment. By following the guidelines in this chapter, you can help ensure that your data is protected and easily recoverable in the event of a disaster.
Get Hands-On MySQL Administration now with the O’Reilly learning platform.
O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.
