RE: Another replication question

[Gavin Towey]

If you use a chain of masters, you can accomplish the same effect:

Master1->Master2->Master->3->Slave

However I don't recommend this since the more links you have in a replication 
chain, the easier it is to break it in a way that's very not-fun to deal with.


-Original Message-
From: Rolando Edwards [mailto:redwa...@logicworks.net]
Sent: Wednesday, November 24, 2010 6:53 AM
To: Machiel Richards; mysql mailing list
Subject: RE: Another replication question

MySQL, by design, cannot do that.

A DB Server can be Master to Multiple Slaves
Think of the CHANGE MASTER TO command.
Its internal design cannot accommodate reading from more than one set of relay 
logs.

You could attempt something convoluted, like
1) STOP SLAVE;
2) CHANGE MASTER TO 
3) START SLAVE;
4) Read and process some binary log transactions, wait till you are zero sec 
behind master
5) STOP SLAVE;
6) CHANGE MASTER TO 
7) START SLAVE;
8) Read some process binary log transactions, wait till you are zero sec behind 
master
9) Repeat from step 1

Make sure Each Master is updating only one specific set of databases, mutual 
exclusive from other Masters
Make sure you properly record the log file and log position from each master

I would never try this under normal circumstances.

I think this was described in the "High Performance MySQL" book
http://www.amazon.com/dp/0596101716?tag=xaprb-20


Rolando A. Edwards
MySQL DBA (CMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: Machiel Richards [mailto:machi...@rdc.co.za]
Sent: Wednesday, November 24, 2010 7:20 AM
To: mysql mailing list
Subject: Another replication question

Hi All

I am back once again with another replication question (maybe this
can also be handled by MMM but not sure) this time for a different
client.

We are trying to find out how to setup 3 different masters to
replicate to a single slave server (without the need to have 3 different
instances running on the slave machine).

Does anybody have any ideas?

Any ideas will be greatly appreciated.

Regards
Machiel

This message contains confidential information and is intended only for the 
individual named.  If you are not the named addressee, you are notified that 
reviewing, disseminating, disclosing, copying or distributing this e-mail is 
strictly prohibited.  Please notify the sender immediately by e-mail if you 
have received this e-mail by mistake and delete this e-mail from your system. 
E-mail transmission cannot be guaranteed to be secure or error-free as 
information could be intercepted, corrupted, lost, destroyed, arrive late or 
incomplete, or contain viruses. The sender therefore does not accept liability 
for any loss or damage caused by viruses or errors or omissions in the contents 
of this message, which arise as a result of e-mail transmission. [FriendFinder 
Networks, Inc., 220 Humboldt Court, Sunnyvale, CA 94089, USA, FriendFinder.com

RE: localhost vs domain for connection string

[Jerry Schwartz]

>-Original Message-
>From: vegiv...@gmail.com [mailto:vegiv...@gmail.com] On Behalf Of Johan De
>Meersman
>Sent: Wednesday, November 24, 2010 2:39 AM
>To: Jerry Schwartz
>Cc: Brent Clark; mysql mailing list
>Subject: Re: localhost vs domain for connection string
>
>On Tue, Nov 23, 2010 at 7:55 PM, Jerry Schwartz  wrote:
>
>> >IIRC, "localhost" is seen by the client as a magic word to mean "use the
>> >UNIX socket, not 127.0.0.1".
>> >
>> [JS] IF it is enabled in my.cnf.
>>
>Hmm, didn't know that bit. What's the option called ?
>
[JS] This might or might not be enabled by default. I'm running on Windows, 
and I seem to remember having to change it.

# SERVER SECTION
# --
#
# The following options will be read by the MySQL Server. Make sure that
# you have installed the server correctly (see above) so it reads this
# file.
#
[mysqld]

# The TCP/IP Port the MySQL Server will listen on
port=3306

# Enable named pipe, bypassing the network stack
enable-named-pipe

=
Regards,

Jerry Schwartz
Global Information Incorporated
195 Farmington Ave.
Farmington, CT 06032

860.674.8796 / FAX: 860.674.8341
E-mail: je...@gii.co.jp
Web site: www.the-infoshop.com




>--
>Bier met grenadyn
>Is als mosterd by den wyn
>Sy die't drinkt, is eene kwezel
>Hy die't drinkt, is ras een ezel




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org

Re: SQLStats 1.1 available

[Anders Karlsson]

If you downloaded this earlier today, note that there is now a 1.2 
version available, that also shows ROWS_EXAMINED in INFORMATION_SCHEMA 
tables.


/Karlsson
Claudio Nanni wrote On 2010-11-24 09:02:


Cool!

On Nov 24, 2010 8:46 AM, "Anders Karlsson" > wrote:

> SQLStats is a MySQL 5.5 plugin that allows MySQL SQL Statement
> monitoring in "real time", without any Proxies, source code
> modifications, different connectors or anything. Read more on my 
blog here:
> 
http://karlssonondatabases.blogspot.com/2010/11/monitoring-mysql-sql-statement-way-it.html

>
> The plugin is GPL and is downloadable from sourceforge here:
> https://sourceforge.net/projects/sqlstats/
>
> Best regards
> Anders Karlsson
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: 
http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com

>

RE: Another replication question

[Rolando Edwards]

MySQL, by design, cannot do that.

A DB Server can be Master to Multiple Slaves
Think of the CHANGE MASTER TO command.
Its internal design cannot accommodate reading from more than one set of relay 
logs.

You could attempt something convoluted, like
1) STOP SLAVE;
2) CHANGE MASTER TO 
3) START SLAVE;
4) Read and process some binary log transactions, wait till you are zero sec 
behind master
5) STOP SLAVE;
6) CHANGE MASTER TO 
7) START SLAVE;
8) Read some process binary log transactions, wait till you are zero sec behind 
master
9) Repeat from step 1

Make sure Each Master is updating only one specific set of databases, mutual 
exclusive from other Masters
Make sure you properly record the log file and log position from each master

I would never try this under normal circumstances.

I think this was described in the "High Performance MySQL" book
http://www.amazon.com/dp/0596101716?tag=xaprb-20


Rolando A. Edwards
MySQL DBA (CMDBA)

155 Avenue of the Americas, Fifth Floor
New York, NY 10013
212-625-5307 (Work)
201-660-3221 (Cell)
AIM & Skype : RolandoLogicWorx
redwa...@logicworks.net
http://www.linkedin.com/in/rolandoedwards


-Original Message-
From: Machiel Richards [mailto:machi...@rdc.co.za] 
Sent: Wednesday, November 24, 2010 7:20 AM
To: mysql mailing list
Subject: Another replication question

Hi All

I am back once again with another replication question (maybe this
can also be handled by MMM but not sure) this time for a different
client.

We are trying to find out how to setup 3 different masters to
replicate to a single slave server (without the need to have 3 different
instances running on the slave machine).

Does anybody have any ideas?

Any ideas will be greatly appreciated.

Regards
Machiel

Re: Another replication question

[Johan De Meersman]

On Wed, Nov 24, 2010 at 2:50 PM, John Daisley wrote:

> Are you sure mmm couldn't handle this?
>

That, I don't know, but MySQL's internal replication mechanisms definitely
don't support multimaster slaves. If mmm does it, it'll likely be akin to
the offline log shipping I proposed earlier.


> Maybe you could `simulate` the whole thing using some federated tables on
> one of the slave instances? Then although you are replicating to 3
> instances
> you could access all the data from a single instance?
>

Purely with federated tables, you're not actually replicating. You'd have
the data visible on the "slave", but it would be useless for
failover/backup; and it would be less than optimal for querying since you'll
be pulling all the data off the primaries on every select anyway.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Another replication question

[John Daisley]

Johan,

Are you sure mmm couldn't handle this?

Machiel,

Maybe you could `simulate` the whole thing using some federated tables on
one of the slave instances? Then although you are replicating to 3 instances
you could access all the data from a single instance?

Regards

John

On 24 November 2010 13:08, Johan De Meersman  wrote:

> You can't, plain and simple - a slave may only have a single master.
>
> You could script binlog shipping, I suppose, if you're so inclined, and
> apply them on the slave. No guarantees about it working as expected,
> though.
>
>
> On Wed, Nov 24, 2010 at 1:20 PM, Machiel Richards  >wrote:
>
> > Hi All
> >
> >I am back once again with another replication question (maybe this
> > can also be handled by MMM but not sure) this time for a different
> > client.
> >
> >We are trying to find out how to setup 3 different masters to
> > replicate to a single slave server (without the need to have 3 different
> > instances running on the slave machine).
> >
> >Does anybody have any ideas?
> >
> >Any ideas will be greatly appreciated.
> >
> > Regards
> > Machiel
> >
>
>
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



-- 
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.dais...@butterflysystems.co.uk

Re: Mysql server full with idle connections

[Johan De Meersman]

On Wed, Nov 24, 2010 at 1:13 PM, Nigel Wood  wrote:

> Quick thought: what is your idle timeout set to on the MySQL server?
> Could you have configured it to reap these idle connections?
>

I could, probably, but the applications are generally well-behaved, and it's
not a recurring problem. I hope. I'm more interested in the cause, atm.


On Wed, Nov 24, 2010 at 1:58 PM, John Daisley wrote:

> Seen this a lot with poorly written web apps which open connections but
> dont
> close them when finished. Try setting wait_timeout and/or
> interactive_timeout to close unused connections.
>


Well, yes, but as far as we're aware nothing new has been deployed - this
setup is several years old. I suppose it's possible that one of those kind
of bugs is hiding somewhere in a forgotten corner of code, but given that
we're running Drupal and Wordpress, I'd be surprised at something like that
remaining unnoticed for so long.


-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Another replication question

[Johan De Meersman]

You can't, plain and simple - a slave may only have a single master.

You could script binlog shipping, I suppose, if you're so inclined, and
apply them on the slave. No guarantees about it working as expected, though.


On Wed, Nov 24, 2010 at 1:20 PM, Machiel Richards wrote:

> Hi All
>
>I am back once again with another replication question (maybe this
> can also be handled by MMM but not sure) this time for a different
> client.
>
>We are trying to find out how to setup 3 different masters to
> replicate to a single slave server (without the need to have 3 different
> instances running on the slave machine).
>
>Does anybody have any ideas?
>
>Any ideas will be greatly appreciated.
>
> Regards
> Machiel
>



-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: Mysql server full with idle connections

[John Daisley]

Seen this a lot with poorly written web apps which open connections but dont
close them when finished. Try setting wait_timeout and/or
interactive_timeout to close unused connections.

John



On 24 November 2010 11:27, Johan De Meersman  wrote:

> Hey,
>
> Late last night, I got a call that one of our servers was down. Turns out
> the machine had all 2500 connection slots in use, but none of them appeared
> to be doing anything: hardly any CPU was being used, load 0.05, and tcpdump
> confirmed that pretty much no data was being sent, either.
>
> Disks were writeable - so not hanging write flood - and when I eventually
> sent a kill, the daemon did a clean shutdown and came back up without a
> hitch.
>
> Unfortunately I couldn't log in while this was going on because I never
> configured a few extra connections for super - that's been fixed by now.
>
> Munin shows no discernable cause, no ramp-up on the load or whatever on
> either the DB or the webservers; just a very sudden increase in open
> connections and an equally sudden drop in network traffic.
>
> Neither slowlog nor sys/errorlog shows anything out of the ordinary.
>
> I can think of only two causes: bug in the app code, or bug in MySQL. This
> is 5.0.32-Debian_7etch3-log (Debian package) on Debian Etch, running on
> x86_64.
>
> Does anyone know of similar things happening ?
>
> --
> Bier met grenadyn
> Is als mosterd by den wyn
> Sy die't drinkt, is eene kwezel
> Hy die't drinkt, is ras een ezel
>



-- 
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.dais...@butterflysystems.co.uk

Another replication question

[Machiel Richards]

Hi All

I am back once again with another replication question (maybe this
can also be handled by MMM but not sure) this time for a different
client.

We are trying to find out how to setup 3 different masters to
replicate to a single slave server (without the need to have 3 different
instances running on the slave machine).

Does anybody have any ideas?

Any ideas will be greatly appreciated.

Regards
Machiel

Mysql server full with idle connections

[Johan De Meersman]

Hey,

Late last night, I got a call that one of our servers was down. Turns out
the machine had all 2500 connection slots in use, but none of them appeared
to be doing anything: hardly any CPU was being used, load 0.05, and tcpdump
confirmed that pretty much no data was being sent, either.

Disks were writeable - so not hanging write flood - and when I eventually
sent a kill, the daemon did a clean shutdown and came back up without a
hitch.

Unfortunately I couldn't log in while this was going on because I never
configured a few extra connections for super - that's been fixed by now.

Munin shows no discernable cause, no ramp-up on the load or whatever on
either the DB or the webservers; just a very sudden increase in open
connections and an equally sudden drop in network traffic.

Neither slowlog nor sys/errorlog shows anything out of the ordinary.

I can think of only two causes: bug in the app code, or bug in MySQL. This
is 5.0.32-Debian_7etch3-log (Debian package) on Debian Etch, running on
x86_64.

Does anyone know of similar things happening ?

-- 
Bier met grenadyn
Is als mosterd by den wyn
Sy die't drinkt, is eene kwezel
Hy die't drinkt, is ras een ezel

Re: MMM setup and requirements

[John Daisley]

>
>
>1. From what I understand the setup requires a
> management server (3rd machine) which will be used to monitor and manage
> the two database nodes and the replication.
>- Am I correct in thinking that this will have
> to be almost the same specs seeing that it will be receiving the sam
> amount of traffic which the single master would have received
> originally?
>
> A low end machine, eg an old desktop will do the job of the monitor.



>
>2. Master-master replication will mean that the data
> needs to be replicated to both machines everytime thus it may be viable
> for high availability but in terms of load balancing / performance it
> will degrade the performance. Am I correct?
>
mmm will do read balancing


>
>3. I have also gone through about 6 websites now for the
> installation procedures and thus far all of them have different
> installation documentation... is there someone with a link or resource
> for a reliable installation (RHEL 5/mysql 5.1.5_x64)
>
>
http://mysql-mmm.org/mysql-mmm.html



>
>I would also be glad for any other suggestions,
> warnings, etc... in this regard.
>
>My personal feeling from the research thus far is that
> the current master/slave setup will be the best option for performance
> as that has been their main concern recently and during a system failure
> it is rather quick and easy enough to get the slave running as the
> master.
>
>I do  not have as much experience on this yet so I would
> really appreciate assistance.
>
> Regards
> Machiel
>



-- 
John Daisley

Certified MySQL 5 Database Administrator
Certified MySQL 5 Developer
Cognos BI Developer

Telephone: +44 (0)7918 621621
Email: john.dais...@butterflysystems.co.uk

MMM setup and requirements

[Machiel Richards]

Good day all

  I was asked to research the MMM multi master setup and viability
for a specific environment in order to assist with load balancing and
high availability.

I do however have some questions and hope that someone out there
have had enough experience to assist me with this as I am starting to
think that the option is not viable for their requirements.

my questions:

1. From what I understand the setup requires a
management server (3rd machine) which will be used to monitor and manage
the two database nodes and the replication.
- Am I correct in thinking that this will have
to be almost the same specs seeing that it will be receiving the sam
amount of traffic which the single master would have received
originally?
- I may be wrong here but that is what my
understanding is from how MMM works, however they specify it does not
need to be state of the art (confusing).

2. Master-master replication will mean that the data
needs to be replicated to both machines everytime thus it may be viable
for high availability but in terms of load balancing / performance it
will degrade the performance. Am I correct?

3. I have also gone through about 6 websites now for the
installation procedures and thus far all of them have different
installation documentation... is there someone with a link or resource
for a reliable installation (RHEL 5/mysql 5.1.5_x64)


I would also be glad for any other suggestions,
warnings, etc... in this regard.

My personal feeling from the research thus far is that
the current master/slave setup will be the best option for performance
as that has been their main concern recently and during a system failure
it is rather quick and easy enough to get the slave running as the
master.

I do  not have as much experience on this yet so I would
really appreciate assistance.

Regards
Machiel

Re: SQLStats 1.1 available

[Claudio Nanni]

Cool!
On Nov 24, 2010 8:46 AM, "Anders Karlsson" 
wrote:
> SQLStats is a MySQL 5.5 plugin that allows MySQL SQL Statement
> monitoring in "real time", without any Proxies, source code
> modifications, different connectors or anything. Read more on my blog
here:
>
http://karlssonondatabases.blogspot.com/2010/11/monitoring-mysql-sql-statement-way-it.html
>
> The plugin is GPL and is downloadable from sourceforge here:
> https://sourceforge.net/projects/sqlstats/
>
> Best regards
> Anders Karlsson
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=claudio.na...@gmail.com
>