subject:"Re\: Replication"

Re: Replication and user privileges

2019-02-26 Thread Jim

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

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

Hello Jim,

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

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

Hello Jim,

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

of it, but have a question about user privileges.

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

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

exist on the slave.

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

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

the slave as well?

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

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

Thank you.
Jim

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

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

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

===

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

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

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

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

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

That would make sense based on our discussion, correct?

Thanks again.
Jim

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

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

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

Yours,

Thanks, Shawn.

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

Given how you read:
"If the |read_only|

system variable is enabled, the server permits client updates only
from users who have the |SUPER|

privilege. If the |super_read_only|

system variable is also enabled, the server prohibits client updates
even from users who have |SUPER|
."
One somewhat gets the impression that in order to enable
super_read_only, one must also enable read_only.

However, based on:

Re: Replication and user privileges

2019-02-26 Thread Jim

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

Hello Jim,

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

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

Hello Jim,

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

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

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

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

exist on the slave.

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

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

the slave as well?

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

Thank you.
Jim

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

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

===

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

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

That would make sense based on our discussion, correct?

Thanks again.
Jim

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

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

Yours,

Thanks, Shawn.

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

Given how you read:
"If the |read_only|

system variable is enabled, the server permits client updates only from
users who have the |SUPER|

privilege. If the |super_read_only|

However, based on:

Re: Replication and user privileges

2019-02-26 Thread shawn l.green

Hello Jim,

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

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

Hello Jim,

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

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

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

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

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

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

Thank you.
Jim

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

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

===

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

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

That would make sense based on our discussion, correct?

Thanks again.
Jim

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

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

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

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

Yours,

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

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

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

Re: Replication and user privileges

2019-02-25 Thread Jim


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

Hello Jim,

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

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

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




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

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

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

Thank you.
Jim



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


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


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


===

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


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





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

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


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


That would make sense based on our discussion, correct?

Thanks again.
Jim

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

Re: Replication and user privileges

2019-02-25 Thread shawn l.green


Hello Jim,

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

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

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


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

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

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

Thank you.
Jim



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


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


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


===

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


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



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

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


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

Re: Replication problem

2014-08-30 Thread william drescher


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

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


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

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

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

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


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

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

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

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

Re: Replication problem

2014-08-30 Thread william drescher


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

Hello guys, some points to check here:

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


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


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

Not - see prior post

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


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




Cheers,


Thanks, I need the cheers.






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

Re: Replication problem

2014-08-30 Thread william drescher


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

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

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

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

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

  Replicate_Ignore_Server_Ids:
 Master_Server_Id: 3



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

Re: Replication problem

2014-08-30 Thread Johnny Withers

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

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

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

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

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

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



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

Re: Replication problem

2014-08-30 Thread william drescher


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

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


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

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

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

  Replicate_Ignore_Server_Ids:
 Master_Server_Id: 3


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





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

Re: Replication problem

2014-08-30 Thread wagnerbianchi.com

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

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

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

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

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


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

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

--

*Wagner Bianchi*


2014-08-30 9:54 GMT-03:00 Johnny Withers joh...@pixelated.net:

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

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

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

Re: Replication problem

2014-08-30 Thread Jose Julian Buda




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

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

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


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

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


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

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










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


Bye

Julian

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

Re: Replication problem

2014-08-30 Thread william drescher


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

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


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




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

Re: Replication problem -solved

2014-08-30 Thread william drescher


Thanks for pointing out the importance of the last error

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


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


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

--bill



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

Re: Replication problem -solved

2014-08-30 Thread Jose Julian Buda




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

Thanks for pointing out the importance of the last error

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

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

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

--bill






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


Bye
Julian

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

Re: Replication problem -solved

2014-08-30 Thread william drescher


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



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

Thanks for pointing out the importance of the last error

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

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

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

--bill






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


Bye
Julian



Thanks Julian, it is.


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

Re: Replication problem

2014-08-29 Thread Andrew Moore

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


 Replication novice

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

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

 I did the usual

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

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

 --bill


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

Re: Replication problem

2014-08-29 Thread wagnerbianchi.com

Hello guys, some points to check here:

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

Cheers,
--
*WB*

2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com:

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

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

Re: Replication problem

2014-08-29 Thread Suresh Kuna

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


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

 Hello guys, some points to check here:

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

 Cheers,
 --
 *WB*

 2014-08-29 17:46 GMT-03:00 Andrew Moore eroomy...@gmail.com:

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




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

Re: replication question replacing the master

2014-01-18 Thread Manuel Arostegui

2014/1/17 Richard Reina gatorre...@gmail.com

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

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

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



If the three machines are sync'ed and have consistent data I don't see the
need of stopping MySQL:

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


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

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

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

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

I see that faster than any other thing.

Hope this helps
Manuel.

Re: replication question replacing the master

2014-01-18 Thread Richard Reina

Manuel,

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

Again, thank you very much.

Richard 



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

Re: replication question replacing the master

2014-01-17 Thread Reindl Harald



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

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

how should the salve smell that anything has changed?



signature.asc
Description: OpenPGP digital signature

re: replication newbie questions

2013-08-29 Thread Michael Widenius


Hi!

 Ed == Ed L mysql@bluepolka.net writes:

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

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

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

Yes.

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

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

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

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

Regards,
Monty

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

Re: replication newbie questions

2013-08-28 Thread Ananda Kumar

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

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

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

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

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

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

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

 Thank you in advance for any help.

 Ed

Re: replication newbie questions

2013-08-28 Thread Ed L.


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


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


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

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


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


Thanks,
Ed



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


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


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


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


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


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

 Thank you in advance for any help.

 Ed

Re: replication newbie questions

2013-08-28 Thread Ananda Kumar

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


On Wed, Aug 28, 2013 at 3:08 PM, Ed L. mysql@bluepolka.net wrote:

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


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


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

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

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

 Thanks,
 Ed



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

Re: Replication question

2013-07-25 Thread rich gray



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

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

NO.

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


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


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

RE: Replication question

2013-07-24 Thread Rick James

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

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

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


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

Re: replication fails after upgrade to 5.6

2013-02-22 Thread Mike Franon

HI Trimurthy,

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


Thanks



On Fri, Feb 22, 2013 at 12:15 AM, Trimurthy trimur...@tulassi.com wrote:
 Dear Mike Franon,

  i have also faced the same problem while setting up the replication. by
 that time i have added the error no to
 slave-skip-errors=1062 configuration file and restarted the server. after
 that replication gets work properly. once try this one.
 and one thing that i would like to inform that master server version always
 should be less than or equal to the slave version.

 Thanks  Kind Regards,
 TRIMURTHY




 
 From: Mike Franon kongfra...@gmail.com
 Sent: Thursday, February 21, 2013 11:43 PM
 To: Reindl Harald h.rei...@thelounge.net
 Subject: Re: replication fails after upgrade to 5.6

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

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

 Thanks

 On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald h.rei...@thelounge.net
 wrote:
 update the master ASAP in a short timeframe too
 and re-init replication if needed

 normally both should have exactly the same version

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

 Am 21.02.2013 18:03, schrieb Mike Franon:
 So I created a new test box on AWS, and just did one upgrade from
 5.0.96 to 5.1, like I did before and replication will not work from a
 master with 5.0.96 to a slave with 5.1.68

 I keep getting Error 1062, Duplicate Entry for key

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

 I was curious if anyone had any ideas?

 Thanks

 On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com
 wrote:
 This is on a slave, i only upgraded on one box which is the slave i
 have not touched master

 On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net
 wrote:


 Am 20.02.2013 23:27, schrieb Mike Franon:
 So I successfully upgraded a test db server from 5.0.96 all the way up
 to 5.6

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

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

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

 did you surely upgrade and restart the slaves first?

 i personally would NOT go to 5.6 now

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


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


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

Re: replication fails after upgrade to 5.6

2013-02-21 Thread Mike Franon

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

I keep getting Error 1062, Duplicate Entry for key

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

I was curious if anyone had any ideas?

Thanks

On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote:
 This is on a slave, i only upgraded on one box which is the slave i
 have not touched master

 On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote:


 Am 20.02.2013 23:27, schrieb Mike Franon:
 So I successfully upgraded a test db server from 5.0.96 all the way up to 
 5.6

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

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

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

 did you surely upgrade and restart the slaves first?

 i personally would NOT go to 5.6 now

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


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

Re: replication fails after upgrade to 5.6

2013-02-21 Thread Reindl Harald

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

normally both should have exactly the same version

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

Am 21.02.2013 18:03, schrieb Mike Franon:
 So I created a new test box on AWS, and just did one upgrade from
 5.0.96 to 5.1, like I did before and replication will not work from a
 master with 5.0.96 to a slave with 5.1.68
 
 I keep getting Error 1062, Duplicate Entry for key
 
 I get no errors when I do a mysql_upgrade, all comes back ok.
 
 I was curious if anyone had any ideas?
 
 Thanks
 
 On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote:
 This is on a slave, i only upgraded on one box which is the slave i
 have not touched master

 On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net 
 wrote:


 Am 20.02.2013 23:27, schrieb Mike Franon:
 So I successfully upgraded a test db server from 5.0.96 all the way up to 
 5.6

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

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

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

 did you surely upgrade and restart the slaves first?

 i personally would NOT go to 5.6 now

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



signature.asc
Description: OpenPGP digital signature

Re: replication fails after upgrade to 5.6

2013-02-21 Thread Mike Franon

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

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

Thanks

On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald h.rei...@thelounge.net wrote:
 update the master ASAP in a short timeframe too
 and re-init replication if needed

 normally both should have exactly the same version

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

 Am 21.02.2013 18:03, schrieb Mike Franon:
 So I created a new test box on AWS, and just did one upgrade from
 5.0.96 to 5.1, like I did before and replication will not work from a
 master with 5.0.96 to a slave with 5.1.68

 I keep getting Error 1062, Duplicate Entry for key

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

 I was curious if anyone had any ideas?

 Thanks

 On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote:
 This is on a slave, i only upgraded on one box which is the slave i
 have not touched master

 On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net 
 wrote:


 Am 20.02.2013 23:27, schrieb Mike Franon:
 So I successfully upgraded a test db server from 5.0.96 all the way up to 
 5.6

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

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

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

 did you surely upgrade and restart the slaves first?

 i personally would NOT go to 5.6 now

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


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

Re: replication fails after upgrade to 5.6

2013-02-21 Thread Reindl Harald



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

normally no

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

 On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald h.rei...@thelounge.net wrote:
 update the master ASAP in a short timeframe too
 and re-init replication if needed

 normally both should have exactly the same version

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

 Am 21.02.2013 18:03, schrieb Mike Franon:
 So I created a new test box on AWS, and just did one upgrade from
 5.0.96 to 5.1, like I did before and replication will not work from a
 master with 5.0.96 to a slave with 5.1.68

 I keep getting Error 1062, Duplicate Entry for key

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

 I was curious if anyone had any ideas?

 Thanks

 On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com wrote:
 This is on a slave, i only upgraded on one box which is the slave i
 have not touched master

 On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net 
 wrote:


 Am 20.02.2013 23:27, schrieb Mike Franon:
 So I successfully upgraded a test db server from 5.0.96 all the way up 
 to 5.6

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

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

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

 did you surely upgrade and restart the slaves first?

 i personally would NOT go to 5.6 now

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



signature.asc
Description: OpenPGP digital signature

RE: replication fails after upgrade to 5.6

2013-02-21 Thread Rick James

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

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


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

Re: replication fails after upgrade to 5.6

2013-02-21 Thread Reindl Harald

and where did anybody say the opposite in this thread?

Am 21.02.2013 22:25, schrieb Rick James:
 It is safer to have the Slave be a newer version.
 
 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Thursday, February 21, 2013 10:30 AM
 To: mysql@lists.mysql.com
 Subject: Re: replication fails after upgrade to 5.6

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

 normally no

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

 On Thu, Feb 21, 2013 at 1:03 PM, Reindl Harald
 h.rei...@thelounge.net wrote:
 update the master ASAP in a short timeframe too and re-init
 replication if needed

 normally both should have exactly the same version

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

 Am 21.02.2013 18:03, schrieb Mike Franon:
 So I created a new test box on AWS, and just did one upgrade from
 5.0.96 to 5.1, like I did before and replication will not work from
 a master with 5.0.96 to a slave with 5.1.68

 I keep getting Error 1062, Duplicate Entry for key

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

 I was curious if anyone had any ideas?

 Thanks

 On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon kongfra...@gmail.com
 wrote:
 This is on a slave, i only upgraded on one box which is the slave
 i
 have not touched master

 On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald
 h.rei...@thelounge.net wrote:


 Am 20.02.2013 23:27, schrieb Mike Franon:
 So I successfully upgraded a test db server from 5.0.96 all the
 way up to 5.6

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

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

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

 did you surely upgrade and restart the slaves first?

 i personally would NOT go to 5.6 now

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



signature.asc
Description: OpenPGP digital signature

Re: replication fails after upgrade to 5.6

2013-02-20 Thread Reindl Harald



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

did you surely upgrade and restart the slaves first?

i personally would NOT go to 5.6 now

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



signature.asc
Description: OpenPGP digital signature

Re: replication fails after upgrade to 5.6

2013-02-20 Thread Mike Franon

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

On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald h.rei...@thelounge.net wrote:


 Am 20.02.2013 23:27, schrieb Mike Franon:
 So I successfully upgraded a test db server from 5.0.96 all the way up to 5.6

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

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

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

 did you surely upgrade and restart the slaves first?

 i personally would NOT go to 5.6 now

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


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

Re: replication problem

2012-12-05 Thread Shawn Green


On 12/3/2012 7:24 AM, Trimurthy wrote:

hi list,   i am trying to set up replication but i couldn't
complete because of the following error
Could not find first log file name in binary log index file can any one
please help me.


... snipped ...

This one should have been easy for the list to advise you about. Let me 
step in to keep things on track.


For each instance of MySQL that creates binary logs there will be an 
'index' file that keeps track of which files this instance is managing. 
For example, if the binary logs are called mysql-bin.00, then the 
index file will be called mysql-bin.index


What has happened is that the contents of the index file no longer 
matches the inventory of actual binary logs present on disk. They have 
either been moved, erased, or had their permissions changed so that the 
system user that the mysqld daemon executes as can no longer see them.


The solution is to manually update the .index file with the current 
listing of binary log files. The longer term solution is to stop 
manually removing binary log files and allow the system to perform that 
for you with a PURGE BINARY LOGS command.  When you have MySQL do the 
purging, the .index file will be automatically updated.


For more details about the binary log, please read:
http://dev.mysql.com/doc/refman/5.5/en/binary-log.html

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



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

Re: replication problem

2012-12-04 Thread divesh kamra

HI

share detail

   Slave_IO_Running: Yes/No
  Slave_SQL_Running: Yes/No
 Last_IO_Errno: 0
  Last_IO_Error:
   Last_SQL_Errno: 0
   Last_SQL_Error:



On Tue, Dec 4, 2012 at 5:59 AM, Rick James rja...@yahoo-inc.com wrote:

 SHOW SLAVE STATUS\G
 SHOW MASTER STATUS;
 What directory are the binlogs in?


  -Original Message-
  From: Michael Dykman [mailto:mdyk...@gmail.com]
  Sent: Monday, December 03, 2012 8:53 AM
  To: trimur...@tulassi.com
  Cc: mysql@lists.mysql.com
  Subject: Re: replication problem
 
  Trimurthy,
 
  you will have to describe the method you are using to setup
  replication.  The error message seems plain but an observer could not
  reasonably guess what caused it without more information.
 
   - michael dykman
 
 
  On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com
  wrote:
   hi list,   i am trying to set up replication but i couldn't
   complete because of the following error Could not find first log file
   name in binary log index file can any one please help me.
  
  
  
  
  
  
  
 Normal
 0
  
  
  
  
 false
 false
 false
  
 EN-US
 X-NONE
 AR-SA
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
   Thanks  Kind Regards,
  
TRIMURTHY
 
 
 
  --
   - michael dykman
   - mdyk...@gmail.com
 
   May the Source be with you.
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql


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

Re: replication problem

2012-12-03 Thread Michael Dykman

Trimurthy,

you will have to describe the method you are using to setup
replication.  The error message seems plain but an observer could not
reasonably guess what caused it without more information.

 - michael dykman


On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com wrote:
 hi list,   i am trying to set up replication but i couldn't
 complete because of the following error
 Could not find first log file name in binary log index file can any one
 please help me.







   Normal
   0




   false
   false
   false

   EN-US
   X-NONE
   AR-SA







































































































































































 Thanks  Kind Regards,

  TRIMURTHY



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

 May the Source be with you.

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

RE: replication problem

2012-12-03 Thread Rick James

SHOW SLAVE STATUS\G
SHOW MASTER STATUS;
What directory are the binlogs in?

 -Original Message-
 From: Michael Dykman [mailto:mdyk...@gmail.com]
 Sent: Monday, December 03, 2012 8:53 AM
 To: trimur...@tulassi.com
 Cc: mysql@lists.mysql.com
 Subject: Re: replication problem

 Trimurthy,

 you will have to describe the method you are using to setup
 replication.  The error message seems plain but an observer could not
 reasonably guess what caused it without more information.

  - michael dykman

 On Mon, Dec 3, 2012 at 7:24 AM, Trimurthy trimur...@tulassi.com
 wrote:
  hi list,   i am trying to set up replication but i couldn't
  complete because of the following error Could not find first log file
  name in binary log index file can any one please help me.

Normal
0

false
false
false

EN-US
X-NONE
AR-SA

  Thanks  Kind Regards,

   TRIMURTHY

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

  May the Source be with you.

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

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

Re: Replication between different versions

2012-11-15 Thread Manuel Arostegui

2012/11/15 Lorenzo Milesi max...@ufficyo.com

 Hi.

 Is it possible to have a master/master replication between servers of
 different versions?
 Now I've two 5.0.51 syncing themselves. I'm installing a new server with
 5.5.28 and I'd like if I can upgrade but still mantaining compatibility.
 Otherwise I'll keep on installing the same version...


You should have no problem as long as you're not using some feature not
supported anymore in 5.5 version.

Manuel

Re: Replication between different versions

2012-11-15 Thread Reindl Harald



Am 15.11.2012 12:46, schrieb Manuel Arostegui:
 2012/11/15 Lorenzo Milesi max...@ufficyo.com
 
 Hi.

 Is it possible to have a master/master replication between servers of
 different versions?
 Now I've two 5.0.51 syncing themselves. I'm installing a new server with
 5.5.28 and I'd like if I can upgrade but still mantaining compatibility.
 Otherwise I'll keep on installing the same version...
 
 
 You should have no problem as long as you're not using some feature not
 supported anymore in 5.5 version

this is not really true

the slave should have the higher version because you can
not control which statements the master writes to the binlog

generally you should not mix exepct for update both starting
with the slave



signature.asc
Description: OpenPGP digital signature

Re: Replication Question

2012-10-26 Thread Manuel Arostegui

2012/10/25 Sabika M sabika.makhd...@gmail.com

 I have replication setup in the following way:

 A - B -C

 I am making updates to server A. I want to stop all my updates and point
 them to server C. After I start writing to server C, can I use the change
 master statement to make the C the master of A (take B out of the topology)
 and proceed to set up peer-to-peer between A - C without taking a new
 backup of c to set up the replication between A-C?

 I guess what I am really asking is if the data is the same, is a backup
 required for initialization?

 Hello,

Make sure you run a show master status at C before sending the writes to
it so you can get to know at which position/binlog you have to start A
replicating from. Also make sure read_only = OFF in C.

Manuel.

RE: Replication Question

2012-10-25 Thread Rick James

Sound right.
Be sure there are no writes during certain critical times.  And that 
replication is caught up.

If you want to verify the sameness see Percona's pt-table-checksum.


 -Original Message-
 From: Sabika M [mailto:sabika.makhd...@gmail.com]
 Sent: Thursday, October 25, 2012 10:16 AM
 To: MySql
 Subject: Replication Question
 
 I have replication setup in the following way:
 
 A - B -C
 
 I am making updates to server A. I want to stop all my updates and
 point them to server C. After I start writing to server C, can I use
 the change master statement to make the C the master of A (take B out
 of the topology) and proceed to set up peer-to-peer between A - C
 without taking a new backup of c to set up the replication between A-
 C?
 
 I guess what I am really asking is if the data is the same, is a backup
 required for initialization?

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

Re: Replication question: How to make a slave a master?

2012-08-24 Thread Reindl Harald



Am 24.08.2012 17:25, schrieb Richard Reina:
 I have a couple of mysql database slaves and would like to make one of them
 be a master as well so that I can set another machine to replicate from it.
 Can anyone tell me how I should go about it or know of any howtos for this
 specific task?

add the same master-conig lines as you did on the other master

there is no magic, master-configurazion does not have
any impact to slave-operation and vice versa




signature.asc
Description: OpenPGP digital signature

RE: Replication question: How to make a slave a master?

2012-08-24 Thread Rick James

Pointing the other slave to the new master is a bit tricky.

 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Friday, August 24, 2012 8:29 AM
 To: mysql@lists.mysql.com
 Subject: Re: Replication question: How to make a slave a master?

 Am 24.08.2012 17:25, schrieb Richard Reina:
  I have a couple of mysql database slaves and would like to make one
 of
  them be a master as well so that I can set another machine to
 replicate from it.
  Can anyone tell me how I should go about it or know of any howtos for
  this specific task?

 add the same master-conig lines as you did on the other master

 there is no magic, master-configurazion does not have any impact to
 slave-operation and vice versa

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

Re: Replication question: How to make a slave a master?

2012-08-24 Thread Reindl Harald

what would be tricky?

remove all relay-logs, remove master.info adn that was it
done this many times in the last years and it takes 30 seconds
if you are fast enough to type the slave-commands

Am 24.08.2012 23:35, schrieb Rick James:
 Pointing the other slave to the new master is a bit tricky.
 
 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Friday, August 24, 2012 8:29 AM
 To: mysql@lists.mysql.com
 Subject: Re: Replication question: How to make a slave a master?



 Am 24.08.2012 17:25, schrieb Richard Reina:
 I have a couple of mysql database slaves and would like to make one
 of
 them be a master as well so that I can set another machine to
 replicate from it.
 Can anyone tell me how I should go about it or know of any howtos for
 this specific task?

 add the same master-conig lines as you did on the other master

 there is no magic, master-configurazion does not have any impact to
 slave-operation and vice versa

 

-- 

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

RE: Replication question: How to make a slave a master?

2012-08-24 Thread Rick James

It's the sequence that is tricky.
1. STOP SLAVE
2. CHANGE MASTER
3. START SLAVE
Do it wrong, and you miss or duplicate replication stuff that happens between 
#1 and #2.
Could you please lay out the precise steps, so that I can understand how 
un-tricky it can be.

 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Friday, August 24, 2012 2:43 PM
 To: mysql@lists.mysql.com
 Subject: Re: Replication question: How to make a slave a master?
 
 what would be tricky?
 
 remove all relay-logs, remove master.info adn that was it done this
 many times in the last years and it takes 30 seconds if you are fast
 enough to type the slave-commands
 
 Am 24.08.2012 23:35, schrieb Rick James:
  Pointing the other slave to the new master is a bit tricky.
 
  -Original Message-
  From: Reindl Harald [mailto:h.rei...@thelounge.net]
  Sent: Friday, August 24, 2012 8:29 AM
  To: mysql@lists.mysql.com
  Subject: Re: Replication question: How to make a slave a master?
 
 
 
  Am 24.08.2012 17:25, schrieb Richard Reina:
  I have a couple of mysql database slaves and would like to make one
  of
  them be a master as well so that I can set another machine to
  replicate from it.
  Can anyone tell me how I should go about it or know of any howtos
  for this specific task?
 
  add the same master-conig lines as you did on the other master
 
  there is no magic, master-configurazion does not have any impact to
  slave-operation and vice versa
 
 
 
 --
 
 Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / CISO / Software-Development
 p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
 icq: 154546673, http://www.thelounge.net/
 
 http://www.thelounge.net/signature.asc.what.htm


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

Re: Replication question: How to make a slave a master?

2012-08-24 Thread Reindl Harald

* stop mysqld
* rm -f mysql-relay-bin*
* rm -f master.info
* rm -f relay-log.info
* start mysqld

so, and now your slave is no longer any slave

to make sure you are binary-identical with the new master
stop the new master, remove all it's BINLOGS, not its
relay-logs, stop the new salve, rsync the data,
start both mywqld and start replication from slave

this is really easy after you understand what
each file in the datadir is supposded to do

Am 24.08.2012 23:53, schrieb Rick James:
 It's the sequence that is tricky.
 1. STOP SLAVE
 2. CHANGE MASTER
 3. START SLAVE
 Do it wrong, and you miss or duplicate replication stuff that happens between 
 #1 and #2.
 Could you please lay out the precise steps, so that I can understand how 
 un-tricky it can be.
 
 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Friday, August 24, 2012 2:43 PM
 To: mysql@lists.mysql.com
 Subject: Re: Replication question: How to make a slave a master?

 what would be tricky?

 remove all relay-logs, remove master.info adn that was it done this
 many times in the last years and it takes 30 seconds if you are fast
 enough to type the slave-commands

 Am 24.08.2012 23:35, schrieb Rick James:
 Pointing the other slave to the new master is a bit tricky.

 -Original Message-
 From: Reindl Harald [mailto:h.rei...@thelounge.net]
 Sent: Friday, August 24, 2012 8:29 AM
 To: mysql@lists.mysql.com
 Subject: Re: Replication question: How to make a slave a master?



 Am 24.08.2012 17:25, schrieb Richard Reina:
 I have a couple of mysql database slaves and would like to make one
 of
 them be a master as well so that I can set another machine to
 replicate from it.
 Can anyone tell me how I should go about it or know of any howtos
 for this specific task?

 add the same master-conig lines as you did on the other master

 there is no magic, master-configurazion does not have any impact to
 slave-operation and vice versa



 --

 Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / CISO / Software-Development
 p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
 icq: 154546673, http://www.thelounge.net/

 http://www.thelounge.net/signature.asc.what.htm
 

-- 

Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / CISO / Software-Development
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

Re: Replication question: How to make a slave a master?

2012-08-24 Thread Mihail Manolov

Are you trying to promote a slave as a new master and replace current master or 
create intermediate slave? If it is the latter all you need to do is to:

1. Stop the slave
2. Add log_slave_updates = 1 in the slave's config file
3. Copy the slave files to the new slave(s)
4. Start your intermediate slave
5. Add replication user on your intermediate slave and allow new slaves to 
replicate
6. Start your slave(s) of intermediate slave
7. Issue following on your new slaves: CHANGE MASTER TO master_log_file = 
'mysql-bin.01', master_log_pos = 4;

On Aug 24, 2012, at 11:25 AM, Richard Reina wrote:

 I have a couple of mysql database slaves and would like to make one of them
 be a master as well so that I can set another machine to replicate from it.
 Can anyone tell me how I should go about it or know of any howtos for this
 specific task?
 
 Thanks,
 
 Richard


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

Re: Replication setup hep

2012-08-02 Thread Ananda Kumar

if the server is offline , what kind of operation happens on it.

On Thu, Aug 2, 2012 at 11:31 AM, Pothanaboyina Trimurthy 
skd.trimur...@gmail.com wrote:

 Hi everyone
  i have 4 mysql servers out of those one server will
 be online always and the remaining will be offline and online. the
 operations takes place at the offline servers i want to replicate the
 data from the offline servers to the online server. for this can any
 one help me which kind of replication suits for this kind of
 situation.

 thanks in advance

 --
 3murthy

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

RE: Replication setup hep

2012-08-02 Thread Rick James

1 Master replicationg to 3 Slaves.
You can use any of the Slaves for readonly (online), or you can not use them 
(offline).
All writes go to the Master.  It will be online for writes, and optionally 
online for reads.

 -Original Message-
 From: Ananda Kumar [mailto:anan...@gmail.com]
 Sent: Wednesday, August 01, 2012 11:20 PM
 To: Pothanaboyina Trimurthy
 Cc: mysql
 Subject: Re: Replication setup hep

 if the server is offline , what kind of operation happens on it.

 On Thu, Aug 2, 2012 at 11:31 AM, Pothanaboyina Trimurthy 
 skd.trimur...@gmail.com wrote:

  Hi everyone
   i have 4 mysql servers out of those one server will
  be online always and the remaining will be offline and online. the
  operations takes place at the offline servers i want to replicate the
  data from the offline servers to the online server. for this can any
  one help me which kind of replication suits for this kind of
  situation.

  thanks in advance

  --
  3murthy

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

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

Re: Replication rings/maatkit (was: HA Scalability w MySQL + SAN + VMWare)

2012-04-02 Thread Wes Modes


 Replication rings are possible but you must design your application to
 take special care to NOT update the same row in multiple nodes of the
 ring at the same time. This is even harder to design and code for than
 splitting writes/reads to master/slaves.

 Also the loss of one node of a replication ring is not as easy to
 recover from as simply promoting one slave to become the new master of
 a replication tree (demoting the recovered former-master to become yet
 another slave) as there may be pending events in the relay logs of the
 lost node that have not yet been relayed to the downstream node.

 I may not have every answer, but I have seen nearly every kind of
 failure.  Everyone else is encouraged to add their views to the
 discussion.


Has anyone used maatkit or Percona to setup circular replication?  How
does it affect this system's reliability and robustness?  Do the tools
help to deal with fail over?

W.

-- 
Wes Modes
Systems Designer, Developer, and Administrator
University Library ITS
University of California, Santa Cruz


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

Re: Replication rings/maatkit (was: HA Scalability w MySQL + SAN + VMWare)

2012-04-02 Thread Eric Bergen

I suggest buying a copy of High Performance MySQL 3rd edition that
just came out. It has a whole chapter on HA that answers a lot of
these questions.

On Mon, Apr 2, 2012 at 6:14 PM, Wes Modes wmo...@ucsc.edu wrote:

 Replication rings are possible but you must design your application to
 take special care to NOT update the same row in multiple nodes of the
 ring at the same time. This is even harder to design and code for than
 splitting writes/reads to master/slaves.

 Also the loss of one node of a replication ring is not as easy to
 recover from as simply promoting one slave to become the new master of
 a replication tree (demoting the recovered former-master to become yet
 another slave) as there may be pending events in the relay logs of the
 lost node that have not yet been relayed to the downstream node.

 I may not have every answer, but I have seen nearly every kind of
 failure.  Everyone else is encouraged to add their views to the
 discussion.


 Has anyone used maatkit or Percona to setup circular replication?  How
 does it affect this system's reliability and robustness?  Do the tools
 help to deal with fail over?

 W.

 --
 Wes Modes
 Systems Designer, Developer, and Administrator
 University Library ITS
 University of California, Santa Cruz


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




-- 
Eric Bergen
eric.ber...@gmail.com
http://www.ebergen.net

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

Re: replication between two tables in same database

2011-10-03 Thread Anupam Karmarkar

Try out functionality you desire with trigger to replicate data

From: Derek Downey de...@orange-pants.com
To: [MySQL] mysql@lists.mysql.com
Sent: Thursday, 29 September 2011 10:46 PM
Subject: Re: replication between two tables in same database

You could look into the sphinx engine http://sphinxsearch.com/about/sphinx/

No experience with this personally though

- Derek
On Sep 29, 2011, at 1:07 PM, Tompkins Neil wrote:

 Yes, unless I can set-up some sort of replication between the two tables.

 On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald h.rei...@thelounge.netwrote:

 please do NOT post off-list!

 so your only workaround is like '%whatever%' currently

 Am 29.09.2011 19:04, schrieb Tompkins Neil:
 The reason I'm using Innodb is because the usage of foreign keys in short
 however, we need a couple of the tables to support FULLTEXT searching which 
 Innodb
 does not support.

 On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald h.rei...@thelounge.netwrote:

 Am 29.09.2011 18:56, schrieb Tompkins Neil:
 Hi

 I've a Innodb and MyISAM table in the SAME database that I wish to
 replicate
 the data between the two because I need to use FULLTEXT searching on
 the MyISAM table.  Is this possible ? If so how do I do it.

 in short: no

 in longer:
 why in the world are you using the wrong engine if you need
 fulltext-search?
 mysql 5.6 semmes to start support this in innodb, but currently not

 replication is based on binlogs and contains database/table so there is
 no dirty trick do this on one server except let run a script and copy
 the table-contents per cronjob

 --

 Mit besten Grüßen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
 icq: 154546673, http://www.thelounge.net/
 http://www.thelounge.net/signature.asc.what.htm

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

Re: replication between two tables in same database

2011-09-30 Thread Peter Boros

You can do this by initially copying the table and setting up triggers
to refresh new data. Looks like you are not looking for replication, but
you are calling it replication. Having 2 tables in a database with the
same content and different indexes/storage engines/partitioning is
totally possible. For the fulltext problem, sphinx is the better
solution.

Peter

On Thu, 2011-09-29 at 22:59 +0100, Tompkins Neil wrote:
 I've now just upgraded to MySQL 5.6 on our test server.  Just trying to find
 a date as to when it will go into production
 
 On Thu, Sep 29, 2011 at 8:10 PM, mos mo...@fastmail.fm wrote:
 
  Derek is right. The Sphynx search engine is much faster than MyISAM's full
  text search engine. It will work on InnoDb tables so you don't have to
  export the data to MyISAM.
 
  Mike
 
  At 01:43 PM 9/29/2011, you wrote:
 
  But I could create an additional myisam table to overcome my problem
  providing I can get the data to synchronise between the two tables
 
  On 29 Sep 2011, at 18:16, Reindl Harald h.rei...@thelounge.net wrote:
 
   so mysql is currently the wrong database for your project
   sad but true, you can not have fulltext-search and innodb this time
  
   Am 29.09.2011 19:15, schrieb Tompkins Neil:
   We've succesfully used FULLTEXT searching on another application that
  does not need Innodb tables.  In addition for
   the FULLTEXT searching we use things like IN BOOLEAN MODE as well -
  so for our project Like %% is not enough.
  
   On Thu, Sep 29, 2011 at 6:13 PM, Reindl Harald 
   h.rei...@thelounge.netmailto:
  h.rei...@thelounge.net** wrote:
  
  you do not need any replication or myisam for
  select * from table where field like '%input%';
  
  for most workloads this is enough and you have not the problem
  with stop-words, minimum input length and so on
  
  Am 29.09.2011 19:07, schrieb Tompkins Neil:
   Yes, unless I can set-up some sort of replication between the two
  tables.
  
   On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald 
  h.rei...@thelounge.net mailto:h.rei...@thelounge.net**wrote:
  
   please do NOT post off-list!
  
   so your only workaround is like '%whatever%' currently
  
   Am 29.09.2011 19:04, schrieb Tompkins Neil:
   The reason I'm using Innodb is because the usage of foreign keys in
  short
   however, we need a couple of the tables to support FULLTEXT searching
  which Innodb
   does not support.
  
  
   On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald 
  h.rei...@thelounge.net mailto:h.rei...@thelounge.net**wrote:
  
  
  
   Am 29.09.2011 18:56, schrieb Tompkins Neil:
   Hi
  
   I've a Innodb and MyISAM table in the SAME database that I wish to
   replicate
   the data between the two because I need to use FULLTEXT searching
  on
   the MyISAM table.  Is this possible ? If so how do I do it.
  
   in short: no
  
   in longer:
   why in the world are you using the wrong engine if you need
   fulltext-search?
   mysql 5.6 semmes to start support this in innodb, but currently not
  
   replication is based on binlogs and contains database/table so there
  is
   no dirty trick do this on one server except let run a script and
  copy
   the table-contents per cronjob
  
  
  
   --
  
   Mit besten GrÃ¼ÃŸen, Reindl Harald
   the lounge interactive design GmbH
   A-1060 Vienna, HofmÃ¼hlgasse 17
   CTO / software-development / cms-solutions
   p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%**203999%2033, m:
  +43 (676) 40 221 40
  tel:%2B43%20%28676%29%2040%**20221%2040
   icq: 154546673, http://www.thelounge.net/
   http://www.thelounge.net/**signature.asc.what.htmhttp://www.thelounge.net/signature.asc.what.htm
  
  
  
  
  --
  
  Mit besten GrÃ¼ÃŸen, Reindl Harald
  the lounge interactive design GmbH
  A-1060 Vienna, HofmÃ¼hlgasse 17
  CTO / software-development / cms-solutions
  p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%**203999%2033,
  m: +43 (676) 40 221 40
  tel:%2B43%20%28676%29%2040%**20221%2040
  icq: 154546673, http://www.thelounge.net/
  
  
   http://www.thelounge.net/**signature.asc.what.htmhttp://www.thelounge.net/signature.asc.what.htm
  
  
  
   --
  
   Mit besten GrÃ¼ÃŸen, Reindl Harald
   the lounge interactive design GmbH
   A-1060 Vienna, HofmÃ¼hlgasse 17
   CTO / software-development / cms-solutions
   p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
   icq: 154546673, http://www.thelounge.net/
  
   http://www.thelounge.net/**signature.asc.what.htmhttp://www.thelounge.net/signature.asc.what.htm
  
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:
  http://lists.mysql.com/mysql?**unsub=mo...@fastmail.fmhttp://lists.mysql.com/mysql?unsub=mo...@fastmail.fm
 
 
 
  --
  MySQL General Mailing List
  For list archives: http://lists.mysql.com/mysql
  To unsubscribe:http://lists.mysql.com/mysql?**unsub=neil.tompkins@**
  googlemail.comhttp://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.com
 
 



--

Re: replication between two tables in same database

2011-09-29 Thread Reindl Harald



Am 29.09.2011 18:56, schrieb Tompkins Neil:
 Hi
 
 I've a Innodb and MyISAM table in the SAME database that I wish to replicate
 the data between the two because I need to use FULLTEXT searching on
 the MyISAM table.  Is this possible ? If so how do I do it.

in short: no

in longer:
why in the world are you using the wrong engine if you need fulltext-search?
mysql 5.6 semmes to start support this in innodb, but currently not

replication is based on binlogs and contains database/table so there is
no dirty trick do this on one server except let run a script and copy
the table-contents per cronjob



signature.asc
Description: OpenPGP digital signature

Re: replication between two tables in same database

2011-09-29 Thread Jim Moseby


Sounds like a job for CREATE TRIGGER to me.  :)
http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html
 
 

 Tompkins Neil neil.tompk...@googlemail.com 9/29/2011 12:56 PM 
Hi

I've a Innodb and MyISAM table in the SAME database that I wish to replicate
the data between the two because I need to use FULLTEXT searching on
the MyISAM table.  Is this possible ? If so how do I do it.

Thanks
Neil

CONFIDENTIALITY NOTICE:  This message is directed to and is for the use of the 
above-noted addressee only, and its contents may be legally privileged or 
confidential.  If the reader of this message is not the intended recipient, you 
are hereby notified that any distribution, dissemination, or copy of this 
message is strictly prohibited.  If you have received this message in error, 
please delete it immediately and notify the sender.  This message is not 
intended to be an electronic signature nor to constitute an agreement of any 
kind under applicable law unless otherwise expressly indicated herein.

Re: replication between two tables in same database

2011-09-29 Thread Tompkins Neil

Yes, unless I can set-up some sort of replication between the two tables.

On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald h.rei...@thelounge.netwrote:

  please do NOT post off-list!

 so your only workaround is like '%whatever%' currently

 Am 29.09.2011 19:04, schrieb Tompkins Neil:
 The reason I'm using Innodb is because the usage of foreign keys in short
 however, we need a couple of the tables to support FULLTEXT searching which 
 Innodb
 does not support.


 On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald h.rei...@thelounge.netwrote:



 Am 29.09.2011 18:56, schrieb Tompkins Neil:
  Hi
 
  I've a Innodb and MyISAM table in the SAME database that I wish to
 replicate
  the data between the two because I need to use FULLTEXT searching on
  the MyISAM table.  Is this possible ? If so how do I do it.

 in short: no

 in longer:
 why in the world are you using the wrong engine if you need
 fulltext-search?
 mysql 5.6 semmes to start support this in innodb, but currently not

 replication is based on binlogs and contains database/table so there is
 no dirty trick do this on one server except let run a script and copy
 the table-contents per cronjob



 --

 Mit besten Grüßen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
 icq: 154546673, http://www.thelounge.net/
 http://www.thelounge.net/signature.asc.what.htm

Re: replication between two tables in same database

2011-09-29 Thread Tompkins Neil

We've succesfully used FULLTEXT searching on another application that does
not need Innodb tables.  In addition for the FULLTEXT searching we use
things like IN BOOLEAN MODE as well - so for our project Like %% is not
enough.

On Thu, Sep 29, 2011 at 6:13 PM, Reindl Harald h.rei...@thelounge.netwrote:

 you do not need any replication or myisam for
 select * from table where field like '%input%';

 for most workloads this is enough and you have not the problem
 with stop-words, minimum input length and so on

 Am 29.09.2011 19:07, schrieb Tompkins Neil:
  Yes, unless I can set-up some sort of replication between the two tables.
 
  On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald h.rei...@thelounge.net
 wrote:
 
   please do NOT post off-list!
 
  so your only workaround is like '%whatever%' currently
 
  Am 29.09.2011 19:04, schrieb Tompkins Neil:
  The reason I'm using Innodb is because the usage of foreign keys in
 short
  however, we need a couple of the tables to support FULLTEXT searching
 which Innodb
  does not support.
 
 
  On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald h.rei...@thelounge.net
 wrote:
 
 
 
  Am 29.09.2011 18:56, schrieb Tompkins Neil:
  Hi
 
  I've a Innodb and MyISAM table in the SAME database that I wish to
  replicate
  the data between the two because I need to use FULLTEXT searching on
  the MyISAM table.  Is this possible ? If so how do I do it.
 
  in short: no
 
  in longer:
  why in the world are you using the wrong engine if you need
  fulltext-search?
  mysql 5.6 semmes to start support this in innodb, but currently not
 
  replication is based on binlogs and contains database/table so there is
  no dirty trick do this on one server except let run a script and copy
  the table-contents per cronjob
 
 
 
  --
 
  Mit besten Grüßen, Reindl Harald
  the lounge interactive design GmbH
  A-1060 Vienna, Hofmühlgasse 17
  CTO / software-development / cms-solutions
  p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
  icq: 154546673, http://www.thelounge.net/
  http://www.thelounge.net/signature.asc.what.htm
 
 
 

 --

 Mit besten Grüßen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
 icq: 154546673, http://www.thelounge.net/

 http://www.thelounge.net/signature.asc.what.htm

Re: replication between two tables in same database

2011-09-29 Thread Reindl Harald

so mysql is currently the wrong database for your project
sad but true, you can not have fulltext-search and innodb this time

Am 29.09.2011 19:15, schrieb Tompkins Neil:
 We've succesfully used FULLTEXT searching on another application that does 
 not need Innodb tables.  In addition for
 the FULLTEXT searching we use things like IN BOOLEAN MODE as well - so for 
 our project Like %% is not enough.
 
 On Thu, Sep 29, 2011 at 6:13 PM, Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.net wrote:
 
 you do not need any replication or myisam for
 select * from table where field like '%input%';
 
 for most workloads this is enough and you have not the problem
 with stop-words, minimum input length and so on
 
 Am 29.09.2011 19:07, schrieb Tompkins Neil:
  Yes, unless I can set-up some sort of replication between the two 
 tables.
 
  On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.netwrote:
 
   please do NOT post off-list!
 
  so your only workaround is like '%whatever%' currently
 
  Am 29.09.2011 19:04, schrieb Tompkins Neil:
  The reason I'm using Innodb is because the usage of foreign keys in 
 short
  however, we need a couple of the tables to support FULLTEXT searching 
 which Innodb
  does not support.
 
 
  On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.netwrote:
 
 
 
  Am 29.09.2011 18:56, schrieb Tompkins Neil:
  Hi
 
  I've a Innodb and MyISAM table in the SAME database that I wish to
  replicate
  the data between the two because I need to use FULLTEXT searching on
  the MyISAM table.  Is this possible ? If so how do I do it.
 
  in short: no
 
  in longer:
  why in the world are you using the wrong engine if you need
  fulltext-search?
  mysql 5.6 semmes to start support this in innodb, but currently not
 
  replication is based on binlogs and contains database/table so there 
 is
  no dirty trick do this on one server except let run a script and copy
  the table-contents per cronjob
 
 
 
  --
 
  Mit besten Grüßen, Reindl Harald
  the lounge interactive design GmbH
  A-1060 Vienna, Hofmühlgasse 17
  CTO / software-development / cms-solutions
  p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%203999%2033, m: +43 
 (676) 40 221 40
 tel:%2B43%20%28676%29%2040%20221%2040
  icq: 154546673, http://www.thelounge.net/
  http://www.thelounge.net/signature.asc.what.htm
 
 
 
 
 --
 
 Mit besten Grüßen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%203999%2033, m: +43 
 (676) 40 221 40
 tel:%2B43%20%28676%29%2040%20221%2040
 icq: 154546673, http://www.thelounge.net/
 
 http://www.thelounge.net/signature.asc.what.htm
 
 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



signature.asc
Description: OpenPGP digital signature

Re: replication between two tables in same database

2011-09-29 Thread Derek Downey

You could look into the sphinx engine http://sphinxsearch.com/about/sphinx/

No experience with this personally though

- Derek
On Sep 29, 2011, at 1:07 PM, Tompkins Neil wrote:

 Yes, unless I can set-up some sort of replication between the two tables.
 
 On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald h.rei...@thelounge.netwrote:
 
 please do NOT post off-list!
 
 so your only workaround is like '%whatever%' currently
 
 Am 29.09.2011 19:04, schrieb Tompkins Neil:
 The reason I'm using Innodb is because the usage of foreign keys in short
 however, we need a couple of the tables to support FULLTEXT searching which 
 Innodb
 does not support.
 
 
 On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald h.rei...@thelounge.netwrote:
 
 
 
 Am 29.09.2011 18:56, schrieb Tompkins Neil:
 Hi
 
 I've a Innodb and MyISAM table in the SAME database that I wish to
 replicate
 the data between the two because I need to use FULLTEXT searching on
 the MyISAM table.  Is this possible ? If so how do I do it.
 
 in short: no
 
 in longer:
 why in the world are you using the wrong engine if you need
 fulltext-search?
 mysql 5.6 semmes to start support this in innodb, but currently not
 
 replication is based on binlogs and contains database/table so there is
 no dirty trick do this on one server except let run a script and copy
 the table-contents per cronjob
 
 
 
 --
 
 Mit besten Grüßen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
 icq: 154546673, http://www.thelounge.net/
 http://www.thelounge.net/signature.asc.what.htm
 
 


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

Re: replication between two tables in same database

2011-09-29 Thread Neil Tompkins

But I could create an additional myisam table to overcome my problem providing 
I can get the data to synchronise between the two tables

On 29 Sep 2011, at 18:16, Reindl Harald h.rei...@thelounge.net wrote:

 so mysql is currently the wrong database for your project
 sad but true, you can not have fulltext-search and innodb this time
 
 Am 29.09.2011 19:15, schrieb Tompkins Neil:
 We've succesfully used FULLTEXT searching on another application that does 
 not need Innodb tables.  In addition for
 the FULLTEXT searching we use things like IN BOOLEAN MODE as well - so for 
 our project Like %% is not enough.
 
 On Thu, Sep 29, 2011 at 6:13 PM, Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.net wrote:
 
you do not need any replication or myisam for
select * from table where field like '%input%';
 
for most workloads this is enough and you have not the problem
with stop-words, minimum input length and so on
 
Am 29.09.2011 19:07, schrieb Tompkins Neil:
 Yes, unless I can set-up some sort of replication between the two tables.
 
 On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.netwrote:
 
 please do NOT post off-list!
 
 so your only workaround is like '%whatever%' currently
 
 Am 29.09.2011 19:04, schrieb Tompkins Neil:
 The reason I'm using Innodb is because the usage of foreign keys in short
 however, we need a couple of the tables to support FULLTEXT searching 
 which Innodb
 does not support.
 
 
 On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald h.rei...@thelounge.net 
 mailto:h.rei...@thelounge.netwrote:
 
 
 
 Am 29.09.2011 18:56, schrieb Tompkins Neil:
 Hi
 
 I've a Innodb and MyISAM table in the SAME database that I wish to
 replicate
 the data between the two because I need to use FULLTEXT searching on
 the MyISAM table.  Is this possible ? If so how do I do it.
 
 in short: no
 
 in longer:
 why in the world are you using the wrong engine if you need
 fulltext-search?
 mysql 5.6 semmes to start support this in innodb, but currently not
 
 replication is based on binlogs and contains database/table so there is
 no dirty trick do this on one server except let run a script and copy
 the table-contents per cronjob
 
 
 
 --
 
 Mit besten Grüßen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%203999%2033, m: +43 
 (676) 40 221 40
tel:%2B43%20%28676%29%2040%20221%2040
 icq: 154546673, http://www.thelounge.net/
 http://www.thelounge.net/signature.asc.what.htm
 
 
 
 
--
 
Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%203999%2033, m: +43 
 (676) 40 221 40
tel:%2B43%20%28676%29%2040%20221%2040
icq: 154546673, http://www.thelounge.net/
 
http://www.thelounge.net/signature.asc.what.htm
 
 
 
 -- 
 
 Mit besten Grüßen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, Hofmühlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
 icq: 154546673, http://www.thelounge.net/
 
 http://www.thelounge.net/signature.asc.what.htm
 

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

Re: replication between two tables in same database

2011-09-29 Thread mos

Derek is right. The Sphynx search engine is much 
faster than MyISAM's full text search engine. It 
will work on InnoDb tables so you don't have to export the data to MyISAM.


Mike

At 01:43 PM 9/29/2011, you wrote:
But I could create an additional myisam table to 
overcome my problem providing I can get the data 
to synchronise between the two tables


On 29 Sep 2011, at 18:16, Reindl Harald h.rei...@thelounge.net wrote:

 so mysql is currently the wrong database for your project
 sad but true, you can not have fulltext-search and innodb this time

 Am 29.09.2011 19:15, schrieb Tompkins Neil:
 We've succesfully used FULLTEXT searching on 
another application that does not need Innodb tables.  In addition for
 the FULLTEXT searching we use things like 
IN BOOLEAN MODE as well - so for our project Like %% is not enough.


 On Thu, Sep 29, 2011 at 6:13 PM, Reindl 
Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.net wrote:


you do not need any replication or myisam for
select * from table where field like '%input%';

for most workloads this is enough and you have not the problem
with stop-words, minimum input length and so on

Am 29.09.2011 19:07, schrieb Tompkins Neil:
 Yes, unless I can set-up some sort of replication between the two tables.

 On Thu, Sep 29, 2011 at 6:05 PM, Reindl 
Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.netwrote:


 please do NOT post off-list!

 so your only workaround is like '%whatever%' currently

 Am 29.09.2011 19:04, schrieb Tompkins Neil:
 The reason I'm using Innodb is because the 
usage of foreign keys in short
 however, we need a couple of the tables to 
support FULLTEXT searching which Innodb

 does not support.


 On Thu, Sep 29, 2011 at 6:01 PM, Reindl 
Harald h.rei...@thelounge.net mailto:h.rei...@thelounge.netwrote:




 Am 29.09.2011 18:56, schrieb Tompkins Neil:
 Hi

 I've a Innodb and MyISAM table in the SAME database that I wish to
 replicate
 the data between the two because I need to use FULLTEXT searching on
 the MyISAM table.  Is this possible ? If so how do I do it.

 in short: no

 in longer:
 why in the world are you using the wrong engine if you need
 fulltext-search?
 mysql 5.6 semmes to start support this in innodb, but currently not

 replication is based on binlogs and contains database/table so there is
 no dirty trick do this on one server except let run a script and copy
 the table-contents per cronjob



 --

 Mit besten GrÃ¼Ãen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, HofmÃ¼hlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33 
tel:%2B43%20%281%29%20595%203999%2033, m: +43 (676) 40 221 40

tel:%2B43%20%28676%29%2040%20221%2040
 icq: 154546673, http://www.thelounge.net/
 http://www.thelounge.net/signature.asc.what.htm




--

Mit besten GrÃ¼Ãen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, HofmÃ¼hlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33 
tel:%2B43%20%281%29%20595%203999%2033, m: +43 (676) 40 221 40

tel:%2B43%20%28676%29%2040%20221%2040
icq: 154546673, http://www.thelounge.net/

http://www.thelounge.net/signature.asc.what.htm



 --

 Mit besten GrÃ¼Ãen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, HofmÃ¼hlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
 icq: 154546673, http://www.thelounge.net/

 http://www.thelounge.net/signature.asc.what.htm


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



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

Re: replication between two tables in same database

2011-09-29 Thread Neil Tompkins

I'm not sure it would work in my environment of IIS and MySQL ?

On 29 Sep 2011, at 20:10, mos mo...@fastmail.fm wrote:

 Derek is right. The Sphynx search engine is much faster than MyISAM's full 
 text search engine. It will work on InnoDb tables so you don't have to export 
 the data to MyISAM.
 
 Mike
 
 At 01:43 PM 9/29/2011, you wrote:
 But I could create an additional myisam table to overcome my problem 
 providing I can get the data to synchronise between the two tables
 
 On 29 Sep 2011, at 18:16, Reindl Harald h.rei...@thelounge.net wrote:
 
  so mysql is currently the wrong database for your project
  sad but true, you can not have fulltext-search and innodb this time
 
  Am 29.09.2011 19:15, schrieb Tompkins Neil:
  We've succesfully used FULLTEXT searching on another application that 
  does not need Innodb tables.  In addition for
  the FULLTEXT searching we use things like IN BOOLEAN MODE as well - so 
  for our project Like %% is not enough.
 
  On Thu, Sep 29, 2011 at 6:13 PM, Reindl Harald h.rei...@thelounge.net 
  mailto:h.rei...@thelounge.net wrote:
 
 you do not need any replication or myisam for
 select * from table where field like '%input%';
 
 for most workloads this is enough and you have not the problem
 with stop-words, minimum input length and so on
 
 Am 29.09.2011 19:07, schrieb Tompkins Neil:
  Yes, unless I can set-up some sort of replication between the two tables.
 
  On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald h.rei...@thelounge.net 
  mailto:h.rei...@thelounge.netwrote:
 
  please do NOT post off-list!
 
  so your only workaround is like '%whatever%' currently
 
  Am 29.09.2011 19:04, schrieb Tompkins Neil:
  The reason I'm using Innodb is because the usage of foreign keys in 
  short
  however, we need a couple of the tables to support FULLTEXT searching 
  which Innodb
  does not support.
 
 
  On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald h.rei...@thelounge.net 
  mailto:h.rei...@thelounge.netwrote:
 
 
 
  Am 29.09.2011 18:56, schrieb Tompkins Neil:
  Hi
 
  I've a Innodb and MyISAM table in the SAME database that I wish to
  replicate
  the data between the two because I need to use FULLTEXT searching on
  the MyISAM table.  Is this possible ? If so how do I do it.
 
  in short: no
 
  in longer:
  why in the world are you using the wrong engine if you need
  fulltext-search?
  mysql 5.6 semmes to start support this in innodb, but currently not
 
  replication is based on binlogs and contains database/table so there is
  no dirty trick do this on one server except let run a script and copy
  the table-contents per cronjob
 
 
 
  --
 
  Mit besten GrÃ¼Ãen, Reindl Harald
  the lounge interactive design GmbH
  A-1060 Vienna, HofmÃ¼hlgasse 17
  CTO / software-development / cms-solutions
  p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%203999%2033, m: +43 
  (676) 40 221 40
 tel:%2B43%20%28676%29%2040%20221%2040
  icq: 154546673, http://www.thelounge.net/
  http://www.thelounge.net/signature.asc.what.htm
 
 
 
 
 --
 
 Mit besten GrÃ¼Ãen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, HofmÃ¼hlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%203999%2033, m: +43 
  (676) 40 221 40
 tel:%2B43%20%28676%29%2040%20221%2040
 icq: 154546673, http://www.thelounge.net/
 
 http://www.thelounge.net/signature.asc.what.htm
 
 
 
  --
 
  Mit besten GrÃ¼Ãen, Reindl Harald
  the lounge interactive design GmbH
  A-1060 Vienna, HofmÃ¼hlgasse 17
  CTO / software-development / cms-solutions
  p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
  icq: 154546673, http://www.thelounge.net/
 
  http://www.thelounge.net/signature.asc.what.htm
 
 
 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:http://lists.mysql.com/mysql?unsub=mo...@fastmail.fm
 
 
 -- 
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?unsub=neil.tompk...@googlemail.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: replication between two tables in same database

2011-09-29 Thread Neil Tompkins

Am thinking now that it might be best to use MySQL 5.6 as this is a new project 
still in development and we will have the new FullText Search with Innodb

On 29 Sep 2011, at 18:43, Andrew Moore eroomy...@gmail.com wrote:

 Hey Neil, I read your question too quickly and jumped to the conclusion you 
 weren't sure MyISAM replicates in a mixed storage engine environment. 
 However, you could still potentially replicate the data using standard 
 replication and even on the same server (different instances). 
 
 InstanceA --- InstanceB
 MyISAM   InnoDB
 
 You could use Triggers but be careful if you decide to do that because 
 triggers and replication don't play well together in some circumstances.
 
 As a side note and something that was already mentioned to you, MySQL are 
 working on 5.6 and as part of that release InnoDB will have it's own 
 implementation of FT indexing.
 
 HTH 
 
 Andy
 
 On Thu, Sep 29, 2011 at 6:00 PM, Tompkins Neil neil.tompk...@googlemail.com 
 wrote:
 Can you give me any pointers ?
 
 
 On Thu, Sep 29, 2011 at 5:59 PM, Andrew Moore eroomy...@gmail.com wrote:
 Hey Neil, it sure is possible through standard replication configuration.
 
 Hth Andy
 
 On Sep 29, 2011 5:57 PM, Tompkins Neil neil.tompk...@googlemail.com wrote:
  Hi
  
  I've a Innodb and MyISAM table in the SAME database that I wish to replicate
  the data between the two because I need to use FULLTEXT searching on
  the MyISAM table. Is this possible ? If so how do I do it.
  
  Thanks
  Neil

Re: replication between two tables in same database

2011-09-29 Thread Tompkins Neil

I've now just upgraded to MySQL 5.6 on our test server.  Just trying to find
a date as to when it will go into production

On Thu, Sep 29, 2011 at 8:10 PM, mos mo...@fastmail.fm wrote:

 Derek is right. The Sphynx search engine is much faster than MyISAM's full
 text search engine. It will work on InnoDb tables so you don't have to
 export the data to MyISAM.

 Mike

 At 01:43 PM 9/29/2011, you wrote:

 But I could create an additional myisam table to overcome my problem
 providing I can get the data to synchronise between the two tables

 On 29 Sep 2011, at 18:16, Reindl Harald h.rei...@thelounge.net wrote:

  so mysql is currently the wrong database for your project
  sad but true, you can not have fulltext-search and innodb this time
 
  Am 29.09.2011 19:15, schrieb Tompkins Neil:
  We've succesfully used FULLTEXT searching on another application that
 does not need Innodb tables.  In addition for
  the FULLTEXT searching we use things like IN BOOLEAN MODE as well -
 so for our project Like %% is not enough.
 
  On Thu, Sep 29, 2011 at 6:13 PM, Reindl Harald 
  h.rei...@thelounge.netmailto:
 h.rei...@thelounge.net** wrote:
 
 you do not need any replication or myisam for
 select * from table where field like '%input%';
 
 for most workloads this is enough and you have not the problem
 with stop-words, minimum input length and so on
 
 Am 29.09.2011 19:07, schrieb Tompkins Neil:
  Yes, unless I can set-up some sort of replication between the two
 tables.
 
  On Thu, Sep 29, 2011 at 6:05 PM, Reindl Harald 
 h.rei...@thelounge.net mailto:h.rei...@thelounge.net**wrote:
 
  please do NOT post off-list!
 
  so your only workaround is like '%whatever%' currently
 
  Am 29.09.2011 19:04, schrieb Tompkins Neil:
  The reason I'm using Innodb is because the usage of foreign keys in
 short
  however, we need a couple of the tables to support FULLTEXT searching
 which Innodb
  does not support.
 
 
  On Thu, Sep 29, 2011 at 6:01 PM, Reindl Harald 
 h.rei...@thelounge.net mailto:h.rei...@thelounge.net**wrote:
 
 
 
  Am 29.09.2011 18:56, schrieb Tompkins Neil:
  Hi
 
  I've a Innodb and MyISAM table in the SAME database that I wish to
  replicate
  the data between the two because I need to use FULLTEXT searching
 on
  the MyISAM table.  Is this possible ? If so how do I do it.
 
  in short: no
 
  in longer:
  why in the world are you using the wrong engine if you need
  fulltext-search?
  mysql 5.6 semmes to start support this in innodb, but currently not
 
  replication is based on binlogs and contains database/table so there
 is
  no dirty trick do this on one server except let run a script and
 copy
  the table-contents per cronjob
 
 
 
  --
 
  Mit besten GrÃ¼ÃŸen, Reindl Harald
  the lounge interactive design GmbH
  A-1060 Vienna, HofmÃ¼hlgasse 17
  CTO / software-development / cms-solutions
  p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%**203999%2033, m:
 +43 (676) 40 221 40
 tel:%2B43%20%28676%29%2040%**20221%2040
  icq: 154546673, http://www.thelounge.net/
  http://www.thelounge.net/**signature.asc.what.htmhttp://www.thelounge.net/signature.asc.what.htm
 
 
 
 
 --
 
 Mit besten GrÃ¼ÃŸen, Reindl Harald
 the lounge interactive design GmbH
 A-1060 Vienna, HofmÃ¼hlgasse 17
 CTO / software-development / cms-solutions
 p: +43 (1) 595 3999 33 tel:%2B43%20%281%29%20595%**203999%2033,
 m: +43 (676) 40 221 40
 tel:%2B43%20%28676%29%2040%**20221%2040
 icq: 154546673, http://www.thelounge.net/
 
 
  http://www.thelounge.net/**signature.asc.what.htmhttp://www.thelounge.net/signature.asc.what.htm
 
 
 
  --
 
  Mit besten GrÃ¼ÃŸen, Reindl Harald
  the lounge interactive design GmbH
  A-1060 Vienna, HofmÃ¼hlgasse 17
  CTO / software-development / cms-solutions
  p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
  icq: 154546673, http://www.thelounge.net/
 
  http://www.thelounge.net/**signature.asc.what.htmhttp://www.thelounge.net/signature.asc.what.htm
 

 --
 MySQL General Mailing List
 For list archives: http://lists.mysql.com/mysql
 To unsubscribe:
 http://lists.mysql.com/mysql?**unsub=mo...@fastmail.fmhttp://lists.mysql.com/mysql?unsub=mo...@fastmail.fm



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

Re: Replication issue

2011-02-16 Thread Suresh Kuna

Run the change master again to get the relay logs from master server again.

On Wed, Feb 16, 2011 at 4:50 PM, Carl c...@etrak-plus.com wrote:

 I am running master - master replication between two locations using MySQL
 version 5.1.41 on Slackware Linux 13 (64bit).

 The problem from show slave status is:

   Last_Error: Relay log read failure: Could not parse relay
 log event entry. The possible reasons are: the master's binary log is
 corrupted (you can check this by running 'mysqlbinlog' on the binary log),
 the slave's relay log is corrupted (you can check this by running
 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's
 or slave's MySQL code. If you want to check the master's binary log or
 slave's relay log, you will be able to know their names by issuing 'SHOW
 SLAVE STATUS' on this slave.
 Skip_Counter: 1
  Exec_Master_Log_Pos: 552321409
  Relay_Log_Space: 165412833
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading
 data from binary log: 'log event entry exceeded max_allowed_packet; Increase
 max_allowed_packet on master'
   Last_SQL_Errno: 1594
   Last_SQL_Error: Relay log read failure: Could not parse relay
 log event entry. The possible reasons are: the master's binary log is
 corrupted (you can check this by running 'mysqlbinlog' on the binary log),
 the slave's relay log is corrupted (you can check this by running
 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's
 or slave's MySQL code. If you want to check the master's binary log or
 slave's relay log, you will be able to know their names by issuing 'SHOW
 SLAVE STATUS' on this slave.

 I have tried telling it to skip that transaction (set global
 sql_slave_skip_counter = 1) to no avail.

 From what I have been able to determine from searching the Internet, it
 appears that the replication is failing replicating blobs ahich are
 basically jpg's of members.  If I understand the problem, it is caused by
 blob containing a character which is the same character that is used to mark
 the end of a transaction in the bin log.

 My questions: 1) Is this a reasonable/correct analysis and 2) how do I work
 around the issue?

 Thanks,

 Carl







-- 
Thanks
Suresh Kuna
MySQL DBA

Re: Replication issue

2011-02-16 Thread Reindl Harald

Got fatal error 1236 from master when reading data from binary log:
'log event entry exceeded max_allowed_packet; Increase max_allowed_packet on 
master

So do this in your my.cnf :-)

Forget workarounds to solve replication errors
and re-init you replication if you will be sure
it is really consistent

Am 16.02.2011 12:20, schrieb Carl:
 I am running master - master replication between two locations using MySQL 
 version 5.1.41 
 on Slackware Linux 13 (64bit).
 
 The problem from show slave status is:
 
Last_Error: Relay log read failure: Could not parse relay 
 log event entry. The possible reasons are: the master's binary log is 
 corrupted (you can check this by running 'mysqlbinlog' on the binary log), 
 the slave's relay log is corrupted (you can check this by running 
 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's 
 or slave's MySQL code. If you want to check the master's binary log or 
 slave's relay log, you will be able to know their names by issuing 'SHOW 
 SLAVE STATUS' on this slave.
  Skip_Counter: 1
   Exec_Master_Log_Pos: 552321409
   Relay_Log_Space: 165412833
   Until_Condition: None
Until_Log_File:
 Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
   Master_SSL_Cert:
 Master_SSL_Cipher:
Master_SSL_Key:
 Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
 Last_IO_Errno: 1236
 Last_IO_Error: Got fatal error 1236 from master when reading 
 data from binary log: 'log event entry exceeded max_allowed_packet; Increase 
 max_allowed_packet on master'
Last_SQL_Errno: 1594
Last_SQL_Error: Relay log read failure: Could not parse relay 
 log event entry. The possible reasons are: the master's binary log is 
 corrupted (you can check this by running 'mysqlbinlog' on the binary log), 
 the slave's relay log is corrupted (you can check this by running 
 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's 
 or slave's MySQL code. If you want to check the master's binary log or 
 slave's relay log, you will be able to know their names by issuing 'SHOW 
 SLAVE STATUS' on this slave.
 
 I have tried telling it to skip that transaction (set global 
 sql_slave_skip_counter = 1) to no avail.
 
 From what I have been able to determine from searching the Internet, it 
 appears that the replication is failing replicating blobs ahich are basically 
 jpg's of members.  If I understand the problem, it is caused by blob 
 containing a character which is the same character that is used to mark the 
 end of a transaction in the bin log.
 
 My questions: 1) Is this a reasonable/correct analysis and 2) how do I work 
 around the issue?
 
 Thanks,
 
 Carl
 
 
 
 
 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/



signature.asc
Description: OpenPGP digital signature

Re: Replication issue

2011-02-16 Thread Elizabeth Mattijsen

First make sure that the max_allowed_packet setting is the same on both 
masters.

Make sure that setting is active on the slave in question.  Then start 
replication or bounce the master (not sure which I did to fix this the last 
time I ran into this).


Elizabeth Mattijsen
=
On Feb 16, 2011, at 12:20 PM, Carl wrote:
 I am running master - master replication between two locations using MySQL 
 version 5.1.41 on Slackware Linux 13 (64bit).
 
 The problem from show slave status is:
 
   Last_Error: Relay log read failure: Could not parse relay 
 log event entry. The possible reasons are: the master's binary log is 
 corrupted (you can check this by running 'mysqlbinlog' on the binary log), 
 the slave's relay log is corrupted (you can check this by running 
 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's 
 or slave's MySQL code. If you want to check the master's binary log or 
 slave's relay log, you will be able to know their names by issuing 'SHOW 
 SLAVE STATUS' on this slave.
 Skip_Counter: 1
  Exec_Master_Log_Pos: 552321409
  Relay_Log_Space: 165412833
  Until_Condition: None
   Until_Log_File:
Until_Log_Pos: 0
   Master_SSL_Allowed: No
   Master_SSL_CA_File:
   Master_SSL_CA_Path:
  Master_SSL_Cert:
Master_SSL_Cipher:
   Master_SSL_Key:
Seconds_Behind_Master: NULL
 Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 1236
Last_IO_Error: Got fatal error 1236 from master when reading 
 data from binary log: 'log event entry exceeded max_allowed_packet; Increase 
 max_allowed_packet on master'
   Last_SQL_Errno: 1594
   Last_SQL_Error: Relay log read failure: Could not parse relay 
 log event entry. The possible reasons are: the master's binary log is 
 corrupted (you can check this by running 'mysqlbinlog' on the binary log), 
 the slave's relay log is corrupted (you can check this by running 
 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's 
 or slave's MySQL code. If you want to check the master's binary log or 
 slave's relay log, you will be able to know their names by issuing 'SHOW 
 SLAVE STATUS' on this slave.
 
 I have tried telling it to skip that transaction (set global 
 sql_slave_skip_counter = 1) to no avail.
 
 From what I have been able to determine from searching the Internet, it 
 appears that the replication is failing replicating blobs ahich are basically 
 jpg's of members.  If I understand the problem, it is caused by blob 
 containing a character which is the same character that is used to mark the 
 end of a transaction in the bin log.
 
 My questions: 1) Is this a reasonable/correct analysis and 2) how do I work 
 around the issue?
 
 Thanks,
 
 Carl
 
 
 
 


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

Re: Replication issue

2011-02-16 Thread Carl


The max_allowed_packet setting is the same on both.

I have tried restarting the slave... didn't work.  I can bounce the master.

Thanks,

Carl


- Original Message - 
From: Elizabeth Mattijsen l...@dijkmat.nl

To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, February 16, 2011 6:23 AM
Subject: Re: Replication issue


First make sure that the max_allowed_packet setting is the same on both 
masters.


Make sure that setting is active on the slave in question.  Then start 
replication or bounce the master (not sure which I did to fix this the last 
time I ran into this).



Elizabeth Mattijsen
=
On Feb 16, 2011, at 12:20 PM, Carl wrote:
I am running master - master replication between two locations using MySQL 
version 5.1.41 on Slackware Linux 13 (64bit).


The problem from show slave status is:

  Last_Error: Relay log read failure: Could not parse 
relay log event entry. The possible reasons are: the master's binary log 
is corrupted (you can check this by running 'mysqlbinlog' on the binary 
log), the slave's relay log is corrupted (you can check this by running 
'mysqlbinlog' on the relay log), a network problem, or a bug in the 
master's or slave's MySQL code. If you want to check the master's binary 
log or slave's relay log, you will be able to know their names by issuing 
'SHOW SLAVE STATUS' on this slave.

Skip_Counter: 1
 Exec_Master_Log_Pos: 552321409
 Relay_Log_Space: 165412833
 Until_Condition: None
  Until_Log_File:
   Until_Log_Pos: 0
  Master_SSL_Allowed: No
  Master_SSL_CA_File:
  Master_SSL_CA_Path:
 Master_SSL_Cert:
   Master_SSL_Cipher:
  Master_SSL_Key:
   Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
   Last_IO_Errno: 1236
   Last_IO_Error: Got fatal error 1236 from master when 
reading data from binary log: 'log event entry exceeded 
max_allowed_packet; Increase max_allowed_packet on master'

  Last_SQL_Errno: 1594
  Last_SQL_Error: Relay log read failure: Could not parse 
relay log event entry. The possible reasons are: the master's binary log 
is corrupted (you can check this by running 'mysqlbinlog' on the binary 
log), the slave's relay log is corrupted (you can check this by running 
'mysqlbinlog' on the relay log), a network problem, or a bug in the 
master's or slave's MySQL code. If you want to check the master's binary 
log or slave's relay log, you will be able to know their names by issuing 
'SHOW SLAVE STATUS' on this slave.


I have tried telling it to skip that transaction (set global 
sql_slave_skip_counter = 1) to no avail.


From what I have been able to determine from searching the Internet, it 
appears that the replication is failing replicating blobs ahich are 
basically jpg's of members.  If I understand the problem, it is caused by 
blob containing a character which is the same character that is used to 
mark the end of a transaction in the bin log.


My questions: 1) Is this a reasonable/correct analysis and 2) how do I 
work around the issue?


Thanks,

Carl








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

Re: Replication issue

2011-02-16 Thread Carl


I am not quite certain I understand your suggestion:

Forget workarounds to solve replication errors
and re-init you replication if you will be sure
it is really consistent

When you say re-init the replication, are you saying to restart the slave in 
question from a good copy of the master that I know to be good?


Just trying to be really careful.

Thanks,

Carl


- Original Message - 
From: Reindl Harald h.rei...@thelounge.net

To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, February 16, 2011 6:24 AM
Subject: Re: Replication issue



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

Re: Replication issue

2011-02-16 Thread Reindl Harald

Am 16.02.2011 12:33, schrieb Carl:
 The max_allowed_packet setting is the same on both.

the question is how large the setting is
we have 200M on all machines

 I have tried restarting the slave... didn't work

after replication errors you should every time

* stop the slave
* hot rsync the dadadir on the master
* stop the master
* cold rsync to get last changes
* remove binlog files in datadir und backup
* start the master
* rsync the backup to the slave
* start the slave
* restart replication

this way the downtime of the master is very short because
the second rsync must only copy changes since the first run



signature.asc
Description: OpenPGP digital signature

Re: Replication issue

2011-02-16 Thread Reindl Harald

Am 16.02.2011 12:36, schrieb Carl:
 are you saying to restart the slave in question from a good copy of the
 master that I know to be good?

yes!

there is a reason why the salve stops to work and in my opinion
the only save way to get a 100% clean slave is clone it again
from the stopped master



signature.asc
Description: OpenPGP digital signature

Re: Replication, log info

2011-02-16 Thread Johan De Meersman

On Wed, Feb 16, 2011 at 12:23 PM, Carl c...@etrak-plus.com wrote:

 110216  5:15:20 [ERROR] Error reading packet from server: log event entry
 exceeded max_allowed_packet; Increase
 max_allowed_packet on master ( server_errno=1236)


This seems to be the major player, here. I would make sure to increase the
setting identically on both sides, though.

I'm not entirely sure how this would happen - maybe due to making a
borderline-sized query deterministic?


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

2011-02-16 Thread Carl


are you saying to restart the slave in question from a good copy of the
master that I know to be good?


Reindl Harald replied:

yes!

there is a reason why the salve stops to work and in my opinion
the only save way to get a 100% clean slave is clone it again
from the stopped master

Carl:

I was hoping to avoid that because it approximately 24 hours to move the 
master data to the slave.
I know that is the only way to be certain they are sync'd but is there any 
other way?


Thanks,

Carl


- Original Message - 
From: Reindl Harald h.rei...@thelounge.net

To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, February 16, 2011 6:40 AM
Subject: Re: Replication issue



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

Re: Replication issue

2011-02-16 Thread Reindl Harald

I can not believe that this would take 24 hours
since rsync with compression is very efficient
and on the other hand - who cares, the master
is not down if you do this in the order i described

Am 16.02.2011 12:57, schrieb Carl:
 are you saying to restart the slave in question from a good copy of the
 master that I know to be good?
 
 Reindl Harald replied:
 
 yes!
 
 there is a reason why the salve stops to work and in my opinion
 the only save way to get a 100% clean slave is clone it again
 from the stopped master
 
 Carl:
 
 I was hoping to avoid that because it approximately 24 hours to move the 
 master data to the slave.
 I know that is the only way to be certain they are sync'd but is there any 
 other way?



signature.asc
Description: OpenPGP digital signature

Re: Replication issue

2011-02-16 Thread Carl

I was describing how long it takes to do a mysqldump, move the data, load 
the data in the slave and then restart the slave.  I have never used the 
rsync process... I will try it out in the in the middle of the night when I 
have time to recover from a screwup.  Who says systems people need sleep!


Thanks,

Carl
- Original Message - 
From: Reindl Harald h.rei...@thelounge.net

To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, February 16, 2011 7:02 AM
Subject: Re: Replication issue



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

Re: Replication, log info

2011-02-16 Thread Carl

It won't hurt anything to change the mac_allowed_packet size so I will increase 
it (it is set to 80MB, I will double it and see what happens it the future.)

Does anyone know if there are issues replicating blobs (I read yesterday that 
these sometimes cause problems)?  I am just trying to see if I am setting 
myself up for future problems.

Thanks,

Carl
  - Original Message - 
  From: Johan De Meersman 
  To: Carl 
  Cc: mysql@lists.mysql.com 
  Sent: Wednesday, February 16, 2011 6:55 AM
  Subject: Re: Replication, log info




  On Wed, Feb 16, 2011 at 12:23 PM, Carl c...@etrak-plus.com wrote:

110216  5:15:20 [ERROR] Error reading packet from server: log event entry 
exceeded max_allowed_packet; Increase
max_allowed_packet on master ( server_errno=1236)

  This seems to be the major player, here. I would make sure to increase the 
setting identically on both sides, though.

  I'm not entirely sure how this would happen - maybe due to making a 
borderline-sized query deterministic?


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

2011-02-16 Thread Reindl Harald



Am 16.02.2011 13:39, schrieb Carl:
 I was describing how long it takes to do a mysqldump, move the data, 
 load the data in the slave and then restart the slave.  

I would never do this with dumps because

* text-files - *br*
* size
* overhead

Really important is that you stop the slave before
you stop the master so no newer files can be
on the slave and you probably are very fast
rsync the backup from the master because here also
only diffs going over the network

Below my default-script for re-init a mysql-replication
make sure modify datadir and service-comamnd on non redhat

after that you have under /datadir-bkp/ a 100% consistent copy
without any binlogs and a running master with a fresh binlog

use --compress while rsync this backup to your slave
_

echo 
echo prepare
date
rsync --times --perms --owner --group --recursive --delete-after /datadir/ 
/datadir-bkp/
date
echo 
echo 

if ([ $1 != really ])
 then
  echo Please use 'really' as Param to make the cold backup
  exit
fi

echo stopping mysqld and make cold backup
date
echo 
echo 

service mysqld stop
cd /datadir/
rm -f /datadir/bin*
rsync --progress --times --perms --owner --group --recursive --delete-after 
/datadir/ /datadir-bkp/
service mysqld start


echo 
echo 
echo backup finsihed and mysqld startet
date
echo 
echo 


I have never used the rsync process... I will try it out in the in the middle 
of the night when I have
 time to recover from a screwup.  Who says systems people need sleep!
 
 Thanks,
 
 Carl
 - Original Message - From: Reindl Harald h.rei...@thelounge.net
 To: Carl c...@etrak-plus.com
 Cc: mysql@lists.mysql.com
 Sent: Wednesday, February 16, 2011 7:02 AM
 Subject: Re: Replication issue
 
 

-- 

Mit besten Grüßen, Reindl Harald
the lounge interactive design GmbH
A-1060 Vienna, Hofmühlgasse 17
CTO / software-development / cms-solutions
p: +43 (1) 595 3999 33, m: +43 (676) 40 221 40
icq: 154546673, http://www.thelounge.net/



signature.asc
Description: OpenPGP digital signature

Re: Replication issue

2011-02-16 Thread Carl

Thank you for the information and script.  I will try it out tonight when 
traffic stops.


Thanks,

Carl

- Original Message - 
From: Reindl Harald h.rei...@thelounge.net

To: Carl c...@etrak-plus.com
Cc: mysql@lists.mysql.com
Sent: Wednesday, February 16, 2011 7:47 AM
Subject: Re: Replication issue



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

Re: Replication Error on Slave

2011-02-07 Thread Nagaraj S

All,

Thanks for your replies and as per the advise I switched to row-based
replication but replication ended with below error.



*Last_Error: Error 'Table 'b.sdefrent' doesn't exist' on query. Default
database: 'b. Query: 'drop table sdefrent'*


above error is due to non existence of database and table. However bit
confused on the error I got, if its row based replication why its taking as
statement. Please help me here. Thank you

-Naga


On Thu, Jan 27, 2011 at 3:23 PM, Johan De Meersman vegiv...@tuxera.bewrote:

  On Thu, Jan 27, 2011 at 10:40 AM, Nagaraj S nagaraj@gmail.comwrote:

 **On Slave Server I replicate database *A alone* and my replication not
 working due to data fetching happen on B database.


 Well, yes. Statement-based replication does what it says on the box: it
 executes the exact same statement on the slave. If database B is not there,
 then insert into A select from B will not work.

 You may switch to row-based replication (which of course has it's own
 caveats, see the online manuals); or you can simply choose to also replicate
 database B.



 --
 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: Replication Error on Slave

2011-02-03 Thread Kristian Davies

 **On master server I have two databases *A and B*. App team use database B
 temporarily for there application to  compute calculation and insert the
 values on A database.

For certain statements you could start with SET SQL_BIN_LOG=0 so it
doesn't log that statement, and it won't be replicated.

-Kristian

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

Re: Replication Error on Slave

2011-01-27 Thread Johan De Meersman

On Thu, Jan 27, 2011 at 10:40 AM, Nagaraj S nagaraj@gmail.com wrote:

 **On Slave Server I replicate database *A alone* and my replication not
 working due to data fetching happen on B database.


Well, yes. Statement-based replication does what it says on the box: it
executes the exact same statement on the slave. If database B is not there,
then insert into A select from B will not work.

You may switch to row-based replication (which of course has it's own
caveats, see the online manuals); or you can simply choose to also replicate
database B.



-- 
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: Replication on MySQL databases

2010-11-04 Thread Johan De Meersman

If your sites are busy with *writes*, you're kind of stuck. Replication
means that every write that happens on one side, also MUST happen on the
other side, so you win nothing. Well, you win a little delay on half of your
writes, which is, to most people, really a downside, not an upside.

Your best bet in that scenario would be horizontal partitioning, that is,
put part of your tables on a second cluster. This entails quite some changes
to your application, though, and a hefty analysis of what tables you NEVER
use together in a single query. Can be quite the bugger to implement :-)
Keep in the back of your mind (but never tell management) that you *can*
actually use federated tables for accessing remote tables, but there's
plenty of drawbacks to that.


If you're talking mostly reads, you have more options. Even then, though, it
is best if your application is keenly aware of what's going on, as you have
no guarantee about the time it takes for an insert to replicate to all your
slaves - your application shouldn't panic if it can't immediately see the
data it just wrote.


Other people here will undoubtedly tell you about MMM - I keep hearing that
that's pretty good, but I (still) have no personal experience with it,
myself.



On Thu, Nov 4, 2010 at 10:04 AM, Machiel Richards machi...@rdc.co.zawrote:

 Good day all

  I am hoping that someone can perhaps help me with some resources
 or info.

 I need to go to a meeting in the next hour and was requested this
 morning to research possible load balancing options for MySQL database.


  What is currently running is a website (balanced over a couple of
 web servers all connecting to the same database) using apacje and jdk.

   2 MySQL databases running as Masler/Slave replication with all
 reads and writes going to the master and the slave being used for data
 exports and failover if required.


The websites are rather busy and during times of high load the
 master server takes some strain.

  The Databases are being to new upgraded hardware soon,
 including a database upgrade.


 The idea is that they also want to introduce load balancing for
 the MySQL databases in order to manage the high load situations.

Any help would be appreciated as google has not yet turned up
 any sufficient info for me in this short time I had been given.

 Regards
 Machiel




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

Re: Replication on MySQL databases

2010-11-04 Thread Machiel Richards

Thank you for the quick response 

just to answer one of the things here, the load is mostly reads as
writes only happen in batches every so often.

When I am saying reads I am talking of up to 2000-5000 concurrently at
any given time during high load.




-Original Message-
From: Johan De Meersman vegiv...@tuxera.be
To: Machiel Richards machi...@rdc.co.za
Cc: mysql mailing list mysql@lists.mysql.com
Subject: Re: Replication on MySQL databases
Date: Thu, 4 Nov 2010 10:21:11 +0100

If your sites are busy with *writes*, you're kind of stuck. Replication
means that every write that happens on one side, also MUST happen on the
other side, so you win nothing. Well, you win a little delay on half of
your writes, which is, to most people, really a downside, not an upside.

Your best bet in that scenario would be horizontal partitioning, that
is, put part of your tables on a second cluster. This entails quite some
changes to your application, though, and a hefty analysis of what tables
you NEVER use together in a single query. Can be quite the bugger to
implement :-) Keep in the back of your mind (but never tell management)
that you *can* actually use federated tables for accessing remote
tables, but there's plenty of drawbacks to that.


If you're talking mostly reads, you have more options. Even then,
though, it is best if your application is keenly aware of what's going
on, as you have no guarantee about the time it takes for an insert to
replicate to all your slaves - your application shouldn't panic if it
can't immediately see the data it just wrote.


Other people here will undoubtedly tell you about MMM - I keep hearing
that that's pretty good, but I (still) have no personal experience with
it, myself.



On Thu, Nov 4, 2010 at 10:04 AM, Machiel Richards machi...@rdc.co.za
wrote:

Good day all

 I am hoping that someone can perhaps help me with some
resources
or info.

I need to go to a meeting in the next hour and was requested
this
morning to research possible load balancing options for MySQL
database.


 What is currently running is a website (balanced over a
couple of
web servers all connecting to the same database) using apacje
and jdk.

  2 MySQL databases running as Masler/Slave replication with
all
reads and writes going to the master and the slave being used
for data
exports and failover if required.


   The websites are rather busy and during times of high
load the
master server takes some strain.

 The Databases are being to new upgraded hardware soon,
including a database upgrade.


The idea is that they also want to introduce load
balancing for
the MySQL databases in order to manage the high load situations.

   Any help would be appreciated as google has not yet
turned up
any sufficient info for me in this short time I had been given.

Regards
Machiel



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

Re: Replication on MySQL databases

2010-11-04 Thread Walter Heck

Classic scenario where MMM will be your best bet. Check out
http://mysql-mmm.org for more information. Setup two masters and 2 or
more slaves for full High Availability. It scales extremely well if
your application is read-heavy (which most applications are).

If you need help implementing this, I work for OpenQuery and we do
this kind of setup almost on a weekly basis. Check out the website in
the signature and let me/us know if you need our professional help.
Otherwise: feel free to ask questions here :)

kind regards,

On Thu, Nov 4, 2010 at 17:26, Machiel Richards machi...@rdc.co.za wrote:
 Thank you for the quick response

 just to answer one of the things here, the load is mostly reads as
 writes only happen in batches every so often.

 When I am saying reads I am talking of up to 2000-5000 concurrently at
 any given time during high load.

-- 
Walter Heck
Engineer @ Open Query (http://openquery.com)
Exceptional services for MariaDB and MySQL at a fixed budget

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

Re: Replication / standby option on MySQL (GA)

2010-10-01 Thread a . smith


This is in the GA (free) release and has been for many years I believe...

Quoting Machiel Richards machiel.richa...@gmail.com:


Hi Guys

Can someone please confirm whether replication / standby
functionality is available on the GA release of MySQL or this an
Enterprise specific functionality?


Regards
Machiel









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

Re: Replication / standby option on MySQL (GA)

2010-10-01 Thread Joerg Bruehe

Hi everbody!


Machiel Richards wrote:
 Hi Guys
 
 Can someone please confirm whether replication / standby
 functionality is available on the GA release of MySQL or this an
 Enterprise specific functionality?

1) Replication is available in all MySQL configurations, be they for the
   community (free of charge) or for paying users.

2) Terminology: GA means generally available as in may be used for
   any purpose, it is the short way of saying production quality.
   So it is a software maturity level, not a configuration.
   The counterpart of enterprise builds is called community.


HTH,
Jörg

-- 
Joerg Bruehe,  MySQL Build Team,  joerg.bru...@oracle.com
ORACLE Deutschland B.V.  Co. KG,   Komturstrasse 18a,   D-12099 Berlin
Geschaeftsfuehrer: Juergen Kunz, Marcel v.d. Molen, Alexander v.d. Ven
Amtsgericht Muenchen: HRA 95603


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

Re: Replication VS Cluster

2010-09-02 Thread Johan De Meersman

On Thu, Sep 2, 2010 at 1:10 PM, Jangita jang...@jangita.com wrote:

 Hi Guys,
 We have a system that has been running along nicely for the past three
 months on a pc (4gb 1,8ghz,debian lenny pc). It is a telecom-financal
 system; slightly 2 hits per minute but growing exponentally as customers
 increase.


Growth should be linear to the growth of customers, no ? :-)


 We have now bought two servers 12Gb RAM RAID blah blah;


RAID setup is important :-) Datafiles preferably on raid 10.



 and we want to set the servers up such that one is an exact duplicate of
 the other; to guard against hardware failiure (in case for example one
 motherboard is fried for some reason). We want to be able to switch from one
 server to the next and continue with minimum downtime. Switching will be
 manual until I figure out how to do an automatic switch (probably
 continuously ping the main server from the hot backup and if the ping fails
 the hot backup can change its ip automatically or something!)


Have a look at Ultramonkey for that.


 Anyway, what method of keeping the two servers in sync would the experts
 recommend between replication and setting up a cluster (or something else)?
 which will also give me a painless (and later maybe automatic) changeover?
 Both servers are connected to the same switch.


Standard setup would be replication, yes. If you setup automatic failover,
make sure you prevent automatic failback - that's the best way to mess up
your dataset.

I also hear MMM is pretty good, although I have no personal experience with
it.

Another route you might want to investigate, is Xen (or VMWare, if so
inclined). Build a single virtual host on your hardware, allocate everything
and the kitchen sink to it, and run your MySQL in it. You'll have a slight
performance loss, obviously, but here's the benefit: you can set up the
second server so that it keeps a bit-perfect copy of your primary machine.
The moment your primary machine dies, the second takes over; and since it
has the EXACT same state down to the last bit of ram, you don't even lose a
ping.

Under Xen this feature is called Remus I believe, VMWare calls it Live
Migration or something similar.




 --
 Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
 Skype: jangita | GTalk: jangita.nyag...@gmail.com

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




-- 
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: Replication VS Cluster

2010-09-02 Thread a . smith

Clustering is a general term, do you know which one you are comparing  
with replication? Clustering most typically refers to high  
availability clustering or high performance clustering, which wouldnt  
necessarily/normally imply any copy of the actual data.


If you want a copy of your data on another server, replication is the  
obvious choice. Assuming the realities of replication arent a problem  
with respect to your requirements, ie replication is asynchronous...


Andy.




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

Re: Replication VS Cluster

2010-09-02 Thread Jangita


On 02/09/2010 4:32 p, Johan De Meersman wrote:

On Thu, Sep 2, 2010 at 1:10 PM, Jangita jang...@jangita.com
mailto:jang...@jangita.com wrote:



...


Growth should be linear to the growth of customers, no ? :-)



I thought so too; but one customer = 1 customer record, plus all his 
transactions, and also weirdly enough (common for us Africans) customers 
tend to use the service more as more customers enroll (did that make any 
sence?) :):)

...

RAID setup is important :-) Datafiles preferably on raid 10.


Thanks a bunch on that generous tip!
...


Have a look at Ultramonkey for that.


Thanks again!


Standard setup would be replication, yes. If you setup automatic
failover, make sure you prevent automatic failback - that's the best way
to mess up your dataset.

I also hear MMM is pretty good, although I have no personal experience
with it.

Another route you might want to investigate, is Xen (or VMWare, if so
inclined). Build a single virtual host on your hardware, allocate
everything and the kitchen sink to it, and run your MySQL in it. You'll
have a slight performance loss, obviously, but here's the benefit: you
can set up the second server so that it keeps a bit-perfect copy of your
primary machine. The moment your primary machine dies, the second takes
over; and since it has the EXACT same state down to the last bit of ram,
you don't even lose a ping.

Under Xen this feature is called Remus I believe, VMWare calls it Live
Migration or something similar.


Thanks!
--
Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
Skype: jangita | GTalk: jangita.nyag...@gmail.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: Replication VS Cluster

2010-09-02 Thread Jangita


On 02/09/2010 4:35 p, a.sm...@ukgrid.net wrote:

Clustering is a general term, do you know which one you are comparing
with replication? Clustering most typically refers to high availability
clustering or high performance clustering, which wouldnt
necessarily/normally imply any copy of the actual data.

If you want a copy of your data on another server, replication is the
obvious choice. Assuming the realities of replication arent a problem
with respect to your requirements, ie replication is asynchronous...

Andy.


True.

Simply put: I want a solution that ensures that server 2 has all the 
data at server 1 at any point in time; say server 1 suddenly fell into a 
pond :) . I wouldnt want to open server 2 and find the last 
insert/update/delete missing...


--
Jangita | +256 76 91 8383 | Y!  MSN: jang...@yahoo.com
Skype: jangita | GTalk: jangita.nyag...@gmail.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: Replication VS Cluster

2010-09-02 Thread Neil Aggarwal

 Simply put: I want a solution that ensures that server 2 has all the 
 data at server 1 at any point in time

If server 1 and 2 are on the same local network, I would use
a cluster.  If they are located on physically separate networks,
I would use master-master replication.

Neil

--
Neil Aggarwal, (281)846-8957
FREE trial: Wordpress VPS with unmetered bandwidth
http://UnmeteredVPS.net/wordpress 


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

Re: Replication VS Cluster

2010-09-02 Thread a . smith


Quoting Jangita jang...@jangita.com:



Simply put: I want a solution that ensures that server 2 has all the  
data at server 1 at any point in time; say server 1 suddenly fell  
into a pond :) . I wouldnt want to open server 2 and find the last  
insert/update/delete missing...




Ok so that rules out any asynchronous replication (MySQL replication  
for example).
So options available would include, HA clustering with a shared fibre  
channel RAID array. Or synchronous replication over the network using  
something like DRBD or HAST (on FreeBSD), plus any other suggestions  
from others... :P





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

Re: Replication VS Cluster

2010-09-02 Thread Johan De Meersman

On Thu, Sep 2, 2010 at 5:12 PM, Neil Aggarwal n...@jammconsulting.comwrote:

 If server 1 and 2 are on the same local network, I would use
 a cluster.


As in NDB ? I've no personal experience with it - save for a sales talk by
MySQL guys some years back where we decided it was useless to us - but I
understand it has quite a few specific limitations that make it suited for a
rather specific range of applications.



 If they are located on physically separate networks,
 I would use master-master replication.


If you're on separate networks, you're gonna have trouble maintaining both
performance and perfect replication, regardless of what you do; not to
mention you'll be in performance hell as soon as you want to switch to the
remote master.

-- 
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: Replication VS Cluster

2010-09-02 Thread Johan De Meersman

On Thu, Sep 2, 2010 at 5:51 PM, a.sm...@ukgrid.net wrote:

 Quoting Jangita jang...@jangita.com:


 Simply put: I want a solution that ensures that server 2 has all the data
 at server 1 at any point in time; say server 1 suddenly fell into a pond :)
 . I wouldnt want to open server 2 and find the last insert/update/delete
 missing...


 Ok so that rules out any asynchronous replication (MySQL replication for
 example).



Actually, recent 5.1 servers do have 'semi-synchronous' replication, where
replication is synchronous until the slave happens to timeout, where it
reverts to asynchronous until you fix it. Incidentally, this is default
Oracle behaviour, too, if you're not talking RAC.

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

2010-08-30 Thread andrew.2.moore

Force all traffic to Node A
Break/Stop replication
backup Node using mysqldump
Restore backup file on Node B
Resume replication
Install Maatkit

Should do the trick...

From: ext Norman Khine [nor...@khine.net]
Sent: 30 August 2010 15:50
To: mysql@lists.mysql.com
Subject: replication help

hello,

i had a master-master replication now one of the masters seems to be
about two days ahead of the slave

how do i make the first server in sync with the second and force any
data added to the second master to be pushed back into the first
server.

thanks

--
˙uʍop ǝpısdn p,uɹnʇ pןɹoʍ ǝɥʇ ǝǝs noʎ 'ʇuǝɯɐן sǝɯıʇ ǝɥʇ puɐ 'ʇuǝʇuoɔ
ǝq s,ʇǝן ʇǝʎ
% .join( [ {'*':'@','^':'.'}.get(c,None) or
chr(97+(ord(c)-83)%26) for c in ,adym,*)uzq^zqf ] )

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/mysql?unsub=andrew.2.mo...@nokia.com

Re: Replication: ignore specified columns?

2010-07-23 Thread Claudio Nanni

No.

If it is for security reasons: did you think about a view on slave db?
(removing rights from source table)

In case you dont want those columns to reach the slave you could use
triggers to reflect changes from source table (or a view) on master to a
table(with missing columns) that will be replicated.

Just a couple of ideas

Claudio

2010/7/23 Bryan Cantwell bcantw...@firescope.com

 I know that you can ignore certain databases and tables in mysql
 replication, but is it possible to replicate all but a certain column or
 two from a table? This is 5.1.48 on linux.

 Thanks,
 Bryancan



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




-- 
Claudio

1 2 3 4 5 6 7 8 9 10 >

1 - 100 of 1042 matches

Mail list logo