Hi WB,
Here is my question in detail:
- I have one production DB1 and slave for the same is DB2.
- Planning to decommission DB5 as it is low end server. There are two DB’s available on the low end server which were used for reporting purpose.
- Idea is to move DB5 databases to DB2. Question: DB1 is master and DB2 is slave where replication is on and completely caught up with master.
- As said earlier there are 2 databases on DB5. One database which is critical to business is being backup using xtrabackup tool and the other db is not backup at all.
- If I need to restore or copy the databases from DB5 to DB2, how do I do? The xtrabackup which I am taking for one database on db5 has generated a .qp file when extracted we get set of files related to xtrabackup, bin log etc.
- DB2 is acting as slave for DB1, backup of DB1 is taken using xtrabackup tool and all the required files are available on mysql data directory. When I restore other database from db5, shall I restore at the same data directory location or at other place. If I restore at other place what files need to be copied to data directory to have the database of db5 showing up fine. Tried to copy the data file, though database is showing up but when we desc the table it says no ERROR 1146 (42S02): Table ‘Table_name’ doesn’t exist.
- With respect to the other database, we can take the mysqldump and restore on DB5 database. We can also take mysql dump of the first databases, but it would take really long time to restore, in our case it took around 23 hours to restore 100GB.
Please suggest a best approach to proceed in above scenario.
Best Regards,
Krishna