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

Merge two percona backups from two servers to one server

prabhuprabhu EntrantCurrent User Role Beginner
Hi

I have taken Full backups from two different servers and I want to restore it to one server. The process is
1) Take a backup using innobackup from one server (say S1) and restore it on S3
S3 now has ibdata1 and s1 dbs
2) Take a backup from S2 and copy on S3.Rename ibdata1 to ibdata2.
3) Copy the database files from S2 backup and ibdata2 to S3 data directory.
S3 now has ibdata1 , ibdata2 ,S1 and S2 DBs
4)add innodb_data_file_path = ibdata1:45122M;ibdata2:890M:autoextend in my.cnf
5) start S3 Mysql server.
6) The show table command lists all the tables but the table referring to ibdata2 will throw this error "ERROR 1146 (42S02): Table 'xyz' doesn't exist
In error log , I see this error
[ERROR] Cannot find or open table xyz from
the internal data dictionary of InnoDB though the .frm file for the
table exists. Maybe you have deleted and recreated InnoDB data
files but have forgotten to delete the corresponding .frm files
of InnoDB tables, or you have moved .frm files to another database?
or, the table contains indexes that this version of the engine
doesn't support.
See http://dev.mysql.com/doc/refman/5.1/en/innodb-troubleshooting.html
how you can resolve the problem.

If I change the file path to
innodb_data_file_path =ibdata2:890M; ibdata1:45122M:autoextend in my.cnf
all table in ibdata2 will come normally and ibdata1 will throw error.

Please let me know if anyone tried this or anybody knows the workaround.I cannot take mysqldumps as the DBs are quite huge in size .

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi prabhu;

    You cannot join together two backups (basically two different MySQL servers) together like that. They are two independent systems, so joining them into one is not that straight forward. The method I would use is restoring the first backup using standard Xtrabackup method, and then use mysqldump to dump the second database and reload it into the first one you restored. You say your database is too big to dump with mysqldump; how big is it? Could you use a parallel mysqldump process like mydumper?

    Otherwise if you have innodb_file_per_table you could try importing the second backup table by table:
    https://www.percona.com/doc/percona-xtrabackup/2.1/innobackupex/restoring_individual_tables_ibk.html

    -Scott
  • prabhuprabhu Entrant Current User Role Beginner
    Thanks Scott .I will try using mysqldumper.I have a database of 180GB and 250 GB which I want to merge for a testing environment.I already have the innobackup backups from both the servers so I wanted a quick solution for the merge.I appreciate your help here .Thanks again
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.