Re: Replication and user privileges

[Jim]

On 2/26/2019 1:57 PM, Jim wrote:

On 2/26/2019 9:44 AM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 7:29 PM, Jim wrote:

On 2/25/2019 5:46 PM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 5:04 PM, Jim wrote:
I have a question about mysql replication. I believe I understand 
most

of it, but have a question about user privileges.

I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave 





My question is about what replication-related users and privileges 
must

exist on the slave.

So, for example, if an insert on the master that is to be 
replicated is

performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to 
exist on

the slave as well?

In other words, what user is performing the replication operation 
on the

slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user 
privileges?


Thank you.
Jim



Your final supposition is correct. All privileges were checked and
verified on the master when the original command was executed. The
Replication system on the slave is going to repeat that change as well
as possible given the state of its copy of the data without regards to
"who originally performed this change" on the upstream master.

We do not store credentials in the Binary Log because they are not
important to either of the purposes of the Binary Log

* point-in-time recovery
or
* Replication (which is very much like an automated, continuous
point-in-time recovery)

===

That replication account you mentioned, on the master, is required to
give a slave (and you could have several) enough rights to read the
Binary Log and not much else. This allows you to create an account
that can login from a remote location with the "least privileges"
necessary to do its job. This minimizes your data's exposure should
that account become compromised.

Many other accounts could also have the REPL_SLAVE_PRIV privilege and
any of those could be used by a slave to do the same job. However
losing control over one of those more privileged accounts could pose a
higher risk to your data.




Thanks, Shawn. Your response confirms what I had assumed was happening.

So bottom line... what I plan to do is strip the various
insert/update/delete privileges from appropriate db users on my slaves.
I had placed them there originally because I thought they would be
needed for the replicated queries, but not true based on your response.

I only want the various mysql users used by my code to have select 
privs
on the slaves so that if somehow a slave was mistakenly written to 
via a
bug in my code, that write would fail and I would receive the error. 
The
slaves should only be used for selects and should never experience a 
write.


That would make sense based on our discussion, correct?

Thanks again.
Jim



As masters and slaves can exchange "positions" or "roles" (it depends 
on how you like to mentally visualize the relationship) within a 
replication graph in a failover situation, adding time to 
re-establish actual permissions using GRANT commands to reset user 
accounts to their old privileges may not be time you want to spend.


A cleaner, simpler solution is to set the --super-read-only flag in 
the server:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only 



That way, you get the behavior you want (no writes to a read-only 
slave) without forcing differences to the content of your privileges 
tables within different nodes of your Replication setup.  Each node 
will remain a transactionally consistent copy of all the others 
(within the temporal limits of replication being an asynchronous 
process).


Yours,



Thanks, Shawn.

super-read-only looks perfect for what I want. I can keep my slaves 
with all the potential users needed to take over as master without 
risking unwanted writes.


Given how you read:
"If the |read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only> 
system variable is enabled, the server permits client updates only 
from users who have the |SUPER| 
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super> 
privilege. If the |super_read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only> 
system variable is also enabled, the server prohibits client updates 
even from users who have |SUPER| 
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super>."
One somewhat gets the impression that in order to

Re: Replication and user privileges

[Jim]

On 2/26/2019 9:44 AM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 7:29 PM, Jim wrote:

On 2/25/2019 5:46 PM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 5:04 PM, Jim wrote:

I have a question about mysql replication. I believe I understand most
of it, but have a question about user privileges.

I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave 





My question is about what replication-related users and privileges 
must

exist on the slave.

So, for example, if an insert on the master that is to be 
replicated is

performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to 
exist on

the slave as well?

In other words, what user is performing the replication operation 
on the

slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user 
privileges?


Thank you.
Jim



Your final supposition is correct. All privileges were checked and
verified on the master when the original command was executed. The
Replication system on the slave is going to repeat that change as well
as possible given the state of its copy of the data without regards to
"who originally performed this change" on the upstream master.

We do not store credentials in the Binary Log because they are not
important to either of the purposes of the Binary Log

* point-in-time recovery
or
* Replication (which is very much like an automated, continuous
point-in-time recovery)

===

That replication account you mentioned, on the master, is required to
give a slave (and you could have several) enough rights to read the
Binary Log and not much else. This allows you to create an account
that can login from a remote location with the "least privileges"
necessary to do its job. This minimizes your data's exposure should
that account become compromised.

Many other accounts could also have the REPL_SLAVE_PRIV privilege and
any of those could be used by a slave to do the same job. However
losing control over one of those more privileged accounts could pose a
higher risk to your data.




Thanks, Shawn. Your response confirms what I had assumed was happening.

So bottom line... what I plan to do is strip the various
insert/update/delete privileges from appropriate db users on my slaves.
I had placed them there originally because I thought they would be
needed for the replicated queries, but not true based on your response.

I only want the various mysql users used by my code to have select privs
on the slaves so that if somehow a slave was mistakenly written to via a
bug in my code, that write would fail and I would receive the error. The
slaves should only be used for selects and should never experience a 
write.


That would make sense based on our discussion, correct?

Thanks again.
Jim



As masters and slaves can exchange "positions" or "roles" (it depends 
on how you like to mentally visualize the relationship) within a 
replication graph in a failover situation, adding time to re-establish 
actual permissions using GRANT commands to reset user accounts to 
their old privileges may not be time you want to spend.


A cleaner, simpler solution is to set the --super-read-only flag in 
the server:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only 



That way, you get the behavior you want (no writes to a read-only 
slave) without forcing differences to the content of your privileges 
tables within different nodes of your Replication setup.  Each node 
will remain a transactionally consistent copy of all the others 
(within the temporal limits of replication being an asynchronous 
process).


Yours,



Thanks, Shawn.

super-read-only looks perfect for what I want. I can keep my slaves with 
all the potential users needed to take over as master without risking 
unwanted writes.


Given how you read:
"If the |read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_read_only> 
system variable is enabled, the server permits client updates only from 
users who have the |SUPER| 
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super> 
privilege. If the |super_read_only| 
<https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only> 
system variable is also enabled, the server prohibits client updates 
even from users who have |SUPER| 
<https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_super>."
One somewhat gets the impression that in order to enable 
super_read_only, one must also ena

Re: Replication and user privileges

[shawn l.green]

Hello Jim,

On 2/25/2019 7:29 PM, Jim wrote:

On 2/25/2019 5:46 PM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 5:04 PM, Jim wrote:

I have a question about mysql replication. I believe I understand most
of it, but have a question about user privileges.

I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave



My question is about what replication-related users and privileges must
exist on the slave.

So, for example, if an insert on the master that is to be replicated is
performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to exist on
the slave as well?

In other words, what user is performing the replication operation on the
slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user privileges?

Thank you.
Jim



Your final supposition is correct. All privileges were checked and
verified on the master when the original command was executed. The
Replication system on the slave is going to repeat that change as well
as possible given the state of its copy of the data without regards to
"who originally performed this change" on the upstream master.

We do not store credentials in the Binary Log because they are not
important to either of the purposes of the Binary Log

* point-in-time recovery
or
* Replication (which is very much like an automated, continuous
point-in-time recovery)

===

That replication account you mentioned, on the master, is required to
give a slave (and you could have several) enough rights to read the
Binary Log and not much else. This allows you to create an account
that can login from a remote location with the "least privileges"
necessary to do its job. This minimizes your data's exposure should
that account become compromised.

Many other accounts could also have the REPL_SLAVE_PRIV privilege and
any of those could be used by a slave to do the same job. However
losing control over one of those more privileged accounts could pose a
higher risk to your data.




Thanks, Shawn. Your response confirms what I had assumed was happening.

So bottom line... what I plan to do is strip the various
insert/update/delete privileges from appropriate db users on my slaves.
I had placed them there originally because I thought they would be
needed for the replicated queries, but not true based on your response.

I only want the various mysql users used by my code to have select privs
on the slaves so that if somehow a slave was mistakenly written to via a
bug in my code, that write would fail and I would receive the error. The
slaves should only be used for selects and should never experience a write.

That would make sense based on our discussion, correct?

Thanks again.
Jim



As masters and slaves can exchange "positions" or "roles" (it depends on 
how you like to mentally visualize the relationship) within a 
replication graph in a failover situation, adding time to re-establish 
actual permissions using GRANT commands to reset user accounts to their 
old privileges may not be time you want to spend.


A cleaner, simpler solution is to set the --super-read-only flag in the 
server:

https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_super_read_only

That way, you get the behavior you want (no writes to a read-only slave) 
without forcing differences to the content of your privileges tables 
within different nodes of your Replication setup.  Each node will remain 
a transactionally consistent copy of all the others (within the temporal 
limits of replication being an asynchronous process).


Yours,

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication and user privileges

[Jim]

On 2/25/2019 5:46 PM, shawn l.green wrote:

Hello Jim,

On 2/25/2019 5:04 PM, Jim wrote:

I have a question about mysql replication. I believe I understand most
of it, but have a question about user privileges.

I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave 




My question is about what replication-related users and privileges must
exist on the slave.

So, for example, if an insert on the master that is to be replicated is
performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to exist on
the slave as well?

In other words, what user is performing the replication operation on the
slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user privileges?

Thank you.
Jim



Your final supposition is correct. All privileges were checked and 
verified on the master when the original command was executed. The 
Replication system on the slave is going to repeat that change as well 
as possible given the state of its copy of the data without regards to 
"who originally performed this change" on the upstream master.


We do not store credentials in the Binary Log because they are not 
important to either of the purposes of the Binary Log


* point-in-time recovery
or
* Replication (which is very much like an automated, continuous 
point-in-time recovery)


===

That replication account you mentioned, on the master, is required to 
give a slave (and you could have several) enough rights to read the 
Binary Log and not much else. This allows you to create an account 
that can login from a remote location with the "least privileges" 
necessary to do its job. This minimizes your data's exposure should 
that account become compromised.


Many other accounts could also have the REPL_SLAVE_PRIV privilege and 
any of those could be used by a slave to do the same job. However 
losing control over one of those more privileged accounts could pose a 
higher risk to your data.





Thanks, Shawn. Your response confirms what I had assumed was happening.

So bottom line... what I plan to do is strip the various 
insert/update/delete privileges from appropriate db users on my slaves.
I had placed them there originally because I thought they would be 
needed for the replicated queries, but not true based on your response.


I only want the various mysql users used by my code to have select privs 
on the slaves so that if somehow a slave was mistakenly written to via a 
bug in my code, that write would fail and I would receive the error. The 
slaves should only be used for selects and should never experience a write.


That would make sense based on our discussion, correct?

Thanks again.
Jim

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication and user privileges

[shawn l.green]

Hello Jim,

On 2/25/2019 5:04 PM, Jim wrote:

I have a question about mysql replication. I believe I understand most
of it, but have a question about user privileges.

I understand on the master, the replication user must have the
Repl_slave_priv privilege as described here:
https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave


My question is about what replication-related users and privileges must
exist on the slave.

So, for example, if an insert on the master that is to be replicated is
performed by user 'abc' with proper insert permissions on the master,
does that same 'abc' user with same insert permissions need to exist on
the slave as well?

In other words, what user is performing the replication operation on the
slave? I don't see any indication of users referenced in the bin logs
that I have examined on the master. Are user and privileges regarding
replicated queries irrelevant on the slave and that is handled all
internally via the replication thread with no regard to user privileges?

Thank you.
Jim



Your final supposition is correct. All privileges were checked and 
verified on the master when the original command was executed. The 
Replication system on the slave is going to repeat that change as well 
as possible given the state of its copy of the data without regards to 
"who originally performed this change" on the upstream master.


We do not store credentials in the Binary Log because they are not 
important to either of the purposes of the Binary Log


* point-in-time recovery
or
* Replication (which is very much like an automated, continuous 
point-in-time recovery)


===

That replication account you mentioned, on the master, is required to 
give a slave (and you could have several) enough rights to read the 
Binary Log and not much else. This allows you to create an account that 
can login from a remote location with the "least privileges" necessary 
to do its job. This minimizes your data's exposure should that account 
become compromised.


Many other accounts could also have the REPL_SLAVE_PRIV privilege and 
any of those could be used by a slave to do the same job. However losing 
control over one of those more privileged accounts could pose a higher 
risk to your data.



--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Integrated Cloud Applications & Platform Services
Office: Blountville, TN

Become certified in MySQL! Visit https://www.mysql.com/certification/ 
for details.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Replication and user privileges

[Jim]

I have a question about mysql replication. I believe I understand most 
of it, but have a question about user privileges.


I understand on the master, the replication user must have the 
Repl_slave_priv privilege as described here:

https://dev.mysql.com/doc/refman/8.0/en/privileges-provided.html#priv_replication-slave

My question is about what replication-related users and privileges must 
exist on the slave.


So, for example, if an insert on the master that is to be replicated is 
performed by user 'abc' with proper insert permissions on the master, 
does that same 'abc' user with same insert permissions need to exist on 
the slave as well?


In other words, what user is performing the replication operation on the 
slave? I don't see any indication of users referenced in the bin logs 
that I have examined on the master. Are user and privileges regarding 
replicated queries irrelevant on the slave and that is handled all 
internally via the replication thread with no regard to user privileges?


Thank you.
Jim

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Network throughput for mysql replication

[Machiel Richards]

Hi Guys

   I am once again having a weird issue... well weird to me anyway.

   We have a master,slave setup using mysql 5.7 and they are both
connected on the same network segment through the same switch.


  During the weekend a HUGE amount of processing was done on the master,
and thus resulted in the slave lagging and is currently on 213000
seconds behind master.


While troubleshooting, I found the following :

 - MySQL io thread (relay log positions) are lagging far behind what
is currently on master server.
  - SQL thread processes as the io thread are writing the relay log
with no lag there.

   As a test , I disabled sql thread and let the io thread sync binlog
data to relay log, while monitoring network traffic.

 The network traffic would max out at 6mb/s and as it needs to sync
over 200Gb of binary logs, this seems to be taking too long.

  I then suspected a network issue, however when using SCP to copy
data from the master server to slave server I get speeds of up to
80Mb/s for those copies.

   When I start the sql thread again after about 2 gb of data have
been synced, then it catches up within a minute.


So my question is this..

  are there any options to mysql that would be causing it to limit
the speed at which it sync binary logs.

  If not, how else can I speed up that process.


  FYI, the scp that was done was done to the same disks where the
relay logs are on in order to also confirm that it is not a disk
io issue, however that went more than good enough.

So from what we can see the bottleneck is specifically related to
slow sync over the network and seems to be only related to mysql.


  Any ideas here would be appreciated.


Regards




-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Pointers to Mysql Replication

[Jatin Davey]

On 7/6/2015 1:39 PM, Claudio Nanni wrote:

Hi Jatin,

Say if i am using a DB instance for quiet some time on node-1 and
it has data in it. If i decide to have replication with another DB
instance , will all the data from node-1 be first replicated to
node-2's DB ? Or will it just start the DB replication from the
point where i configured DB replication ?


Whatever method you use (traditional asynchronous Replication or 
Galera) when you add a new node (either a Replication Slave or a 
Galera Node) it has to acquire the full (current) dataset and then 
start replication from that point.


With Replication you need to do it manually by making a backup of the 
Master, with Galera it is automatic, the newly added node will request 
to be synced with the cluster and it will receive the backup from one 
of the nodes of the cluster.


Cheers
--
Claudio


Thanks for the responses Claudio. I will go with Master-Master 
replication and test it out.


Thanks
Jatin

Re: Pointers to Mysql Replication

[Jatin Davey]

On 7/5/2015 10:48 PM, Claudio Nanni wrote:

Hello Jatin,

> We are basically intending to implement High Availability for our 
application. In this regard , we want to use Mysql replication for 
this purpose.


Your scenario(MySQL Master/Slave with Failover/Failback procedures) 
has been a typical one for ages now, so I am sure many good advices 
about it will arrive.


While, I just want to bring into the table the possibility to use 
Galera replication which is synchronous*, multi master, and does not 
need failover/failback procedures, nodes syncs and desyncs automatically.
Indeed I believe that this simple Master/Slave traditional scenario 
can represent an interesting (base case) application for Galera, I'd 
like other expert opinions on this too.


Galera is of course a bit more complex so this has also to be taken 
into account.


*(It has an optimistic multinode transactional approach so that in 
some cases a transaction might be rolled back in case of conflicts.)


Cheers
--
Claudio

Thanks Claudio,

I think having a master-master replication is what i precisely need. 
Basically if my application writes on the DB on node-1 it should be 
replicated to the DB running on node-2. After a failover my application 
runs on node-2 and when it writes to the DB on node-2 it should be 
replicated to the DB on node-1.


I have a question here though,

Say if i am using a DB instance for quiet some time on node-1 and it has 
data in it. If i decide to have replication with another DB instance , 
will all the data from node-1 be first replicated to node-2's DB ? Or 
will it just start the DB replication from the point where i configured 
DB replication ?


Thanks
Jatin

Pointers to Mysql Replication

[Jatin Davey]

Hi All

We are basically intending to implement High Availability for our 
application. In this regard , we want to use Mysql replication for this 
purpose.


We would have a two-node cluster , say node-1 & node-2.

Assume that if node-1is functioning we want the DB changes on it to be 
replicated onto the DB of node-2. If node-1 goes down due to say power 
failure then node-2 will become active and it will start servicing 
application requests. And if node-1 is recovered again then any DB 
chages that were done on node-2 should be replicated again on node-1 so 
that in case of power failure in node-2 , node-1 can start the 
application and DB and start servicing application requests.


Appreciate if you can provide some pointers of reading in this regard 
and the type of solution that i should implement to achieve it.


Thanks
Jatin

Re: Multi-Master Asynchronous Replication

[Rodrigo Ferreira]

Sorry, I will check this = "with recent MariaDB, a slave can have multiple 
masters" 

 On Monday, November 24, 2014 2:26 PM, Rodrigo Ferreira 
 wrote:
   

 Thanks for your response.

In this case, circular replication is not apropriate because there are about 10 
to 15 nodes, and it is known that availability decreases on circular setup as 
the number of nodes increases.
Another reason (related to availability) is that nodes are eventually 
disconected, and one node disconnected (suppose one whole day disconnected) can 
delay the whole replication.
Any suggestion?
Rodrigo 

 On Monday, November 24, 2014 1:43 PM, Johan De Meersman 
 wrote:
   

 - Original Message -
> From: "Rodrigo Ferreira" 
> Subject: Multi-Master Asynchronous Replication

> Hi,
> Is that a way to make multi-master asynchronous replication with mysql ou
> external lib?
> I know galera cluster but it is synchronous.
> The problem is a set of eventually disconected nodes need to send all changes
> (when connected) to a master always connected node.

Yes, async is the default setup with MySQL replication; multimaster is possible 
using a circular setup (or, with recent MariaDB, a slave can have multiple 
masters); but it's not officially supported, and it's tricky to get right 
because of concurrent updates etc.

Careful with that :-)


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql





   

Re: Multi-Master Asynchronous Replication

[Rodrigo Ferreira]

Thanks for your response.

In this case, circular replication is not apropriate because there are about 10 
to 15 nodes, and it is known that availability decreases on circular setup as 
the number of nodes increases.
Another reason (related to availability) is that nodes are eventually 
disconected, and one node disconnected (suppose one whole day disconnected) can 
delay the whole replication.
Any suggestion?
Rodrigo 

 On Monday, November 24, 2014 1:43 PM, Johan De Meersman 
 wrote:
   

 - Original Message -
> From: "Rodrigo Ferreira" 
> Subject: Multi-Master Asynchronous Replication

> Hi,
> Is that a way to make multi-master asynchronous replication with mysql ou
> external lib?
> I know galera cluster but it is synchronous.
> The problem is a set of eventually disconected nodes need to send all changes
> (when connected) to a master always connected node.

Yes, async is the default setup with MySQL replication; multimaster is possible 
using a circular setup (or, with recent MariaDB, a slave can have multiple 
masters); but it's not officially supported, and it's tricky to get right 
because of concurrent updates etc.

Careful with that :-)


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql



   

Re: Multi-Master Asynchronous Replication

[Johan De Meersman]

- Original Message -
> From: "Rodrigo Ferreira" 
> Subject: Multi-Master Asynchronous Replication

> Hi,
> Is that a way to make multi-master asynchronous replication with mysql ou
> external lib?
> I know galera cluster but it is synchronous.
> The problem is a set of eventually disconected nodes need to send all changes
> (when connected) to a master always connected node.

Yes, async is the default setup with MySQL replication; multimaster is possible 
using a circular setup (or, with recent MariaDB, a slave can have multiple 
masters); but it's not officially supported, and it's tricky to get right 
because of concurrent updates etc.

Careful with that :-)


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Multi-Master Asynchronous Replication

[Rodrigo Ferreira]

Hi,
Is that a way to make multi-master asynchronous replication with mysql ou 
external lib?
I know galera cluster but it is synchronous.
The problem is a set of eventually disconected nodes need to send all changes 
(when connected) to a master always connected node.
Thanks!
Rodrigo

Re: Which replication solution should I choose?

[Johan De Meersman]

> From: "Walter Heck" 
> Subject: Re: Which replication solution should I choose?

> Hi Johan,
> it'll be a good ol' war story of the transition of a large 130k QPS MMM 
> cluster
> to PXC, so come visit for sure. Here's the link to the session:
> http://www.percona.com/live/london-2014/sessions/moving-mysql-infrastructure-130k-qps-galera

It was pretty interesting, indeed, especially the bit about the spontaneous 
resyncs :-p 

-- 
Unhappiness is discouraged and will be corrected with kitten pictures. 

Re: Which replication solution should I choose?

[Heck, Walter]

Hi Johan,

it'll be a good ol' war story of the transition of a large 130k QPS MMM
cluster to PXC, so come visit for sure. Here's the link to the session:
http://www.percona.com/live/london-2014/sessions/moving-mysql-infrastructure-130k-qps-galera

see you there :)

cheers,

On Thu, Oct 30, 2014 at 11:39 AM, Johan De Meersman 
wrote:

>
>
> - Original Message -
> > From: "Walter Heck" 
> > Subject: Re: Which replication solution should I choose?
> >
> > If you happen to be at the Percona Live conference next week in London
> I'm
> > speaking about Galera in a high performance setup as well.
>
>
> Ooo, very interested, especially if you're going to be talking about the
> gotcha's - I'm considering switching our master-master setups out for
> Galera clusters, and it'd be good to have an idea what the side effects are
> going to be :-)
>
>
> --
> Unhappiness is discouraged and will be corrected with kitten pictures.
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
Best regards,

Walter Heck
CEO / Founder OlinData <http://olindata.com/?src=wh_gapp> - Open Source
Training & Consulting

Check out our upcoming trainings <http://olindata.com/training/upcoming>

Re: Which replication solution should I choose?

[Johan De Meersman]

- Original Message -
> From: "Walter Heck" 
> Subject: Re: Which replication solution should I choose?
> 
> If you happen to be at the Percona Live conference next week in London I'm
> speaking about Galera in a high performance setup as well.


Ooo, very interested, especially if you're going to be talking about the 
gotcha's - I'm considering switching our master-master setups out for Galera 
clusters, and it'd be good to have an idea what the side effects are going to 
be :-)


-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Which replication solution should I choose?

[Heck, Walter]

Hi Rafal,
On Wed, Oct 29, 2014 at 3:16 PM, Rafał Radecki 
wrote:

> Thanks for the info, Walter.
> I checked some basic info about Galera and it looks very promising. Can
> you tell what is preferable for loadbalancing of requests? HAProxy/Galera
> loadbalancer or maybe something else?
>
We use haProxy for our clients, I blogged about it here:
http://www.olindata.com/blog/2014/04/managing-percona-xtradb-cluster-puppet

If you happen to be at the Percona Live conference next week in London I'm
speaking about Galera in a high performance setup as well.


> Can you tell me also how much does MHA differ from MMM? Functionality
> looks quite similar.
>
The principles are very similar, the implementation is quite different. MHA
is actively maintained as far as I know. Haven't used it myself though.

-- 
Best regards,

Walter Heck
CEO / Founder OlinData

- Open Source Training & Consulting

Check out our upcoming trainings

Re: Which replication solution should I choose?

[Rafał Radecki]

Thanks for the info, Walter.
I checked some basic info about Galera and it looks very promising. Can you
tell what is preferable for loadbalancing of requests? HAProxy/Galera
loadbalancer or maybe something else?

Can you tell me also how much does MHA differ from MMM? Functionality looks
quite similar.

BR,
Rafal.

2014-10-29 11:38 GMT+01:00 Heck, Walter :

> Hi Rafael,
> On Wed, Oct 29, 2014 at 10:15 AM, Rafał Radecki 
> wrote:
>
>> I am creating an environment based on about 15 hardware nodes. 6 of them
>> will be for mysql databases. They will be divided into three pairs (3x2
>> nodes). Nodes in every pair will be configured with replication.
>>
>> I done similar configuration about an year ago. I used than:
>> - percona 5.5 with standard asynchronous replication;
>> - Multi Master Replication Manager for MySQL (http://mysql-mmm.org/
>> <https://t.yesware.com/tl/2627942011ccc3835f76c6e9ba4c0af91f3d3722/407ea34fcfb29332bbc93cdb34f13e4f/df42c03e188abb8c80b981666b82e7ff?ytl=http%3A%2F%2Fmysql-mmm.org%2F>)
>> for
>> automatic assignment/failover of read and write IP addresses and checking
>> the status of replication (mmm provides status scripts);
>>
> While it works fine for many situations, I wouldn't recommend using it for
> any new setups. There hasn't been any updates for years and there are a
> decent number of edge cases where MMM fails miserably. There are better
> alternatives out there these days.
>
>
>> My question is: I heard that there are other options than standard
>> asynchronous replication which sometimes was problematic (for example
>> there
>> was one slave thread only in percona 5.5 and there was a possibility that
>> slave node will fall behind master node in high load situations). I also
>> am
>> thinking which MySQL fork is the best option if I plan to use replication.
>> What can you propose based on your experience?
>>
> If you want to stick with standard replication, either MariaDB 10 or
> Percona 5.6 will do just fine. Instead of MMM you can google for MHA for
> instance.
>
> I would recommend taking a look at Galera though, which takes a different
> approach but with some nice benefits. The one thing is that with Galera
> your data generally lives on 3 or more servers (2 is possible, but not
> recommended). If that is a good idea to you, then Galera would be my
> personal preference.
>
>
> --
> Best regards,
>
> Walter Heck
> CEO / Founder OlinData
> <https://t.yesware.com/tl/2627942011ccc3835f76c6e9ba4c0af91f3d3722/407ea34fcfb29332bbc93cdb34f13e4f/fd1ca40450db4c95f61e02cfe4940db2?ytl=http%3A%2F%2Folindata.com%2F%3Fsrc%3Dwh_gapp>
> - Open Source Training & Consulting
>
> Check out our upcoming trainings
> <https://t.yesware.com/tl/2627942011ccc3835f76c6e9ba4c0af91f3d3722/407ea34fcfb29332bbc93cdb34f13e4f/63ebe1eaf25f15c25c5b43a8b2954a8d?ytl=http%3A%2F%2Folindata.com%2Ftraining%2Fupcoming>
>
>

Re: Which replication solution should I choose?

[Heck, Walter]

Hi Rafael,
On Wed, Oct 29, 2014 at 10:15 AM, Rafał Radecki 
wrote:

> I am creating an environment based on about 15 hardware nodes. 6 of them
> will be for mysql databases. They will be divided into three pairs (3x2
> nodes). Nodes in every pair will be configured with replication.
>
> I done similar configuration about an year ago. I used than:
> - percona 5.5 with standard asynchronous replication;
> - Multi Master Replication Manager for MySQL (http://mysql-mmm.org/
> <https://t.yesware.com/tl/2627942011ccc3835f76c6e9ba4c0af91f3d3722/407ea34fcfb29332bbc93cdb34f13e4f/df42c03e188abb8c80b981666b82e7ff?ytl=http%3A%2F%2Fmysql-mmm.org%2F>)
> for
> automatic assignment/failover of read and write IP addresses and checking
> the status of replication (mmm provides status scripts);
>
While it works fine for many situations, I wouldn't recommend using it for
any new setups. There hasn't been any updates for years and there are a
decent number of edge cases where MMM fails miserably. There are better
alternatives out there these days.


> My question is: I heard that there are other options than standard
> asynchronous replication which sometimes was problematic (for example there
> was one slave thread only in percona 5.5 and there was a possibility that
> slave node will fall behind master node in high load situations). I also am
> thinking which MySQL fork is the best option if I plan to use replication.
> What can you propose based on your experience?
>
If you want to stick with standard replication, either MariaDB 10 or
Percona 5.6 will do just fine. Instead of MMM you can google for MHA for
instance.

I would recommend taking a look at Galera though, which takes a different
approach but with some nice benefits. The one thing is that with Galera
your data generally lives on 3 or more servers (2 is possible, but not
recommended). If that is a good idea to you, then Galera would be my
personal preference.


-- 
Best regards,

Walter Heck
CEO / Founder OlinData
<https://t.yesware.com/tl/2627942011ccc3835f76c6e9ba4c0af91f3d3722/407ea34fcfb29332bbc93cdb34f13e4f/fd1ca40450db4c95f61e02cfe4940db2?ytl=http%3A%2F%2Folindata.com%2F%3Fsrc%3Dwh_gapp>
- Open Source Training & Consulting

Check out our upcoming trainings
<https://t.yesware.com/tl/2627942011ccc3835f76c6e9ba4c0af91f3d3722/407ea34fcfb29332bbc93cdb34f13e4f/63ebe1eaf25f15c25c5b43a8b2954a8d?ytl=http%3A%2F%2Folindata.com%2Ftraining%2Fupcoming>

Which replication solution should I choose?

[Rafał Radecki]

Hi All :)

I am creating an environment based on about 15 hardware nodes. 6 of them
will be for mysql databases. They will be divided into three pairs (3x2
nodes). Nodes in every pair will be configured with replication.

I done similar configuration about an year ago. I used than:
- percona 5.5 with standard asynchronous replication;
- Multi Master Replication Manager for MySQL (http://mysql-mmm.org/) for
automatic assignment/failover of read and write IP addresses and checking
the status of replication (mmm provides status scripts);
- Pacemaker to create a cluster in which scripts for Multi Master
Replication Manager were used.

My question is: I heard that there are other options than standard
asynchronous replication which sometimes was problematic (for example there
was one slave thread only in percona 5.5 and there was a possibility that
slave node will fall behind master node in high load situations). I also am
thinking which MySQL fork is the best option if I plan to use replication.
What can you propose based on your experience?

BR,
Rafal.

Re: Query regarding implementation of parallel-replication

[wagnerbianchi.com]

It's good to know. Keep up with good work, cheers!!



--
*Wagner Bianchi, MySQL Database Specialist*
Mobile:  +55.31.8654.9510
E-mail:  m...@wagnerbianchi.com
Twitter: @wagnerbianchijr

2014-09-06 3:01 GMT-03:00 Ajay Garg :

> Hi Wagner.
>
> That is what I did as the last resort, and that is "only" what solved the
> issue.
>
>
> Thanks.
>
> On Fri, Sep 5, 2014 at 1:52 AM, wagnerbianchi.com 
> wrote:
> > You can try these steps:
> >
> > 1-) Stop slave and write down the replication coordinates getting that in
> > MySQL's error log (*very important step*);
> > 2-) Issue the `reset slave` command on MySQL Slave;
> > 3-) Issue the CHANGE MASTER TO considering the replication coordinates
> > you've just written down on step 1;
> > 4-) Give replication a start;
> > 5-) Check if the issue has gone away.
> >
> > If you're not comfortable to do that, just share the SHOW SLAVE STATUS
> > output with us.
> >
> > Let us know how's it going, cheers!!
> >
> >
> >
> >
> > --
> > Wagner Bianchi, MySQL Database Specialist
> > Mobile:  +55.31.8654.9510
> > E-mail:  m...@wagnerbianchi.com
> > Twitter: @wagnerbianchijr
> >
> >
> > 2014-09-04 7:24 GMT-03:00 Ajay Garg :
> >
> >> Hi all.
> >>
> >> Unfortunately, I have run into the logs, as described at
> >> http://bugs.mysql.com/bug.php?id=71495
> >>
> >> Unfortunately, the issue does not go away, even after reverting back
> >> to "slave-parallel-workers=0" in "my.cnf", and restarting the mysql
> >> instance.
> >>
> >>
> >> Any quick idea, as to how we may get the mysql+replication up and
> >> running (even with the plain old non-multi-threaded mode)?
> >>
> >>
> >>
> >>
> >> On Tue, Sep 2, 2014 at 12:57 PM, Ajay Garg 
> wrote:
> >> > Thanks Akshay for the reply.
> >> >
> >> > On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi
> >> >  wrote:
> >> >> Hello Ajay,
> >> >>
> >> >> I tried testing the slave-parallel-workers few months ago, what I can
> >> >> surely
> >> >> tell you its still under development, and at that time needed some
> >> >> critical
> >> >> bug fixing.
> >> >>
> >> >> It is helpful in situations where each schema has even workload. The
> >> >> case
> >> >> you mentioned above doesnt have so. DB2 is getting different type of
> >> >> load
> >> >> than the others, in that case the other slave workers should be able
> to
> >> >> proceed with their workload as opposed to db2 which is still
> executing
> >> >> the
> >> >> long running statement. Now just imagine what happens if we try to
> take
> >> >> a
> >> >> backup, what binlog position should be captured ? the show slave
> status
> >> >> will
> >> >> print what ? this is where it needs development, I tried testing
> >> >> backups on
> >> >> it, but there is no concrete documentation on what position it would
> >> >> fetch.
> >> >>
> >> >> db2-statement-1 (very, very long-running)
> >> >> db2-statement-2 (short-running)
> >> >>
> >> >> about the above scenario, the next db2-statement-2 it will wait for
> the
> >> >> long
> >> >> running statement-1 to complete.
> >> >
> >> > Surely.. !! :)
> >> >
> >> >
> >> > However, my concern is how this "tracking" is done.
> >> > That is, how is the db-wise segregation of statements done (from a
> >> > single-binlog-file originally coming onto the slave) ?
> >> >
> >> > If this segregation is not done, then I cannot think of a way on how
> >> > things would scale up, like for example, when the slave-relay-log-file
> >> > contains a random mix of statements from tens of different databases.
> >> >
> >> >
> >> >
> >> > Any pointers on the "actual current" implementation of this db-wise
> >> > statements-segregation will be a great confidence-booster !!  :)
> >> >
> >> >
> >> >
> >> > Thanks and Regards,
> >> > Ajay
> >> >
> >> >
> >> >  However db2-statement-2 can be

Re: Query regarding implementation of parallel-replication

[Ajay Garg]

Hi Wagner.

That is what I did as the last resort, and that is "only" what solved the issue.


Thanks.

On Fri, Sep 5, 2014 at 1:52 AM, wagnerbianchi.com  
wrote:
> You can try these steps:
>
> 1-) Stop slave and write down the replication coordinates getting that in
> MySQL's error log (*very important step*);
> 2-) Issue the `reset slave` command on MySQL Slave;
> 3-) Issue the CHANGE MASTER TO considering the replication coordinates
> you've just written down on step 1;
> 4-) Give replication a start;
> 5-) Check if the issue has gone away.
>
> If you're not comfortable to do that, just share the SHOW SLAVE STATUS
> output with us.
>
> Let us know how's it going, cheers!!
>
>
>
>
> --
> Wagner Bianchi, MySQL Database Specialist
> Mobile:  +55.31.8654.9510
> E-mail:  m...@wagnerbianchi.com
> Twitter: @wagnerbianchijr
>
>
> 2014-09-04 7:24 GMT-03:00 Ajay Garg :
>
>> Hi all.
>>
>> Unfortunately, I have run into the logs, as described at
>> http://bugs.mysql.com/bug.php?id=71495
>>
>> Unfortunately, the issue does not go away, even after reverting back
>> to "slave-parallel-workers=0" in "my.cnf", and restarting the mysql
>> instance.
>>
>>
>> Any quick idea, as to how we may get the mysql+replication up and
>> running (even with the plain old non-multi-threaded mode)?
>>
>>
>>
>>
>> On Tue, Sep 2, 2014 at 12:57 PM, Ajay Garg  wrote:
>> > Thanks Akshay for the reply.
>> >
>> > On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi
>> >  wrote:
>> >> Hello Ajay,
>> >>
>> >> I tried testing the slave-parallel-workers few months ago, what I can
>> >> surely
>> >> tell you its still under development, and at that time needed some
>> >> critical
>> >> bug fixing.
>> >>
>> >> It is helpful in situations where each schema has even workload. The
>> >> case
>> >> you mentioned above doesnt have so. DB2 is getting different type of
>> >> load
>> >> than the others, in that case the other slave workers should be able to
>> >> proceed with their workload as opposed to db2 which is still executing
>> >> the
>> >> long running statement. Now just imagine what happens if we try to take
>> >> a
>> >> backup, what binlog position should be captured ? the show slave status
>> >> will
>> >> print what ? this is where it needs development, I tried testing
>> >> backups on
>> >> it, but there is no concrete documentation on what position it would
>> >> fetch.
>> >>
>> >> db2-statement-1 (very, very long-running)
>> >> db2-statement-2 (short-running)
>> >>
>> >> about the above scenario, the next db2-statement-2 it will wait for the
>> >> long
>> >> running statement-1 to complete.
>> >
>> > Surely.. !! :)
>> >
>> >
>> > However, my concern is how this "tracking" is done.
>> > That is, how is the db-wise segregation of statements done (from a
>> > single-binlog-file originally coming onto the slave) ?
>> >
>> > If this segregation is not done, then I cannot think of a way on how
>> > things would scale up, like for example, when the slave-relay-log-file
>> > contains a random mix of statements from tens of different databases.
>> >
>> >
>> >
>> > Any pointers on the "actual current" implementation of this db-wise
>> > statements-segregation will be a great confidence-booster !!  :)
>> >
>> >
>> >
>> > Thanks and Regards,
>> > Ajay
>> >
>> >
>> >  However db2-statement-2 can be picked up by
>> >> any other sql worker thread.
>> >>
>> >> This is a good feature added in mysql, however still needs to go
>> >> through lot
>> >> of testing. Please share your observation and findings in case it
>> >> differs
>> >> from the above.
>> >>
>> >> Cheers!!!
>> >> Akshay
>> >>
>> >>
>> >> On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg 
>> >> wrote:
>> >>>
>> >>> Hi all.
>> >>>
>> >>>
>> >>> We have replication set-up, where we cater to HUUGEE amounts of data.
>> >>> Since quite some time, we have been facing issues wherein the slave
>> >

Re: Query regarding implementation of parallel-replication

[wagnerbianchi.com]

You can try these steps:

1-) Stop slave and write down the replication coordinates getting that in
MySQL's error log (*very important step*);
2-) Issue the `reset slave` command on MySQL Slave;
3-) Issue the CHANGE MASTER TO considering the replication coordinates
you've just written down on step 1;
4-) Give replication a start;
5-) Check if the issue has gone away.

If you're not comfortable to do that, just share the SHOW SLAVE STATUS
output with us.

Let us know how's it going, cheers!!




--
*Wagner Bianchi, MySQL Database Specialist*
Mobile:  +55.31.8654.9510
E-mail:  m...@wagnerbianchi.com
Twitter: @wagnerbianchijr


2014-09-04 7:24 GMT-03:00 Ajay Garg :

> Hi all.
>
> Unfortunately, I have run into the logs, as described at
> http://bugs.mysql.com/bug.php?id=71495
>
> Unfortunately, the issue does not go away, even after reverting back
> to "slave-parallel-workers=0" in "my.cnf", and restarting the mysql
> instance.
>
>
> Any quick idea, as to how we may get the mysql+replication up and
> running (even with the plain old non-multi-threaded mode)?
>
>
>
>
> On Tue, Sep 2, 2014 at 12:57 PM, Ajay Garg  wrote:
> > Thanks Akshay for the reply.
> >
> > On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi
> >  wrote:
> >> Hello Ajay,
> >>
> >> I tried testing the slave-parallel-workers few months ago, what I can
> surely
> >> tell you its still under development, and at that time needed some
> critical
> >> bug fixing.
> >>
> >> It is helpful in situations where each schema has even workload. The
> case
> >> you mentioned above doesnt have so. DB2 is getting different type of
> load
> >> than the others, in that case the other slave workers should be able to
> >> proceed with their workload as opposed to db2 which is still executing
> the
> >> long running statement. Now just imagine what happens if we try to take
> a
> >> backup, what binlog position should be captured ? the show slave status
> will
> >> print what ? this is where it needs development, I tried testing
> backups on
> >> it, but there is no concrete documentation on what position it would
> fetch.
> >>
> >> db2-statement-1 (very, very long-running)
> >> db2-statement-2 (short-running)
> >>
> >> about the above scenario, the next db2-statement-2 it will wait for the
> long
> >> running statement-1 to complete.
> >
> > Surely.. !! :)
> >
> >
> > However, my concern is how this "tracking" is done.
> > That is, how is the db-wise segregation of statements done (from a
> > single-binlog-file originally coming onto the slave) ?
> >
> > If this segregation is not done, then I cannot think of a way on how
> > things would scale up, like for example, when the slave-relay-log-file
> > contains a random mix of statements from tens of different databases.
> >
> >
> >
> > Any pointers on the "actual current" implementation of this db-wise
> > statements-segregation will be a great confidence-booster !!  :)
> >
> >
> >
> > Thanks and Regards,
> > Ajay
> >
> >
> >  However db2-statement-2 can be picked up by
> >> any other sql worker thread.
> >>
> >> This is a good feature added in mysql, however still needs to go
> through lot
> >> of testing. Please share your observation and findings in case it
> differs
> >> from the above.
> >>
> >> Cheers!!!
> >> Akshay
> >>
> >>
> >> On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg 
> wrote:
> >>>
> >>> Hi all.
> >>>
> >>>
> >>> We have replication set-up, where we cater to HUUGEE amounts of data.
> >>> Since quite some time, we have been facing issues wherein the slave
> >>> lags behind master quite a lot.
> >>>
> >>>
> >>> So, yesterday we were able to setup parallel replication, by
> >>> incorporating the following changes ::
> >>>
> >>> a)
> >>> To begin with, partitioned some tables into dedicated databases.
> >>>
> >>> b)
> >>> Set up the "slave-parallel-workers" parameter.
> >>>
> >>>
> >>> The above seems to work functionally fine, but we have one doubt/query
> >>> about the scalability of this solution.
> >>>
> >>>
> >>>
> >>>
> >>> First, I will jot down the flow as far as I understand (please correct

Re: Query regarding implementation of parallel-replication

[Ajay Garg]

Hi all.

Unfortunately, I have run into the logs, as described at
http://bugs.mysql.com/bug.php?id=71495

Unfortunately, the issue does not go away, even after reverting back
to "slave-parallel-workers=0" in "my.cnf", and restarting the mysql
instance.


Any quick idea, as to how we may get the mysql+replication up and
running (even with the plain old non-multi-threaded mode)?




On Tue, Sep 2, 2014 at 12:57 PM, Ajay Garg  wrote:
> Thanks Akshay for the reply.
>
> On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi
>  wrote:
>> Hello Ajay,
>>
>> I tried testing the slave-parallel-workers few months ago, what I can surely
>> tell you its still under development, and at that time needed some critical
>> bug fixing.
>>
>> It is helpful in situations where each schema has even workload. The case
>> you mentioned above doesnt have so. DB2 is getting different type of load
>> than the others, in that case the other slave workers should be able to
>> proceed with their workload as opposed to db2 which is still executing the
>> long running statement. Now just imagine what happens if we try to take a
>> backup, what binlog position should be captured ? the show slave status will
>> print what ? this is where it needs development, I tried testing backups on
>> it, but there is no concrete documentation on what position it would fetch.
>>
>> db2-statement-1 (very, very long-running)
>> db2-statement-2 (short-running)
>>
>> about the above scenario, the next db2-statement-2 it will wait for the long
>> running statement-1 to complete.
>
> Surely.. !! :)
>
>
> However, my concern is how this "tracking" is done.
> That is, how is the db-wise segregation of statements done (from a
> single-binlog-file originally coming onto the slave) ?
>
> If this segregation is not done, then I cannot think of a way on how
> things would scale up, like for example, when the slave-relay-log-file
> contains a random mix of statements from tens of different databases.
>
>
>
> Any pointers on the "actual current" implementation of this db-wise
> statements-segregation will be a great confidence-booster !!  :)
>
>
>
> Thanks and Regards,
> Ajay
>
>
>  However db2-statement-2 can be picked up by
>> any other sql worker thread.
>>
>> This is a good feature added in mysql, however still needs to go through lot
>> of testing. Please share your observation and findings in case it differs
>> from the above.
>>
>> Cheers!!!
>> Akshay
>>
>>
>> On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg  wrote:
>>>
>>> Hi all.
>>>
>>>
>>> We have replication set-up, where we cater to HUUGEE amounts of data.
>>> Since quite some time, we have been facing issues wherein the slave
>>> lags behind master quite a lot.
>>>
>>>
>>> So, yesterday we were able to setup parallel replication, by
>>> incorporating the following changes ::
>>>
>>> a)
>>> To begin with, partitioned some tables into dedicated databases.
>>>
>>> b)
>>> Set up the "slave-parallel-workers" parameter.
>>>
>>>
>>> The above seems to work functionally fine, but we have one doubt/query
>>> about the scalability of this solution.
>>>
>>>
>>>
>>>
>>> First, I will jot down the flow as far as I understand (please correct
>>> if wrong) ::
>>>
>>> """
>>> Even in parallel-replication scenario, the master writes all the
>>> binlog (combined for all databases) in just one file, which then gets
>>> passed onto the slave as single-file itself. Thereafter, all the
>>> replication commands (combined for all databases) are written
>>> sequentially onto one slave-relay file.
>>>
>>> Thereafter, as per the documentation, the slave-SQL-Thread acts as the
>>> manager, handing over commands to worker-threads depending upon the
>>> databases on which the commands run.
>>> """
>>>
>>>
>>>
>>> So far, so good.
>>> However, what would happen if the slave-relay file contains the following
>>> ::
>>>
>>>
>>> db1-statement-1 (short-running)
>>> db2-statement-1 (very, very long-running)
>>> db2-statement-2 (short-running)
>>> db1-statement-2 (short-running)
>>> db1-statement-3 (short-running)
>>>
>>>
>>> We will be grateful if someone could please clarifiy, as to how the
>>> above statements will be managed amongst the Manager and the
>>> Worker-Threads (let's say there is just one worker-thread-per-db) ?
>>>
>>> In particular, does the Manager thread creates internal
>>> slave-relay-log-files, one for per database-statements?
>>>
>>>
>>>
>>> Thanks and Regards,
>>> Ajay
>>>
>>> --
>>> MySQL General Mailing List
>>> For list archives: http://lists.mysql.com/mysql
>>> To unsubscribe:http://lists.mysql.com/mysql
>>>
>>
>
>
>
> --
> Regards,
> Ajay



-- 
Regards,
Ajay

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Query regarding implementation of parallel-replication

[Ajay Garg]

Thanks Akshay for the reply.

On Tue, Sep 2, 2014 at 12:25 PM, Akshay Suryavanshi
 wrote:
> Hello Ajay,
>
> I tried testing the slave-parallel-workers few months ago, what I can surely
> tell you its still under development, and at that time needed some critical
> bug fixing.
>
> It is helpful in situations where each schema has even workload. The case
> you mentioned above doesnt have so. DB2 is getting different type of load
> than the others, in that case the other slave workers should be able to
> proceed with their workload as opposed to db2 which is still executing the
> long running statement. Now just imagine what happens if we try to take a
> backup, what binlog position should be captured ? the show slave status will
> print what ? this is where it needs development, I tried testing backups on
> it, but there is no concrete documentation on what position it would fetch.
>
> db2-statement-1 (very, very long-running)
> db2-statement-2 (short-running)
>
> about the above scenario, the next db2-statement-2 it will wait for the long
> running statement-1 to complete.

Surely.. !! :)


However, my concern is how this "tracking" is done.
That is, how is the db-wise segregation of statements done (from a
single-binlog-file originally coming onto the slave) ?

If this segregation is not done, then I cannot think of a way on how
things would scale up, like for example, when the slave-relay-log-file
contains a random mix of statements from tens of different databases.



Any pointers on the "actual current" implementation of this db-wise
statements-segregation will be a great confidence-booster !!  :)



Thanks and Regards,
Ajay


 However db2-statement-2 can be picked up by
> any other sql worker thread.
>
> This is a good feature added in mysql, however still needs to go through lot
> of testing. Please share your observation and findings in case it differs
> from the above.
>
> Cheers!!!
> Akshay
>
>
> On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg  wrote:
>>
>> Hi all.
>>
>>
>> We have replication set-up, where we cater to HUUGEE amounts of data.
>> Since quite some time, we have been facing issues wherein the slave
>> lags behind master quite a lot.
>>
>>
>> So, yesterday we were able to setup parallel replication, by
>> incorporating the following changes ::
>>
>> a)
>> To begin with, partitioned some tables into dedicated databases.
>>
>> b)
>> Set up the "slave-parallel-workers" parameter.
>>
>>
>> The above seems to work functionally fine, but we have one doubt/query
>> about the scalability of this solution.
>>
>>
>>
>>
>> First, I will jot down the flow as far as I understand (please correct
>> if wrong) ::
>>
>> """
>> Even in parallel-replication scenario, the master writes all the
>> binlog (combined for all databases) in just one file, which then gets
>> passed onto the slave as single-file itself. Thereafter, all the
>> replication commands (combined for all databases) are written
>> sequentially onto one slave-relay file.
>>
>> Thereafter, as per the documentation, the slave-SQL-Thread acts as the
>> manager, handing over commands to worker-threads depending upon the
>> databases on which the commands run.
>> """
>>
>>
>>
>> So far, so good.
>> However, what would happen if the slave-relay file contains the following
>> ::
>>
>>
>> db1-statement-1 (short-running)
>> db2-statement-1 (very, very long-running)
>> db2-statement-2 (short-running)
>> db1-statement-2 (short-running)
>> db1-statement-3 (short-running)
>>
>>
>> We will be grateful if someone could please clarifiy, as to how the
>> above statements will be managed amongst the Manager and the
>> Worker-Threads (let's say there is just one worker-thread-per-db) ?
>>
>> In particular, does the Manager thread creates internal
>> slave-relay-log-files, one for per database-statements?
>>
>>
>>
>> Thanks and Regards,
>> Ajay
>>
>> --
>> MySQL General Mailing List
>> For list archives: http://lists.mysql.com/mysql
>> To unsubscribe:http://lists.mysql.com/mysql
>>
>



-- 
Regards,
Ajay

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Query regarding implementation of parallel-replication

[Ajay Garg]

Ping !! :)

On Mon, Sep 1, 2014 at 8:27 AM, Ajay Garg  wrote:
> Hi all.
>
>
> We have replication set-up, where we cater to HUUGEE amounts of data.
> Since quite some time, we have been facing issues wherein the slave
> lags behind master quite a lot.
>
>
> So, yesterday we were able to setup parallel replication, by
> incorporating the following changes ::
>
> a)
> To begin with, partitioned some tables into dedicated databases.
>
> b)
> Set up the "slave-parallel-workers" parameter.
>
>
> The above seems to work functionally fine, but we have one doubt/query
> about the scalability of this solution.
>
>
>
>
> First, I will jot down the flow as far as I understand (please correct
> if wrong) ::
>
> """
> Even in parallel-replication scenario, the master writes all the
> binlog (combined for all databases) in just one file, which then gets
> passed onto the slave as single-file itself. Thereafter, all the
> replication commands (combined for all databases) are written
> sequentially onto one slave-relay file.
>
> Thereafter, as per the documentation, the slave-SQL-Thread acts as the
> manager, handing over commands to worker-threads depending upon the
> databases on which the commands run.
> """
>
>
>
> So far, so good.
> However, what would happen if the slave-relay file contains the following ::
>
>
> db1-statement-1 (short-running)
> db2-statement-1 (very, very long-running)
> db2-statement-2 (short-running)
> db1-statement-2 (short-running)
> db1-statement-3 (short-running)
>
>
> We will be grateful if someone could please clarifiy, as to how the
> above statements will be managed amongst the Manager and the
> Worker-Threads (let's say there is just one worker-thread-per-db) ?
>
> In particular, does the Manager thread creates internal
> slave-relay-log-files, one for per database-statements?
>
>
>
> Thanks and Regards,
> Ajay



-- 
Regards,
Ajay

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Query regarding implementation of parallel-replication

[Ajay Garg]

Hi all.


We have replication set-up, where we cater to HUUGEE amounts of data.
Since quite some time, we have been facing issues wherein the slave
lags behind master quite a lot.


So, yesterday we were able to setup parallel replication, by
incorporating the following changes ::

a)
To begin with, partitioned some tables into dedicated databases.

b)
Set up the "slave-parallel-workers" parameter.


The above seems to work functionally fine, but we have one doubt/query
about the scalability of this solution.




First, I will jot down the flow as far as I understand (please correct
if wrong) ::

"""
Even in parallel-replication scenario, the master writes all the
binlog (combined for all databases) in just one file, which then gets
passed onto the slave as single-file itself. Thereafter, all the
replication commands (combined for all databases) are written
sequentially onto one slave-relay file.

Thereafter, as per the documentation, the slave-SQL-Thread acts as the
manager, handing over commands to worker-threads depending upon the
databases on which the commands run.
"""



So far, so good.
However, what would happen if the slave-relay file contains the following ::


db1-statement-1 (short-running)
db2-statement-1 (very, very long-running)
db2-statement-2 (short-running)
db1-statement-2 (short-running)
db1-statement-3 (short-running)


We will be grateful if someone could please clarifiy, as to how the
above statements will be managed amongst the Manager and the
Worker-Threads (let's say there is just one worker-thread-per-db) ?

In particular, does the Manager thread creates internal
slave-relay-log-files, one for per database-statements?



Thanks and Regards,
Ajay

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication problem -solved

[william drescher]

On 8/30/2014 12:53 PM, Jose Julian Buda wrote:



On 30/08/14 12:56, william drescher wrote:

Thanks for pointing out the importance of the "last error"

I resynced the slave to the master, reset the master position, and
restarted the slave.

Now all works fine and I am much better equipped next time to
debug the
loss of the link.

When is the Last Error data deleted from the show slave data ?

--bill






Just after you resync the slave, "last error" should be empty.


Bye
Julian



Thanks Julian, it is.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication problem -solved

[Jose Julian Buda]

On 30/08/14 12:56, william drescher wrote:

Thanks for pointing out the importance of the "last error"

I resynced the slave to the master, reset the master position, and
restarted the slave.

Now all works fine and I am much better equipped next time to debug the
loss of the link.

When is the Last Error data deleted from the show slave data ?

--bill






Just after you resync the slave, "last error" should be empty.


Bye
Julian

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication problem -solved

[william drescher]

Thanks for pointing out the importance of the "last error"

I resynced the slave to the master, reset the master position, 
and restarted the slave.


Now all works fine and I am much better equipped next time to 
debug the loss of the link.


When is the Last Error data deleted from the show slave data ?

--bill



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication problem

[william drescher]

On 8/29/2014 7:40 PM, Suresh Kuna wrote:

You can paste the show slave status\G here for us to review  and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )


mysql> show global variables like 'log_bin%';
+-+---+
| Variable_name   | Value |
+-+---+
| log_bin | ON|
| log_bin_trust_function_creators | OFF   |
+-+---+




--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication problem

[Jose Julian Buda]

On 30/08/14 09:39, william drescher wrote:

On 8/29/2014 7:40 PM, Suresh Kuna wrote:

You can paste the show slave status\G here for us to review and on
Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )


Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 107 | | |
+--+--+--+--+
1 row in set (0.00 sec)

Slave
* 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 107
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query.
Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29727610
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on
query. Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)


1 transaction entered:
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 1837 | | |
+--+--+--+--+
1 row in set (0.00 sec)

SLave
*** 1. row ***
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.10.101
Master_User: repl
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.001225
Read_Master_Log_Pos: 1837
Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
Slave_IO_Running: Yes
Slave_SQL_Running: No
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 1062
Last_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on query.
Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Skip_Counter: 0
Exec_Master_Log_Pos: 90012430
Relay_Log_Space: 29729340
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 1062
Last_SQL_Error: Error 'Duplicate entry '18699' for key 'PRIMARY'' on
query. Default database: 'Information_server'. Query: 'insert into audit
(audit_type, subtype, user_Id, ptNum, data) values ('login', 'login',
'WPD', 0, 'Login: WPD from location: Suite B')'
Replicate_Ignore_Server_Ids:
Master_Server_Id: 3
1 row in set (0.00 sec)










Well , it seems that you have a 'Duplicate entry ' issue on slave, so 
the Slave_SQL_Running threads is locked :) , there's something wrong 
here with the initial slave status, maybe you have to rebuild the slave 
from scratch(backup or a dump from master), and be sure that you don't 
change anything on slave( phpmyadmin? )


Bye

Julian

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication problem

[wagnerbianchi.com]

Based on the SHOW SLAVE STATUS output you've sent us, I'd suggest that you
check what the application is doing, understand *why* the application is
violating the PK of the table information_server.audit, repair the possible
application problem and the, reconfigure the replication.

It seems that there's something not really good happening on the
application side since the error 1062 is being threw out by a INSERT sent
to the database ... by the application.

*PS.: looking at the purpose of information_server.audit, I wonder if this
is not a *design* problem!*

1-) Fix the application if you can, investigating the problem the
replication is showing up on SHOW SLAVE STATUS - remember that the
column Last_SQL_Error
is one of SHOW SLAVE STATUS best friends in this context;

2-) After to fix the problem, if design or application, recreate the slave
and then, start replication again;


It's possible to use another things to make the replication to bypass this
kind of problem, but, it's not that cool to have it configured. due to
that, I'd like to omit it at this point.

Let's keep in touch, happy mysql'ing!!

--

*Wagner Bianchi*


2014-08-30 9:54 GMT-03:00 Johnny Withers :

> There's a duplicate key on the audit table, 18699. Delete it and restart
> slave (start slave). Check slave status again,  might be more rows in there
> duplicated.
>
> You might want to compare the row to master to ensure it's a duplicate
> before deleting from slave.
>  On Aug 30, 2014 7:52 AM, "william drescher" 
> wrote:
>
> > On 8/29/2014 7:40 PM, Suresh Kuna wrote:
> >
> >> You can paste the show slave status\G here for us to review  and on
> >> Master,
> >> show global variables like 'log-bin%'; show master status ( 3 to 4 times
> >> continuously )
> >>
> > after a more complex transaction;
> > +--+--+--+--+
> > | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
> > +--+--+--+--+
> > | mysql-bin.001225 | 5952 |  |  |
> > +--+--+--+--+
> >
> > *** 1. row ***
> >Slave_IO_State: Waiting for master to send event
> >   Master_Host: 192.168.10.101
> >   Master_User: repl
> >   Master_Port: 3306
> > Connect_Retry: 60
> >   Master_Log_File: mysql-bin.001225
> >   Read_Master_Log_Pos: 5952
> >Relay_Log_File: ubuntu2-relay-bin.02
> > Relay_Log_Pos: 16426
> > Relay_Master_Log_File: mysql-bin.001139
> >  Slave_IO_Running: Yes
> > Slave_SQL_Running: No
> >   Replicate_Do_DB:
> >   Replicate_Ignore_DB:
> >Replicate_Do_Table:
> >Replicate_Ignore_Table:
> >   Replicate_Wild_Do_Table:
> >   Replicate_Wild_Ignore_Table:
> >Last_Errno: 1062
> >Last_Error: Error 'Duplicate entry '18699' for key
> > 'PRIMARY'' on query. Default database: 'Information_server'. Query:
> 'insert
> > into audit  (audit_type, subtype, user_Id, ptNum, data) values ('login',
> > 'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
> >  Skip_Counter: 0
> >   Exec_Master_Log_Pos: 90012430
> >   Relay_Log_Space: 29733455
> >   Until_Condition: None
> >Until_Log_File:
> > Until_Log_Pos: 0
> >Master_SSL_Allowed: No
> >Master_SSL_CA_File:
> >Master_SSL_CA_Path:
> >   Master_SSL_Cert:
> > Master_SSL_Cipher:
> >Master_SSL_Key:
> > Seconds_Behind_Master: NULL
> > Master_SSL_Verify_Server_Cert: No
> > Last_IO_Errno: 0
> > Last_IO_Error:
> >Last_SQL_Errno: 1062
> >Last_SQL_Error: Error 'Duplicate entry '18699' for key
> > 'PRIMARY'' on query. Default database: 'Information_server'. Query:
> 'insert
> > into audit  (audit_type, subtype, user_Id, ptNum, data) values ('login',
> > 'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
> >   Replicate_Ignore_Server_Ids:
> >  Master_Server_Id: 3
> >
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> >
>

Re: Replication problem

[william drescher]

On 8/29/2014 7:40 PM, Suresh Kuna wrote:

You can paste the show slave status\G here for us to review  and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously ) global var, below


+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 8919 |  |  |
+--+--+--+--+

*** 1. row ***
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.10.101
  Master_User: repl
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: mysql-bin.001225
  Read_Master_Log_Pos: 8919
   Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

 Skip_Counter: 0
  Exec_Master_Log_Pos: 90012430
  Relay_Log_Space: 29736422
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

  Replicate_Ignore_Server_Ids:
 Master_Server_Id: 3


mysql> show global variables like 'log-bin%';
Empty set (0.01 sec)





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication problem

[Johnny Withers]

There's a duplicate key on the audit table, 18699. Delete it and restart
slave (start slave). Check slave status again,  might be more rows in there
duplicated.

You might want to compare the row to master to ensure it's a duplicate
before deleting from slave.
 On Aug 30, 2014 7:52 AM, "william drescher" 
wrote:

> On 8/29/2014 7:40 PM, Suresh Kuna wrote:
>
>> You can paste the show slave status\G here for us to review  and on
>> Master,
>> show global variables like 'log-bin%'; show master status ( 3 to 4 times
>> continuously )
>>
> after a more complex transaction;
> +--+--+--+--+
> | File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
> +--+--+--+--+
> | mysql-bin.001225 | 5952 |  |  |
> +--+--+--+--+
>
> *** 1. row ***
>Slave_IO_State: Waiting for master to send event
>   Master_Host: 192.168.10.101
>   Master_User: repl
>   Master_Port: 3306
> Connect_Retry: 60
>   Master_Log_File: mysql-bin.001225
>   Read_Master_Log_Pos: 5952
>Relay_Log_File: ubuntu2-relay-bin.02
> Relay_Log_Pos: 16426
> Relay_Master_Log_File: mysql-bin.001139
>  Slave_IO_Running: Yes
> Slave_SQL_Running: No
>   Replicate_Do_DB:
>   Replicate_Ignore_DB:
>Replicate_Do_Table:
>Replicate_Ignore_Table:
>   Replicate_Wild_Do_Table:
>   Replicate_Wild_Ignore_Table:
>Last_Errno: 1062
>Last_Error: Error 'Duplicate entry '18699' for key
> 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert
> into audit  (audit_type, subtype, user_Id, ptNum, data) values ('login',
> 'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
>  Skip_Counter: 0
>   Exec_Master_Log_Pos: 90012430
>   Relay_Log_Space: 29733455
>   Until_Condition: None
>Until_Log_File:
> Until_Log_Pos: 0
>Master_SSL_Allowed: No
>Master_SSL_CA_File:
>Master_SSL_CA_Path:
>   Master_SSL_Cert:
> Master_SSL_Cipher:
>Master_SSL_Key:
> Seconds_Behind_Master: NULL
> Master_SSL_Verify_Server_Cert: No
> Last_IO_Errno: 0
> Last_IO_Error:
>Last_SQL_Errno: 1062
>Last_SQL_Error: Error 'Duplicate entry '18699' for key
> 'PRIMARY'' on query. Default database: 'Information_server'. Query: 'insert
> into audit  (audit_type, subtype, user_Id, ptNum, data) values ('login',
> 'login', 'WPD', 0, 'Login: WPD from location: Suite B')'
>   Replicate_Ignore_Server_Ids:
>  Master_Server_Id: 3
>
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: Replication problem

[william drescher]

On 8/29/2014 7:40 PM, Suresh Kuna wrote:

You can paste the show slave status\G here for us to review  and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )

after a more complex transaction;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 5952 |  |  |
+--+--+--+--+

*** 1. row ***
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.10.101
  Master_User: repl
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: mysql-bin.001225
  Read_Master_Log_Pos: 5952
   Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

 Skip_Counter: 0
  Exec_Master_Log_Pos: 90012430
  Relay_Log_Space: 29733455
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

  Replicate_Ignore_Server_Ids:
 Master_Server_Id: 3



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication problem

[william drescher]

On 8/29/2014 5:11 PM, wagnerbianchi.com wrote:

Hello guys, some points to check here:

1-) Is the master server configured with sync_binlog=1 ?


It was not, I reconfigured and restarted mysql and...


2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading
events from master, is the Exec_Master_Log_Pos incrementing or not?

Not - see prior post

3-) Why are you reconfiguring all the replication just because the link
went down?


AFAIK, I am no reconfiguring,  after the link comes back up, the 
slave does not start replicating - sometimes.  Happens quite 
infrequently.  I intend to be just restoring the database and 
restarting replication




Cheers,


Thanks, I need the cheers.






--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Replication problem

[william drescher]

On 8/29/2014 7:40 PM, Suresh Kuna wrote:

You can paste the show slave status\G here for us to review  and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )


Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 |  107 |  |  |
+--+--+--+--+
1 row in set (0.00 sec)

Slave
* 1. row ***
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.10.101
  Master_User: repl
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: mysql-bin.001225
  Read_Master_Log_Pos: 107
   Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

 Skip_Counter: 0
  Exec_Master_Log_Pos: 90012430
  Relay_Log_Space: 29727610
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

  Replicate_Ignore_Server_Ids:
 Master_Server_Id: 3
1 row in set (0.00 sec)


1 transaction entered:
Master:
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.001225 | 1837 |  |  |
+--+--+--+--+
1 row in set (0.00 sec)

SLave
*** 1. row ***
   Slave_IO_State: Waiting for master to send event
  Master_Host: 192.168.10.101
  Master_User: repl
  Master_Port: 3306
Connect_Retry: 60
  Master_Log_File: mysql-bin.001225
  Read_Master_Log_Pos: 1837
   Relay_Log_File: ubuntu2-relay-bin.02
Relay_Log_Pos: 16426
Relay_Master_Log_File: mysql-bin.001139
 Slave_IO_Running: Yes
Slave_SQL_Running: No
  Replicate_Do_DB:
  Replicate_Ignore_DB:
   Replicate_Do_Table:
   Replicate_Ignore_Table:
  Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
   Last_Errno: 1062
   Last_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

 Skip_Counter: 0
  Exec_Master_Log_Pos: 90012430
  Relay_Log_Space: 29729340
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
   Last_SQL_Errno: 1062
   Last_SQL_Error: Error 'Duplicate entry '18699' 
for key 'PRIMARY'' on query. Default database: 
'Information_server'. Query: 'insert into audit  (audit_type, 
subtype, user_Id, ptNum, data) values ('login', 'login', 'WPD', 
0, 'Login: WPD from location: Suite B')'

  Replicate_Ignore_Server_Ids:
 Master_Server_Id: 3
1 row in set (0.00 sec)

Re: Replication problem

[Suresh Kuna]

You can paste the show slave status\G here for us to review  and on Master,
show global variables like 'log-bin%'; show master status ( 3 to 4 times
continuously )


On Fri, Aug 29, 2014 at 5:11 PM, wagnerbianchi.com 
wrote:

> Hello guys, some points to check here:
>
> 1-) Is the master server configured with sync_binlog=1 ?
> 2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading
> events from master, is the Exec_Master_Log_Pos incrementing or not?
> 3-) Why are you reconfiguring all the replication just because the link
> went down?
>
> Cheers,
> --
> *WB*
>
> 2014-08-29 17:46 GMT-03:00 Andrew Moore :
>
> > Whilst there are a few possibilities, check on the master that your
> binary
> > logs are being written to. Another possible reason could be filtering.
> > On 29 Aug 2014 21:36, "william drescher" 
> wrote:
> >
> > >
> > > Replication novice
> > >
> > > I have a master server at the office and a replication server at home.
> > > This setup has been working for a couple of years. Occasionally the
> > > replication server gets out of sync (usually following a internet
> problem
> > > and the vpn going down.)
> > > I just stop the slave, make sure there is nothing going to the master
> > > (when the office is closed),
> > > copy the database,
> > > transfer the file,
> > > load the backup, and
> > > start the slave and all is well.
> > >
> > > This time there was not a communications problem of which I am aware.
> > The
> > > slave status said the slave_IO_state was "Waiting for master to send
> > event"
> > > but it was not replicating.
> > >
> > > I did the usual
> > >
> > > now it is not updating the replication database (transactions made on
> the
> > > master do not show on the slave - using phpMyAdmin on both servers) BUT
> > > show master status shows the correct log file and the position is
> > > incrementing AND show slave status shows the same master log file and
> the
> > > same position as the master.  So, looking at the status info it seems
> to
> > be
> > > running fine, but the transactions do not appear to appear on the
> slave.
> > >
> > > I seek suggestions how to 1) find out what goes wrong when the vpn goes
> > > down, and 2) (much more important now) how to find out whether or not
> the
> > > slave is actually replicating or not.
> > >
> > > --bill
> > >
> > >
> > > --
> > > MySQL General Mailing List
> > > For list archives: http://lists.mysql.com/mysql
> > > To unsubscribe:http://lists.mysql.com/mysql
> > >
> > >
> >
>



-- 
--
Thanks
Suresh Kuna
MySQL Database Consutant & MongoDB DBA
Hadoop Admin

Re: Replication problem

[wagnerbianchi.com]

Hello guys, some points to check here:

1-) Is the master server configured with sync_binlog=1 ?
2-) About the SHOW SLAVE STATUS output, when slave seems to be just reading
events from master, is the Exec_Master_Log_Pos incrementing or not?
3-) Why are you reconfiguring all the replication just because the link
went down?

Cheers,
--
*WB*

2014-08-29 17:46 GMT-03:00 Andrew Moore :

> Whilst there are a few possibilities, check on the master that your binary
> logs are being written to. Another possible reason could be filtering.
> On 29 Aug 2014 21:36, "william drescher"  wrote:
>
> >
> > Replication novice
> >
> > I have a master server at the office and a replication server at home.
> > This setup has been working for a couple of years. Occasionally the
> > replication server gets out of sync (usually following a internet problem
> > and the vpn going down.)
> > I just stop the slave, make sure there is nothing going to the master
> > (when the office is closed),
> > copy the database,
> > transfer the file,
> > load the backup, and
> > start the slave and all is well.
> >
> > This time there was not a communications problem of which I am aware.
> The
> > slave status said the slave_IO_state was "Waiting for master to send
> event"
> > but it was not replicating.
> >
> > I did the usual
> >
> > now it is not updating the replication database (transactions made on the
> > master do not show on the slave - using phpMyAdmin on both servers) BUT
> > show master status shows the correct log file and the position is
> > incrementing AND show slave status shows the same master log file and the
> > same position as the master.  So, looking at the status info it seems to
> be
> > running fine, but the transactions do not appear to appear on the slave.
> >
> > I seek suggestions how to 1) find out what goes wrong when the vpn goes
> > down, and 2) (much more important now) how to find out whether or not the
> > slave is actually replicating or not.
> >
> > --bill
> >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> >
> >
>

Re: Replication problem

[Andrew Moore]

Whilst there are a few possibilities, check on the master that your binary
logs are being written to. Another possible reason could be filtering.
On 29 Aug 2014 21:36, "william drescher"  wrote:

>
> Replication novice
>
> I have a master server at the office and a replication server at home.
> This setup has been working for a couple of years. Occasionally the
> replication server gets out of sync (usually following a internet problem
> and the vpn going down.)
> I just stop the slave, make sure there is nothing going to the master
> (when the office is closed),
> copy the database,
> transfer the file,
> load the backup, and
> start the slave and all is well.
>
> This time there was not a communications problem of which I am aware.  The
> slave status said the slave_IO_state was "Waiting for master to send event"
> but it was not replicating.
>
> I did the usual
>
> now it is not updating the replication database (transactions made on the
> master do not show on the slave - using phpMyAdmin on both servers) BUT
> show master status shows the correct log file and the position is
> incrementing AND show slave status shows the same master log file and the
> same position as the master.  So, looking at the status info it seems to be
> running fine, but the transactions do not appear to appear on the slave.
>
> I seek suggestions how to 1) find out what goes wrong when the vpn goes
> down, and 2) (much more important now) how to find out whether or not the
> slave is actually replicating or not.
>
> --bill
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Replication problem

[william drescher]

Replication novice

I have a master server at the office and a replication server at 
home.  This setup has been working for a couple of years. 
Occasionally the replication server gets out of sync (usually 
following a internet problem and the vpn going down.)
I just stop the slave, make sure there is nothing going to the 
master (when the office is closed),

copy the database,
transfer the file,
load the backup, and
start the slave and all is well.

This time there was not a communications problem of which I am 
aware.  The slave status said the slave_IO_state was "Waiting for 
master to send event" but it was not replicating.


I did the usual

now it is not updating the replication database (transactions 
made on the master do not show on the slave - using phpMyAdmin on 
both servers) BUT show master status shows the correct log file 
and the position is incrementing AND show slave status shows the 
same master log file and the same position as the master.  So, 
looking at the status info it seems to be running fine, but the 
transactions do not appear to appear on the slave.


I seek suggestions how to 1) find out what goes wrong when the 
vpn goes down, and 2) (much more important now) how to find out 
whether or not the slave is actually replicating or not.


--bill


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Fan-in replication

[Igor Dvorzhak]

Hi all,

I need to configure fan-in replication from MySQL DB shards into single
MySQL DB.
All shards and fan-in replica are located on separate physical servers.
All table IDs are unique across all shards.

Is there way to do this.

I have considered Tungsten, but it seems like it can't be configured in
such way. It can fan-in all shards into one MySQL server but in different
DBs.

Now I'm looking into the multi-source replication in MySQL 5.7.

Does anybody have experience in such or similar fan-in replication setup?
Or what is the best way to do this?

Best,
Igor

Re: LOAD DATA in replication

[neubyr]

Thanks for the details Shawn.

So row based replication would avoid server side LOAD DATA on slave.
Unfortunately, the Master is using MySQL ver 5.0, so I don't think it can
use row based replication.

- thanks,
N



On Thu, Jan 30, 2014 at 7:48 AM, shawn l.green wrote:

> Hello Neubyr,
>
>
> On 1/29/2014 7:16 PM, neubyr wrote:
>
>> I am trying to understand MySQL statement based replication with LOAD DATA
>> LOCAL INFILE statement'.
>>
>> According to manual -
>> https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html -
>> LOAD DATA LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE, however, I
>> am seeing it replicated as 'LOAD DATA INFILE'.
>>
>> I was wondering why slave isn't getting LOAD DATA LOCAL INFILE statements.
>> Appreciate any help on this.
>>
>> Master is using MySQL 5.0 and slave is using MySQL 5.6.
>>
>> -thanks,
>> N
>>
>>
> The slave is not receiving the file from your local disk. When that file
> arrives at the master (due to your LOAD DATA LOCAL ..) it is stored in the
> binary log and copied (via replication) to the slave where the slave
> performs a server-side LOAD DATA...  . This is how STATEMENT-based
> replication operates.
>
> Does that make better sense?
>
> --
> Shawn Green
> MySQL Senior Principal Technical Support Engineer
> Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
> Office: Blountville, TN
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

Re: LOAD DATA in replication

[shawn l.green]

Hello Neubyr,

On 1/29/2014 7:16 PM, neubyr wrote:

I am trying to understand MySQL statement based replication with LOAD DATA
LOCAL INFILE statement'.

According to manual -
https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html -
LOAD DATA LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE, however, I
am seeing it replicated as 'LOAD DATA INFILE'.

I was wondering why slave isn't getting LOAD DATA LOCAL INFILE statements.
Appreciate any help on this.

Master is using MySQL 5.0 and slave is using MySQL 5.6.

-thanks,
N



The slave is not receiving the file from your local disk. When that file 
arrives at the master (due to your LOAD DATA LOCAL ..) it is stored in 
the binary log and copied (via replication) to the slave where the slave 
performs a server-side LOAD DATA...  . This is how STATEMENT-based 
replication operates.


Does that make better sense?

--
Shawn Green
MySQL Senior Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: LOAD DATA in replication

[Antonio Fernández Pérez]

If I don't mistake, there are some parameters to make that you are saying.

Check statement-based-replication and row-based-replication. I think that
this could help you.

Regards,

Antonio.

LOAD DATA in replication

[neubyr]

I am trying to understand MySQL statement based replication with LOAD DATA
LOCAL INFILE statement'.

According to manual -
https://dev.mysql.com/doc/refman/5.0/en/replication-features-load.html -
LOAD DATA LOCAL INFILE is replicated as LOAD DATA LOCAL INFILE, however, I
am seeing it replicated as 'LOAD DATA INFILE'.

I was wondering why slave isn't getting LOAD DATA LOCAL INFILE statements.
Appreciate any help on this.

Master is using MySQL 5.0 and slave is using MySQL 5.6.

-thanks,
N

Re: replication question replacing the master

[Richard Reina]

Manuel,

Thank you very much for this information. This sounds like a very good 
strategy. I think I will try switching some slaves from one relay to another to 
familiarize myself and get practice and them do it to deploy a new master.

Again, thank you very much.

Richard 



> El Jan 18, 2014, a las 2:00 AM, Manuel Arostegui  escribió:
> 
> 
> 
> 
> 2014/1/17 Richard Reina 
>> I have 3 relay MySQL database servers on my small office LAN backing up a
>> master and 3 more machines backing up each relay (1 each). They are all
>> replicating all databases and all tables. The master although running fine
>> is almost eight years old. I'm thinking it's probably time to make one of
>> the relays the master, but I've never done this before.
>> 
>> I want the new master to have the IP address of the old master 192.168.0.1
>> . To make the change I was going to take the master off the LAN and
>> shutdown mysql on all the machines, change the IP address on the chosen
>> relay to that of the master 192.168.0.1, then restart mysql on all the
>> machines. I always refer to the machines by their IP addresses and never by
>> their hostnames. Once I successfully make the change I was planning on
>> making the old master a relay since it is still working fine.
>> 
>> Will this plan work ok? Is there a better or easier way?
> 
> If the three machines are sync'ed and have consistent data I don't see the 
> need of stopping MySQL:
> 
> - Stop whatever writes to your current master
> - Once you are completely sure there are no writes in your current master, 
> set it to read_only = ON
> - In the slave which will become the master, get the logfile and current 
> position with: show master status;
>  - Set the new IP in the new master
> 
> 
> Using the position taken in the new master go to the slaves machines and:
> 
> stop slave; change master to master_host='IP', 
> master_log_file='whatever_file_name_you_got', 
> master_log_pos=whatever_number_you_got, 
> master_user='replication_or_whatever_you_have', 
> master_password='replication_or_whatever_you_have'; start slave;
> 
> - Set read_only = OFF in your new master
> - Start your application so you can start getting writes again.
> 
> As soon as you get writes if you do a "show master status;" in the new master 
> you should see the position going forward.
> 
> I see that faster than any other thing.
>  
> Hope this helps
> Manuel.
> 

Re: replication question replacing the master

[Manuel Arostegui]

2014/1/17 Richard Reina 

> I have 3 relay MySQL database servers on my small office LAN backing up a
> master and 3 more machines backing up each relay (1 each). They are all
> replicating all databases and all tables. The master although running fine
> is almost eight years old. I'm thinking it's probably time to make one of
> the relays the master, but I've never done this before.
>
> I want the new master to have the IP address of the old master 192.168.0.1
> . To make the change I was going to take the master off the LAN and
> shutdown mysql on all the machines, change the IP address on the chosen
> relay to that of the master 192.168.0.1, then restart mysql on all the
> machines. I always refer to the machines by their IP addresses and never by
> their hostnames. Once I successfully make the change I was planning on
> making the old master a relay since it is still working fine.
>
> Will this plan work ok? Is there a better or easier way?
>
>
>
If the three machines are sync'ed and have consistent data I don't see the
need of stopping MySQL:

- Stop whatever writes to your current master
- Once you are completely sure there are no writes in your current master,
set it to read_only = ON
- In the slave which will become the master, get the logfile and current
position with: show master status;
 - Set the new IP in the new master


Using the position taken in the new master go to the slaves machines and:

stop slave; change master to master_host='IP',
master_log_file='whatever_file_name_you_got',
master_log_pos=whatever_number_you_got,
master_user='replication_or_whatever_you_have',
master_password='replication_or_whatever_you_have'; start slave;

- Set read_only = OFF in your new master
- Start your application so you can start getting writes again.

As soon as you get writes if you do a "show master status;" in the new
master you should see the position going forward.

I see that faster than any other thing.

Hope this helps
Manuel.

Re: replication question replacing the master

[Reindl Harald]

Am 17.01.2014 22:42, schrieb Richard Reina:
> I have 3 relay MySQL database servers on my small office LAN backing up a
> master and 3 more machines backing up each relay (1 each). They are all
> replicating all databases and all tables. The master although running fine
> is almost eight years old. I'm thinking it's probably time to make one of
> the relays the master, but I've never done this before.
> 
> I want the new master to have the IP address of the old master 192.168.0.1
> . To make the change I was going to take the master off the LAN and
> shutdown mysql on all the machines, change the IP address on the chosen
> relay to that of the master 192.168.0.1, then restart mysql on all the
> machines. I always refer to the machines by their IP addresses and never by
> their hostnames. Once I successfully make the change I was planning on
> making the old master a relay since it is still working fine.
> 
> Will this plan work ok? Is there a better or easier way?

* stop the master
* sync the complete datadir to the new machine
* give the new machine the same ip
* start mysqld

how should the salve smell that anything has changed?



signature.asc
Description: OpenPGP digital signature

replication question replacing the master

[Richard Reina]

I have 3 relay MySQL database servers on my small office LAN backing up a
master and 3 more machines backing up each relay (1 each). They are all
replicating all databases and all tables. The master although running fine
is almost eight years old. I'm thinking it's probably time to make one of
the relays the master, but I've never done this before.

I want the new master to have the IP address of the old master 192.168.0.1
. To make the change I was going to take the master off the LAN and
shutdown mysql on all the machines, change the IP address on the chosen
relay to that of the master 192.168.0.1, then restart mysql on all the
machines. I always refer to the machines by their IP addresses and never by
their hostnames. Once I successfully make the change I was planning on
making the old master a relay since it is still working fine.

Will this plan work ok? Is there a better or easier way?

Thanks for you attention.

Richard

re: replication newbie questions

[Michael Widenius]

Hi!

>>>>> "Ed" == Ed L  writes:

Ed> Mysql newbie here, looking for some help configuring 5.0.45 master-slave 
Ed> replication.  Here's my scenario...

Ed> We have a heavily loaded 30gb 5.0.45 DB we need to replicate via 
Ed> master-slave configuration to a new, beefier server running same mysql 
Ed> 5.0.45, and then cutover to the new server.  Due to extreme SAN 
Ed> congestion and a grossly overloaded master server, our DB dumps take 5.5 
Ed> hours.  But we cannot afford that much downtime or locking during the 
Ed> replication transition; we can manage 10-15 minutes, but more is very 
Ed> problematic.

Ed> I understand that "FLUSH TABLES WITH READ LOCK" will lock the tables for 
Ed> the duration of the 5.5 hour dump.  Is this true?

Yes.

Ed> If so, we'd like to dump/initialize/sync slave WITHOUT any locking 
Ed> anything the master for more than a few seconds if at all possible.  
Ed> Will this give us the dump we need?

Ed>  mysqldump --single-transaction --master-data --all-databases

You can do a dump without locking by using the xtrabackup tool.
This however assumes you are using InnoDB as the storage engine.

The other option is to use file system snapshots, if your file system
supports that.  In this case you only have to do the FLUSH TABLES
... for the duration of the snapshot.

Regards,
Monty

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: replication newbie questions

[Ananda Kumar]

if i u have LVM's then lock is held only for the duration of taking
snapshot, which would be few min, if there is very less activity on the db.


On Wed, Aug 28, 2013 at 3:08 PM, Ed L.  wrote:

>  On 8/28/13 2:00 PM, Ananda Kumar wrote:
>
>
> Why don't u try snapshot backups, where the lock held for less duration.
> Or can't u take mysql dumps during Night time when there is less bd activity
>
>
> I neglected to mention these systems are both CentOS linux systems.
>
> Unfortunately, the 5.5 hour dumps are already done during the least busy
> times.
>
> Regarding snapshots, how long are snapshot locks held?  These are ext4
> filesystems.  Assuming the lock is not held for long, what's the
> recommended way to do snapshots on ext4?
>
> Thanks,
> Ed
>
>
>
> On Thursday, August 29, 2013, Ed L.  wrote:
> >
> > Mysql newbie here, looking for some help configuring 5.0.45 master-slave
> replication.  Here's my scenario...
> >
> > We have a heavily loaded 30gb 5.0.45 DB we need to replicate via
> master-slave configuration to a new, beefier server running same mysql
> 5.0.45, and then cutover to the new server.  Due to extreme SAN congestion
> and a grossly overloaded master server, our DB dumps take 5.5 hours.  But
> we cannot afford that much downtime or locking during the replication
> transition; we can manage 10-15 minutes, but more is very problematic.
> >
> > I understand that "FLUSH TABLES WITH READ LOCK" will lock the tables for
> the duration of the 5.5 hour dump.  Is this true?
> >
> > If so, we'd like to dump/initialize/sync slave WITHOUT any locking
> anything the master for more than a few seconds if at all possible.  Will
> this give us the dump we need?
> >
> > mysqldump --single-transaction --master-data --all-databases
> >
> > Thank you in advance for any help.
> >
> > Ed
> >
>
>
>

Re: replication newbie questions

[Ed L.]

On 8/28/13 2:00 PM, Ananda Kumar wrote:


Why don't u try snapshot backups, where the lock held for less 
duration. Or can't u take mysql dumps during Night time when there is 
less bd activity


I neglected to mention these systems are both CentOS linux systems.

Unfortunately, the 5.5 hour dumps are already done during the least busy 
times.


Regarding snapshots, how long are snapshot locks held?  These are ext4 
filesystems.  Assuming the lock is not held for long, what's the 
recommended way to do snapshots on ext4?


Thanks,
Ed



On Thursday, August 29, 2013, Ed L. <mailto:mysql@bluepolka.net>> wrote:

>
> Mysql newbie here, looking for some help configuring 5.0.45 
master-slave replication.  Here's my scenario...

>
> We have a heavily loaded 30gb 5.0.45 DB we need to replicate via 
master-slave configuration to a new, beefier server running same mysql 
5.0.45, and then cutover to the new server.  Due to extreme SAN 
congestion and a grossly overloaded master server, our DB dumps take 
5.5 hours.  But we cannot afford that much downtime or locking during 
the replication transition; we can manage 10-15 minutes, but more is 
very problematic.

>
> I understand that "FLUSH TABLES WITH READ LOCK" will lock the tables 
for the duration of the 5.5 hour dump.  Is this true?

>
> If so, we'd like to dump/initialize/sync slave WITHOUT any locking 
anything the master for more than a few seconds if at all possible. 
 Will this give us the dump we need?

>
> mysqldump --single-transaction --master-data --all-databases
>
> Thank you in advance for any help.
>
> Ed
> 

Re: replication newbie questions

[Ananda Kumar]

Why don't u try snapshot backups, where the lock held for less duration. Or
can't u take mysql dumps during Night time when there is less bd activity

On Thursday, August 29, 2013, Ed L.  wrote:
>
> Mysql newbie here, looking for some help configuring 5.0.45 master-slave
replication.  Here's my scenario...
>
> We have a heavily loaded 30gb 5.0.45 DB we need to replicate via
master-slave configuration to a new, beefier server running same mysql
5.0.45, and then cutover to the new server.  Due to extreme SAN congestion
and a grossly overloaded master server, our DB dumps take 5.5 hours.  But
we cannot afford that much downtime or locking during the replication
transition; we can manage 10-15 minutes, but more is very problematic.
>
> I understand that "FLUSH TABLES WITH READ LOCK" will lock the tables for
the duration of the 5.5 hour dump.  Is this true?
>
> If so, we'd like to dump/initialize/sync slave WITHOUT any locking
anything the master for more than a few seconds if at all possible.  Will
this give us the dump we need?
>
> mysqldump --single-transaction --master-data --all-databases
>
> Thank you in advance for any help.
>
> Ed
>

replication newbie questions

[Ed L.]

Mysql newbie here, looking for some help configuring 5.0.45 master-slave 
replication.  Here's my scenario...


We have a heavily loaded 30gb 5.0.45 DB we need to replicate via 
master-slave configuration to a new, beefier server running same mysql 
5.0.45, and then cutover to the new server.  Due to extreme SAN 
congestion and a grossly overloaded master server, our DB dumps take 5.5 
hours.  But we cannot afford that much downtime or locking during the 
replication transition; we can manage 10-15 minutes, but more is very 
problematic.


I understand that "FLUSH TABLES WITH READ LOCK" will lock the tables for 
the duration of the 5.5 hour dump.  Is this true?


If so, we'd like to dump/initialize/sync slave WITHOUT any locking 
anything the master for more than a few seconds if at all possible.  
Will this give us the dump we need?


mysqldump --single-transaction --master-data --all-databases

Thank you in advance for any help.

Ed

Re: Replication question

[rich gray]

On 24/07/2013 19:52, Rick James wrote:

4) 3 tables from the slaves are to be replicated back to the master

NO.

However, consider Percona XtraDb Cluster or MariaDB+Galera.  They allow 
multiple writable masters.  But they won't let you be so selective about tables 
not being replicated.
Here are the gotchas for Galera usage:
 http://mysql.rjweb.org/doc.php/galera
If you can live with them (plus replicating everything), it may be best for you.


Ok thanks Rick for confirming my initial gut feelings about this...! 
Will have to implement a manual process to push the required data back 
to the master.


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Replication question

[Rick James]

> 4) 3 tables from the slaves are to be replicated back to the master
NO.

However, consider Percona XtraDb Cluster or MariaDB+Galera.  They allow 
multiple writable masters.  But they won't let you be so selective about tables 
not being replicated.
Here are the gotchas for Galera usage:
http://mysql.rjweb.org/doc.php/galera
If you can live with them (plus replicating everything), it may be best for you.

> -Original Message-
> From: rich gray [mailto:r...@richgray.com]
> Sent: Wednesday, July 24, 2013 8:21 AM
> To: mysql@lists.mysql.com
> Subject: Replication question
> 
> I have been asked to set up multiple database replication which I have
> done before for simple cases however there are some nuances with this
> instance that add some complexity and I'd like to hear your collective
> expertise on this proposed scenario:-
> 
> 1) Single master database
> 2) n (probably 3 to start with) number of slave databases
> 3) All but 5 tables (123 tables in total) are to be replicated from the
> master to all the slaves
> 4) 3 tables from the slaves are to be replicated back to the master
> 
> It is mainly item 4) that concerns me - the primary ID's are almost
> certain to collide unless I seed the auto increment ID to partition the
> IDs into separate ranges or does MySQL handle this issue?
> There are some foreign keys on one of the 3 slave to master tables but
> they are pointing at some extremely static tables that are very unlikely
> to change.
> 
> Is the above a feasible implementation...?
> 
> Thanks in advance for any advice/pointers!
> 
> Rich
> 
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Replication question

[rich gray]

I have been asked to set up multiple database replication which I have 
done before for simple cases however there are some nuances with this 
instance that add some complexity and I'd like to hear your collective 
expertise on this proposed scenario:-


1) Single master database
2) n (probably 3 to start with) number of slave databases
3) All but 5 tables (123 tables in total) are to be replicated from the 
master to all the slaves

4) 3 tables from the slaves are to be replicated back to the master

It is mainly item 4) that concerns me - the primary ID's are almost 
certain to collide unless I seed the auto increment ID to partition the 
IDs into separate ranges or does MySQL handle this issue?
There are some foreign keys on one of the 3 slave to master tables but 
they are pointing at some extremely static tables that are very unlikely 
to change.


Is the above a feasible implementation...?

Thanks in advance for any advice/pointers!

Rich



--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

replication issue from ec2 to my network ..

[umapathi b]

Hi,

I have configured replication from EC2 mysql instance to another mysql
instance on  our network via vpn  in between.  Sometimes it is showing no
errors on the slave but the slave falls behind . When I issue the commands
stop slave ; start slave ; it again catches up with the master.

What is the reason for this ?  how to resolve this ?

Thanks,
Umapathi.
umapath...@gmail.com

Re: User-defined variables not working ONLY on first query in chained replication

[Jesper Wisborg Krogh]

Hi Matthew,

On 16/07/2013 21:21, Matthew Ward wrote:

I've noticed a weird issue in our chained replication environment where when 
setting user-defined variables, the first time the variable is used in a 
session the value is NULL, but all uses thereafter work correctly.



The first time I run this insert, the value is correctly inserted in to master1 
and its slave, master3 (as you'd expect). However, a NULL value is inserted 
into master3-slave1. However, if I run the INSERT a second time (just the 
insert, no re-declaration of the user-defined variable), the value is correctly 
inserted in to all three servers, so that the contents of test_table on the 
three servers looks as follows:



Is this a known issue in MySQL with chained replication like this, or have I 
discovered a bug?


Do you happen to have any table level replication filters? If so it 
sounds like you are affected by a bug that was fixed in 5.5.32 
(https://dev.mysql.com/doc/relnotes/mysql/5.5/en/news-5-5-32.html):


*Replication:*Running the server with both the|--log-slave-updates| 
<http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_log-slave-updates>and|--replicate-wild-ignore-table| 
<http://dev.mysql.com/doc/refman/5.5/en/replication-options-slave.html#option_mysqld_replicate-wild-ignore-table>options 
in some cases caused updates to user variables not to be logged.


(Note: at least replicate-ignore-table is also triggering that bug).

Best regards,
Jesper Krogh
MySQL Support

User-defined variables not working ONLY on first query in chained replication

[Matthew Ward]

I've noticed a weird issue in our chained replication environment where when 
setting user-defined variables, the first time the variable is used in a 
session the value is NULL, but all uses thereafter work correctly.

The environment is such: we have a master (master1), which has a slave which is 
also a master (master3), which itself has slaves (master3-slave1), i.e.:

master1 -> master3 -> master3-slave1

I can replicate my issue with a very simple setup. I simply create a test table 
with one TEXT column, and I set a user-defined variable:

CREATE TABLE test_table (id INT(10) PRIMARY KEY AUTO_INCREMENT, result TEXT) 
ENGINE=InnoDB;
SET @mynewvariable = "testvalue"
And then insert the variable into the test table:

INSERT INTO test_table VALUES (NULL, @mynewvariable);
The first time I run this insert, the value is correctly inserted in to master1 
and its slave, master3 (as you'd expect). However, a NULL value is inserted 
into master3-slave1. However, if I run the INSERT a second time (just the 
insert, no re-declaration of the user-defined variable), the value is correctly 
inserted in to all three servers, so that the contents of test_table on the 
three servers looks as follows:

master1   master3   master3-slave1
- - --
testvalue testvalue NULL
testvalue testvalue testvalue
I don't believe this is related to replication delay, because even if I leave a 
while between setting the variable and running the first INSERT, the result is 
always the same. The problem is agnostic of table format or how complex the 
table is, we can reproduce it exactly like this all of the time.

Is this a known issue in MySQL with chained replication like this, or have I 
discovered a bug?

Server version information:

master1: Percona Server 5.5.28-29.1
master3: Percona Server 5.5.28-29.3
master3-slave1: Percona Server 5.5.20-55

-- 


Global Personals is a limited company registered in England and Wales. 
Registered number: 04880697. 
Registered office: Minton Place, Victoria Street, Windsor, Berkshire, SL4 
1EG, United Kingdom.

Re: Chain Replication QUestion

[Michael Dykman]

That is correct.


On Mon, May 6, 2013 at 11:06 AM, Richard Reina  wrote:

> To activate log-slave-updates do I just add "log-slave-updates" to the
> my.cnf file?
>
>
>
> 2013/4/30, Manuel Arostegui :
> > 2013/4/30 Richard Reina 
> >
> >> I have a few slaves set up on my local network that get updates from
> >> my main mysql database master. I was hoping to turn one into a master
> >> while keeping it a slave so that I can set up a chain.  Does anyone
> >> know where I can find a "how to" or other documentation for this
> >> specific task?
> >>
> >>
> > It is quite easy:
> >
> > Enable log-slave-updates in the slave you want to be a master.
> > Do a mysqldump -e --master-data=2 and put that mysqldump in the future
> > slaves. Take a look at the first lines of the mysqldump where you'll find
> > the position and logfile those slaves need to start the replication from.
> > You can also use xtrabackup if you like.
> >
> > Manuel.
> >
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>


-- 
 - michael dykman
 - mdyk...@gmail.com

 May the Source be with you.

Re: Chain Replication QUestion

[Richard Reina]

To activate log-slave-updates do I just add "log-slave-updates" to the
my.cnf file?



2013/4/30, Manuel Arostegui :
> 2013/4/30 Richard Reina 
>
>> I have a few slaves set up on my local network that get updates from
>> my main mysql database master. I was hoping to turn one into a master
>> while keeping it a slave so that I can set up a chain.  Does anyone
>> know where I can find a "how to" or other documentation for this
>> specific task?
>>
>>
> It is quite easy:
>
> Enable log-slave-updates in the slave you want to be a master.
> Do a mysqldump -e --master-data=2 and put that mysqldump in the future
> slaves. Take a look at the first lines of the mysqldump where you'll find
> the position and logfile those slaves need to start the replication from.
> You can also use xtrabackup if you like.
>
> Manuel.
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Chain Replication QUestion

[Rick James]

> 1) Enable log-bin on master2 (slave that will be converted to a master)
That does not 'convert' it -- it makes it both a Master and a Slave (a "Relay").

The CHANGE MASTER is probably correct, but it is difficult to find the right 
spot.
A simple way is to 
1. Stop all writes everywhere.
2. Wait for replication to catchup everywhere.
3. FLUSH LOGS everywhere.
4. Now CHANGE MASTER on the Slave to the start (POS=0 or 4) of the freshly 
created binlog in the machine that is the Slave's new Master.
5. Start writes.


> -Original Message-
> From: Richard Reina [mailto:gatorre...@gmail.com]
> Sent: Wednesday, May 01, 2013 6:00 AM
> To: Manuel Arostegui
> Cc: mysql@lists.mysql.com
> Subject: Re: Chain Replication QUestion
> 
> Hello Manuel,
> 
> Thank you for your reply. Could I do the following?:
> 
> 1) Enable log-bin on master2 (slave that will be converted to a master)
> 2) Enable log-slave-updates on master2
> 3) Execute CHANGE MASTER to on another existing slave so that it gets
> it's updates from master2 instead of master1.
> 
> Thanks for the help thus far.
> 
> 
> 2013/4/30, Manuel Arostegui :
> > 2013/4/30 Richard Reina 
> >
> >> I have a few slaves set up on my local network that get updates from
> >> my main mysql database master. I was hoping to turn one into a
> master
> >> while keeping it a slave so that I can set up a chain.  Does anyone
> >> know where I can find a "how to" or other documentation for this
> >> specific task?
> >>
> >>
> > It is quite easy:
> >
> > Enable log-slave-updates in the slave you want to be a master.
> > Do a mysqldump -e --master-data=2 and put that mysqldump in the
> future
> > slaves. Take a look at the first lines of the mysqldump where you'll
> > find the position and logfile those slaves need to start the
> replication from.
> > You can also use xtrabackup if you like.
> >
> > Manuel.
> >
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: Chain Replication QUestion

[Andrew Morgan]

If you're able to use MySQL 5.6 and enable GTIDs then it gets a whole lot 
simpler as you don't need to worry about finding the correct positions in the 
binary logs. Take a look at 
http://www.mysql.com/why-mysql/white-papers/mysql-replication-high-availability/
 and http://www.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/

Andrew.

> -Original Message-
> From: Rick James [mailto:rja...@yahoo-inc.com]
> Sent: 01 May 2013 16:29
> To: Richard Reina; Manuel Arostegui
> Cc: mysql@lists.mysql.com
> Subject: RE: Chain Replication QUestion
> 
> > 1) Enable log-bin on master2 (slave that will be converted to a
> master)
> That does not 'convert' it -- it makes it both a Master and a Slave (a
> "Relay").
> 
> The CHANGE MASTER is probably correct, but it is difficult to find the
> right spot.
> A simple way is to
> 1. Stop all writes everywhere.
> 2. Wait for replication to catchup everywhere.
> 3. FLUSH LOGS everywhere.
> 4. Now CHANGE MASTER on the Slave to the start (POS=0 or 4) of the
> freshly created binlog in the machine that is the Slave's new Master.
> 5. Start writes.
> 
> 
> > -Original Message-
> > From: Richard Reina [mailto:gatorre...@gmail.com]
> > Sent: Wednesday, May 01, 2013 6:00 AM
> > To: Manuel Arostegui
> > Cc: mysql@lists.mysql.com
> > Subject: Re: Chain Replication QUestion
> >
> > Hello Manuel,
> >
> > Thank you for your reply. Could I do the following?:
> >
> > 1) Enable log-bin on master2 (slave that will be converted to a
> master)
> > 2) Enable log-slave-updates on master2
> > 3) Execute CHANGE MASTER to on another existing slave so that it gets
> > it's updates from master2 instead of master1.
> >
> > Thanks for the help thus far.
> >
> >
> > 2013/4/30, Manuel Arostegui :
> > > 2013/4/30 Richard Reina 
> > >
> > >> I have a few slaves set up on my local network that get updates
> from
> > >> my main mysql database master. I was hoping to turn one into a
> > master
> > >> while keeping it a slave so that I can set up a chain.  Does
> anyone
> > >> know where I can find a "how to" or other documentation for this
> > >> specific task?
> > >>
> > >>
> > > It is quite easy:
> > >
> > > Enable log-slave-updates in the slave you want to be a master.
> > > Do a mysqldump -e --master-data=2 and put that mysqldump in the
> > future
> > > slaves. Take a look at the first lines of the mysqldump where
> you'll
> > > find the position and logfile those slaves need to start the
> > replication from.
> > > You can also use xtrabackup if you like.
> > >
> > > Manuel.
> > >
> >
> > --
> > MySQL General Mailing List
> > For list archives: http://lists.mysql.com/mysql
> > To unsubscribe:http://lists.mysql.com/mysql
> 
> 
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
> 

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Chain Replication QUestion

[Richard Reina]

Hello Manuel,

Thank you for your reply. Could I do the following?:

1) Enable log-bin on master2 (slave that will be converted to a master)
2) Enable log-slave-updates on master2
3) Execute CHANGE MASTER to on another existing slave so that it gets
it's updates from master2 instead of master1.

Thanks for the help thus far.


2013/4/30, Manuel Arostegui :
> 2013/4/30 Richard Reina 
>
>> I have a few slaves set up on my local network that get updates from
>> my main mysql database master. I was hoping to turn one into a master
>> while keeping it a slave so that I can set up a chain.  Does anyone
>> know where I can find a "how to" or other documentation for this
>> specific task?
>>
>>
> It is quite easy:
>
> Enable log-slave-updates in the slave you want to be a master.
> Do a mysqldump -e --master-data=2 and put that mysqldump in the future
> slaves. Take a look at the first lines of the mysqldump where you'll find
> the position and logfile those slaves need to start the replication from.
> You can also use xtrabackup if you like.
>
> Manuel.
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Chain Replication QUestion

[Manuel Arostegui]

2013/4/30 Richard Reina 

> I have a few slaves set up on my local network that get updates from
> my main mysql database master. I was hoping to turn one into a master
> while keeping it a slave so that I can set up a chain.  Does anyone
> know where I can find a "how to" or other documentation for this
> specific task?
>
>
It is quite easy:

Enable log-slave-updates in the slave you want to be a master.
Do a mysqldump -e --master-data=2 and put that mysqldump in the future
slaves. Take a look at the first lines of the mysqldump where you'll find
the position and logfile those slaves need to start the replication from.
You can also use xtrabackup if you like.

Manuel.

Re: Possible issues with replication filters

[Radoulov, Dimitre]

Hi Shawn,
thanks for replying!
Comments in-line.

On 25/03/2013 21:51, shawn green wrote:

Hello Dimitre,

On 3/24/2013 5:23 PM, Radoulov, Dimitre wrote:

[...]
The documentation states the following about the replicate-wild-do-table
option:

==
This option applies to tables, views, and triggers. It does not apply to
stored procedures and functions,
or events. To filter statements operating on the latter objects, use one
or more of the |--replicate-*-db| options.
==

Does anybody know how exactly this option doesn't apply for stored
procedures, functions or events?
Is the creation DDL "skipped" or their execution isn't replicated?
[...]


Stored procedures and Functions do not exist at the table level. They 
only exist at the global (system) level. Therefore there is no way to 
filter statements like CREATE PROCEDURE or DROP FUNCTION based on 
table-level substring matches.


I run a few tests (replicating from 5.5 to 5.6) and it seems that even 
only with Replicate_Wild_Do_Table = db_name.%
functions and procedures are replicated. I suppose that it's because 
we're forced to execute "use dbname"
before creating them (otherwise we get ERROR 1046 (3D000): No database 
selected) and because
they are associated to a schema 
(information_schema.routines.routine_schema).

It gets logged like this:

use `sakila`/*!*/;
[...]
DROP FUNCTION IF EXISTS `sampleFunc1`
/*!*/;
# at 209
#130326  7:25:10 server id 2  end_log_pos 524   Query thread_id=2 
exec_time=0 error_code=0

SET TIMESTAMP=1364279110/*!*/;
CREATE DEFINER=`root`@`localhost` FUNCTION `sampleFunc1`(a INT, b INT) 
RETURNS tinyint(1)

[...]

At this point, I don't understand why the documentation states that 
Replicate_Wild_Do_Table doesn't apply

to procedures and functions. Am I missing something?



The key to notice is 'operating on' in the text you quoted. If you 
limit execution of those DDL statements to just a few databases, then 
any user with enough privileges that start the DDL command from the 
context of the permitted database will be able to affect those objects 
on the slave via replication. If you change one of them on the master 
and you do it from the context of a database that is not on the 'do 
list', then that change will not be applied to the slave via replication.


Based on your example, a DBA starting in the db_name1 database or 
db_name2 database would be able to affect a PROCEDURE or FUNCTION on 
the master and through replication, the same command would execute on 
the slave.


Yes, thanks for pointing this out.
The same applies for other type of SQL that spans multiple databases 
(and Replicate_Wild_Do_Table is enabled for those databases).


Controlling this behavior is one of the uses of the 'principle of 
least privileges'. In short, it means you give each user just enough 
rights to do what it is they are supposed to do. For example, you want 
very few users to have the 'super' privilege or the 'with create 
option' option on their accounts. In my position I see many servers 
operating where every user (including applications) are operating with 
root privileges.  This is as bad for databases as it is for operating 
systems.


Completely agreed!


Best regards
Dimitre


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Possible issues with replication filters

[shawn green]

Hello Dimitre,

On 3/24/2013 5:23 PM, Radoulov, Dimitre wrote:

Hi all,
we need to move a few databases from one host to another.
During the process we need to use replication filters (to replicate
only the databases to be moved).
The current MySQL version is 5.5, the new one will be 5.6.
We're using "mixed" binlog_format.

We're planing to use:

replicate-wild-do-table=db_name1.%
replicate-wild-do-table=db_name2.%
...

The documentation states the following about the replicate-wild-do-table
option:

==
This option applies to tables, views, and triggers. It does not apply to
stored procedures and functions,
or events. To filter statements operating on the latter objects, use one
or more of the |--replicate-*-db| options.
==

Does anybody know how exactly this option doesn't apply for stored
procedures, functions or events?
Is the creation DDL "skipped" or their execution isn't replicated?

Anyway, I assume that with the following configuration (i.e. by adding
replicate-do-db),
I'll be able to replicate all statements related to db_name1 and db_name2
(excluding possible grant/revoke statements, because the mysql system
database
won't be replicated).

Is that correct?
Is there a better (more robust) way to replicate a subset of databases?

replicate-wild-do-table=db_name1.%
replicate-wild-do-table=db_name2.%
...
replicate-do-db=db_name1
replicate-do-db=db_name2
...


Thanks
Dimitre



Stored procedures and Functions do not exist at the table level. They 
only exist at the global (system) level. Therefore there is no way to 
filter statements like CREATE PROCEDURE or DROP FUNCTION based on 
table-level substring matches.


The key to notice is 'operating on' in the text you quoted. If you limit 
execution of those DDL statements to just a few databases, then any user 
with enough privileges that start the DDL command from the context of 
the permitted database will be able to affect those objects on the slave 
via replication. If you change one of them on the master and you do it 
from the context of a database that is not on the 'do list', then that 
change will not be applied to the slave via replication.


Based on your example, a DBA starting in the db_name1 database or 
db_name2 database would be able to affect a PROCEDURE or FUNCTION on the 
master and through replication, the same command would execute on the 
slave.


Controlling this behavior is one of the uses of the 'principle of least 
privileges'. In short, it means you give each user just enough rights to 
do what it is they are supposed to do. For example, you want very few 
users to have the 'super' privilege or the 'with create option' option 
on their accounts. In my position I see many servers operating where 
every user (including applications) are operating with root privileges. 
 This is as bad for databases as it is for operating systems.


Yours,
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc. - Hardware and Software, Engineered to Work Together.
Office: Blountville, TN

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Possible issues with replication filters

[Radoulov, Dimitre]

Hi all,
we need to move a few databases from one host to another.
During the process we need to use replication filters (to replicate
only the databases to be moved).
The current MySQL version is 5.5, the new one will be 5.6.
We're using "mixed" binlog_format.

We're planing to use:

replicate-wild-do-table=db_name1.%
replicate-wild-do-table=db_name2.%
...

The documentation states the following about the replicate-wild-do-table 
option:


==
This option applies to tables, views, and triggers. It does not apply to 
stored procedures and functions,
or events. To filter statements operating on the latter objects, use one 
or more of the |--replicate-*-db| options.

==

Does anybody know how exactly this option doesn't apply for stored 
procedures, functions or events?

Is the creation DDL "skipped" or their execution isn't replicated?

Anyway, I assume that with the following configuration (i.e. by adding 
replicate-do-db),

I'll be able to replicate all statements related to db_name1 and db_name2
(excluding possible grant/revoke statements, because the mysql system 
database

won't be replicated).

Is that correct?
Is there a better (more robust) way to replicate a subset of databases?

replicate-wild-do-table=db_name1.%
replicate-wild-do-table=db_name2.%
...
replicate-do-db=db_name1
replicate-do-db=db_name2
...


Thanks
Dimitre

Re: Multi-Master Replication Manager - monitor works but mmm_control show executes very long and sometimes returns no output.

[Rafał Radecki]

I have used debug on monitor and agent:
- on agent I see:
/etc/init.d/mysql-mmm-agent start
Starting MMM Agent Daemon: 2013/03/13 16:19:52 DEBUG Created pid file
'/var/run/mysql-mmm/mmm_agentd.pid' with pid 13640
2013/03/13 16:19:52 DEBUG Listener: Waiting for connection...
2013/03/13 16:19:53 DEBUG Listener: Connect!
2013/03/13 16:19:53 DEBUG Daemon: Command =
'SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1'
2013/03/13 16:19:53 DEBUG Received Command
SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1
2013/03/13 16:19:53  INFO We have some new roles added or old rules deleted!
2013/03/13 16:19:53  INFO Added:   reader(192.168.0.1)
2013/03/13 16:19:53 DEBUG Executing
/usr/libexec/mysql-mmm//agent/configure_ip vlan1503 192.168.0.1
2013/03/13 16:19:53 DEBUG Fetching uptime from /proc/uptime
2013/03/13 16:19:53 DEBUG Uptime is 4327.64
2013/03/13 16:19:53 DEBUG Daemon: Answer = 'OK: Status applied successfully!'
2013/03/13 16:19:53 DEBUG Listener: Disconnect!
2013/03/13 16:19:53 DEBUG Executing
/usr/libexec/mysql-mmm//agent/configure_ip vlan1503 192.168.0.1
2013/03/13 16:19:53 DEBUG Listener: Waiting for connection...
2013/03/13 16:19:56 DEBUG Listener: Connect!
2013/03/13 16:19:56 DEBUG Daemon: Command =
'SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1'
2013/03/13 16:19:56 DEBUG Received Command
SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1
2013/03/13 16:19:56 DEBUG Fetching uptime from /proc/uptime
2013/03/13 16:19:56 DEBUG Uptime is 4330.64
2013/03/13 16:19:56 DEBUG Daemon: Answer = 'OK: Status applied successfully!'
2013/03/13 16:19:56 DEBUG Listener: Disconnect!
2013/03/13 16:19:56 DEBUG Executing
/usr/libexec/mysql-mmm//agent/configure_ip vlan1503 192.168.0.1
2013/03/13 16:19:56 DEBUG Listener: Waiting for connection...
2013/03/13 16:19:59 DEBUG Listener: Connect!
2013/03/13 16:19:59 DEBUG Daemon: Command =
'SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1'
2013/03/13 16:19:59 DEBUG Received Command
SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1
2013/03/13 16:19:59 DEBUG Fetching uptime from /proc/uptime
2013/03/13 16:19:59 DEBUG Uptime is 4333.62
2013/03/13 16:19:59 DEBUG Daemon: Answer = 'OK: Status applied successfully!'
2013/03/13 16:19:59 DEBUG Listener: Disconnect!
2013/03/13 16:19:59 DEBUG Executing
/usr/libexec/mysql-mmm//agent/configure_ip vlan1503 192.168.0.1
2013/03/13 16:19:59 DEBUG Listener: Waiting for connection...
2013/03/13 16:20:02 DEBUG Listener: Connect!
2013/03/13 16:20:02 DEBUG Daemon: Command =
'SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1'
2013/03/13 16:20:02 DEBUG Received Command
SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1
2013/03/13 16:20:02 DEBUG Fetching uptime from /proc/uptime
2013/03/13 16:20:02 DEBUG Uptime is 4336.63
2013/03/13 16:20:02 DEBUG Daemon: Answer = 'OK: Status applied successfully!'
2013/03/13 16:20:02 DEBUG Listener: Disconnect!
2013/03/13 16:20:02 DEBUG Executing
/usr/libexec/mysql-mmm//agent/configure_ip vlan1503 192.168.0.1
2013/03/13 16:20:02 DEBUG Listener: Waiting for connection...
2013/03/13 16:20:05 DEBUG Listener: Connect!
2013/03/13 16:20:05 DEBUG Daemon: Command =
'SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1'
2013/03/13 16:20:05 DEBUG Received Command
SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1
2013/03/13 16:20:05 DEBUG Fetching uptime from /proc/uptime
2013/03/13 16:20:05 DEBUG Uptime is 4339.61
2013/03/13 16:20:05 DEBUG Daemon: Answer = 'OK: Status applied successfully!'
2013/03/13 16:20:05 DEBUG Listener: Disconnect!
2013/03/13 16:20:05 DEBUG Executing
/usr/libexec/mysql-mmm//agent/configure_ip vlan1503 192.168.0.1
2013/03/13 16:20:05 DEBUG Listener: Waiting for connection...
2013/03/13 16:20:08 DEBUG Listener: Waiting for connection...
2013/03/13 16:20:08 DEBUG Listener: Connect!
2013/03/13 16:20:08 DEBUG Daemon: Command =
'SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1'
2013/03/13 16:20:08 DEBUG Received Command
SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1
2013/03/13 16:20:08 DEBUG Fetching uptime from /proc/uptime
2013/03/13 16:20:08 DEBUG Uptime is 4342.64
2013/03/13 16:20:08 DEBUG Daemon: Answer = 'OK: Status applied successfully!'
2013/03/13 16:20:08 DEBUG Listener: Disconnect!
2013/03/13 16:20:08 DEBUG Executing
/usr/libexec/mysql-mmm//agent/configure_ip vlan1503 192.168.0.1
2013/03/13 16:20:08 DEBUG Listener: Waiting for connection...
2013/03/13 16:20:11 DEBUG Listener: Connect!
2013/03/13 16:20:11 DEBUG Daemon: Command =
'SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1'
2013/03/13 16:20:11 DEBUG Received Command
SET_STATUS|1|db2|ONLINE|reader(192.168.0.1)|db1
2013/03/13 16:20:11 DEBUG Fetching uptime from /proc/uptime
2013/03/13 16:20:11 DEBUG Uptime is 4345.60
2013/03/13 16:20:11 DEBUG Daemon: Answer = 'OK: Status applied successfully!'
2013/03/13 16:20:11 DEBUG Listener: Disconnect!
2013/03/13 16:20:11 DEBUG Executing
/usr/libexec/mysql-mmm//agent/configure_ip vlan1503 192.168.0.1
2013/03/13 16:20:11 DEBUG Listener: Waiting for connection...
2013/03/13 16:20:14 DEBUG Listener: Connect!
2013/03/13 16:20:14 DEBUG Daemo

Re: Multi-Master Replication Manager - monitor works but mmm_control show executes very long and sometimes returns no output.

[Igor Shevtsov]

Hi,
I think MMM's proper functionality depends a lot on network interface 
configuration and sometimes it behaves odd. I moved to MySQL or in my 
case to MariaDB + Galera + HaProxy configuration. It's not difficult to 
setup. There is no SPO as in case with monitor host in MMM, much more 
robust solution. There are limitations though such as you InnoDB is the 
only one being supported.

Cheers,
Igor


On 11/03/13 09:22, Manuel Arostegui wrote:

2013/3/11 Rafał Radecki 


Hi All.

I use:

cat /etc/redhat-release
CentOS release 6.3 (Final)

uname -a
Linux prod1.local 2.6.32-279.14.1.el6.x86_64 #1 SMP Tue Nov 6 23:43:09
UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

on db host:
rpm -qa | grep mmm
mysql-mmm-2.2.1-1.el6.noarch
bmysql-mmm-agent-2.2.1-1.el6.noarch

on monitor host:
rpm -qa | grep mmm
mysql-mmm-monitor-2.2.1-1.el6.noarch
mysql-mmm-2.2.1-1.el6.noarch
mysql-mmm-tools-2.2.1-1.el6.noarch

When I make mmm_control show/checks the command executes for example
for ~15 seconds and sometimes returns no output. In logs there is no
info about problems and overall the monitor performs well. But I
cannot use mmm_control to check its status. The servers are not over
loaded. I have restarted agents and monitor but that has not resolved
the problem. When I have rebooted the monitor host first use of
mmm_control show was ok but then the problem was active again.

Any advice? Have you had any similar problems?


Hello,

Have you tried to set "debug 1" in your mmm_common.conf and start the
monitor? You might find useful output in there.

Manuel.





--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Multi-Master Replication Manager - monitor works but mmm_control show executes very long and sometimes returns no output.

[Manuel Arostegui]

2013/3/11 Rafał Radecki 

> Hi All.
>
> I use:
>
> cat /etc/redhat-release
> CentOS release 6.3 (Final)
>
> uname -a
> Linux prod1.local 2.6.32-279.14.1.el6.x86_64 #1 SMP Tue Nov 6 23:43:09
> UTC 2012 x86_64 x86_64 x86_64 GNU/Linux
>
> on db host:
> rpm -qa | grep mmm
> mysql-mmm-2.2.1-1.el6.noarch
> bmysql-mmm-agent-2.2.1-1.el6.noarch
>
> on monitor host:
> rpm -qa | grep mmm
> mysql-mmm-monitor-2.2.1-1.el6.noarch
> mysql-mmm-2.2.1-1.el6.noarch
> mysql-mmm-tools-2.2.1-1.el6.noarch
>
> When I make mmm_control show/checks the command executes for example
> for ~15 seconds and sometimes returns no output. In logs there is no
> info about problems and overall the monitor performs well. But I
> cannot use mmm_control to check its status. The servers are not over
> loaded. I have restarted agents and monitor but that has not resolved
> the problem. When I have rebooted the monitor host first use of
> mmm_control show was ok but then the problem was active again.
>
> Any advice? Have you had any similar problems?


Hello,

Have you tried to set "debug 1" in your mmm_common.conf and start the
monitor? You might find useful output in there.

Manuel.


-- 
Manuel Aróstegui
Systems Team
tuenti.com

Multi-Master Replication Manager - monitor works but mmm_control show executes very long and sometimes returns no output.

[Rafał Radecki]

Hi All.

I use:

cat /etc/redhat-release
CentOS release 6.3 (Final)

uname -a
Linux prod1.local 2.6.32-279.14.1.el6.x86_64 #1 SMP Tue Nov 6 23:43:09
UTC 2012 x86_64 x86_64 x86_64 GNU/Linux

on db host:
rpm -qa | grep mmm
mysql-mmm-2.2.1-1.el6.noarch
bmysql-mmm-agent-2.2.1-1.el6.noarch

on monitor host:
rpm -qa | grep mmm
mysql-mmm-monitor-2.2.1-1.el6.noarch
mysql-mmm-2.2.1-1.el6.noarch
mysql-mmm-tools-2.2.1-1.el6.noarch

When I make mmm_control show/checks the command executes for example
for ~15 seconds and sometimes returns no output. In logs there is no
info about problems and overall the monitor performs well. But I
cannot use mmm_control to check its status. The servers are not over
loaded. I have restarted agents and monitor but that has not resolved
the problem. When I have rebooted the monitor host first use of
mmm_control show was ok but then the problem was active again.

Any advice? Have you had any similar problems?

Best regards,
Rafal Radecki.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: replication fails after upgrade to 5.6

[Mike Franon]

HI Trimurthy,

Just curious won't it make that data inconsistent on the slave?


Thanks



On Fri, Feb 22, 2013 at 12:15 AM, Trimurthy  wrote:
> Dear Mike Franon,
>
>  i have also faced the same problem while setting up the replication. by
> that time i have added the error no to
> slave-skip-errors=1062 configuration file and restarted the server. after
> that replication gets work properly. once try this one.
> and one thing that i would like to inform that master server version always
> should be less than or equal to the slave version.
>
> Thanks & Kind Regards,
> TRIMURTHY
>
>
>
>
> 
> From: "Mike Franon" 
> Sent: Thursday, February 21, 2013 11:43 PM
> To: "Reindl Harald" 
> Subject: Re: replication fails after upgrade to 5.6
>
> Unfortunately that is not possible at the moment, I have 6 slaves off
> the one master, also I want to test it as much as possible before
> upgrading the master.
>
> Is the only way to really fix this is to upgrade master? I thought
> you can replicate from master -> slave if version is higher on slave,
> just not the other way around?
>
> Thanks
>
> On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald 
> wrote:
>> update the master ASAP in a short timeframe too
>> and re-init replication if needed
>>
>> normally both should have exactly the same version
>>
>> the slaves must be updated first because otherwise
>> a master may write instructions in the binlog the older
>> slave does not undersatdn at all, but as said normally
>> both should have the same version
>>
>> Am 21.02.2013 18:03, schrieb Mike Franon:
>>> So I created a new test box on AWS, and just did one upgrade from
>>> 5.0.96 to 5.1, like I did before and replication will not work from a
>>> master with 5.0.96 to a slave with 5.1.68
>>>
>>> I keep getting Error 1062, Duplicate Entry for key
>>>
>>> I get no errors when I do a mysql_upgrade, all comes back ok.
>>>
>>> I was curious if anyone had any ideas?
>>>
>>> Thanks
>>>
>>> On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon 
>>> wrote:
>>>> This is on a slave, i only upgraded on one box which is the slave i
>>>> have not touched master
>>>>
>>>> On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald 
>>>> wrote:
>>>>>
>>>>>
>>>>> Am 20.02.2013 23:27, schrieb Mike Franon:
>>>>>> So I successfully upgraded a test db server from 5.0.96 all the way up
>>>>>> to 5.6
>>>>>>
>>>>>> Replication as the slave, where the master is 5.0.96, started working
>>>>>> for about 10 minutes and then got the following error:
>>>>>>
>>>>>> [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on
>>>>>> query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code:
>>>>>> 1062
>>>>>>
>>>>>> All of our other slaves on 5.0.96 are fine, so I know it has to do
>>>>>> with 5.6 but just not sure what, when ir an mysql_upgrade everything
>>>>>> was OK
>>>>>
>>>>> did you surely upgrade and restart the slaves first?
>>>>>
>>>>> i personally would NOT go to 5.6 now
>>>>>
>>>>> it is a very young release and looking and the typical changelogs
>>>>> replication has always the most fixed bugs
>>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: replication fails after upgrade to 5.6

[Reindl Harald]

and where did anybody say the opposite in this thread?

Am 21.02.2013 22:25, schrieb Rick James:
> It is safer to have the Slave be a newer version.
> 
>> -Original Message-
>> From: Reindl Harald [mailto:h.rei...@thelounge.net]
>> Sent: Thursday, February 21, 2013 10:30 AM
>> To: mysql@lists.mysql.com
>> Subject: Re: replication fails after upgrade to 5.6
>>
>> Am 21.02.2013 19:11, schrieb Mike Franon:
>>> Is the only way to really fix this is to upgrade master?  I thought
>>> you can replicate from master -> slave if version is higher on slave,
>>> just not the other way around?
>>
>> normally no
>>
>> but take a look at the changelogs of myslq in the last years
>> 80 out of 100 fixes are replication bugs
>>
>>> On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald
>>  wrote:
>>>> update the master ASAP in a short timeframe too and re-init
>>>> replication if needed
>>>>
>>>> normally both should have exactly the same version
>>>>
>>>> the slaves must be updated first because otherwise a master may
>> write
>>>> instructions in the binlog the older slave does not undersatdn at
>>>> all, but as said normally both should have the same version
>>>>
>>>> Am 21.02.2013 18:03, schrieb Mike Franon:
>>>>> So I created a new test box on AWS, and just did one upgrade from
>>>>> 5.0.96 to 5.1, like I did before and replication will not work from
>>>>> a master with 5.0.96 to a slave with 5.1.68
>>>>>
>>>>> I keep getting Error 1062, Duplicate Entry for key
>>>>>
>>>>> I get no errors when I do a mysql_upgrade, all comes back ok.
>>>>>
>>>>> I was curious if anyone had any ideas?
>>>>>
>>>>> Thanks
>>>>>
>>>>> On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon 
>> wrote:
>>>>>> This is on a slave, i only upgraded on one box which is the slave
>> i
>>>>>> have not touched master
>>>>>>
>>>>>> On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald
>>  wrote:
>>>>>>>
>>>>>>>
>>>>>>> Am 20.02.2013 23:27, schrieb Mike Franon:
>>>>>>>> So I successfully upgraded a test db server from 5.0.96 all the
>>>>>>>> way up to 5.6
>>>>>>>>
>>>>>>>> Replication as the slave, where the master is 5.0.96, started
>>>>>>>> working for about 10 minutes and then got the following error:
>>>>>>>>
>>>>>>>> [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key
>>>>>>>> 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE
>> IGNORE , Error_code:
>>>>>>>> 1062
>>>>>>>>
>>>>>>>> All of our other slaves on 5.0.96 are fine, so I know it has to
>>>>>>>> do with 5.6 but just not sure what, when ir an mysql_upgrade
>>>>>>>> everything was OK
>>>>>>>
>>>>>>> did you surely upgrade and restart the slaves first?
>>>>>>>
>>>>>>> i personally would NOT go to 5.6 now
>>>>>>>
>>>>>>> it is a very young release and looking and the typical changelogs
>>>>>>> replication has always the most fixed bugs



signature.asc
Description: OpenPGP digital signature

RE: replication fails after upgrade to 5.6

[Rick James]

It is safer to have the Slave be a newer version.

> -Original Message-
> From: Reindl Harald [mailto:h.rei...@thelounge.net]
> Sent: Thursday, February 21, 2013 10:30 AM
> To: mysql@lists.mysql.com
> Subject: Re: replication fails after upgrade to 5.6
> 
> 
> 
> Am 21.02.2013 19:11, schrieb Mike Franon:
> > Is the only way to really fix this is to upgrade master?  I thought
> > you can replicate from master -> slave if version is higher on slave,
> > just not the other way around?
> 
> normally no
> 
> but take a look at the changelogs of myslq in the last years
> 80 out of 100 fixes are replication bugs
> 
> > On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald
>  wrote:
> >> update the master ASAP in a short timeframe too and re-init
> >> replication if needed
> >>
> >> normally both should have exactly the same version
> >>
> >> the slaves must be updated first because otherwise a master may
> write
> >> instructions in the binlog the older slave does not undersatdn at
> >> all, but as said normally both should have the same version
> >>
> >> Am 21.02.2013 18:03, schrieb Mike Franon:
> >>> So I created a new test box on AWS, and just did one upgrade from
> >>> 5.0.96 to 5.1, like I did before and replication will not work from
> >>> a master with 5.0.96 to a slave with 5.1.68
> >>>
> >>> I keep getting Error 1062, Duplicate Entry for key
> >>>
> >>> I get no errors when I do a mysql_upgrade, all comes back ok.
> >>>
> >>> I was curious if anyone had any ideas?
> >>>
> >>> Thanks
> >>>
> >>> On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon 
> wrote:
> >>>> This is on a slave, i only upgraded on one box which is the slave
> i
> >>>> have not touched master
> >>>>
> >>>> On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald
>  wrote:
> >>>>>
> >>>>>
> >>>>> Am 20.02.2013 23:27, schrieb Mike Franon:
> >>>>>> So I successfully upgraded a test db server from 5.0.96 all the
> >>>>>> way up to 5.6
> >>>>>>
> >>>>>> Replication as the slave, where the master is 5.0.96, started
> >>>>>> working for about 10 minutes and then got the following error:
> >>>>>>
> >>>>>> [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key
> >>>>>> 'PRIMARY'' on query. Default database: 'test'. Query: 'UPDATE
> IGNORE , Error_code:
> >>>>>> 1062
> >>>>>>
> >>>>>> All of our other slaves on 5.0.96 are fine, so I know it has to
> >>>>>> do with 5.6 but just not sure what, when ir an mysql_upgrade
> >>>>>> everything was OK
> >>>>>
> >>>>> did you surely upgrade and restart the slaves first?
> >>>>>
> >>>>> i personally would NOT go to 5.6 now
> >>>>>
> >>>>> it is a very young release and looking and the typical changelogs
> >>>>> replication has always the most fixed bugs


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: replication fails after upgrade to 5.6

[Reindl Harald]

Am 21.02.2013 19:11, schrieb Mike Franon:
> Is the only way to really fix this is to upgrade master?  I thought
> you can replicate from master -> slave if version is higher on slave,
> just not the other way around?

normally no

but take a look at the changelogs of myslq in the last years
80 out of 100 fixes are replication bugs

> On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald  wrote:
>> update the master ASAP in a short timeframe too
>> and re-init replication if needed
>>
>> normally both should have exactly the same version
>>
>> the slaves must be updated first because otherwise
>> a master may write instructions in the binlog the older
>> slave does not undersatdn at all, but as said normally
>> both should have the same version
>>
>> Am 21.02.2013 18:03, schrieb Mike Franon:
>>> So I created a new test box on AWS, and just did one upgrade from
>>> 5.0.96 to 5.1, like I did before and replication will not work from a
>>> master with 5.0.96 to a slave with 5.1.68
>>>
>>> I keep getting Error 1062, Duplicate Entry for key
>>>
>>> I get no errors when I do a mysql_upgrade, all comes back ok.
>>>
>>> I was curious if anyone had any ideas?
>>>
>>> Thanks
>>>
>>> On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon  wrote:
>>>> This is on a slave, i only upgraded on one box which is the slave i
>>>> have not touched master
>>>>
>>>> On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald  
>>>> wrote:
>>>>>
>>>>>
>>>>> Am 20.02.2013 23:27, schrieb Mike Franon:
>>>>>> So I successfully upgraded a test db server from 5.0.96 all the way up 
>>>>>> to 5.6
>>>>>>
>>>>>> Replication as the slave, where the master is 5.0.96, started working
>>>>>> for about 10 minutes and then got the following error:
>>>>>>
>>>>>> [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on
>>>>>> query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code:
>>>>>> 1062
>>>>>>
>>>>>> All of our other slaves on 5.0.96 are fine, so I know it has to do
>>>>>> with 5.6 but just not sure what, when ir an mysql_upgrade everything
>>>>>> was OK
>>>>>
>>>>> did you surely upgrade and restart the slaves first?
>>>>>
>>>>> i personally would NOT go to 5.6 now
>>>>>
>>>>> it is a very young release and looking and the typical changelogs
>>>>> replication has always the most fixed bugs



signature.asc
Description: OpenPGP digital signature

Re: replication fails after upgrade to 5.6

[Mike Franon]

Unfortunately that is not possible at the moment, I have 6 slaves off
the one master, also I want to test it as much as possible before
upgrading the master.

Is the only way to really fix this is to upgrade master?  I thought
you can replicate from master -> slave if version is higher on slave,
just not the other way around?

Thanks

On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald  wrote:
> update the master ASAP in a short timeframe too
> and re-init replication if needed
>
> normally both should have exactly the same version
>
> the slaves must be updated first because otherwise
> a master may write instructions in the binlog the older
> slave does not undersatdn at all, but as said normally
> both should have the same version
>
> Am 21.02.2013 18:03, schrieb Mike Franon:
>> So I created a new test box on AWS, and just did one upgrade from
>> 5.0.96 to 5.1, like I did before and replication will not work from a
>> master with 5.0.96 to a slave with 5.1.68
>>
>> I keep getting Error 1062, Duplicate Entry for key
>>
>> I get no errors when I do a mysql_upgrade, all comes back ok.
>>
>> I was curious if anyone had any ideas?
>>
>> Thanks
>>
>> On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon  wrote:
>>> This is on a slave, i only upgraded on one box which is the slave i
>>> have not touched master
>>>
>>> On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald  
>>> wrote:
>>>>
>>>>
>>>> Am 20.02.2013 23:27, schrieb Mike Franon:
>>>>> So I successfully upgraded a test db server from 5.0.96 all the way up to 
>>>>> 5.6
>>>>>
>>>>> Replication as the slave, where the master is 5.0.96, started working
>>>>> for about 10 minutes and then got the following error:
>>>>>
>>>>> [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on
>>>>> query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code:
>>>>> 1062
>>>>>
>>>>> All of our other slaves on 5.0.96 are fine, so I know it has to do
>>>>> with 5.6 but just not sure what, when ir an mysql_upgrade everything
>>>>> was OK
>>>>
>>>> did you surely upgrade and restart the slaves first?
>>>>
>>>> i personally would NOT go to 5.6 now
>>>>
>>>> it is a very young release and looking and the typical changelogs
>>>> replication has always the most fixed bugs
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: replication fails after upgrade to 5.6

[Reindl Harald]

update the master ASAP in a short timeframe too
and re-init replication if needed

normally both should have exactly the same version

the slaves must be updated first because otherwise
a master may write instructions in the binlog the older
slave does not undersatdn at all, but as said normally
both should have the same version

Am 21.02.2013 18:03, schrieb Mike Franon:
> So I created a new test box on AWS, and just did one upgrade from
> 5.0.96 to 5.1, like I did before and replication will not work from a
> master with 5.0.96 to a slave with 5.1.68
> 
> I keep getting Error 1062, Duplicate Entry for key
> 
> I get no errors when I do a mysql_upgrade, all comes back ok.
> 
> I was curious if anyone had any ideas?
> 
> Thanks
> 
> On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon  wrote:
>> This is on a slave, i only upgraded on one box which is the slave i
>> have not touched master
>>
>> On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald  
>> wrote:
>>>
>>>
>>> Am 20.02.2013 23:27, schrieb Mike Franon:
>>>> So I successfully upgraded a test db server from 5.0.96 all the way up to 
>>>> 5.6
>>>>
>>>> Replication as the slave, where the master is 5.0.96, started working
>>>> for about 10 minutes and then got the following error:
>>>>
>>>> [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on
>>>> query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code:
>>>> 1062
>>>>
>>>> All of our other slaves on 5.0.96 are fine, so I know it has to do
>>>> with 5.6 but just not sure what, when ir an mysql_upgrade everything
>>>> was OK
>>>
>>> did you surely upgrade and restart the slaves first?
>>>
>>> i personally would NOT go to 5.6 now
>>>
>>> it is a very young release and looking and the typical changelogs
>>> replication has always the most fixed bugs



signature.asc
Description: OpenPGP digital signature

Re: replication fails after upgrade to 5.6

[Mike Franon]

So I created a new test box on AWS, and just did one upgrade from
5.0.96 to 5.1, like I did before and replication will not work from a
master with 5.0.96 to a slave with 5.1.68

I keep getting Error 1062, Duplicate Entry for key

I get no errors when I do a mysql_upgrade, all comes back ok.

I was curious if anyone had any ideas?

Thanks

On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon  wrote:
> This is on a slave, i only upgraded on one box which is the slave i
> have not touched master
>
> On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald  wrote:
>>
>>
>> Am 20.02.2013 23:27, schrieb Mike Franon:
>>> So I successfully upgraded a test db server from 5.0.96 all the way up to 
>>> 5.6
>>>
>>> Replication as the slave, where the master is 5.0.96, started working
>>> for about 10 minutes and then got the following error:
>>>
>>> [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on
>>> query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code:
>>> 1062
>>>
>>> All of our other slaves on 5.0.96 are fine, so I know it has to do
>>> with 5.6 but just not sure what, when ir an mysql_upgrade everything
>>> was OK
>>
>> did you surely upgrade and restart the slaves first?
>>
>> i personally would NOT go to 5.6 now
>>
>> it is a very young release and looking and the typical changelogs
>> replication has always the most fixed bugs
>>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

replication fails after upgrade to 5.6

[Mike Franon]

So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6

Replication as the slave, where the master is 5.0.96, started working
for about 10 minutes and then got the following error:

[ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on
query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code:
1062


All of our other slaves on 5.0.96 are fine, so I know it has to do
with 5.6 but just not sure what, when ir an mysql_upgrade everything
was OK

Thanks

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: replication fails after upgrade to 5.6

[Mike Franon]

This is on a slave, i only upgraded on one box which is the slave i
have not touched master

On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald  wrote:
>
>
> Am 20.02.2013 23:27, schrieb Mike Franon:
>> So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6
>>
>> Replication as the slave, where the master is 5.0.96, started working
>> for about 10 minutes and then got the following error:
>>
>> [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on
>> query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code:
>> 1062
>>
>> All of our other slaves on 5.0.96 are fine, so I know it has to do
>> with 5.6 but just not sure what, when ir an mysql_upgrade everything
>> was OK
>
> did you surely upgrade and restart the slaves first?
>
> i personally would NOT go to 5.6 now
>
> it is a very young release and looking and the typical changelogs
> replication has always the most fixed bugs
>

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: replication fails after upgrade to 5.6

[Reindl Harald]

Am 20.02.2013 23:27, schrieb Mike Franon:
> So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6
> 
> Replication as the slave, where the master is 5.0.96, started working
> for about 10 minutes and then got the following error:
> 
> [ERROR] Slave SQL: Error 'Duplicate entry 'data' for key 'PRIMARY'' on
> query. Default database: 'test'. Query: 'UPDATE IGNORE , Error_code:
> 1062
> 
> All of our other slaves on 5.0.96 are fine, so I know it has to do
> with 5.6 but just not sure what, when ir an mysql_upgrade everything
> was OK

did you surely upgrade and restart the slaves first?

i personally would NOT go to 5.6 now

it is a very young release and looking and the typical changelogs
replication has always the most fixed bugs



signature.asc
Description: OpenPGP digital signature

Re: slave replication with lots of 'duplicate entry' errors

[Manuel Arostegui]

2013/2/14 Robert Citek 

>
>
> According to the client, nothing is writing to the slave and
> everything is being logged at the master.  I have not had the
> opportunity to independently verified any of this, yet.  I do know
> that the slave is not in read-only mode, but rather "we promise not to
> write to it" mode.
>

The only way to be totally safe is to enable it.


>
> At the moment, I am trying to come up with plausible explanations for
> the observations.


If you are sure you're setting the correct master IP, logfile and position
that appears in your mysqldump...the only explanation is...they are writing
directly into the slave.

Manuel.

Re: slave replication with lots of 'duplicate entry' errors

[Robert Citek]

Agreed.  Will do that along with several other possible changes.  But
for the moment, I'm still gathering information and coming up with
plausible models.

Will also be turning on general mysql logging on both Master and
Slave, at least briefly, to see what statements are being run on both.

Regards,
- Robert

On Thu, Feb 14, 2013 at 6:13 PM, Rick James  wrote:
> Recommend, for security reasons, you rectify that.
> If you need some SUPER action, create a stored procedure with privileges 
> 'creator', so the security hole is still mostly contained.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: slave replication with lots of 'duplicate entry' errors

[Manuel Arostegui]

2013/2/13 Robert Citek 

> On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek 
> wrote:
> > Any other possibilities?  Do other scenarios become likely if there
> > are two or more tables?
> >
> > Of those, which are the most likely?
>
> [from off-list responder]:
> > Other possibility: The replication is reading from master not from the
> point when the dump was done, but some time before and is fetching insert
> statements which are already in the dump.
>
> To prevent that I used the coordinates in the dump file included with
> --master-data=2.  Could the coordinates still be off?


Hello,

Are you sure nothing is getting inserted directly into the slave? Is it in
read only mode?
If you're starting replication using the values provided by --master-data=2
(which should be something like):

 -- Position to start replication or point-in-time recovery from

-- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000974',
MASTER_LOG_POS=240814775;

And if you're using the right IP, there's no reason to have duplicate
entries unless someone is writing directly into the slave.

Manuel.

Re: slave replication with lots of 'duplicate entry' errors

[Robert Citek]

Yes.  Except for a handful of static MyISAM tables.  But the tables
that are experiencing the issues are all InnoDB and large (a dozen or
so fields, but lots of records.)

Regards,
- Robert

On Thu, Feb 14, 2013 at 5:59 PM, Singer Wang  wrote:
> Are you using all InnoDB?
>
> S

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: slave replication with lots of 'duplicate entry' errors

[Robert Citek]

On Thu, Feb 14, 2013 at 4:54 PM, Manuel Arostegui  wrote:
> 2013/2/13 Robert Citek 
>>
>> On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek 
>> wrote:
>> > Any other possibilities?  Do other scenarios become likely if there
>> > are two or more tables?
>> >
>> > Of those, which are the most likely?
>>
>> [from off-list responder]:
>> > Other possibility: The replication is reading from master not from the
>> > point when the dump was done, but some time before and is fetching insert
>> > statements which are already in the dump.
>>
>> To prevent that I used the coordinates in the dump file included with
>> --master-data=2.  Could the coordinates still be off?
>
>
> Hello,
>
> Are you sure nothing is getting inserted directly into the slave? Is it in
> read only mode?
> If you're starting replication using the values provided by --master-data=2
> (which should be something like):
>
>  -- Position to start replication or point-in-time recovery from
>
> -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000974',
> MASTER_LOG_POS=240814775;
>
> And if you're using the right IP, there's no reason to have duplicate
> entries unless someone is writing directly into the slave.
>
> Manuel.

According to the client, nothing is writing to the slave and
everything is being logged at the master.  I have not had the
opportunity to independently verified any of this, yet.  I do know
that the slave is not in read-only mode, but rather "we promise not to
write to it" mode.

At the moment, I am trying to come up with plausible explanations for
the observations.

Thanks for your feedback.

Regards,
- Robert

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: slave replication with lots of 'duplicate entry' errors

[Robert Citek]

On Thu, Feb 14, 2013 at 5:46 PM, Rick James  wrote:
>> Is it in read only mode?
> Furthermore, are all users logging in as non-SUPER users?  Note:  root 
> bypasses the readonly flag!

No.  The user that is commonly used does have Super privileges.  I am
not sure why, but it does.

Regards,
- Robert

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: slave replication with lots of 'duplicate entry' errors

[Singer Wang]

Are you using all InnoDB?

S


On Thu, Feb 14, 2013 at 5:55 PM, Robert Citek wrote:

> On Thu, Feb 14, 2013 at 4:54 PM, Manuel Arostegui 
> wrote:
> > 2013/2/13 Robert Citek 
> >>
> >> On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek 
> >> wrote:
> >> > Any other possibilities?  Do other scenarios become likely if there
> >> > are two or more tables?
> >> >
> >> > Of those, which are the most likely?
> >>
> >> [from off-list responder]:
> >> > Other possibility: The replication is reading from master not from the
> >> > point when the dump was done, but some time before and is fetching
> insert
> >> > statements which are already in the dump.
> >>
> >> To prevent that I used the coordinates in the dump file included with
> >> --master-data=2.  Could the coordinates still be off?
> >
> >
> > Hello,
> >
> > Are you sure nothing is getting inserted directly into the slave? Is it
> in
> > read only mode?
> > If you're starting replication using the values provided by
> --master-data=2
> > (which should be something like):
> >
> >  -- Position to start replication or point-in-time recovery from
> >
> > -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000974',
> > MASTER_LOG_POS=240814775;
> >
> > And if you're using the right IP, there's no reason to have duplicate
> > entries unless someone is writing directly into the slave.
> >
> > Manuel.
>
> According to the client, nothing is writing to the slave and
> everything is being logged at the master.  I have not had the
> opportunity to independently verified any of this, yet.  I do know
> that the slave is not in read-only mode, but rather "we promise not to
> write to it" mode.
>
> At the moment, I am trying to come up with plausible explanations for
> the observations.
>
> Thanks for your feedback.
>
> Regards,
> - Robert
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe:http://lists.mysql.com/mysql
>
>

RE: slave replication with lots of 'duplicate entry' errors

[Rick James]

> Is it in read only mode?
Furthermore, are all users logging in as non-SUPER users?  Note:  root bypasses 
the readonly flag!

> -Original Message-
> From: Manuel Arostegui [mailto:man...@tuenti.com]
> Sent: Thursday, February 14, 2013 1:55 PM
> To: Robert Citek
> Cc: mysql
> Subject: Re: slave replication with lots of 'duplicate entry' errors
> 
> 2013/2/13 Robert Citek 
> 
> > On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek
> 
> > wrote:
> > > Any other possibilities?  Do other scenarios become likely if there
> > > are two or more tables?
> > >
> > > Of those, which are the most likely?
> >
> > [from off-list responder]:
> > > Other possibility: The replication is reading from master not from
> > > the
> > point when the dump was done, but some time before and is fetching
> > insert statements which are already in the dump.
> >
> > To prevent that I used the coordinates in the dump file included with
> > --master-data=2.  Could the coordinates still be off?
> 
> 
> Hello,
> 
> Are you sure nothing is getting inserted directly into the slave? Is it
> in read only mode?
> If you're starting replication using the values provided by --master-
> data=2 (which should be something like):
> 
>  -- Position to start replication or point-in-time recovery from
> 
> -- CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000974',
> MASTER_LOG_POS=240814775;
> 
> And if you're using the right IP, there's no reason to have duplicate
> entries unless someone is writing directly into the slave.
> 
> Manuel.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

RE: slave replication with lots of 'duplicate entry' errors

[Rick James]

Recommend, for security reasons, you rectify that.
If you need some SUPER action, create a stored procedure with privileges 
'creator', so the security hole is still mostly contained.

> -Original Message-
> From: Robert Citek [mailto:robert.ci...@gmail.com]
> Sent: Thursday, February 14, 2013 2:59 PM
> To: Rick James
> Cc: mysql
> Subject: Re: slave replication with lots of 'duplicate entry' errors
> 
> On Thu, Feb 14, 2013 at 5:46 PM, Rick James 
> wrote:
> >> Is it in read only mode?
> > Furthermore, are all users logging in as non-SUPER users?  Note:
> root bypasses the readonly flag!
> 
> No.  The user that is commonly used does have Super privileges.  I am
> not sure why, but it does.
> 
> Regards,
> - Robert

Re: slave replication with lots of 'duplicate entry' errors

[Robert Citek]

On Wed, Feb 13, 2013 at 8:59 AM, Robert Citek  wrote:
> Any other possibilities?  Do other scenarios become likely if there
> are two or more tables?
>
> Of those, which are the most likely?

[from off-list responder]:
> Other possibility: The replication is reading from master not from the point 
> when the dump was done, but some time before and is fetching insert 
> statements which are already in the dump.

To prevent that I used the coordinates in the dump file included with
--master-data=2.  Could the coordinates still be off?

Regards,
- Robert

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: Percona 5.5 semisynchronous replication.

[Johan De Meersman]

- Original Message -
> From: "Rafał Radecki" 
> 
> Seconds_Behind_Master: 364
> 
> So if I have rpl_semi_sync_master_timeout=5000 (5s) and semisync
> 
> why is it possible on the slave to fall behind the master?

Semi-sync only guarantees that the slave has *received* the statement in it's 
relay log. The rpl_semi_sync_master_timeout value is the timeout for the slave 
to acknowledge this, whereas the Seconds_Behind_Master value gives an 
indication of how far statement *execution* is lagging.

-- 
Unhappiness is discouraged and will be corrected with kitten pictures.

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Percona 5.5 semisynchronous replication.

[Rafał Radecki]

Hi all.

I have installed a setup in which I use semisync replication. On master I have:
mysql> show status like '%rpl%';
++-+
| Variable_name  | Value   |
++-+
| Rpl_semi_sync_master_clients   | 1   |
| Rpl_semi_sync_master_net_avg_wait_time | 1389|
| Rpl_semi_sync_master_net_wait_time | 159296355   |
| Rpl_semi_sync_master_net_waits | 114643  |
| Rpl_semi_sync_master_no_times  | 0   |
| Rpl_semi_sync_master_no_tx | 0   |
| Rpl_semi_sync_master_status| ON  |
| Rpl_semi_sync_master_timefunc_failures | 0   |
| Rpl_semi_sync_master_tx_avg_wait_time  | 33380   |
| Rpl_semi_sync_master_tx_wait_time  | 77877106|
| Rpl_semi_sync_master_tx_waits  | 2333|
| Rpl_semi_sync_master_wait_pos_backtraverse | 43  |
| Rpl_semi_sync_master_wait_sessions | 0   |
| Rpl_semi_sync_master_yes_tx| 114667  |
| Rpl_semi_sync_slave_status | ON  |
| Rpl_status | AUTH_MASTER |
++-+
So semisync replication is enabled and working and there have been no
switches to async replication since mysql start.
But at the same time I have on the slave:

Seconds_Behind_Master: 364

And I wonder how is it possible?
As far as I understand semisync replication it works that way:
- master starts executing the query
- master sends the query to slave
- master starts the commit and waits for rpl_semi_sync_master_timeout
for ack from one of the slaves
So if I have rpl_semi_sync_master_timeout=5000 (5s) and semisync
replication is working and it has not switched to async replication
why is it possible on the slave to fall behind the master?

Best regards,
Rafal.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Semisynchrounous replication - how to clear Rpl_semi_sync_master_no_tx/_times.

[Rafał Radecki]

Hi all.

I would like to clear values off Rpl_semi_sync_master_no_tx and
Rpl_semi_sync_master_no_times status variables because I am plugging a
sync replication setup into nagios.
I've tried:
mysql> SET global Rpl_semi_sync_master_no_tx=0;
ERROR 1193 (HY000): Unknown system variable 'Rpl_semi_sync_master_no_tx'
mysql> SET global Rpl_semi_sync_master_no_times=0;
ERROR 1193 (HY000): Unknown system variable 'Rpl_semi_sync_master_no_times'

Is there a way to clear these counters?

Best regards,
Rafal Radecki.

-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql

Re: MySQL Replication Error

[Johan De Meersman]

- Original Message -
> From: "Néstor" 
> 
> I spoke to soon!!!
> Here is the error about 1.5 hours after replication  has started.
>  121205 16:39:51 [ERROR] Slave: Error 'Duplicate entry '3468897'
>  for

Yes, that's what you get for running SQL_SLAVE_SKIP_COUNTER without knowing 
what the problem is.

Your database is now nicely out of sync, and hopefully it's just the watchdog.

The *proper* way of resolving this is to resync the entire database. You *may* 
get away with deleting watchdog entry 3468897 and restarting replication as 
someone has suggested, but I suspect you'll find another half-million of them.

Look at the Percona Toolkit, specifically mk-table-checksum and mk-table-sync, 
understand how they work and then apply them. If there's too many differences 
by now, your only recourse will be to completely reprovision the slave, though.

-- 
Linux Bier Wanderung 2012, now also available in Belgium!
August, 12 to 19, Diksmuide, Belgium - http://lbw2012.tuxera.be

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql