[MCOL-931] Partition Backup / Restore Created: 2017-09-20  Updated: 2023-10-26  Resolved: 2021-01-17

Status: Closed
Project: MariaDB ColumnStore
Component/s: None
Affects Version/s: None
Fix Version/s: N/A

Type: New Feature Priority: Major
Reporter: hogeun cheong Assignee: Todd Stoffel (Inactive)
Resolution: Won't Do Votes: 1
Labels: Backup

Epic Link: ColumnStore Partitioning

 Description   

ColumnStore does not support partition backup and restore.
ColumnStore guides only full data backup except mysqldump.
I don't want to use mysqldump because of speed and management.

After several attempts, I found out how to back up and restore specific partitions.
Please confirm that the method below is correct.

▶ Informations.

  • DB : test
  • Table : security_tbl
  • Backup Partition : 0

1. backup
1) Check the partition table information to be backed up.

MariaDB [(none)]> select calshowpartitions('test', 'security_tbl', 'stime');

calshowpartitions('test', 'security_tbl', 'stime')
Part# Min Max Status
0.0.1 2017-03-21 16:09:08 2017-03-27 16:27:44 Enabled
0.1.1 2017-03-22 04:14:02 2017-03-24 16:45:44 Enabled
0.2.1 2017-03-22 16:20:23 2017-03-24 21:40:07 Enabled
0.3.1 2017-03-23 04:26:43 2017-03-23 16:33:03 Enabled
1.0.1 2017-03-21 16:09:08 2017-03-24 04:40:48 Enabled
1.1.1 2017-03-22 04:15:27 2017-03-24 16:47:09 Enabled
1.2.1 2017-03-22 16:21:48 2017-03-24 21:40:07 Enabled
1.3.1 2017-03-23 04:28:08 2017-03-23 16:34:29 Enabled

2) make a script that backs up the* object_id files and the dictionary_object_id files* from 0th partition.
>> mcsmysql -e "set global group_concat_max_len=1024000;"
>> mcsmysql -e "select concat('tar cvf security_0.tar ', group_concat(distinct left(b.filename,length(b.filename)-11) separator ' ')) from information_schema.columnstore_columns a join information_schema.columnstore_files b on a.object_id = b.object_id or a.DICTIONARY_OBJECT_ID = b.object_id where a.table_schema = 'test' and a.table_name = 'security_tbl' and b.partition_id = '0';" -s > backup_security_0.sh

>> ./backup_security_0.sh

3) Make a script that back up the below files.
- table schema file(frm)
- BRM files (/usr/local/mariadb/columnstore/data1/systemFiles)
- Table and column info files (I'm not sure)
> /usr/local/mariadb/columnstore/data1/000.dir/000.dir/003.dir
> /usr/local/mariadb/columnstore/data1/000.dir/000.dir/004.dir
> /usr/local/mariadb/columnstore/data1/000.dir/000.dir/007.dir
> /usr/local/mariadb/columnstore/data1/000.dir/000.dir/008.dir

>> echo "tar cvzf security_0_meta.tgz /usr/local/mariadb/columnstore/mysql/db/test/ /usr/local/mariadb/columnstore/data1/000.dir/000.dir/003.dir/ /usr/local/mariadb/columnstore/data1/000.dir/000.dir/004.dir/ /usr/local/mariadb/columnstore/data1/000.dir/000.dir/007.dir/ /usr/local/mariadb/columnstore/data1/000.dir/000.dir/008.dir/ /usr/local/mariadb/columnstore/data1/systemFiles/" > backup_security_0_meta.sh

>> ./backup_security_0_meta.sh

2. Restore
1) Install ColumnStore on another server.
2) Copy the backup files to another server.
3) Restore the 0th partition file.
>> tar xvf security_0.tar -C /
3) Restore the meta files.
>> tar xvzf security_0_meta.tgz -C /
4) Restart Columnstore
>> mcsadmin restart
>> mcsadmin resumeDatabaseWrites

5) Show the Partitions.
MariaDB [(none)]> select calshowpartitions('test', 'security_tbl', 'stime');
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

calshowpartitions('test', 'security_tbl', 'stime')

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

Part# Min Max Status
0.0.1 2017-03-21 16:09:08 2017-03-27 16:27:44 Enabled
0.1.1 2017-03-22 04:14:02 2017-03-24 16:45:44 Enabled
0.2.1 2017-03-22 16:20:23 2017-03-24 21:40:07 Enabled
0.3.1 2017-03-23 04:26:43 2017-03-23 16:33:03 Enabled
1.0.1 2017-03-21 16:09:08 2017-03-24 04:40:48 Enabled
1.1.1 2017-03-22 04:15:27 2017-03-24 16:47:09 Enabled
1.2.1 2017-03-22 16:21:48 2017-03-24 21:40:07 Enabled
1.3.1 2017-03-23 04:28:08 2017-03-23 16:34:29 Enabled

--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
1 row in set (0.00 sec)

6) Disable all Partitions.
MariaDB [(none)]> select caldisablepartitionsbyvalue('test', 'security_tbl', 'stime', '1900-01-01', '2100-01-01');
------------------------------------------------------------------------------------------

caldisablepartitionsbyvalue('test', 'security_tbl', 'stime', '1900-01-01', '2100-01-01')

------------------------------------------------------------------------------------------

Partitions are disabled successfully

------------------------------------------------------------------------------------------
1 row in set (0.05 sec)

7) enable the 0th Partition.

MariaDB [(none)]> select calenablepartitions('test', 'security_tbl', '0.0.1');
------------------------------------------------------

calenablepartitions('test', 'security_tbl', '0.0.1')

------------------------------------------------------

Partitions are enabled successfully.

------------------------------------------------------
1 row in set, 1 warning (0.04 sec)

MariaDB [(none)]> select calenablepartitions('test', 'security_tbl', '0.1.1');
------------------------------------------------------

calenablepartitions('test', 'security_tbl', '0.1.1')

------------------------------------------------------

Partitions are enabled successfully.

------------------------------------------------------
1 row in set (0.04 sec)

MariaDB [(none)]> select calenablepartitions('test', 'security_tbl', '0.2.1');
------------------------------------------------------

calenablepartitions('test', 'security_tbl', '0.2.1')

------------------------------------------------------

Partitions are enabled successfully.

------------------------------------------------------
1 row in set (0.04 sec)

MariaDB [(none)]> select calenablepartitions('test', 'security_tbl', '0.3.1');
------------------------------------------------------

calenablepartitions('test', 'security_tbl', '0.3.1')

------------------------------------------------------

Partitions are enabled successfully.

------------------------------------------------------
1 row in set (0.05 sec)

We hope that this feature will be developed in the future.
Thank you.

 Comments   
Comment by David Thompson (Inactive) [ 2017-09-21 ]

brown2773 very ingenious approach! I think step 3 is probably flawed and where we'd need to enhance the product. By backing up the entire brm metadata including the extent map you'd have an issue restoring this if you had changes in the target database you are restoring to. If you are strict about maintaining the 2 in exact sync then it should in theory work fine.

Also not sure about copying the additional data files in step 3, i assume the system didn't work without those? One of our engineers wrote a blog about the directory naming if this helps: https://linuxjedi.co.uk/2017/09/01/how-mariadb-columnstores-filenames-work/

Anyway this is a very good feature enhancement regardless. I suspect it should be relatively easy to do if we can find a way to migrate just the portion of extent map for the partition plus we can provide an easier interface as well.

Comment by hogeun cheong [ 2017-09-22 ]

Thank you for your reply and the blog you gave me.
It is very helped me to understand the directory naming.
But table's object_id does not apply.

I want to implement partition backup and restore although i am also concerned about step 3.
So, I want to know where the table and column metadata files in the columnstore are stored.
I assume /usr/local/mariadb/columnstore/data1/000.dir/000.dir/003.dir, 004.dir, 007.dir, and 008.dir are the table / column metadata files for the columnstore.
If the information is incorrect, please provide the correct information and the purpose of that path.

Comment by David Thompson (Inactive) [ 2017-09-22 ]

the columnstore info schema tables all provide the necessary information (columns, extents, files) to map columns to the underyling columnar files (making sure you also handle dictionary extents). In your original query above you might want to add brackets around the or clause for that which is always good practice anyway.

Generated at Thu Feb 08 02:24:53 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.