[MDEV-17132] Feature request: mariabackup doesn't support Backup & Restore of specific partition of table Created: 2018-09-05  Updated: 2022-05-17  Resolved: 2020-06-23

Status: Closed
Project: MariaDB Server
Component/s: Backup
Affects Version/s: 10.2.14
Fix Version/s: N/A

Type: Bug Priority: Critical
Reporter: Pramod Mahto Assignee: Vladislav Lesin
Resolution: Not a Bug Votes: 1
Labels: need_feedback
Environment:

All

Issue Links:
Relates
relates to MDEV-10568 Implement support for ALTER TABLE ...... Open
relates to MDEV-17689 Document how to do partial backups an... Closed
relates to MDEV-28590 Creating backup of a specific partiti... Open

 Description   

Mariabackup doesn't support backup of individual partition of table. Its not having something to be like "--include='^test.members#P#p4'" to have a backup of test.members#P#p4 partition table.
Its taking backup of entire table itself (test case is mentioned below).

While other hand its working fine with innobackupex:-

innobackupex --user='root' --password='root' --include='^test.members#P#p4' /tmp/

(skipping all other databases and its relevant tables including other partition of that table which need to be skipped)
https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/recipes_ibkx_partition.html

Example:- 

 
 
mariabackup --backup --user='root' --password='root' --databases='test' --tables='^test.members#P#p4' --target-dir /tmp
 
 
 
180904 20:20:37 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
 
Using server version 10.2.14-MariaDB-log
 
mariabackup based on MariaDB server 10.2.14-MariaDB Linux (x86_64)
 
mariabackup: uses posix_fadvise().
 
mariabackup: cd to /var/lib/mysql/
 
mariabackup: open files limit requested 0, set to 1024
 
mariabackup: using the following InnoDB configuration:
 
mariabackup:   innodb_data_home_dir = .
 
mariabackup:   innodb_data_file_path = ibdata1:12M:autoextend
 
mariabackup:   innodb_log_group_home_dir = ./
 
2018-09-04 20:20:37 140236241541088 [Note] InnoDB: Number of pools: 1
 
mariabackup: Generating a list of tablespaces
 
Skipping db: ./performance_schema
 
2018-09-04 20:20:37 140236241541088 [Warning] InnoDB: Allocated tablespace ID 28 for test/members#P#p4, old maximum was 0
 
Skipping db: ./sakila
 
Skipping db: ./mysql
 
180904 20:20:37 >> log scanned up to (9047515)
 
180904 20:20:37 [01] Copying ./ibdata1 to /tmp/ibdata1
 
180904 20:20:37 [01]        ...done
 
180904 20:20:37 [01] Copying ./test/members#P#p4.ibd to /tmp/test/members#P#p4.ibd
 
180904 20:20:37 [01]        ...done
 
180904 20:20:37 [01] Copying ./test/members#P#p0.ibd to /tmp/test/members#P#p0.ibd
 
180904 20:20:37 [01]        ...done
 
180904 20:20:37 [01] Copying ./test/members#P#p1.ibd to /tmp/test/members#P#p1.ibd
 
180904 20:20:37 [01]        ...done
 
180904 20:20:37 [01] Copying ./test/members#P#p3.ibd to /tmp/test/members#P#p3.ibd
 
180904 20:20:37 [01]        ...done
 
180904 20:20:37 [01] Copying ./test/members#P#p2.ibd to /tmp/test/members#P#p2.ibd
 
180904 20:20:37 [01]        ...done
 
180904 20:20:38 >> log scanned up to (9047515)
 
180904 20:20:38 Executing FLUSH NO_WRITE_TO_BINLOG TABLES...
 
180904 20:20:38 Executing FLUSH TABLES WITH READ LOCK...
 
180904 20:20:38 Starting to backup non-InnoDB tables and files
 
Skipping db: ./performance_schema
 
180904 20:20:38 [01] Copying ./test/db.opt to /tmp/test/db.opt
 
180904 20:20:38 [01]        ...done
 
180904 20:20:38 [01] Copying ./test/members.frm to /tmp/test/members.frm
 
180904 20:20:38 [01]        ...done
 
180904 20:20:38 [01] Copying ./test/members.par to /tmp/test/members.par
 
180904 20:20:38 [01]        ...done
 
Skipping db: ./sakila
 
Skipping db: ./mysql
 
180904 20:20:38 Finished backing up non-InnoDB tables and files
 
180904 20:20:38 [01] Copying aria_log_control to /tmp/aria_log_control
 
180904 20:20:38 [01]        ...done
 
180904 20:20:38 [01] Copying aria_log.00000001 to /tmp/aria_log.00000001
 
180904 20:20:38 [01]        ...done
 
180904 20:20:38 [00] Writing xtrabackup_binlog_info
 
180904 20:20:38 [00]        ...done
 
180904 20:20:38 Executing FLUSH NO_WRITE_TO_BINLOG ENGINE LOGS...
 
mariabackup: The latest check point (for incremental): '9047506'
 
mariabackup: Stopping log copying thread.
 
 
 
180904 20:20:38 >> log scanned up to (9047515)
 
180904 20:20:38 Executing UNLOCK TABLES
 
180904 20:20:38 All tables unlocked
 
180904 20:20:38 [00] Copying ib_buffer_pool to /tmp/ib_buffer_pool
 
180904 20:20:38 [00]        ...done
 
180904 20:20:38 Backup created in directory '/tmp/'
 
MySQL binlog position: filename 'master-bin.000020', position '3506814', GTID of the last change '1-100-99'
 
180904 20:20:38 [00] Writing backup-my.cnf
 
180904 20:20:38 [00]        ...done
 
180904 20:20:38 [00] Writing xtrabackup_info
 
180904 20:20:38 [00]        ...done
 
mariabackup: Redo log (from LSN 9047506 to 9047515) was copied.
 
180904 20:20:38 completed OK!
 
 
 
 
 
[root@master-node tmp]# cd test/
 
[root@master-node test]# ls -ltrh
 
total 492K
 
-rw-r----- 1 root root 96K Sep  4 20:39 members#P#p4.ibd
 
-rw-r----- 1 root root 96K Sep  4 20:39 members#P#p0.ibd
 
-rw-r----- 1 root root 96K Sep  4 20:39 members#P#p1.ibd
 
-rw-r----- 1 root root 96K Sep  4 20:39 members#P#p3.ibd
 
-rw-r----- 1 root root 96K Sep  4 20:39 members#P#p2.ibd
 
-rw-r----- 1 root root  60 Sep  4 20:39 members.par
 
-rw-r----- 1 root root 977 Sep  4 20:39 members.frm
 
-rw-r----- 1 root root  65 Sep  4 20:39 db.opt
 
 
 
Other option tried but fail to go.. 
 
 
[root@master-node tmp]# mariabackup --backup --user='root' --password='root' --databases='test' --tables-file='/var/lib/mysql/test/members#P#p4.ibd' --target-dir /tmp
 
180904 20:43:45 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
 
Using server version 10.2.14-MariaDB-log
 
mariabackup based on MariaDB server 10.2.14-MariaDB Linux (x86_64)
 
mariabackup: uses posix_fadvise().
 
mariabackup: cd to /var/lib/mysql/
 
mariabackup: open files limit requested 0, set to 1024
 
mariabackup: using the following InnoDB configuration:
 
mariabackup:   innodb_data_home_dir = .
 
mariabackup:   innodb_data_file_path = ibdata1:12M:autoextend
 
mariabackup:   innodb_log_group_home_dir = ./
 
2018-09-04 20:43:45 140461935507424 [Note] InnoDB: Number of pools: 1
 
mariabackup: `▒▒D...` name is too long
 
 
 
 
 
 
 
[root@master-node tmp]# mariabackup --backup --user='root' --password='root'  --tables-file='/var/lib/mysql/test/members#P#p4.ibd' --target-dir /tmp
 
180904 20:44:01 Connecting to MySQL server host: localhost, user: root, password: set, port: not set, socket: not set
 
Using server version 10.2.14-MariaDB-log
 
mariabackup based on MariaDB server 10.2.14-MariaDB Linux (x86_64)
 
mariabackup: uses posix_fadvise().
 
mariabackup: cd to /var/lib/mysql/
 
mariabackup: open files limit requested 0, set to 1024
 
mariabackup: using the following InnoDB configuration:
 
mariabackup:   innodb_data_home_dir = .
 
mariabackup:   innodb_data_file_path = ibdata1:12M:autoextend
 
mariabackup:   innodb_log_group_home_dir = ./
 
2018-09-04 20:44:01 140566690641888 [Note] InnoDB: Number of pools: 1
 
mariabackup: `▒▒D...` name is too long
 
 



 Comments   
Comment by Vladislav Vaintroub [ 2018-12-18 ]

pramod.mahto@mariadb.com : I do not think we'll be able to fix that in mariabackup.
While copying just a partition into backup could be implemented, "export" would not work, I is done by the server that needs full table.

However, outside of mariabackup, there is an easy solution https://mariadb.com/kb/en/library/flush-tables-for-export/

So, you

  • FLUSH TABLES mytable FOR EXPORT ,
  • then copy mydb/mytable#P#p4.ibd and mydb/mytable#P#p4.cfg into "backup directory"
  • then UNLOCK TABLES

then proceed with the hacks described in the Percona walkthrough https://www.percona.com/doc/percona-xtrabackup/LATEST/howtos/recipes_ibkx_partition.html

(i.e create empty table like the partitioned table, but without partitioning, DISCARD/IMPORT TABLESPACE, and finally EXCHANGE PARTITION)

Comment by Geoff Montee (Inactive) [ 2018-12-21 ]

The workaround is to essentially use the following process instead of using mariabackup:

https://mariadb.com/kb/en/library/innodb-file-per-table-tablespaces/#copying-transportable-tablespaces-for-partitioned-tables

See also MDEV-17689.

Generated at Thu Feb 08 08:34:09 UTC 2024 using Jira 8.20.16#820016-sha1:9d11dbea5f4be3d4cc21f03a88dd11d8c8687422.