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

How to restsore single database in mysql-5.7

gopalakrishna99gopalakrishna99 Current User Role Novice

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.

Best Answer

Answers

  • Marcelo AltmannMarcelo Altmann Percona Staff Role

    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

  • jjengel11jjengel11 Entrant Current User Role Patron

    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?

  • jjengel11jjengel11 Entrant Current User Role Patron

    That helps! Thanks for the clarification.

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.