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.
