Uploaded image for project: 'MariaDB ColumnStore'
  1. MariaDB ColumnStore
  2. MCOL-931

Partition Backup / Restore

    XMLWordPrintable

    Details

      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.

        Attachments

          Activity

            People

            Assignee:
            toddstoffel Todd Stoffel
            Reporter:
            brown2773 hogeun cheong
            Votes:
            1 Vote for this issue
            Watchers:
            3 Start watching this issue

              Dates

              Created:
              Updated:
              Resolved:

                Git Integration