Master - Slave MySQL Database Server

[Kaushal Shriyan-2]

 Hi,

is this a correct documentation
http://howtoforge.com/mysql_master_master_replication for Master Slave
Replication

My requirement was if the Master goes down, the slave should take care and
act as Master. the Master should acts as slave and reversing of this role.
is this possible

Also how can i test this setup from the MySQL client. Any test cases
available would be of great help to test out this MySQL Database Master and
Slave Server

Thanks and Regards

Kaushal

[Marcus Bointon]

On 7 Apr 2008, at 11:09, Kaushal Shriyan wrote:
> is this a correct documentation
> http://howtoforge.com/mysql_master_master_replication for Master Slave
> Replication

Well, it's for master-master, not master-slave. Also see http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html

> My requirement was if the Master goes down, the slave should take  
> care and
> act as Master. the Master should acts as slave and reversing of this  
> role.
> is this possible

Yes. With master-master, neither machine is considered a slave  
(actually they are both slaves too). However, there are good practical  
reasons to treat one as a master, for example it may be on superior  
hardware, or you need to ensure transactional integrity which you  
can't do spread across servers.

When you have master-master up and running, you need to deal with  
failover at the application level - so your app needs to know about  
both servers and know to try the other one if its default connection  
fails.

> Also how can i test this setup from the MySQL client. Any test cases
> available would be of great help to test out this MySQL Database  
> Master and
> Slave Server


It's easy to test - log into each instance. Do an insert on one and it  
should be available pretty much immediately from the other, then test  
it in the other direction. Then try the same thing for deletes. After  
that, you just need to monitor the 'seconds_behind_master' field in  
the output from "show slave status" on each.

Most guides neglect to tell you about:

mysqldump -u root -p --opt --quick --single-transaction --master-data  
mydb > mydb.sql

If you're using InnoDB everywhere, this will get you a data dump in a  
consistent state without having to lock tables or otherwise block  
access to your DB while you're setting up the replication.

Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@... | http://www.synchromedia.co.uk/

[Kaushal Shriyan-2]

Hi Marcus,

Thanks for the quick reply.

On Mon, Apr 7, 2008 at 3:54 PM, Marcus Bointon <marcus@...>
wrote:

> On 7 Apr 2008, at 11:09, Kaushal Shriyan wrote:
>
> > is this a correct documentation
> > http://howtoforge.com/mysql_master_master_replication for Master Slave
> > Replication
> >
>
> Well, it's for master-master, not master-slave. Also see
> http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
>
>  My requirement was if the Master goes down, the slave should take care
> > and
> > act as Master. the Master should acts as slave and reversing of this
> > role.
> > is this possible
> >
>
> Yes. With master-master, neither machine is considered a slave (actually
> they are both slaves too). However, there are good practical reasons to
> treat one as a master, for example it may be on superior hardware, or you
> need to ensure transactional integrity which you can't do spread across
> servers.
>
> When you have master-master up and running, you need to deal with failover
> at the application level - so your app needs to know about both servers and
> know to try the other one if its default connection fails.
>

In that case so setting up Master-Master will serve my purpose of  "if the
Master goes down, the slave should take care and
act as Master. the Master should acts as slave and reversing of this role."

Am i correct.

>
>  Also how can i test this setup from the MySQL client. Any test cases
> > available would be of great help to test out this MySQL Database Master
> > and
> > Slave Server
> >
>
>
> It's easy to test - log into each instance. Do an insert on one and it
> should be available pretty much immediately from the other, then test it in
> the other direction. Then try the same thing for deletes. After that, you
> just need to monitor the 'seconds_behind_master' field in the output from
> "show slave status" on each.
>
> Most guides neglect to tell you about:
>
> mysqldump -u root -p --opt --quick --single-transaction --master-data mydb
> > mydb.sql
>

I believe the above command does a full backup of database "mydb" In that
case whats the best usage of the mysqldump command to restore it in the
other host

>
> If you're using InnoDB everywhere, this will get you a data dump in a
> consistent state without having to lock tables or otherwise block access to
> your DB while you're setting up the replication.
>
> Marcus
> --
> Marcus Bointon
> Synchromedia Limited: Creators of http://www.smartmessages.net/
> UK resellers of info@hand CRM solutions
> marcus@... | http://www.synchromedia.co.uk/
>
>
>

Thanks and Regards

Kaushal

[Marcus Bointon]

On 7 Apr 2008, at 11:34, Kaushal Shriyan wrote:

> In that case so setting up Master-Master will serve my purpose of  
> "if the Master goes down, the slave should take care and
> act as Master. the Master should acts as slave and reversing of this  
> role."
>
> Am i correct.

Well, yes, but in fact you don't have to do anything at all at the  
server end - they are continuously acting as both master and slave, so  
all you need to do is change where your app points. You can then  
rebuild/fix your other server, let it catch up with the other server's  
binary logs, and you're back where you were with no down time.

> mysqldump -u root -p --opt --quick --single-transaction --master-
> data mydb > mydb.sql
>
> I believe the above command does a full backup of database "mydb" In  
> that case whats the best usage of the mysqldump command to restore  
> it in the other host


Yes. To import it you'd typically do:

mysql -u root -p < mydb.sql

or interactively using \. in the mysql client.

Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@... | http://www.synchromedia.co.uk/

[Kaushal Shriyan-2]

On Mon, Apr 7, 2008 at 4:15 PM, Marcus Bointon <marcus@...>
wrote:

> On 7 Apr 2008, at 11:34, Kaushal Shriyan wrote:
>
>  In that case so setting up Master-Master will serve my purpose of  "if
> > the Master goes down, the slave should take care and
> > act as Master. the Master should acts as slave and reversing of this
> > role."
> >
> > Am i correct.
> >
>
> Well, yes, but in fact you don't have to do anything at all at the server
> end - they are continuously acting as both master and slave, so all you need
> to do is change where your app points. You can then rebuild/fix your other
> server, let it catch up with the other server's binary logs, and you're back
> where you were with no down time.
>
>  mysqldump -u root -p --opt --quick --single-transaction --master-data
> > mydb > mydb.sql
> >
> > I believe the above command does a full backup of database "mydb" In
> > that case whats the best usage of the mysqldump command to restore it in the
> > other host
> >
>
>
> Yes. To import it you'd typically do:
>
> mysql -u root -p < mydb.sql
>
> or interactively using \. in the mysql client.
>
>
> Marcus
> --
> Marcus Bointon
> Synchromedia Limited: Creators of http://www.smartmessages.net/
> UK resellers of info@hand CRM solutions
> marcus@... | http://www.synchromedia.co.uk/
>
>
>

Thanks Marcus :-)

Marcus, do you have a step by step guide for configuring MySQL Database
Master Master server for my requirement

Thanks in Advance

Thanks and Regards

Kaushal

[Marcus Bointon]

> Marcus, do you have a step by step guide for configuring MySQL  
> Database
> Master Master server for my requirement

Well, the link I posted before is pretty good: http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
The MySQL docs are good too: http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

That's all I've ever used.

Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@... | http://www.synchromedia.co.uk/

[Kaushal Shriyan-2]

On Mon, Apr 7, 2008 at 6:06 PM, Marcus Bointon <marcus@...>
wrote:

> Marcus, do you have a step by step guide for configuring MySQL Database
> > Master Master server for my requirement
> >
>
> Well, the link I posted before is pretty good:
> http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html
> The MySQL docs are good too:
> http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html
>
> That's all I've ever used.
>
>
> Marcus
> --
> Marcus Bointon
> Synchromedia Limited: Creators of http://www.smartmessages.net/
> UK resellers of info@hand CRM solutions
> marcus@... | http://www.synchromedia.co.uk/
>
>
>

Hi Marcus

Can i replicate multiple MySQL Database from Master to Slave.

Lets say I have temp1 and temp2 database on Master MySQL Database Server. I
need both the temp1 and temp2 database on the slave

is that possible.

Thanks and Regards

Kaushal

[Marcus Bointon]

On 10 Apr 2008, at 07:39, Kaushal Shriyan wrote:

> Can i replicate multiple MySQL Database from Master to Slave.
>
> Lets say I have temp1 and temp2 database on Master MySQL Database  
> Server. I need both the temp1 and temp2 database on the slave


Yes. See the replicate-do-db option. Note that you can't replicate  
from more than master to the same slave unless you run multiple  
instances of MySQL on the slave.

Marcus
--
Marcus Bointon
Synchromedia Limited: Creators of http://www.smartmessages.net/
UK resellers of info@hand CRM solutions
marcus@... | http://www.synchromedia.co.uk/