Announcement

Announcement Module
Collapse
No announcement yet.

Questions about making a MySQL Slave with innobackupex

Page Title Module
Move Remove Collapse
X
Conversation Detail Module
Collapse
  • Filter
  • Time
  • Show
Clear All
new posts

  • Questions about making a MySQL Slave with innobackupex

    I am looking to start doing replication on a **very** busy and financially important MySQL Server so I started looking at the how-to linked below...however, I have some questions:

    1. The server I am on, is VERY busy, averages 600-1000 queries per second. I have not turned on binlogs yet. Does it matter if I am just enabling bin-logs now and not at the beginning of when I started this server years ago?

    2. I hadn't realized that the bin logs were not yet enabled before I ran innobackupex, so I will re-do this script with them enabled. However, when I did run it, it seemed like it was in an infinite loop and I killed it before it finished...

    >> log scanned up to (90655878867)
    >> log scanned up to (90655878867)
    >> log scanned up to (90655878867)

    Is this normal? The numbers didn't increase, however the backup file was getting larger..

    3. Has this process been tested on very large databases? I am running close to 90GB of data in my mysql folder and I need a hot-spare in-case things go bad.

    I am open to any suggestions. Our database can't go down for even a short period. 15 minutes can cost my client thousands in lost sales so I hope you all understand my dilemma.

  • #2
    >> 1. The server I am on, is VERY busy, averages 600-1000 queries per second. I have not turned on binlogs yet. Does it matter if I am just enabling bin-logs now and not at the beginning of when I started this server years ago?

    In order to work with replication, you need to enable binary logging. Yes you can start binlog when setting up replication it doesn't matter that it's not started when you started the server because when you take backup of master db and restores it slave. xtrabackup also saves binlogs coordinates and those will be used to resume replication on slave so master/slave should have the exact copy of database.

    >> it seemed like it was in an infinite loop and I killed it before it finished...
    >> log scanned up to (90655878867)

    It is normal.

    Are you using InnoDB exclusively or mix of MyISAM/InnoDB. For innoDB, you can use --no-lock option. This avoids any locking.
    For this to work properly:
    1) 100% of your tables must be InnoDB (except the "mysql" schema tables).
    2) No data-definition queries (DROP, ALTER, CREATE) should be executed while the innobackupex is copying the "static" files (.frm, MRG, .MYD, .MYI, .TRG, .TRN, .ARM, .ARZ, .CSM, .CSV, etc).


    Also, there are no. of features with xtrabackup, you can throttle the backup, For more info refer to http://www.percona.com/doc/percona-x...tling_ibk.html

    Hope it clears your doubts.

    Comment


    • #3
      >> Has this process been tested on very large databases?

      Yes, I have run it on 200 GB of data and I am sure it has been run on much larger sets as well.

      The 'bin-log' option cannot be changed at runtime, so you will have downtime to enable it. http://www.mysqlperformanceblog.com/...hutdown-times/ will help.

      Comment

      Working...
      X