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]

Yes that's correct.
it needs to be added on the slave side under [mysqld] section usually 
close to the place where you define  your replication settings (for 
convenience only).

The reason why server failed to start difficult to guess without error log.

this will prevent all tables under parallax db with watchdog bit in the 
name to be replicated.


another option
*|replicate-ignore-table=/|parallax.watchdog|/| 
http://dev.mysql.com/doc/refman/5.1/en/replication-options-slave.html#option_mysqld_replicate-ignore-table* 



and restart server








On 07/12/12 00:56, Néstor wrote:

I added those line to the slave's my.cnf and mysql would not start
replicate-wild-ignore-table=parallax%.watchdog%
replicate-wild-ignore-table=parallax%.cache%

Nestor


On Thu, Dec 6, 2012 at 4:39 PM, Néstor rot...@gmail.com wrote:


HI Igor,

Are you saying to add these lines to the my.cnf file:
replicate-wild-ignore-table=dbname%.watchdog%
replicate-wild-ignore-table=dbname%.cache%

Correct?

Thanks,

Nestor :-)



On Thu, Dec 6, 2012 at 3:07 PM, Igor Shevtsov nixofort...@gmail.comwrote:


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.pnghttp://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/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
   

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 a 

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]

I added those line to the slave's my.cnf and mysql would not start
replicate-wild-ignore-table=parallax%.watchdog%
replicate-wild-ignore-table=parallax%.cache%

Nestor


On Thu, Dec 6, 2012 at 4:39 PM, Néstor rot...@gmail.com wrote:

 HI Igor,

 Are you saying to add these lines to the my.cnf file:
 replicate-wild-ignore-table=dbname%.watchdog%
 replicate-wild-ignore-table=dbname%.cache%

 Correct?

 Thanks,

 Nestor :-)



 On Thu, Dec 6, 2012 at 3:07 PM, Igor Shevtsov nixofort...@gmail.comwrote:

 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.pnghttp://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/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 

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

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

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

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

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

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

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

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 

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

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

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]

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]

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]

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]

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

[Warren Crigger]

 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)

Thanks,
Warren


-- 
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 Thu, 2006-08-10 at 14:47 +1000, Logan, David (SST - Adelaide) wrote:
 Hi Warren,
 
 I would generally keep the binary log until you have done a full backup.
 Once that is done, all the transactions that are contained in those logs
 are now committed and saved in your database backup.
snip

You also need to keep any binary logs until all slaves to that server
have finished with them. You can find that out by running 
 SHOW SLAVE STATUS;  # on the slave(s) look for Master_Log_File
 SHOW MASTER STATUS; # on the master
and comparing the binary log filenames. You can delete any numbered less
than the lowest numbered log listed in the slave status output.

I found that when I set this up by default mysql didn't create a new bin
log until the old one was over a gig, which meant it couldn't be cleared
up for weeks (not a busy database). So I set the max_binlog_size option
in my.cnf to a 100 meg so I could clean up more often.

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

 
 





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

-Original Message-

 From: Warren Crigger [mailto:[EMAIL PROTECTED] 
 Sent: Thursday, 10 August 2006 9:52 AM
 To: mysql@lists.mysql.com
 Cc: [EMAIL PROTECTED]
 Subject: MySQL Replication Binary Logs - How Long to Keep?
 
 I've just recently set up MySQL replication amongst two servers so I'm
 not
 too familiar with it.  I was cleaning up my /var filesystem and found
 the
 binary data below being stored in /var/lib/mysql, taking up 1.5gb.  
 
 I did a little reading on mysql.org docs.  My interpretation was that
 you
 can have it replicate every so often, then you can purge these after
 that
 happens..however, my replication is instantaneous.  I can insert a
 record on
 the master and then go select it on the slave immediately.  Is there any
 reason to keep this data?  It's replicated to the 2nd server, in
 addition to
 dumps of the more important databases nightly, and dumps of the entire
 server weekly, which go to different physical drives and then ultimately
 off-site.  I like redudancy obviously, however, this seems pretty
 useless to
 me.
 
 If it is of use, is there a way I can put this on a different
 filesystem,
 keeping the databases themselves within /var/lib/mysql?
 
 Thanks,
 Warren
 
 [EMAIL PROTECTED] mysql]# pwd
 /var/lib/mysql
 [EMAIL PROTECTED] mysql]# ls -lart |grep repl
 -rw-rw1 mysqlmysql 3088434 Jun 11 04:02 repl.001
 -rw-rw1 mysqlmysql 107 Jun 11 04:02 repl.002
 -rw-rw1 mysqlmysql188387006 Jun 18 04:02 repl.003
 -rw-rw1 mysqlmysql 107 Jun 18 04:02 repl.004
 -rw-rw1 mysqlmysql 107 Jun 25 04:02 repl.006
 -rw-rw1 mysqlmysql156749380 Jun 25 04:02 repl.005
 -rw-rw1 mysqlmysql153489679 Jul  2 04:02 repl.007
 -rw-rw1 mysqlmysql 107 Jul  2 04:02 repl.008
 -rw-rw1 mysqlmysql 107 Jul  9 04:02 repl.010
 -rw-rw1 mysqlmysql140922795 Jul  9 04:02 repl.009
 -rw-rw1 mysqlmysql58638790 Jul 11 17:30 repl.011
 -rw-rw1 mysqlmysql   38410 Jul 11 17:46 repl.012
 -rw-rw1 mysqlmysql 5927431 Jul 12 09:00 repl.013
 -rw-rw1 mysqlmysql80007235 Jul 16 04:02 repl.014
 -rw-rw1 mysqlmysql 107 Jul 16 04:02 repl.015
 -rw-rw1 mysqlmysql 107 Jul 23 04:02 repl.017
 -rw-rw1 mysqlmysql155468996 Jul 23 04:02 repl.016
 -rw-rw1 mysqlmysql 107 Jul 30 04:02 repl.019
 -rw-rw1 mysqlmysql155806419 Jul 30 04:02 repl.018
 -rw-rw1 mysqlmysql 107 Aug  6 04:02 repl.021
 -rw-rw1 mysqlmysql159420166 Aug  6 04:02 repl.020
 -rw-rw1 mysqlmysql 6366383 Aug  6 13:33 repl.022
 -rw-rw1 mysqlmysql 1138297 Aug  6 17:36 repl.023
 -rw-rw1 mysqlmysql 264 Aug  6 17:40 repl.index
 -rw-rw1 mysqlmysql43014905 Aug  9 00:03 repl.024
 

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]

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

[Logan, David (SST - Adelaide)]

Hi Warren,

I would generally keep the binary log until you have done a full backup.
Once that is done, all the transactions that are contained in those logs
are now committed and saved in your database backup.

All binary logs may be deleted that were created before that backup took
place. The main reason for this is that it allows you to restore from
your last backup and then roll forward all transactions (I use the
term to describe anything modifying the database and logged in the
binary log) until the database is back up to date.

Regards


---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: Warren Crigger [mailto:[EMAIL PROTECTED] 
Sent: Thursday, 10 August 2006 9:52 AM
To: mysql@lists.mysql.com
Cc: [EMAIL PROTECTED]
Subject: MySQL Replication Binary Logs - How Long to Keep?

I've just recently set up MySQL replication amongst two servers so I'm
not
too familiar with it.  I was cleaning up my /var filesystem and found
the
binary data below being stored in /var/lib/mysql, taking up 1.5gb.  

I did a little reading on mysql.org docs.  My interpretation was that
you
can have it replicate every so often, then you can purge these after
that
happens..however, my replication is instantaneous.  I can insert a
record on
the master and then go select it on the slave immediately.  Is there any
reason to keep this data?  It's replicated to the 2nd server, in
addition to
dumps of the more important databases nightly, and dumps of the entire
server weekly, which go to different physical drives and then ultimately
off-site.  I like redudancy obviously, however, this seems pretty
useless to
me.

If it is of use, is there a way I can put this on a different
filesystem,
keeping the databases themselves within /var/lib/mysql?

Thanks,
Warren

[EMAIL PROTECTED] mysql]# pwd
/var/lib/mysql
[EMAIL PROTECTED] mysql]# ls -lart |grep repl
-rw-rw1 mysqlmysql 3088434 Jun 11 04:02 repl.001
-rw-rw1 mysqlmysql 107 Jun 11 04:02 repl.002
-rw-rw1 mysqlmysql188387006 Jun 18 04:02 repl.003
-rw-rw1 mysqlmysql 107 Jun 18 04:02 repl.004
-rw-rw1 mysqlmysql 107 Jun 25 04:02 repl.006
-rw-rw1 mysqlmysql156749380 Jun 25 04:02 repl.005
-rw-rw1 mysqlmysql153489679 Jul  2 04:02 repl.007
-rw-rw1 mysqlmysql 107 Jul  2 04:02 repl.008
-rw-rw1 mysqlmysql 107 Jul  9 04:02 repl.010
-rw-rw1 mysqlmysql140922795 Jul  9 04:02 repl.009
-rw-rw1 mysqlmysql58638790 Jul 11 17:30 repl.011
-rw-rw1 mysqlmysql   38410 Jul 11 17:46 repl.012
-rw-rw1 mysqlmysql 5927431 Jul 12 09:00 repl.013
-rw-rw1 mysqlmysql80007235 Jul 16 04:02 repl.014
-rw-rw1 mysqlmysql 107 Jul 16 04:02 repl.015
-rw-rw1 mysqlmysql 107 Jul 23 04:02 repl.017
-rw-rw1 mysqlmysql155468996 Jul 23 04:02 repl.016
-rw-rw1 mysqlmysql 107 Jul 30 04:02 repl.019
-rw-rw1 mysqlmysql155806419 Jul 30 04:02 repl.018
-rw-rw1 mysqlmysql 107 Aug  6 04:02 repl.021
-rw-rw1 mysqlmysql159420166 Aug  6 04:02 repl.020
-rw-rw1 mysqlmysql 6366383 Aug  6 13:33 repl.022
-rw-rw1 mysqlmysql 1138297 Aug  6 17:36 repl.023
-rw-rw1 mysqlmysql 264 Aug  6 17:40 repl.index
-rw-rw1 mysqlmysql43014905 Aug  9 00:03 repl.024


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

Re: MySQL Replication

[Atle Veka]

To add a few short notes:

1) What happens when you modify data on the slave directly depends on how
you configure your setup. It is possible to have slave updates appear on
the master, that is usually referred to as circular replication. Since you
have 2 replication slaves I would advise against using circular
replication (1 - 2 - 3 - 1). I would also highly recommend you use the
'read-only' flag on your slave to prevent accidental updates which would
break data consistency.

2) In a setup like this, I would recommend that you consistently name your
database handles appropriately in your code, say $dbh_write and
$dbh_readonly.


Atle
-
Flying Crocodile Inc, Unix Systems Administrator

On Fri, 6 Jan 2006, Jason Williard wrote:

 I am trying to understand exactly how replication works.  So far, I see that
 changes made on a master server are replicated to the slave server(s).
 However, if a change is made on a slave server, is that replicated back to
 the master as well as all other slaves?


 I am asking this question as I try to develop a plan for more efficient web
 servers.  Here is what I am planning.  Please let me know if this sounds
 smart, or like a bad idea.

 Server 1: Redhat MySQL Master
 Servers 2  3: Load-Balanced Redhat Apache web servers w/MySQL Slaves

 Servers 2  3 will be serving the same content and will need access to the
 same data from the MySQL server(s).  I am hoping that running MySQL on each
 of the web servers will help to reduce the overall load on the servers.
  

 
 Thank You,
 Jason Williard
  





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

Re: MySQL Replication

[sheeri kritzer]

To be clear:

Replication in MySQL replicates the DML (data manipulation language)
and DDL (data definition language) commands -- that is, any command
that's an alter, update, insert, replace, create, drop, etc statement
to the slave.

If you write to the slave, it does not write back to the master.  If
you change the data on the slave server, replication from the master
to the slave will happily continue (hopefully, unless you've changed
something that would cause a duplicate key error or something) because
it only sends the commands over.

There's no way for the master to tell that the slave has changed. 
There is also no easy way to take a master and a slave and ensure that
they do, indeed, have the same data.

-Sheeri

On 1/6/06, Jason Williard [EMAIL PROTECTED] wrote:
 I am trying to understand exactly how replication works.  So far, I see that
 changes made on a master server are replicated to the slave server(s).
 However, if a change is made on a slave server, is that replicated back to
 the master as well as all other slaves?


 I am asking this question as I try to develop a plan for more efficient web
 servers.  Here is what I am planning.  Please let me know if this sounds
 smart, or like a bad idea.

 Server 1: Redhat MySQL Master
 Servers 2  3: Load-Balanced Redhat Apache web servers w/MySQL Slaves

 Servers 2  3 will be serving the same content and will need access to the
 same data from the MySQL server(s).  I am hoping that running MySQL on each
 of the web servers will help to reduce the overall load on the servers.


 
 Thank You,
 Jason Williard




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

[Logan, David (SST - Adelaide)]

Hi Jason,

Most other peoples responses are excellent as usual, however might I suggest 
getting a copy of High Performance MySQL by Jeremy Zawodny (O'Reilly 
publishers). This covers the exact scenario you are talking about.

Regards


---
** _/ **  David Logan 
***   _/ ***  ITO Delivery Specialist - Database
*_/*  Hewlett-Packard Australia Ltd
_/_/_/  _/_/_/    E-Mail: [EMAIL PROTECTED]
   _/  _/  _/  _/     Desk:   +618 8408 4273
  _/  _/  _/_/_/  Mobile: 0417 268 665
*_/   **
**  _/    Postal: 148 Frome Street,
   _/ **  Adelaide SA 5001
  Australia 
invent   
---

-Original Message-
From: Jason Williard [mailto:[EMAIL PROTECTED] 
Sent: Saturday, 7 January 2006 9:32 AM
To: mysql@lists.mysql.com
Subject: MySQL Replication

I am trying to understand exactly how replication works.  So far, I see that
changes made on a master server are replicated to the slave server(s).
However, if a change is made on a slave server, is that replicated back to
the master as well as all other slaves?


I am asking this question as I try to develop a plan for more efficient web
servers.  Here is what I am planning.  Please let me know if this sounds
smart, or like a bad idea.

Server 1: Redhat MySQL Master
Servers 2  3: Load-Balanced Redhat Apache web servers w/MySQL Slaves

Servers 2  3 will be serving the same content and will need access to the
same data from the MySQL server(s).  I am hoping that running MySQL on each
of the web servers will help to reduce the overall load on the servers.
 


Thank You,
Jason Williard
 



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

[Sid Lane]

I'll 2nd that High Performance MySQL.

it is by far the best MySQL book I've come across (though I didn't need the
101 stuff, I specifically needed tuning/architecting for HA, etc.)

the only knock I could make (which isn't their fault) is that it needs to be
updated for 5.x (can you say 2nd edition?).

Re: MySQL Replication

[Chander Ganesan]

Jason Williard wrote:


I am trying to understand exactly how replication works.  So far, I see that
changes made on a master server are replicated to the slave server(s).
However, if a change is made on a slave server, is that replicated back to
the master as well as all other slaves?

 

It can be if you enable circular replication (A--B--A) .  However, 
keep in mind that there is no way to prevent simultaneous updates (i.e., 
locking a table on A for update won't apply a lock to B..)



I am asking this question as I try to develop a plan for more efficient web
servers.  Here is what I am planning.  Please let me know if this sounds
smart, or like a bad idea.

Server 1: Redhat MySQL Master
Servers 2  3: Load-Balanced Redhat Apache web servers w/MySQL Slaves

Servers 2  3 will be serving the same content and will need access to the
same data from the MySQL server(s).  I am hoping that running MySQL on each
of the web servers will help to reduce the overall load on the servers.
 

Are there update operations occuring on servers 2 and 3?  What's the 
bulk of your operations - selects or updates?  There are a couple of 
ways to go with this.  You can use clustering - in which case you can 
apply changes to the cluster and everything should stay in sync, or 
(the more straightfoward way) you can simply have all write operations 
performed on the master - use the slave servers for lookup only.


If you use the second option (this assumes that the bulk of your 
operations are 'select' statements)  you might be able to squeeze out 
some more performance by having the replica servers use a MyISAM table 
type (Assuming you use InnoDB on the master).


If needed you can design a circular replication scheme - and have one of 
the slaves switch to a master role when the master is unavailable.


--
Chander Ganesan
Open Technology Group, Inc.
One Copley Parkway, Suite 210
Morrisville, NC  27560
http://www.otg-nc.com
Phone: 877-258-8987/919-463-0999
Fax: 919-386-0158


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

Re: MySQL Replication

[Gleb Paharenko]

Hello.



Not enough information to make a conclusion. Use SHOW SLAVE STATUS and

information from the binary logs to determine the problem. See:



  http://dev.mysql.com/doc/mysql/en/replication-problems.html









David Lloyd [EMAIL PROTECTED] wrote:

 

 Hi There,

 

 

 I have a replication setup on my local network (so any updates can be 

 transported at around ethernet speed).

 

 Here's the behaviour I see:

 

 * MySQL Master

 

 - I do a whole slew of drop table and create tables

 

 * MySQL Slave

 

 - It doesn't pick them up

 

 ... until ...

 

 - I restart the slave

 

 It doesn't appear to have a problem with a single database table being 

 dropped, only when I drop a whole heap at once [I'm replacing the 

 underlying scheme with a heap of drop table ifs followed by create table].

 

 I'm running the official mysql-4.1.10 Apple binaries. One on my OS X 

 server [which is the master] and another on OS X [not server] [which is 

 the slave].

 

 Anywhere I can work out what might be happening or why the updates are 

 being sent through?

 

 DSL

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Re: mysql replication with one database

[Gleb Paharenko]

Hello.



Use --replicate-do-db=db_name

See:

  http://dev.mysql.com/doc/mysql/en/replication-options.html





[snip]



I have two mysql server A,B. I'm using the replication between these servers. 
But i want

to the replication work with just one database.

Example:



A server:  database1, database2, database3

B server:  database1, database4, database5



The A.database1 and B.database1 is same!

I want replicate only this database1! 



[snip]Zoltan Gyurasits [EMAIL PROTECTED] wrote:



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Re: MySQL replication

[Gleb Paharenko]

Hello.



See:

  http://dev.mysql.com/doc/mysql/en/Replication_Options.html



Dave Goodrich [EMAIL PROTECTED] wrote:

 Good morning,

 

 Been reading through the docs and checking online info and I m still 

 looking for a answer. I have a radius DB on two radius servers and I 

 want to sync them via a master server. Seems easy enough, but I have one 

 table which holds accounting data. How long a user has been online, when 

 they logged on, when they logged off. This data is sent to the slave 

 servers by the auth equipment.

 

 Is it possible to only replicate a *table* to a slave and not the entire DB?

 

 Thanks,

 

 DAve

 



-- 
For technical support contracts, goto https://order.mysql.com/?ref=ensita
This email is sponsored by Ensita.NET http://www.ensita.net/
   __  ___ ___   __
  /  |/  /_ __/ __/ __ \/ /Gleb Paharenko
 / /|_/ / // /\ \/ /_/ / /__   [EMAIL PROTECTED]
/_/  /_/\_, /___/\___\_\___/   MySQL AB / Ensita.NET
   ___/   www.mysql.com




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

Re: MySQL replication

[Dave Goodrich]

Ahh, thanks and to Bill Alliar as well. I just needed to re-read 
everything, I think that answers my questions. This full situation is this.

On each slave I have a database, radius, which holds auth info and 
accounting info for each user. I want to limit the accounting info on 
the slave servers as the tables grow very large, very fast. I would like 
to move the accounting info over a week old onto the master server for 
historical use. But I do not want to update the auth info on the slave 
servers, it would be better to have a single point to update, delete, 
insert auth info such as the master.

In a nutshell I want,
MASTER.authinfo - SLAVE.authinfo
SLAVE.accounting - MASTER.accounting
delete SLAVE.accounting
I believe the best solution would be to replicate auth info from the 
master to the slaves using,

--replicate-do-db=radius
--replicate-ignore-table=radius.radacct
This would replicate only the radius db, and ignore the radius.radacct 
table correct? (there are hundreds of DB on this master)

Then I can move the accounting data back to the master by running,
[on the slave]
SELECT * INTO OUTFILE '/file/path' FROM radius.radacct (records to keep)
DELETE FROM radius.radacct WHERE (records to DELETE)
[on the master]
(ftp the outfile from slave to master)
LOAD DATA LOCAL INFILE '/file/path' REPLACE INTO TABLE radius.radacct
Does this sound right or am I making this too complicated?
Thanks.
DAve


Gleb Paharenko wrote:
Hello.
See:
  http://dev.mysql.com/doc/mysql/en/Replication_Options.html
Dave Goodrich [EMAIL PROTECTED] wrote:
Good morning,
Been reading through the docs and checking online info and I m still 
looking for a answer. I have a radius DB on two radius servers and I 
want to sync them via a master server. Seems easy enough, but I have one 
table which holds accounting data. How long a user has been online, when 
they logged on, when they logged off. This data is sent to the slave 
servers by the auth equipment.
Is it possible to only replicate a *table* to a slave and not the entire DB?
Thanks,
DAve
--
Systems Administrator
http://www.tls.net
Get rid of Unwanted Emails...get TLS Spam Blocker!
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Re: [mysql] replication of database structure changes

[Dan Nelson]

In the last episode (May 04), Jim said:
 Do changes in database structure replicate to the slaves from the
 master?  Is there a document somewhere in the manual (I have not
 found one) that explains what gets replicated and what does not? 
 Specifically, does an ALTER TABLE get replicated?  There is some
 mention that replication is for data and not for structure, but a
 formal explanation of what exactly that means is hard to find.

All commands that modify data, including CREATE|DROP TABLE|DATABASE,
replicated.

-- 
Dan Nelson
[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 scheme - DMZ - LAN

[Sasha Pachev]

Fabbro Alberto wrote:
Network configuration:

- Firewall connected to Internet, with two ports: LAN (protected) and DMZ
- Web server on the DMZ segment
- Internal Servers on the LAN segment.
We would like to introduce a web database application on the Web server,
using a Slave replication of a Master database running on the LAN Server.
We have two kinds of problems:

- For running replication we need to open ports on the LAN segment of the
firewall to let the Slave read binary logs.
- If we want web users to change information we need to operate on the
Master Database (and open other ports).
Is there another kind of replication schema that keep security of the LAN
segment ?
Alberto:

Perhaps a better question to ask is - is there a different kind of topology that 
will allow you replicate securely and meet your other goals?

E.g - keep both the master and the slave on the secure LAN, and open the MySQL 
port on the master to only to your web server in the DMZ.

--
Sasha Pachev
Create online surveys at http://www.surveyz.com/
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

RE: MySQL Replication

[Ian Neubert]

Thats a good quote on your site, I agree.

To sum things up I've changed my design and basically am going to implement
the last Q of http://www.mysql.com/doc/en/Replication_FAQ.html.

Thanks for the info everyone.

PS. Jeremy hows your book coming? I just bought a bunch of MySQL books, and
can't wait to get yours.

...
Ian Neubert
Director of IS
TWAcomm.com, Inc.
http://www.twacomm.com/

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Monday, August 11, 2003 12:39 PM
To: Adam Nelson
Cc: 'Ian Neubert'; [EMAIL PROTECTED]
Subject: Re: MySQL Replication


On Fri, Aug 08, 2003 at 12:10:18PM -0400, Adam Nelson wrote:
 Also, one has to work out the cost of high availability.  If you're
 talking about a situation where you reduce downtime from 4 hours/yr to
 .5 hours/yr and it costs you x dollars, you have to make sure that the
 extra 3.5 hours of downtime would cost more than that much money.

Agreed.

In fact, this has come up before...

  http://jeremy.zawodny.com/blog/archives/000805.html

Jeremy
--
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 10 days, processed 336,775,492 queries (387/sec. avg)


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

Re: MySQL Replication

[Jeremy Zawodny]

On Thu, Aug 07, 2003 at 01:00:12PM -0700, Ian Neubert wrote:
 I was trying to design it so that the slaves wouldn't know they had
 connected to a different master, as they both masters would have the same IP
 address that gets failed over based on the Linux Virtual Server software and
 VRRP (like heartbeat from Linux-HA).

That path is a very, very, very difficult one.

How can you absolutely guarantee that each master's binlog will be
indentical in name, size, and content?

If you can't, this scenario really falls apart.

(I've suggested enhancements to MySQL that would fix this but don't
know if they're terribly high on the priority list...)

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 6 days, processed 212,501,412 queries (399/sec. avg)

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

RE: MySQL Replication

[Ian Neubert]

Good question :)

I got a message from a person off the list that suggested I use network disk
mirroring or a NAS/SAN/NFS system to handle that. I'm not sure if the
mirroring would be 100% perfect, but the NAS/SAN solution should as either
server would be reading and writing to the same physical data.

But, then I have another point of failure. Heh.

I realize that creating the perfect HA system is probably the most difficult
thing to do, and doesn't come cheaply either. However, I'm going to think it
through and try anyway :)

I've read your presentations on your website and have used that info for my
plan here, but its a little difficult to get details from just the slides
(as you even mentioned on your site) :)

Do you bother with multi-masters? How do you ensure redundancy on the
write/master server?

...
Ian Neubert
Director of IS
TWAcomm.com, Inc.
http://www.twacomm.com/

-Original Message-
From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 2:53 PM
To: Ian Neubert
Cc: Dathan Vance Pattishall; [EMAIL PROTECTED]
Subject: Re: MySQL Replication


On Thu, Aug 07, 2003 at 01:00:12PM -0700, Ian Neubert wrote:
 I was trying to design it so that the slaves wouldn't know they had
 connected to a different master, as they both masters would have the same
IP
 address that gets failed over based on the Linux Virtual Server software
and
 VRRP (like heartbeat from Linux-HA).

That path is a very, very, very difficult one.

How can you absolutely guarantee that each master's binlog will be
indentical in name, size, and content?

If you can't, this scenario really falls apart.

(I've suggested enhancements to MySQL that would fix this but don't
know if they're terribly high on the priority list...)

Jeremy
--
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 6 days, processed 212,501,412 queries (399/sec. avg)

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

[Ian Neubert]

Thanks Dathan! I really appreciate your comments and suggestions, they've
been very helpful.

I updated my drawings with your info. Thanks again!

http://linux.ianneubert.com/images/mysql_failover.png or
http://linux.ianneubert.com/images/mysql_failover.pdf

...
Ian Neubert
Director of IS
TWAcomm.com, Inc.
http://www.twacomm.com/

-Original Message-
From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 1:34 PM
To: 'Ian Neubert'
Cc: [EMAIL PROTECTED]
Subject: RE: MySQL Replication




---Original Message-
--From: Ian Neubert [mailto:[EMAIL PROTECTED]
--Sent: Thursday, August 07, 2003 1:00 PM
--To: Dathan Vance Pattishall
--Cc: [EMAIL PROTECTED]
--Subject: RE: MySQL Replication
--

--Off hand do you know any good resources on how to setup a highly
--available
--and load balanced MySQL system? Setting up replication is easy
enough,

Highly available systems work generally in your design but here are some
suggestions based on some things I've been doing.

1) Configure the 2 masters to share the same IP with VRRP.
2) The sub master or fail-over master needs to have the same data and
same bin-log so CHANGE MASTER TO can be avoided on the slaves where a
auto reconnect can reliably pick up from where they left off.
 - Suggestions mirror over the network or write the bin logs on a shared
NFS device (another point of failure)
3) Software need to monitor the master / sub-master to fail over and
keep the primary master down since fail back should be done manually.
This is due to order of events such as repair etc -what happens 1st.

There are a few steps I'm forgetting but this should work.






--but I
--can't find much data about getting the whole thing to work together
so to
--speak (highly available writes).
--
--Thanks!
--
--...
--Ian Neubert
--Director of IS
--TWAcomm.com, Inc.
--http://www.twacomm.com/
--
---Original Message-
--From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
--Sent: Thursday, August 07, 2003 12:54 PM
--To: 'Ian Neubert'; [EMAIL PROTECTED]
--Subject: RE: MySQL Replication
--
--
--That configuration doesn't seem to work if I'm reading it right. A
slave
--cannot have more then one master unless mysql more then 1 mysqld
process
--running on different ports is used.
--
For example, if a person places an order on our site, the update
is
--sent
to
the master server. Can that update be replicated to the slaves
before
--the
browser is taken to a reciept page (which is queried against the
--slaves)?
--
--Also guaranteeing an event has occurred on a slave and building
--application logic dependent on this necessity is a tough problem to
--solve and you might as well query the master since you need to verify
--the event is there anyway as part of the conditional.
--
--The reason for my last statement, I've notice in 3.23.5x that
--replication lag can occur in many situations:
--- The master is loaded and is not able to stream the binary log file
as
--fast.
--- Locks on the slave preventing updates to occur
--- Load on the slave is high
--- Invalid packet errors where the slave needs to reconnect
--- Some sort of error that stops replication
--
--
--With all of the above examples, I would not have application logic
--dependent on slave events. Instead code your applications based on
the
--known limitations.
--
--For example a search engine. It does not necessarily need an up to
date
--all of the time index of words and matches...
--
--

--Dathan
--
--
--
-Original Message-
From: Ian Neubert [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 11:27 AM
To: [EMAIL PROTECTED]
Subject: MySQL Replication

Hello all,

Can anyone share their experience with replication? In particular
I'm
wondering how fast (or how slow?) replication occurs. Could I pull
--off
the
configuration below?


http://linux.ianneubert.com/images/mysql_failover.png or
http://linux.ianneubert.com/images/mysql_failover.pdf

Thanks for your insight!

...
Ian Neubert
Director of IS
TWAcomm.com, Inc.
http://www.twacomm.com/


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





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

RE: MySQL Replication

[Dathan Vance Pattishall]

Ack bad English in the 1st sentence
I meant a slave server cannot have more then one master unless more then
one mysqld processes is running on different ports i.e. port 3306 and
port 3307.

---Original Message-
--From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
--Sent: Thursday, August 07, 2003 12:54 PM
--To: 'Ian Neubert'; [EMAIL PROTECTED]
--Subject: RE: MySQL Replication
--
--That configuration doesn't seem to work if I'm reading it right. A
slave
--cannot have more then one master unless mysql more then 1 mysqld
process
--running on different ports is used.
--
For example, if a person places an order on our site, the update
is
--sent
to
the master server. Can that update be replicated to the slaves
before
--the
browser is taken to a reciept page (which is queried against the
--slaves)?
--
--Also guaranteeing an event has occurred on a slave and building
--application logic dependent on this necessity is a tough problem to
--solve and you might as well query the master since you need to verify
--the event is there anyway as part of the conditional.
--
--The reason for my last statement, I've notice in 3.23.5x that
--replication lag can occur in many situations:
--- The master is loaded and is not able to stream the binary log file
as
--fast.
--- Locks on the slave preventing updates to occur
--- Load on the slave is high
--- Invalid packet errors where the slave needs to reconnect
--- Some sort of error that stops replication
--
--
--With all of the above examples, I would not have application logic
--dependent on slave events. Instead code your applications based on
the
--known limitations.
--
--For example a search engine. It does not necessarily need an up to
date
--all of the time index of words and matches...
--
--

--Dathan
--
--
--
-Original Message-
From: Ian Neubert [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 11:27 AM
To: [EMAIL PROTECTED]
Subject: MySQL Replication

Hello all,

Can anyone share their experience with replication? In particular
I'm
wondering how fast (or how slow?) replication occurs. Could I pull
--off
the
configuration below?


http://linux.ianneubert.com/images/mysql_failover.png or
http://linux.ianneubert.com/images/mysql_failover.pdf

Thanks for your insight!

...
Ian Neubert
Director of IS
TWAcomm.com, Inc.
http://www.twacomm.com/


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




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

Re: MySQL Replication

[yin]

You can use the default *.cnf file which is located at  /usr/share/mysql.
There are several *.cnf files. You can choose one to suite your machine
configuration.

~~yin~~
- Original Message -
From: System [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Monday, August 11, 2003 9:22 PM
Subject: MySQL Replication


 Hello All,

 I want to setup mysql replication between two hosts Redhat 7.3 and 9.0.
 I have MySQL 4.0.13 on both the systems running.I am following a HOWTO
from

http://docsrv.caldera.com:8457/cgi-bin/info2html?(mysql)Replication%2520HOWT
 O
 My Question is It says to edit my.cnf on Master and slave but i dont have
 my.cnf in the current versions of MYSQL.It used to be in the MySQL-3.23.x
 and earlier. Creating the file manually in /etc/my.cnf will work?

 Any help will be appreciated.

 Thank You,
 Tina.




 --
 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 through a tunnel.

[colbey]

Consider using freeswan (http://www.freeswan.ca) to setup a VPN between
the 2 servers.. that way you can replicate between tunnel addresses..

Or you can spend some cash and buy some vpn appliances..



On Thu, 7 Aug 2003, System wrote:

 Hello All,

 How will i setup Mysql Replication btween two redhat 7.3/9.0 boxes. I want
 the replication to happen through a Secure tunnel between these two. I am
 following the steps that is given on the offcial Mysql website. Just wants
 to know if there is any easy howto to set this up using a Tunnel.

 Any comments will be appreciated.

 Regards,
 Tina.



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

[Adam Nelson]

Also, one has to work out the cost of high availability.  If you're
talking about a situation where you reduce downtime from 4 hours/yr to
.5 hours/yr and it costs you x dollars, you have to make sure that the
extra 3.5 hours of downtime would cost more than that much money.

The system you have appears to cost $10-100k more than a standard
master-slave setup.  If you're using a stable unix, stable mysql, good
hardware (Sun,HP,IBM), and RAID in a datacenter.  You're talking about
99.95% uptime right there.  Throw in a slave and it's probably 99.99%.

People always seem to forget that downtime is usually caused by human
error on a well made system.  Human error is what most effort needs to
be taken to correct.  That means putting your effort into reducing
DELETE and UPDATE statement rights, keeping everybody off the machines,
having auto_commit off by default.

Just think of all the times you've seen a service unavailble due to
somebody [EMAIL PROTECTED] up as compared to a kernel fault or a faulty RAID card.


 -Original Message-
 From: Ian Neubert [mailto:[EMAIL PROTECTED] 
 Sent: Thursday, August 07, 2003 6:23 PM
 To: [EMAIL PROTECTED]
 Cc: Dathan Vance Pattishall; [EMAIL PROTECTED]
 Subject: RE: MySQL Replication
 
 
 Good question :)
 
 I got a message from a person off the list that suggested I 
 use network disk
 mirroring or a NAS/SAN/NFS system to handle that. I'm not sure if the
 mirroring would be 100% perfect, but the NAS/SAN solution 
 should as either
 server would be reading and writing to the same physical data.
 
 But, then I have another point of failure. Heh.
 
 I realize that creating the perfect HA system is probably the 
 most difficult
 thing to do, and doesn't come cheaply either. However, I'm 
 going to think it
 through and try anyway :)
 
 I've read your presentations on your website and have used 
 that info for my
 plan here, but its a little difficult to get details from 
 just the slides
 (as you even mentioned on your site) :)
 
 Do you bother with multi-masters? How do you ensure redundancy on the
 write/master server?
 
 ...
 Ian Neubert
 Director of IS
 TWAcomm.com, Inc.
 http://www.twacomm.com/
 
 -Original Message-
 From: Jeremy Zawodny [mailto:[EMAIL PROTECTED]
 Sent: Thursday, August 07, 2003 2:53 PM
 To: Ian Neubert
 Cc: Dathan Vance Pattishall; [EMAIL PROTECTED]
 Subject: Re: MySQL Replication
 
 
 On Thu, Aug 07, 2003 at 01:00:12PM -0700, Ian Neubert wrote:
  I was trying to design it so that the slaves wouldn't know they had
  connected to a different master, as they both masters would 
 have the same
 IP
  address that gets failed over based on the Linux Virtual 
 Server software
 and
  VRRP (like heartbeat from Linux-HA).
 
 That path is a very, very, very difficult one.
 
 How can you absolutely guarantee that each master's binlog will be
 indentical in name, size, and content?
 
 If you can't, this scenario really falls apart.
 
 (I've suggested enhancements to MySQL that would fix this but don't
 know if they're terribly high on the priority list...)
 
 Jeremy
 --
 Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
 [EMAIL PROTECTED]  |  http://jeremy.zawodny.com/
 
 MySQL 4.0.13: up 6 days, processed 212,501,412 queries (399/sec. avg)
 
 --
 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

[Jeremy Zawodny]

On Fri, Aug 08, 2003 at 12:10:18PM -0400, Adam Nelson wrote:
 Also, one has to work out the cost of high availability.  If you're
 talking about a situation where you reduce downtime from 4 hours/yr to
 .5 hours/yr and it costs you x dollars, you have to make sure that the
 extra 3.5 hours of downtime would cost more than that much money.

Agreed.

In fact, this has come up before...

  http://jeremy.zawodny.com/blog/archives/000805.html

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 10 days, processed 336,775,492 queries (387/sec. avg)

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

Re: MySQL Replication

[Jeremy Zawodny]

On Thu, Aug 07, 2003 at 03:22:58PM -0700, Ian Neubert wrote:
 Good question :)
 
 I got a message from a person off the list that suggested I use
 network disk mirroring or a NAS/SAN/NFS system to handle that. I'm
 not sure if the mirroring would be 100% perfect, but the NAS/SAN
 solution should as either server would be reading and writing to the
 same physical data.
 
 But, then I have another point of failure. Heh.

Right.

 I realize that creating the perfect HA system is probably the most
 difficult thing to do, and doesn't come cheaply either. However, I'm
 going to think it through and try anyway :)

Well, what you end up finding is that eliminating all points of
failure it very, very, very difficult (and expensive).  But you can
try to architect things so that they're still affordable and provide
minimal downtime in the event of a failure.

 I've read your presentations on your website and have used that info
 for my plan here, but its a little difficult to get details from
 just the slides (as you even mentioned on your site) :)

Yeah.  And there are no upcoming tour dates. :-)

 Do you bother with multi-masters?

Sometimes.  It's up to each group to think about the tradeoffs of
multi-master vs. master/slave with a switch-over plan.

 How do you ensure redundancy on the write/master server?

One thing you can do is have a backup master that slaves from the
master but doesn't get other work to do.  If it has hardware as beefy
as the master, then switching isn't *too* painful.  Many of our groups
are using that model today.  But others do not.

If you want to go into a lot more detail off-list, let me know.  We
might be able to arrange something...

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 4.0.13: up 6 days, processed 213,085,838 queries (398/sec. avg)

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

RE: MySQL Replication

[Ian Neubert]

I was trying to design it so that the slaves wouldn't know they had
connected to a different master, as they both masters would have the same IP
address that gets failed over based on the Linux Virtual Server software and
VRRP (like heartbeat from Linux-HA).

I'm beginning to think that my configuration is too complex, and would
introduce too many places for failure.

Off hand do you know any good resources on how to setup a highly available
and load balanced MySQL system? Setting up replication is easy enough, but I
can't find much data about getting the whole thing to work together so to
speak (highly available writes).

Thanks!

...
Ian Neubert
Director of IS
TWAcomm.com, Inc.
http://www.twacomm.com/

-Original Message-
From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 12:54 PM
To: 'Ian Neubert'; [EMAIL PROTECTED]
Subject: RE: MySQL Replication


That configuration doesn't seem to work if I'm reading it right. A slave
cannot have more then one master unless mysql more then 1 mysqld process
running on different ports is used.

--For example, if a person places an order on our site, the update is
sent
--to
--the master server. Can that update be replicated to the slaves before
the
--browser is taken to a reciept page (which is queried against the
slaves)?

Also guaranteeing an event has occurred on a slave and building
application logic dependent on this necessity is a tough problem to
solve and you might as well query the master since you need to verify
the event is there anyway as part of the conditional.

The reason for my last statement, I've notice in 3.23.5x that
replication lag can occur in many situations:
- The master is loaded and is not able to stream the binary log file as
fast.
- Locks on the slave preventing updates to occur
- Load on the slave is high
- Invalid packet errors where the slave needs to reconnect
- Some sort of error that stops replication


With all of the above examples, I would not have application logic
dependent on slave events. Instead code your applications based on the
known limitations.

For example a search engine. It does not necessarily need an up to date
all of the time index of words and matches...


--
Dathan



---Original Message-
--From: Ian Neubert [mailto:[EMAIL PROTECTED]
--Sent: Thursday, August 07, 2003 11:27 AM
--To: [EMAIL PROTECTED]
--Subject: MySQL Replication
--
--Hello all,
--
--Can anyone share their experience with replication? In particular I'm
--wondering how fast (or how slow?) replication occurs. Could I pull
off
--the
--configuration below?
--
--
--http://linux.ianneubert.com/images/mysql_failover.png or
--http://linux.ianneubert.com/images/mysql_failover.pdf
--
--Thanks for your insight!
--
--...
--Ian Neubert
--Director of IS
--TWAcomm.com, Inc.
--http://www.twacomm.com/
--
--

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



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

RE: MySQL Replication

[Dathan Vance Pattishall]

---Original Message-
--From: Ian Neubert [mailto:[EMAIL PROTECTED]
--Sent: Thursday, August 07, 2003 1:00 PM
--To: Dathan Vance Pattishall
--Cc: [EMAIL PROTECTED]
--Subject: RE: MySQL Replication
--

--Off hand do you know any good resources on how to setup a highly
--available
--and load balanced MySQL system? Setting up replication is easy
enough,

Highly available systems work generally in your design but here are some
suggestions based on some things I've been doing.

1) Configure the 2 masters to share the same IP with VRRP.
2) The sub master or fail-over master needs to have the same data and
same bin-log so CHANGE MASTER TO can be avoided on the slaves where a
auto reconnect can reliably pick up from where they left off.
 - Suggestions mirror over the network or write the bin logs on a shared
NFS device (another point of failure)
3) Software need to monitor the master / sub-master to fail over and
keep the primary master down since fail back should be done manually.
This is due to order of events such as repair etc -what happens 1st.

There are a few steps I'm forgetting but this should work.






--but I
--can't find much data about getting the whole thing to work together
so to
--speak (highly available writes).
--
--Thanks!
--
--...
--Ian Neubert
--Director of IS
--TWAcomm.com, Inc.
--http://www.twacomm.com/
--
---Original Message-
--From: Dathan Vance Pattishall [mailto:[EMAIL PROTECTED]
--Sent: Thursday, August 07, 2003 12:54 PM
--To: 'Ian Neubert'; [EMAIL PROTECTED]
--Subject: RE: MySQL Replication
--
--
--That configuration doesn't seem to work if I'm reading it right. A
slave
--cannot have more then one master unless mysql more then 1 mysqld
process
--running on different ports is used.
--
For example, if a person places an order on our site, the update
is
--sent
to
the master server. Can that update be replicated to the slaves
before
--the
browser is taken to a reciept page (which is queried against the
--slaves)?
--
--Also guaranteeing an event has occurred on a slave and building
--application logic dependent on this necessity is a tough problem to
--solve and you might as well query the master since you need to verify
--the event is there anyway as part of the conditional.
--
--The reason for my last statement, I've notice in 3.23.5x that
--replication lag can occur in many situations:
--- The master is loaded and is not able to stream the binary log file
as
--fast.
--- Locks on the slave preventing updates to occur
--- Load on the slave is high
--- Invalid packet errors where the slave needs to reconnect
--- Some sort of error that stops replication
--
--
--With all of the above examples, I would not have application logic
--dependent on slave events. Instead code your applications based on
the
--known limitations.
--
--For example a search engine. It does not necessarily need an up to
date
--all of the time index of words and matches...
--
--

--Dathan
--
--
--
-Original Message-
From: Ian Neubert [mailto:[EMAIL PROTECTED]
Sent: Thursday, August 07, 2003 11:27 AM
To: [EMAIL PROTECTED]
Subject: MySQL Replication

Hello all,

Can anyone share their experience with replication? In particular
I'm
wondering how fast (or how slow?) replication occurs. Could I pull
--off
the
configuration below?


http://linux.ianneubert.com/images/mysql_failover.png or
http://linux.ianneubert.com/images/mysql_failover.pdf

Thanks for your insight!

...
Ian Neubert
Director of IS
TWAcomm.com, Inc.
http://www.twacomm.com/


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




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

Re: MySQL Replication

[Michael Conlen]

Something to ask yourself in all this is, how fast can I change out a 
piece of hardware? If I needed a high availability system, on the cheap 
(ie, not a million dollars US worth of Sun hardware) I'd probably go 
with a bunch of SuperMicro 2U rack mount servers with the hot swap SCSI 
drives. You can rack up a bunch of thses and in the event of hardware 
failure pull the drives from one machine and put them in another, and be 
online in under 15 minutes (that's a lot of 9s if it happens once a 
year, your going to be down longer than that rebooting each time there's 
a critical OS update). I've had a lot of success of bringing a server up 
on different hardware by switching drives in to new units quickly. If 
your racked up and ready to go it could be as quick as the time to swap 
drives and power up. This presumes human presence in your datacenter 
24/7 and good alarms. The nice thing is that it doesn't require someone 
to log in and do things, it doesn't require custom scripts, it doesn't 
require esoteric high availability software. It requires someone to pull 
some drives and plug them in somewhere else and turn the unit on. Is 15 
minutes acceptable over the life of one of these systems (hint: I've 
only ever seen one of these systems fail, and I've got a lot of them 
here (and yes, I pulled the drives and put them in a spare unit and 
voila)).

If you have a lot of data you can look at fibre channel solutions for 
your data drives. The new unit can attach to the same disks over a 
fabric (if I'm not using outdated buzz words here) and voila, a few 
terrabytes of data is on a new system.  It's also handy if you need to 
switch off masters. Work out a system where your data is on a FC array 
to where you can switch which system handles it. Down the server, run a 
script to change which systems attach that data, bring up the server on 
the other machine (complete with IP addresses). Sure, there's some small 
downtime, but you can usually get away with a well planned couple of 
seconds at 3 AM.

Lots of time/money are put in to software solutions where an igor would 
do well (or a NOC tech, and for your NOC techs out there, I've got a lot 
of respect for igors, they are good with a needle).

Something else to consider in high availability systems is regression 
testing. Think about what people can/will do to your systems and test 
against it. This is a good way to get a lot of extra hardware around in 
your office/lab. Think of everything you might do to a production system 
and write a test plan for it (I once did a 1800 line interactive shell 
script that had 900 test plans for each hardware platform it worked on, 
of which there were 12). In any case, when I upgrade the version of the 
OS, what happens. When the code does X (for every X) what happens. When 
I buy a new switch, what happens. When I upgrade MySQL, what happens. 
When I introduce code changes, what happens. While it's not directly 
related to MySQL it's important, and you should at least be thinking in 
terms of OS, Hardware and Database Server and have a good set of 
automated test plans from the developers you can run against your 
hardware that includes load testing. You can put a ton in to hardware 
failover, but it won't mean squat when the code locks all your other 
queries out for a couple of hours. I had a situation where upgrading the 
clients sytems to using INNODB tables caused a problem for one of his 
scripts that bulk loaded information in to the system. It turned out to 
be a nice little switch in my.cnf, but I had no way to test this before 
I did a alter table on his stuff to know that his updates would take 
*that* long before (in the end) failing. Also consider that hte default 
for the option that needed to be switched had changed between 
versions... ...anyway, get your self a nice testing lab out of all this 
if you can, I'm sure we'd all like to have more hardware to play with :)

--
Michael Suspenders and Belt Conlen


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

RE: MySQL Replication

[Dathan Vance Pattishall]

That configuration doesn't seem to work if I'm reading it right. A slave
cannot have more then one master unless mysql more then 1 mysqld process
running on different ports is used.

--For example, if a person places an order on our site, the update is
sent
--to
--the master server. Can that update be replicated to the slaves before
the
--browser is taken to a reciept page (which is queried against the
slaves)?

Also guaranteeing an event has occurred on a slave and building
application logic dependent on this necessity is a tough problem to
solve and you might as well query the master since you need to verify
the event is there anyway as part of the conditional.

The reason for my last statement, I've notice in 3.23.5x that
replication lag can occur in many situations:
- The master is loaded and is not able to stream the binary log file as
fast.
- Locks on the slave preventing updates to occur
- Load on the slave is high
- Invalid packet errors where the slave needs to reconnect
- Some sort of error that stops replication


With all of the above examples, I would not have application logic
dependent on slave events. Instead code your applications based on the
known limitations.

For example a search engine. It does not necessarily need an up to date
all of the time index of words and matches...


--
Dathan



---Original Message-
--From: Ian Neubert [mailto:[EMAIL PROTECTED]
--Sent: Thursday, August 07, 2003 11:27 AM
--To: [EMAIL PROTECTED]
--Subject: MySQL Replication
--
--Hello all,
--
--Can anyone share their experience with replication? In particular I'm
--wondering how fast (or how slow?) replication occurs. Could I pull
off
--the
--configuration below?
--
--
--http://linux.ianneubert.com/images/mysql_failover.png or
--http://linux.ianneubert.com/images/mysql_failover.pdf
--
--Thanks for your insight!
--
--...
--Ian Neubert
--Director of IS
--TWAcomm.com, Inc.
--http://www.twacomm.com/
--
--

--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 with 2 masters and 1 slave

[Andreas]

Erik Olsen wrote:
Is it possible for slave to connect to 2 different masters and have
synchronized database from both?
So you would have updates on 2 masters M1 and M2 which would be 
replicated to the read-only slave S1 ?

The point of MySQL's replication is that after an replication-event 
there is allways a moment in time where slave == master.

In your model you'd build :   M1  --  S  -- M2

An update of M1 would be carried to S but wouldn't be transferred to M2 
so S had no chance to get in the state  S == M2  anymore.
I suppose that'd break the replication process and it'd stop.

You have to do it in a circle as the manual describes :

... --  M1  --  M2  --  M3  --  M1 ...

I'd rather have it like a star formation but that seams to be 
impossible, too.
Updates on all machines with replication to a central supermaster SM

   M1  --  SM  --  M2

This way I wouldn't rely on all hosts in the replication-circle to stay 
up and do their job since one is off site and hanging on a slow dial-up 
line.



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

RE: Mysql replication with 2 masters and 1 slave

[Erik Olsen]

Yes.  The idea was to backup 2 masters, 1 that is ours and 1 that is a
costumer. The slave's job is just going to have a synchronised db of
both servers db. The plan was to have it on a different place in case of
fire. 

But I must find another solution then.
 

Erik Olsen wrote:
 Is it possible for slave to connect to 2 different masters and have
 synchronized database from both?

So you would have updates on 2 masters M1 and M2 which would be 
replicated to the read-only slave S1 ?

The point of MySQL's replication is that after an replication-event 
there is allways a moment in time where slave == master.

In your model you'd build :   M1  --  S  -- M2

An update of M1 would be carried to S but wouldn't be transferred to M2 
so S had no chance to get in the state  S == M2  anymore.
I suppose that'd break the replication process and it'd stop.

You have to do it in a circle as the manual describes :

... --  M1  --  M2  --  M3  --  M1 ...


I'd rather have it like a star formation but that seams to be 
impossible, too.
Updates on all machines with replication to a central supermaster SM

M1  --  SM  --  M2

This way I wouldn't rely on all hosts in the replication-circle to stay 
up and do their job since one is off site and hanging on a slow dial-up 
line.



-- 
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 with 2 masters and 1 slave

[Joseph Bueno]

May be I am misunderstanding your goals but it seems that you want to
have a backup server for two independant masters (with different
databases). Something like:
M1 --  S  --  M2
DB1DB1  DB2
   DB2
If this is the case, you can run two mysqld instances on S, each one
replicating from one master:
M1 -- S1
DB1DB1
   S2  --  M2
   DB2  DB2
Hope this helps
Joseph Bueno
Erik Olsen wrote:
Yes.  The idea was to backup 2 masters, 1 that is ours and 1 that is a
costumer. The slave's job is just going to have a synchronised db of
both servers db. The plan was to have it on a different place in case of
fire. 

But I must find another solution then.
 

Erik Olsen wrote:

Is it possible for slave to connect to 2 different masters and have
synchronized database from both?


So you would have updates on 2 masters M1 and M2 which would be 
replicated to the read-only slave S1 ?

The point of MySQL's replication is that after an replication-event 
there is allways a moment in time where slave == master.

In your model you'd build :   M1  --  S  -- M2

An update of M1 would be carried to S but wouldn't be transferred to M2 
so S had no chance to get in the state  S == M2  anymore.
I suppose that'd break the replication process and it'd stop.

You have to do it in a circle as the manual describes :

... --  M1  --  M2  --  M3  --  M1 ...

I'd rather have it like a star formation but that seams to be 
impossible, too.
Updates on all machines with replication to a central supermaster SM

M1  --  SM  --  M2

This way I wouldn't rely on all hosts in the replication-circle to stay 
up and do their job since one is off site and hanging on a slow dial-up 
line.





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

Re: mysql replication problem

[Alec . Cawley]

Have you turned log-bin on the master server? The master will not generate
the log files that the slave reads unless this is turned on.


|-+
| |   Floyd Wellershaus|
| |   [EMAIL PROTECTED]|
| |   .com|
| ||
| |   04/03/2003 12:54 |
| ||
|-+
  
--|
  |
  |
  |   To:   Mysqllist (E-mail) [EMAIL PROTECTED]   
  |
  |   cc:  
  |
  |   Subject:  mysql replication problem  
  |
  
--|




Hello,
I am trying to start replication between 2 linux servers mysql version is
4.0.8.
I am following all instructions, but I keep getting the following error
message on the slave when I restart.

030303 10:35:28  mysqld started
030303 10:35:29  InnoDB: Started
/usr/mysql/libexec/mysqld: ready for connections
030303 10:35:29  Slave I/O thread: connected to master '[EMAIL PROTECTED]:3306',
replication started in log 'floyd-bin.008' at position 164
030303 10:35:29  Error reading packet from server: Could not find first log
file name in binary log index file (server_errno=1236)
030303 10:35:29  Got fatal error 1236: 'Could not find first log file name
in binary log index file' from master when reading data from binary log
030303 10:35:29  Slave I/O thread exiting, read up to log 'floyd-bin.008',
position 164






-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysql replication across platforms

[Jerry]

What versions of MySQL are on each machine ?

Jerry

- Original Message -
From: Floyd Wellershaus [EMAIL PROTECTED]
To: Mysqllist (E-mail) [EMAIL PROTECTED]
Sent: Thursday, February 27, 2003 1:55 PM
Subject: mysql replication across platforms


 Hello,
 I am unsuccessfully trying to start mysql replication between an Sun
Solaris
 server and a RedHat Linux server.
 Can this be done ?
 What about the fact that the binary files for the logs that need to be
 transferred over upon replication startup are from different OS's.
 Does anyone have any experience doing this ?

 Thank You,
 floyd


 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail
[EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysql replication problem

[walt]

Vaso Koutsonikola wrote:

 Hi,
 I am replicating a table between 2 servers.
 The table on the master executes many transactions that should be
 replicated to the table on the slave..
 The table on the slave is only readable...
 I have noticed that the changes on the master are not replicated at once
 but it takes about 2 hours to
 get the slave updated..
 When this happens I see on the slaves error log file the error
 Error reading packet from server: Lost connection to MySQL server during
 query (read_errno 134,server_errno=2013)
 and then the slave seems to reconnect to the master.
 Has anyone any idea why is this happening?
 Is it possible that when I read data from the table on the slave, the
 slave cannot be updated because it gets locked or something
 like that?

 Thanks..

 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)

 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Vaso,
What version of mysql and what OS?

walt


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Mysql Replication

[Gianluca Sordiglioni]

AFAIK, you can't do it transparently from the MySQL client library. (like in
Oracle)
You have to accomplish the task in your application, ie: try to
connect/select from the master database, if command fails then try the
slave1, then slave2, then slave3... and so on.
Hint: create you own mysql_connect() and mysql_query().


Gianluca Sordiglioni
DB Manager
Vox2Web Srl
tel. 0245054533



- Original Message -
From: Massimo Bandinelli [EMAIL PROTECTED]
To: [EMAIL PROTECTED]
Sent: Wednesday, November 20, 2002 4:22 PM
Subject: Mysql Replication


A question:

I've a Mysql System with a Master and two slave.

Can I configure my master to automatically forward the select queries to =
the slaves?

Massimo Bandinelli
System Administrator



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: Mysql replication problem

[Gerald R. Jensen]

Just a guess, but I would say the problem rests in the network or network
configuration. We have a number of installation running replication (both
Win32 and Linux), and have never seen anything like this.

Gerald Jensen

- Original Message -
From: Vaso Koutsonikola [EMAIL PROTECTED]
To: [EMAIL PROTECTED]; Vaso Koutsonikola [EMAIL PROTECTED]
Sent: Saturday, November 23, 2002 1:10 PM
Subject: Mysql replication problem


Hello,
I use mysql version 3.23.49 and I have set up replication but once in a
while I get the error below on the slave..
Error reading packet from server: Lost connection to MySQL server
during query (read_errno 134,server_errno=2013)

Then the slave reconnects to the master and the replication goes on...
Does anyone have any idea why is this happening and what can I do to
solve this problem??

Thank you..


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php





-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Mysql Replication

[Simon Green]

Not out of the box.
You would have to right some code to do this.
The best way to do this is get every one who rights code to set up to hosts
at the top of there code.
Simon

-Original Message-
From: Massimo Bandinelli [mailto:[EMAIL PROTECTED]]
Sent: 21 November 2002 09:48
To: Jon Frisby
Cc: [EMAIL PROTECTED]
Subject: R: Mysql Replication


I want do build a mysql cluster.

The master receive all queries.

Then, the master write, update, delete, ecc..
the slaves answer SELECTs

If this is impossible, which is the utility to have slaves???

-Messaggio originale-
Da: Jon Frisby [mailto:[EMAIL PROTECTED]]
Inviato: mercoledì 20 novembre 2002 21.10
A: Massimo Bandinelli
Oggetto: RE: Mysql Replication


No.  Why would you wish to do so?

-JF

 -Original Message-
 From: Massimo Bandinelli [mailto:[EMAIL PROTECTED]] 
 Sent: Wednesday, November 20, 2002 7:22 AM
 To: [EMAIL PROTECTED]
 Subject: Mysql Replication
 
 
 A question:
 
 I've a Mysql System with a Master and two slave.
 
 Can I configure my master to automatically forward the select 
 queries to =
 the slaves?
 
 
 Massimo Bandinelli
 System Administrator
 -
 Register.it S.p.A. - Gruppo DADA
 tel +39 199 REGIST (+39 199 734478)
 fax +39 035 3230312
 http://we.register.it
 mailto:[EMAIL PROTECTED]
 
 -
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list archive)
 
 To request this thread, e-mail [EMAIL PROTECTED]
 To unsubscribe, e-mail 
 [EMAIL PROTECTED]
 Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
 
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: Mysql Replication

[Jon Frisby]

You have to send the SELECT queries directly to the slaves.  Your code
must know to connect to an appropriate machine for the type of work it
wants to do:  If it's only reading data (SELECTs), it should connect to
one of the slaves.  If it must alter data (INSERT/UPDATE/DELETE) or must
both read AND alter data then it must connect to the master.

-JF

 -Original Message-
 From: Massimo Bandinelli [mailto:[EMAIL PROTECTED]] 
 Sent: Thursday, November 21, 2002 1:48 AM
 To: Jon Frisby
 Cc: [EMAIL PROTECTED]
 Subject: R: Mysql Replication
 
 
 I want do build a mysql cluster.
 
 The master receive all queries.
 
 Then, the master write, update, delete, ecc..
 the slaves answer SELECTs
 
 If this is impossible, which is the utility to have slaves???
 
 -Messaggio originale-
 Da: Jon Frisby [mailto:[EMAIL PROTECTED]]
 Inviato: mercoledì 20 novembre 2002 21.10
 A: Massimo Bandinelli
 Oggetto: RE: Mysql Replication
 
 
 No.  Why would you wish to do so?
 
 -JF
 
  -Original Message-
  From: Massimo Bandinelli [mailto:[EMAIL PROTECTED]] 
  Sent: Wednesday, November 20, 2002 7:22 AM
  To: [EMAIL PROTECTED]
  Subject: Mysql Replication
  
  
  A question:
  
  I've a Mysql System with a Master and two slave.
  
  Can I configure my master to automatically forward the select 
  queries to =
  the slaves?
  
  
  Massimo Bandinelli
  System Administrator
  -
  Register.it S.p.A. - Gruppo DADA
  tel +39 199 REGIST (+39 199 734478)
  fax +39 035 3230312
  http://we.register.it
  mailto:[EMAIL PROTECTED]
  
  
 -
  Before posting, please check:
 http://www.mysql.com/manual.php   (the manual)
 http://lists.mysql.com/   (the list archive)
  
  To request this thread, e-mail [EMAIL PROTECTED]
  To unsubscribe, e-mail 
  [EMAIL PROTECTED]
  Trouble unsubscribing? Try: 
 http://lists.mysql.com/php/unsubscribe.php
  
  
 
 


-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

Re: mysql replication

[Jeremy Zawodny]

On Tue, Nov 12, 2002 at 12:06:08PM +0530, mod_perl wrote:
  hi all,
  i want to know how can i implement a two way replication between
  more than two machines in mysql.

A slave can only have one master.  So two-way replication can only be
done between two MySQL instances--whether or not they're on the same
machine.

Jeremy
-- 
Jeremy D. Zawodny |  Perl, Web, MySQL, Linux Magazine, Yahoo!
[EMAIL PROTECTED]  |  http://jeremy.zawodny.com/

MySQL 3.23.51: up 97 days, processed 2,053,697,790 queries (242/sec. avg)

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: mysql replication error

[Natale Babbo]

No ... I'm just setting up the replication for the
first time.
I make a dump of the master and i replaced it on the
slave.

thanks and bye.





 --- Victor Pendleton [EMAIL PROTECTED] ha
scritto:   Has the replication worked previously for
you?
 If so, have you reset the master and the slave?
 Was a snapshot of the master placed on the slave
 before beginning
 replication?
 I hate to ask such simple questions but I am just
 checking.
 
 Victor
 
 -Original Message-
 From: Natale Babbo
 To: [EMAIL PROTECTED]
 Sent: 10/23/02 8:22 AM
 Subject: mysql replication error
 
 i get the following error in the connection
 slave-master while setting up a replication between
 two MySQLServer(slave and master both windows XP):
 
 .err file on slave:
 MySql: ready for connections
 021023 14:08:34  InnoDB: Started
 021023 14:08:35  Slave I/O thread: connected to
 master
 '[EMAIL PROTECTED]:3306',  replication started in log
 'FIRST' at position 4
 021023 14:08:35  Error updating slave list: Query
 error
 021023 14:08:35  Slave I/O thread exiting, read up
 to
 log 'FIRST', position 4
 
 what does it mean?
 
 My.ini on Master:
 log-bin=C:\Programmi\MySQL\Logs\MySQL-bin
 server-id=1
 
 My.ini on Slave:
 master-host=192.168.0.4
 master-user=repl
 master-password=password
 master-port=3306
 server-id=2
 set-variable = slave_net_timeout=3600
 set-variable = net_read_timeout=3600
 
 Show master status:
 File : MySQL-bin.001
 Position : 79
 Binlog_do_db : 
 Binlog_inogne_db :
 
 ERROR
 No query specified
 
 Show Slave status:
 Master_Host  : 192.168.0.04
 Master_User  : repl
 Master_Port  : 3306
 Connect_retry: 60
 Master_Log_File  : 
 Read_Master_Log_Pos  : 4
 Relay_Log_File   : MySQL-relay-bin.002
 Relay_Log_Pos: 4
 Relay_Master_Log_File: 
 Slave_IO_Running : Yes
 Slave_SQL_Running: Yes
 Replicate_do_db  : 
 Replicate_ignore_db  : 
 Last_errno   : 0
 Last_error   : 
 Skip_counter : 0
 Exec_master_log_pos  : 0
 Relay_log_space  : 0
 
 ERROR:
 No query specified
 
 What is wrong?
 Why do I get the error No query specified? Which
 type of query should I specify?
 
 Thanks in advance.
 Natale Babbo
 

__
 Mio Yahoo!: personalizza Yahoo! come piace a te 

http://it.yahoo.com/mail_it/foot/?http://it.my.yahoo.com/
 

-
 Before posting, please check:
http://www.mysql.com/manual.php   (the manual)
http://lists.mysql.com/   (the list
 archive)
 
 To request this thread, e-mail
 [EMAIL PROTECTED]
 To unsubscribe, e-mail

[EMAIL PROTECTED]
 Trouble unsubscribing? Try:
http://lists.mysql.com/php/unsubscribe.php 

__
Mio Yahoo!: personalizza Yahoo! come piace a te 
http://it.yahoo.com/mail_it/foot/?http://it.my.yahoo.com/

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php

RE: MySQL Replication - v4 slave with v3 master

[Jeremy Tinley]

According to the replication compatibility table, you can.

http://www.mysql.com/doc/en/Replication_Implementation.html

-J


-Original Message-
From: Andrew Braithwaite [mailto:[EMAIL PROTECTED]] 
Sent: Tuesday, October 15, 2002 8:36 AM
To: [EMAIL PROTECTED]
Subject: MySQL Replication - v4 slave with v3 master

Hi all,

Does anyone have any experience with running a v3 master and v4 slaves?

Will it work?  -  I can't see why not...

Thanks in advance,

Andrew

sql, query

-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail
[EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php



-
Before posting, please check:
   http://www.mysql.com/manual.php   (the manual)
   http://lists.mysql.com/   (the list archive)

To request this thread, e-mail [EMAIL PROTECTED]
To unsubscribe, e-mail [EMAIL PROTECTED]
Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php