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:
https://dev.mysql.com/doc/refma

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:
https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#

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


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

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

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 wagnerbianchi.com

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

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

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

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

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


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

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

--

*Wagner Bianchi*


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

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

Re: Replication problem

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

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

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

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

Re: Replication problem

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


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

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



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

Re: Replication problem

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 :

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

Re: Replication problem

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

Re: replication question replacing the master

2014-01-18 Thread Manuel Arostegui

2014/1/17 Richard Reina 

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

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


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

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

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

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

I see that faster than any other thing.

Hope this helps
Manuel.

Re: replication question replacing the master

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

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


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

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

Re: replication newbie questions

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

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

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

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

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

Re: replication fails after upgrade to 5.6

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



signature.asc
Description: OpenPGP digital signature

RE: replication fails after upgrade to 5.6

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


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

Re: replication fails after upgrade to 5.6

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  wrote:
>> update the master ASAP in a short timeframe too
>> and re-init replication if needed
>>
>> normally both should have exactly the same version
>>
>> the slaves must be updated first because otherwise
>> a master may write instructions in the binlog the older
>> slave does not undersatdn at all, but as said normally
>> both should have the same version
>>
>> Am 21.02.2013 18:03, schrieb Mike Franon:
>>> So I created a new test box on AWS, and just did one upgrade from
>>> 5.0.96 to 5.1, like I did before and replication will not work from a
>>> master with 5.0.96 to a slave with 5.1.68
>>>
>>> I keep getting Error 1062, Duplicate Entry for key
>>>
>>> I get no errors when I do a mysql_upgrade, all comes back ok.
>>>
>>> I was curious if anyone had any ideas?
>>>
>>> Thanks
>>>
>>> On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon  wrote:
 This is on a slave, i only upgraded on one box which is the slave i
 have not touched master

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



signature.asc
Description: OpenPGP digital signature

Re: replication fails after upgrade to 5.6

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  wrote:
> update the master ASAP in a short timeframe too
> and re-init replication if needed
>
> normally both should have exactly the same version
>
> the slaves must be updated first because otherwise
> a master may write instructions in the binlog the older
> slave does not undersatdn at all, but as said normally
> both should have the same version
>
> Am 21.02.2013 18:03, schrieb Mike Franon:
>> So I created a new test box on AWS, and just did one upgrade from
>> 5.0.96 to 5.1, like I did before and replication will not work from a
>> master with 5.0.96 to a slave with 5.1.68
>>
>> I keep getting Error 1062, Duplicate Entry for key
>>
>> I get no errors when I do a mysql_upgrade, all comes back ok.
>>
>> I was curious if anyone had any ideas?
>>
>> Thanks
>>
>> On Wed, Feb 20, 2013 at 5:51 PM, Mike Franon  wrote:
>>> This is on a slave, i only upgraded on one box which is the slave i
>>> have not touched master
>>>
>>> On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald  
>>> wrote:


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

 did you surely upgrade and restart the slaves first?

 i personally would NOT go to 5.6 now

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

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

Re: replication fails after upgrade to 5.6

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  wrote:
>> This is on a slave, i only upgraded on one box which is the slave i
>> have not touched master
>>
>> On Wed, Feb 20, 2013 at 5:41 PM, Reindl Harald  
>> wrote:
>>>
>>>
>>> Am 20.02.2013 23:27, schrieb Mike Franon:
 So I successfully upgraded a test db server from 5.0.96 all the way up to 
 5.6

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

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

 All of our other slaves on 5.0.96 are fine, so I know it has to do
 with 5.6 but just not sure what, when ir an mysql_upgrade everything
 was OK
>>>
>>> did you surely upgrade and restart the slaves first?
>>>
>>> i personally would NOT go to 5.6 now
>>>
>>> it is a very young release and looking and the typical changelogs
>>> replication has always the most fixed bugs



signature.asc
Description: OpenPGP digital signature

Re: replication fails after upgrade to 5.6

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

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

Re: replication fails after upgrade to 5.6

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  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-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 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  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 
> > 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 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 
> 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  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 between different versions

2012-11-15 Thread Reindl Harald



Am 15.11.2012 12:46, schrieb Manuel Arostegui:
> 2012/11/15 Lorenzo Milesi 
> 
>> 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 between different versions

2012-11-15 Thread Manuel Arostegui

2012/11/15 Lorenzo Milesi 

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

2012-10-25 Thread Manuel Arostegui

2012/10/25 Sabika M 

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

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

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



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

2012-08-01 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 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  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 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 between two tables in same database

2011-10-02 Thread Anupam Karmarkar

Try out functionality you desire with trigger to replicate data




From: Derek Downey 
To: [MySQL] 
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 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 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
>> 
>> 


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

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  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  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**>> 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 >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
>> >>
>> >>
>> >
>> > --
>> >
>> > 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.tompkins@**
> googlemail.com
>
>

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  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  
> wrote:
> Can you give me any pointers ?
> 
> 
> On Thu, Sep 29, 2011 at 5:59 PM, Andrew Moore  wrote:
> Hey Neil, it sure is possible through standard replication configuration.
> 
> Hth Andy
> 
> On Sep 29, 2011 5:57 PM, "Tompkins Neil"  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 Neil Tompkins

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

On 29 Sep 2011, at 20:10, mos  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  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 > >> > 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 > >>> >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 >  >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
>> >>
>> >>
>> >
>> > --
>> >
>> > 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 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  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 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 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 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 
, 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
>>
>>
>
> --
>
> 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

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


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

-- 

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

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



signature.asc
Description: OpenPGP digital signature

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

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

To: "Carl" 
Cc: 
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 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" 
> To: "Carl" 
> Cc: 
> 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, 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  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

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" 

To: "Carl" 
Cc: 
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 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


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" 

To: "Carl" 
Cc: 
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, log info

2011-02-16 Thread Johan De Meersman

On Wed, Feb 16, 2011 at 12:23 PM, Carl  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 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 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 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" 

To: "Carl" 
Cc: 
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 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" 

To: "Carl" 
Cc: 
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 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 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 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  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 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 wrote:

>  On Thu, Jan 27, 2011 at 10:40 AM, Nagaraj S 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 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  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 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  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 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 
To: Machiel Richards 
Cc: mysql mailing list 
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 
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 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 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 / 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 / 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 :


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

2010-09-02 Thread Johan De Meersman

On Thu, Sep 2, 2010 at 5:51 PM,  wrote:

> Quoting Jangita :
>
>
>> 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 VS Cluster

2010-09-02 Thread Johan De Meersman

On Thu, Sep 2, 2010 at 5:12 PM, Neil Aggarwal wrote:

> 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 a . smith


Quoting Jangita :



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


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

On Thu, Sep 2, 2010 at 1:10 PM, Jangita 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 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 Johan De Meersman

On Thu, Sep 2, 2010 at 1:10 PM, Jangita  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 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

1 2 3 4 5 6 7 8 9 10 >

1 - 100 of 1084 matches

Mail list logo