Restore innodb backup on other server

Posted on 2011-10-14
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
    LVL 24

    Expert Comment

    I've used both methods of backup and restore.  For Percona, did you successfully prepare the backup?  See

    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

    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

    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:

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Highfive + Dolby Voice = No More Audio Complaints!

    Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

    How to update Firmware and Bios in Dell Equalogic PS6000 Arrays and Hard Disks firmware update.
    Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
    This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…
    This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    12 Experts available now in Live!

    Get 1:1 Help Now