We have few databases which are critical to business taken backup using xtrabackup tool. In order to setup replication, we get *.qp extension of the file which is extracted at data directory of mysql. However, if I plan to add one more database into an existing slave host how can I do that?
Here my concerns is if we extract the db backup at mysql data directory we see around 18-20 files getting created. However, I wanted to add one more host to replicate on the same server. I have a backup of host3 which is through xtrabackup tool again. Need to know if we can restore the host3 xtrabackup at same old data directory where other DB’s are available now. If not, will extract at other location and try to copy the data file only. In such case will the db gets created or not! If not created, what is the method to add it into the regular Database.Please advice!
Do you mean, you need to restore another database backup from other source i.e. host3 ? It look likes you want to use two masters to replicate on slave. It’s not possible. Master database can have many slaves but slaves can have only one master. Or you want to know procedure to add another database in replication process on already running slave ?
Sorry if i misunderstood it.
Thanks Irfan. I need to add another database h3 in replication process where that host is acting as a slave h2 for another host h1.
Hello Krishna, if this new server will act as a slave connected with the server you just have acting as a master, it’s pretty possible. There are many things you must pay attention as it seems that you’re intending to run both slave MySQL instances on same machine. If you have more questions let us know.
WB
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
Thanks guys, I have figured out a way and could succeed in restoring the Database DB5 onDB2 restarting the mysql instance on DB2 twice.