What are the options for archiving old data of mariadb tables if partitioning can not be implemented due to a restriction

Question

We are planning to archive some old data of our MariaDB tables to some other volume(or mount point) of the linux server apart from the one the tables are on.

We are using partitioning for this and the tables are innodb tables with 'innodb_files_per_table=ON'.

While doing this we are placing the partitions containing old data to a directory located in some other volume.

But a restriction in the form of 'every unique key on the table must use every column in the table's partitioning expression. (This also includes the table's primary key, since it is also a unique key.)' is a major road blocker in our activity. (https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-partitioning-keys-unique-keys.html )

Here for partitioning we have to use a particular column which in most of the cases is not included in every unique key created on the table. We can not even change the key structure of the tables as it may impact the operation of applications.

So , we have to opt for other practices to archive the data. Any suggestions on this will be really helpful.

Sample scenario: Table:

CREATE TABLE `tbl` (
  `col1` int(11) NOT NULL DEFAULT '0',
  `col2` int(11) NOT NULL AUTO_INCREMENT,  
  `col3` int(11) NOT NULL DEFAULT '0',
  `col4` varchar(60) NOT NULL DEFAULT '',
  `col5` varchar(60) DEFAULT NULL,
  `changed` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE 
   CURRENT_TIMESTAMP,

   UNIQUE KEY `uniq1` (`col2`),
   UNIQUE KEY ` uniq2` (`col1`),
   UNIQUE KEY ` uniq3` (`col4`,`col3`),
   UNIQUE KEY `uniq4` (`col5`,`col3`),
   KEY `key1` (`changed`),
 );

We are partitioning w.r.t col1.

Query:

alter table tbl partition by range(col1)( partition p0 values less than (10000000) data directory = '/partitions',partition p1 values less than (20000000) data directory = '/partitions',partition p2 values less than (30000000),partition p3 values less than (maxvalue));

ERROR 1503 (HY000): A PRIMARY KEY must include all columns in the table's partitioning function

Answer 1

Here's a thought on archiving, with transactional integrity.

• Have another table; let's call it archive. It needs few, if any indexes.
• Move 1000 rows from your table to archive at a time, transactionally.
• After you have moved "enough". do whatever you want to Archive archive.

See this for suggestions on efficiently DELETEing rows from a table. The rest is

INSERT INTO `archive` SELECT ... FROM main ...;

where the ... come from the chunking. That can probably be done with autocommit=on and not need any BEGIN...COMMIT.