Advices for a jailed MySQL server

[Doug Poland]

On Mon, May 11, 2009 14:19, Martin Turgeon wrote:
> Bill Moran a écrit :
>> In response to Martin Turgeon <freebsd at optiksecurite.com>:
>>
>>> Hi everyone,
>>>
>>> I'm starting to build a new dedicated MySQL server. I will be
>>> using FreeBSD 7.2-REL. My plan is to jail the latest version of
>>> MySQL 5.0 and to put the MySQL data outside the jail. My
>>> objective is to be able to update MySQL without down time. My
>>> objective would be to create another up to date MySQL jail and
>>> when I'm ready to make the switch, just point the new jail to the
>>> data outside the jail using something like a nullfs mount.
>>>
>>> Is someone using something like this?
>>>
>>> Did someone have any advice about how to update a MySQL server
>>> without down time?
>>>
>>> Did someone have any advice on how to tune a dedicated MySQL
>>> server running FreeBSD 7.2 (Dual core Xeon, 4G RAM, mirror RAID
>>> on a PERC5 controler 2x146G 15K)?
>>>
>>> Thanks everyone for sharing your precious knowledge :)
>>
>> I expect that what you're trying to do will work, however it's
>> horrifically error-prone during the upgrade procedure (what if you
>> forget to stop the first MySQL before you start the new one!)
>>
>> If you need to do anything zero-downtime, then you probably want to
>> run multiple MySQL instances and use database replication to keep
>> the data in sync.  That way you just switch which DB is master,
>> then upgrade the slave ... rinse/repeat.
>>
>
> Hi and thanks for your reply. Sorry for the late response...
>
> I thought about the risk of the procedure and that's why I asked here
>  hoping that someone had a better idea!
>
> Do you mean to have another jail with an up to date slave MySQL, get
> it in sync with the master and then switch the config file of the
> slave to make it a master, restart the new MySQL, change the config
> so that the apps are connecting to the new DB?
>
You may want to research using MySQL in "dual-master" mode.  In this
architecture, each MySQL instance is both a master and a slave.  Some
care must be taken in the configuration but is well-documented and
googalable.  I recently deployed such a configuration using a jail for
each instance.  Upgrading from 5.1.33 to 5.1.34 was a snap and there
was no down time.  In my case, I use a DNS CNAME to identify the
"master" instance that all clients access.


-- 
Regards,
Doug