I have taken Full backups from two different servers and I want to restore it to one server. The process is
- Take a backup using innobackup from one server (say S1) and restore it on S3
S3 now has ibdata1 and s1 dbs
- Take a backup from S2 and copy on S3.Rename ibdata1 to ibdata2.
- 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
- start S3 Mysql server.
- 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
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 .