Advertisement
If you have a new account but are having problems posting or verifying your account, please email us on hello@boards.ie for help. Thanks :)
Hello all! Please ensure that you are posting a new thread or question in the appropriate forum. The Feedback forum is overwhelmed with questions that are having to be moved elsewhere. If you need help to verify your account contact hello@boards.ie

MySQL Replication: Master / Slave

Options
  • 19-03-2009 5:38pm
    #1
    Ross
    Closed Accounts Posts: 1,150 ✭✭✭


    Howdy,

    I'm investigating how best to approach migrating from a single database server for boards.ie to a Master-Slave combination. Right now, the Master is a 32-bit CentOS machine running MySQL 5.0.22 and the Slave is a 64-bit FreeBSD 7.0 machine with MySQL 5.0.67.

    Both machines have a similar beefy hardware spec, the 64-bit machine is one I specced and set up recently, while the other has been the DB server for the last ~18 months (not sure why the OS is 32-bit as the HW is 64-bit, but there ya go).

    Obviously in an idea scenario the machines would be identical, but does anyone have any experience of problems that might be encountered with such a setup?

    I understand the question is kinda vague, but I'm looking for anything I've overlooked, no matter how obvious.

    I imagine what we really need is a very experienced DBA to come in with us for a week, but hopefully won't be necessary once our new systems engineer starts.


Comments

  • Options
    #2
    LARDO
    Closed Accounts Posts: 60 ✭✭LARDO


    Hi ross , i dont know alot about this area but i am curious about ur configuration, i used to work for large data storage company and ur architecture seems limited to me.

    What i mean by this is to say i would have though a large db would have all data on an independent storage device , and would have servers attached making it very scalable into the future, the servers then are more like cahches

    Im probably show my ignorance of whats involved here but what the hell,lol.


  • Options
    #3
    ressem
    Registered Users Posts: 2,426 ✭✭✭ressem


    I don't know of any issues that would discourage mixing Linux 32bit and FreeBSD 64 bit in a master - slave setup. (Linux and windows on the other hand can be problematic only because of old MySql bugs and Windows case insensitivity. ).
    Replication at it's simplest just involves transferring parts of the mysql binary log containing commands that might change the database so the 32/64 doesn't affect anything I've seen.

    As for mixing Mysql versions, so long as the slave's version is newer than the master then you should be OK but

    Mysql was problematic on 64 bit linux with large memory at the time of 5.0.22, resulting in server crashes.
    There's been a lot of fixes in replication since '06, such as commands with certain strings not being replicated. Might want to look at the release notes if you've yet to.

    If you're going to be adding many more slave DBs, it might be worth adding one whose purpose is only to replicate the master DB, so other slaves can be built from this rather than stopping the master or buying HotBackup.

    (Discovered from High Performance MySQL, Second Edition. O'Reilly)
    For when the slave gets out of sync, the perl script mk_table_sync is useful at http://www.maatkit.org/tools.html, comes in handy to fix the bits that differ.

    And if you plan to make use of the slave when the master goes down, you might want to upgrade the MySql of the 32 bit server.
    http://dev.mysql.com/doc/refman/5.0/en/replication-solutions-switch.html


  • Options
    #4
    Ross
    Closed Accounts Posts: 1,150 ✭✭✭Ross


    Thanks for all the info ressem, much appreciated, will have to have a good read through the last few release notes.

    I don't think HotBackup will be an option in the near future as we use MyISAM and there's a lot of writes going on all the time for updating threadviews etc. I think what I'll probably end up doing is switching reads to the master for the period of the backup and then switching back.


Advertisement