In one of the previous blog posts we have discussed what are the types of backup that you can use with MySQL. We have taken a look at the different backup methods. What we haven’t done is to show how to backup MySQL. This blog will focus solely on the backup process. Again, we are going to keep it simple, we will not discuss point-in-time recovery here nor security considerations around backups. Those are great topics that we will cover later. As per the previous blog, we have two main types of backup – a logical backup and a physical one. We are going to give you suggestions on how to run both logical and physical backups using the most common, universal tools – mysqldump and XtraBackup. The main advantage of them is that they are available freely and can be used with any MySQL deployment. They are infrastructure-agnostic therefore they can be used at any time in any setup, no matter where the database is located, what kind of hardware is used and so on. Let’s take a quick look at those tools, we will start with logical backup but before we do that, we will talk a little bit about the very important aspect of backups – consistency.
Why do we want backup to be consistent?
We have touched on this subject in the previous blog but let’s reiterate it here as well – this is a very important concept that we have to understand.
Backups can be very different – we can create a backup of a subset of the data – one or more tables or schemas. We can create a backup of all data but we can do it in a way that every schema or table is backed up separately. All of this leads to the situation in which backup is not consistent to a single point in time. What it means is that different tables will contain data up to different points in time:
| Tab_a | 2022-01-25T23:51:04+01:00 |
| Tab_b | 2022-01-25T23:51:23+01:00 |
| Tab_c | 2022-01-25T23:51:33+01:00 |
| Tab_d | 2022-01-25T23:51:47+01:00 |
Even though you have backed up all tables in the database, they contain different data. Table ‘Tab_d’ contains data up to 25th of January 2022, 23:51:45 while Tab_b contains data only up to 23:51:23 so if those tables are somehow related and there was an INSERT to Tab_d and, let’s say relevant update to Tab_b at 23:51:39 then after the restore, this data will be visible in Tab_d but not in the Tab_b. As a result data is corrupted and inconsistent – we don’t have to explain why it is a serious issue. This is why we want data to be consistent across all tables. Such backups can be used to set up new replicas, to rebuild old ones or to perform a Point-In-Time recovery. With that established, let’s take a look at the examples.
How to backup MySQL using mysqldump?
We are going to start with mysqldump. As we mentioned in the earlier blog post, mysqldump is quite flexible and has numerous options to pick from. If we want to create a consistent backup, there are a couple of settings that are mandatory to us.
root@vagrant:~# mysqldump -u root -p -h 127.0.0.1 --all-databases --dump-replica=2 --single-transaction --routines --triggers --events > database.sql Enter password:First of all, this has been executed on the replica. For a source node you want to skip
–dump-replicaflag.Those are the settings we have used:
--all-databases– we are going to backup all databases, not a subset of them
--dump-replica=2– we want to store the information about the binary log file and position in the backup file. With setting of 2, the CHANGE MASTER command will be commented so it will not be executed if we’d like to use this backup to restore non-replicas
--single-transaction– the backup will be taken in a single transaction, ensuring that the data from different tables will be consistent
--routines– we are going to include stored procedures’ and functions’ definitions in the backup file
--triggers– we are going to include triggers’ definitions in the backup file
--events– we are going to include events’ definitions in the backup fileThose settings are enough to create a consistent backup using mysqldump.
How to backup MySQL using XtraBackup?
Mysqldump is not really suitable for larger data sets as the time needed to create a backup and, later, restore it, will increase with the size of the data. This is why, usually, another backup method is used for regular backups. XtraBackup is one of the most popular backup tools for MySQL thanks to the relatively ease of use and great flexibility. You can take a partial backup, you can compress the backup in a couple of ways, you can stream the backup to another host or store it locally. We are going to show the most basic way of backing up a MySQL database using Xtrabackup – we will create a compressed backup locally, on the database node. Then we will show how to create an incremental backup on top of existing full backup.
Preparations
First of all, XtraBackup requires a MySQL user to be created with proper privileges. Let’s do it then:
mysql> CREATE USER xtrabackup@127.0.0.1 IDENTIFIED BY 'backupp4ss'; Query OK, 0 rows affected (0.19 sec) mysql> CREATE USER xtrabackup@localhost IDENTIFIED BY 'backupp4ss'; Query OK, 0 rows affected (0.01 sec) mysql> GRANT SELECT, INSERT, CREATE, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, CREATE TABLESPACE, BACKUP_ADMIN ON *.* TO xtrabackup@127.0.0.1; Query OK, 0 rows affected, 1 warning (0.01 sec) mysql> GRANT SELECT, INSERT, CREATE, RELOAD, PROCESS, SUPER, LOCK TABLES, REPLICATION CLIENT, SHOW VIEW, EVENT, CREATE TABLESPACE, BACKUP_ADMIN ON *.* TO xtrabackup@localhost; Query OK, 0 rows affected, 2 warnings (0.03 sec)Once the user has been created, we will set up the credentials in my.cnf. It is possible to pass them directly to xtrabackup command using –user and –password flags but it is not necessarily the best practice, security-wise. Instead we can add [xtrabackup] section to the my.cnf and store the credentials there:
[xtrabackup] user=xtrabackup password=backupp4ssThen, make sure that my.cnf can be accessed only by user used to run MySQL:
root@vagrant:~# chown mysql.root /etc/mysql/my.cnf root@vagrant:~# ls -alh /etc/mysql/my.cnf -rw------- 1 mysql root 3.3K Jan 27 11:23 /etc/mysql/my.cnfThis should minimize the chance that the password will be accessed by someone.
How to create a full backup using XtraBackup?
From there we can create a backup. We wanted it to be compressed and we have two options here. We can either use qpress, which can be natively used by XtraBackup or we can use external solutions like, for example, gzip. In this particular case we will go with external compression – it will allow us to show you how to pipe the XtraBackup output to external programs for further processing.
root@vagrant:~# xtrabackup --backup --stream=xbstream --target-dir=/backup/1/ | gzip > /backup/1/backup.gz xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --innodb_buffer_pool_size=182M --innodb_flush_log_at_trx_commit=2 --innodb_file_per_table=1 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_log_file_size=64M --innodb_log_buffer_size=16M --innodb_log_files_in_group=2 --innodb_flush_method=O_DIRECT --server-id=8001 --log_bin=binlog xtrabackup: recognized client arguments: --socket=/var/lib/mysql/mysql.sock --user=xtrabackup --password=* --backup=1 --stream=xbstream --target-dir=/backup/1/ xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82) 220127 13:26:45 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'xtrabackup' (using password: YES). 220127 13:26:45 version_check Connected to MySQL server 220127 13:26:45 version_check Executing a version check against the server... 220127 13:26:45 version_check Done. 220127 13:26:45 Connecting to MySQL server host: localhost, user: xtrabackup, password: set, port: not set, socket: /var/lib/mysql/mysql.sock Using server version 8.0.26-17We have used the
--streamoption which ignores target directory and instead sends the data in xbstream format to standard output. This allows us to pipe the output to gzip and compress it this way. If we want, we can extend this and pipe it further to, for example, netcat and stream it like that to a separate host.At some point you should see that the backup has been completed OK (assuming nothing unexpected happened in the meantime):
220127 13:38:09 Backup created in directory '/backup/1/' MySQL binlog position: filename 'binlog.000008', position '43659', GTID of the last change '8ca1ede1-7f63-11ec-bf30-080027b1285d:1-1139172' 220127 13:38:09 [00] Streaming <STDOUT> 220127 13:38:09 [00] ...done 220127 13:38:09 [00] Streaming <STDOUT> 220127 13:38:09 [00] ...done xtrabackup: Transaction log of lsn (11361452244) to (11623974007) was copied. 220127 13:38:10 completed OK!This is pretty much it – we have created a full backup using XtraBackup.
How to create an incremental backup using XtraBackup?
Incremental backup in XtraBackup uses the information about the last executed transaction in the previous backup and then only copies the changes that happened since that transaction. Before we can run an incremental backup, we have to take a full backup that will serve as a base for future incrementals. Whole data from the full backup is not really needed to create incremental backups – we need a particular file: xtrabackup_checkpoints – it contains information about the last transaction included in the backup. Of course, if we want to restore the incremental backup, we will need all the data but that’s another story for another blog post.
In our case, we can either decompress the backup that we have just created and find the file we need or we can create another full backup, this time using –extra-lsndir, which will create a copy of the xtrabackup_checkpoints file in the directory we pick.
root@vagrant:~# xtrabackup --backup --stream=xbstream --target-dir=/backup/2/ --extra-lsndir=/backup/2/ | gzip > /backup/2/backup.gz xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --innodb_buffer_pool_size=182M --innodb_flush_log_at_trx_commit=2 --innodb_file_per_table=1 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_log_file_size=64M --innodb_log_buffer_size=16M --innodb_log_files_in_group=2 --innodb_flush_method=O_DIRECT --server-id=8001 --log_bin=binlog xtrabackup: recognized client arguments: --socket=/var/lib/mysql/mysql.sock --user=xtrabackup --password=* --backup=1 --stream=xbstream --target-dir=/backup/2/ --extra-lsndir=/backup/2/ xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82) 220127 23:14:42 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'xtrabackup' (using password: YES). 220127 23:14:42 version_check Connected to MySQL server 220127 23:14:43 version_check Executing a version check against the server... 220127 23:14:43 version_check Done. 220127 23:14:43 Connecting to MySQL server host: localhost, user: xtrabackup, password: set, port: not set, socket: /var/lib/mysql/mysql.sock Using server version 8.0.26-17Once the backup has completed, we will see:
root@vagrant:~# ls -alh /backup/2/ total 3.6G drwxr-xr-x 2 root root 4.0K Jan 27 15:03 . drwxr-xr-x 5 root root 4.0K Jan 27 23:13 .. -rw-r--r-- 1 root root 3.6G Jan 27 15:03 backup.gz -rw-r--r-- 1 root root 111 Jan 27 15:03 xtrabackup_checkpoints -rw-r--r-- 1 root root 578 Jan 27 15:03 xtrabackup_infoWe can now use this directory as a source directory for our incremental backups.
root@vagrant:~# xtrabackup --backup --target-dir=/backup/3/ --incremental-basedir=/backup/2/ --extra-lsndir=/backup/3/ --stream=xbstream | gzip > /backup/3/backup_incremental.gz xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --innodb_buffer_pool_size=182M --innodb_flush_log_at_trx_commit=2 --innodb_file_per_table=1 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_log_file_size=64M --innodb_log_buffer_size=16M --innodb_log_files_in_group=2 --innodb_flush_method=O_DIRECT --server-id=8001 --log_bin=binlog xtrabackup: recognized client arguments: --socket=/var/lib/mysql/mysql.sock --user=xtrabackup --password=* --backup=1 --target-dir=/backup/3/ --incremental-basedir=/backup/2/ --extra-lsndir=/backup/3/ --stream=xbstream xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82) 220127 23:59:54 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'xtrabackup' (using password: YES). 220127 23:59:54 version_check Connected to MySQL server 220127 23:59:54 version_check Executing a version check against the server... 220127 23:59:54 version_check Done. 220127 23:59:54 Connecting to MySQL server host: localhost, user: xtrabackup, password: set, port: not set, socket: /var/lib/mysql/mysql.sock Using server version 8.0.26-17Once this is done, we will have our incremental backup along with xtrabackup_checkpoints file located in /backup/3/. If we would like to execute another incremental backup, we would point towards that directory as the incremental base directory:
root@vagrant:~# xtrabackup --backup --target-dir=/backup/4/ --incremental-basedir=/backup/3/ --extra-lsndir=/backup/4/ --stream=xbstream | gzip > /backup/4/backup_incremental.gz xtrabackup: recognized server arguments: --datadir=/var/lib/mysql --innodb_buffer_pool_size=182M --innodb_flush_log_at_trx_commit=2 --innodb_file_per_table=1 --innodb_data_file_path=ibdata1:100M:autoextend --innodb_read_io_threads=4 --innodb_write_io_threads=4 --innodb_log_file_size=64M --innodb_log_buffer_size=16M --innodb_log_files_in_group=2 --innodb_flush_method=O_DIRECT --server-id=8001 --log_bin=binlog xtrabackup: recognized client arguments: --socket=/var/lib/mysql/mysql.sock --user=xtrabackup --password=* --backup=1 --target-dir=/backup/4/ --incremental-basedir=/backup/3/ --extra-lsndir=/backup/4/ --stream=xbstream xtrabackup version 8.0.26-18 based on MySQL server 8.0.26 Linux (x86_64) (revision id: 4aecf82) 220128 00:11:39 version_check Connecting to MySQL server with DSN 'dbi:mysql:;mysql_read_default_group=xtrabackup;mysql_socket=/var/lib/mysql/mysql.sock' as 'xtrabackup' (using password: YES). 220128 00:11:39 version_check Connected to MySQL server 220128 00:11:39 version_check Executing a version check against the server... 220128 00:11:39 version_check Done. 220128 00:11:39 Connecting to MySQL server host: localhost, user: xtrabackup, password: set, port: not set, socket: /var/lib/mysql/mysql.sock Using server version 8.0.26-17This is pretty much it – every new incremental backup would have to be based on the latest incremental backup. At some point you will run another full backup and then it will become the base backup for further incremental backups.
We hope this short blog will give you some idea how to backup MySQL database using mysqldump and XtraBackup. Those two tools should be able to cover majority of the use cases and are universal enough to be used across vast range of environments.
