Hot Backup For MyRocks(Rocksdb) using Percona Xtrabackup

May 13, 2019

Mins to Read

All

Xtrabackup now supports Hotbackup for Myrocks!! yes you heard me right, this is one of the most awaited features with xtrabackup. With the latest release of percona xtrabackup 8.0.6 this is enabled and is supported only for Percona Server version 8.0.15-6 or higher, you can see detailed released notes here.

Myrocks is getting much of the attention now because of its much improved write capabilities and compression. We have also planned to have detailed blog on Myrocks features and limitations.

We shall proceed to test the backup and restore of Myrocks

Environment:

 
OS : Debian GNU/Linux 9 (stretch)
Cores : 14
RAM : 36G
HardDiskType : HDD
MySQL version : 8.0.15-6 Percona Server (GPL), Release '6', Revision '63abd08'
Xtrabackup version : 8.0.6

Below is the installed package both Percona MySQL & Xtrabackup

 
ii  libperconaserverclient21:amd64 8.0.15-6-1.stretch             amd64 Percona Server database client library
ii  percona-server-client          8.0.15-6-1.stretch amd64        Percona Server database client binaries
ii  percona-server-common          8.0.15-6-1.stretch amd64        Percona Server database common files (e.g. /etc/mysql/my.cnf)
ii  percona-server-rocksdb         8.0.15-6-1.stretch amd64        MyRocks storage engine plugin for Percona Server
ii  percona-server-server          8.0.15-6-1.stretch amd64        Percona Server database server binaries
ii  percona-xtrabackup-80          8.0.6-1.stretch amd64        Open source backup tool for InnoDB and XtraDB

With the above setup, I have used both the rocksDB and InnoDB on a single database, As below.

 
+---------+-------------------+--------------+-----------------+
| ENGINE  | TABLE_NAME        | TABLE_SCHEMA | Total size(GB)  |
+---------+-------------------+--------------+-----------------+
| ROCKSDB | customer          | erp          |  1.193671055138 |
| InnoDB  | customerAttribute | erp          | 16.391601562500 |
+---------+-------------------+--------------+-----------------+

Note: To make bulk loading on to myrocks, I have enabled the below session variables:

 
mysql> set rocksdb_bulk_load=ON;
mysql> set rocksdb_commit_in_the_middle=ON;
mysql> set sql_log_bin=0;

Row Count of Both the Tables(Before backup):

 
mysql> select count(*) from customer; (Myrocks)
+----------+
| count(*) |
+----------+
| 37605935 |
+----------+

mysql> select count(*) from customerAttribute;(innodb)
+-----------+
| count(*) |
+-----------+
| 126022626 |
+-----------+

MyRocks Disk Files:

Myrocks Datadir on disk ie “.rocksdb” contains two key files

WAL files(redo)
WAL(Write ahead log) log file (redologs), these logs would be automatically removed when its data is fully synced to data files ie., SST files
SST files
SST files are the actual data files, SST files are where the compaction happens at different levels (L0….Ln)

To visualise better on the disk files of Myrocks, am just sharing image by Percona CTO, Vadim Tkachenko

Full Backup:

Below is the Xtrabackup command used to take a full backup.

 
:~# xtrabackup --target-dir=/mysqlbackup/backup/ --backup

Now lets see some logs:

Backup Of InnoDB Tables:

As the tables of different engine co-exists, Xtrabackup completes the backup of InnoDB engine tables first as below

 
190512 18:00:29 >> log scanned up to (22897434392)
xtrabackup: Generating a list of tablespaces
Directories to scan '/var/lib/mysql/;./;.'
Scanning '/var/lib/mysql/'
Completed space ID check of 2 files.
Allocated tablespace ID 2 for erp/customerAttribute, old maximum was 0
Using undo tablespace '/var/lib/mysql/undo_001'.
Using undo tablespace '/var/lib/mysql/undo_002'.
Opened 2 existing undo tablespaces.
190512 18:00:29 [01] Copying /var/lib/mysql/ibdata1 to /mysqlbackup/backup/ibdata1
190512 18:00:29 [01]        ...done
190512 18:00:29 [01] Copying /var/lib/mysql/sys/sys_config.ibd to /mysqlbackup/backup/sys/sys_config.ibd
190512 18:00:29 [01]        ...done
190512 18:00:29 [01] Copying /var/lib/mysql/erp/customerAttribute.ibd to /mysqlbackup/backup/erp/customerAttribute.ibd
190512 18:00:30 >> log scanned up to (22897434392)
190512 18:00:31 >> log scanned up to (22897434392)
:
90512 18:02:31 >> log scanned up to (22897434392)
190512 18:02:31 [01]        ...done
190512 18:02:31 [01] Copying /var/lib/mysql/mysql.ibd to /mysqlbackup/backup/mysql.ibd
190512 18:02:31 [01]        ...done
190512 18:02:31 [01] Copying ./undo_002 to /mysqlbackup/backup/undo_002
190512 18:02:31 [01]        ...done
190512 18:02:31 [01] Copying ./undo_001 to /mysqlbackup/backup/undo_001
190512 18:02:31 [01]        ...done

Backup of Non-InnoDB system Tables:

Once done with the InnoDB tables, now its proceeds for copying the system tables of various engine type as below, Along the metadata file of Myrocks table “/mysqlbackup/backup/erp/customer_365.sdi”

 
190512 18:02:32 Executing LOCK TABLES FOR BACKUP...
190512 18:02:32 Starting to backup non-InnoDB tables and files
190512 18:02:32 [01] Copying mysql/general_log_209.sdi to /mysqlbackup/backup/mysql/general_log_209.sdi
190512 18:02:32 [01] ...done
190512 18:02:32 [01] Copying mysql/general_log.CSM to /mysqlbackup/backup/mysql/general_log.CSM
190512 18:02:32 [01] ...done
190512 18:02:32 [01] Copying mysql/general_log.CSV to /mysqlbackup/backup/mysql/general_log.CSV
190512 18:02:32 [01] ...done
190512 18:02:32 [01] Copying mysql/slow_log_210.sdi to /mysqlbackup/backup/mysql/slow_log_210.sdi
190512 18:02:32 [01] ...done
:
:
190512 18:02:32 [01] Copying erp/customer_365.sdi to /mysqlbackup/backup/erp/customer_365.sdi
190512 18:02:32 [01] ...done
190512 18:02:32 Finished backing up non-InnoDB tables and files

Data copy for Myrocks :

Now lets see how Myrocks engine tables are backed up,

Xtrabackup first creates a “Checkpoint”(LSN) for RocksDB to make a consistent copy of data, here its LSN=”22897434402”
Then it proceeds to copy the WAL logs, with respect to the checkpoint LSN “22897434402”
Then it starts copying the SST file, with respect to the checkpoint.
Finally it removes the check-point of rocksDB and marks the constant binlog pos along with it

 
190512 18:02:32 xtrabackup: Creating RocksDB checkpoint
190512 18:02:33 >> log scanned up to (22897434402)
190512 18:02:34 >> log scanned up to (22897434402)
190512 18:02:35 >> log scanned up to (22897434402)
190512 18:02:36 >> log scanned up to (22897434402)
190512 18:02:36 Executing FLUSH NO_WRITE_TO_BINLOG BINARY LOGS
190512 18:02:36 Selecting LSN and binary log position from p_s.log_status
190512 18:02:36 [00] Copying /var/log/mysql/ekl-logis-archiva-325561-binary.000011 to /mysqlbackup/backup/ekl-logis-archiva-325561-binary.000011 up to position 155
190512 18:02:36 [00]        ...done
190512 18:02:36 [00] Writing /mysqlbackup/backup/ekl-logis-archiva-325561-binary.index
190512 18:02:36 [00]        ...done
190512 18:02:36 [00] Copying ./.rocksdb/000364.log to /mysqlbackup/backup/.rocksdb/000364.log up to position 5056023
190512 18:02:36 [00]        ...done
190512 18:02:36 [00] Copying ./.rocksdb/000367.log to /mysqlbackup/backup/.rocksdb/000367.log up to position 435553
190512 18:02:36 [00]        ...done
:

 
190512 18:03:11 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
xtrabackup: The latest check point (for incremental): '22897434402'
xtrabackup: Stopping log copying thread at LSN 22897434402.
.190512 18:03:11 >> log scanned up to (22897434402)
190512 18:03:11 >> log scanned up to (22897434402)

190512 18:03:11 Executing UNLOCK TABLES
190512 18:03:11 All tables unlocked
190512 18:03:11 [00] Copying ib_buffer_pool to /mysqlbackup/backup/ib_buffer_pool
190512 18:03:11 [00]        ...done
190512 18:03:11 [00] Copying .xtrabackup_rocksdb_checkpoint_1557664352626/000409.sst to /mysqlbackup/backup/.rocksdb/000409.sst
190512 18:03:12 [00]        ...done
:

 
190512 18:03:22 [00] Copying .xtrabackup_rocksdb_checkpoint_1557664352626/CURRENT to /mysqlbackup/backup/.rocksdb/CURRENT
190512 18:03:22 [00]        ...done
190512 18:03:22 xtrabackup: Removing RocksDB checkpoint
190512 18:03:22 Backup created in directory '/mysqlbackup/backup/'
MySQL binlog position: filename 'erp-mydbops-archiva-325561-binary.000011', position '155'
190512 18:03:22 [00] Writing /mysqlbackup/backup/backup-my.cnf
190512 18:03:22 [00]        ...done
190512 18:03:22 [00] Writing /mysqlbackup/backup/xtrabackup_info
190512 18:03:22 [00]        ...done
xtrabackup: Transaction log of lsn (22897434392) to (22897434402) was copied.
190512 18:03:22 completed OK!

Preparing Backup:

Now let’s proceed to prepare the backup, Which runs crash recovery process to make the backup consistent to the scanned LSN (22897434402)

Below is the command I have used for the same

 
# xtrabackup --target-dir=/mysqlbackup/backup --use-memory=1G --prepare

Am not proving the entire, Am just highlighting some major tasks

 
xtrabackup: Starting InnoDB instance for recovery.
xtrabackup: Using 1073741824 bytes for buffer pool (set by --use-memory parameter)
:
:
The log sequence number 19338764 in the system tablespace does not match the log sequence number 22897434392 in the ib_logfiles!
Database was not shutdown normally!
Starting crash recovery.
Starting to parse redo log at lsn = 22897434182, whereas checkpoint_lsn = 22897434392
Doing recovery: scanned up to log sequence number 22897434402
Log background threads are being started...
Applying a batch of 1 redo log records ...
100%
Apply batch completed!
xtrabackup: Last MySQL binlog file position 847444434, file name erp-mydbops-archiva-325561-binary.000010
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.

xtrabackup: Last MySQL binlog file position 847444434, file name erp-mydbops-archiva-325561-binary.000010
:
xtrabackup: starting shutdown with innodb_fast_shutdown = 1
FTS optimize thread exiting.
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 22897434402
Number of pools: 1
xtrabackup: using the following InnoDB configuration for recovery:
xtrabackup:   innodb_data_home_dir = .
xtrabackup:   innodb_data_file_path = ibdata1:12M:autoextend
xtrabackup:   innodb_log_group_home_dir = .
xtrabackup:   innodb_log_files_in_group = 2
xtrabackup:   innodb_log_file_size = 1073741824
:
:
Completed space ID check of 5 files.
Initializing buffer pool, total size = 1.000000G, instances = 1, chunk size =128.000000M 
Completed initialization of buffer pool
page_cleaner coordinator priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
page_cleaner worker priority: -20
Setting log file ./ib_logfile101 size to 16777216 MB
Progress in MB:
 100 200 300 400 500 600 700 800 900 1000
Setting log file ./ib_logfile1 size to 16777216 MB
Progress in MB:
 100 200 300 400 500 600 700 800 900 1000
Renaming log file ./ib_logfile101 to ./ib_logfile0
New log files created, LSN=22897434636
Log background threads are being started...
Applying a batch of 0 redo log records ...
Apply batch completed!
Using undo tablespace './undo_001'.
Using undo tablespace './undo_002'.
Opened 2 existing undo tablespaces.
Removed temporary tablespace data file: "ibtmp1"
:
:
Starting shutdown...
Log background threads are being closed...
Shutdown completed; log sequence number 22897434636
190512 18:09:28 completed OK!

Backup preparing has been completed.And is now ready to restore.

Backup DIR and its contents

Now let’s have a look at the backup directory and its contents:

Am using -a option ls command , since with backupdir datafile in myrocks are stored hidden under “.rocksdb”

 
drwxr-xr-x 4 root root 103 May 10 16:27 ..
drwxr-x--- 2 root root 28 May 12 18:00 sys
-rw-r----- 1 root root 24M May 12 18:02 mysql.ibd
-rw-r----- 1 root root 10M May 12 18:02 undo_002
-rw-r----- 1 root root 10M May 12 18:02 undo_001
drwxr-x--- 2 root root 143 May 12 18:02 mysql
drwxr-x--- 2 root root 8.0K May 12 18:02 performance_schema
drwxr-x--- 2 root root 59 May 12 18:02 erp
-rw-r----- 1 root root 54 May 12 18:02 erp-mydbops-archiva-325561-binary.index
-rw-r----- 1 root root 155 May 12 18:02 erp-mydbops-archiva-325561-binary.000011
-rw-r----- 1 root root 43 May 12 18:03 xtrabackup_binlog_info
-rw-r----- 1 root root 10K May 12 18:03 ib_buffer_pool
drwxr-x--- 2 root root 4.0K May 12 18:03 .rocksdb
-rw-r----- 1 root root 481 May 12 18:03 xtrabackup_info
-rw-r----- 1 root root 485 May 12 18:03 backup-my.cnf
-rw-r--r-- 1 root root 1 May 12 18:09 xtrabackup_master_key_id
-rw-r--r-- 1 root root 49 May 12 18:09 xtrabackup_binlog_pos_innodb
-rw-r----- 1 root root 8.0M May 12 18:09 xtrabackup_logfile
-rw-r----- 1 root root 101 May 12 18:09 xtrabackup_checkpoints
-rw-r----- 1 root root 1.0G May 12 18:09 ib_logfile1
-rw-r----- 1 root root 1.0G May 12 18:09 ib_logfile0
drwxr-xr-x 8 root root 4.0K May 12 18:09 .
-rw-r----- 1 root root 12M May 12 18:09 ibtmp1
-rw-r----- 1 root root 12M May 12 18:09 ibdata1
drwxr-x--- 2 root root 6 May 12 18:09 #innodb_temp
-rw-r----- 1 root root 248 May 12 18:09 xtrabackup_tablespaces

Backup Restore:

Now the backup has been prepared and ready to restore, so I will proceed to stop mysql server, remove contents of datadir & restore this backup.

Stopping server:

 
# service mysql stop
# service mysql status (to check status)

Remove datadir:

While removing the content don’t forget to remove the hidden “.rocksdb” folder as below or it might cause conflicts while restoring.

 
# rm -rf /var/lib/mysql/*
# rm -rf /var/lib/mysql/.rocksdb

Restore:

Am using the option –copy-back to copy the content of backupdir to datadir, you can also use –move-back as well, to make a manual copy using #CP command.

 
# xtrabackup --target-dir=/mysqlbackup/backup --copy-back

Logs:

 
xtrabackup: recognized client arguments: --target-dir=/mysqlbackup/backup --copy-back=1 
xtrabackup version 8.0.6 based on MySQL server 8.0.14 Linux (x86_64) (revision id: c0a2d91)
190512 18:14:40 [01] Copying undo_001 to /var/lib/mysql/undo_001
190512 18:14:40 [01]        ...done
190512 18:14:40 [01] Copying undo_002 to /var/lib/mysql/undo_002
190512 18:14:40 [01]        ...done
190512 18:14:40 [01] Copying ib_logfile0 to /var/lib/mysql/ib_logfile0
190512 18:14:46 [01]        ...done
:
:
190512 18:17:05 [00] Copying .rocksdb/000379.sst to /var/lib/mysql//.rocksdb/000379.sst
190512 18:17:05 [00]        ...done
190512 18:17:05 [00] Copying .rocksdb/MANIFEST-000371 to /var/lib/mysql//.rocksdb/MANIFEST-000371
190512 18:17:05 [00]        ...done
190512 18:17:05 [00] Copying .rocksdb/OPTIONS-000380 to /var/lib/mysql//.rocksdb/OPTIONS-000380
190512 18:17:05 [00]        ...done
190512 18:17:05 [00] Copying .rocksdb/CURRENT to /var/lib/mysql//.rocksdb/CURRENT
190512 18:17:05 [00]        ...done
190512 18:17:05 completed OK!

Now proceeding to start the server as below:

 
# service mysql start

After restoring am just proceeding to check the count for both Rocks and Innodb table as below to verify the counts before and after

 
Uptime: 12 sec

Threads: 2  Questions: 9  Slow queries: 0  Opens: 135  Flush tables: 2  Open tables: 111  Queries per second avg: 0.750
-----------------------

mysql> select count(*) from customerAttribute;(Innodb)
+-----------+
| count(*)  |
+-----------+
| 126022626 |
+-----------+
1 row in set (55.14 sec)

 
mysql> select count(*) from customer;(Rocksdb)

+----------+
| count(*) |
+----------+
| 37605935 |
+----------+
1 row in set (3 min 30.95 sec)

On a concurrent server with incoming writes for the Myrocks, it makes check-point to the LSN and make the backup consistent, till that checkpoint.

In this blog we have just seen basic full backup of Myrocks in a hot online server using Xtrabackup, preparing the same and restoring it back. What I have not covered here is Incremental backup & restore of the same.

Thanks to Percona Team, for this most awaited feature. This will definitely boost the adaptability of Myrocks to next level.We will also continue to explore more.

No items found.

About the Author

Mydbops

Hot Backup For MyRocks(Rocksdb) using Percona Xtrabackup

Environment:

Row Count of Both the Tables(Before backup):

MyRocks Disk Files:

Full Backup:

Backup Of InnoDB Tables:

Backup of Non-InnoDB system Tables:

Data copy for Myrocks :

Preparing Backup:

Backup DIR and its contents

Backup Restore:

About the Author

Topic covered

Continue Reading

MongoDB Shutdown Methods: db.shutdownServer() vs systemctl stop

PostgreSQL 17: Convert Streaming Replica to Logical Replication with pg_createsubscriber

Evolution of Histograms in MySQL 8.4: From Manual to Automated Performance Optimization

Mastering Null Handling in MongoDB

Subscribe Now!