i configured mysql-5.7 in ubuntu 16.04 and had 4 databases. In that, i want take the full database backup of particular database and incremental also. After that i have drop the one database and using xtrabackup of restore the particular database without disturbing of remaining databases.
I tried tor restore the database, but when am i connecting mysql, i am unable to see that databases. But restoration is completed successfully . I have observed in mysql data directory , the folder name is “PERCONA_SCHEMA”.
Could you please help to me in this case, it would be great.
If I understand correctly, you have below example databases:
a, b, c and d.
You want to take full backup of all of them, and incremental and restore only c while a,b and d are still intact.
You can backup only needed databases/tables using https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/xbk_option_reference.html#cmdoption-databases --databases option. If you try to restore this backup on a new server, it will only have the tables specified on --databases option. In your case, you want to keep a,b and d. For that you will need to use the export approach https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/restoring_individual_tables.html
From what I am seeing with innobackex and backing up a single database, there doesn’t seem to be a way to restore this database backup.
My use case is to copy one database from server1 to server 2. Here is what I’ve tried.
Run backup from server1
innobackupex --user=backup --password=* --stream=tar --database=test ./ | ssh server2 \ "cat - /data/backups/test.tar
After the backup completed, I un-tarred it and prepared it
innobackupex --apply-log /data/backups
I shutdown mysql on server2 and copied the /data/backups/test directory to /data/mysql
cp -r /data/backups/test /data/mysql/test
I then started mysql and logged in
I can see the table in the test database but I am unable to select from it.
select * from test.junk;
Error 1146 (42S02): Table ‘test.junk’ doesn’t exist
InnoDB: Cannot open table test/junk from the internal data dictionary of InnoDB though the .frm file for the table exists
I’ve tried deleting the ibdata1 file as I’ve seen in some places but I can’t seem to get the junk table to register to the data dictionary if you will.
I’m a little surprised there are no examples of DB restores. I’ve seen table restore examples. Am I understanding what the --databases option is for?
There is a confusion here:
–database option is meant to backup one database from a source server that have many. If you restore that backup alone you will have that single database. However, only copying the files generated by the backup to a running instance will not make MySQL import it as pointed by the error message, the internal data dictionary won’t be updated.
There is no single command to restore a single database, you have to repeat the procedure https://www.percona.com/doc/percona-xtrabackup/8.0/xtrabackup_bin/restoring_individual_tables.html for all tables on the database you want to restore.
I hope this is clear now.
That helps! Thanks for the clarification.