Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


Restore innodb backup on other server

Posted on 2011-10-14
Medium Priority
Last Modified: 2012-06-12

We are using Percona Xtrabackup to create a db mirroring setup in many to one method.

Server A --> adam_db --> LIVE server
Server B --> adam_db --> mirror server

m taking a tar backup using innobackupex of adam_db on Server A

then, I am copyinh adam_db.tar to Server B, untaring it and synching all folders and files to /var/lib/mysql on Server B
will the ibdata1 of Server A sync to ibdata1 of Server B?

We were only left with recent innodb backup and previous dbs using innodb when checked were corrupted.

We also tried another method of taking backup.

We added innodb_file_per_table in our my.cnf and we got saperate ibd files for all databases(ibd files were created only for db which are added after we added innodb_file_per_table in my.cnf)

We rsynced the db dir in /var/lib/mysql from live to mirror server. DB were still not working.

In this world isn't a single recognised solution for mirroring dbs from one server to another. Please suggest if any other road is there for mirroring dbs from multiple servers to a server.

Thanks in Advance.
Question by:alok86
  • 2
LVL 24

Expert Comment

ID: 36968280
I've used both methods of backup and restore.  For Percona, did you successfully prepare the backup?  See http://www.percona.com/doc/percona-xtrabackup/xtrabackup_bin/preparing_the_backup.html?id=percona-xtrabackup:xtrabackup:prepare

I didnt actually use rsync but did copy the whole mysql datadir.  You need to be sure that either the db is shut down or the tables were locked while the files were being copied (I do a flush tables with read lock, do an lvm snapshot, release the locks, then mount and copy from the snapshot).

For both cases, did you check the Mysql error log for errors encountered?

Author Comment

ID: 37012921
Yes we are able to get backups successfully from Percona. We are able to restore innodbs but issue comes when we restore consecutive innodb dbs. In this case the most recent innodb is left OK and previous are corrupted.
Yes we looked error logs but of no use.
LVL 24

Accepted Solution

johanntagle earned 2000 total points
ID: 37013042
Ah yes because the ib_logfile*, etc files are shared between the different databases and you overwrote it with just the backup of one.  Think you need to take a look at: http://www.mysqlperformanceblog.com/2009/07/31/copying-innodb-tables-between-servers/

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog, we’ll look at how improvements to Percona XtraDB Cluster improved IST performance.
How much do you know about the future of data centers? If you're like 50% of organizations, then it's probably not enough. Read on to get up to speed on this emerging field.
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
Suggested Courses

581 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question