Not the answer you need?
Register and ask your own question!

Partial Backups not working on centos 7 mariadb 5.5.x Percona 2.3.x

klabacitaklabacita ContributorCurrent User Role Novice
Hi people.

I'm trying to learn how to do a partial backup for mariadb 5.5.60 under centos 7 with percona version 2.3.10

Reading the manual Percona XtraBackup 2.3 Documentation Release 2.3.10
The full backup works, tested and is easy which in some situations is good, but exist others where we just need to restore some databases of the whole system.
I had try the steps for partial backups but looks like I'm doing some mistakes.
The option: innodb_file_per_table is enable on /etc/my.cnf
My tables are now ready for this type of backup.


Example, the first 2 steps are easy.
1; Run the backup
innobackupex --user=root --password=mypassword --databases="sbtest" /opt/backups/

2; Prepare for restore.
innobackupex --apply-log --export /opt/backups/2018-12-24_21-30-54/

Here I got my backup files:

-rw-r
1 root root 386 Dec 24 21:30 backup-my.cnf
-rw-r
1 root root 18874368 Dec 24 21:32 ibdata1
-rw-r--r-- 1 root root 5242880 Dec 24 21:32 ib_logfile0
-rw-r--r-- 1 root root 5242880 Dec 24 21:32 ib_logfile1
drwx
2 root root 172 Dec 24 21:32 sbtest
-rw-r--r-- 1 root root 355 Dec 24 21:37 tables.sql
-rw-r
1 root root 113 Dec 24 21:32 xtrabackup_checkpoints
-rw-r
1 root root 451 Dec 24 21:30 xtrabackup_info
-rw-r
1 root root 2097152 Dec 24 21:32 xtrabackup_logfile

3; Example, I drop my database(sbtest), latter I create my 2 tables:

drop database sbtest;
create my 2 tables:
CREATE TABLE `sbtest1` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB;

CREATE TABLE `sbtest2` (
`id` int(11) NOT NULL,
`k` int(11) NOT NULL DEFAULT '0',
`c` char(120) NOT NULL DEFAULT '',
`pad` char(60) NOT NULL DEFAULT ''
) ENGINE=InnoDB;

Run ALTER command:

ALTER TABLE sbtest1 DISCARD TABLESPACE;
ALTER TABLE sbtest2 DISCARD TABLESPACE;

Now I change my backup files for each table to mysql:mysql .idb, .exp for each table.
copy those files to mysql path:
cp -p sbtest1.ibd sbtest1.exp sbtest2.ibd sbtest2.exp /var/lib/mysql/sbtest

Run the ALTER command:
ALTER TABLE sbtest1 IMPORT TABLESPACE;
ALTER TABLE sbtest2 IMPORT TABLESPACE;

Once I run the command I got this error in console:
ERROR 1030 (HY000): Got error -1 from storage engine

From this point, I lost my database, it won't even allow me to drop the database, I have to manually delete the folder.

drop database sbtest;
ERROR 1010 (HY000): Error dropping database (can't rmdir './sbtest/', errno: 17)

The idea is just to restore the database I want.

I'm missing something in my steps?

Thanks all for your support and happy holidays!!!
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.