Announcement

Announcement Module
Collapse
No announcement yet.

Restore individual table

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Restore individual table

    Is it really true that XtraBackup requires the original table definition to be created before it can restore the table files using "IMPORT TABLESPACE"? If so that seems like a big oversight. This functionality should be built into XtraBackup. Imagine this scenario:

    1. DBA has a 500 GB MySQL database on a production server. The server is a standalone server (no replication).
    2. An application or user (or careless DBA) accidentally drops a very important 1 GB table that is the key to the whole application.
    3. DBA tries to restore the table from the XtraBackup backup, but doesn't have the definition of the table to create the empty table first.
    4. DBA gets fired because the entire 500 GB (the whole server) needs to be restored in order to restore the innodb table dictionary stuff.

    Am I missing something here? Seems like as part of the backup process the XtraBackup software should perform some sort of "mysqldump --no-data" on the backed up databases and store that away for later table creation use if needed. Better yet, I think the table definitions can be retrieved from the backup files using the minimal innodb engine that is used to apply the logs during the backup process.

  • #2
    Good point. On the other hand, it is easy to get the table definition from a full backup, and mysqldump --no-data may take some time for a large number of tables.

    Comment


    • #3
      How can I get the table definition from a full backup?

      Also, what happens if my database has hundreds of tables, how can I easily discard each tablespace and then import for each one? Again, it seems like there is a great need for automating some of this into the XtraBackup product. Worth paying for features like that. I guess since it's open source we can develop it ourselves but everyone needs these features. We copy databases all the time from server to server.
      Last edited by SQLGuy; 04-15-2013, 04:37 PM.

      Comment


      • #4
        You can read the full backup by starting a new MySQL instance. I'm not saying it is an ideal solution, it should be much easier.

        Comment


        • #5
          Originally posted by SQLGuy View Post
          Is it really true that XtraBackup requires the original table definition to be created before it can restore the table files using "IMPORT TABLESPACE"? If so that seems like a big oversight. This functionality should be built into XtraBackup. Imagine this scenario: 1. DBA has a 500 GB MySQL database on a production server. The server is a standalone server (no replication). 2. An application or user (or careless DBA) accidentally drops a very important 1 GB table that is the key to the whole application. 3. DBA tries to restore the table from the XtraBackup backup, but doesn't have the definition of the table to create the empty table first. 4. DBA gets fired because the entire 500 GB (the whole server) needs to be restored in order to restore the innodb table dictionary stuff. Am I missing something here? Seems like as part of the backup process the XtraBackup software should perform some sort of "mysqldump --no-data" on the backed up databases and store that away for later table creation use if needed. Better yet, I think the table definitions can be retrieved from the backup files using the minimal innodb engine that is used to apply the logs during the backup process.
          You simply have to make sure to use innobackupex instead of the xtrabackup* binaries so that the corresponding FRM files will be included in the backup. You can retrieve table structure from *.frm http://www.mysqlperformanceblog.com/...from-frm-file/

          This is not the only way, of course, it would be good to have contingency plans for your backups.
          Our documentation has a lot of answers about common questions on Percona software, have you checked there before posting that question here? http://www.percona.com/forums/core/i...lies/smile.png

          Join us at the annual Percona Live MySQL Users Conference - http://www.percona.com/live/mysql-conference-2014/

          Comment


          • #6
            Even without .frm file you can retrieve the table structure:
            http://www.mysqlperformanceblog.com/...db-dictionary/

            Comment

            Working...
            X