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

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: 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

Re: MySQL Replication Error

[Johan De Meersman]

- Original Message -
 From: Néstor rot...@gmail.com
 
 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

Re: MySQL Replication Error

[Igor Shevtsov]

  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: 0
1 row in set (0.00 sec)
---

I get the folllowing on my mysql.err:
121205 15:09:56 [Note] Slave SQL thread initialized, starting replication
in log 'mysql-bin.000289' at position 42394063, relay log
'/var/log/mysql/mysql-relay-**bin.01' position: 4
121205 15:09:56 [Note] Slave I/O thread: connected to master '
sdcwa_slave@192.168.1.21:3306'**,  replication started in log
'mysql-bin.000289' at position 42394063


Everything is GOOD, for a little while and then I get an error on
mysql.err:

-

Now if I do the SKIP FLAG many times, I will error after error...
I do not see how replication works so well for others if I am using the
steps in the link
on top of this message.

THANKS!!!


On Wed, Dec 5, 2012 at 3:42 AM, Manuel Arostegui man...@tuenti.com
wrote:



2012/11/30 Néstor rot...@gmail.com

  I am trying to set up mysql replication on 2 systems but Once I get it

going, I get the following an error 1062,

  skip

  I have re-installed the database on the slave also to see if this fixes

the
problem
but after a few minutes I get the same error.  I have repeated the
replication from the
beginning but I get the same problem after a while.

Does anyone know a way to fix this?


  Hello,

How did you build the slave from the master? How did you decide in which
position the slave should start replicating from?
You might want to try pt-table-checksum (
http://www.percona.com/doc/**percona-toolkit/2.1/pt-table-**
checksum.htmlhttp://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html)
to
see what else do you have broken.

Manuel.


--
Manuel Aróstegui
Systems Team
tuenti.com



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

Re: MySQL Replication Error

[Igor Shevtsov]

Hi Néstor,
You might want to put those lines into your my.cnf under replication 
section and restart the slave:

replicate-wild-ignore-table=dbname%.watchdog%

and may be this as well:
replicate-wild-ignore-table=dbname%.cache%

Do you really need to replicate cache and session tables?

Cheers,
Igor


On 06/12/12 21:59, Néstor wrote:

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
key 1' on query. Default database: 'parallax'. Query: 'INSERT INTO watchdog
 (uid, type, message, variables, severity, link, location, referer,
hostname, timestamp)
 VALUES
 (0, 'page not found', 'images/internet_explorer/borderTopLeft.png',
'N;', 4, '','
http://www.sdcwa.org/es/images/internet_explorer/borderTopLeft.png', '',
'10.20.141.24', 1354754352)', Error_code: 1062
121205 16:39:51 [ERROR] Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with SLAVE START. We
stopped at log 'mysql-bin.000289' position 86451409


Sorry for the long message, below are the steps use to create my
replication.


When I created the replication, I followed the steps here :
http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/
  I am on a red hat server.
---
I set my firewall to accept info from server1 on server2
SERVER1:
tcp0  0 wahoo.sdcwa.org:mysql   wahooesc.sdcwa.org:52131
  ESTABLISHED 30145/mysqld
SERVER2:
tcp0  0 wahooesc.sdcwa.org:52131www.sdcwa.org:mysql
ESTABLISHED 30875/mysqld
-

I have try using the SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

I have also done these steps where in SERVER1
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.000289 | 42394063 |  |  |
+--+--+--+--+

Then Dump SERVER1 database then you unlock SERVER1 database
and copy it to SERVER2 then FLUSH TABLES WITH READ LOCK;

I proceed to STOP SLAVE then add the database to SERVER2 then
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000289',
MASTER_LOG_POS=42394063; to synch with the SERVER1
Then I start SERVER2 and the output of  slave status is:

mysql SHOW SLAVE STATUS\G
*** 1. row ***
  Slave_IO_State: Waiting for master to send event
 Master_Host: 10.168.1.21
 Master_User: sdcwa_slave
 Master_Port: 3306
   Connect_Retry: 60
 Master_Log_File: mysql-bin.000289
 Read_Master_Log_Pos: 55848766
  Relay_Log_File: mysql-relay-bin.02
   Relay_Log_Pos: 13454938
   Relay_Master_Log_File: mysql-bin.000289
Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
 Replicate_Do_DB:
 Replicate_Ignore_DB:
  Replicate_Do_Table:
  Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
  Last_Errno: 0
  Last_Error:
Skip_Counter: 0
 Exec_Master_Log_Pos: 55848766
 Relay_Log_Space: 13454938
 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: 0
1 row in set (0.00 sec)
---

I get the folllowing on my mysql.err:
121205 15:09:56 [Note] Slave SQL thread initialized, starting replication
in log 'mysql-bin.000289' at position 42394063, relay log
'/var/log/mysql/mysql-relay-bin.01' position: 4
121205 15:09:56 [Note] Slave I/O thread: connected to master '
sdcwa_slave@192.168.1.21:3306',  replication started in log
'mysql-bin.000289' at position 42394063


Everything is GOOD, for a little while and then I get an error on mysql.err:

-

Now if I do the SKIP FLAG many times, I will error after error...
I do not see how replication works so well for others if I am using the
steps in the link
on top of this message.

THANKS!!!


On Wed, Dec 5, 2012 at 3:42 AM, Manuel Arostegui man...@tuenti.com wrote:



2012/11/30 Néstor rot...@gmail.com


I am trying to set up mysql replication on 2 systems but Once I get it
going, I get the following an error 1062,


skip


I have re-installed the database on the slave also to see if this fixes
the
problem
but after a few minutes I get the same error.  I have repeated the
replication from the
beginning but I get the same problem after a while.

Does anyone know

Re: MySQL Replication Error

[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
key 1' on query. Default database: 'parallax'. Query: 'INSERT INTO watchdog
(uid, type, message, variables, severity, link, location, referer,
hostname, timestamp)
VALUES
(0, 'page not found', 'images/internet_explorer/borderTopLeft.png',
'N;', 4, '', '
http://www.sdcwa.org/es/images/internet_explorer/borderTopLeft.png', '',
'10.20.141.24', 1354754352)', Error_code: 1062
121205 16:39:51 [ERROR] Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with SLAVE START. We
stopped at log 'mysql-bin.000289' position 86451409


Sorry for the long message, below are the steps use to create my
replication.


When I created the replication, I followed the steps here :
http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/
 I am on a red hat server.
---
I set my firewall to accept info from server1 on server2
SERVER1:
tcp0  0 wahoo.sdcwa.org:mysql   wahooesc.sdcwa.org:52131
 ESTABLISHED 30145/mysqld
SERVER2:
tcp0  0 wahooesc.sdcwa.org:52131www.sdcwa.org:mysql
ESTABLISHED 30875/mysqld
-

I have try using the SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

I have also done these steps where in SERVER1
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.000289 | 42394063 |  |  |
+--+--+--+--+

Then Dump SERVER1 database then you unlock SERVER1 database
and copy it to SERVER2 then FLUSH TABLES WITH READ LOCK;

I proceed to STOP SLAVE then add the database to SERVER2 then
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000289',
MASTER_LOG_POS=42394063; to synch with the SERVER1
Then I start SERVER2 and the output of  slave status is:

mysql SHOW SLAVE STATUS\G
*** 1. row ***
 Slave_IO_State: Waiting for master to send event
Master_Host: 10.168.1.21
Master_User: sdcwa_slave
Master_Port: 3306
  Connect_Retry: 60
Master_Log_File: mysql-bin.000289
Read_Master_Log_Pos: 55848766
 Relay_Log_File: mysql-relay-bin.02
  Relay_Log_Pos: 13454938
  Relay_Master_Log_File: mysql-bin.000289
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
 Replicate_Do_Table:
 Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
 Last_Errno: 0
 Last_Error:
   Skip_Counter: 0
Exec_Master_Log_Pos: 55848766
Relay_Log_Space: 13454938
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: 0
1 row in set (0.00 sec)
---

I get the folllowing on my mysql.err:
121205 15:09:56 [Note] Slave SQL thread initialized, starting replication
in log 'mysql-bin.000289' at position 42394063, relay log
'/var/log/mysql/mysql-relay-bin.01' position: 4
121205 15:09:56 [Note] Slave I/O thread: connected to master '
sdcwa_slave@192.168.1.21:3306',  replication started in log
'mysql-bin.000289' at position 42394063


Everything is GOOD, for a little while and then I get an error on mysql.err:

-

Now if I do the SKIP FLAG many times, I will error after error...
I do not see how replication works so well for others if I am using the
steps in the link
on top of this message.

THANKS!!!


On Wed, Dec 5, 2012 at 3:42 AM, Manuel Arostegui man...@tuenti.com wrote:



 2012/11/30 Néstor rot...@gmail.com

 I am trying to set up mysql replication on 2 systems but Once I get it
 going, I get the following an error 1062,


 skip


 I have re-installed the database on the slave also to see if this fixes
 the
 problem
 but after a few minutes I get the same error.  I have repeated the
 replication from the
 beginning but I get the same problem after a while.

 Does anyone know a way to fix this?


 Hello,

 How did you build the slave from the master? How did you decide in which
 position the slave should start replicating from?
 You might want to try pt-table-checksum (
 http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html) to
 see what else do you have broken.

 Manuel.


 --
 Manuel Aróstegui
 Systems Team
 tuenti.com

Re: MySQL Replication Error

[Igor Shevtsov]

In regards to the second part of your email.
You don't have errors in your mysql.err log. Those are notes saying that 
you started IO and SQL replication threads after you skipped an 
replication error and run start slave command.


I wouldn't run  SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; command either as 
it makes your tables inconsistent.
I'd rather deleted offending row on the slave and started replication. 
In this case insert would've succeed and tables would become consistent 
again.






On 06/12/12 21:59, Néstor wrote:

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
key 1' on query. Default database: 'parallax'. Query: 'INSERT INTO watchdog
 (uid, type, message, variables, severity, link, location, referer,
hostname, timestamp)
 VALUES
 (0, 'page not found', 'images/internet_explorer/borderTopLeft.png',
'N;', 4, '','
http://www.sdcwa.org/es/images/internet_explorer/borderTopLeft.png', '',
'10.20.141.24', 1354754352)', Error_code: 1062
121205 16:39:51 [ERROR] Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with SLAVE START. We
stopped at log 'mysql-bin.000289' position 86451409


Sorry for the long message, below are the steps use to create my
replication.


When I created the replication, I followed the steps here :
http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/
  I am on a red hat server.
---
I set my firewall to accept info from server1 on server2
SERVER1:
tcp0  0 wahoo.sdcwa.org:mysql   wahooesc.sdcwa.org:52131
  ESTABLISHED 30145/mysqld
SERVER2:
tcp0  0 wahooesc.sdcwa.org:52131www.sdcwa.org:mysql
ESTABLISHED 30875/mysqld
-

I have try using the SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

I have also done these steps where in SERVER1
FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;
+--+--+--+--+
| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+--+--+--+--+
| mysql-bin.000289 | 42394063 |  |  |
+--+--+--+--+

Then Dump SERVER1 database then you unlock SERVER1 database
and copy it to SERVER2 then FLUSH TABLES WITH READ LOCK;

I proceed to STOP SLAVE then add the database to SERVER2 then
CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000289',
MASTER_LOG_POS=42394063; to synch with the SERVER1
Then I start SERVER2 and the output of  slave status is:

mysql SHOW SLAVE STATUS\G
*** 1. row ***
  Slave_IO_State: Waiting for master to send event
 Master_Host: 10.168.1.21
 Master_User: sdcwa_slave
 Master_Port: 3306
   Connect_Retry: 60
 Master_Log_File: mysql-bin.000289
 Read_Master_Log_Pos: 55848766
  Relay_Log_File: mysql-relay-bin.02
   Relay_Log_Pos: 13454938
   Relay_Master_Log_File: mysql-bin.000289
Slave_IO_Running: Yes
   Slave_SQL_Running: Yes
 Replicate_Do_DB:
 Replicate_Ignore_DB:
  Replicate_Do_Table:
  Replicate_Ignore_Table:
 Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
  Last_Errno: 0
  Last_Error:
Skip_Counter: 0
 Exec_Master_Log_Pos: 55848766
 Relay_Log_Space: 13454938
 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: 0
1 row in set (0.00 sec)
---

I get the folllowing on my mysql.err:
121205 15:09:56 [Note] Slave SQL thread initialized, starting replication
in log 'mysql-bin.000289' at position 42394063, relay log
'/var/log/mysql/mysql-relay-bin.01' position: 4
121205 15:09:56 [Note] Slave I/O thread: connected to master '
sdcwa_slave@192.168.1.21:3306',  replication started in log
'mysql-bin.000289' at position 42394063


Everything is GOOD, for a little while and then I get an error on mysql.err:

-

Now if I do the SKIP FLAG many times, I will error after error...
I do not see how replication works so well for others if I am using the
steps in the link
on top of this message.

THANKS!!!


On Wed, Dec 5, 2012 at 3:42 AM, Manuel Arostegui man...@tuenti.com wrote:



2012/11/30 Néstor rot...@gmail.com


I am trying to set up mysql replication on 2 systems but Once I get it
going, I get the following an error 1062,


skip


I have re-installed the database on the slave

Re: MySQL Replication Error

[Néstor]

 log
 '/var/log/mysql/mysql-relay-**bin.01' position: 4
 121205 15:09:56 [Note] Slave I/O thread: connected to master '
 sdcwa_slave@192.168.1.21:3306'**,  replication started in log
 'mysql-bin.000289' at position 42394063


 Everything is GOOD, for a little while and then I get an error on
 mysql.err:

 -

 Now if I do the SKIP FLAG many times, I will error after error...
 I do not see how replication works so well for others if I am using the
 steps in the link
 on top of this message.

 THANKS!!!


 On Wed, Dec 5, 2012 at 3:42 AM, Manuel Arostegui man...@tuenti.com
 wrote:


 2012/11/30 Néstor rot...@gmail.com

  I am trying to set up mysql replication on 2 systems but Once I get it
 going, I get the following an error 1062,

  skip

  I have re-installed the database on the slave also to see if this fixes
 the
 problem
 but after a few minutes I get the same error.  I have repeated the
 replication from the
 beginning but I get the same problem after a while.

 Does anyone know a way to fix this?


  Hello,

 How did you build the slave from the master? How did you decide in which
 position the slave should start replicating from?
 You might want to try pt-table-checksum (
 http://www.percona.com/doc/**percona-toolkit/2.1/pt-table-**
 checksum.htmlhttp://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html)
 to
 see what else do you have broken.

 Manuel.


 --
 Manuel Aróstegui
 Systems Team
 tuenti.com



 --
 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: divesh kamra kamra.div...@gmail.com
 
 slave-skip-errors=1062 --- in my.cnf and restart mysql

Really? Just like that? Without even knowing what it does or what the problem 
is?

If you have replication errors, this kind of stuff is only going to break it 
further. It exists is not an indication of fitness for any specific use, and 
that kind of option is really only for use in very very specific cases where 
you KNOW what's wrong and KNOW that it's not harmful in your highly specific 
situation.


-- 
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

Re: MySQL Replication Error

[Manuel Arostegui]

2012/11/30 Néstor rot...@gmail.com

 I am trying to set up mysql replication on 2 systems but Once I get it
 going, I get the following an error 1062,


skip


 I have re-installed the database on the slave also to see if this fixes the
 problem
 but after a few minutes I get the same error.  I have repeated the
 replication from the
 beginning but I get the same problem after a while.

 Does anyone know a way to fix this?


Hello,

How did you build the slave from the master? How did you decide in which
position the slave should start replicating from?
You might want to try pt-table-checksum (
http://www.percona.com/doc/percona-toolkit/2.1/pt-table-checksum.html) to
see what else do you have broken.

Manuel.


-- 
Manuel Aróstegui
Systems Team
tuenti.com

Re: MySQL Replication Error

[Néstor]

I was about to reply with a long message of all the steps I followed to
create replication but I did my steps about an hour ago and I have not
seen the replication fail so far.

BTW, I created my replication following the info on this web page:
http://crazytoon.com/2008/01/29/mysql-how-do-you-set-up-masterslave-replication-in-mysql-centos-rhel-fedora/

THANKS!!!

Nestor


On Wed, Dec 5, 2012 at 3:35 AM, Johan De Meersman vegiv...@tuxera.bewrote:

 - Original Message -
  From: divesh kamra kamra.div...@gmail.com
 
  slave-skip-errors=1062 --- in my.cnf and restart mysql

 Really? Just like that? Without even knowing what it does or what the
 problem is?

 If you have replication errors, this kind of stuff is only going to break
 it further. It exists is not an indication of fitness for any specific
 use, and that kind of option is really only for use in very very specific
 cases where you KNOW what's wrong and KNOW that it's not harmful in your
 highly specific situation.


 --
 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

Re: MySQL Replication Error

[divesh kamra]

Hi

first check this thought application end

or

There is another way

slave-skip-errors=1062 --- in my.cnf and restart mysql


On Sat, Dec 1, 2012 at 4:30 AM, Reindl Harald h.rei...@thelounge.netwrote:



 Am 30.11.2012 23:52, schrieb Rick James:
  Possible causes:
  * Someone is writing to the Slave

 that is why the option read-only exists for my.cnf

  * The Slave was not in synch with the Master.
  * Schemas are different between Master and Slave

 should not happen if the slave is properly cloned
 as a binary copy of the stopped master and secured
 with read-only

MySQL Replication Error

[Néstor]

I am trying to set up mysql replication on 2 systems but Once I get it
going, I get the following an error 1062,
which is a duplicate entry and my 'slave status' shows:
  Slave_IO_Running: Yes
  Slave_SQL_Running: No

And this is the error on my mysql.err file:
121130 12:41:53 [Note] Slave I/O thread killed while reading event
121130 12:41:53 [Note] Slave I/O thread exiting, read up to log
'mysql-bin.000266', position 76841310
121130 12:41:57 [Note] Slave SQL thread initialized, starting replication
in log 'mysql-bin.000265' at position 99071761, relay log
'/var/log/mysql/mysql-relay-bin.06' position: 98589371
121130 12:41:57 [Note] Slave I/O thread: connected to master '
sdcwa_slave@10.168.1.21:3306',  replication started in log
'mysql-bin.000266' at position 76841310
121130 12:41:57 [ERROR] Slave: Error 'Duplicate entry
'links:primary-links:tree-data:7b7216a15969aa93e5dfb9aaa24bfc32' for key 1'
on query. Default database: 'parallax'. Query: 'INSERT INTO cache_menu
(cid, data, created, expire, headers, serialized) VALUES
('links:primary-links:tree-data:7b7216a15969aa93e5dfb9aaa24bfc32',
'a:2:{s:4:\tree\;a:19:{i:310;a:2:{s:4:\link\;a:38:{s:14:\load_functions\;s:26:\a:1:{i:1;s:9:\node_load\;}\;s:16:\to_arg_functions\;s:0:\\;s:15:\access_callback\;s:11:\node_access\;s:16:\access_arguments\;s:29:\a:2:{i:0;s:4:\view\;i:1;i:1;}\;s:13:\page_callback\;s:14:\node_page_view\;s:14:\page_arguments\;s:14:\a:1:{i:0;i:1;}\;s:5:\title\;s:0:\\;s:14:\title_callback\;s:15:\node_page_title\;s:15:\title_arguments\;s:14:\a:1:{i:0;i:1;}\;s:4:\type\;s:1:\4\;s:11:\description\;s:0:\\;s:9:\menu_name\;s:13:\primary-links\;s:4:\mlid\;s:3:\310\;s:4:\plid\;s:1:\0\;s:9:\link_path\;s:7:\node/14\;s:11:\router_path\;s:6:\node/%\;s:10:\link_title\;s:11:\Wh
121130 12:41:57 [ERROR] Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with SLAVE START. We
stopped at log 'mysql-bin.000265' position 99451408

I done searches on google and I look at several sites and basically they
say to do the following:
mysql stop slave;
mysql SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql start slave;
mysql show slave status\G

But after doing this a couple of minutes later I get the same situation

I have re-installed the database on the slave also to see if this fixes the
problem
but after a few minutes I get the same error.  I have repeated the
replication from the
beginning but I get the same problem after a while.

Does anyone know a way to fix this?

Thanks,

Néstor

RE: MySQL Replication Error

[Rick James]

Possible causes:
* Someone is writing to the Slave.
* The Slave was not in synch with the Master.
* Schemas are different between Master and Slave.
* Someone is changing the POS for replication.

After you have eliminated those possibilities, provide
SHOW SLAVE STATUS \G
SHOW MASTER STATUS;
SHOW CREATE TABLE (for a table that is in trouble)

 -Original Message-
 From: Néstor [mailto:rot...@gmail.com]
 Sent: Friday, November 30, 2012 1:24 PM
 To: mysql@lists.mysql.com
 Subject: MySQL Replication Error
 
 I am trying to set up mysql replication on 2 systems but Once I get it
 going, I get the following an error 1062, which is a duplicate entry
 and my 'slave status' shows:
   Slave_IO_Running: Yes
   Slave_SQL_Running: No
 
 And this is the error on my mysql.err file:
 121130 12:41:53 [Note] Slave I/O thread killed while reading event
 121130 12:41:53 [Note] Slave I/O thread exiting, read up to log 'mysql-
 bin.000266', position 76841310
 121130 12:41:57 [Note] Slave SQL thread initialized, starting
 replication in log 'mysql-bin.000265' at position 99071761, relay log
 '/var/log/mysql/mysql-relay-bin.06' position: 98589371
 121130 12:41:57 [Note] Slave I/O thread: connected to master '
 sdcwa_slave@10.168.1.21:3306',  replication started in log 'mysql-
 bin.000266' at position 76841310
 121130 12:41:57 [ERROR] Slave: Error 'Duplicate entry 'links:primary-
 links:tree-data:7b7216a15969aa93e5dfb9aaa24bfc32' for key 1'
 on query. Default database: 'parallax'. Query: 'INSERT INTO cache_menu
 (cid, data, created, expire, headers, serialized) VALUES
 ('links:primary-links:tree-data:7b7216a15969aa93e5dfb9aaa24bfc32',
 'a:2:{s:4:\tree\;a:19:{i:310;a:2:{s:4:\link\;a:38:{s:14:\load_func
 tions\;s:26:\a:1:{i:1;s:9:\node_load\;}\;s:16:\to_arg_functions\
 ;s:0:\\;s:15:\access_callback\;s:11:\node_access\;s:16:\access_a
 rguments\;s:29:\a:2:{i:0;s:4:\view\;i:1;i:1;}\;s:13:\page_callbac
 k\;s:14:\node_page_view\;s:14:\page_arguments\;s:14:\a:1:{i:0;i:1
 ;}\;s:5:\title\;s:0:\\;s:14:\title_callback\;s:15:\node_page_ti
 tle\;s:15:\title_arguments\;s:14:\a:1:{i:0;i:1;}\;s:4:\type\;s:1
 :\4\;s:11:\description\;s:0:\\;s:9:\menu_name\;s:13:\primary-
 links\;s:4:\mlid\;s:3:\310\;s:4:\plid\;s:1:\0\;s:9:\link_path
 \;s:7:\node/14\;s:11:\router_path\;s:6:\node/%\;s:10:\link_titl
 e\;s:11:\Wh
 121130 12:41:57 [ERROR] Error running query, slave SQL thread aborted.
 Fix the problem, and restart the slave SQL thread with SLAVE START.
 We stopped at log 'mysql-bin.000265' position 99451408
 
 I done searches on google and I look at several sites and basically
 they say to do the following:
 mysql stop slave;
 mysql SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1; start slave; show slave
 mysql status\G
 
 But after doing this a couple of minutes later I get the same situation
 
 I have re-installed the database on the slave also to see if this fixes
 the problem but after a few minutes I get the same error.  I have
 repeated the replication from the beginning but I get the same problem
 after a while.
 
 Does anyone know a way to fix this?
 
 Thanks,
 
 Néstor

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

Re: MySQL Replication Error

[Reindl Harald]

Am 30.11.2012 23:52, schrieb Rick James:
 Possible causes:
 * Someone is writing to the Slave

that is why the option read-only exists for my.cnf

 * The Slave was not in synch with the Master.
 * Schemas are different between Master and Slave

should not happen if the slave is properly cloned
as a binary copy of the stopped master and secured
with read-only








signature.asc
Description: OpenPGP digital signature

mysql replication

[aaron zhang]

Hi all

i use mysql database,when i use mysql replication ,the slave host do not
replication,i check the error message,i found error message,it is 'row is
too large', i do not understand why,please tell and help me ,thanks

Re: mysql replication

[P.R.Karthik]

Hi ,

Can you paste the complete error log.

Regards,
KarthiK.P.R



On Wed, Aug 29, 2012 at 7:04 AM, aaron zhang
aaron.zh...@embracesource.comwrote:

 Hi all

 i use mysql database,when i use mysql replication ,the slave host do not
 replication,i check the error message,i found error message,it is 'row is
 too large', i do not understand why,please tell and help me ,thanks

Re: Can the mysql replication limited to one database

[a . smith]

Quoting Brown, Charles cbr...@bmi.com:



Can the mysql replication limited to selected schema or database?


Hi, yes it can. On the master side you control what is written to the  
binlog with my.cnf entries for each DB like:


binlog-do-db=DB1
binlog-do-db=DB2

And on the slave side you use entries like:

replicate-do-db=DB1
replicate-do-db=DB2

thanks Andy.




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

RE: Can the mysql replication limited to one database

[Rick James]

Yes, doable.  In my.cnf on master:
  Binlog-do-database = dbname1
  Binlog-do-database = dbname2
Would replicate those two dbs only.
There are many other combinations using 
binlog/replicate-do/ignore-db/table/wild.  See the manual on replication, and 
especially the flowchart on how those interact (sometimes in unexpected ways).

 -Original Message-
 From: Brown, Charles [mailto:cbr...@bmi.com]
 Sent: Wednesday, May 02, 2012 8:44 AM
 To: Rick James
 Cc: mysql@lists.mysql.com
 Subject: Can the mysql replication limited to one database
 
 Hi Gurus,
 
 Can the mysql replication limited to selected schema or database? I've
 been asked to set up mysql for only 1 out 5 databases exist in
 production. Please advise if this is doable
 
 Thanks
 
 
 This message is intended only for the use of the Addressee and may
 contain information that is PRIVILEGED and CONFIDENTIAL.
 
 If you are not the intended recipient, you are hereby notified that any
 dissemination of this communication is strictly prohibited.
 
 If you have received this communication in error, please erase all
 copies of the message and its attachments and notify us immediately.
 
 Thank you.
 

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

Re: Can the mysql replication limited to one database

[Claudio Nanni]

I recommend to use the 'wild' modifier, if you have a default db and you
specify the schema in the query like  update
schemanotreplicated.mytable.  you will miss that.

Claudio

2012/5/2 Rick James rja...@yahoo-inc.com

 Yes, doable.  In my.cnf on master:
  Binlog-do-database = dbname1
  Binlog-do-database = dbname2
 Would replicate those two dbs only.
 There are many other combinations using
 binlog/replicate-do/ignore-db/table/wild.  See the manual on replication,
 and especially the flowchart on how those interact (sometimes in unexpected
 ways).

  -Original Message-
  From: Brown, Charles [mailto:cbr...@bmi.com]
  Sent: Wednesday, May 02, 2012 8:44 AM
  To: Rick James
  Cc: mysql@lists.mysql.com
  Subject: Can the mysql replication limited to one database
 
  Hi Gurus,
 
  Can the mysql replication limited to selected schema or database? I've
  been asked to set up mysql for only 1 out 5 databases exist in
  production. Please advise if this is doable
 
  Thanks
 
  
  This message is intended only for the use of the Addressee and may
  contain information that is PRIVILEGED and CONFIDENTIAL.
 
  If you are not the intended recipient, you are hereby notified that any
  dissemination of this communication is strictly prohibited.
 
  If you have received this communication in error, please erase all
  copies of the message and its attachments and notify us immediately.
 
  Thank you.
  

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




-- 
Claudio

Re: Can the mysql replication limited to one database

[Singer X.J. Wang]

RBR solves the default db case..

S


On Wed, May 2, 2012 at 1:25 PM, Claudio Nanni claudio.na...@gmail.comwrote:

 I recommend to use the 'wild' modifier, if you have a default db and you
 specify the schema in the query like  update
 schemanotreplicated.mytable.  you will miss that.

 Claudio

 2012/5/2 Rick James rja...@yahoo-inc.com

  Yes, doable.  In my.cnf on master:
   Binlog-do-database = dbname1
   Binlog-do-database = dbname2
  Would replicate those two dbs only.
  There are many other combinations using
  binlog/replicate-do/ignore-db/table/wild.  See the manual on replication,
  and especially the flowchart on how those interact (sometimes in
 unexpected
  ways).
 
   -Original Message-
   From: Brown, Charles [mailto:cbr...@bmi.com]
   Sent: Wednesday, May 02, 2012 8:44 AM
   To: Rick James
   Cc: mysql@lists.mysql.com
   Subject: Can the mysql replication limited to one database
  
   Hi Gurus,
  
   Can the mysql replication limited to selected schema or database? I've
   been asked to set up mysql for only 1 out 5 databases exist in
   production. Please advise if this is doable
  
   Thanks
  
   
   This message is intended only for the use of the Addressee and may
   contain information that is PRIVILEGED and CONFIDENTIAL.
  
   If you are not the intended recipient, you are hereby notified that any
   dissemination of this communication is strictly prohibited.
  
   If you have received this communication in error, please erase all
   copies of the message and its attachments and notify us immediately.
  
   Thank you.
   
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql
 
 


 --
 Claudio


-- 


--

Get an insider’s guide to ODA performance. Join Gwen Shapira, Oracle Ace 
Director and Senior Pythian Consultant, on May 10th as she presents her 
findings in an informative webinar. Register today, bit.ly/odaperfwebinar

 

 

RE: Can the mysql replication limited to one database

[Brown, Charles]

Hello Rick,
What goes into your my.cnf slave  ?
Given dbname1 and dbname2 




-Original Message-
From: Rick James [mailto:rja...@yahoo-inc.com] 
Sent: Wednesday, May 02, 2012 12:05 PM
To: Brown, Charles
Cc: mysql@lists.mysql.com
Subject: RE: Can the mysql replication limited to one database 

Yes, doable.  In my.cnf on master:
  Binlog-do-database = dbname1
  Binlog-do-database = dbname2
Would replicate those two dbs only.
There are many other combinations using 
binlog/replicate-do/ignore-db/table/wild.  See the manual on replication, and 
especially the flowchart on how those interact (sometimes in unexpected ways).

 -Original Message-
 From: Brown, Charles [mailto:cbr...@bmi.com]
 Sent: Wednesday, May 02, 2012 8:44 AM
 To: Rick James
 Cc: mysql@lists.mysql.com
 Subject: Can the mysql replication limited to one database
 
 Hi Gurus,
 
 Can the mysql replication limited to selected schema or database? I've 
 been asked to set up mysql for only 1 out 5 databases exist in 
 production. Please advise if this is doable
 
 Thanks
 
 
 This message is intended only for the use of the Addressee and may 
 contain information that is PRIVILEGED and CONFIDENTIAL.
 
 If you are not the intended recipient, you are hereby notified that 
 any dissemination of this communication is strictly prohibited.
 
 If you have received this communication in error, please erase all 
 copies of the message and its attachments and notify us immediately.
 
 Thank you.
 

This message is intended only for the use of the Addressee and
may contain information that is PRIVILEGED and CONFIDENTIAL.

If you are not the intended recipient, you are hereby notified
that any dissemination of this communication is strictly prohibited.

If you have received this communication in error, please erase
all copies of the message and its attachments and notify us
immediately.

Thank you.


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

RE: Can the mysql replication limited to one database

[Rick James]

Nothing in the slave's my.cnf.

On the Master, binlog-do/ignore-* filters before leaving the Master.
On the Slave, replicate-do/ignore-* (if used) would filter after getting to the 
Slave.

That is, you _could_ do the replicate-* instead of the binlog-*, but that would 
be inefficient.  Doing both is redundant.

 -Original Message-
 From: Brown, Charles [mailto:cbr...@bmi.com]
 Sent: Wednesday, May 02, 2012 11:15 AM
 To: Rick James
 Cc: mysql@lists.mysql.com
 Subject: RE: Can the mysql replication limited to one database
 
 Hello Rick,
 What goes into your my.cnf slave  ?
 Given dbname1 and dbname2
 
 
 
 
 -Original Message-
 From: Rick James [mailto:rja...@yahoo-inc.com]
 Sent: Wednesday, May 02, 2012 12:05 PM
 To: Brown, Charles
 Cc: mysql@lists.mysql.com
 Subject: RE: Can the mysql replication limited to one database
 
 Yes, doable.  In my.cnf on master:
   Binlog-do-database = dbname1
   Binlog-do-database = dbname2
 Would replicate those two dbs only.
 There are many other combinations using binlog/replicate-do/ignore-
 db/table/wild.  See the manual on replication, and especially the
 flowchart on how those interact (sometimes in unexpected ways).
 
  -Original Message-
  From: Brown, Charles [mailto:cbr...@bmi.com]
  Sent: Wednesday, May 02, 2012 8:44 AM
  To: Rick James
  Cc: mysql@lists.mysql.com
  Subject: Can the mysql replication limited to one database
 
  Hi Gurus,
 
  Can the mysql replication limited to selected schema or database?
 I've
  been asked to set up mysql for only 1 out 5 databases exist in
  production. Please advise if this is doable
 
  Thanks
 
  
  This message is intended only for the use of the Addressee and may
  contain information that is PRIVILEGED and CONFIDENTIAL.
 
  If you are not the intended recipient, you are hereby notified that
  any dissemination of this communication is strictly prohibited.
 
  If you have received this communication in error, please erase all
  copies of the message and its attachments and notify us immediately.
 
  Thank you.
  
 
 This message is intended only for the use of the Addressee and may
 contain information that is PRIVILEGED and CONFIDENTIAL.
 
 If you are not the intended recipient, you are hereby notified that any
 dissemination of this communication is strictly prohibited.
 
 If you have received this communication in error, please erase all
 copies of the message and its attachments and notify us immediately.
 
 Thank you.
 

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

RE: Can the mysql replication limited to one database

[Rick James]

Doing both is redundant.  Doing binlog (on the Master) is more efficient 
because it eliminates sending the other dbs.

 -Original Message-
 From: a.sm...@ukgrid.net [mailto:a.sm...@ukgrid.net]
 Sent: Wednesday, May 02, 2012 8:50 AM
 To: mysql@lists.mysql.com
 Subject: Re: Can the mysql replication limited to one database
 
 Quoting Brown, Charles cbr...@bmi.com:
 
 
  Can the mysql replication limited to selected schema or database?
 
 Hi, yes it can. On the master side you control what is written to the
 binlog with my.cnf entries for each DB like:
 
 binlog-do-db=DB1
 binlog-do-db=DB2
 
 And on the slave side you use entries like:
 
 replicate-do-db=DB1
 replicate-do-db=DB2
 
 thanks Andy.
 
 
 
 
 --
 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: Can the mysql replication limited to one database

[Rick James]

Here's the use case that screws up a lot of people:

USE db_that_is_not_replicated;
INSERT INTO db_that_IS_replicate.tbl ...;

Surprise!  That INSERT is not replicated.  Workaround:  *wild will notice it.

The other way around, the INSERT will be replicated, then hang replication 
because the table won't be on the Slave.


From: Claudio Nanni [mailto:claudio.na...@gmail.com]
Sent: Wednesday, May 02, 2012 10:25 AM
To: Rick James
Cc: Brown, Charles; mysql@lists.mysql.com
Subject: Re: Can the mysql replication limited to one database

I recommend to use the 'wild' modifier, if you have a default db and you 
specify the schema in the query like  update schemanotreplicated.mytable.  
you will miss that.

Claudio
2012/5/2 Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com
Yes, doable.  In my.cnf on master:
 Binlog-do-database = dbname1
 Binlog-do-database = dbname2
Would replicate those two dbs only.
There are many other combinations using 
binlog/replicate-do/ignore-db/table/wild.  See the manual on replication, and 
especially the flowchart on how those interact (sometimes in unexpected ways).

 -Original Message-
 From: Brown, Charles [mailto:cbr...@bmi.commailto:cbr...@bmi.com]
 Sent: Wednesday, May 02, 2012 8:44 AM
 To: Rick James
 Cc: mysql@lists.mysql.commailto:mysql@lists.mysql.com
 Subject: Can the mysql replication limited to one database

 Hi Gurus,

 Can the mysql replication limited to selected schema or database? I've
 been asked to set up mysql for only 1 out 5 databases exist in
 production. Please advise if this is doable

 Thanks

 
 This message is intended only for the use of the Addressee and may
 contain information that is PRIVILEGED and CONFIDENTIAL.

 If you are not the intended recipient, you are hereby notified that any
 dissemination of this communication is strictly prohibited.

 If you have received this communication in error, please erase all
 copies of the message and its attachments and notify us immediately.

 Thank you.
 

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



--
Claudio

Re: MySQL Replication - promote slave

[Radoulov, Dimitre]

On 22/06/2011 15:21, Matthias Urlichs wrote:

Hi,


1. flush logs on the master (only if it's accessible, of course).


Not really necessary if you block clients (firewall rule for new
connections to port 3306?).


Hi Matthias,
thank you for replying!


Isn't the flush logs command necessary in order to flush
any remaining buffered content and make it available to
the dump reading replication thread, even when there are
no client connections?



Anyway, why don't you use a dual-master setup?


Yes, this could be an option.

We have two application environments:
production and spare, the app data is synchronized with rsync and
we use replication for the databases. If the application is started on
the wrong node by mistake, with multi-master replication active,
the production database  could be logically corrupted.



I find that this is a whole lot easier to administer than a master/
fallback-slave situation. In particular, restoring the master after it
comes back happens automatically, or (if you need to re-install the master
from scratch) the command
slave# mysqldump --single-transaction --master-data=1 --all-databases \
| ssh master mysql
ensures that you can continue to use the slave while restoring the master.

Assuming you use only transaction-safe tables, of course.
(You should.)


Unfortunately we have both MyISAM and InnoDB tables,
I don't have control over this choice.


Best regards
Dimitre

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

Re: MySQL Replication - promote slave

[Matthias Urlichs]

Hi,

 1. flush logs on the master (only if it's accessible, of course).
 
Not really necessary if you block clients (firewall rule for new
connections to port 3306?).

Anyway, why don't you use a dual-master setup?

I find that this is a whole lot easier to administer than a master/
fallback-slave situation. In particular, restoring the master after it
comes back happens automatically, or (if you need to re-install the master
from scratch) the command
slave# mysqldump --single-transaction --master-data=1 --all-databases \
| ssh master mysql
ensures that you can continue to use the slave while restoring the master.

Assuming you use only transaction-safe tables, of course.
(You should.)

-- 
-- Matthias Urlichs

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

MySQL Replication - promote slave

[Radoulov, Dimitre]

Hi all,
I need to document the switch between master and slave
and I want to double check if the outlined procedure is correct.

We have a simple master slave replication setup on OEL 5.5 and MySQL 
5.5.13.



Original config: machineA master, machineB slave.
Target: machineB master, machineA not operational.

On the master:

1. flush logs on the master (only if it's accessible, of course).

On the slave:

2. stop slave io_thread
3. Wait until show processlist reports Has read all relay log.
4. stop slave
5. reset master
6. change master to master_host='' and reset slave (only to be sure that 
the slave couldn't be started easily by mistake)

7. Start the application and checK if everything's OK

Now I should have only machineB operational,
I don't care for the state of machineA at this point.

To restore the initial state: machineA master, machineB slave:

1. Stop the application (assuming that's not a problem).
2. Generate a consistent dump on machineB:

mysqldump -u username -ppassword -A -x  dump_file

3. Import on machineA

mysql -u username -ppassword  dump_file

4. On machineA after the import:

reset master;
change master to master_host='';   # just in case, I prefer to have all 
info reset for safety

reset slave;  # see above
flush tables with read lock;   # just in case, because there 
are no connections but mine at this time
show master status;  # just in case, it should 
be 4, right after the reset master command


5. On machineB (the slave):

change master to
master_host='machineA',
master_user='my_rep_usr',
master_password='password',
master_log_file='mysql-bin.01',
master_log_pos=pos;

master_log_file and master_log_pos should be unnecessary because after 
the reset master they should correspond to the default values

of change master command, but again, it's just for safety.

Am I missing something?


Best regards
Dimitre

MySQL Replication Broken

[Alejandro Bednarik]

Hi all,
   We have a pool of 8 MySQL servers where the first one is the
master and the others 7 are slave. Yesterday we have a hard time where the
server begin with too many lag ( 1 hour ) and then slaves start to fail with
messages like this one

thd=0x38f18130
Attempting backtrace. You can use the following information to find out
where mysqld died. If you see no messages after this, something went
terribly wrong...
Cannot determine thread, fp=0x418ac0a8, backtrace may not be correct.
Stack range sanity check OK, backtrace follows:
(nil)
New value of fp=0x38f18130 failed sanity check, terminating stack trace!
Please read http://dev.mysql.com/doc/mysql/en/using-stack-trace.html and
follow instructions on how to resolve the stack tr
ace. Resolved
stack trace is much more helpful in diagnosing the problem, so please do
resolve it
Trying to get some variables.
Some pointers may be invalid and cause the dump to abort...
thd-query at 0x3a4d2c13 = UPDATE t_items i, site_items_195_16 pc SET
i.item_type = CASE pc.item_type WHEN '0' THEN 2 WHEN '
1' THEN 2 WHEN '2' THEN 1 END, i.optional_field_6 = CASE pc.item_type WHEN
'0' THEN '' WHEN '1' THEN 1 WHEN '2' THEN '' END
, i.email = pc.email, i.optional_field_19 = pc.lang_id, i.optional_field_16
= pc.ip, i.optional_field_9 = (SELECT COUNT(*)
FROM geodesic_auctions_bids ab WHERE ab.auction_id = i.item_id), i.title =
pc.title, i.date = pc.date, i.description = pc.d
escription, i.image = pc.image, i.price = pc.price, i.optional_field_8 =
pc.deleted_type, i.optional_field_13 = pc.bloqued_
type WHERE i.item_id = pc.id AND i.item_id = 113347296
thd-thread_id=7
The manual page at http://www.mysql.com/doc/en/Crashing.html contains
information that should help you find out what is causing the crash.

t is a temporal table. More strange was that Slave's failed with more or
less 2 hours of difference. MySQL version is 5.0.x

Any help is welcome,
Thanks.
-- 


Infrastructure Team

OLX Inc.

Buenos Aires - Argentina
Phone   : 54.11.4775.6696
Mobile : 54.911.50436059

Email: alejand...@olx.com

Re: Error in mysql replication with LOAD DATA INFILE

[Anand Kumar]

The application is designed to work such a way that it will process the csv
files daily as part of the aggregate process to calculate some metrics.

it runs fine on the master, when it come to slave through replicaiton it
fails with the error.

i even tried upgrading the slave to latest version mysql 5.1.53 after i see
some post on the internet saying we have some issues in the older version ,
but it keeps giving the same error.


thanks
Anand


On Mon, Dec 20, 2010 at 7:42 PM, who.cat win@gmail.com wrote:

 i wanna know you have done  LOAD DATA INFILE in master ,why are you tring
 to do it in the slave ?The master didn't replication the data to the master
 ?

 All you best
 
 What we are struggling for ?
 The life or the life ?




 On Mon, Dec 20, 2010 at 3:32 PM, Anand Kumar sanan...@gmail.com wrote:

  On Mon, Dec 20, 2010 at 9:00 AM, Anand anand@gmail.com wrote:

  Hi guys,
 
  i am facing a serious issue with my replication , i tried so many things
  but no luck.
 
  my replication is running with mysql 5.0.51a in master and 5.0.90 in
 slave.
 
  we run LOAD DATA INFILE in master to process some csv files and load it
  into a table, it runs perfectly well in master but when it comes to
 slave it
  stops with SQL SYNTAX error
 
  i tried running the LOAD DATA INFILE manually on the slave , but it says
  different error as below
 
  mysql load data  infile '/tmp/SQL_LOAD-4-3-161.data' into table t;
  ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the
 database
  directory or be readable by all
 
  when  i chcked the file persmission it is
 
  -rw-rw 1 mysql mysql  0 Dec 18 23:53  /tmp/SQL_LOAD-4-3-161
 
 
 
  snippet from my error log
  
 
  101219  0:06:32 [Note] Slave SQL thread initialized, starting
 replication
  in log '.000127' at position 923914670, relay log
  '/var/lib/mysql/slave-relay.02' position: 39311
  101219  0:06:32 [Note] Slave I/O thread: connected to master
  'repli_u...@221.131.104.66:3306',replication started in log '.000127'
 at
  position 946657303
  101219  0:06:33 [ERROR] Slave SQL: Error 'You have an error in your SQL
  syntax; check the manual that corresponds to your MySQL server version
 for
  the right syntax to use near ''' at line 1' on query. Default database:
  'caratlane_diamonds'. Query: 'LOAD DATA INFILE
 '/tmp/SQL_LOAD-4-3-161.data'
  IGNORE INTO  TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED
 BY
  '' ESCAPED BY '', Error_code: 1064
  101219  0:06:33 [Warning] Slave: You have an error in your SQL syntax;
  check the manual that corresponds to your MySQL server version for the
 right
  syntax to use near ''' at line 1 Error_code: 1064
  101219  0:06:33 [ERROR] Error running query, slave SQL thread aborted.
 Fix
  the problem, and restart the slave SQL thread with SLAVE START. We
 stopped
  at log '.000127' position 926912155
 
 
 
  please help me fixing this ..
 
  thanks in advance..
 
  thanks
  Anand
 

Error in mysql replication with LOAD DATA INFILE

[Anand]

Hi guys,

i am facing a serious issue with my replication , i tried so many things but
no luck.

my replication is running with mysql 5.0.51a in master and 5.0.90 in slave.

we run LOAD DATA INFILE in master to process some csv files and load it into
a table, it runs perfectly well in master but when it comes to slave it
stops with SQL SYNTAX error

i tried running the LOAD DATA INFILE manually on the slave , but it says
different error as below

mysql load data  infile '/tmp/SQL_LOAD-4-3-161.data' into table t;
ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database
directory or be readable by all

when  i chcked the file persmission it is

-rw-rw 1 mysql mysql  0 Dec 18 23:53  /tmp/SQL_LOAD-4-3-161



snippet from my error log


101219  0:06:32 [Note] Slave SQL thread initialized, starting replication in
log '.000127' at position 923914670, relay log
'/var/lib/mysql/slave-relay.02' position: 39311
101219  0:06:32 [Note] Slave I/O thread: connected to master
'repli_u...@221.131.104.66:3306',replication started in log '.000127' at
position 946657303
101219  0:06:33 [ERROR] Slave SQL: Error 'You have an error in your SQL
syntax; check the manual that corresponds to your MySQL server version for
the right syntax to use near ''' at line 1' on query. Default database:
'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data'
IGNORE INTO  TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY
'' ESCAPED BY '', Error_code: 1064
101219  0:06:33 [Warning] Slave: You have an error in your SQL syntax; check
the manual that corresponds to your MySQL server version for the right
syntax to use near ''' at line 1 Error_code: 1064
101219  0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix
the problem, and restart the slave SQL thread with SLAVE START. We stopped
at log '.000127' position 926912155



please help me fixing this ..

thanks in advance..

thanks
Anand

Re: Error in mysql replication with LOAD DATA INFILE

[Lee Gary]

Hi Anand,

Just try 'load data local infile',it maybe work.

Eric

2010/12/20 Anand anand@gmail.com:
 Hi guys,

 i am facing a serious issue with my replication , i tried so many things but
 no luck.

 my replication is running with mysql 5.0.51a in master and 5.0.90 in slave.

 we run LOAD DATA INFILE in master to process some csv files and load it into
 a table, it runs perfectly well in master but when it comes to slave it
 stops with SQL SYNTAX error

 i tried running the LOAD DATA INFILE manually on the slave , but it says
 different error as below

 mysql load data  infile '/tmp/SQL_LOAD-4-3-161.data' into table t;
 ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database
 directory or be readable by all

 when  i chcked the file persmission it is

 -rw-rw 1 mysql mysql          0 Dec 18 23:53  /tmp/SQL_LOAD-4-3-161



 snippet from my error log
 

 101219  0:06:32 [Note] Slave SQL thread initialized, starting replication in
 log '.000127' at position 923914670, relay log
 '/var/lib/mysql/slave-relay.02' position: 39311
 101219  0:06:32 [Note] Slave I/O thread: connected to master
 'repli_u...@221.131.104.66:3306',replication started in log '.000127' at
 position 946657303
 101219  0:06:33 [ERROR] Slave SQL: Error 'You have an error in your SQL
 syntax; check the manual that corresponds to your MySQL server version for
 the right syntax to use near ''' at line 1' on query. Default database:
 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data'
 IGNORE INTO  TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY
 '' ESCAPED BY '', Error_code: 1064
 101219  0:06:33 [Warning] Slave: You have an error in your SQL syntax; check
 the manual that corresponds to your MySQL server version for the right
 syntax to use near ''' at line 1 Error_code: 1064
 101219  0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix
 the problem, and restart the slave SQL thread with SLAVE START. We stopped
 at log '.000127' position 926912155



 please help me fixing this ..

 thanks in advance..

 thanks
 Anand


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

Re: Error in mysql replication with LOAD DATA INFILE

[Anand Kumar]

On Mon, Dec 20, 2010 at 9:00 AM, Anand anand@gmail.com wrote:

 Hi guys,

 i am facing a serious issue with my replication , i tried so many things
 but no luck.

 my replication is running with mysql 5.0.51a in master and 5.0.90 in slave.

 we run LOAD DATA INFILE in master to process some csv files and load it
 into a table, it runs perfectly well in master but when it comes to slave it
 stops with SQL SYNTAX error

 i tried running the LOAD DATA INFILE manually on the slave , but it says
 different error as below

 mysql load data  infile '/tmp/SQL_LOAD-4-3-161.data' into table t;
 ERROR 1085: The file '/tmp/SQL_LOAD-4-3-161.data' must be in the database
 directory or be readable by all

 when  i chcked the file persmission it is

 -rw-rw 1 mysql mysql  0 Dec 18 23:53  /tmp/SQL_LOAD-4-3-161



 snippet from my error log
 

 101219  0:06:32 [Note] Slave SQL thread initialized, starting replication
 in log '.000127' at position 923914670, relay log
 '/var/lib/mysql/slave-relay.02' position: 39311
 101219  0:06:32 [Note] Slave I/O thread: connected to master
 'repli_u...@221.131.104.66:3306',replication started in log '.000127' at
 position 946657303
 101219  0:06:33 [ERROR] Slave SQL: Error 'You have an error in your SQL
 syntax; check the manual that corresponds to your MySQL server version for
 the right syntax to use near ''' at line 1' on query. Default database:
 'caratlane_diamonds'. Query: 'LOAD DATA INFILE '/tmp/SQL_LOAD-4-3-161.data'
 IGNORE INTO  TABLE osc_diamond_backup FIELDS TERMINATED BY ',' ENCLOSED BY
 '' ESCAPED BY '', Error_code: 1064
 101219  0:06:33 [Warning] Slave: You have an error in your SQL syntax;
 check the manual that corresponds to your MySQL server version for the right
 syntax to use near ''' at line 1 Error_code: 1064
 101219  0:06:33 [ERROR] Error running query, slave SQL thread aborted. Fix
 the problem, and restart the slave SQL thread with SLAVE START. We stopped
 at log '.000127' position 926912155



 please help me fixing this ..

 thanks in advance..

 thanks
 Anand

restrict mysql replication ?

[Per Jessen]

I have a need to have a number of small tables (perhaps up to 1 rows
each) replicated to a number of mysql slaves.  Frequency of change is
very low, and they need not be replicated within seconds, an hour is
fine.  The master server has a lot more and bigger tables, but each
slave will only have a small subset of those.  I've held off setting up
proper replication, thinking it was too much effort, but I've now just
yesterday set up one such replication. 

I've got the slave only replicating two tiny, mostly static tables, so I
had kind of expected not to see a lot of network traffic.  Instead I
see lots and lots of replication traffic?  I'm guessing the master
notifies the slave(s) of all changes, not just changes to the
replicated tables? 

Is there a way of limiting that?  Alternatively, is there a way of
doing replication-on-demand, perhaps triggered by cron?  


/Per Jessen, Zürich


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

Re: restrict mysql replication ?

[Per Jessen]

Per Jessen wrote:

 Is there a way of limiting that?  Alternatively, is there a way of
 doing replication-on-demand, perhaps triggered by cron?

Ignore this, problem solved.  I'll let the slaves query the master
regularly and just reload the entire table. 


/Per Jessen, Zürich


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

Re: restrict mysql replication ?

[Johan De Meersman]

On Wed, Dec 15, 2010 at 10:08 AM, Per Jessen p...@computer.org wrote:

 Per Jessen wrote:

  Is there a way of limiting that?  Alternatively, is there a way of
  doing replication-on-demand, perhaps triggered by cron?

 Ignore this, problem solved.  I'll let the slaves query the master
 regularly and just reload the entire table.


That works. As for what you're seeing, you're probably limiting replication
on the slave instead of limiting binlogging on the master. Can't quite
recall the exact option, something like binlog-do-db I think.

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

Re: restrict mysql replication ?

[Per Jessen]

Johan De Meersman wrote:

 On Wed, Dec 15, 2010 at 10:08 AM, Per Jessen p...@computer.org wrote:
 
 Per Jessen wrote:

  Is there a way of limiting that?  Alternatively, is there a way of
  doing replication-on-demand, perhaps triggered by cron?

 Ignore this, problem solved.  I'll let the slaves query the master
 regularly and just reload the entire table.

 
 That works. As for what you're seeing, you're probably limiting
 replication on the slave instead of limiting binlogging on the master.

Yes, that sounds like what I'm doing. 

 Can't quite recall the exact option, something like binlog-do-db I
 think.

Thanks for the hint, might still come in useful.


/Per Jessen, Zürich


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

MySQL replication server

[Machiel Richards]

Hi All

sorry to bother everyone again. but now I have a question from a
client which I am sure about my answer, however need to confirm.

When setting up a master/slave replication set.

As I understand it, the slave server can't accept any writes,
however it will be able to accept reads.

Is this correct, or will the slave server still be able to
accept writes as well (even though it may not be replicated) ?

Kind Regards

Machiel

Re: MySQL replication server

[John Daisley]

You are correct, in a master slave setup the slave does not accept writes.

John

On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote:

 Hi All

sorry to bother everyone again. but now I have a question from a
 client which I am sure about my answer, however need to confirm.

When setting up a master/slave replication set.

As I understand it, the slave server can't accept any writes,
 however it will be able to accept reads.

Is this correct, or will the slave server still be able to
 accept writes as well (even though it may not be replicated) ?

 Kind Regards

 Machiel




-- 
John Daisley

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

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

Re: MySQL replication server

[a . smith]

Hi,

  I think you are wrong, slaves will always accept writes unless you  
set readonly in the mysql config.
Due to this, and if you dont specifically set readonly on the slave  
you have to be very careful in order to maintain data integrity on the  
slave and also not to break repliacton. Tools like Maatkit are  
designed to check data integrity on the slave due to exactly this issue,


thanks Andy.

Quoting John Daisley daisleyj...@googlemail.com:


You are correct, in a master slave setup the slave does not accept writes.

John

On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote:







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

Re: MySQL replication server

[Machiel Richards]

My reason for asking this is the following


The client have database A on one machine, Database B on a second
machine both of which are production.

They want to setup replication of Database B to Server hosting Database
A and still keep Server A as the primary production system.





-Original Message-
From: a.sm...@ukgrid.net
To: John Daisley daisleyj...@googlemail.com
Cc: Machiel Richards machi...@rdc.co.za, mysql mailing list
mysql@lists.mysql.com
Subject: Re: MySQL replication server
Date: Mon, 22 Nov 2010 13:03:38 +


Hi,

   I think you are wrong, slaves will always accept writes unless you  
set readonly in the mysql config.
Due to this, and if you dont specifically set readonly on the slave  
you have to be very careful in order to maintain data integrity on the  
slave and also not to break repliacton. Tools like Maatkit are  
designed to check data integrity on the slave due to exactly this issue,

thanks Andy.

Quoting John Daisley daisleyj...@googlemail.com:

 You are correct, in a master slave setup the slave does not accept writes.

 John

 On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote:

Re: MySQL replication server

[John Daisley]

The replicated database should not be accepting writes, if it is then you
haven't set it up correctly

On 22 November 2010 13:03, a.sm...@ukgrid.net wrote:

 Hi,

  I think you are wrong, slaves will always accept writes unless you set
 readonly in the mysql config.
 Due to this, and if you dont specifically set readonly on the slave you
 have to be very careful in order to maintain data integrity on the slave and
 also not to break repliacton. Tools like Maatkit are designed to check data
 integrity on the slave due to exactly this issue,

 thanks Andy.


 Quoting John Daisley daisleyj...@googlemail.com:

 You are correct, in a master slave setup the slave does not accept writes.

 John

 On 22 November 2010 11:06, Machiel Richards machi...@rdc.co.za wrote:








-- 
John Daisley

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

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

Re: MySQL replication server

[Tyler Poland]

Additionally, if a user has the SUPER privilege (eg. all privileges on 
*.*) they can write to a database running in read-only mode.  Yet 
another reason to never allow this privilege for general purpose users.


Tyler

On 11/22/10 8:08 AM, John Daisley wrote:

The replicated database should not be accepting writes, if it is then you
haven't set it up correctly

On 22 November 2010 13:03,a.sm...@ukgrid.net  wrote:


Hi,

  I think you are wrong, slaves will always accept writes unless you set
readonly in the mysql config.
Due to this, and if you dont specifically set readonly on the slave you
have to be very careful in order to maintain data integrity on the slave and
also not to break repliacton. Tools like Maatkit are designed to check data
integrity on the slave due to exactly this issue,

thanks Andy.


Quoting John Daisleydaisleyj...@googlemail.com:

You are correct, in a master slave setup the slave does not accept writes.

John

On 22 November 2010 11:06, Machiel Richardsmachi...@rdc.co.za  wrote:











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

Re: MySQL replication server

[a . smith]

Hi,

  so yes you can do that, but then I guess you cannot set the server  
hosting database A as readonly (from memory this can only be set  
server wide, but worht checking it out). Which might leave you a few  
options to ensure data integrity, for example simply by user security  
either by disabling access to relevant users or via setting grants  
appropriately. Or you could look at a second MySQL instance on the  
database A server either listening on another port or in a virtual  
server/zone/jail,


Andy.

Quoting Machiel Richards machi...@rdc.co.za:


My reason for asking this is the following


The client have database A on one machine, Database B on a second
machine both of which are production.

They want to setup replication of Database B to Server hosting Database
A and still keep Server A as the primary production system.







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

Re: MySQL replication server

[Johan De Meersman]

On Mon, Nov 22, 2010 at 2:08 PM, John Daisley daisleyj...@googlemail.comwrote:

 The replicated database should not be accepting writes, if it is then you
 haven't set it up correctly


*shrug*

I never bother. The slave is way too useful to fuck around with
optimisations and whatnot, reporting tools tend to do useful aggregations,
et cetera.

You may like to set it read-only, but that doesn't make it the only way, let
a lone a requirement.


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

MySQL replication SSL

[Paul Nowosielski]

Dear All,

I'm trying to get SSL connections 
for all mysql slave and masters.

I have one box that will not use SSL for some reason.
When I start this slave it can not connect because 
it's not using SSL.




show slave status\G
*** 1. row ***
 Slave_IO_State: Connecting to master
Master_Host: myhost
Master_User: rep_user
Master_Port: 3306
  Connect_Retry: 60
Master_Log_File: mysql-bin.95
Read_Master_Log_Pos: 1095
 Relay_Log_File: slave-relay.04
  Relay_Log_Pos: 98
  Relay_Master_Log_File: mysql-bin.95
   Slave_IO_Running: No
  Slave_SQL_Running: Yes
Replicate_Do_DB: crm
Replicate_Ignore_DB: 
 Replicate_Do_Table: 
 Replicate_Ignore_Table: 
Replicate_Wild_Do_Table: 
Replicate_Wild_Ignore_Table: 
 Last_Errno: 0
 Last_Error: 
   Skip_Counter: 0
Exec_Master_Log_Pos: 1095
Relay_Log_Space: 98
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



Here is a portion of my.cnf.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
user=mysql
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
max_connections=500
max_connect_errors = 0

# replication
server-id = 1
replicate-same-server-id = 0
auto-increment-increment =3
auto-increment-offset =1

master-host = myHost
master-user = rep_user
master-password = rep_passwd
master-connect-retry = 60
replicate-do-db = crm

log-bin= /var/log/mysql/mysql-bin.log
binlog-do-db = crm

relay-log = /var/lib/mysql/slave-relay.log
relay-log-index = /var/lib/mysql/slave-relay-log.index

expire_logs_days = 10
max_binlog_size = 500M
# end replication


# SSL for replication
ssl
ssl-key=/etc/mysql/ssl/server-key.pem
ssl-cert=/etc/mysql/ssl/server-cert.pem
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-capath=/etc/mysql/ssl/
ssl-cipher=DHE-RSA-AES256-SHA





[client]
ssl
port = 3306
socket = /var/lib/mysql/mysql.sock
ssl-ca=/etc/mysql/ssl/ca-cert.pem
ssl-key=/etc/mysql/ssl/client-key.pem
ssl-cert=/etc/mysql/ssl/client-cert.pem
ssl-cipher=DHE-RSA-AES256-SHA


Anyone see any issues with this?

Thank you,

Paul




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

MySQL replication and reverse replication?

[Machiel Richards]

Good day all

 I am hoping everyone is well.


Can someone perhaps assist me with some resources on the following
topic


one of our clients are running a MySQL 5 master/slave replication
setup.

There is a DR test schedule during next month, however they want the
slave server to become the master and the master server to become the
slave server.

I am busy looking into how to setup a master / slave replication,
however I am trying to find out how to do the relevant changes as
mentioned above.

The next test they then want to do is to test the switch over to the
slave server in cases where the master server should fail. I am however
not that experienced on MySQL as yet and cant seem to find specific
references on how to do this change over?


any ideas or resources will be appreciated.


Regards
Machiel

Re: MySQL Replication

[Tompkins Neil]

Hi

Just one other question.  With regards the replication in MySQL 5.1 - does
it it replication the whole row of data or just the field in which the data
has been changed for the current record ?

Thanks
Neil


2010/6/24 Jaime Crespo Rincón jcre...@warp.es

 2010/6/24 Tompkins Neil neil.tompk...@googlemail.com:
  Hi
 
  Regarding two-way replication what do you mean by very
  controlled environment ?  What things do I need to consider ?

 Control at application level that you are not going to
 insert/update/delete the same record on the two servers.
 Even if MySQL gives some support to handle this
 (auto-increment-offset, replicate-ignore-table), you should mostly
 handle it at business logic (application server) layer, not in the
 MySQL database. Alternatively, as Johan pointed, have a look at the
 semi-synchronous replication.


 --
 Jaime Crespo
 MySQL  Java Instructor
 Warp Networks
 http://warp.es

Re: MySQL Replication

[Jaime Crespo Rincón]

2010/6/30 Tompkins Neil neil.tompk...@googlemail.com:
 Hi

 Just one other question.  With regards the replication in MySQL 5.1 - does
 it it replication the whole row of data or just the field in which the data
 has been changed for the current record ?

MySQL 5.1 supports two replication formats: row and statement-based.
Please, have a look at the manual page:
http://dev.mysql.com/doc/refman/5.1/en/replication-formats.html

-- 
Jaime Crespo
MySQL  Java Instructor
Warp Networks
http://warp.es

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

Re: MySQL Replication - Master-Slave crash

[Manasi Save]

Dear Nilnandan,
 
Thanks for the quick reply. 
 
But this particular variable is not working in only one proc call...but
rest all procedures creating temporary tables, does not affect my
replication slaves.
 
So I am not able to identify the problem.
 
Here's the snapshot of my procedure which is causing a problem.
 
Drop Temporary Table If Exists norep_Temp3;
Create Temporary Table norep_Temp3
(
  UserID BigInt,
  FirstName Varchar(45),
  LastName Varchar(45)

);
 
Insert into norep_Temp3(UserID, FirstName, LastName)
Select UserID From tbl1;
 
Update norep_Temp3 T3, tbl2
Set T3.FirstName = tbl2.FirstName,
T3.LastName = tbl2.LastName
Where T3.UserID = tbl2.UserID;
 
Insert into MyTbl(UserID, Name)
Select UserID, Concat(FirstName,' ',LastName) From norep_Temp3;

 --
Regards,
 Manasi Save 

On Wed, 23 Jun 2010 18:47:19  0530, Nilnandan Joshi  wrote:
Hi Manasi,
 
  Please try with this one.
  Replicate_Wild_Ignore_Table = mydb\temp_.%
 
  Regards,
  Nilnandan Joshi
 
  Manasi Save wrote:
   Hi All,
  
   I have kept
   Replicate_Wild_Ignore_Table = mydb%.temp_%
  
   this is temporary table  which i want should not be replicated. But
   still it is getting replicated and slave is getting crashed.
  
  
   any input will be a great help.
  
   --
   Thanks and Regards,
   Manasi Save
 
 


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

MySQL Replication

[Tompkins Neil]

HI,

We have set-up MySQL Community Server 5.1.46 with Master to Slave
replication and everything appears to be working correctly, however I have a
couple of questions which I hope somebody can shed some light.

(1) When the network connection goes down between the master and slave
servers, it would appear that the updates are only sent from the master to
the slave, but not from the slave to the master when the connect is
re-established.  Is this correct ?

(2) What is the situation regarding conflicts if the same master and slave
record is edited at the same time ?

Thanks for any help.

Neil

Re: MySQL Replication

[Jaime Crespo Rincón]

2010/6/24 Tompkins Neil neil.tompk...@googlemail.com:
 HI,

 We have set-up MySQL Community Server 5.1.46 with Master to Slave
 replication and everything appears to be working correctly, however I have a
 couple of questions which I hope somebody can shed some light.

 (1) When the network connection goes down between the master and slave
 servers, it would appear that the updates are only sent from the master to
 the slave, but not from the slave to the master when the connect is
 re-established.  Is this correct ?

In a master-slave architecture, updates are always from the master to
the slave. If you want two-way replication, that is a master-master
setup, but not recommended in general unless in a very controlled
environment.

 (2) What is the situation regarding conflicts if the same master and slave
 record is edited at the same time ?

Fail :-) Whenever there is a conflict in the replication process, it
stops. You have to solve the issues manually and then start the
replication again. This usually occurs due to the
synchronous/distributed nature of the replication.

-- 
Jaime Crespo
MySQL  Java Instructor
Warp Networks
http://warp.es

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

Re: MySQL Replication

[Mark Goodge]

On 24/06/2010 09:18, Tompkins Neil wrote:

HI,

We have set-up MySQL Community Server 5.1.46 with Master to Slave
replication and everything appears to be working correctly, however I have a
couple of questions which I hope somebody can shed some light.

(1) When the network connection goes down between the master and slave
servers, it would appear that the updates are only sent from the master to
the slave, but not from the slave to the master when the connect is
re-established.  Is this correct ?


Yes. Replication is one-way by default. If you want two-way replication 
you have to set it up explicitly with both servers simultaneously acting 
as both master and slave.



(2) What is the situation regarding conflicts if the same master and slave
record is edited at the same time ?


You shouldn't normally edit records on the slave while it's acting as a 
slave. Replication has two main functions: to provide a hot backup of 
the master so that you can switch to the slave as the new master 
instantly should the master fail, and to allow load balancing by 
performing all reads on the slave (or multiple slaves) and updating only 
the master (eg, where you have a web cluster with each web server having 
its own MySQL instance acting as a slave from a central master updated 
from your CMS).


Two-way replication is possible, but there are rarely any significant 
benefits from it. If you do use two-way replication, you have to 
implement locking at the application level as MySQL doesn't provide it 
natively.


See the replication FAQ for more information:

http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html

Mark
--
http://mark.goodge.co.uk

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

Re: MySQL Replication

[Tompkins Neil]

Thanks for your quick response.

Basically our need for replication is because our websites access a local
MySQL database - which is fine.  In our remote office, we also need to
access this MySQL database too however the connect time/ query speed is very
slow.  At the moment the application in the office needs to update certain
fields (not all).  Therefore I thought we'd look into using replication.

In your opinion what is the best method for us to use ?

Cheers
Neil

On Thu, Jun 24, 2010 at 9:31 AM, Mark Goodge m...@good-stuff.co.uk wrote:

 On 24/06/2010 09:18, Tompkins Neil wrote:

 HI,

 We have set-up MySQL Community Server 5.1.46 with Master to Slave
 replication and everything appears to be working correctly, however I have
 a
 couple of questions which I hope somebody can shed some light.

 (1) When the network connection goes down between the master and slave
 servers, it would appear that the updates are only sent from the master to
 the slave, but not from the slave to the master when the connect is
 re-established.  Is this correct ?


 Yes. Replication is one-way by default. If you want two-way replication you
 have to set it up explicitly with both servers simultaneously acting as both
 master and slave.


  (2) What is the situation regarding conflicts if the same master and slave
 record is edited at the same time ?


 You shouldn't normally edit records on the slave while it's acting as a
 slave. Replication has two main functions: to provide a hot backup of the
 master so that you can switch to the slave as the new master instantly
 should the master fail, and to allow load balancing by performing all reads
 on the slave (or multiple slaves) and updating only the master (eg, where
 you have a web cluster with each web server having its own MySQL instance
 acting as a slave from a central master updated from your CMS).

 Two-way replication is possible, but there are rarely any significant
 benefits from it. If you do use two-way replication, you have to implement
 locking at the application level as MySQL doesn't provide it natively.

 See the replication FAQ for more information:

 http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html

 Mark
 --
 http://mark.goodge.co.uk

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com

Re: MySQL Replication

[Tompkins Neil]

Hi

Regarding two-way replication what do you mean by very
controlled environment ?  What things do I need to consider ?

Cheers
Neil

2010/6/24 Jaime Crespo Rincón jcre...@warp.es

 2010/6/24 Tompkins Neil neil.tompk...@googlemail.com:
  HI,
 
  We have set-up MySQL Community Server 5.1.46 with Master to Slave
  replication and everything appears to be working correctly, however I
 have a
  couple of questions which I hope somebody can shed some light.
 
  (1) When the network connection goes down between the master and slave
  servers, it would appear that the updates are only sent from the master
 to
  the slave, but not from the slave to the master when the connect is
  re-established.  Is this correct ?

 In a master-slave architecture, updates are always from the master to
 the slave. If you want two-way replication, that is a master-master
 setup, but not recommended in general unless in a very controlled
 environment.

  (2) What is the situation regarding conflicts if the same master and
 slave
  record is edited at the same time ?

 Fail :-) Whenever there is a conflict in the replication process, it
 stops. You have to solve the issues manually and then start the
 replication again. This usually occurs due to the
 synchronous/distributed nature of the replication.

 --
 Jaime Crespo
 MySQL  Java Instructor
 Warp Networks
 http://warp.es

Re: MySQL Replication

[Johan De Meersman]

You could have a look at the more recent 5.1 releases, those support
semi-synchronous replication iirc.


On Thu, Jun 24, 2010 at 10:50 AM, Tompkins Neil 
neil.tompk...@googlemail.com wrote:

 Thanks for your quick response.

 Basically our need for replication is because our websites access a local
 MySQL database - which is fine.  In our remote office, we also need to
 access this MySQL database too however the connect time/ query speed is
 very
 slow.  At the moment the application in the office needs to update certain
 fields (not all).  Therefore I thought we'd look into using replication.

 In your opinion what is the best method for us to use ?

 Cheers
 Neil

 On Thu, Jun 24, 2010 at 9:31 AM, Mark Goodge m...@good-stuff.co.uk
 wrote:

  On 24/06/2010 09:18, Tompkins Neil wrote:
 
  HI,
 
  We have set-up MySQL Community Server 5.1.46 with Master to Slave
  replication and everything appears to be working correctly, however I
 have
  a
  couple of questions which I hope somebody can shed some light.
 
  (1) When the network connection goes down between the master and slave
  servers, it would appear that the updates are only sent from the master
 to
  the slave, but not from the slave to the master when the connect is
  re-established.  Is this correct ?
 
 
  Yes. Replication is one-way by default. If you want two-way replication
 you
  have to set it up explicitly with both servers simultaneously acting as
 both
  master and slave.
 
 
   (2) What is the situation regarding conflicts if the same master and
 slave
  record is edited at the same time ?
 
 
  You shouldn't normally edit records on the slave while it's acting as a
  slave. Replication has two main functions: to provide a hot backup of
 the
  master so that you can switch to the slave as the new master instantly
  should the master fail, and to allow load balancing by performing all
 reads
  on the slave (or multiple slaves) and updating only the master (eg, where
  you have a web cluster with each web server having its own MySQL instance
  acting as a slave from a central master updated from your CMS).
 
  Two-way replication is possible, but there are rarely any significant
  benefits from it. If you do use two-way replication, you have to
 implement
  locking at the application level as MySQL doesn't provide it natively.
 
  See the replication FAQ for more information:
 
  http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html
 
  Mark
  --
  http://mark.goodge.co.uk
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
  http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
 
 




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

Re: MySQL Replication

[Jaime Crespo Rincón]

2010/6/24 Tompkins Neil neil.tompk...@googlemail.com:
 Hi

 Regarding two-way replication what do you mean by very
 controlled environment ?  What things do I need to consider ?

Control at application level that you are not going to
insert/update/delete the same record on the two servers.
Even if MySQL gives some support to handle this
(auto-increment-offset, replicate-ignore-table), you should mostly
handle it at business logic (application server) layer, not in the
MySQL database. Alternatively, as Johan pointed, have a look at the
semi-synchronous replication.


-- 
Jaime Crespo
MySQL  Java Instructor
Warp Networks
http://warp.es

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

MySQL Replication - Master-Slave crash

[Manasi Save]

Hi All,

I have kept
Replicate_Wild_Ignore_Table = mydb%.temp_%

this is temporary table which i want should not be replicated. But still
it is getting replicated and slave is getting crashed.


any input will be a great help.
 --Thanks and Regards, Manasi Save  Artificial Machines
Private Limited manasi.s...@artificialmachines.com Ph:-9833537392

Re: MySQL Replication - Master-Slave crash

[Nilnandan Joshi]

Hi Manasi,

Please try with this one.
Replicate_Wild_Ignore_Table = mydb\temp_.%

Regards,
Nilnandan Joshi

Manasi Save wrote:

Hi All,
 
I have kept 
Replicate_Wild_Ignore_Table = mydb%.temp_%
 
this is temporary table  which i want should not be replicated. But 
still it is getting replicated and slave is getting crashed.
 
 
any input will be a great help.


--
Thanks and Regards,
Manasi Save
Artificial Machines Private Limited
manasi.s...@artificialmachines.com
Ph:-9833537392


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

Reason for MySQL Replication Slave Crash

[Manasi Save]

Dear All,I am using MySQL Master-Master Replication. 
Where most of the times it is happening that my slave crashes with two
errors either :- 1. Duplicate Entry (Error No:- 1062)2. Does not
find the row for update or delete. (Error No :- 1032)Well I google
the problem but unable to find exact reason behind this behaviour of
replication.I read somewhere that this can happen when relay-log.info
does not get updated. But did not find any reason of how to deal with it. 
Does anyone faced similar type of issue in MySQL Replication. Any
input will be a great help.Thanks in advance.--Regards,Manasi Save 

Re: Reason for MySQL Replication Slave Crash

[Shawn Green]

Hello Manasi,

Manasi Save wrote:

Dear All,

I am using MySQL Master-Master Replication.

Where most of the times it is happening that my slave crashes with two 
errors either :-


1. Duplicate Entry (Error No:- 1062)



That means that a row with the same PRIMARY or UNIQUE key value(s) 
already exists on this server. Somehow you are not protecting yourself 
against writing the same things to both servers at the same time.




2. Does not find the row for update or delete. (Error No :- 1032)



Same problem, in reverse. This time, though, the row you are trying to 
remove has already been removed.



Well I google the problem but unable to find exact reason behind this 
behaviour of replication.




There is never an exact reason for this type of problem. It is a 
well-known engineering requirement that when replicating MySQL servers 
in a ring that you absolutely must avoid changing the same row of data 
(as identified by the tuple used for either the PRIMARY or UNIQUE keys) 
on both servers at nearly the same time. Your MASTER-MASTER 
configuration is simply a two-element ring configuration.


I read somewhere that this can happen when relay-log.info does not get 
updated. But did not find any reason of how to deal with it.


Does anyone faced similar type of issue in MySQL Replication. Any input 
will be a great help.


Here are my suggestions.

1) Read how the replication systems of MySQL actually work. Only 
completed changes to the database are written to the binary log as 
either statements (to be repeated on the slave) or as row deltas (to be 
applied by the slave to its data). Those binary log entries are spooled 
asynchronously to the slave where they are buffered into the relay logs. 
One the slave a second thread (different than the one used to fill up 
the relay logs with binary log events) then steps through the relay logs 
 one statement or change at a time.


http://dev.mysql.com/doc/refman/5.1/en/replication.html
http://dev.mysql.com/doc/refman/5.1/en/replication-implementation-details.html

2) Read the specific sections and FAQs about ring-based replication. 
There are some good things you can configure that will mitigate, but not 
eliminate, your exposure to the errors you reported above.


http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html#qandaitem-16-3-4-1-5
http://dev.mysql.com/doc/refman/5.1/en/replication-features.html

3) Then read all of the warnings from other sites that tell you how to 
configure this type of replication ring. Here's just one:


http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html

Ring-based replication has many potential problems and very few 
benefits. It is a very difficult configuration to use properly. I do not 
recommend it for most purposes. The fact that you did not check the 
binary log entries against the actual data to detect that the duplicates 
or deletions were already on the table (and probably caused by another 
session) implies to me that your administrative skills may not yet be 
ready for this particular challenge.  May I recommend that you switch 
back to the much easier to maintain master-slave replication 
configuration? If not that, at least use your masters in an 
active/passive mode, not active/active.


Warmest regards,
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc.
Office: Blountville, TN



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

Re: Reason for MySQL Replication Slave Crash

[Manasi Save]

Dear Shawn,

Thanks for the quick reply. 

To your points, First I have a query regarding your last line if I want 
to run master-master replication I should run it in active-passive mode. 
Does that mean that I should run only one master at a time. But I am 
doing it because I am not keeping two masters to distribute queries 
equally. 
For example:- I have two servers Server A and Server B

I have db1 on Server A and db2 on Server B
and replication db2 on Server A and db1 on Server B

I am querying Server A for db1 and not Server B for db1. But I want 
Server B to be replicated same time with Server A. 
Same for Server B for db2. 

So ideally in this case I should not get duplicate entry error. Is that 
possible to be happened?


I am not pretty sure that whether you have understood what I meant by 
above example. Please let me know if you have any questions. 

Thanks in advance. 


--
Regards,
Manasi Save

Quoting Shawn Green shawn.gr...@sun.com:

Hello Manasi,

Manasi Save wrote:
 Dear All,

 I am using MySQL Master-Master Replication. 


 Where most of the times it is happening that my slave crashes with two
 errors either :-

 1. Duplicate Entry (Error No:- 1062)


That means that a row with the same PRIMARY or UNIQUE key value(s)
already exists on this server. Somehow you are not protecting yourself
against writing the same things to both servers at the same time. 



 2. Does not find the row for update or delete. (Error No :- 1032)


Same problem, in reverse. This time, though, the row you are trying to
remove has already been removed. 



 Well I google the problem but unable to find exact reason behind this
 behaviour of replication. 



There is never an exact reason for this type of problem. It is a
well-known engineering requirement that when replicating MySQL servers
in a ring that you absolutely must avoid changing the same row of data
(as identified by the tuple used for either the PRIMARY or UNIQUE keys)
on both servers at nearly the same time. Your MASTER-MASTER
configuration is simply a two-element ring configuration. 


 I read somewhere that this can happen when relay-log.info does not get
 updated. But did not find any reason of how to deal with it. 


 Does anyone faced similar type of issue in MySQL Replication. Any input
 will be a great help. 

Here are my suggestions. 


1) Read how the replication systems of MySQL actually work. Only
completed changes to the database are written to the binary log as
either statements (to be repeated on the slave) or as row deltas (to be
applied by the slave to its data). Those binary log entries are spooled
asynchronously to the slave where they are buffered into the relay logs. 
One the slave a second thread (different than the one used to fill up

the relay logs with binary log events) then steps through the relay logs
  one statement or change at a time. 


http://dev.mysql.com/doc/refman/5.1/en/replication.html
http://dev.mysql.com/doc/refman/5.1/en/replication-implementation-details.html

2) Read the specific sections and FAQs about ring-based replication. 
There are some good things you can configure that will mitigate, but not
eliminate, your exposure to the errors you reported above. 


http://dev.mysql.com/doc/refman/5.1/en/replication-faq.html#qandaitem-16-3-4-1-5
http://dev.mysql.com/doc/refman/5.1/en/replication-features.html

3) Then read all of the warnings from other sites that tell you how to
configure this type of replication ring. Here's just one:

http://onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html

Ring-based replication has many potential problems and very few
benefits. It is a very difficult configuration to use properly. I do not
recommend it for most purposes. The fact that you did not check the
binary log entries against the actual data to detect that the duplicates
or deletions were already on the table (and probably caused by another
session) implies to me that your administrative skills may not yet be
ready for this particular challenge.  May I recommend that you switch
back to the much easier to maintain master-slave replication
configuration? If not that, at least use your masters in an
active/passive mode, not active/active. 


Warmest regards,
--
Shawn Green, MySQL Senior Support Engineer
Sun Microsystems, Inc. 
Office: Blountville, TN






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

Re: MySQL Replication Delete is not gettting replicated

[Carlos Proal]

I dont see anything unusual or missing on your config file and as the 
only thing missing are deletes, i think that might be a permission issue.


Can you check out the grants for your replication users and see if they 
have full permissions granted ?


mysql show grants for x;

where is x is replication and replication2 respectively.

Carlos


On 1/18/2010 1:35 AM, Manasi Save wrote:

Hi Anand,

Please find below my configuration file of both the masters:

ON MASTER 1:

[mysqld]
datadir=/var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin=/usr/local/mysql/bin.log
#binlog-do-db=database name  # input the database which should 
be replicated
binlog-ignore-db=mysql# input the database that should be 
ignored for replication

binlog-ignore-db=test
log-bin-index=/usr/local/mysql/log-bin.index
log_slave_updates

server-id=2

auto_increment_increment=2
auto_increment_offset=1

#information for becoming slave.
master-host = 192.168.1.1
master-user = replication
master-password = replication
master-port = 3306

[mysql.server]
user=mysql

[mysqld_safe]
err-log=/var/lib/mysql/mysql.log
pid-file=/var/lib/mysql/mysql.privatedns.com.pid

ON MASTER 2:

[mysqld]
datadir=/var/lib/mysql/
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin=/usr/local/mysql/bin.log
#binlog-do-db=database name  # input the database which should 
be replicated
binlog-ignore-db=mysql# input the database that should be 
ignored for replication

binlog-ignore-db=test
log-bin-index=/usr/local/mysql/log-bin.index
log_slave_updates

server-id=1

auto_increment_increment=2
auto_increment_offset=2

#information for becoming slave.
master-host = 192.168.1.2
master-user = replication2
master-password = replication2
master-port = 3306

[mysql.server]
user=mysql

[mysqld_safe]
err-log=/var/var/lib/mysql/mysql.log
pid-file=/var/lib/mysql/mysql.privatedns.com.pid

Please let me know if I need to add any parameter to enable this 
replication.


Thanks in advance.

--

Regards,

Manasi Save



Quoting Anand kumar :

can you give us the configuration(.cnf) file from both the masters ?
--Anand
On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save
manasi.s...@artificialmachines.com
mailto:manasi.s...@artificialmachines.com wrote:

Hi All,


I have configured MySQL Master-Master Replication on my
servers. When I am inserting or updating any data in a regular
table the data is getting replicated.


But When I am doing delete on that same table. the data is
only getting deleted only on the server where I am doing
delete. but it is not getting replicated on its slave.


Even if I am doing truncate it is not getting replicated. Can
anyone provide any input on this?


Thanks in advance.


--

Regards,

Manasi Save

Re: MySQL Replication Delete is not gettting replicated

[Manasi Save]

Dear Carlos,

Thanks for the response. But I haven't gave any privileges besides 
repl_slave priv to user replication and replication2 respectively. 


So does that amke any difference really?

Thanks in advance. 


--
Regards,
Manasi Save

Quoting Carlos Proal carlos.pr...@gmail.com:


I dont see anything unusual or missing on your config file and as the
only thing missing are deletes, i think that might be a permission issue. 


Can you check out the grants for your replication users and see if they
have full permissions granted ?

mysql show grants for x;

where is x is replication and replication2 respectively. 


Carlos


On 1/18/2010 1:35 AM, Manasi Save wrote:
 Hi Anand,

 Please find below my configuration file of both the masters:

 ON MASTER 1:

 [mysqld]
 datadir=/var/lib/mysql/
 socket=/var/lib/mysql/mysql.sock
 old_passwords=1

 log-bin=/usr/local/mysql/bin.log
 #binlog-do-db=database name  # input the database which should
 be replicated
 binlog-ignore-db=mysql# input the database that should be
 ignored for replication
 binlog-ignore-db=test
 log-bin-index=/usr/local/mysql/log-bin.index
 log_slave_updates

 server-id=2

 auto_increment_increment=2
 auto_increment_offset=1

 #information for becoming slave. 
 master-host = 192.168.1.1

 master-user = replication
 master-password = replication
 master-port = 3306

 [mysql.server]
 user=mysql

 [mysqld_safe]
 err-log=/var/lib/mysql/mysql.log
 pid-file=/var/lib/mysql/mysql.privatedns.com.pid

 ON MASTER 2:

 [mysqld]
 datadir=/var/lib/mysql/
 socket=/var/lib/mysql/mysql.sock
 old_passwords=1

 log-bin=/usr/local/mysql/bin.log
 #binlog-do-db=database name  # input the database which should
 be replicated
 binlog-ignore-db=mysql# input the database that should be
 ignored for replication
 binlog-ignore-db=test
 log-bin-index=/usr/local/mysql/log-bin.index
 log_slave_updates

 server-id=1

 auto_increment_increment=2
 auto_increment_offset=2

 #information for becoming slave. 
 master-host = 192.168.1.2

 master-user = replication2
 master-password = replication2
 master-port = 3306

 [mysql.server]
 user=mysql

 [mysqld_safe]
 err-log=/var/var/lib/mysql/mysql.log
 pid-file=/var/lib/mysql/mysql.privatedns.com.pid

 Please let me know if I need to add any parameter to enable this
 replication. 

 Thanks in advance. 


 --

 Regards,

 Manasi Save



 Quoting Anand kumar :

 can you give us the configuration(.cnf) file from both the masters ?
 --Anand
 On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save
 manasi.s...@artificialmachines.com
 mailto:manasi.s...@artificialmachines.com wrote:

 Hi All,


 I have configured MySQL Master-Master Replication on my
 servers. When I am inserting or updating any data in a regular
 table the data is getting replicated. 



 But When I am doing delete on that same table. the data is
 only getting deleted only on the server where I am doing
 delete. but it is not getting replicated on its slave. 



 Even if I am doing truncate it is not getting replicated. Can
 anyone provide any input on this?


 Thanks in advance. 



 --

 Regards,

 Manasi Save






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

Re: MySQL Replication Delete is not gettting replicated

[Carlos Proal]

Hi Manasi

Yes, you only need the repl_slave_priv,  the show grants should give you 
something like:
GRANT REPLICATION SLAVE ON *.* TO 'replication'@'%' IDENTIFIED BY 
PASSWORD '...'


If thats ok, have check your binlog and relay binlog to see if them 
contain the delete statements?

Im trying to figure out whats wrong.

Carlos

On 1/18/2010 11:06 PM, Manasi Save wrote:

Dear Carlos,

Thanks for the response. But I haven't gave any privileges besides 
repl_slave priv to user replication and replication2 respectively.

So does that amke any difference really?

Thanks in advance.
--
Regards,
Manasi Save

Quoting Carlos Proal carlos.pr...@gmail.com:


I dont see anything unusual or missing on your config file and as the
only thing missing are deletes, i think that might be a permission 
issue.

Can you check out the grants for your replication users and see if they
have full permissions granted ?

mysql show grants for x;

where is x is replication and replication2 respectively.
Carlos


On 1/18/2010 1:35 AM, Manasi Save wrote:
 Hi Anand,

 Please find below my configuration file of both the masters:

 ON MASTER 1:

 [mysqld]
 datadir=/var/lib/mysql/
 socket=/var/lib/mysql/mysql.sock
 old_passwords=1

 log-bin=/usr/local/mysql/bin.log
 #binlog-do-db=database name  # input the database which should
 be replicated
 binlog-ignore-db=mysql# input the database that should be
 ignored for replication
 binlog-ignore-db=test
 log-bin-index=/usr/local/mysql/log-bin.index
 log_slave_updates

 server-id=2

 auto_increment_increment=2
 auto_increment_offset=1

 #information for becoming slave.  master-host = 192.168.1.1
 master-user = replication
 master-password = replication
 master-port = 3306

 [mysql.server]
 user=mysql

 [mysqld_safe]
 err-log=/var/lib/mysql/mysql.log
 pid-file=/var/lib/mysql/mysql.privatedns.com.pid

 ON MASTER 2:

 [mysqld]
 datadir=/var/lib/mysql/
 socket=/var/lib/mysql/mysql.sock
 old_passwords=1

 log-bin=/usr/local/mysql/bin.log
 #binlog-do-db=database name  # input the database which should
 be replicated
 binlog-ignore-db=mysql# input the database that should be
 ignored for replication
 binlog-ignore-db=test
 log-bin-index=/usr/local/mysql/log-bin.index
 log_slave_updates

 server-id=1

 auto_increment_increment=2
 auto_increment_offset=2

 #information for becoming slave.  master-host = 192.168.1.2
 master-user = replication2
 master-password = replication2
 master-port = 3306

 [mysql.server]
 user=mysql

 [mysqld_safe]
 err-log=/var/var/lib/mysql/mysql.log
 pid-file=/var/lib/mysql/mysql.privatedns.com.pid

 Please let me know if I need to add any parameter to enable this
 replication. 
 Thanks in advance. 
 --

 Regards,

 Manasi Save



 Quoting Anand kumar :

 can you give us the configuration(.cnf) file from both the 
masters ?

 --Anand
 On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save
 manasi.s...@artificialmachines.com
 mailto:manasi.s...@artificialmachines.com wrote:

 Hi All,


 I have configured MySQL Master-Master Replication on my
 servers. When I am inserting or updating any data in a regular
 table the data is getting replicated. 

 But When I am doing delete on that same table. the data is
 only getting deleted only on the server where I am doing
 delete. but it is not getting replicated on its slave. 

 Even if I am doing truncate it is not getting replicated. Can
 anyone provide any input on this?


 Thanks in advance. 

 --

 Regards,

 Manasi Save









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

Re: MySQL Replication Delete is not gettting replicated

[Suresh Kuna]

Hi Manasi,
That alone is the difference in this case.

-- 
Thanks
Suresh Kuna
MySQL DBA

On Tue, Jan 19, 2010 at 10:36 AM, Manasi Save 
manasi.s...@artificialmachines.com wrote:

 Dear Carlos,

 Thanks for the response. But I haven't gave any privileges besides
 repl_slave priv to user replication and replication2 respectively.
 So does that amke any difference really?


 Thanks in advance.
 --
 Regards,
 Manasi Save

 Quoting Carlos Proal carlos.pr...@gmail.com:


 I dont see anything unusual or missing on your config file and as the
 only thing missing are deletes, i think that might be a permission issue.
 Can you check out the grants for your replication users and see if they
 have full permissions granted ?

 mysql show grants for x;

 where is x is replication and replication2 respectively.
 Carlos


 On 1/18/2010 1:35 AM, Manasi Save wrote:
  Hi Anand,
 
  Please find below my configuration file of both the masters:
 
  ON MASTER 1:
 
  [mysqld]
  datadir=/var/lib/mysql/
  socket=/var/lib/mysql/mysql.sock
  old_passwords=1
 
  log-bin=/usr/local/mysql/bin.log
  #binlog-do-db=database name  # input the database which should
  be replicated
  binlog-ignore-db=mysql# input the database that should be
  ignored for replication
  binlog-ignore-db=test
  log-bin-index=/usr/local/mysql/log-bin.index
  log_slave_updates
 
  server-id=2
 
  auto_increment_increment=2
  auto_increment_offset=1
 
  #information for becoming slave.  master-host = 192.168.1.1
  master-user = replication
  master-password = replication
  master-port = 3306
 
  [mysql.server]
  user=mysql
 
  [mysqld_safe]
  err-log=/var/lib/mysql/mysql.log
  pid-file=/var/lib/mysql/mysql.privatedns.com.pid
 
  ON MASTER 2:
 
  [mysqld]
  datadir=/var/lib/mysql/
  socket=/var/lib/mysql/mysql.sock
  old_passwords=1
 
  log-bin=/usr/local/mysql/bin.log
  #binlog-do-db=database name  # input the database which should
  be replicated
  binlog-ignore-db=mysql# input the database that should be
  ignored for replication
  binlog-ignore-db=test
  log-bin-index=/usr/local/mysql/log-bin.index
  log_slave_updates
 
  server-id=1
 
  auto_increment_increment=2
  auto_increment_offset=2
 
  #information for becoming slave.  master-host = 192.168.1.2
  master-user = replication2
  master-password = replication2
  master-port = 3306
 
  [mysql.server]
  user=mysql
 
  [mysqld_safe]
  err-log=/var/var/lib/mysql/mysql.log
  pid-file=/var/lib/mysql/mysql.privatedns.com.pid
 
  Please let me know if I need to add any parameter to enable this
  replication. 
  Thanks in advance. 
  --
 
  Regards,
 
  Manasi Save
 
 
 
  Quoting Anand kumar :
 
  can you give us the configuration(.cnf) file from both the masters ?
  --Anand
  On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save
  manasi.s...@artificialmachines.com
  mailto:manasi.s...@artificialmachines.com wrote:
 
  Hi All,
 
 
  I have configured MySQL Master-Master Replication on my
  servers. When I am inserting or updating any data in a regular
  table the data is getting replicated. 
 
  But When I am doing delete on that same table. the data is
  only getting deleted only on the server where I am doing
  delete. but it is not getting replicated on its slave. 
 
  Even if I am doing truncate it is not getting replicated. Can
  anyone provide any input on this?
 
 
  Thanks in advance. 
 
  --
 
  Regards,
 
  Manasi Save
 
 



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

Re: MySQL Replication Delete is not gettting replicated

[Manasi Save]

Hi Anand,Please find below my configuration
file of both the masters:ON MASTER 1:[mysqld]datadir=/var/lib/mysql/socket=/var/lib/mysql/mysql.sockold_passwords=1log-bin=/usr/local/mysql/bin.log#binlog-do-db=database
name   # input the database which should be
replicatedbinlog-ignore-db=mysql 
# input the database that should be ignored for
replicationbinlog-ignore-db=testlog-bin-index=/usr/local/mysql/log-bin.indexlog_slave_updatesserver-id=2auto_increment_increment=2auto_increment_offset=1#information for becoming slave.master-host =
192.168.1.1master-user = replicationmaster-password =
replicationmaster-port = 3306[mysql.server]user=mysql[mysqld_safe]err-log=/var/lib/mysql/mysql.logpid-file=/var/lib/mysql/mysql.privatedns.com.pidON MASTER 2:[mysqld]datadir=/var/lib/mysql/socket=/var/lib/mysql/mysql.sockold_passwords=1log-bin=/usr/local/mysql/bin.log#binlog-do-db=database
name   # input the database which should be
replicatedbinlog-ignore-db=mysql 
# input the database that should be ignored for
replicationbinlog-ignore-db=testlog-bin-index=/usr/local/mysql/log-bin.indexlog_slave_updatesserver-id=1auto_increment_increment=2auto_increment_offset=2#information for becoming slave.master-host =
192.168.1.2master-user = replication2master-password =
replication2master-port = 3306[mysql.server]user=mysql[mysqld_safe]err-log=/var/var/lib/mysql/mysql.logpid-file=/var/lib/mysql/mysql.privatedns.com.pidPlease let me know if I need to add any parameter to enable
this replication.Thanks in advance.--Regards, Manasi Save Quoting
Anand kumar :can you give us the
configuration(.cnf) file from both the masters ?

--Anand
On Sat, Jan 16, 2010 at 3:06 PM, Manasi Save manasi.s...@artificialmachines.com
wrote:

Hi All,

I have configured MySQL Master-Master Replication on my servers. When I am
inserting or updating any data in a regular table the data is getting
replicated.

But When I am doing delete on that same table. the data is only getting
deleted only on the server where I am doing delete. but it is not getting
replicated on its slave.

Even if I am doing truncate it is not getting replicated. Can anyone provide
any input on this?

Thanks in advance.

--
Regards,Manasi Save 

MySQL Replication Delete is not gettting replicated

[Manasi Save]

Hi All,I have configured MySQL Master-Master Replication
on my servers. When I am inserting or updating any data in a regular table the
data is getting replicated.But When I am doing delete on
that same table. the data is only getting deleted only on the server where I am
doing delete. but it is not getting replicated on its slave.Even if I am doing truncate it is not getting replicated. Can anyone
provide any input on this?

Thanks in advance.--Regards,
 Manasi Save 

MySQL University session on May 28: MySQL replication: new features in MySQL 5.1 and 6.0

[Stefan Hinz]

MySQL Replication: Walk-through of the new 5.1 and 6.0 features
http://forge.mysql.com/wiki/MySQL_Replication:_Walk-through_of_the_new_5.1_and_6.0_features

This Thursday (May 28th, 14:00 UTC), Lars Thalmann will give a MySQL
University session on MySQL Replication: Walk-through of the new 5.1 and
6.0 features. (This session was originally scheduled for May 7th, but
had to be put off due to technical problems. Apologies.) Lars is leading
the replication and backup teams at MySQL, so this is one of the best
opportunities to ask whatever questions you might have about new
replication features in MySQL.

For MySQL University sessions, point your browser to this page:

http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity

You need a browser with a working Flash plugin. You may register for a
Dimdim account, but you don't have to. (Dimdim is the conferencing
system we're using for MySQL University sessions. It provides integrated
voice streaming, chat, whiteboard, session recording, and more.)

MySQL University is a free educational online program for
engineers/developers. MySQL University sessions are open to anyone, not
just Sun employees. Sessions are recorded (slides and audio), so if you
can't attend the live session you can look at the recording anytime
after the session.

Here's the schedule for the upcoming weeks:

# June 4, 2009: Boosting Performance With MySQL 5.1 Partitioning
(Giuseppe Maxia)
# June 11, 2009: Building MySQL Releases on Unix (Jörg Brühe)
# June 18, 2009: No session scheduled
# June 15, 2009: MySQL code contributions (Lenz Grimmer)
# July 2: Starring Sakila - a data warehouse mini-tutorial (Roland Bouman)
# July 9 through September 3: Semester break

The schedule is not engraved in stone at this point. Please visit
http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the
up-to-date list. On that page, you can also find the starting time for
many time zones.

Cheers,

Stefan
-- 
***
Sun Microsystems GmbHStefan Hinz
Sonnenallee 1Manager Documentation, Database Group
85551 Kirchheim-Heimstetten  Phone: +49-30-82702940
Germany  Fax:   +49-30-82702941
http://www.sun.de/mysql  mailto: stefan.h...@sun.com

Amtsgericht Muenchen: HRB161028
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering
***


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

MySQL University session on May 7: MySQL replication: new features in MySQL 5.1 and 6.0

[Stefan Hinz]

MySQL Replication: Walk-through of the new 5.1 and 6.0 features
http://forge.mysql.com/wiki/MySQL_Replication:_Walk-through_of_the_new_5.1_and_6.0_features

This Thursday (May 7th, 14:00 UTC), Lars Thalmann will give a MySQL
University session on MySQL Replication: Walk-through of the new 5.1 and
6.0 features. Lars is leading the replication and backup teams at MySQL,
so this is one of the best opportunities to ask whatever questions you
might have about new replication features in MySQL.

For MySQL University sessions, point your browser to this page:

http://webmeeting.dimdim.com/portal/JoinForm.action?confKey=mysqluniversity

You need a browser with a working Flash plugin. You may register for a
Dimdim account, but you don't have to. (Dimdim is the conferencing
system we're using for MySQL University sessions. It provides integrated
voice streaming, chat, whiteboard, session recording, and more.)

MySQL University is a free educational online program for
engineers/developers. MySQL University sessions are open to anyone, not
just Sun employees. Sessions are recorded (slides and audio), so if you
can't attend the live session you can look at the recording anytime
after the session.

Here's the schedule for the upcoming weeks:

* May 7, 2009: MySQL Replication: Walk-through of the new 5.1 and 6.0
features (Lars Thalmann)
* May 14, 2009 (tentative): Programming Towards Multi-Core CPUs (Mikael
Ronström)
* May 21, 2009: No session scheduled
* May 28, 2009: No session scheduled
* June 4, 2009: Boosting Performance With MySQL 5.1 Partitioning
(Giuseppe Maxia)
* June 11, 2009: Building MySQL Releases on Unix (Jörg Brühe)
* June 18, 2009:  Architecture of MySQL Backup (Lars Thalmann)
* July 2 (tentative): Starring Sakila - a data warehouse mini-tutorial
(Roland Bouman)

The schedule is not engraved in stone at this point. Please visit
http://forge.mysql.com/wiki/MySQL_University#Upcoming_Sessions for the
up-to-date list. On that page, you can also find the starting time for
many time zones.

Cheers,

Stefan
-- 
***
Sun Microsystems GmbHStefan Hinz
Sonnenallee 1Manager Documentation, Database Group
85551 Kirchheim-Heimstetten  Phone: +49-30-82702940
Germany  Fax:   +49-30-82702941
http://www.sun.de/mysql  mailto: stefan.h...@sun.com

Amtsgericht Muenchen: HRB161028
Geschaeftsfuehrer: Thomas Schroeder, Wolfgang Engels, Dr. Roland Boemer
Vorsitzender des Aufsichtsrates: Martin Haering
***

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

MySQL replication status plugin

[Gabriel - IP Guys]

Good morning guys, (and girls), I hope all is well. I've been given the
task to, and I quote - Write a Nagios plugin to test the replication
status of two servers by 
comparing the position on the master to that on the slave 

 

To save myself a lot of work, I'd like to know if anything has been done
in this arena already, I would be over the moon, if someone has beaten
me to it, but I am so not in the mood to write one!

 

Any hints, recommendations, and ideas are wholly welcome!

 

---

Kind Regards,

Mr Gabriel

 

RE: MySQL replication status plugin

[Andrew Braithwaite]

You could try this:

http://www.consol.de/opensource/nagios/check-mysql-health

(in German but should be self-explanatory).

Cheers,

Andrew

-Original Message-
From: Gabriel - IP Guys [mailto:gabr...@impactteachers.com] 
Sent: 15 April 2009 10:12
To: replicat...@lists.mysql.com
Cc: mysql@lists.mysql.com
Subject: MySQL replication status plugin

Good morning guys, (and girls), I hope all is well. I've been given the
task to, and I quote - Write a Nagios plugin to test the replication
status of two servers by 
comparing the position on the master to that on the slave 

 

To save myself a lot of work, I'd like to know if anything has been done
in this arena already, I would be over the moon, if someone has beaten
me to it, but I am so not in the mood to write one!

 

Any hints, recommendations, and ideas are wholly welcome!

 

---

Kind Regards,

Mr Gabriel

 


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

Re: MySQL replication status plugin

[Jim Lyons]

I'd just write a perl script to do it and return the appropriate status
code/message to nagios.  Shouldn't be hard at all.  PhP or any language that
can talk to mysql would work, too.  You just mentioned the position, you'll
have to compare the names of the binlog files as well: position 100 in file
bin-00010 is ahead of positions 9 in file bin-9.

On Wed, Apr 15, 2009 at 4:12 AM, Gabriel - IP Guys 
gabr...@impactteachers.com wrote:

 Good morning guys, (and girls), I hope all is well. I've been given the
 task to, and I quote - Write a Nagios plugin to test the replication
 status of two servers by
 comparing the position on the master to that on the slave



 To save myself a lot of work, I'd like to know if anything has been done
 in this arena already, I would be over the moon, if someone has beaten
 me to it, but I am so not in the mood to write one!



 Any hints, recommendations, and ideas are wholly welcome!



 ---

 Kind Regards,

 Mr Gabriel






-- 
Jim Lyons
Web developer / Database administrator
http://www.weblyons.com

Re: MySQL replication status plugin

[Baron Schwartz]

I would not compare binlog positions.  I would use mk-heartbeat from
Maatkit.  It tells the truth in a much simpler and more direct way.
Instead of checking things that indicate your data is being
replicated, just replicate some data and check the data itself.

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

Mysql Replication out of sync

[Shain Miley]

Hello all,
I noticed that my two Mysql slave servers were running out of sync this 
morning.  After looking into it...it appears that yesterday when I tried 
to change the username of a user on the master server...it caused some 
issues on the slaves.  Here is what I was able to pull from the slave 
error logs:


090108 16:46:05 [ERROR] Slave SQL: Error 'Operation CREATE USER failed 
for 'root'@'127.0.0.1'' on query. Default database: 'mysql'. Query: 
'CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD 
'*3313E27C7AD7F792A60D5D651029375E60'', Error_code: 1396


090108 16:46:05 [Warning] Slave: Operation CREATE USER failed for 
'root'@'127.0.0.1' Error_code: 1396


090108 16:46:05 [ERROR] Error running query, slave SQL thread aborted. 
Fix the problem, and restart the slave SQL thread with SLAVE START. We 
stopped at log 'master-bin.08' position 970652826



Can anyone tell me if/how I can remove that statement from the log 
file...so I can restart the slave?


Is there a better way to fix this?

Thanks,

Shain


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

Re: Mysql Replication out of sync

[Jose Julian Buda]

maybe if you put on the slave server :

mysql STOP SLAVE;
mysql SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql START SLAVE;

then just to check take a look at this lines :

mysql show slave status\G;
...
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
...


it will restart the replication, but you have to check then why this happened, 
just in case...


  - Original Message - 
  From: Shain Miley 
  To: mysql@lists.mysql.com 
  Sent: Friday, January 09, 2009 3:17 PM
  Subject: Mysql Replication out of sync


  Hello all,
  I noticed that my two Mysql slave servers were running out of sync this 
  morning.  After looking into it...it appears that yesterday when I tried 
  to change the username of a user on the master server...it caused some 
  issues on the slaves.  Here is what I was able to pull from the slave 
  error logs:

  090108 16:46:05 [ERROR] Slave SQL: Error 'Operation CREATE USER failed 
  for 'root'@'127.0.0.1'' on query. Default database: 'mysql'. Query: 
  'CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD 
  '*3313E27C7AD7F792A60D5D651029375E60'', Error_code: 1396

  090108 16:46:05 [Warning] Slave: Operation CREATE USER failed for 
  'root'@'127.0.0.1' Error_code: 1396

  090108 16:46:05 [ERROR] Error running query, slave SQL thread aborted. 
  Fix the problem, and restart the slave SQL thread with SLAVE START. We 
  stopped at log 'master-bin.08' position 970652826


  Can anyone tell me if/how I can remove that statement from the log 
  file...so I can restart the slave?

  Is there a better way to fix this?

  Thanks,

  Shain


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


  __ Información de NOD32, revisión 3749 (20090107) __

  Este mensaje ha sido analizado con  NOD32 antivirus system
  http://www.nod32.com

Re: Mysql Replication out of sync

[Shain Miley]

Jose,
Thank you very much...I found that fix a few minutes before I read your 
email...however I really appreciate your assistance. 

Just an FYI to anyone one else though...I had to skip a total of 2 
queries (I executed  SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;  two times) 
because of the particular query I had problems with...


Thanks again,

Shain


Jose Julian Buda wrote:

maybe if you put on the slave server :

mysql STOP SLAVE;
mysql SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;
mysql START SLAVE;

then just to check take a look at this lines :

mysql show slave status\G;
...
   Slave_IO_Running: Yes
  Slave_SQL_Running: Yes
...


it will restart the replication, but you have to check then why this happened, 
just in case...


  - Original Message - 
  From: Shain Miley 
  To: mysql@lists.mysql.com 
  Sent: Friday, January 09, 2009 3:17 PM

  Subject: Mysql Replication out of sync


  Hello all,
  I noticed that my two Mysql slave servers were running out of sync this 
  morning.  After looking into it...it appears that yesterday when I tried 
  to change the username of a user on the master server...it caused some 
  issues on the slaves.  Here is what I was able to pull from the slave 
  error logs:


  090108 16:46:05 [ERROR] Slave SQL: Error 'Operation CREATE USER failed 
  for 'root'@'127.0.0.1'' on query. Default database: 'mysql'. Query: 
  'CREATE USER 'root'@'127.0.0.1' IDENTIFIED BY PASSWORD 
  '*3313E27C7AD7F792A60D5D651029375E60'', Error_code: 1396


  090108 16:46:05 [Warning] Slave: Operation CREATE USER failed for 
  'root'@'127.0.0.1' Error_code: 1396


  090108 16:46:05 [ERROR] Error running query, slave SQL thread aborted. 
  Fix the problem, and restart the slave SQL thread with SLAVE START. We 
  stopped at log 'master-bin.08' position 970652826



  Can anyone tell me if/how I can remove that statement from the log 
  file...so I can restart the slave?


  Is there a better way to fix this?

  Thanks,

  Shain


  -- 
  MySQL General Mailing List

  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
http://lists.mysql.com/mysql?unsub=jb...@noticiasargentinas.com


  __ Información de NOD32, revisión 3749 (20090107) __

  Este mensaje ha sido analizado con  NOD32 antivirus system
  http://www.nod32.com


  



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

Re: mysql replication

[Moon's Father]

I think this is the same as the other system.

On Mon, Mar 31, 2008 at 10:32 PM, Daniel Brown [EMAIL PROTECTED] wrote:

 On Mon, Mar 31, 2008 at 9:39 AM, Kaushal Shriyan
 [EMAIL PROTECTED] wrote:
  Hi
 
   Is there a documentation on replication of MySQL Database on Gentoo

 From the manual:
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

 --
 /Daniel P. Brown
 Forensic Services, Senior Unix Engineer
 1+ (570-) 362-0283

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




-- 
I'm a mysql DBA in china.
More about me just visit here:
http://yueliangdao0608.cublog.cn

mysql replication

[Kaushal Shriyan]

Hi

Is there a documentation on replication of MySQL Database on Gentoo

Thanks and Regards

Kaushal

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

Re: mysql replication

[Mike Zupan]

http://gentoo-wiki.com/HOWTO_MySQL_Replication

On 3/31/08, Kaushal Shriyan [EMAIL PROTECTED] wrote:

 Hi

 Is there a documentation on replication of MySQL Database on Gentoo

 Thanks and Regards

 Kaushal

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

Re: mysql replication

[Daniel Brown]

On Mon, Mar 31, 2008 at 9:39 AM, Kaushal Shriyan
[EMAIL PROTECTED] wrote:
 Hi

  Is there a documentation on replication of MySQL Database on Gentoo

From the manual:
http://dev.mysql.com/doc/refman/5.0/en/replication-howto.html

-- 
/Daniel P. Brown
Forensic Services, Senior Unix Engineer
1+ (570-) 362-0283

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

mysql replication

[Naufal Sheikh]

Hello,

Just a small question. I had mysql replication configured on my master and
slave server. Due to some issues ( when I was on vacations ), logging on
master server was switched off and server restarted. Now my question is that
in order to resysnc my master and slave correctly, do I need to copy the
master database again to slave and then turn on logging and start slave
again, or by just enabling the logging again, slave will resync from last
known state. I believe that if the logging on master was switched off, it
will have no record of the changes made to database and I will need top
recopy the database, but just to confirm or if there is any simpler way.

Thanks

Re: mysql replication

[B. Keith Murphy]

Naufal,

You probably need to start with a clean slate.  Do a complete resync and 
start the slave back up from a known stopping point.


Keith

Naufal Sheikh wrote:

Hello,

Just a small question. I had mysql replication configured on my master and
slave server. Due to some issues ( when I was on vacations ), logging on
master server was switched off and server restarted. Now my question is that
in order to resysnc my master and slave correctly, do I need to copy the
master database again to slave and then turn on logging and start slave
again, or by just enabling the logging again, slave will resync from last
known state. I believe that if the logging on master was switched off, it
will have no record of the changes made to database and I will need top
recopy the database, but just to confirm or if there is any simpler way.

Thanks

  



--
B. Keith Murphy

Paragon Consulting Services
http://www.paragon-cs.com
850-637-3877


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

mysql replication....

[bruce]

hi...

a quick question that i haven't found an answer to.

i can use replicate-do-db=foo in a my.cnf file for replication, to
replicate the master foo db on the slave. but this requires that i use/have
a my.cnf set on the slave.

is there a way to dynamically set this attribute/parameter within mysql on
the fly. i thought it would be possible via change master to but didn't
find the cmd when looking through the mysql information.

basically, i'm going to have multiple databases, on multiple systems, that
i'm going to be replicating to a single system. so, for each master server,
i'd like to be able to set the databases that i'm going to replicate...

thanks


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

Re: mysql replication....

[B. Keith Murphy]

bruce wrote:

hi...

a quick question that i haven't found an answer to.

i can use replicate-do-db=foo in a my.cnf file for replication, to
replicate the master foo db on the slave. but this requires that i use/have
a my.cnf set on the slave.

is there a way to dynamically set this attribute/parameter within mysql on
the fly. i thought it would be possible via change master to but didn't
find the cmd when looking through the mysql information.

basically, i'm going to have multiple databases, on multiple systems, that
i'm going to be replicating to a single system. so, for each master server,
i'd like to be able to set the databases that i'm going to replicate...

thanks


  
Can't do that currently in MySQL.   It is called multi-master 
replication.  You can do multi-slave replication which replicates from 
one master to multiple slaves, but not the other way around.


Keith

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

Re: mysql replication....

[Baron Schwartz]

You can only do that in the my.cnf file.

On Nov 27, 2007 9:50 AM, bruce [EMAIL PROTECTED] wrote:
 hi keith...

 i recognize you can't do multiple masters to a single slave with mysql's
 replication.

 but you can setup separate mysql slave dbs that are independent, and that yo
 can then iteratively walk through each slave/master, one at a time, and then
 do the sync/update for each one... this essentially gets you the
 slave/master replication for each server, replicated to the slave db on the
 system. the result is a bunch of different slave dbs, instead of a single
 db...

 however, that didn't get me my answer to my question...

 so, how can you do a replicate-do-db from within the mysql cmd???

 in fact, even if i only had a single master, but multiple dbs, i'd still
 like to know this, given that i might not want to use the my.cnf file...

 thanks


 -Original Message-
 From: B. Keith Murphy [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, November 27, 2007 6:31 AM
 To: bruce; 'mysql list'
 Subject: Re: mysql replication


 bruce wrote:
  hi...
 
  a quick question that i haven't found an answer to.
 
  i can use replicate-do-db=foo in a my.cnf file for replication, to
  replicate the master foo db on the slave. but this requires that i
 use/have
  a my.cnf set on the slave.
 
  is there a way to dynamically set this attribute/parameter within mysql on
  the fly. i thought it would be possible via change master to but didn't
  find the cmd when looking through the mysql information.
 
  basically, i'm going to have multiple databases, on multiple systems, that
  i'm going to be replicating to a single system. so, for each master
 server,
  i'd like to be able to set the databases that i'm going to replicate...
 
  thanks
 
 
 
 Can't do that currently in MySQL.   It is called multi-master
 replication.  You can do multi-slave replication which replicates from
 one master to multiple slaves, but not the other way around.

 Keith


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



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

RE: mysql replication....

[bruce]

ok...

you guys have convinced me!! my.cnf it is!

so, one more question. is there an attribute i can use to run/restart mysql
using a given my.cnf file... i can simply have a number of separate my.cnf
files, and point to them when i run/restart mysql..

/etc/init.d/mysqld --??? myown.cnf

is there an option/attribute for this.

thanks



-Original Message-
From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
Behalf Of Baron Schwartz
Sent: Tuesday, November 27, 2007 6:50 AM
To: bruce
Cc: B. Keith Murphy; mysql list
Subject: Re: mysql replication


You can only do that in the my.cnf file.

On Nov 27, 2007 9:50 AM, bruce [EMAIL PROTECTED] wrote:
 hi keith...

 i recognize you can't do multiple masters to a single slave with mysql's
 replication.

 but you can setup separate mysql slave dbs that are independent, and that
yo
 can then iteratively walk through each slave/master, one at a time, and
then
 do the sync/update for each one... this essentially gets you the
 slave/master replication for each server, replicated to the slave db on
the
 system. the result is a bunch of different slave dbs, instead of a single
 db...

 however, that didn't get me my answer to my question...

 so, how can you do a replicate-do-db from within the mysql cmd???

 in fact, even if i only had a single master, but multiple dbs, i'd still
 like to know this, given that i might not want to use the my.cnf file...

 thanks


 -Original Message-
 From: B. Keith Murphy [mailto:[EMAIL PROTECTED]
 Sent: Tuesday, November 27, 2007 6:31 AM
 To: bruce; 'mysql list'
 Subject: Re: mysql replication


 bruce wrote:
  hi...
 
  a quick question that i haven't found an answer to.
 
  i can use replicate-do-db=foo in a my.cnf file for replication, to
  replicate the master foo db on the slave. but this requires that i
 use/have
  a my.cnf set on the slave.
 
  is there a way to dynamically set this attribute/parameter within mysql
on
  the fly. i thought it would be possible via change master to but
didn't
  find the cmd when looking through the mysql information.
 
  basically, i'm going to have multiple databases, on multiple systems,
that
  i'm going to be replicating to a single system. so, for each master
 server,
  i'd like to be able to set the databases that i'm going to replicate...
 
  thanks
 
 
 
 Can't do that currently in MySQL.   It is called multi-master
 replication.  You can do multi-slave replication which replicates from
 one master to multiple slaves, but not the other way around.

 Keith


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




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

RE: mysql replication....

[bruce]

hi keith...

i recognize you can't do multiple masters to a single slave with mysql's
replication.

but you can setup separate mysql slave dbs that are independent, and that yo
can then iteratively walk through each slave/master, one at a time, and then
do the sync/update for each one... this essentially gets you the
slave/master replication for each server, replicated to the slave db on the
system. the result is a bunch of different slave dbs, instead of a single
db...

however, that didn't get me my answer to my question...

so, how can you do a replicate-do-db from within the mysql cmd???

in fact, even if i only had a single master, but multiple dbs, i'd still
like to know this, given that i might not want to use the my.cnf file...

thanks


-Original Message-
From: B. Keith Murphy [mailto:[EMAIL PROTECTED]
Sent: Tuesday, November 27, 2007 6:31 AM
To: bruce; 'mysql list'
Subject: Re: mysql replication


bruce wrote:
 hi...

 a quick question that i haven't found an answer to.

 i can use replicate-do-db=foo in a my.cnf file for replication, to
 replicate the master foo db on the slave. but this requires that i
use/have
 a my.cnf set on the slave.

 is there a way to dynamically set this attribute/parameter within mysql on
 the fly. i thought it would be possible via change master to but didn't
 find the cmd when looking through the mysql information.

 basically, i'm going to have multiple databases, on multiple systems, that
 i'm going to be replicating to a single system. so, for each master
server,
 i'd like to be able to set the databases that i'm going to replicate...

 thanks



Can't do that currently in MySQL.   It is called multi-master
replication.  You can do multi-slave replication which replicates from
one master to multiple slaves, but not the other way around.

Keith


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

Re: mysql replication....

[Baron Schwartz]

The relevant options are...

[EMAIL PROTECTED] ~ $ mysqld --help --verbose | grep default
--no-defaults   Don't read default options from any options file
--defaults-file=#   Only read default options from the given file #
--defaults-extra-file=# Read this file after the global files are read

I would suggest using the --defaults-extra-file to refer to a defaults
file that has only the settings you want.  But this is roughly
equivalent to restarting with the --replicate-XXX options manually
specified, no?  You might also look into the Instance Manager, which
could give you some more ideas.

I'm being vague because I don't know what I'm talking about :-)

On Nov 27, 2007 10:19 AM, bruce [EMAIL PROTECTED] wrote:
 ok...

 you guys have convinced me!! my.cnf it is!

 so, one more question. is there an attribute i can use to run/restart mysql
 using a given my.cnf file... i can simply have a number of separate my.cnf
 files, and point to them when i run/restart mysql..

 /etc/init.d/mysqld --??? myown.cnf

 is there an option/attribute for this.

 thanks



 -Original Message-
 From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]
 Behalf Of Baron Schwartz
 Sent: Tuesday, November 27, 2007 6:50 AM
 To: bruce
 Cc: B. Keith Murphy; mysql list
 Subject: Re: mysql replication


 You can only do that in the my.cnf file.

 On Nov 27, 2007 9:50 AM, bruce [EMAIL PROTECTED] wrote:
  hi keith...
 
  i recognize you can't do multiple masters to a single slave with mysql's
  replication.
 
  but you can setup separate mysql slave dbs that are independent, and that
 yo
  can then iteratively walk through each slave/master, one at a time, and
 then
  do the sync/update for each one... this essentially gets you the
  slave/master replication for each server, replicated to the slave db on
 the
  system. the result is a bunch of different slave dbs, instead of a single
  db...
 
  however, that didn't get me my answer to my question...
 
  so, how can you do a replicate-do-db from within the mysql cmd???
 
  in fact, even if i only had a single master, but multiple dbs, i'd still
  like to know this, given that i might not want to use the my.cnf file...
 
  thanks
 
 
  -Original Message-
  From: B. Keith Murphy [mailto:[EMAIL PROTECTED]
  Sent: Tuesday, November 27, 2007 6:31 AM
  To: bruce; 'mysql list'
  Subject: Re: mysql replication
 
 
  bruce wrote:
   hi...
  
   a quick question that i haven't found an answer to.
  
   i can use replicate-do-db=foo in a my.cnf file for replication, to
   replicate the master foo db on the slave. but this requires that i
  use/have
   a my.cnf set on the slave.
  
   is there a way to dynamically set this attribute/parameter within mysql
 on
   the fly. i thought it would be possible via change master to but
 didn't
   find the cmd when looking through the mysql information.
  
   basically, i'm going to have multiple databases, on multiple systems,
 that
   i'm going to be replicating to a single system. so, for each master
  server,
   i'd like to be able to set the databases that i'm going to replicate...
  
   thanks
  
  
  
  Can't do that currently in MySQL.   It is called multi-master
  replication.  You can do multi-slave replication which replicates from
  one master to multiple slaves, but not the other way around.
 
  Keith
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
 
 



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

RE: MySQL - Replication (Master/Slave) Question

[Mike Johnson]

From: Dan Rogart [mailto:[EMAIL PROTECTED] 

 On 11/14/07 4:01 PM, Mike Johnson 
 [EMAIL PROTECTED] wrote:
 
  Correction to a couple of replies I've seen -- a slave 
  server can have more than one master, but not to the same 
  database. That is, Slave reads Database1 and Database3 
  from Master1 and also reads Database2 from Master2.
  
  You may actually be able to get down to the table level, 
  but I'd have to check on that. Not likely, though.
  
  As for how to set it all up, don't ask me. I just enjoy 
  the results.   :)
  
  (apologies if you get a dupe, Baron -- I accidentally hit 
  reply, not reply-to-all)
 
 I would be very interested in hearing more about how you set 
 this up, because as far as I know it's impossible for a 
 slave to have more than one master at any given time.
 
 Are you using some kind of time based rotation that changes 
 the master info on the slave periodically or something?

So, I looked into our my.cnf and it turns out that I was wrong. My
apologies.

Where I was misled was that we're doing a sort of pass-through
replication. That is, Server1 replicates Database1 and Database3 to
Server2, and Server2 then replicates Database1, Database2, and Database3
to Server3.

Sorry to have spouted misinformation!

-- 
Mike Johnson

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

MySQL - Replication (Master/Slave) Question

[bruce]

Hi...

I have a number of servers that I want to treat as Master Servers or the
purpose of Replication..

I'd like to have each of the Master, have the Slave DB on the same machine.
Ie, a Slave server, might have 10 different Slave Databases/config files,
with each of the SlaveDB tied back to the Master Server/DB...

However, in looking through various docs, I can only see how to setup a
single Slave connection in the my.cnf file. I can't see how to setup
multiple Slave connections in the Slave Server, to allow it to handle
multiple Masters...

So, my basic question is how/what do I need to do? Can I have multiple
my.cnf files.. Should everything be placed in a single my.cnf file?

Any thoughts/pointers/comments would be helpful!!

thanks



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

Re: MySQL - Replication (Master/Slave) Question

[Baron Schwartz]

bruce wrote:

Hi...

I have a number of servers that I want to treat as Master Servers or the
purpose of Replication..

I'd like to have each of the Master, have the Slave DB on the same machine.
Ie, a Slave server, might have 10 different Slave Databases/config files,
with each of the SlaveDB tied back to the Master Server/DB...

However, in looking through various docs, I can only see how to setup a
single Slave connection in the my.cnf file. I can't see how to setup
multiple Slave connections in the Slave Server, to allow it to handle
multiple Masters...

So, my basic question is how/what do I need to do? Can I have multiple
my.cnf files.. Should everything be placed in a single my.cnf file?


I would advise you to set up a slave with CHANGE MASTER TO instead of 
configuring it in the slave's my.cnf.  It avoids nasty things happening 
when the slave is restarted, etc.  The server will remember information 
about its master in a separate file.


Each slave can have only a single master.  Multiple masters are not 
possible.  (Don't be confused by the terminology: some people say 
multi-master replication when they discuss a setup in which two servers 
are mutually master and slave.  Each slave can have only one master; 
co-master or dual-master or whatever you want to call it is not the same 
as multi-master).


Baron

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

Re: MySQL - Replication (Master/Slave) Question

[Michael Dykman]

On Nov 14, 2007 3:40 PM, bruce [EMAIL PROTECTED] wrote:
 Hi...

 I have a number of servers that I want to treat as Master Servers or the
 purpose of Replication..

 I'd like to have each of the Master, have the Slave DB on the same machine.
 Ie, a Slave server, might have 10 different Slave Databases/config files,
 with each of the SlaveDB tied back to the Master Server/DB...

 However, in looking through various docs, I can only see how to setup a
 single Slave connection in the my.cnf file. I can't see how to setup
 multiple Slave connections in the Slave Server, to allow it to handle
 multiple Masters...

 So, my basic question is how/what do I need to do? Can I have multiple
 my.cnf files.. Should everything be placed in a single my.cnf file?

 Any thoughts/pointers/comments would be helpful!!

 thanks




Typically, each slave is configured to follow only a single master..
mutli-mastering is possible, but it highly complex and not for
beginners .. you really have to know your way around replication
before planning something like that... There are reservations among
the pros as to just how advisable it is.

-- 
 - michael dykman
 - [EMAIL PROTECTED]

 - All models are wrong.  Some models are useful.

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

RE: MySQL - Replication (Master/Slave) Question

[Mike Johnson]

Correction to a couple of replies I've seen -- a slave server can have
more than one master, but not to the same database. That is, Slave reads
Database1 and Database3 from Master1 and also reads Database2 from
Master2.

You may actually be able to get down to the table level, but I'd have to
check on that. Not likely, though.

As for how to set it all up, don't ask me. I just enjoy the results.
:)

(apologies if you get a dupe, Baron -- I accidentally hit reply, not
reply-to-all)

-- 
Mike Johnson

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

Re: MySQL - Replication (Master/Slave) Question

[Dan Rogart]

On 11/14/07 4:01 PM, Mike Johnson [EMAIL PROTECTED] wrote:

 Correction to a couple of replies I've seen -- a slave server can have
 more than one master, but not to the same database. That is, Slave reads
 Database1 and Database3 from Master1 and also reads Database2 from
 Master2.
 
 You may actually be able to get down to the table level, but I'd have to
 check on that. Not likely, though.
 
 As for how to set it all up, don't ask me. I just enjoy the results.
 :)
 
 (apologies if you get a dupe, Baron -- I accidentally hit reply, not
 reply-to-all)

I would be very interested in hearing more about how you set this up,
because as far as I know it's impossible for a slave to have more than one
master at any given time.

Are you using some kind of time based rotation that changes the master info
on the slave periodically or something?

-Dan



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

RE: MySQL - Replication (Master/Slave) Question

[bruce]

hi...

in very basic terms/pics...

i have :

 masterServer1
   masterDB1
   my.cnf
 masterServer2
   masterDB2
   my.cnf
.
.
.
 masterServerN
   masterDBN
   my.cnf

this gets me N masterServers, each with it's own my.cnf file, as well as
it's own masterDB.

I'd like to be able to have the master servers have a slave. Under normal
situations (per docs i've seen) the slave would be on a machine, with a
slaveDB that matches the masterDB in terms of TBLs, and it's own my.conf to
handle the interface between the slave/master.

In my situation, I'd like to be able to simply have all the slave DBs, and
my.conf information on the same box. Given that I can easily have multiple
DBs on a mySQL app, my question appears to come down to how to handle the
my.cnf information. I don't see how I can handle multiple my.cnf files that
are separate, so is there a way to have all the information for the various
slave DBs in the same my.cnf file.

Or would I essentially have to have multiple instances of mySQL running, and
use a different my.cnf for each instance, which would be a pain!!

Or am I tilting at windmills here

If this is at all possible, can I get/see a sample my.cnf file illustrating
how this can be handled



thanks




-Original Message-
From: Dan Rogart [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 14, 2007 1:07 PM
To: Mike Johnson; Baron Schwartz; bruce
Cc: mysql list
Subject: Re: MySQL - Replication (Master/Slave) Question





On 11/14/07 4:01 PM, Mike Johnson [EMAIL PROTECTED] wrote:

 Correction to a couple of replies I've seen -- a slave server can have
 more than one master, but not to the same database. That is, Slave reads
 Database1 and Database3 from Master1 and also reads Database2 from
 Master2.

 You may actually be able to get down to the table level, but I'd have to
 check on that. Not likely, though.

 As for how to set it all up, don't ask me. I just enjoy the results.
 :)

 (apologies if you get a dupe, Baron -- I accidentally hit reply, not
 reply-to-all)

I would be very interested in hearing more about how you set this up,
because as far as I know it's impossible for a slave to have more than one
master at any given time.

Are you using some kind of time based rotation that changes the master info
on the slave periodically or something?

-Dan



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

RE: MySQL - Replication (Master/Slave) Question

[bruce]

Update/Clarification:

 It's apparent that you need a master-slaveDB, and that I can have multiple
slaveDBs on the slave server. But can I setup all the slaveDBs on a single
machine.

Thanks



hi...

in very basic terms/pics...

i have :

 masterServer1
   masterDB1
   my.cnf
 masterServer2
   masterDB2
   my.cnf
.
.
.
 masterServerN
   masterDBN
   my.cnf

this gets me N masterServers, each with it's own my.cnf file, as well as
it's own masterDB.

I'd like to be able to have the master servers have a slave. Under normal
situations (per docs i've seen) the slave would be on a machine, with a
slaveDB that matches the masterDB in terms of TBLs, and it's own my.conf to
handle the interface between the slave/master.

In my situation, I'd like to be able to simply have all the slave DBs, and
my.conf information on the same box. Given that I can easily have multiple
DBs on a mySQL app, my question appears to come down to how to handle the
my.cnf information. I don't see how I can handle multiple my.cnf files that
are separate, so is there a way to have all the information for the various
slave DBs in the same my.cnf file.

Or would I essentially have to have multiple instances of mySQL running, and
use a different my.cnf for each instance, which would be a pain!!

Or am I tilting at windmills here

If this is at all possible, can I get/see a sample my.cnf file illustrating
how this can be handled



thanks




-Original Message-
From: Dan Rogart [mailto:[EMAIL PROTECTED]
Sent: Wednesday, November 14, 2007 1:07 PM
To: Mike Johnson; Baron Schwartz; bruce
Cc: mysql list
Subject: Re: MySQL - Replication (Master/Slave) Question





On 11/14/07 4:01 PM, Mike Johnson [EMAIL PROTECTED] wrote:

 Correction to a couple of replies I've seen -- a slave server can have
 more than one master, but not to the same database. That is, Slave reads
 Database1 and Database3 from Master1 and also reads Database2 from
 Master2.

 You may actually be able to get down to the table level, but I'd have to
 check on that. Not likely, though.

 As for how to set it all up, don't ask me. I just enjoy the results.
 :)

 (apologies if you get a dupe, Baron -- I accidentally hit reply, not
 reply-to-all)

I would be very interested in hearing more about how you set this up,
because as far as I know it's impossible for a slave to have more than one
master at any given time.

Are you using some kind of time based rotation that changes the master info
on the slave periodically or something?

-Dan



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

RE: MySQL Replication Binary Logs - How Long to Keep?

[mark addison]

On Sat, 2006-08-12 at 08:38 -0400, Warren Crigger wrote:
  Note that you should not just delete the bin logs. Instead 
  use PURGE MASTER LOGS. See 
  http://dev.mysql.com/doc/refman/4.1/en/purge-master-logs.html
  
  hth,
  mark
 
 
 Sorry, accidently hit Ctrl/Enter :(
 
 Anyway, I can't purge with that command:
 
 mysql PURGE MASTER LOGS TO 'mysql-bin.023';
 ERROR: 
 A purgeable log is in use, will not purge
 
 Any ideas?  I'm tempted to just delete but would prefer to do this the right
 way, and for some reason it thinks they are in use :/.  I'm showing:
 
 
 mysql show master status; 
 +--+---+--+--+
 | File | Position  | Binlog_do_db | Binlog_ignore_db |
 +--+---+--+--+
 | repl.024 | 110962544 |  |  |
 +--+---+--+--+
 1 row in set (0.00 sec)

That File column looks wrong, the name should match your setting for the
name of the binary log e.g. 'mysql-bin.023'. 'repl' looks like the name
of a relay log, which is what slaves use to update them selves.
Can you send the output of SHOW MASTER STATUS; and SHOW SLAVE STATUS;
for both boxes?
Note if you use \G for the slave on the mysql command line the output is
much easy to read. e.g.
mysql SHOW SLAVE STATUS\G 

Also you might find running SHOW PROCESSLIST; on the servers usefull, if
the bin log is in use you should be able to see the replication
processes using it.

cheers,
mark
--
 





MARK ADDISON
WEB DEVELOPER

200 GRAY'S INN ROAD
LONDON
WC1X 8XZ
UNITED KINGDOM
T +44 (0)20 7430 4678
F 
E [EMAIL PROTECTED]
WWW.ITN.CO.UK
Please Note:

 

Any views or opinions are solely those of the author and do not necessarily 
represent 
those of Independent Television News Limited unless specifically stated. 
This email and any files attached are confidential and intended solely for the 
use of the individual
or entity to which they are addressed. 
If you have received this email in error, please notify [EMAIL PROTECTED] 

Please note that to ensure regulatory compliance and for the protection of our 
clients and business,
we may monitor and read messages sent to and from our systems.

Thank You.


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