Re: slave to master
On 4/30/2016 3:22 AM, Thomas wrote: Am Freitag, 29. April 2016, 14:49:57 schrieb william drescher: On 4/28/2016 5:20 PM, Thomas wrote: Hi, I have setup an master slave replication. This works fine. I have running an Apache webserver and some other programms accessing the master. Whats the standard pocedure if master fail? When the master is repaired and up you need to stop the programs on the slave from accessing mysql (ie: stop the programs on the slave), make a database copy of the slave, copy it to the master and reload the database on the master, and then use the Change Master to command on the slave to set up the slave to begin replicating and start slave. This is the poor man's failover for low volume systems. In essence the slave becomes the main database server until you are ready to restart the master and replication. Hi thanks for all the answers, I only want that when master fails the slave will be the new master and do everythink that the master has before done. So I have more time to repair the master. I have to change the slave to master because I need write access to the mySQL databases. Sure that when mySQL data on the master are damaged I will have same problems on the slave, but for this I have an undependent data saving, not realtime but acceptable. Sure I need in this case more time to repair the whole system. slave to master: Like this: https://dev.mysql.com/doc/refman/5.5/en/replication-solutions-switch.html Is this the way to go? thanks Thomas yes -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave to master
Am Freitag, 29. April 2016, 14:49:57 schrieb william drescher: > On 4/28/2016 5:20 PM, Thomas wrote: > > Hi, > > > > I have setup an master slave replication. > > This works fine. > > I have running an Apache webserver and some other programms accessing the > > master. > > Whats the standard pocedure if master fail? > > > > When the master is repaired and up you need to stop the programs > on the slave from accessing mysql (ie: stop the programs on the > slave), make a database copy of the slave, copy it to the master > and reload the database on the master, and then use the Change > Master to command on the slave to set up the slave to begin > replicating and start slave. > > This is the poor man's failover for low volume systems. > In essence the slave becomes the main database server until you > are ready to restart the master and replication. Hi thanks for all the answers, I only want that when master fails the slave will be the new master and do everythink that the master has before done. So I have more time to repair the master. I have to change the slave to master because I need write access to the mySQL databases. Sure that when mySQL data on the master are damaged I will have same problems on the slave, but for this I have an undependent data saving, not realtime but acceptable. Sure I need in this case more time to repair the whole system. slave to master: Like this: https://dev.mysql.com/doc/refman/5.5/en/replication-solutions-switch.html Is this the way to go? thanks Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave to master
Am 29.04.2016 um 20:49 schrieb william drescher: On 4/28/2016 5:20 PM, Thomas wrote: Hi, I have setup an master slave replication. This works fine. I have running an Apache webserver and some other programms accessing the master. Whats the standard pocedure if master fail? I want to start up the programms on the slave by hand and then they are accessing the mySQL slave. Can they write to the slave or do I have to change something before in the mysql slave configuration? The simple answer is that you don't have to do anything. At some point you will want to stop the slave so that it will not start replicating when the master comes back up. it won't frankly crash the master and replication will break and stop after reboot in most cases - no way that it just starts again just because the master comes back later hence you normally have "read-only" on your slaves to prevent breaking replication signature.asc Description: OpenPGP digital signature
Re: slave to master
Am 29.04.2016 um 22:22 schrieb Mahmoud Alshinhab:
I was think of it from the cloud prospective, as for example you can
guarantee that Amazon's Elastic Load Balancer won't be a single point of
failure, so I was thinking how can I use MariaDB MaxScale and in the
same time guarantee that it won't be a single point of failure.
why should max scale care *what* your mysql-client is?
anything which can talk to your mysql server will talk the same way to
max scale, it acts like a ordinary mysql server from the view of any client
On Fri, Apr 29, 2016 at 5:34 PM, Reindl Harald <h.rei...@thelounge.net
<mailto:h.rei...@thelounge.net>> wrote:
Am 29.04.2016 um 17:27 schrieb Mahmoud Alshinhab:
I also like the MariaDB Max scale that Reindl Harald Sent -Thanks-
However I don't know if it is possible to use 2 servers of the
max scale
with a load balancer in front of them or not.
I always try to avoid the Single Point of Failure
get rid of the idea that your "load-balancer" not a single point
in a sane environment there is no single point of failure because
max scale *is the load balancer* and typically runs on a HA cluster
where it never goes away (virtual machine on a cluster FS - as
example - VMware vSphere with two hosts and VMware HA enabled)
why do you want a load balancer in front of a load balancer and how
do you make sure that this load balancer is redundant and not a
single point of failure itself?
"https://mariadb.com/products/mariadb-maxscale/how-maxscale-works;
On Fri, Apr 29, 2016 at 5:20 PM, Mahmoud Alshinhab
<mahmoud.alshin...@gmail.com
<mailto:mahmoud.alshin...@gmail.com>
<mailto:mahmoud.alshin...@gmail.com
<mailto:mahmoud.alshin...@gmail.com>>> wrote:
I quote this from the page[1]:
Load balancing implementation
Random picking
When initializing a connection or after a failed connection, the
connector will attempt to connect to a host with a certain role
(slave/master). The connection is selected randomly among
the valid
hosts. Thereafter, all statements will run on that database
server
until the connection will be closed (or fails).
The load-balancing will includes a pooling mechanism.
Example: when
creating a pool of 60 connections, each one will use a
random host.
With 3 master hosts, the pool will have about 20 connections
to each
host.
Master/slave distributed load
For a cluster composed of masters and slaves on connection
initialization, there will be 2 underlying connections: one
with a
master host, another with a slave host. Only one connection
is used
at a time.
For a cluster composed of master hosts only, each connection has
only one underlying connection.
The load will be distributed due to the random distribution of
connections..
Master/slave connection selection
It’s the application that has to decide to use master or slave
connection (the master connection is set by default).
Switching the type of connection is done by using JDBC
connection.setReadOnly(boolean readOnly)
<http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setReadOnly%28boolean%29>
method. Setting read-only to true will use the slave connection,
false, the master connection.
[1]
https://mariadb.com/kb/en/mariadb/failover-and-high-availability-with-mariadb-connector-j/
So I think it is not implemented yet. as "the application has to
decide to use master or slave connection (the master
connection is
set by default)."
--
Eng. Mahmoud Alshinhab
AWS Cloud Support Engineer
Fedora Ambassador
Wiki : https://fedoraproject.org/wiki/User:Tuxawy
mahmoud.alshin...@gmail.com
<mailto:mahmoud.alshin...@gmail.com>
<mailto:mahmoud.alshin...@gmail.com
<mailto:mahmoud.alshin...@gmail.com>>
tux...@fedoraproject.org <mailto:tux...@fedoraproject.org>
<mailto:tux...@fedoraproject.org <mailto:tux...@fedoraproject.org>>
On Fri, Apr 29, 2016 at 5:14 PM, Mahmoud Alshinhab
<mahmoud.alshin...@gmail.com
<mailto:mahmoud.alshin...@gmail.com>
<mailto:mahmoud.alshin...@gmail.com
<mailto:mahmoud.alshin...@gmail.com>>>
wrote:
It was actually built for Amazon's Aurora, but it sh
Re: slave to master
I was think of it from the cloud prospective, as for example you can guarantee that Amazon's Elastic Load Balancer won't be a single point of failure, so I was thinking how can I use MariaDB MaxScale and in the same time guarantee that it won't be a single point of failure. -- Eng. Mahmoud Alshinhab AWS Cloud Support Engineer Fedora Ambassador Wiki : https://fedoraproject.org/wiki/User:Tuxawy mahmoud.alshin...@gmail.com tux...@fedoraproject.org On Fri, Apr 29, 2016 at 5:34 PM, Reindl Harald <h.rei...@thelounge.net> wrote: > > > Am 29.04.2016 um 17:27 schrieb Mahmoud Alshinhab: > >> I also like the MariaDB Max scale that Reindl Harald Sent -Thanks- >> However I don't know if it is possible to use 2 servers of the max scale >> with a load balancer in front of them or not. >> I always try to avoid the Single Point of Failure >> > > get rid of the idea that your "load-balancer" not a single point > > in a sane environment there is no single point of failure because max > scale *is the load balancer* and typically runs on a HA cluster where it > never goes away (virtual machine on a cluster FS - as example - VMware > vSphere with two hosts and VMware HA enabled) > > why do you want a load balancer in front of a load balancer and how do you > make sure that this load balancer is redundant and not a single point of > failure itself? > > "https://mariadb.com/products/mariadb-maxscale/how-maxscale-works; >> On Fri, Apr 29, 2016 at 5:20 PM, Mahmoud Alshinhab >> <mahmoud.alshin...@gmail.com <mailto:mahmoud.alshin...@gmail.com>> wrote: >> >> I quote this from the page[1]: >> >> >> Load balancing implementation >> >> >> Random picking >> >> When initializing a connection or after a failed connection, the >> connector will attempt to connect to a host with a certain role >> (slave/master). The connection is selected randomly among the valid >> hosts. Thereafter, all statements will run on that database server >> until the connection will be closed (or fails). >> >> The load-balancing will includes a pooling mechanism. Example: when >> creating a pool of 60 connections, each one will use a random host. >> With 3 master hosts, the pool will have about 20 connections to each >> host. >> >> >> Master/slave distributed load >> >> For a cluster composed of masters and slaves on connection >> initialization, there will be 2 underlying connections: one with a >> master host, another with a slave host. Only one connection is used >> at a time. >> For a cluster composed of master hosts only, each connection has >> only one underlying connection. >> The load will be distributed due to the random distribution of >> connections.. >> >> >> Master/slave connection selection >> >> It’s the application that has to decide to use master or slave >> connection (the master connection is set by default). >> Switching the type of connection is done by using JDBC >> connection.setReadOnly(boolean readOnly) >> < >> http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setReadOnly%28boolean%29 >> > >> method. Setting read-only to true will use the slave connection, >> false, the master connection. >> >> [1] >> >> https://mariadb.com/kb/en/mariadb/failover-and-high-availability-with-mariadb-connector-j/ >> >> So I think it is not implemented yet. as "the application has to >> decide to use master or slave connection (the master connection is >> set by default)." >> >> -- >> Eng. Mahmoud Alshinhab >> AWS Cloud Support Engineer >> Fedora Ambassador >> Wiki : https://fedoraproject.org/wiki/User:Tuxawy >> mahmoud.alshin...@gmail.com <mailto:mahmoud.alshin...@gmail.com> >> tux...@fedoraproject.org <mailto:tux...@fedoraproject.org> >> >> On Fri, Apr 29, 2016 at 5:14 PM, Mahmoud Alshinhab >> <mahmoud.alshin...@gmail.com <mailto:mahmoud.alshin...@gmail.com>> >> wrote: >> >> It was actually built for Amazon's Aurora, but it should work >> with any mysql-compatible protoco. >> >> -- >> Eng. Mahmoud Alshinhab >> AWS Cloud Support Engineer >> Fedora Ambassador >> Wiki : https://fedoraproject.org/wiki/User:Tuxawy >> mahmoud.alshin...@gmail.com <ma
Re: slave to master
On 4/28/2016 5:20 PM, Thomas wrote: Hi, I have setup an master slave replication. This works fine. I have running an Apache webserver and some other programms accessing the master. Whats the standard pocedure if master fail? I want to start up the programms on the slave by hand and then they are accessing the mySQL slave. Can they write to the slave or do I have to change something before in the mysql slave configuration? thanks Thomas The simple answer is that you don't have to do anything. At some point you will want to stop the slave so that it will not start replicating when the master comes back up. When the master is repaired and up you need to stop the programs on the slave from accessing mysql (ie: stop the programs on the slave), make a database copy of the slave, copy it to the master and reload the database on the master, and then use the Change Master to command on the slave to set up the slave to begin replicating and start slave. This is the poor man's failover for low volume systems. In essence the slave becomes the main database server until you are ready to restart the master and replication. bill -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: slave to master
Am 29.04.2016 um 17:27 schrieb Mahmoud Alshinhab:
I also like the MariaDB Max scale that Reindl Harald Sent -Thanks-
However I don't know if it is possible to use 2 servers of the max scale
with a load balancer in front of them or not.
I always try to avoid the Single Point of Failure
get rid of the idea that your "load-balancer" not a single point
in a sane environment there is no single point of failure because max
scale *is the load balancer* and typically runs on a HA cluster where it
never goes away (virtual machine on a cluster FS - as example - VMware
vSphere with two hosts and VMware HA enabled)
why do you want a load balancer in front of a load balancer and how do
you make sure that this load balancer is redundant and not a single
point of failure itself?
"https://mariadb.com/products/mariadb-maxscale/how-maxscale-works;
On Fri, Apr 29, 2016 at 5:20 PM, Mahmoud Alshinhab
<mahmoud.alshin...@gmail.com <mailto:mahmoud.alshin...@gmail.com>> wrote:
I quote this from the page[1]:
Load balancing implementation
Random picking
When initializing a connection or after a failed connection, the
connector will attempt to connect to a host with a certain role
(slave/master). The connection is selected randomly among the valid
hosts. Thereafter, all statements will run on that database server
until the connection will be closed (or fails).
The load-balancing will includes a pooling mechanism. Example: when
creating a pool of 60 connections, each one will use a random host.
With 3 master hosts, the pool will have about 20 connections to each
host.
Master/slave distributed load
For a cluster composed of masters and slaves on connection
initialization, there will be 2 underlying connections: one with a
master host, another with a slave host. Only one connection is used
at a time.
For a cluster composed of master hosts only, each connection has
only one underlying connection.
The load will be distributed due to the random distribution of
connections..
Master/slave connection selection
It’s the application that has to decide to use master or slave
connection (the master connection is set by default).
Switching the type of connection is done by using JDBC
connection.setReadOnly(boolean readOnly)
<http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setReadOnly%28boolean%29>
method. Setting read-only to true will use the slave connection,
false, the master connection.
[1]
https://mariadb.com/kb/en/mariadb/failover-and-high-availability-with-mariadb-connector-j/
So I think it is not implemented yet. as "the application has to
decide to use master or slave connection (the master connection is
set by default)."
--
Eng. Mahmoud Alshinhab
AWS Cloud Support Engineer
Fedora Ambassador
Wiki : https://fedoraproject.org/wiki/User:Tuxawy
mahmoud.alshin...@gmail.com <mailto:mahmoud.alshin...@gmail.com>
tux...@fedoraproject.org <mailto:tux...@fedoraproject.org>
On Fri, Apr 29, 2016 at 5:14 PM, Mahmoud Alshinhab
<mahmoud.alshin...@gmail.com <mailto:mahmoud.alshin...@gmail.com>>
wrote:
It was actually built for Amazon's Aurora, but it should work
with any mysql-compatible protoco.
--
Eng. Mahmoud Alshinhab
AWS Cloud Support Engineer
Fedora Ambassador
Wiki : https://fedoraproject.org/wiki/User:Tuxawy
mahmoud.alshin...@gmail.com <mailto:mahmoud.alshin...@gmail.com>
tux...@fedoraproject.org <mailto:tux...@fedoraproject.org>
On Fri, Apr 29, 2016 at 3:13 PM, Reindl Harald
<h.rei...@thelounge.net <mailto:h.rei...@thelounge.net>> wrote:
Am 29.04.2016 um 15:07 schrieb Johan De Meersman:
From: "Mahmoud Alshinhab"
<mahmoud.alshin...@gmail.com
<mailto:mahmoud.alshin...@gmail.com>>
Subject: Re: slave to master
I think you should have a look at MariaDB Connector[1].
It provides Load balancing and failover as Failover
occurs when a connection to
a primary database server fails and the connector
will open up a connection to
another database server.
Hmm, I didn't know that they built that into it,
interesting. Does it require server features, or would
it work with any mysql-compatible protocol ?
Load balancing allows load (read and write) to be
distributed over multiple
servers.
Is read-write splitting also built-in, then?
here you go:
Re: slave to master
I also like the MariaDB Max scale that Reindl Harald Sent -Thanks- However I don't know if it is possible to use 2 servers of the max scale with a load balancer in front of them or not. I always try to avoid the Single Point of Failure. "https://mariadb.com/products/mariadb-maxscale/how-maxscale-works; -- Eng. Mahmoud Alshinhab AWS Cloud Support Engineer Fedora Ambassador Wiki : https://fedoraproject.org/wiki/User:Tuxawy mahmoud.alshin...@gmail.com tux...@fedoraproject.org On Fri, Apr 29, 2016 at 5:20 PM, Mahmoud Alshinhab < mahmoud.alshin...@gmail.com> wrote: > I quote this from the page[1]: > Load balancing implementation Random picking > > When initializing a connection or after a failed connection, the connector > will attempt to connect to a host with a certain role (slave/master). The > connection is selected randomly among the valid hosts. Thereafter, all > statements will run on that database server until the connection will be > closed (or fails). > > The load-balancing will includes a pooling mechanism. Example: when > creating a pool of 60 connections, each one will use a random host. With 3 > master hosts, the pool will have about 20 connections to each host. > Master/slave distributed load > > For a cluster composed of masters and slaves on connection initialization, > there will be 2 underlying connections: one with a master host, another > with a slave host. Only one connection is used at a time. > For a cluster composed of master hosts only, each connection has only one > underlying connection. > The load will be distributed due to the random distribution of > connections.. > Master/slave connection selection It’s the application that has to decide > to use master or slave connection (the master connection is set by default). > Switching the type of connection is done by using JDBC > connection.setReadOnly(boolean > readOnly) > <http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setReadOnly%28boolean%29> > method. Setting read-only to true will use the slave connection, false, the > master connection. > > [1] > https://mariadb.com/kb/en/mariadb/failover-and-high-availability-with-mariadb-connector-j/ > > So I think it is not implemented yet. as "the application has to decide to > use master or slave connection (the master connection is set by default)." > > -- > Eng. Mahmoud Alshinhab > AWS Cloud Support Engineer > Fedora Ambassador > Wiki : https://fedoraproject.org/wiki/User:Tuxawy > mahmoud.alshin...@gmail.com > tux...@fedoraproject.org > > On Fri, Apr 29, 2016 at 5:14 PM, Mahmoud Alshinhab < > mahmoud.alshin...@gmail.com> wrote: > >> It was actually built for Amazon's Aurora, but it should work with any >> mysql-compatible protoco. >> >> -- >> Eng. Mahmoud Alshinhab >> AWS Cloud Support Engineer >> Fedora Ambassador >> Wiki : https://fedoraproject.org/wiki/User:Tuxawy >> mahmoud.alshin...@gmail.com >> tux...@fedoraproject.org >> >> On Fri, Apr 29, 2016 at 3:13 PM, Reindl Harald <h.rei...@thelounge.net> >> wrote: >> >>> >>> Am 29.04.2016 um 15:07 schrieb Johan De Meersman: >>> >>>> From: "Mahmoud Alshinhab" <mahmoud.alshin...@gmail.com> >>>>> Subject: Re: slave to master >>>>> >>>> >>>> I think you should have a look at MariaDB Connector[1]. >>>>> >>>> >>>> It provides Load balancing and failover as Failover occurs when a >>>>> connection to >>>>> a primary database server fails and the connector will open up a >>>>> connection to >>>>> another database server. >>>>> >>>> >>>> Hmm, I didn't know that they built that into it, interesting. Does it >>>> require server features, or would it work with any mysql-compatible >>>> protocol ? >>>> >>>> Load balancing allows load (read and write) to be distributed over >>>>> multiple >>>>> servers. >>>>> >>>> >>>> Is read-write splitting also built-in, then? >>>> >>> >>> here you go: https://mariadb.com/de/products/mariadb-maxscale and >>> forget about "MariaDB Connector" whatever that is >>> >>> >> >
Re: slave to master
Am 29.04.2016 um 17:20 schrieb Mahmoud Alshinhab: Master/slave connection selection It’s the application that has to decide to use master or slave connection (the master connection is set by default). Switching the type of connection is done by using JDBC connection.setReadOnly(boolean readOnly) <http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setReadOnly%28boolean%29> method. Setting read-only to true will use the slave connection, false, the master connection. [1] https://mariadb.com/kb/en/mariadb/failover-and-high-availability-with-mariadb-connector-j/ So I think it is not implemented yet. as "the application has to decide to use master or slave connection (the master connection is set by default)." so what's the benfit to use it? https://mariadb.com/products/mariadb-maxscale/database-scaling * Replication aware dynamic routing * Query load balancing * Client to database connection multiplexing https://mariadb.com/products/mariadb-maxscale/minimize-maintenance-downtime Whether you need to take a database node of a cluster out of service for maintenance or add a database node back to cluster, MaxScale?s load balance routing and monitoring plugins will assure that database traffic is always routed to database nodes in service without impacting the applications. https://mariadb.com/products/mariadb-maxscale MariaDB MaxScale is designed to hide the database setup complexity from the application so that the app still believes it connects to a single database instance but actually it connects to MariaDB MaxScale which sits on top of a growing MariaDB or MySQL database farm. The concept of a data-centric proxy isn?t new, but MariaDB MaxScale is different because it leverages the deep server and parser knowledge of MariaDB to ensure that queries are processed in the exactly the same way inside MariaDB server. This makes it faster, more efficient and more transparent than any other database proxy on the market. signature.asc Description: OpenPGP digital signature
Re: slave to master
I quote this from the page[1]: Load balancing implementation Random picking When initializing a connection or after a failed connection, the connector will attempt to connect to a host with a certain role (slave/master). The connection is selected randomly among the valid hosts. Thereafter, all statements will run on that database server until the connection will be closed (or fails). The load-balancing will includes a pooling mechanism. Example: when creating a pool of 60 connections, each one will use a random host. With 3 master hosts, the pool will have about 20 connections to each host. Master/slave distributed load For a cluster composed of masters and slaves on connection initialization, there will be 2 underlying connections: one with a master host, another with a slave host. Only one connection is used at a time. For a cluster composed of master hosts only, each connection has only one underlying connection. The load will be distributed due to the random distribution of connections.. Master/slave connection selection It’s the application that has to decide to use master or slave connection (the master connection is set by default). Switching the type of connection is done by using JDBC connection.setReadOnly(boolean readOnly) <http://docs.oracle.com/javase/7/docs/api/java/sql/Connection.html#setReadOnly%28boolean%29> method. Setting read-only to true will use the slave connection, false, the master connection. [1] https://mariadb.com/kb/en/mariadb/failover-and-high-availability-with-mariadb-connector-j/ So I think it is not implemented yet. as "the application has to decide to use master or slave connection (the master connection is set by default)." -- Eng. Mahmoud Alshinhab AWS Cloud Support Engineer Fedora Ambassador Wiki : https://fedoraproject.org/wiki/User:Tuxawy mahmoud.alshin...@gmail.com tux...@fedoraproject.org On Fri, Apr 29, 2016 at 5:14 PM, Mahmoud Alshinhab < mahmoud.alshin...@gmail.com> wrote: > It was actually built for Amazon's Aurora, but it should work with any > mysql-compatible protoco. > > -- > Eng. Mahmoud Alshinhab > AWS Cloud Support Engineer > Fedora Ambassador > Wiki : https://fedoraproject.org/wiki/User:Tuxawy > mahmoud.alshin...@gmail.com > tux...@fedoraproject.org > > On Fri, Apr 29, 2016 at 3:13 PM, Reindl Harald <h.rei...@thelounge.net> > wrote: > >> >> Am 29.04.2016 um 15:07 schrieb Johan De Meersman: >> >>> From: "Mahmoud Alshinhab" <mahmoud.alshin...@gmail.com> >>>> Subject: Re: slave to master >>>> >>> >>> I think you should have a look at MariaDB Connector[1]. >>>> >>> >>> It provides Load balancing and failover as Failover occurs when a >>>> connection to >>>> a primary database server fails and the connector will open up a >>>> connection to >>>> another database server. >>>> >>> >>> Hmm, I didn't know that they built that into it, interesting. Does it >>> require server features, or would it work with any mysql-compatible >>> protocol ? >>> >>> Load balancing allows load (read and write) to be distributed over >>>> multiple >>>> servers. >>>> >>> >>> Is read-write splitting also built-in, then? >>> >> >> here you go: https://mariadb.com/de/products/mariadb-maxscale and forget >> about "MariaDB Connector" whatever that is >> >> >
Re: slave to master
It was actually built for Amazon's Aurora, but it should work with any mysql-compatible protoco. -- Eng. Mahmoud Alshinhab AWS Cloud Support Engineer Fedora Ambassador Wiki : https://fedoraproject.org/wiki/User:Tuxawy mahmoud.alshin...@gmail.com tux...@fedoraproject.org On Fri, Apr 29, 2016 at 3:13 PM, Reindl Harald <h.rei...@thelounge.net> wrote: > > Am 29.04.2016 um 15:07 schrieb Johan De Meersman: > >> From: "Mahmoud Alshinhab" <mahmoud.alshin...@gmail.com> >>> Subject: Re: slave to master >>> >> >> I think you should have a look at MariaDB Connector[1]. >>> >> >> It provides Load balancing and failover as Failover occurs when a >>> connection to >>> a primary database server fails and the connector will open up a >>> connection to >>> another database server. >>> >> >> Hmm, I didn't know that they built that into it, interesting. Does it >> require server features, or would it work with any mysql-compatible >> protocol ? >> >> Load balancing allows load (read and write) to be distributed over >>> multiple >>> servers. >>> >> >> Is read-write splitting also built-in, then? >> > > here you go: https://mariadb.com/de/products/mariadb-maxscale and forget > about "MariaDB Connector" whatever that is > >
Re: slave to master
Am 29.04.2016 um 15:07 schrieb Johan De Meersman: From: "Mahmoud Alshinhab" <mahmoud.alshin...@gmail.com> Subject: Re: slave to master I think you should have a look at MariaDB Connector[1]. It provides Load balancing and failover as Failover occurs when a connection to a primary database server fails and the connector will open up a connection to another database server. Hmm, I didn't know that they built that into it, interesting. Does it require server features, or would it work with any mysql-compatible protocol ? Load balancing allows load (read and write) to be distributed over multiple servers. Is read-write splitting also built-in, then? here you go: https://mariadb.com/de/products/mariadb-maxscale and forget about "MariaDB Connector" whatever that is signature.asc Description: OpenPGP digital signature
Re: slave to master
> From: "Mahmoud Alshinhab" <mahmoud.alshin...@gmail.com> > Subject: Re: slave to master > I think you should have a look at MariaDB Connector[1]. > It provides Load balancing and failover as Failover occurs when a connection > to > a primary database server fails and the connector will open up a connection to > another database server. Hmm, I didn't know that they built that into it, interesting. Does it require server features, or would it work with any mysql-compatible protocol ? > Load balancing allows load (read and write) to be distributed over multiple > servers. Is read-write splitting also built-in, then? -- Unhappiness is discouraged and will be corrected with kitten pictures.
Re: slave to master
Hi, I think you should have a look at MariaDB Connector[1]. It provides Load balancing and failover as Failover occurs when a connection to a primary database server fails and the connector will open up a connection to another database server. For example, server A has the current connection. After a failure (server crash, network down …) the connection will switch to another server (B). Load balancing allows load (read and write) to be distributed over multiple servers. I hope this will help you. [References] [1] https://mariadb.com/kb/en/mariadb/failover-and-high-availability-with-mariadb-connector-j/ -- Eng. Mahmoud Alshinhab AWS Cloud Support Engineer Fedora Ambassador Wiki : https://fedoraproject.org/wiki/User:Tuxawy mahmoud.alshin...@gmail.com tux...@fedoraproject.org On Fri, Apr 29, 2016 at 11:03 AM, Johan De Meersman <vegiv...@tuxera.be> wrote: > - Original Message - > > From: "Jason Mallory" <jason.mall...@iridium.com> > > Subject: RE: slave to master > > > > Master-master with load balancer would be best > > That's a bit brief, isn't it? :-) > > It's more than worth pointing out that your loadbalancer should not > actually be loadbalancing the connections; master-master replication > doesn't quite work the way you think it does in most scenarios. > > You only want the loadbalancer for the automated failover; but it should > never send requests to more than one master at any given time. Have it send > everything to your primary master only; and when that host fails, have it > send everything to the secondary master only, and never fail back > automatically. > > It's also worth noting that master-master is still not an officially > supported replication topology. Regular master-slave also works fine with > the above loadbalancer configuration; in that case you'll just treat the > slave as the new primary after failover; and will manually reconfigure the > broken master to be a slave (and adapt the loadbalancer config accordingly) > as repair. > > There does exist software that can do those reconfigurations by itself, > MMM is one such example. > > /Johan > > -- > Unhappiness is discouraged and will be corrected with kitten pictures. > > -- > MySQL General Mailing List > For list archives: http://lists.mysql.com/mysql > To unsubscribe:http://lists.mysql.com/mysql > >
Re: slave to master
- Original Message - > From: "Jason Mallory" <jason.mall...@iridium.com> > Subject: RE: slave to master > > Master-master with load balancer would be best That's a bit brief, isn't it? :-) It's more than worth pointing out that your loadbalancer should not actually be loadbalancing the connections; master-master replication doesn't quite work the way you think it does in most scenarios. You only want the loadbalancer for the automated failover; but it should never send requests to more than one master at any given time. Have it send everything to your primary master only; and when that host fails, have it send everything to the secondary master only, and never fail back automatically. It's also worth noting that master-master is still not an officially supported replication topology. Regular master-slave also works fine with the above loadbalancer configuration; in that case you'll just treat the slave as the new primary after failover; and will manually reconfigure the broken master to be a slave (and adapt the loadbalancer config accordingly) as repair. There does exist software that can do those reconfigurations by itself, MMM is one such example. /Johan -- Unhappiness is discouraged and will be corrected with kitten pictures. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
RE: slave to master
Master-master with load balancer would be best Jason Mallory, MySQL/SQL Server DBA p: +1.480.752.1198 | m: +1.480.823.7771 | f: +1. 480.752.1105 | www.iridium.com The information contained in this email is strictly confidential and may be legally privileged and protected from disclosure by law. This email is intended for use by the addressee only. Notice is hereby given that any disclosure, use or copying of the information by anyone other than the intended recipient is strictly prohibited and may be illegal. If you have received this email in error, please destroy all electronic and other copies of this message and contact the sender or Iridium at em...@iridium.com -Original Message- From: Thomas [mailto:thomasit...@gmail.com] Sent: Thursday, April 28, 2016 2:21 PM To: mysql@lists.mysql.com Subject: slave to master Hi, I have setup an master slave replication. This works fine. I have running an Apache webserver and some other programms accessing the master. Whats the standard pocedure if master fail? I want to start up the programms on the slave by hand and then they are accessing the mySQL slave. Can they write to the slave or do I have to change something before in the mysql slave configuration? thanks Thomas -- 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
slave to master
Hi, I have setup an master slave replication. This works fine. I have running an Apache webserver and some other programms accessing the master. Whats the standard pocedure if master fail? I want to start up the programms on the slave by hand and then they are accessing the mySQL slave. Can they write to the slave or do I have to change something before in the mysql slave configuration? thanks Thomas -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Promoting MySQL 5.5 slave to master
2013/3/21 Miguel González Castaños miguel_3_gonza...@yahoo.es Dear all, I have a pretty simple setup. A LAMP server in production and a failover LAMP server in case the main server is down. I have been searching around to find out a clear answer of how to proceed when you need to promote a MySQL 5.5 slave server when the master (also 5.5) has crashed. So far I have read that if the master crashes I should issue an stop slave in the slave server and restart the mysql service (otherwise the master info is still kept in memory) with a skip-start-slave setting. Is that right? Is that all? Hi Miguel, Do you have a master-master replication between them? How is the failover over done? Do you just move a VIP between them? Anyways, there's no need to do stop slave or restar MySQL service in the new master...you just need to set it active (move the VIP, pointing your Apache manually to write to it, however you do it). What you have to make sure though is about the read_only parameter. Make sure the new master comes back to life with read_only = ON just to avoid any problems. The standby master should always have read_only = ON until it becomes active. Manuel.
Re: Promoting MySQL 5.5 slave to master
2013/3/21 Miguel Gonzalez miguel_3_gonza...@yahoo.es Can you elaborate about this? I thought that once you fixed the issues in the master server you needed to set it as slave of the new promoted master server, and do the other way round. That's why you might want to have master-master replication to avoid this manual process every time the active master dies. Anyways, you're right - you'd need to configure the dead master to become a slave of the new master. In order to do so, you need to now the binlog file and position the new master had before getting the VIP. You'd need to check the binlogs. By having both MySQL replicating from each other, you'd avoid this. Make sure you do reply all instead of replying only to me :-) Manuel.
RE: Promoting MySQL 5.5 slave to master
-Original Message- From: Miguel Gonzalez [mailto:miguel_3_gonza...@yahoo.es] Sent: 21 March 2013 08:29 To: Manuel Arostegui Cc: mysql@lists.mysql.com Subject: Re: Promoting MySQL 5.5 slave to master - Mensaje original - De: Manuel Arostegui man...@tuenti.com Para: Miguel Gonzalez miguel_3_gonza...@yahoo.es CC: mysql@lists.mysql.com Enviado: Jueves 21 de marzo de 2013 9:17 Asunto: Re: Promoting MySQL 5.5 slave to master 2013/3/21 Miguel Gonzalez miguel_3_gonza...@yahoo.es Can you elaborate about this? I thought that once you fixed the issues in the master server you needed to set it as slave of the new promoted master server, and do the other way round. By having both MySQL replicating from each other, you'd avoid this. I'm trying not to overcomplicate things. Also the server where the replica of the production server is is not active at all. I thought master-master configuration where more common when you were behind a web balancer. I thought also that replication had changed in MySQL 5.5. Is that difficult to find a clear procedure for performing a mysql failover when a server crashed? [AM] If you've the option to use MySQL 5.6 then managing replication is a lot simpler and more reliable... http://www.clusterdb.com/mysql-replication/mysql-5-6-ga-replication-enhancements/ MySQL 5.6 Failing over is described in section 5 of http://www.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/ - note that the paper deals with a more complex scenario where there are multiple slaves and so you could simplify. Make sure you do reply all instead of replying only to me :-) Sorry about that, I thought the list was configured differently Miguel -- 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: Promoting MySQL 5.5 slave to master
[AM] If you've the option to use MySQL 5.6 then managing replication is a lot simpler and more reliable... http://www.clusterdb.com/mysql-replication/mysql-5-6-ga-replication-enhancements/ MySQL 5.6 Failing over is described in section 5 of http://www.mysql.com/why-mysql/white-papers/mysql-replication-tutorial/ - note that the paper deals with a more complex scenario where there are multiple slaves and so you could simplify. I don't normally rush when it comes to upgrading services. MySQL 5.6 has been recently released and I prefer until the RPM package has been included in most YUM repos and it has been throughly tested. I still remember how painful was to install a new system with MySQL (5.5 I believe) under Ubuntu last May. I had to report a bug and make some tweaks in order to get the mysql service installed. Regards, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Promoting MySQL 5.5 slave to master
- Mensaje original - De: Manuel Arostegui man...@tuenti.com Para: Miguel Gonzalez miguel_3_gonza...@yahoo.es CC: mysql@lists.mysql.com Enviado: Jueves 21 de marzo de 2013 9:17 Asunto: Re: Promoting MySQL 5.5 slave to master 2013/3/21 Miguel Gonzalez miguel_3_gonza...@yahoo.es Can you elaborate about this? I thought that once you fixed the issues in the master server you needed to set it as slave of the new promoted master server, and do the other way round. By having both MySQL replicating from each other, you'd avoid this. I'm trying not to overcomplicate things. Also the server where the replica of the production server is is not active at all. I thought master-master configuration where more common when you were behind a web balancer. I thought also that replication had changed in MySQL 5.5. Is that difficult to find a clear procedure for performing a mysql failover when a server crashed? Make sure you do reply all instead of replying only to me :-) Sorry about that, I thought the list was configured differently Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Promoting MySQL 5.5 slave to master
Dear all, I have a pretty simple setup. A LAMP server in production and a failover LAMP server in case the main server is down. I have been searching around to find out a clear answer of how to proceed when you need to promote a MySQL 5.5 slave server when the master (also 5.5) has crashed. So far I have read that if the master crashes I should issue an stop slave in the slave server and restart the mysql service (otherwise the master info is still kept in memory) with a skip-start-slave setting. Is that right? Is that all? Regards, Miguel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Replication question: How to make a slave a master?
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
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 signature.asc Description: OpenPGP digital signature
RE: Replication question: How to make a slave a master?
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?
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?
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?
* 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?
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
Multi slave-single master
Good day all I just have a quick question in order to confirm something.. If I remember correctly, one master are allowed to have more than one slave server (i.e. serverA can be master to both serverB and serverC) Am I correct in this matter? We are busy with a migration to new hardware this weekend and my plan is to setup the new master server as a slave to current production master in order to get all data in sync and during switchover to simply shutdown current production and change over to the new servers using relevant configuration. Regards Machiel
Re: Multi slave-single master
As far as I know your correcct. You can set as much slave servers as you need. -- João Cândido de Souza Neto Machiel Richards machi...@rdc.co.za escreveu na mensagem news:1297774004.1798.25.camel@machielr-laptop... Good day all I just have a quick question in order to confirm something.. If I remember correctly, one master are allowed to have more than one slave server (i.e. serverA can be master to both serverB and serverC) Am I correct in this matter? We are busy with a migration to new hardware this weekend and my plan is to setup the new master server as a slave to current production master in order to get all data in sync and during switchover to simply shutdown current production and change over to the new servers using relevant configuration. 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: Multi slave-single master
Absolutely true. We have a master/slave pair and a secondary slave that is our 'live backup' and we take offline every night to rsync the tarballs to tape backup too. When it comes online, it syncs up with master. Rinse repeat. Works awesome and seemless. -Original Message- From: João Cândido de Souza Neto [mailto:j...@consultorweb.cnt.br] Sent: Tuesday, February 15, 2011 4:50 AM To: mysql@lists.mysql.com Subject: Re: Multi slave-single master As far as I know your correcct. You can set as much slave servers as you need. -- João Cândido de Souza Neto Machiel Richards machi...@rdc.co.za escreveu na mensagem news:1297774004.1798.25.camel@machielr-laptop... Good day all I just have a quick question in order to confirm something.. If I remember correctly, one master are allowed to have more than one slave server (i.e. serverA can be master to both serverB and serverC) Am I correct in this matter? We are busy with a migration to new hardware this weekend and my plan is to setup the new master server as a slave to current production master in order to get all data in sync and during switchover to simply shutdown current production and change over to the new servers using relevant configuration. Regards Machiel -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=dae...@daevid.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: Master/Slave - trucate master table
I don't have the error anymore, but the slave will start, but when you show slave status, it shows that there is a failure, and the failure was, a duplicate primary key. What i did to fix/bandaid it, was i truncated the table on the slave, and restarted the slave again... then i did a delete on the master, where ID . Steven Staples -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: March 5, 2010 1:11 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Master/Slave - trucate master table Does START SLAVE fail? If so, what is the output of SHOW SLAVE STATUS? JW On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples sstap...@mnsi.net wrote: Good day everyone... I am working with a master/slave set up, and of course, we started it once the database tables were a combined size of 60gb+ We've got it running and in sync now, (stopped the master, deleted the bin files, rsync'd the database tables and restarted the master) and all has been fine for the last week... until today. We stopped the mysql process on the slave, and did a backup (due to size, we just rsync and backup the rsync) of the table files. Took about 40 minutes to do (still not sure why it was that long, but anyway), and then we restarted the slave mysql. We're getting an error now. Apparently one of the tables is out of sync now? What appears to have happened, is that when the slave was down, i truncated a table on the master. I've read that this sometimes causes errors, is this a bug? Or a is there something I should do differently? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/05/10 02:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Master/Slave - trucate master table
did u stop the slave process before stopping mysql on slave. Did u do the below before stopping the mysql on slave? slave stop; show slave status\G; regards anandkl On Mon, Mar 8, 2010 at 6:46 PM, Steven Staples sstap...@mnsi.net wrote: I don't have the error anymore, but the slave will start, but when you show slave status, it shows that there is a failure, and the failure was, a duplicate primary key. What i did to fix/bandaid it, was i truncated the table on the slave, and restarted the slave again... then i did a delete on the master, where ID . Steven Staples -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: March 5, 2010 1:11 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Master/Slave - trucate master table Does START SLAVE fail? If so, what is the output of SHOW SLAVE STATUS? JW On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples sstap...@mnsi.net wrote: Good day everyone... I am working with a master/slave set up, and of course, we started it once the database tables were a combined size of 60gb+ We've got it running and in sync now, (stopped the master, deleted the bin files, rsync'd the database tables and restarted the master) and all has been fine for the last week... until today. We stopped the mysql process on the slave, and did a backup (due to size, we just rsync and backup the rsync) of the table files. Took about 40 minutes to do (still not sure why it was that long, but anyway), and then we restarted the slave mysql. We're getting an error now. Apparently one of the tables is out of sync now? What appears to have happened, is that when the slave was down, i truncated a table on the master. I've read that this sometimes causes errors, is this a bug? Or a is there something I should do differently? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/05/10 02:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com
RE: Master/Slave - trucate master table
Technically, shouldn't the slave be able to shutdown and then catch back up? What if the slave lost power and shut down... and when the power came back, shouldn't the slave restart, get the bin logs, and catchup? From now on, I wont truncate any tables, i will just delete from the table where the ID and then reset the ID to 0 or 1... and then delete everything from and beyond I was just wondering if this was a bug, or if there was something wrong with what I did? Steven Staples -Original Message- From: Ananda Kumar [mailto:anan...@gmail.com] Sent: March 8, 2010 11:45 AM To: Steven Staples Cc: Johnny Withers; mysql@lists.mysql.com Subject: Re: Master/Slave - trucate master table did u stop the slave process before stopping mysql on slave. Did u do the below before stopping the mysql on slave? slave stop; show slave status\G; regards anandkl On Mon, Mar 8, 2010 at 6:46 PM, Steven Staples sstap...@mnsi.net wrote: I don't have the error anymore, but the slave will start, but when you show slave status, it shows that there is a failure, and the failure was, a duplicate primary key. What i did to fix/bandaid it, was i truncated the table on the slave, and restarted the slave again... then i did a delete on the master, where ID . Steven Staples -Original Message- From: Johnny Withers [mailto:joh...@pixelated.net] Sent: March 5, 2010 1:11 PM To: Steven Staples Cc: mysql@lists.mysql.com Subject: Re: Master/Slave - trucate master table Does START SLAVE fail? If so, what is the output of SHOW SLAVE STATUS? JW On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples sstap...@mnsi.net wrote: Good day everyone... I am working with a master/slave set up, and of course, we started it once the database tables were a combined size of 60gb+ We've got it running and in sync now, (stopped the master, deleted the bin files, rsync'd the database tables and restarted the master) and all has been fine for the last week... until today. We stopped the mysql process on the slave, and did a backup (due to size, we just rsync and backup the rsync) of the table files. Took about 40 minutes to do (still not sure why it was that long, but anyway), and then we restarted the slave mysql. We're getting an error now. Apparently one of the tables is out of sync now? What appears to have happened, is that when the slave was down, i truncated a table on the master. I've read that this sometimes causes errors, is this a bug? Or a is there something I should do differently? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/05/10 02:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=anan...@gmail.com No virus found in this incoming message. Checked by AVG - www.avg.com Version: 9.0.733 / Virus Database: 271.1.1/2711 - Release Date: 03/08/10 02:34:00 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Master/Slave - trucate master table
Good day everyone... I am working with a master/slave set up, and of course, we started it once the database tables were a combined size of 60gb+ We've got it running and in sync now, (stopped the master, deleted the bin files, rsync'd the database tables and restarted the master) and all has been fine for the last week... until today. We stopped the mysql process on the slave, and did a backup (due to size, we just rsync and backup the rsync) of the table files. Took about 40 minutes to do (still not sure why it was that long, but anyway), and then we restarted the slave mysql. We're getting an error now. Apparently one of the tables is out of sync now? What appears to have happened, is that when the slave was down, i truncated a table on the master. I've read that this sometimes causes errors, is this a bug? Or a is there something I should do differently? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=arch...@jab.org
Re: Master/Slave - trucate master table
Does START SLAVE fail? If so, what is the output of SHOW SLAVE STATUS? JW On Fri, Mar 5, 2010 at 10:11 AM, Steven Staples sstap...@mnsi.net wrote: Good day everyone... I am working with a master/slave set up, and of course, we started it once the database tables were a combined size of 60gb+ We've got it running and in sync now, (stopped the master, deleted the bin files, rsync'd the database tables and restarted the master) and all has been fine for the last week... until today. We stopped the mysql process on the slave, and did a backup (due to size, we just rsync and backup the rsync) of the table files. Took about 40 minutes to do (still not sure why it was that long, but anyway), and then we restarted the slave mysql. We're getting an error now. Apparently one of the tables is out of sync now? What appears to have happened, is that when the slave was down, i truncated a table on the master. I've read that this sometimes causes errors, is this a bug? Or a is there something I should do differently? Steven Staples -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql?unsub=joh...@pixelated.net -- - Johnny Withers 601.209.4985 joh...@pixelated.net
slave backups master data
We've got a couple of production databases using mostly MyISAM tables, that can't be taken offline without bringing down our application. To reduce downtime, we run a full mysqldump once a week and back up the binary logs every day, so we can always use them to catch up from the most recent full dump. Because we're mostly using MyISAM we can't make the dump a transaction, so the database is very slow for about 10 minutes while we run the dump. This will get longer and longer as our database grows. We could eliminate the slowness entirely if we ran full dumps off a replication slave. We could do it on a slave not in use by the application at that time. However, the master data in that dump file would refer to the slave's binary logs, not the real master. That means we couldn't use that dump to start new replication slaves, nor to restore a master and catch up (though the latter is less important since we could could run these dumps more often). One tactic that seems to work is to stop replication on the slave, note the master data in show slave status, run a dump, and keep that master data alongside that dump. This is clunky for several reasons; it's harder to automate the backup, harder to automate the restore, and error-prone. Is there any way to do a mysqldump of a replication slave and have that dumpfile contain the master data for the master server? -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave backups master data
Hi Ofer, I prefer to use mylvmbackup and lvm to backup mysql. It's snapshot includes a copy of the relay-log.info file which has the exec_master_log_pos and relay_master_log_file. These allow you to use the snapshot to restore another slave. It's also much faster to take a snapshot of the file system than it is to run mysqldump especially on large datasets. -Eric On 7/16/07, Ofer Inbar [EMAIL PROTECTED] wrote: We've got a couple of production databases using mostly MyISAM tables, that can't be taken offline without bringing down our application. To reduce downtime, we run a full mysqldump once a week and back up the binary logs every day, so we can always use them to catch up from the most recent full dump. Because we're mostly using MyISAM we can't make the dump a transaction, so the database is very slow for about 10 minutes while we run the dump. This will get longer and longer as our database grows. We could eliminate the slowness entirely if we ran full dumps off a replication slave. We could do it on a slave not in use by the application at that time. However, the master data in that dump file would refer to the slave's binary logs, not the real master. That means we couldn't use that dump to start new replication slaves, nor to restore a master and catch up (though the latter is less important since we could could run these dumps more often). One tactic that seems to work is to stop replication on the slave, note the master data in show slave status, run a dump, and keep that master data alongside that dump. This is clunky for several reasons; it's harder to automate the backup, harder to automate the restore, and error-prone. Is there any way to do a mysqldump of a replication slave and have that dumpfile contain the master data for the master server? -- Cos -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- Eric Bergen [EMAIL PROTECTED] http://www.provenscaling.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: slave backups master data
Hi, Ofer Inbar wrote: We've got a couple of production databases using mostly MyISAM tables, that can't be taken offline without bringing down our application. To reduce downtime, we run a full mysqldump once a week and back up the binary logs every day, so we can always use them to catch up from the most recent full dump. Because we're mostly using MyISAM we can't make the dump a transaction, so the database is very slow for about 10 minutes while we run the dump. This will get longer and longer as our database grows. We could eliminate the slowness entirely if we ran full dumps off a replication slave. We could do it on a slave not in use by the This doesn't address your question directly, but I have gotten in the habit of warning people to verify that their slaves do have exactly the same data as the master. Many people assume it does, but when they check, they find out there are differences, usually attributable to bugs in replication. Backing up from slaves is wonderful, but it is only a good idea if the slave's data is right :-) This is why I wrote MySQL Table Checksum, currently as far as I know the only way to verify a slave's data: http://mysqltoolkit.sourceforge.net/ cheers Baron -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Slave behind master... or not ?
Hi, I encounter a really strange behaviour with some of my slaves servers : I'm using MySQL 4.1.20 x86_64 on both master and slave servers. If I issue three times the command SHOW SLAVE STATUS several times in a few seconds, I could obtain the following results for the Seconds_Behind_Master column : 0 48 0 I don't understand how it's possible within 1 or 2 seconds to switch from 0 second behind master to 48 seconds behind master and then back again to 0 second behind master. Any idea of what could be wrong here ? Thanks, Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave behind master... or not ?
--On September 5, 2006 3:18:21 PM +0200 Jocelyn Fournier [EMAIL PROTECTED] wrote: Hi, Any idea of what could be wrong here ? My guess is that the variable is based on the last update timestamp, and the last time that the slave has seen data from the master. If it had been about a minute since the last update, then an update came through and the slave then saw the update it would think oh I'm about a minute behind since my timestamp is about a minute behind the timestamp I just saw. They're not constantly exchanging heartbeats or anything of any kind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave behind master... or not ?
Hi, Thanks for the answer. I don't think this is what's happening because I can also see within a few seconds : 0 48 0 48 0 for example. Thanks, Jocelyn Michael Loftis a écrit : --On September 5, 2006 3:18:21 PM +0200 Jocelyn Fournier [EMAIL PROTECTED] wrote: Hi, Any idea of what could be wrong here ? My guess is that the variable is based on the last update timestamp, and the last time that the slave has seen data from the master. If it had been about a minute since the last update, then an update came through and the slave then saw the update it would think oh I'm about a minute behind since my timestamp is about a minute behind the timestamp I just saw. They're not constantly exchanging heartbeats or anything of any kind. -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Slave behind master... or not ?
Jocelyn, without more information about how many queries your system has, it's impossible to say. I do not know how MySQL calculates the 'Seconds_Behind_Master' data, but: If your system has bursts of queries, why is the data represented below confusing? What if inbetween your 1st and 2nd 'SHOW SLAVE STATUS' query a large amount of replicated queries were processed causing the slave to temporarily lag behind. Atle - Flying Crocodile Inc, Unix Systems Administrator On Tue, 5 Sep 2006, Jocelyn Fournier wrote: Hi, I encounter a really strange behaviour with some of my slaves servers : I'm using MySQL 4.1.20 x86_64 on both master and slave servers. If I issue three times the command SHOW SLAVE STATUS several times in a few seconds, I could obtain the following results for the Seconds_Behind_Master column : 0 48 0 I don't understand how it's possible within 1 or 2 seconds to switch from 0 second behind master to 48 seconds behind master and then back again to 0 second behind master. Any idea of what could be wrong here ? Thanks, Jocelyn -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Making Slave a Master
On Thu, 31 Mar 2005 [EMAIL PROTECTED] wrote: We have one master and one slave database and use the slave for reads. If for some reason our master goes down, we would like to make our slave the master and use it for both writes and reads and then switch to the original configuration when the master is up, which includes updating the master copy. Limited downtime/locking of the second database is OK. Is this something that is easy to do or recommended? If so, what steps we need to go through or where can I find isome nformation regarding this? If not, what other approachs are there (assuming we only have two machines w/ above configuration). Hi, it's not easy, but if you take great precaution and have a thorough understanding of replication you will be able to pull it off. Have you considered a master - master (dual master) replication setup? It's covered in the High Performance MySQL book by Derek Balling and Jeremy Zawodny: http://dev.mysql.com/books/hpmysql-excerpts/ch07.html However, be very careful if you have any AUTO_INCREMENT fields. Good luck! :) Atle - Flying Crocodile Inc, Unix Systems Administrator -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Making Slave a Master
We have one master and one slave database and use the slave for reads. If for some reason our master goes down, we would like to make our slave the master and use it for both writes and reads and then switch to the original configuration when the master is up, which includes updating the master copy. Limited downtime/locking of the second database is OK. Is this something that is easy to do or recommended? If so, what steps we need to go through or where can I find isome nformation regarding this? If not, what other approachs are there (assuming we only have two machines w/ above configuration). Thanks, -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Making Slave a Master
I haven't done it in a nice way and I haven't done it in a long time, but you can do this. In the past, I've done the following: On the slave: 1) stop the server 2) comment out all the lines in my.cnf that refer to the machine as being a slave -- you still need your binary log directives though. You'll also need to nuke all of your binary logs on the slave, along with the status files. 3) start the server and test some updates -- you want to make sure your binary logs are working At this point, you're all set. I'm sure there is a way to do this with a CHANGE MASTER TO command. When you want to turn your master back on, you can use a CHANGE MASTER TO on the master machine. This will make it a slave to your slave server. Once you're all back in sync, you need to disable replication and run the CHANGE MASTER TO command on the slave server. On Thu, 31 Mar 2005 16:02:55 -0700, [EMAIL PROTECTED] [EMAIL PROTECTED] wrote: We have one master and one slave database and use the slave for reads. If for some reason our master goes down, we would like to make our slave the master and use it for both writes and reads and then switch to the original configuration when the master is up, which includes updating the master copy. Limited downtime/locking of the second database is OK. Is this something that is easy to do or recommended? If so, what steps we need to go through or where can I find isome nformation regarding this? If not, what other approachs are there (assuming we only have two machines w/ above configuration). Thanks, -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: Making Slave a Master
Hi Jalil, I had the same problem more than once. I solved it with a (not-so) simple perl script but the idea is very simple: swap the machines! scenario #1: master and slave running - master is used for writes, slave for reads, replication goes well. you have a DNS entry for MASTERDB.domain and SLAVEDB.domain. scenario #2: master fail, slave stop receiving data - now, you should have something to warn you the master went down, otherwise you could spend much time until find out by yourself. - since the time you know, the idea is to change MASTERDB.domain pointing to the same as SLAVEDB.domain so both reading and writing is happening on the slave server. - at this time you broke the chain between what had on master and slave, so replication will not happen again automatically. - now you can fix your master. - change the master config to stop doing log-bin and start as a slave using SLAVEDB.machine as master. - edit my.cfg on slave's machine to start log-bin and stop trying to get data from master. - restart the slave, now as master. - restart the master, now as slave and issue a LOAD DATA FROM MASTER - start the old master (new slave) slave status and sync them. - point SLAVEDB.domain to the old master machine. scenario #3: machines swapped, same scenario - now you have exactly the same thing as before, but your machines are swapped. You can do it as many times as you want. - If the machines are the same you can keep that scenario untill the new master breaks. - If the (old) master db is much better you can do the same trick again on a low-traffic hour. This swap may take you no more than an hour, plus the time to fix the broken machine. The trick with domains could also be done using IP alias on the interface if you're connecting the machines using IPs. I used IPs instead of names. hope that helps, --rengolin --- [EMAIL PROTECTED] wrote: We have one master and one slave database and use the slave for reads. If for some reason our master goes down, we would like to make our slave the master and use it for both writes and reads and then switch to the original configuration when the master is up, which includes updating the master copy. Limited downtime/locking of the second database is OK. Is this something that is easy to do or recommended? If so, what steps we need to go through or where can I find isome nformation regarding this? If not, what other approachs are there (assuming we only have two machines w/ above configuration). Thanks, -JF -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED] Yahoo! Acesso Grátis - Internet rápida e grátis. Instale o discador agora! http://br.acesso.yahoo.com/ -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Unable to shutdown slave when master isn't available.
We have a failed master right now... If I attempt to shutdown any of my slaves right now they ALL timeout when trying to shutdown. To make matters MUCH worse I can no longer connect to the STILL running mysql daemon. So the only way to restart is with a killall -9 which results in a corrupt database on the slave. This is REALLY bad! I'm sure MySQL could do a better job here. No need for a corrupt database. Why not have the control port be the LAST thing to shutdown so if it fails you can still connect and FLUSH TABLES. Kevin -- Use Rojo (RSS/Atom aggregator). Visit http://rojo.com. Ask me for an invite! Also see irc.freenode.net #rojo if you want to chat. Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html If you're interested in RSS, Weblogs, Social Networking, etc... then you should work for Rojo! If you recommend someone and we hire them you'll get a free iPod! Kevin A. Burton, Location - San Francisco, CA AIM/YIM - sfburtonator, Web - http://peerfear.org/ GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412 -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication - promoting slave to master
I would need log-bin and log-slave-updates enabled on the slave, correct? So to automate the process it would be better to start both servers without the Master-host info in the conf file, letting Heartbeat issue the commands on startup to convert one box to slave. During a fail-over, heartbeat would take down the master server and issue the stop slave command on the slave.Would deleting the master.info be needed at this point, or would the stop slave be enough to accept writes? In theory, a slave will look at the server-id and will not process statements where the server-id matches its own server-id So is another option to tell the slave to change master to itself? Thanks, Scott Tanner System Administrator AMI International Victor Pendleton [EMAIL PROTECTED] 06/17/2004 01:41 PM To: '[EMAIL PROTECTED] ' [EMAIL PROTECTED], '[EMAIL PROTECTED] ' [EMAIL PROTECTED] cc: Subject:RE: Replication - promoting slave to master Initially you should have set the slave up with log-bin in its my.cnf file so that it is writing to its own binary logs. In the event that the master goes down, you should issue a change master on any other slaves so that they point to the new master. Redirect all writes to the new master. On the new master you will want to remove references to the old master. A slave stop and the removing of the master.info file will work. But you should still edit the my.cnf file to prevent the machine from trying to reattach at the next startup. In theory, a slave will look at the server-id and will not process statements where the server-id matches its own server-id. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 6/17/04 12:25 PM Subject: Replication - promoting slave to master Hello, I've been lurking about the lists for a few months now, hoping to gain some knowledge on MySQL's replication features for HA setup. I have a fairly good understanding of the setup process, but I'm a little confused on one points. We are running a 2 node - master-slave setup, and we are planning on using Heartbeat (from the Linux-HA project) to handle failover. If the Master fails, the slave will take over permanently, and the master will be rebuilt as a slave. What is the procedure for promoting a slave server to master? If the slave server has the Master-Host, User, and Password set in the conf file, will the Stop Slave command be enough to promote it, or will I need to remove the master.info file as well? Thank you, Scott Tanner System Administrator AMI International
RE: Replication - promoting slave to master
If the server has log-bin enabled it will log writes. If you have a daisy-chained master, one that serves as a master to other slaves, you will need to have log-slave-updates enabled. If this server is just another machine pulling from the master it is not necessary to log slave updates but it is necessary to have log-bin enabled in the event that you will be promoting this server to master one day. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 6/18/04 9:09 AM Subject: RE: Replication - promoting slave to master I would need log-bin and log-slave-updates enabled on the slave, correct? So to automate the process it would be better to start both servers without the Master-host info in the conf file, letting Heartbeat issue the commands on startup to convert one box to slave. During a fail-over, heartbeat would take down the master server and issue the stop slave command on the slave.Would deleting the master.info be needed at this point, or would the stop slave be enough to accept writes? In theory, a slave will look at the server-id and will not process statements where the server-id matches its own server-id So is another option to tell the slave to change master to itself? Thanks, Scott Tanner System Administrator AMI International Victor Pendleton [EMAIL PROTECTED] 06/17/2004 01:41 PM To: '[EMAIL PROTECTED] ' [EMAIL PROTECTED], '[EMAIL PROTECTED] ' [EMAIL PROTECTED] cc: Subject:RE: Replication - promoting slave to master Initially you should have set the slave up with log-bin in its my.cnf file so that it is writing to its own binary logs. In the event that the master goes down, you should issue a change master on any other slaves so that they point to the new master. Redirect all writes to the new master. On the new master you will want to remove references to the old master. A slave stop and the removing of the master.info file will work. But you should still edit the my.cnf file to prevent the machine from trying to reattach at the next startup. In theory, a slave will look at the server-id and will not process statements where the server-id matches its own server-id. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 6/17/04 12:25 PM Subject: Replication - promoting slave to master Hello, I've been lurking about the lists for a few months now, hoping to gain some knowledge on MySQL's replication features for HA setup. I have a fairly good understanding of the setup process, but I'm a little confused on one points. We are running a 2 node - master-slave setup, and we are planning on using Heartbeat (from the Linux-HA project) to handle failover. If the Master fails, the slave will take over permanently, and the master will be rebuilt as a slave. What is the procedure for promoting a slave server to master? If the slave server has the Master-Host, User, and Password set in the conf file, will the Stop Slave command be enough to promote it, or will I need to remove the master.info file as well? Thank you, Scott Tanner System Administrator AMI International -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
RE: Replication - promoting slave to master
[EMAIL PROTECTED] wrote on 18/06/2004 15:09:58: I would need log-bin and log-slave-updates enabled on the slave, correct? So to automate the process it would be better to start both servers without the Master-host info in the conf file, letting Heartbeat issue the commands on startup to convert one box to slave. During a fail-over, heartbeat would take down the master server and issue the stop slave command on the slave.Would deleting the master.info be needed at this point, or would the stop slave be enough to accept writes? That is what we do. We have an HA system of single master and one or more slaves in a line. All systems start up with slave threads NOT running. At startup, the heartbeat does not know which machine was previously master and which slave(s) in what order. We have a special 1 row, 1 column table known, for reasons lost in the past, as the Fishbowl value. This is incremented every time the configuration changes. We therefore read the fishbowl value from all machines. Only machines with the largest value of the fishbowl are candidates for becoming master. We then read the slave status of all the machines which have this value. Reading the name of the machine to which it is slaved, we should be able to organise them into one or more chains of consistent master and slave. The longest such chain becomes the live chain, and its head becomes the master. Slave threads are then started on all the slaves in this chain. We now have a running system. All writes are directed to the master, reads can be directed to any slave (because we have designed around transactions and locking problems). All machines with less than the maximum fishbowl value, or which form part of shorter chains, or simply do not have the databases on them (status when new or repaired machine is added) have the databases dropped and are regarded as idle. One by one, the idle machines are slaved to the last slave in the chain and loaded up with LOAD DATA FROM MASTER, then slave threads started. The heartbeat continuously monitors all machines. If the master fails, it is simply necessary to stop the slave thread on the first slave (now master), increment the fishbowl value on it (which will ripple down to all remaining slaves) and direct all writes to it. If the ex-master reappears, it will have an out-of-dated fishbowl value and will therefore have its database dumped and reloaded. We do not bother to delete master.info. It will point to a database with an out-of-date fishbowl, which can be ignored. In theory, a slave will look at the server-id and will not process statements where the server-id matches its own server-id So is another option to tell the slave to change master to itself? This sounds a bit dodgy to me. Strictly speaking, if the master is truly dead, it is not necessary even to stop the slave thread: there is no reason not to write to the database while slaving IF no conflicting updates are being replicated from the master. Practically, it is definitely advisable to do so in case the master is rebooting. Alec -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Replication - promoting slave to master
Hello, I've been lurking about the lists for a few months now, hoping to gain some knowledge on MySQL's replication features for HA setup. I have a fairly good understanding of the setup process, but I'm a little confused on one points. We are running a 2 node - master-slave setup, and we are planning on using Heartbeat (from the Linux-HA project) to handle failover. If the Master fails, the slave will take over permanently, and the master will be rebuilt as a slave. What is the procedure for promoting a slave server to master? If the slave server has the Master-Host, User, and Password set in the conf file, will the Stop Slave command be enough to promote it, or will I need to remove the master.info file as well? Thank you, Scott Tanner System Administrator AMI International
RE: Replication - promoting slave to master
Initially you should have set the slave up with log-bin in its my.cnf file so that it is writing to its own binary logs. In the event that the master goes down, you should issue a change master on any other slaves so that they point to the new master. Redirect all writes to the new master. On the new master you will want to remove references to the old master. A slave stop and the removing of the master.info file will work. But you should still edit the my.cnf file to prevent the machine from trying to reattach at the next startup. In theory, a slave will look at the server-id and will not process statements where the server-id matches its own server-id. -Original Message- From: [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: 6/17/04 12:25 PM Subject: Replication - promoting slave to master Hello, I've been lurking about the lists for a few months now, hoping to gain some knowledge on MySQL's replication features for HA setup. I have a fairly good understanding of the setup process, but I'm a little confused on one points. We are running a 2 node - master-slave setup, and we are planning on using Heartbeat (from the Linux-HA project) to handle failover. If the Master fails, the slave will take over permanently, and the master will be rebuilt as a slave. What is the procedure for promoting a slave server to master? If the slave server has the Master-Host, User, and Password set in the conf file, will the Stop Slave command be enough to promote it, or will I need to remove the master.info file as well? Thank you, Scott Tanner System Administrator AMI International -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
One Slave Many Master
I know someone already ask this, and the answer generally 'NO YOU CANT' :b but, is there any work around so i can make a backup server (slave), from many other server (master) through replication? well, it's not just a backup server actually.. it really is the headquarter's db server, and the branch offices can not directly post their transaction.. because the line is teriblyy sllw :D so, im thinking of adding extra server on branches and replicate what ever happens there to the main server perhaps, if i can run multiple mysql server on different port on the main server, with it's own database, that act as a slave one to one to other server, and add one more that handle all of the DB .. Main ServerBranch ----- DB1, 3306 DB1, 3306, BranchServer1 DB2, 3307 DB2, 3307, BranchServer2 DB3, 3308 DB3, 3308, BranchServer3 DB4, 3309 DB4, 3309, BranchServer4 and so on... MASTER DB(DB1,DB2...DBn) on port 33xx is this possible??? or maybe there are other solution.. pleasseee.. i really appreciate it oh, one more thing, can the replication run both way? what ever happened on machine A, replicated on machine B and vice versa.. thanks... -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com
Re: One Slave Many Master
On Mon, 29 Dec 2003, Leo wrote: I know someone already ask this, and the answer generally 'NO YOU CANT' :b but, is there any work around so i can make a backup server (slave), from many other server (master) through replication? You could run a server instance per database replicated and use the same data-dir (be careful of conflicting writes, however, you probably want to enable-external-locking, especially if they share any data). oh, one more thing, can the replication run both way? what ever happened on machine A, replicated on machine B and vice versa.. That works fine, just be aware of the problems that can arise if this is not taken into consideration since the replication is asynchronous read the Q: What issues should I be aware of when setting up two-way replication? part at http://www.mysql.com/doc/en/Replication_FAQ.html thanks... -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: One Slave Many Master
Hi Leo, a quick search for multiple daemons at www.mysql.com/doc reveals: http://www.mysql.com/doc/en/Multiple_servers.html This should give you all your answers and make it possible to do what you want. Hope this helps, Ken - Original Message - From: Leo [EMAIL PROTECTED] To: [EMAIL PROTECTED] Sent: Monday, December 29, 2003 2:28 AM Subject: One Slave Many Master I know someone already ask this, and the answer generally 'NO YOU CANT' :b but, is there any work around so i can make a backup server (slave), from many other server (master) through replication? well, it's not just a backup server actually.. it really is the headquarter's db server, and the branch offices can not directly post their transaction.. because the line is teriblyy sllw :D so, im thinking of adding extra server on branches and replicate what ever happens there to the main server perhaps, if i can run multiple mysql server on different port on the main server, with it's own database, that act as a slave one to one to other server, and add one more that handle all of the DB .. Main ServerBranch ----- DB1, 3306 DB1, 3306, BranchServer1 DB2, 3307 DB2, 3307, BranchServer2 DB3, 3308 DB3, 3308, BranchServer3 DB4, 3309 DB4, 3309, BranchServer4 and so on... MASTER DB(DB1,DB2...DBn) on port 33xx is this possible??? or maybe there are other solution.. pleasseee.. i really appreciate it oh, one more thing, can the replication run both way? what ever happened on machine A, replicated on machine B and vice versa.. thanks... -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]
Re: One Slave Many Master
Thank You All for the response, I'll try to set up your recomendation.. I'll post the result back in a few days.. thanks :) -- Regards Leonardus Setiabudi IT Project Coordinator PT Bina San Prima, www.binasanprima.com
Re: how to elect slave as master?
On Wed, Jul 18, 2001 at 11:14:51AM -0700, Ricardo Kleemann wrote: Thank you. But then how can mysql replication be used for high availability? If I always rely on 1 master only, what happens if the master goes down? I've tried two-way master-slave and I understand there are problems. :-( Have you experienced problems with it? If you're careful, 2-way replication works. It has been discussed on the list a fair amount. Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 349-7878 Fax: (408) 349-5454 Cell: (408) 685-5936 -- NEW MySQL 3.23.29: up 33 days, processed 263,837,728 queries (89/sec. avg) - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
how to elect slave as master?
Hi everyone, I'm wondering how I should configure my.cnf if I have a master-slave scenario where I would like to declare the slave master if the master goes down. What I mean is, let's say I have 2 database servers: server1 = master server2 = slave so normally my.cnf for server2 would have server1 configured as master-host, and server1 would not have any master database server configured. So assuming server1 goes down, I'd have to declare server2 as master, and have it no longer use the master-host. My confusion is mostly with server1. When it comes back up, how is it supposed to work? Is it supposed to suddenly become a slave? Or does it again come up as master and then server2 relinquishes its master status? How can all of this be done automatically? It seems a little confusing to me to have to have all this automatic switching... HELP! :-) Ricardo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how to elect slave as master?
Hi Ricardo From my opinion, and yet again the pro's may dispute it, I understand what you actually want is a two way slave situation, in that way, when one goes down the second is there to take over, and when server1 comes back up if will update itself's from server2 and carry on. The rest of (front end web, or app or whatever) is upto you, you will need to configure your app to use server1 when available or server2. two way replication is set up by setting server2 as a slave to server1, and vice versa. THe scenario you describe will not work, because when server1 comes back up it will not have the changes that has occured since Server2 took over. Beware though, two way replication causes serious issues with auto-numbering, and it is best to handle this yourself, in my opinion. Regards Warren ~ Warren van der Merwe Software Director PRT Trading (Pty) Ltd t/a RedTie Durban, South Africa Cell (+27-83) 262-9163 Office (+27-31) 767-0249 -Original Message- From: Ricardo Kleemann [mailto:[EMAIL PROTECTED]] Sent: 18 July 2001 19:37 To: [EMAIL PROTECTED] Subject: how to elect slave as master? Hi everyone, I'm wondering how I should configure my.cnf if I have a master-slave scenario where I would like to declare the slave master if the master goes down. What I mean is, let's say I have 2 database servers: server1 = master server2 = slave so normally my.cnf for server2 would have server1 configured as master-host, and server1 would not have any master database server configured. So assuming server1 goes down, I'd have to declare server2 as master, and have it no longer use the master-host. My confusion is mostly with server1. When it comes back up, how is it supposed to work? Is it supposed to suddenly become a slave? Or does it again come up as master and then server2 relinquishes its master status? How can all of this be done automatically? It seems a little confusing to me to have to have all this automatic switching... HELP! :-) Ricardo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
RE: how to elect slave as master?
Thank you. But then how can mysql replication be used for high availability? If I always rely on 1 master only, what happens if the master goes down? I've tried two-way master-slave and I understand there are problems. :-( On Wed, 18 Jul 2001, Warren van der Merwe wrote: Hi Ricardo From my opinion, and yet again the pro's may dispute it, I understand what you actually want is a two way slave situation, in that way, when one goes down the second is there to take over, and when server1 comes back up if will update itself's from server2 and carry on. The rest of (front end web, or app or whatever) is upto you, you will need to configure your app to use server1 when available or server2. two way replication is set up by setting server2 as a slave to server1, and vice versa. THe scenario you describe will not work, because when server1 comes back up it will not have the changes that has occured since Server2 took over. Beware though, two way replication causes serious issues with auto-numbering, and it is best to handle this yourself, in my opinion. Regards Warren ~ Warren van der Merwe Software Director PRT Trading (Pty) Ltd t/a RedTie Durban, South Africa Cell (+27-83) 262-9163 Office (+27-31) 767-0249 -Original Message- From: Ricardo Kleemann [mailto:[EMAIL PROTECTED]] Sent: 18 July 2001 19:37 To: [EMAIL PROTECTED] Subject: how to elect slave as master? Hi everyone, I'm wondering how I should configure my.cnf if I have a master-slave scenario where I would like to declare the slave master if the master goes down. What I mean is, let's say I have 2 database servers: server1 = master server2 = slave so normally my.cnf for server2 would have server1 configured as master-host, and server1 would not have any master database server configured. So assuming server1 goes down, I'd have to declare server2 as master, and have it no longer use the master-host. My confusion is mostly with server1. When it comes back up, how is it supposed to work? Is it supposed to suddenly become a slave? Or does it again come up as master and then server2 relinquishes its master status? How can all of this be done automatically? It seems a little confusing to me to have to have all this automatic switching... HELP! :-) Ricardo - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Replication Slave to Master
How much difficulty is there in turning a MySQL replication server into a master?
Slave to Master Connection
Hi there, the error that i specifically get when the slave tries to connect to the master is 010409 10:44:58 Slave thread: error connecting to master:Can't connect to MySQL server on '192.168.1.2' (111)(107), retry in 60 sec even if i try to run mysql -h master the same error happens. what could be wrong ?? thanks bye Mathew - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Re: Slave to Master Connection
On Mon, Apr 09, 2001 at 10:37:36AM +0530, MCA Department PESIT wrote: the error that i specifically get when the slave tries to connect to the master is 010409 10:44:58 Slave thread: error connecting to master:Can't connect to MySQL server on '192.168.1.2' (111)(107), retry in 60 sec even if i try to run mysql -h master the same error happens. what could be wrong ?? Are the username/password correct? Is there anything in the error log on the master? Jeremy -- Jeremy D. Zawodny, [EMAIL PROTECTED] Technical Yahoo - Yahoo Finance Desk: (408) 328-7878Fax: (408) 530-5454 Cell: (408) 439-9951 - Before posting, please check: http://www.mysql.com/manual.php (the manual) http://lists.mysql.com/ (the list archive) To request this thread, e-mail [EMAIL PROTECTED] To unsubscribe, e-mail [EMAIL PROTECTED] Trouble unsubscribing? Try: http://lists.mysql.com/php/unsubscribe.php
Mutual Replication Fails Occasionally Under Heavy Stress with Master-Slave/Slave-Master Configuration
Description:
While setting up a mutual master-slave/slave-master MySQL application for
High-Availability, redundancy, and load-balancing, the autoincrement functionality of
disparate servers seem to be detached from replication functionality. It's likely the
replication code can be improved to consider multiple master-slave arrangements
trivially.
How-To-Repeat:
Get yourself a 64-bit DEC Alpha Linux server (Not Necessary, but, Fun):
Linux tempe.dedserius.com 2.4.2 #3 Sun Feb 25 16:07:31 MST 2001 alpha unknown
root@tempe:~# cat /proc/cpuinfo
cpu : Alpha
cpu model : EV5
cpu variation : 0
cpu revision: 0
cpu serial number : Linux_is_Great!
system type : Alcor
system variation: 0
system revision : 0
system serial number: MILO-
cycle frequency [Hz]: 300014100 est.
timer frequency [Hz]: 1024.00
page size [bytes] : 8192
phys. address bits : 40
max. addr. space # : 127
BogoMIPS: 593.48
kernel unaligned acc: 106 (pc=fc4f26e0,va=fc000417a836)
user unaligned acc : 1 (pc=1200cda00,va=2f8a912)
platform string : N/A
cpus detected : 0
root@tempe:~# free
total used free sharedbuffers cached
Mem:189440 184224 5216 0 42696 17808
-/+ buffers/cache: 123720 65720
Swap: 257008 3104 253904
In separate terminals the following was run from Tempe on a 100/Mbps HUB-based network:
Tempe passes data at 8MBytes/Sec over his NIC. Connectivity is not likely to be a
factor.
/usr/local/apache/bin/ab -n 100 -c 20 http://scottsdale.ds.lan/index.php3
/usr/local/apache/bin/ab -n 100 -c 20 http://pima.ds.lan/index.php3
The index.php3 script does many things, but, included is an insert to a db with 2
tables:
Newstats with the following structure:
CREATE TABLE newstats (
nid int(10) NOT NULL auto_increment,
Referer varchar(150) NOT NULL,
UserAgent varchar(75) NOT NULL,
ScriptFilename varchar(150) NOT NULL,
RemoteHost varchar(50) NOT NULL,
RemoteAddr varchar(15) NOT NULL,
HitTime datetime DEFAULT '-00-00 00:00:00' NOT NULL,
Host varchar(25) NOT NULL,
AcceptLanguage varchar(15) NOT NULL,
QryString varchar(150) NOT NULL,
PRIMARY KEY (nid),
KEY HitTime (HitTime),
KEY RemoteHost (RemoteHost),
KEY RemoteAddr (RemoteAddr),
KEY ScriptFilename (ScriptFilename)
);
CREATE TABLE pagestats (
uri varchar(96) NOT NULL,
hits bigint(21) NOT NULL,
since datetime,
PRIMARY KEY (uri)
);
CREATE TABLE excludes (
ip varchar(25)
);
The query selects from excludes and if the host isn't in there does an insert into
both of the first tables.
After the query on excludes, it does a select from both tables. So for each hit,
there's a select on excludes and an insert if not found, then a select on the tables.
Queries:
SELECT * FROM excludes WHERE ip = '192.168.1.2'
UPDATE pagestats SET hits=hits+1 WHERE uri = '/www/www.dedserius.com/index.php3'
INSERT INTO newstats VALUES ('', '', 'ApacheBench/1.3c',
'/www/www.dedserius.com/index.php3', 'tempe.ds.lan', '192.168.1.2', '20010320
00:34:47', 'scottsdale.ds.lan', '', '/index.php3')
Scottsdale is a Dual 400 MHz Celeron with 128MBytes RAM;
Pima is a Single PII 200 MHz with 64MBytes RAM;
Both machines run Slackware Linux 7.2; patches for GLIBC 2.2.
scottsdale:/mysql# gcc -v
Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
scottsdale:/mysql# uname -a
Linux scottsdale 2.3.99-pre9 #2 SMP Fri Feb 23 16:14:56 MST 2001 i686 unknown
root@pima:/mysql# gcc -v
Reading specs from /usr/lib/gcc-lib/i386-slackware-linux/2.95.2/specs
gcc version 2.95.2 19991024 (release)
root@pima:/mysql# uname -a
Linux pima 2.4.2 #2 Wed Mar 14 01:56:45 MST 2001 i586 unknown
MySQL-3.23.33 built from source.
./configure --with-mysqld-user=mysql --localstatedir=/mysql
I have public and private name servers. Public servers end with dedserius.com.
Private with ds.lan and reverse nslookup on privates function. DBase host
authentication is based on DNS not Host lookups. This is for future implementation of
round-robbin DNS decisions.
The replication succeeded when hitting Scottsdale, first.
Failed when hitting Pima first.
Failure was due to autoincrement field containing duplicate value during the benchmark.
my.cnf:
Scottsdale:
# The MySQL server
[mysqld]
port= 3306
log-bin = scottsdale-bin.log
server-id
