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-image
MySQL
Enterprise
Backup
Ver
8
.
0
.
36
-
commercial
for
Linux
on
x86_64
(
MySQL
Enterprise
-
Commercial
)
Copyright
(
c
)
2003
,
2024
,
Oracle
and
/
or
its
affiliates
.
Oracle
is
a
registered
trademark
of
Oracle
Corporation
and
/
or
its
affiliates
.
Other
names
may
be
trademarks
of
their
respective
owners
.
Starting
with
following
command
line
.
.
.
mysqlbackup
--user=root
--password
--backup-image=/backupdir/my.mbi
--backup-dir=/backupdir/backup-tmp
backup
-
to
-
image
IMPORTANT
:
Please
check
that
mysqlbackup
run
completes
successfully
.
At
the
end
of
a
successful
'backup-to-image'
run
mysqlbackup
prints
"mysqlbackup completed OK!"
Enter
password
:
240216
11
:
42
:
48
MAIN
INFO
:
Establishing
connection
to
server
.
WARNING
:
MYSQL_OPT_RECONNECT
is
deprecated
and
will
be
removed
in
a
future
version
.
240216
11
:
42
:
48
MAIN
INFO
:
No
SSL
options
specified
.
240216
11
:
42
:
48
MAIN
INFO
:
MySQL
server
version
is
'8.0.36'
240216
11
:
42
:
48
MAIN
INFO
:
MySQL
server
compile
os
version
is
'Linux'
240216
11
:
42
:
48
MAIN
INFO
:
Got
some
server
configuration
information
from
running
server
.
From the log, you can validate the server repository and backup configuration options:
240216
11
:
42
:
48
MAIN
INFO
:
The
MySQL
server
has
no
active
keyring
.
--------------------------------------------------------------------
Server
Repository
Options
:
--------------------------------------------------------------------
datadir
=
/
var
/
lib
/
mysql
/
innodb_data_home_dir
=
/
var
/
lib
/
mysql
/
datafile
/
innodb_data_file_path
=
ibdata1
:
12
M
;
ibdata2
:
10
M
:
autoextend
innodb_log_group_home_dir
=
/
var
/
lib
/
mysql
/
innodb_undo_directory
=
/
var
/
lib
/
mysql
/
innodb_undo_tablespaces
=
2
innodb_buffer_pool_filename
=
ib_buffer_pool
innodb_page_size
=
16384
innodb_checksum_algorithm
=
crc32
--------------------------------------------------------------------
Backup
Config
Options
:
--------------------------------------------------------------------
datadir
=
/
backupdir
/
backup
-
tmp
/
datadir
innodb_data_home_dir
=
/
backupdir
/
backup
-
tmp
/
datadir
innodb_data_file_path
=
ibdata1
:
12
M
;
ibdata2
:
10
M
:
autoextend
innodb_log_group_home_dir
=
/
backupdir
/
backup
-
tmp
/
datadir
innodb_undo_directory
=
/
backupdir
/
backup
-
tmp
/
datadir
innodb_undo_tablespaces
=
2
innodb_buffer_pool_filename
=
ib_buffer_pool
innodb_page_size
=
16384
innodb_checksum_algorithm
=
crc32
Backup
Image
Path
=
/
backupdir
/
my
.
mbi
240216
11
:
42
:
48
MAIN
INFO
:
Unique
generated
backup
id
for
this
is
17080837682941805
Upon the completion of the backup, you receive the status of completion as indicated in the log:
240216
11
:
42
:
49
RDR1
INFO
:
Copying
/
var
/
lib
/
mysql
/
binlog
.
000012
.
240216
11
:
42
:
49
RDR1
INFO
:
Completed
the
copy
of
binlog
files
.
.
.
240216
11
:
42
:
49
RDR1
INFO
:
The
server
instance
is
unlocked
after
0
.
236
seconds
.
240216
11
:
42
:
49
RDR1
INFO
:
Reading
all
global
variables
from
the
server
.
240216
11
:
42
:
49
RDR1
INFO
:
Completed
reading
of
all
640
global
variables
from
the
server
.
240216
11
:
42
:
49
RDR1
INFO
:
Writing
server
defaults
files
'server-my.cnf'
and
'server-all.cnf'
for
server
'8.0.36'
in
'/backupdir/backup-tmp'
.
240216
11
:
42
:
49
RDR1
INFO
:
Copying
meta
file
/
backupdir
/
backup
-
tmp
/
meta
/
backup_variables
.
txt
.
240216
11
:
42
:
49
RDR1
INFO
:
Copying
meta
file
/
backupdir
/
backup
-
tmp
/
datadir
/
ibbackup_logfile
.
240216
11
:
42
:
49
RDR1
INFO
:
Copying
meta
file
/
backupdir
/
backup
-
tmp
/
server
-
all
.
cnf
.
240216
11
:
42
:
49
RDR1
INFO
:
Copying
meta
file
/
backupdir
/
backup
-
tmp
/
server
-
my
.
cnf
.
240216
11
:
42
:
49
RDR1
INFO
:
Copying
meta
file
/
backupdir
/
backup
-
tmp
/
meta
/
backup_content
.
xml
.
240216
11
:
42
:
49
RDR1
INFO
:
Copying
meta
file
/
backupdir
/
backup
-
tmp
/
meta
/
image_files
.
xml
.
240216
11
:
42
:
49
MAIN
INFO
:
Full
Image
Backup
operation
completed
successfully
.
240216
11
:
42
:
49
MAIN
INFO
:
Backup
image
created
successfully
.
240216
11
:
42
:
49
MAIN
INFO
:
Image
Path
=
/
backupdir
/
my
.
mbi
240216
11
:
42
:
49
MAIN
INFO
:
MySQL
binlog
position
:
filename
binlog
.
000012
,
position
4131
.
-------------------------------------------------------------
Parameters
Summary
-------------------------------------------------------------
Start
LSN
:
37130752
Last
Checkpoint
LSN
:
37130784
End
LSN
:
37204275
-------------------------------------------------------------
mysqlbackup
completed
OK
!
[
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
--user
option. --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 validate
MySQL
Enterprise
Backup
Ver
8
.
0
.
36
-
commercial
for
Linux
on
x86_64
(
MySQL
Enterprise
-
Commercial
)
Copyright
(
c
)
2003
,
2024
,
Oracle
and
/
or
its
affiliates
.
Oracle
is
a
registered
trademark
of
Oracle
Corporation
and
/
or
its
affiliates
.
Other
names
may
be
trademarks
of
their
respective
owners
.
Starting
with
following
command
line
.
.
.
mysqlbackup
--backup-image=/backupdir/my.mbi
validate
IMPORTANT
:
Please
check
that
mysqlbackup
run
completes
successfully
.
At
the
end
of
a
successful
'validate'
run
mysqlbackup
prints
"mysqlbackup completed OK!"
.
240216
11
:
49
:
26
MAIN
INFO
:
Backup
Image
MEB
version
string
:
8
.
0
.
36
240216
11
:
49
:
26
MAIN
INFO
:
MySQL
server
version
is
'8.0.36'
240216
11
:
49
:
26
MAIN
INFO
:
The
backup
image
has
no
keyring
.
240216
11
:
49
:
26
MAIN
INFO
:
Creating
14
buffers
each
of
size
16777216
.
240216
11
:
49
:
26
MAIN
INFO
:
Validate
operation
starts
with
following
threads
1
read
-
threads
6
process
-
threads
240216
11
:
49
:
26
MAIN
INFO
:
Validating
image
.
.
.
/
backupdir
/
my
.
mbi
240216
11
:
49
:
26
PCR6
INFO
:
Validate
:
[
Dir
]
:
meta
240216
11
:
49
:
26
PCR5
INFO
:
Validate
:
[
Dir
]
:
datadir
/
appdb
240216
11
:
49
:
26
PCR5
INFO
:
Validate
:
[
Dir
]
:
datadir
/
datafile
240216
11
:
49
:
26
PCR6
INFO
:
Validate
:
[
Dir
]
:
datadir
/
mydatabase
240216
11
:
49
:
26
PCR4
INFO
:
Validate
:
[
Dir
]
:
datadir
/
mysql
240216
11
:
49
:
26
PCR3
INFO
:
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
@
mysql80
meta
]
#
cat
MEB_2024
-
02
-
16
.
11
-
42
-
48
_backup
-
to
-
image
.
log
|
grep
"error"
[
root
@
mysql80
meta
]
#
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
@
mysql80
meta
]
#
systemctl
stop
mysqld
[
root
@
mysql80
meta
]
#
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
@
mysql80
mysql
]
#
pwd
/
var
/
lib
/
mysql
[
root
@
mysql80
mysql
]
#
rm
-
rf
*
[
root
@
mysql80
mysql
]
#
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
@
mysql80
backup
-
tmp
]
#
mysqlbackup
--datadir=/var/lib/mysql
--backup-image=/backupdir/my.mbi --backup-dir=/backupdir/backup-tmp1
copy
-
back
-
and
-
apply
-
log
MySQL
Enterprise
Backup
Ver
8
.
0
.
36
-
commercial
for
Linux
on
x86_64
(
MySQL
Enterprise
-
Commercial
)
Copyright
(
c
)
2003
,
2024
,
Oracle
and
/
or
its
affiliates
.
Oracle
is
a
registered
trademark
of
Oracle
Corporation
and
/
or
its
affiliates
.
Other
names
may
be
trademarks
of
their
respective
owners
.
Starting
with
following
command
line
.
.
.
mysqlbackup
--datadir=/var/lib/mysql
--backup-image=/backupdir/my.mbi
--backup-dir=/backupdir/backup-tmp1
copy
-
back
-
and
-
apply
-
log
.
.
.
240216
12
:
10
:
24
PCR1
INFO
:
Last
MySQL
binlog
file
position
0
4131
,
file
name
binlog
.
000012
240216
12
:
10
:
24
PCR1
INFO
:
The
first
data
file
is
'/var/lib/mysql/datafile/ibdata1'
and
the
new
created
log
files
are
at
'/var/lib/mysql'
240216
12
:
10
:
24
MAIN
INFO
:
Apply
-
log
operation
completed
successfully
.
240216
12
:
10
:
24
MAIN
INFO
:
Full
Backup
has
been
restored
successfully
.
mysqlbackup
completed
OK
!
[
root
@
mysql80
backup
-
tmp
]
#
The restored files currently have ownership set to the root
user:
[
root
@
mysql80
mysql
]
#
cd
/
var
/
lib
/
mysql
[
root
@
mysql80
mysql
]
#
[
root
@
mysql80
mysql
]
#
ls
-
ltr
total
80104
-
rw
-
r
-----. 1
root root
56 Feb 16 12:10 backup-auto.cnf
-
rw
-
r
-----. 1
root root
14 Feb 16 12:10 backup-mysqld-auto.cnf
drwxr
-
x
---. 2
root root
28 Feb 16 12:10 sys
drwxr
-
x
---. 2
root root
91 Feb 16 12:10 world
drwxr
-
x
---. 2
root root
4096 Feb 16 12:10 sakila
drwxr
-
x
---. 2
root root
44 Feb 16 12:10 mydatabase
-
rw
-
r
-----. 1
root root
114688 Feb 16 12:10 my_tablespace.ibd
drwxr
-
x
---. 2
root root
164 Feb 16 12:10 appdb
drwxr
-
x
---. 2
root root
72 Feb 16 12:10 datafile
-
rw
-
r
-----. 1
root root
180 Feb 16 12:10 binlog.000011
-
rw
-
r
-----. 1
root root
1102 Feb 16 12:10 binlog.000010
drwxr
-
x
---. 2
root root
140 Feb 16 12:10 mytestdb
drwxr
-
x
---. 2
root root
170 Feb 16 12:10 mysql
drwxr
-
x
---. 2
root root
8192 Feb 16 12:10 performance_schema
-
rw
-
r
-----. 1
root root
4131 Feb 16 12:10 binlog.000012
-
rw
-
r
-----. 1
root root
192 Feb 16 12:10 binlog.index
-
rw
-
r
-----. 1
root root
660 Feb 16 12:10 server-my.cnf
-
rw
-
r
-----. 1
root root
19983 Feb 16 12:10 server-all.cnf
-
rw
-
r
-----. 1
root root
16777216 Feb 16 12:10 undo_002
-
rw
-
r
-----. 1
root root
16777216 Feb 16 12:10 undo_001
-
rw
-
r
-----. 1
root root
16777216 Feb 16 12:10 my_undo_tablespace.ibu
-
rw
-
r
-----. 1
root root
27262976 Feb 16 12:10 mysql.ibd
drwxr
-
x
---. 2
root root
23 Feb 16 12:10 '
innodb_redo
'
-
rw
-
r
--r--. 1
root root
723 Feb 16 12:10 backup_variables.txt
[
root
@
mysql80
mysql
]
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
@
mysql80
mysql
]
#
chown
-
R
mysql
.
mysql
/
var
/
lib
/
mysql
[
root
@
mysql80
mysql
]
#
[
root
@
mysql80
mysql
]
#
ls
-
ltr
total
80104
-
rw
-
r
-----. 1
mysql mysql
56 Feb 16 12:10 backup-auto.cnf
-
rw
-
r
-----. 1
mysql mysql
14 Feb 16 12:10 backup-mysqld-auto.cnf
drwxr
-
x
---. 2
mysql mysql
28 Feb 16 12:10 sys
drwxr
-
x
---. 2
mysql mysql
91 Feb 16 12:10 world
drwxr
-
x
---. 2
mysql mysql
4096 Feb 16 12:10 sakila
drwxr
-
x
---. 2
mysql mysql
44 Feb 16 12:10 mydatabase
-
rw
-
r
-----. 1
mysql mysql
114688 Feb 16 12:10 my_tablespace.ibd
drwxr
-
x
---. 2
mysql mysql
164 Feb 16 12:10 appdb
drwxr
-
x
---. 2
mysql mysql
72 Feb 16 12:10 datafile
-
rw
-
r
-----. 1
mysql mysql
180 Feb 16 12:10 binlog.000011
-
rw
-
r
-----. 1
mysql mysql
1102 Feb 16 12:10 binlog.000010
drwxr
-
x
---. 2
mysql mysql
140 Feb 16 12:10 mytestdb
drwxr
-
x
---. 2
mysql mysql
170 Feb 16 12:10 mysql
drwxr
-
x
---. 2
mysql mysql
8192 Feb 16 12:10 performance_schema
-
rw
-
r
-----. 1
mysql mysql
4131 Feb 16 12:10 binlog.000012
-
rw
-
r
-----. 1
mysql mysql
192 Feb 16 12:10 binlog.index
-
rw
-
r
-----. 1
mysql mysql
660 Feb 16 12:10 server-my.cnf
-
rw
-
r
-----. 1
mysql mysql
19983 Feb 16 12:10 server-all.cnf
-
rw
-
r
-----. 1
mysql mysql
16777216 Feb 16 12:10 undo_002
-
rw
-
r
-----. 1
mysql mysql
16777216 Feb 16 12:10 undo_001
-
rw
-
r
-----. 1
mysql mysql
16777216 Feb 16 12:10 my_undo_tablespace.ibu
-
rw
-
r
-----. 1
mysql mysql
27262976 Feb 16 12:10 mysql.ibd
drwxr
-
x
---. 2
mysql mysql
23 Feb 16 12:10 '
innodb_redo
'
-
rw
-
r
--r--. 1
mysql mysql
723 Feb 16 12:10 backup_variables.txt
[
root
@
mysql80
mysql
]
Once you have changed the ownership, start the MySQL server by using the following command:
[
root
@
mysql80
mysql
]
#
systemctl
start
mysqld
[
root
@
mysql80
mysql
]
#
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
@
mysql80
meta
]
#
pwd
/
backupdir
/
backup
-
tmp
/
meta
[
root
@
mysql80
meta
]
#
cat
backup_variables
.
txt
|
grep
binlog_position
binlog_position
=
binlog
.
000012
:
4131
[
root
@
mysql80
meta
]
#
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 -p
2024
-
02
-
20
T07
:
27
:
13
.
279554
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--datadir=/var/lib/mysql
2024
-
02
-
20
T07
:
27
:
13
.
279897
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--user=root --password=* --backup=1 --target-dir=/root/
backupdir
--user=root --password
Enter
password
:
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
240220
07
:
27
:
14
version_check
Connecting
to
MySQL
server
with
DSN
'dbi:mysql:;mysql_read_default_group=xtrabackup'
as
'root'
(
using
password
:
YES
)
.
240220
07
:
27
:
14
version_check
Connected
to
MySQL
server
240220
07
:
27
:
14
version_check
Executing
a
version
check
against
the
server
.
.
.
#
A
software
update
is
available
:
240220
07
:
27
:
15
version_check
Done
.
2024
-
02
-
20
T07
:
27
:
15
.
257381
-
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
-
20
T07
:
27
:
15
.
269103
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Using
server
version
8
.
0
.
36
.
.
2024
-
02
-
20
T07
:
27
:
19
.
627144
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Writing
/
root
/
backupdir
/
backup
-
my
.
cnf
2024
-
02
-
20
T07
:
27
:
19
.
627324
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Writing
file
/
root
/
backupdir
/
backup
-
my
.
cnf
2024
-
02
-
20
T07
:
27
:
19
.
736769
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Writing
/
root
/
backupdir
/
xtrabackup_info
2024
-
02
-
20
T07
:
27
:
19
.
736961
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Writing
file
/
root
/
backupdir
/
xtrabackup_info
2024
-
02
-
20
T07
:
27
:
19
.
768177
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Transaction
log
of
lsn
(
20366453
)
to
(
20366463
)
was
copied
.
2024
-
02
-
20
T07
:
27
:
19
.
998055
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
[
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/backupdir
2024
-
02
-
20
T07
:
29
:
48
.
539204
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--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=0
2024
-
02
-
20
T07
:
29
:
48
.
539575
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--prepare=1 --target-dir=/root/backupdir
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
2024
-
02
-
20
T07
:
29
:
48
.
539839
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
cd
to
/
root
/
backupdir
/
2024
-
02
-
20
T07
:
29
:
48
.
543518
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
This
target
seems
to
be
not
prepared
yet
.
2024
-
02
-
20
T07
:
29
:
48
.
562638
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
xtrabackup_logfile
detected
:
size
=
8388608
,
start_lsn
=
(
20366453
)
.
.
2024
-
02
-
20
T07
:
29
:
49
.
169641
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Completed
loading
of
2
tablespaces
into
cache
in
0
.
00413599
seconds
2024
-
02
-
20
T07
:
29
:
49
.
221375
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Time
taken
to
build
dictionary
:
0
.
0515769
seconds
2024
-
02
-
20
T07
:
29
:
49
.
239865
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
starting
shutdown
with
innodb_fast_shutdown
=
1
2024
-
02
-
20
T07
:
29
:
49
.
240024
-
00
:
00
0
[
Note
]
[
MY
-
012330
]
[
InnoDB
]
FTS
optimize
thread
exiting
.
2024
-
02
-
20
T07
:
29
:
50
.
232093
-
00
:
00
0
[
Note
]
[
MY
-
013072
]
[
InnoDB
]
Starting
shutdown
.
.
.
2024
-
02
-
20
T07
:
29
:
50
.
240493
-
00
:
00
0
[
Note
]
[
MY
-
013084
]
[
InnoDB
]
Log
background
threads
are
being
closed
.
.
.
2024
-
02
-
20
T07
:
29
:
50
.
260550
-
00
:
00
0
[
Note
]
[
MY
-
012980
]
[
InnoDB
]
Shutdown
completed
;
log
sequence
number
20366870
2024
-
02
-
20
T07
:
29
:
50
.
266082
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
[
root
@
mysql80
~
]
#
Stop the MySQL server on the machine where you want to restore the backup:
[
root
@
mysql80
~
]
#
sudo
systemctl
stop
mysqld
[
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
~
]
#
sudo
xtrabackup
--copy-back --target-dir=/root/backupdir
2024
-
02
-
20
T07
:
34
:
03
.
208252
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--datadir=/var/lib/mysql
2024
-
02
-
20
T07
:
34
:
03
.
208429
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--user=root --password=* --copy-back=1 --target-dir=/root/
backupdir
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
2024
-
02
-
20
T07
:
34
:
03
.
208499
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
cd
to
/
root
/
backupdir
/
2024
-
02
-
20
T07
:
34
:
03
.
212994
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Copying
undo_001
to
/
var
/
lib
/
mysql
/
undo_001
2024
-
02
-
20
T07
:
34
:
03
.
372784
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Copying
undo_001
to
/
var
/
lib
/
mysql
/
undo_001
2024
-
02
-
20
T07
:
34
:
03
.
379912
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Copying
undo_002
to
/
var
/
lib
/
mysql
/
undo_002
2024
-
02
-
20
T07
:
34
:
03
.
481821
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Copying
undo_002
to
/
var
/
lib
/
mysql
/
undo_002
2024
-
02
-
20
T07
:
34
:
03
.
488697
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Copying
ibdata1
to
/
var
/
lib
/
mysql
/
ibdata1
2024
-
02
-
20
T07
:
34
:
03
.
557571
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Copying
ibdata1
to
/
var
/
lib
/
mysql
/
ibdata1
.
.
.
2024
-
02
-
20
T07
:
34
:
04
.
081817
-
00
:
00
1
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Copying
.
/
ib_buffer_pool
to
/
var
/
lib
/
mysql
/
ib_buffer_pool
2024
-
02
-
20
T07
:
34
:
04
.
082713
-
00
:
00
1
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Copying
.
/
ib_buffer_pool
to
/
var
/
lib
/
mysql
/
ib_buffer_pool
2024
-
02
-
20
T07
:
34
:
04
.
084413
-
00
:
00
1
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Copying
.
/
xtrabackup_info
to
/
var
/
lib
/
mysql
/
xtrabackup_info
2024
-
02
-
20
T07
:
34
:
04
.
084548
-
00
:
00
1
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Copying
.
/
xtrabackup_info
to
/
var
/
lib
/
mysql
/
xtrabackup_info
2024
-
02
-
20
T07
:
34
:
04
.
086055
-
00
:
00
1
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Creating
directory
.
/
#
innodb_redo
2024
-
02
-
20
T07
:
34
:
04
.
086133
-
00
:
00
1
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
creating
directory
.
/
#
innodb_redo
2024
-
02
-
20
T07
:
34
:
04
.
086241
-
00
:
00
1
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Copying
.
/
ibtmp1
to
/
var
/
lib
/
mysql
/
ibtmp1
2024
-
02
-
20
T07
:
34
:
04
.
131401
-
00
:
00
1
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Copying
.
/
ibtmp1
to
/
var
/
lib
/
mysql
/
ibtmp1
2024
-
02
-
20
T07
:
34
:
04
.
177825
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
[
root
@
mysql80
~
]
#
After copying the files, you need to set the ownership and permissions of the data directory to the mysql
user:
[
root
@
mysql80
~
]
#
sudo
chown
-
R
mysql
:
mysql
/
var
/
lib
/
mysql
[
root
@
mysql80
~
]
#
Start the MySQL server with the following command:
[
root
@
mysql80
~
]
#
sudo
systemctl
start
mysqld
[
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
.
log
2024
-
02
-
20
T07
:
37
:
48
.
624988
Z
0
[
System
]
[
MY
-
011323
]
[
Server
]
X
Plugin
ready
for
connections
.
Bind
-
address
:
'::'
port
:
33060
,
socket
:
/
var
/
run
/
mysqld
/
mysqlx
.
sock
2024
-
02
-
20
T07
:
37
:
48
.
625234
Z
0
[
System
]
[
MY
-
010931
]
[
Server
]
/
usr
/
sbin
/
mysqld
:
ready
for
connections
.
Version
:
'8.0.36'
socket
:
'/var/lib/mysql/mysql.sock'
port
:
3306
MySQL
Community
Server
-
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/base
2024
-
02
-
23
T11
:
50
:
13
.
697883
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--datadir=/var/lib/mysql
2024
-
02
-
23
T11
:
50
:
13
.
698792
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--user=root --password=* --backup=1 --target-dir=/data/
backups
/
base
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
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
-
23
T11
:
50
:
13
.
728094
-
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
-
23
T11
:
50
:
14
.
135079
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Using
server
version
8
.
0
.
36
2024
-
02
-
23
T11
:
50
:
14
.
149822
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Executing
LOCK
INSTANCE
FOR
BACKUP
.
.
.
2024
-
02
-
23
T11
:
50
:
14
.
151389
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
uses
posix_fadvise
(
)
.
2024
-
02
-
23
T11
:
50
:
14
.
151443
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
cd
to
/
var
/
lib
/
mysql
2024
-
02
-
23
T11
:
50
:
14
.
151460
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
open
files
limit
requested
0
,
set
to
1024
2024
-
02
-
23
T11
:
50
:
14
.
174389
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
using
the
following
InnoDB
configuration
:
2024
-
02
-
23
T11
:
50
:
14
.
174418
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_data_home_dir
=
.
2024
-
02
-
23
T11
:
50
:
14
.
174429
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_data_file_path
=
ibdata1
:
12
M
:
autoextend
2024
-
02
-
23
T11
:
50
:
14
.
174470
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_log_group_home_dir
=
.
/
2024
-
02
-
23
T11
:
50
:
14
.
174482
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_log_files_in_group
=
2
.
.
.
.
2024
-
02
-
23
T11
:
50
:
32
.
554338
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Writing
file
/
data
/
backups
/
base
/
backup
-
my
.
cnf
2024
-
02
-
23
T11
:
50
:
33
.
071966
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Writing
/
data
/
backups
/
base
/
xtrabackup_info
2024
-
02
-
23
T11
:
50
:
33
.
072211
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Writing
file
/
data
/
backups
/
base
/
xtrabackup_info
2024
-
02
-
23
T11
:
50
:
33
.
188617
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Transaction
log
of
lsn
(
20289655
)
to
(
20289665
)
was
copied
.
2024
-
02
-
23
T11
:
50
:
33
.
405903
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
[
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_checkpoints
backup_type
=
full
-
backuped
from_lsn
=
0
to_lsn
=
20289655
last_lsn
=
20289655
flushed_lsn
=
20289655
redo_memory
=
0
redo_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 -p
2024
-
02
-
23
T12
:
07
:
38
.
147005
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--datadir=/var/lib/mysql
2024
-
02
-
23
T12
:
07
:
38
.
147368
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--user=root --password=* --backup=1 --target-dir=/data/
backups
/
inc1
--incremental-basedir=/data/backups/base --user=root --password
Enter
password
:
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
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
-
23
T12
:
07
:
52
.
016406
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Writing
/
data
/
backups
/
inc1
/
backup
-
my
.
cnf
2024
-
02
-
23
T12
:
07
:
52
.
016580
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Writing
file
/
data
/
backups
/
inc1
/
backup
-
my
.
cnf
2024
-
02
-
23
T12
:
07
:
52
.
185241
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Writing
/
data
/
backups
/
inc1
/
xtrabackup_info
2024
-
02
-
23
T12
:
07
:
52
.
185445
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Writing
file
/
data
/
backups
/
inc1
/
xtrabackup_info
2024
-
02
-
23
T12
:
07
:
53
.
188689
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Transaction
log
of
lsn
(
20784993
)
to
(
20784993
)
was
copied
.
2024
-
02
-
23
T12
:
07
:
53
.
414360
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
[
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_checkpoints
backup_type
=
incremental
from_lsn
=
20289655
to_lsn
=
20784993
last_lsn
=
20784993
flushed_lsn
=
20784993
redo_memory
=
0
redo_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/inc1
2024
-
02
-
23
T13
:
10
:
43
.
005350
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--datadir=/var/lib/mysql
2024
-
02
-
23
T13
:
10
:
43
.
008358
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--user=root --password=* --backup=1 --target-dir=/data/
backups
/
inc2
--incremental-basedir=/data/backups/inc1
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
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
-
23
T13
:
10
:
50
.
814250
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Writing
/
data
/
backups
/
inc2
/
backup
-
my
.
cnf
2024
-
02
-
23
T13
:
10
:
50
.
814471
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Writing
file
/
data
/
backups
/
inc2
/
backup
-
my
.
cnf
2024
-
02
-
23
T13
:
10
:
51
.
014542
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Writing
/
data
/
backups
/
inc2
/
xtrabackup_info
2024
-
02
-
23
T13
:
10
:
51
.
014890
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Writing
file
/
data
/
backups
/
inc2
/
xtrabackup_info
2024
-
02
-
23
T13
:
10
:
51
.
018154
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Transaction
log
of
lsn
(
21291941
)
to
(
21291941
)
was
copied
.
2024
-
02
-
23
T13
:
10
:
51
.
140104
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
[
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_checkpoints
backup_type
=
incremental
from_lsn
=
20784993
to_lsn
=
21291941
last_lsn
=
21291941
flushed_lsn
=
21291941
redo_memory
=
0
redo_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
@
mysql8
backups
]
#
ls
-
ltr
total
12
drwxr
-
xr
-
x
.
5
root
root
4096
Feb
23
11
:
50
base
drwxr
-
x
---. 6 root root 4096 Feb 23 12:07 inc1
drwxr
-
x
---. 6 root root 4096 Feb 23 13:10 inc2
[
root
@
mysql8
backups
]
#
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
@
mysql8
backups
]
#
xtrabackup
--prepare --apply-log-only \
--target-dir=/data/backups/base
2024
-
02
-
23
T13
:
28
:
08
.
266142
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--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=0
2024
-
02
-
23
T13
:
28
:
08
.
266501
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--prepare=1 --apply-log-only=1 --target-dir=/data/backups/base
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
2024
-
02
-
23
T13
:
28
:
08
.
266735
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
cd
to
/
data
/
backups
/
base
/
2024
-
02
-
23
T13
:
28
:
08
.
268271
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
This
target
seems
to
be
not
prepared
yet
.
2024
-
02
-
23
T13
:
28
:
08
.
290071
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
inititialize_service_handles
suceeded
2024
-
02
-
23
T13
:
28
:
08
.
290782
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
using
the
following
InnoDB
configuration
for
recovery
:
2024
-
02
-
23
T13
:
28
:
08
.
290856
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_data_home_dir
=
.
2024
-
02
-
23
T13
:
28
:
08
.
290906
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_data_file_path
=
ibdata1
:
12
M
:
autoextend
After this command completes, the output should end with text similar to this:
2024
-
02
-
23
T13
:
28
:
09
.
840970
-
00
:
00
0
[
Note
]
[
MY
-
013072
]
[
InnoDB
]
Starting
shutdown
.
.
.
2024
-
02
-
23
T13
:
28
:
09
.
848432
-
00
:
00
0
[
Note
]
[
MY
-
013084
]
[
InnoDB
]
Log
background
threads
are
being
closed
.
.
.
2024
-
02
-
23
T13
:
28
:
09
.
868724
-
00
:
00
0
[
Note
]
[
MY
-
012980
]
[
InnoDB
]
Shutdown
completed
;
log
sequence
number
20289665
2024
-
02
-
23
T13
:
28
:
09
.
876150
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
[
root
@
mysql8
backups
]
#
To apply the first incremental backup to the full backup, you can run the following command:
[
root
@
mysql8
backups
]
#
xtrabackup
--prepare --apply-log-only --target-dir=/data/
backups
/
base
\
--incremental-dir=/data/backups/inc1
2024
-
02
-
23
T13
:
30
:
43
.
873233
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--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=0
2024
-
02
-
23
T13
:
30
:
43
.
874162
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--prepare=1 --apply-log-only=1 --target-dir=/data/backups/base
--incremental-dir=/data/backups/inc1
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
2024
-
02
-
23
T13
:
30
:
43
.
877184
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
incremental
backup
from
20289655
is
enabled
.
2024
-
02
-
23
T13
:
30
:
43
.
877299
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
cd
to
/
data
/
backups
/
base
/
2024
-
02
-
23
T13
:
30
:
43
.
878200
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
This
target
seems
to
be
already
prepared
with
--apply-log-only.
2024
-
02
-
23
T13
:
30
:
43
.
888050
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
xtrabackup_logfile
detected
:
size
=
8388608
,
start_lsn
=
(
20784993
)
2024
-
02
-
23
T13
:
30
:
43
.
888535
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
using
the
following
InnoDB
configuration
for
recovery
:
2024
-
02
-
23
T13
:
30
:
43
.
888643
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_data_home_dir
=
.
2024
-
02
-
23
T13
:
30
:
43
.
888699
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_data_file_path
=
ibdata1
:
12
M
:
autoextend
2024
-
02
-
23
T13
:
30
:
43
.
888796
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_log_group_home_dir
=
/
data
/
backups
/
inc1
/
2024
-
02
-
23
T13
:
30
:
46
.
424279
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Copying
/
data
/
backups
/
inc1
/
binlog
.
000006
to
.
/
binlog
.
000006
2024
-
02
-
23
T13
:
30
:
46
.
425801
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Copying
/
data
/
backups
/
inc1
/
binlog
.
index
to
.
/
binlog
.
index
2024
-
02
-
23
T13
:
30
:
46
.
425940
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Copying
/
data
/
backups
/
inc1
/
binlog
.
index
to
.
/
binlog
.
index
2024
-
02
-
23
T13
:
30
:
46
.
427799
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
[
root
@
mysql8
backups
]
#
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
@
mysql8
backups
]
#
xtrabackup
--prepare --target-dir=/data/backups/base \
--incremental-dir=/data/backups/inc2
2024
-
02
-
23
T13
:
32
:
54
.
413264
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--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=0
2024
-
02
-
23
T13
:
32
:
54
.
414978
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--prepare=1 --target-dir=/data/backups/base
--incremental-dir=/data/backups/inc2
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
2024
-
02
-
23
T13
:
32
:
54
.
415995
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
incremental
backup
from
20784993
is
enabled
.
2024
-
02
-
23
T13
:
32
:
54
.
416111
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
cd
to
/
data
/
backups
/
base
/
2024
-
02
-
23
T13
:
32
:
54
.
416290
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
This
target
seems
to
be
already
prepared
with
--apply-log-only.
2024
-
02
-
23
T13
:
32
:
54
.
426669
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
xtrabackup_logfile
detected
:
size
=
8388608
,
start_lsn
=
(
21291941
)
2024
-
02
-
23
T13
:
32
:
54
.
427121
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
using
the
following
InnoDB
configuration
for
recovery
:
2024
-
02
-
23
T13
:
32
:
54
.
427198
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_data_home_dir
=
.
2024
-
02
-
23
T13
:
32
:
54
.
427266
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_data_file_path
=
ibdata1
:
12
M
:
autoextend
2024
-
02
-
23
T13
:
32
:
54
.
427333
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
innodb_log_group_home_dir
=
/
data
/
backups
/
inc2
/
2024
-
02
-
23
T13
:
32
:
57
.
169249
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Copying
/
data
/
backups
/
inc2
/
binlog
.
000011
to
.
/
binlog
.
000011
2024
-
02
-
23
T13
:
32
:
57
.
170686
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Copying
/
data
/
backups
/
inc2
/
binlog
.
index
to
.
/
binlog
.
index
2024
-
02
-
23
T13
:
32
:
57
.
170819
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Copying
/
data
/
backups
/
inc2
/
binlog
.
index
to
.
/
binlog
.
index
2024
-
02
-
23
T13
:
32
:
57
.
173092
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
[
root
@
mysql8
backups
]
#
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:3306
MySQL
Shell
8
.
0
.
36
Copyright
(
c
)
2016
,
2023
,
Oracle
and
/
or
its
affiliates
.
Oracle
is
a
registered
trademark
of
Oracle
Corporation
and
/
or
its
Server
version
:
8
.
0
.
36
MySQL
Community
Server
-
GPL
No
default
schema
selected
;
type
\
use
<
schema
>
to
set
one
.
MySQL
localhost
:
3306
ssl
JS
>
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:
MySQL
localhost
:
3306
ssl
JS
>
util
.
dumpInstance
(
"/mysqlsh/backuputildump/"
,
{
dryRun
:
"true"
}
)
dryRun
enabled
,
no
locks
will
be
acquired
and
no
files
will
be
created
.
Acquiring
global
read
lock
Global
read
lock
acquired
Initializing
-
done
1
out
of
5
schemas
will
be
dumped
and
within
them
1
table
,
0
views
.
1
out
of
4
users
will
be
dumped
.
Gathering
information
-
done
All
transactions
have
been
started
Locking
instance
for
backup
Global
read
lock
has
been
released
Writing
global
DDL
files
Writing
users
DDL
Writing
DDL
-
done
Starting
data
dump
0
%
(
0
rows
/
~
20
rows
)
,
0
.
00
rows
/
s
,
0
.
00
B
/
s
uncompressed
,
0
.
00
B
/
s
compressed
MySQL
localhost
:
3306
ssl
JS
>
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:
MySQL
localhost
:
3306
ssl
JS
>
util
.
dumpInstance
(
"/backuputildump"
)
Acquiring
global
read
lock
Global
read
lock
acquired
Initializing
-
done
1
out
of
5
schemas
will
be
dumped
and
within
them
1
table
,
0
views
.
1
out
of
4
users
will
be
dumped
.
Gathering
information
-
done
All
transactions
have
been
started
Locking
instance
for
backup
Global
read
lock
has
been
released
Writing
global
DDL
files
Writing
users
DDL
Running
data
dump
using
4
threads
.
NOTE
:
Progress
information
uses
estimated
values
and
may
not
be
accurate
.
Writing
schema
metadata
-
done
Writing
DDL
-
done
Writing
table
metadata
-
done
Starting
data
dump
100
%
(
20
rows
/
~
20
rows
)
,
0
.
00
rows
/
s
,
0
.
00
B
/
s
uncompressed
,
0
.
00
B
/
s
compressed
Dump
duration
:
00
:
00
:
00
s
Total
duration
:
00
:
00
:
00
s
Schemas
dumped
:
1
Tables
dumped
:
1
Uncompressed
data
size
:
727
bytes
Compressed
data
size
:
258
bytes
Compression
ratio
:
2
.
8
Rows
written
:
20
Bytes
written
:
258
bytes
Average
uncompressed
throughput
:
727
.
00
B
/
s
Average
compressed
throughput
:
258
.
00
B
/
s
MySQL
localhost
:
3306
ssl
JS
>
The backup is stored in the following directory:
[
root
@
mysql80
~
]
#
ls
-
ltr
/
backuputildump
total
44
-
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:
MySQL
localhost
:
3306
ssl
JS
>
util
.
dumpInstance
(
\
"/backuputildump/ mysqlinstance"
,
{
threads
:
8
,
maxRate
:
\
"100M"
,
consistent
:
true
,
chunking
:
true
,
bytesPerchunk
:
\
"64M"
,
compression
:
\
"zstd"
}
)
Acquiring
global
read
lock
Global
read
lock
acquired
Initializing
-
done
1
out
of
5
schemas
will
be
dumped
and
within
them
1
table
,
0
views
.
1
out
of
4
users
will
be
dumped
.
Gathering
information
-
done
All
transactions
have
been
started
Locking
instance
for
backup
Global
read
lock
has
been
released
Writing
global
DDL
files
Writing
users
DDL
Running
data
dump
using
8
threads
.
NOTE
:
Progress
information
uses
estimated
values
and
may
not
be
accurate
.
Writing
schema
metadata
-
done
Writing
DDL
-
done
Writing
table
metadata
-
done
Starting
data
dump
100
%
(
20
rows
/
~
20
rows
)
,
0
.
00
rows
/
s
,
0
.
00
B
/
s
uncompressed
,
0
.
00
B
/
s
compressed
Dump
duration
:
00
:
00
:
00
s
Total
duration
:
00
:
00
:
00
s
Schemas
dumped
:
1
Tables
dumped
:
1
Uncompressed
data
size
:
727
bytes
Compressed
data
size
:
258
bytes
Compression
ratio
:
2
.
8
Rows
written
:
20
Bytes
written
:
258
bytes
Average
uncompressed
throughput
:
727
.
00
B
/
s
Average
compressed
throughput
:
258
.
00
B
/
s
MySQL
localhost
:
3306
ssl
JS
>
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:
MySQL
localhost
:
3306
ssl
JS
>
util
.
dumpSchemas
(
[
\
"employees"
]
,
\
"/backupdir/employees"
,
{
threads
:
2
}
)
Acquiring
global
read
lock
Global
read
lock
acquired
Initializing
-
done
1
schemas
will
be
dumped
and
within
them
2
tables
,
0
views
.
Gathering
information
-
done
All
transactions
have
been
started
Locking
instance
for
backup
Global
read
lock
has
been
released
Writing
global
DDL
files
Running
data
dump
using
2
threads
.
NOTE
:
Progress
information
uses
estimated
values
and
may
not
be
accurate
.
Writing
schema
metadata
-
done
Writing
DDL
-
done
Writing
table
metadata
-
done
Starting
data
dump
100
%
(
8
rows
/
~
8
rows
)
,
0
.
00
rows
/
s
,
0
.
00
B
/
s
uncompressed
,
0
.
00
B
/
s
compressed
Dump
duration
:
00
:
00
:
00
s
Total
duration
:
00
:
00
:
00
s
Schemas
dumped
:
1
Tables
dumped
:
2
Uncompressed
data
size
:
224
bytes
Compressed
data
size
:
182
bytes
Compression
ratio
:
1
.
2
Rows
written
:
8
Bytes
written
:
182
bytes
Average
uncompressed
throughput
:
224
.
00
B
/
s
Average
compressed
throughput
:
182
.
00
B
/
s
MySQL
localhost
:
3306
ssl
JS
>
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
/
employees
total
64
-
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:
MySQL
localhost
:
3306
ssl
JS
>
util
.
dumpTables
(
\
"employees"
,
[
\
"salaries"
]
,
\
"/backupdir/employees/salaries"
,
{
threads
:
2
}
)
Acquiring
global
read
lock
Global
read
lock
acquired
Initializing
-
done
1
tables
and
0
views
will
be
dumped
.
Gathering
information
-
done
All
transactions
have
been
started
Locking
instance
for
backup
Global
read
lock
has
been
released
Writing
global
DDL
files
Running
data
dump
using
2
threads
.
NOTE
:
Progress
information
uses
estimated
values
and
may
not
be
accurate
.
Writing
schema
metadata
-
done
Writing
DDL
-
done
Writing
table
metadata
-
done
Starting
data
dump
100
%
(
4
rows
/
~
4
rows
)
,
0
.
00
rows
/
s
,
0
.
00
B
/
s
uncompressed
,
0
.
00
B
/
s
compressed
Dump
duration
:
00
:
00
:
00
s
Total
duration
:
00
:
00
:
00
s
Schemas
dumped
:
1
Tables
dumped
:
1
Uncompressed
data
size
:
88
bytes
Compressed
data
size
:
61
bytes
Compression
ratio
:
1
.
4
Rows
written
:
4
Bytes
written
:
61
bytes
Average
uncompressed
throughput
:
88
.
00
B
/
s
Average
compressed
throughput
:
61
.
00
B
/
s
MySQL
localhost
:
3306
ssl
JS
>
The backup is stored in the following directory, which is only for the employees.salaries
table:
[
root
@
mysql80
~
]
#
ls
-
ltr
/
backupdir
/
employees
/
salaries
total
48
-
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:
MySQL
localhost
:
3306
ssl
JS
>
util
.
loadDump
(
\
"/backuputildump/"
,
{
dryRun
:
true
}
)
Loading
DDL
and
Data
from
'/backuputildump/'
using
4
threads
.
Opening
dump
.
.
.
dryRun
enabled
,
no
changes
will
be
made
.
Target
is
MySQL
8
.
0
.
36
.
Dump
was
produced
from
MySQL
8
.
0
.
36
Scanning
metadata
-
done
Checking
for
pre
-
existing
objects
.
.
.
Executing
common
preamble
SQL
Executing
DDL
-
done
Executing
view
DDL
-
done
Starting
data
load
Executing
common
postamble
SQL
0
%
(
0
bytes
/
727
bytes
)
,
0
.
00
B
/
s
,
1
/
1
tables
done
Recreating
indexes
-
done
No
data
loaded
.
0
warnings
were
reported
during
the
load
.
MySQL
localhost
:
3306
ssl
JS
>
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:
MySQL
localhost
:
3306
ssl
JS
>
util
.
loadDump
(
\
"/backuputildump/"
,
{
progressFile
:
\
"/backuputildump/backuplog.json"
,
threads
:
4
,
backgroundThreads
:
4
,
maxBytesPerTransaction
:
"4096"
}
)
Loading
DDL
and
Data
from
'/backuputildump/'
using
4
threads
.
Opening
dump
.
.
.
Target
is
MySQL
8
.
0
.
36
.
Dump
was
produced
from
MySQL
8
.
0
.
36
Scanning
metadata
-
done
Checking
for
pre
-
existing
objects
.
.
.
Executing
common
preamble
SQL
Executing
DDL
-
done
Executing
view
DDL
-
done
Starting
data
load
Executing
common
postamble
SQL
100
%
(
727
bytes
/
727
bytes
)
,
0
.
00
B
/
s
,
1
/
1
tables
done
Recreating
indexes
-
done
1
chunks
(
20
rows
,
727
bytes
)
for
1
tables
in
1
schemas
were
loaded
in
0
sec
(
avg
throughput
727
.
00
B
/
s
)
0
warnings
were
reported
during
the
load
.
MySQL
localhost
:
3306
ssl
JS
>
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
~
]
#
man
mysqldump
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:
mysqldump
database_name
table_name
>
backup_file
.
sql
For example, to create a backup of the city
table in the world
database
, use the following command:
[
root
@
mysql80
backup
]
#
mysqldump
-
uroot
-
p
world
city
>
city
.
sql
Enter
password
:
[
root
@
mysql80
backup
]
#
ls
-
ltr
total
176
-
rw
-
r
--r--. 1 root root 179263 Feb 20 12:59
city.sql
[
root
@
mysql80
backup
]
#
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
@
mysql80
backup
]
#
mysqldump
world
-
u
root
-
p
city
--where=\"CountryCode='USA'" > city_USA.sql
Enter
password
:
[
root
@
mysql80
backup
]
#
ls
-
ltr
-
rw
-
r
--r--. 1 root root 14060 Feb 20 13:01
city_USA.sql
[
root
@
mysql80
backup
]
#
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
@
mysql80
backup
]
#
mysqldump
-
u
root
-
p
world
--ignore-table=world.city > ignore_city_world.sql
Enter
password
:
[
root
@
mysql80
backup
]
#
ls
-
ltr
-
rw
-
r
--r--. 1 root root 66071 Feb 20 13:08
ignore_city_world.sql
[
root
@
mysql80
backup
]
#
To create a whole database backup, use the mysqldump
command with the database name as an argument:
mysqldump
database_name
>
backup_file
.
sql
For example, to back up the world
database, use the following
command:
[
root
@
mysql80
backup
]
#
mysqldump
-
u
root
-
p
world
>
world
.
sql
Enter
password
:
[
root
@
mysql80
backup
]
#
ls
-
ltr
-
rw
-
r
--r--. 1 root root 244085 Feb 20 13:05
world.sql
[
root
@
mysql80
backup
]
#
Backing up multiple databases
To back up several databases, specify them as a comma-separated list:
mysqldump
--databases
database1,database2
>
backup_file.sql
To back up the mytestdb sakila world_x
and example_db1
databases, for example, execute the following command:
[
root
@
mysql80
backup
]
#
mysqldump
-
u
root
-
p
--databases mytestdb employees
sakila
>
all_db_backup
.
sql
Enter
password
:
[
root
@
mysql80
backup
]
#
ls
-
ltr
-
rw
-
r
--r--. 1 root root 3390832 Feb 20 13:27
all_db_backup.sql
[
root
@
mysql80
backup
]
#
Backing up all databases
To back up all databases on the MySQL server, use the --all-databases
option:
[
root
@
mysql80
backup
]
#
mysqldump
-
u
root
-
p
--all-databases
>
all_databases_backup
.
sql
Enter
password
:
[
root
@
mysql80
backup
]
#
ls
-
ltr
-
rw
-
r
--r--. 1 root root 4937167 Feb 20 13:29
all_databases_backup.sql
[
root
@
mysql80
backup
]
#
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
@
mysql80
backup
]
#
mysqldump
-
u
root
-
p
--single-transaction --quick
--lock-tables=false --routines --events --triggers
--default-character-set=utf8mb4 sakila >backup.sql
Enter
password
:
[
root
@
mysql80
backup
]
#
ls
-
ltr
-
rw
-
r
--r--. 1 root root 3397023 Feb 20 13:31
backup.sql
[
root
@
mysql80
backup
]
#
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
mysqldump
to 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
mysqldump
to 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
@
mysql80
backup
]
#
cat
backup_script
.
sh
#
!
/
bin
/
bash
#
Set
the
MySQL
username
and
password
MYSQL_USER
=
"root"
MYSQL_PASSWORD
=
'D@#NJU#$@MK28#nM
'
#
Set
the
backup
directory
BACKUP_DIR
=
"/root/backupdir/newbackup"
#
Take
a
full
backup
using
Percona
XtraBackup
xtrabackup
--backup --user=$MYSQL_USER --password=$MYSQL_PASSWORD
--target-dir=$BACKUP_DIR/full_backup_$(date +%Y-%m-%d_%H-%M-%S)
[
root
@
mysql80
backup
]
#
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
@
mysql80
backup
]
#
chmod
+
x
backup_script
.
sh
[
root
@
mysql80
backup
]
#
ls
-
ltr
total
4
-
rwxr
-
xr
-
x
.
1
root
root
344
Feb
20
13
:
35
backup_script
.
sh
[
root
@
mysql80
backup
]
#
The command ./backup_script.sh
executes a shell script named backup_script.sh that is located in the current directory:
[
root
@
mysql80
backup
]
#
.
/
backup_script
.
sh
2024
-
02
-
20
T13
:
42
:
56
.
095800
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--datadir=/var/lib/mysql
2024
-
02
-
20
T13
:
42
:
56
.
096132
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--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-55
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
240220
13
:
42
:
56
version_check
Connecting
to
MySQL
server
with
DSN
'dbi:mysql:; mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock'
as
'root'
(
using
password
:
YES
)
.
240220
13
:
42
:
56
version_check
Connected
to
MySQL
server
240220
13
:
42
:
56
version_check
Executing
a
version
check
against
the
server
.
.
.
240220
13
:
42
:
56
version_check
Done
.
2024
-
02
-
20
T13
:
42
:
56
.
369830
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Connecting
to
MySQL
server
host
:
localhost
,
user
:
root
,
password
:
set
,
port
:
not
set
,
socket
:
/
var
/
lib
/
mysql
/
mysql
.
sock
2024
-
02
-
20
T13
:
42
:
59
.
708031
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Writing
file
/
root
/
backupdir
/
newbackup
/
full_backup_2024
-
02
-
20
_13
-
42
-
55
/
xtrabackup_info
2024
-
02
-
20
T13
:
43
:
00
.
711702
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Transaction
log
of
lsn
(
32549485
)
to
(
32549485
)
was
copied
.
2024
-
02
-
20
T13
:
43
:
00
.
831082
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
[
root
@
mysql80
backup
]
#
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
-
e
0
2
*
*
*
/
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
~
]
#
sudo
mkdir
-
p
/
mnt
/
backups
/
mysql
/
[
root
@
mysql80
~
]
#
-
Grant the necessary permissions to the backup directory:
[
root
@
mysql80
~
]
#
sudo
chown
-
R
mysql
:
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
~
]
#
sudo
chmod
-
R
700
/
mnt
/
backups
/
mysql
/
[
root
@
mysql80
~
]
#
-
Create a shell script:
[
root
@
mysql80
mysql
]
#
cat
/
mnt
/
backups
/
mysql
/
backup_mysqldump
.
sh
#
!
/
bin
/
bash
#
Set
the
backup
directory
backup_dir
=
"/mnt/backups/mysql"
#
Set
the
MySQL
credentials
mysql_user
=
"root"
mysql_password
=
'D@#NJU#$@MK28#nM'
#
Create
the
backup
filename
with
date
stamp
backup_filename
=
"$backup_dir/daily_backup_$(date +%Y%m%d).sql"
#
Execute
the
mysqldump
command
and
save
output
to
file
mysqldump
--all-databases --user="$mysql_user" --password="$mysql_password"
>
"$backup_filename"
[
root
@
mysql80
mysql
]
#
-
Test the script by executing it:
[
root
@
mysql80
mysql
]
#
/
mnt
/
backups
/
mysql
/
backup_mysqldump
.
sh
/
mnt
/
backups
/
mysql
/
daily_backup_20240220
.
sql
[
root
@
mysql80
mysql
]
#
ls
-
ltr
total
4828
-
rwxr
-
xr
-
x
.
1
root
root
472
Feb
20
14
:
18
backup_mysqldump
.
sh
-
rw
-
r
--r--. 1 root root 4937167 Feb 20 14:18 daily_backup_20240220.sql
[
root
@
mysql80
mysql
]
#
-
Open the crontab configuration:
crontab
-
e
-
Add the following line to the crontab file to schedule a daily backup at 2 A.M.:
0
2
*
*
*
/
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
, andmysqldump
support compression. - Encryption
-
Encrypt the backup file to protect sensitive data. XtraBackup supports encryption via the
--encrypt
option.
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
xtrabackup
command, which was already detailed in “Percona XtraBackup”. -
Create a binary log backup of the database by using the
mysqlbinlog
command. 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 LOGS
statement to see the current names if you are not sure what they are:mysql
>
SHOW
BINARY
LOGS
;
+
---------------+-----------+-----------+
|
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 --raw
binlog
.
000001
binlog
.
000002
binlog
.
000003
mysqlbinlog
--read-from-remote-server --host=host_name --raw
--to-last-log binlog.000001
The first command explicitly lists every filename, while the second names only the first file and uses
--to-last-log
to read through the last. One difference between these commands is that if the server opens binlog.000133 beforemysqlbinlog
reaches the end of binlog.000003, the first command does not read it, but the second command does.To create a live backup, where
mysqlbinlog
starts 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.000001
With
--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
-
u
root
-
p
mydatabase
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
>
use
adddb
;
Database
changed
mysql
>
CREATE
TABLE
`
authors
`
(
-
>
`
id
`
int
NOT
NULL
AUTO_INCREMENT
,
-
>
`
first_name
`
varchar
(
50
)
COLLATE
utf8mb3_unicode_ci
NOT
NULL
,
-
>
`
last_name
`
varchar
(
50
)
COLLATE
utf8mb3_unicode_ci
NOT
NULL
,
-
>
`
email
`
varchar
(
100
)
COLLATE
utf8mb3_unicode_ci
NOT
NULL
,
-
>
`
birthdate
`
date
NOT
NULL
,
-
>
`
added
`
timestamp
NOT
NULL
DEFAULT
CURRENT_TIMESTAMP
,
-
>
PRIMARY
KEY
(
`
id
`
)
-
>
)
ENGINE
=
InnoDB
AUTO_INCREMENT
=
72639469
DEFAULT
CHARSET
=
utf8mb4
COLLATE
=
utf8mb4_0900_ai_ci
;
Query
OK
,
0
rows
affected
,
3
warnings
(
0
.
03
sec
)
-
Discard the tablespace of the
authors
table:mysql
>
select
count
(
*
)
from
authors
;
+
----------+
|
count
(
*
)
|
+
----------+
|
0
|
+
----------+
1
row
in
set
(
0
.
01
sec
)
mysql
>
ALTER
TABLE
authors
DISCARD
TABLESPACE
;
Query
OK
,
0
rows
affected
(
0
.
01
sec
)
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
.
ibd
172
.
31
.
84
.
219
:
/
var
/
lib
/
mysql
/
adddb
/
authors
.
ibd
100
%
544
KB
49
.
3
MB
/
s
00
:
00
root
@
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
mysql
as the new owners of the file:[
root
@
mysql80
~
]
#
chown
mysql
.
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 theauthors
table within theadddb
database. This command allows you to restore or replace the tablespace of theauthors
table 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 theauthors
table 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
@
mysql80
mysql
]
#
mysql
Welcome
to
the
MySQL
monitor
.
Commands
end
with
;
or
\
g
.
mysql
>
mysql
>
ALTER
TABLE
adddb
.
authors
IMPORT
TABLESPACE
;
Query
OK
,
0
rows
affected
,
1
warning
(
0
.
05
sec
)
mysql
>
-
Following the import of the tablespace for the
authors
table, verify the data in the table:select
count
(
id
)
from
adddb
.
authors
;
mysql
>
select
count
(
id
)
from
adddb
.
authors
;
+
-----------+
|
count
(
id
)
|
+
-----------+
|
6000
|
+
-----------+
1
row
in
set
(
0
.
03
sec
)
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:
sudo
cp
or
scp
mysql
-
bin
.
*
/
data
/
sudo
chown
mysql
.
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
.
index
With
IMPORT TABLESPACE
, you have already imported the table from the existing backup. -
Configure this table as a dummy replica:
mysql
>
CHANGE
REPLICATION
SOURCE
TO
RELAY_LOG_FILE
=
'mysql-bin.000001'
,
RELAY_LOG_POS
=
4
,
SOURCE_HOST
=
'dummy'
;
Query
OK
,
0
rows
affected
(
0
.
08
sec
)
mysql
>
CHANGE
REPLICATION
FILTER
REPLICATE_DO_TABLE
=
(
adddb
.
authors
)
;
Query
OK
,
0
rows
affected
(
0
.
00
sec
)
-
Validate the output and start the replication thread (only the SQL thread is needed):
mysql
>
START
REPLICA
SQL_THREAD
;
Query
OK
,
0
rows
affected
(
0
.
01
sec
)
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_day
s-
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
,MINIMAL
andNOBLOB
. 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:
PURGE
BINARY
LOGS
TO
'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
-
u
root
-
p
-
List the binary logfiles currently in use:
mysql
>
SHOW
BINARY
LOGS
;
+
---------------+-----------+-----------+
|
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
|
+
---------------+-----------+-----------+
12
rows
in
set
(
0
.
01
sec
)
mysql
>
Along with the binary logfiles currently in use, this command will display their sizes and creation timestamps.
-
Use the
PURGE BINARY LOGS
command to delete old binary logfiles. For example, to delete all binary logfiles older than seven days, use the following command:mysql
>
PURGE
BINARY
LOGS
BEFORE
DATE_SUB
(
NOW
(
)
,
INTERVAL
7
DAY
)
;
Query
OK
,
0
rows
affected
(
0
.
02
sec
)
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 LOGS
command again:mysql
>
SHOW
BINARY
LOGS
;
+
---------------+-----------+-----------+
|
Log_name
|
File_size
|
Encrypted
|
+
---------------+-----------+-----------+
|
binlog
.
000013
|
201
|
No
|
|
binlog
.
000014
|
1402
|
No
|
+
---------------+-----------+-----------+
2
rows
in
set
(
0
.
00
sec
)
Now you’ll see the binary logfiles currently in use, excluding any files that were deleted by the
PURGE BINARY LOGS
command.
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
mysqldump
for small databases -
For small databases,
mysqldump
is 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:
aws
s3
cp
/
backup
/
full
/
backup
-
to
-
image
s3
:
/
/
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:
aws
s3
cp
/
backup
/
full
/
backup
-
to
-
image
s3
:
/
/
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
:
~
#
sudo
dpkg
-
i
percona
-
release_latest
.
generic_all
.
deb
Selecting
previously
unselected
package
percona
-
release
.
<
*
>
All
done
!
*
Enabling
the
Percona
Release
repository
<
*
>
All
done
!
For
example
,
to
enable
the
Percona
Server
8
.
0
repository
use
:
percona
-
release
setup
ps80
Note
:
To
avoid
conflicts
with
older
product
versions
,
the
percona
-
release
root
@
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
:
~
#
sudo
apt
-
get
install
percona
-
xtrabackup
-
80
Reading
package
lists
.
.
.
Done
Building
dependency
tree
.
.
.
Done
Reading
state
information
.
.
.
Done
The
following
additional
packages
will
be
installed
:
libcurl4
-
openssl
-
dev
libev4
Suggested
packages
:
libcurl4
-
doc
libidn11
-
dev
libkrb5
-
dev
libldap2
-
dev
librtmp
-
dev
libssh2
-
1
-
dev
libssl
-
dev
pkg
-
config
zlib1g
-
dev
The
following
NEW
packages
will
be
installed
:
libcurl4
-
openssl
-
dev
libev4
percona
-
xtrabackup
-
80
Processing
triggers
for
libc
-
bin
(
2
.
35
-
0
ubuntu3
.
6
)
.
.
.
Scanning
processes
.
.
.
Running
kernel
seems
to
be
up
-
to
-
date
.
Restarting
services
.
.
.
Service
restarts
being
deferred
:
systemctl
restart
systemd
-
logind
.
service
No
containers
need
to
be
restarted
.
No
user
sessions
are
running
outdated
binaries
.
No
VM
guests
are
running
outdated
hypervisor
(
qemu
)
binaries
on
this
host
.
root
@
MyUbuntuMysql8
:
~
#
Verify the version of the installed XtraBackup:
root
@
MyUbuntuMysql8
:
~
#
xtrabackup
--version
2024
-
02
-
20
T15
:
02
:
37
.
633932
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--innodb_directories=/var/lib/user_defined_general_tablespace
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
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-key
andxtrabackup
--encrypt-key-file
. Here’s an example command to generate an encryption key:root
@
MyUbuntuMysql8
:
~
#
openssl
rand
-
base64
24
P
/
Fu9cZo9gzkzpmtvUKFku3u4ONTrj5Z
root
@
MyUbuntuMysql8
:
~
#
Here’s an example of the XtraBackup command utilizing the
xtrabackup --encrypt-key
option:root
@
MyUbuntuMysql8
:
~
#
mkdir
/
root
/
backups
/
root
@
MyUbuntuMysql8
:
~
#
xtrabackup
--backup --target-dir=/root/backups/
--encrypt=AES256 --encrypt-key=""P/Fu9cZo9gzkzpmtvUKFku3u4ONTrj5Z""
2024
-
02
-
20
T15
:
04
:
59
.
723781
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--innodb_directories=/var/lib/
user_defined_general_tablespace
2024
-
02
-
20
T15
:
04
:
59
.
724228
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--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=*
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
240220
15
:
04
:
59
version_check
Connecting
to
MySQL
server
with
DSN
'dbi:mysql:; mysql_read_default_group=xtrabackup'
(
using
password
:
NO
)
.
240220
15
:
05
:
00
version_check
Connected
to
MySQL
server
240220
15
:
05
:
00
version_check
Executing
a
version
check
against
the
server
.
.
.
-
After completing the backup, check the log to confirm its successful completion. You should get a log similar to this:
2024
-
02
-
20
T15
:
05
:
03
.
706837
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Encrypting
/
root
/
backups
/
xtrabackup_info
.
xbcrypt
2024
-
02
-
20
T15
:
05
:
03
.
707047
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Done
:
Encrypting
file
/
root
/
backups
/
xtrabackup_info
.
xbcrypt
2024
-
02
-
20
T15
:
05
:
04
.
717140
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
Transaction
log
of
lsn
(
42914523
)
to
(
42914533
)
was
copied
.
2024
-
02
-
20
T15
:
05
:
04
.
826343
-
00
:
00
0
[
Note
]
[
MY
-
010733
]
[
Server
]
Shutting
down
plugin
'keyring_file'
2024
-
02
-
20
T15
:
05
:
04
.
828326
-
00
:
00
0
[
Note
]
[
MY
-
010733
]
[
Server
]
Shutting
down
plugin
'daemon_keyring_proxy_plugin'
2024
-
02
-
20
T15
:
05
:
04
.
831369
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
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
/
total
76232
-
rw
-
r
----- 1 root root 12600576 Feb 20 15:05 ibdata1.xbcrypt
drwxr
-
x
--- 2 root root 4096 Feb 20 15:05 sys
drwxr
-
x
--- 2 root root 4096 Feb 20 15:05 mydatabase
drwxr
-
x
--- 2 root root 4096 Feb 20 15:05 mytestschema
drwxr
-
x
--- 2 root root 4096 Feb 20 15:05 unencryptedschema
-
rw
-
r
----- 1 root root 114872 Feb 20 15:05
user_defined_general_tablespace
.
ibd
.
xbcrypt
-
rw
-
r
----- 1 root root 114872 Feb 20 15:05
user_defined_general_tablespace_1
.
ibd
.
xbcrypt
drwxr
-
x
--- 2 root root 4096 Feb 20 15:05 encryptedschema
drwxr
-
x
--- 2 root root 4096 Feb 20 15:05 mytest
drwxr
-
x
--- 2 root root 4096 Feb 20 15:05 mytestdb
drwxr
-
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.xbcrypt
drwxr
-
x
--- 2 root root 12288 Feb 20 15:05 performance_schema
drwxr
-
x
--- 2 root root 4096 Feb 20 15:05 test
drwxr
-
x
--- 2 root root 4096 Feb 20 15:05 mytestdb_inno
drwxr
-
x
--- 2 root root 4096 Feb 20 15:05 mysql
drwxr
-
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.xbcrypt
root
@
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-original
2024
-
02
-
20
T15
:
12
:
08
.
321590
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--innodb_directories=/var/lib/user_defined_general_tablespace
2024
-
02
-
20
T15
:
12
:
08
.
322065
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--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=1
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
2024
-
02
-
20
T15
:
12
:
08
.
354574
-
00
:
00
0
[
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
-
20
T15
:
12
:
09
.
727703
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
decrypting
.
/
xtrabackup_binlog_info
.
xbcrypt
2024
-
02
-
20
T15
:
12
:
09
.
731566
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
removing
.
/
xtrabackup_binlog_info
.
xbcrypt
2024
-
02
-
20
T15
:
12
:
09
.
733903
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
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
-
20
T15
:
15
:
10
.
126368
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
server
arguments
:
--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=0
2024
-
02
-
20
T15
:
15
:
10
.
126706
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
recognized
client
arguments
:
--prepare=1 --target-dir=/root/backups/
xtrabackup
version
8
.
0
.
35
-
30
based
on
MySQL
server
8
.
0
.
35
Linux
(
x86_64
)
(
revision
id
:
6
beb4b49
)
2024
-
02
-
20
T15
:
15
:
10
.
127029
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
cd
to
/
root
/
backups
/
2024
-
02
-
20
T15
:
15
:
10
.
127237
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
This
target
seems
to
be
not
prepared
yet
.
After completing the preparation process, review the log to confirm its successful execution:
2024
-
02
-
20
T15
:
15
:
12
.
162295
-
00
:
00
0
[
Note
]
[
MY
-
012980
]
[
InnoDB
]
Shutdown
completed
;
log
sequence
number
42914838
2024
-
02
-
20
T15
:
15
:
12
.
166062
-
00
:
00
0
[
Note
]
[
MY
-
010733
]
[
Server
]
Shutting
down
plugin
'keyring_file'
2024
-
02
-
20
T15
:
15
:
12
.
166261
-
00
:
00
0
[
Note
]
[
MY
-
010733
]
[
Server
]
Shutting
down
plugin
'daemon_keyring_proxy_plugin'
2024
-
02
-
20
T15
:
15
:
12
.
166814
-
00
:
00
0
[
Note
]
[
MY
-
011825
]
[
Xtrabackup
]
completed
OK
!
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
-
u
root
-
p
mytestdb
>
backup
.
sql
Enter
password
:
root
@
MyUbuntuMysql8
:
~
/
backups
#
ls
-
ltr
total
8
-
rw
-
r
--r-- 1 root root 5714 Feb 20 15:22 backup.sql
root
@
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
#
openssl
aes
-
256
-
cbc
-
salt
-
in
backup
.
sql
-
out
backup
.
sql
.
enc
enter
AES
-
256
-
CBC
encryption
password
:
Verifying
-
enter
AES
-
256
-
CBC
encryption
password
:
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
#
openssl
aes
-
256
-
cbc
-
d
-
in
backup
.
sql
.
enc
-
out
backup
.
sql
enter
AES
-
256
-
CBC
decryption
password
:
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.