MySQL redundancy

classic Classic list List threaded Threaded
4 messages Options
Reply | Threaded
Open this post in threaded view
|

MySQL redundancy

Patrick Larkin-2
I'm currently operating on a single MySQL server running mysqldumps every two hours.  :(

That's my backup strategy.

I remember hearing about Master-Master setups at LDC last year.  Is that the way to go if I want to start thinking about some sort of replication scheme?  The best case scenario would be either 1) if one of the servers fail, the site seamlessly keeps using the other server or 2) the main server dies and I can quickly cut over to the other server.

I also see this webpage written by Marc Vos where he details how to set up (I think) a Master-Slave-Slave setup.  
http://marc.vos.net/howto/mysqlrepli/

I tried years ago setting up a replication server and it didn't end well.  

Any advice for me?  Any pointers to really good documentation on doing it?  Do I need two identical servers?  

Patrick Larkin
Information Systems
Bethlehem Area School District




#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

RE: MySQL redundancy

Rick Draper-2
Hi Patrick,

Master-Master replication is what we use, in combination with backups.  The potential risk with replication is that what happens on one server will happen on the other - so Drop table has an impact on resilience (if you didn’t want to that)... Backups are always your friend.

Getting it going is a case of being methodical.  The first time I did it I used two clean database installations so there were no existing records to have to consider at start up, and then set about progressively placing the database/table/data into the new setup.

Frank does all this for us now, so I'm a little rusty, but I would point you to the MySQL document http://dev.mysql.com/doc/refman/5.0/en/replication.html - reading it thoroughly and following it step by step worked for me.

As far as working with Lasso is concerned you can use a simple DNS approach to the calls or some more sophisticated load balancing / availability strategies.  I suggest you get your replication working and try a manual changeover by editing IP address as your first step and do some research about what will work for you.

Very best regards,

Rick


#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL redundancy

Marc Pope-2
In reply to this post by Patrick Larkin-2
If you just want a good backup solution, that doesn't have downtime of locking tables like dump does, and you want faster backups, even every 15-20 minutes with historical restores, I'd highly recommend R1soft. We use it to backup 75 servers, many every hour, from 1 machine.

On Feb 19, 2013, at 4:10 PM, Patrick Larkin <[hidden email]> wrote:

> I'm currently operating on a single MySQL server running mysqldumps every two hours.  :(
>
> That's my backup strategy.
>
> I remember hearing about Master-Master setups at LDC last year.  Is that the way to go if I want to start thinking about some sort of replication scheme?  The best case scenario would be either 1) if one of the servers fail, the site seamlessly keeps using the other server or 2) the main server dies and I can quickly cut over to the other server.
>
> I also see this webpage written by Marc Vos where he details how to set up (I think) a Master-Slave-Slave setup.  
> http://marc.vos.net/howto/mysqlrepli/
>
> I tried years ago setting up a replication server and it didn't end well.  
>
> Any advice for me?  Any pointers to really good documentation on doing it?  Do I need two identical servers?  
>
> Patrick Larkin
> Information Systems
> Bethlehem Area School District
>
>
>
>
> #############################################################
> This message is sent to you because you are subscribed to
>  the mailing list Lasso
> [hidden email]
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>
#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>
Reply | Threaded
Open this post in threaded view
|

Re: MySQL redundancy

Marc Vos
In reply to this post by Patrick Larkin-2
Hi Patrick,

You might also be interested in this: http://marc.vos.net/howto/mysqlmulti/

I have split up replication so that databases where structural changes and data I/O are at a minimum are replicated by one slave and the higher volume I/O databases are replicated by the other slave. This means I have two separat MySQL instances running, which speeds up things.
Another advantage is that when you make changes to a database which is replicated by slave A, and not picked up by slave A (it happens), then slave B keeps on replicating.

But, it is not a backup solution. As said in a tweet with Chris Wik, you should run a backup solution of the replicated server - that way you still always have actual data but you do not lock the tables on the production server. The slaves will simply temporarily pause and continue whenever everything is available again.

- -
Regards,
Marc


On 19 feb. 2013, at 22:10, Patrick Larkin <[hidden email]> wrote:

> I'm currently operating on a single MySQL server running mysqldumps every two hours.  :(
>
> That's my backup strategy.
>
> I remember hearing about Master-Master setups at LDC last year.  Is that the way to go if I want to start thinking about some sort of replication scheme?  The best case scenario would be either 1) if one of the servers fail, the site seamlessly keeps using the other server or 2) the main server dies and I can quickly cut over to the other server.
>
> I also see this webpage written by Marc Vos where he details how to set up (I think) a Master-Slave-Slave setup.  
> http://marc.vos.net/howto/mysqlrepli/
>
> I tried years ago setting up a replication server and it didn't end well.  
>
> Any advice for me?  Any pointers to really good documentation on doing it?  Do I need two identical servers?  
>
> Patrick Larkin
> Information Systems
> Bethlehem Area School District
>
>
>
>
> #############################################################
> This message is sent to you because you are subscribed to
>  the mailing list Lasso
> [hidden email]
> To unsubscribe, E-mail to: <[hidden email]>
> Send administrative queries to  <[hidden email]>

#############################################################
This message is sent to you because you are subscribed to
  the mailing list Lasso
[hidden email]
To unsubscribe, E-mail to: <[hidden email]>
Send administrative queries to  <[hidden email]>