Announcement

Announcement Module
Collapse
No announcement yet.

How can I upgrade our production db from innodb to percona server/xtradb

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

  • How can I upgrade our production db from innodb to percona server/xtradb

    We are looking to upgrade our production mysql/innodb database to xtradb. I'm trying to find the optimal way to do this with minimal downtime.

    Our current setup is:
    - mysql 5.0.51a-3ubuntu5.5
    - built-in innodb (not innodb plugin)
    - master -> slave replication
    - 80+ tables
    - 5+ tables with 10m+ rows

    We want to move to:
    - Percona Server with XtraDB

    What's the best way to make this transition with minimal downtime?
    Will we need to start our replication from scratch?
    Any tutorials or guides that would help?

    Thanks in advance!

    - James Smith

  • #2
    Storage format for InnoDB and XtraDB should be fully compatible, so if you prepare everything you should be able to just stop the currently running mysqld process and start the Percona server running against the same data directory.

    Although if you intend to also upgrade from 5.0 to a newer 5.x then you will have to take into account the normal upgrade procedures for MySQL server.

    The same goes for replication, you can continue running replication just as it is and even if you want to upgrade to a newer version of 5.x then you should freely be able to do so. Only if you are running a newer master and are using any possible new sql features that is not available on an older slave should you run into trouble. So when upgrading a replication pair you always start with upgrading the slave.

    So depending on how much you want to perform in this upgrade you have two choices:
    1.
    If you just want to switch to Percona but still stay on the 5.0 you should just be able to stop the slave and start it with the new Percona binary and stop the master and start it with the new Percona binary, should just take a couple of minutes of downtime per server and if you can live with it you don't have to fiddle with failover etc.

    2.
    If you also want to upgrade to a newer 5.x release you need to stop the slave, start it with a newer 5.x Percona version, run mysql_upgrade to make sure that tables are up to date (which will probably take time since it runs a mysqlcheck of all tables).
    Then stop the master and perform failover (if you can't live with the downtime).
    Perform the same upgrade on the slave.
    Possibly fiddle with failing back the HA pair if you have some preference on which server should be master and which slave.

    Since option 2 will become quite a bit more work to keep the servers in sync while switching back and forth with the replication I would suggest option 1 but that depends on if you can live with the downtime on the master.

    Good luck!

    Comment


    • #3
      Thanks sterin71, we eventually did a production upgrade using method 2) that you suggested, worked like a charm with no production downtime!

      Thanks again

      -James

      Comment


      • #4
        My pleasure, nice to hear that it worked out so smoothly for you.

        Cheers!
        /Stefan

        Comment

        Working...
        X