Replication config

Hello, I am confused about repliction setup. Reading a config file,
and the docs, leads me to believe this is an either code choice, pick
#1 or #2. If that is the case, why would I want to use #1 over #2?

My confusion comes from several online references where there is a
combination of #1 and #2 going on:

# To configure this host as a replication slave, you can choose between
# two methods :
#
# 1) Use the CHANGE MASTER TO command (fully described in our manual) -
# the syntax is:
#
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
#
# where you replace , , by quoted strings and
# by the master's port number (3306 by default).
#
# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,
# MASTER_USER='joe', MASTER_PASSWORD='secret';
#
# OR
#
# 2) Set the variables below. However, in case you choose this method,
then
# start replication for the first time (even unsuccessfully, for
example
# if you mistyped the password in master-password and the slave
fails to
# connect), the slave will create a master.info file, and any later
# change in this file to the variables' values below will be
ignored and
# overridden by the content of the master.info file, unless you
shutdown
# the slave server, delete master.info and restart the slaver server.
# For that reason, you may want to leave the lines below untouched
# (commented) and instead use CHANGE MASTER TO (see above)

--
Scott * If you contact me off list replace talklists@ with scott@ *


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Replication config

talklists@newgeo.com (Scott Haneda) writes:

> Hello, I am confused about repliction setup. Reading a config file,
> and the docs, leads me to believe this is an either code choice, pick
> #1 or #2. If that is the case, why would I want to use #1 over #2?
>
> My confusion comes from several online references where there is a
> combination of #1 and #2 going on:
>
> # To configure this host as a replication slave, you can choose between
> # two methods :
> #
> # 1) Use the CHANGE MASTER TO command (fully described in our manual) -
> # the syntax is:
> #
> # CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
> # MASTER_USER=, MASTER_PASSWORD= ;
> #
> # where you replace , , by quoted strings and
> # by the master's port number (3306 by default).

Use this method. it works and is the correct way to do things. It also will keep
working if you stop and restart the server with replication carrying on
from where it left off.

The procedure is quite simply:

1. Ensure binlogging is enabled on the master.
2. Ensure you setup grant permissions so the slave can connect to the master.
3. Configure on the slave the replication (which databases need to be replicated)
4. Get the master and slave in sync (via rsync, load/dump or whatever)
5. Run show master status on the master (assuming binlogging is enabled)
to get the current position on the master
6. use CHANGE MASTER TO on the slave providing the appropriate permissions.
7. Run: START SLAVE
8. Use: show slave status\G to check how the replication is working, and
and adjust as necessary.

It's true that initial mysql replication setup is a bit fiddly, but once you've
done it once or twice it's not so hard.

Simon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Replication config

Thanks, a few comments inline below...

On May 12, 2009, at 11:11 PM, Simon J Mudd wrote:

> talklists@newgeo.com (Scott Haneda) writes:
>
>> Hello, I am confused about repliction setup. Reading a config file,
>> and the docs, leads me to believe this is an either code choice, pick
>> #1 or #2. If that is the case, why would I want to use #1 over #2?
>>
>> My confusion comes from several online references where there is a
>> combination of #1 and #2 going on:
>>
>> # To configure this host as a replication slave, you can choose
>> between
>> # two methods :
>> #
>> # 1) Use the CHANGE MASTER TO command (fully described in our
>> manual) -
>> # the syntax is:
>> #
>> # CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
>> # MASTER_USER=, MASTER_PASSWORD= ;
>> #
>> # where you replace , , by quoted strings
>> and
>> # by the master's port number (3306 by default).
>
> Use this method. it works and is the correct way to do things. It
> also will keep
> working if you stop and restart the server with replication carrying
> on
> from where it left off.
>
> The procedure is quite simply:
>
> 1. Ensure binlogging is enabled on the master.

Done, I can see the binlogs as well.

> 2. Ensure you setup grant permissions so the slave can connect to
> the master.

From the remote MySql I have mysql -u user -h example.com -p
It allows me in so I think I am good there.

> 3. Configure on the slave the replication (which databases need to
> be replicated)

This is where I need a little clarification, is the only thing I need
to do is adjust my.cnf to have in the [mysqld] section
server-id = 2

> 4. Get the master and slave in sync (via rsync, load/dump or whatever)

Is this mandatory? There is not a lot of data, hundred rows or so,
can I use LOAD DATA FROM MASTER; ?

Seems most instructions say to use a dump. This does not make a lot
of sense to me, I am setting up replication, is it not the point to be
able to pull the data down? Why does it need "priming" like this?

> 5. Run show master status on the master (assuming binlogging is
> enabled)
> to get the current position on the master

I can do this now, gives back a position. It seems to change over
time. Since it is a moving target, if I am using LOAD DATA FROM
MASTER; I take it I need to lock the tables while the first load is
happening?

> 6. use CHANGE MASTER TO on the slave providing the appropriate
> permissions.
> 7. Run: START SLAVE

Will do, thanks.

> 8. Use: show slave status\G to check how the replication is working,
> and
> and adjust as necessary.

Thanks.

> It's true that initial mysql replication setup is a bit fiddly, but
> once you've
> done it once or twice it's not so hard.


While not part of my plan, if the master goes down and I want to start
using the slave as the master while I am fixing the master server....

What is the best way to do this? Can the slave be treated like a
master by just pointing any client to the slave assuming I set a user
to allow it?

With the slave temporarily becoming the master, the data will of
course change. When I bring the master back online, what is the best
way to reverse sync and get back to where I was? Probably take the
entire thing thing down, copy the database from the current temp live
slave that has been used as a master, and go from there?

Thank you.
--
Scott * If you contact me off list replace talklists@ with scott@ *


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Replication config

replying only to the list...

On Wed, May 13, 2009 at 10:19:21AM -0700, Scott Haneda wrote:

> >3. Configure on the slave the replication (which databases need to
> >be replicated)
>
> This is where I need a little clarification, is the only thing I need
> to do is adjust my.cnf to have in the [mysqld] section
> server-id = 2

That's the minimal configuration. You may need to specify which databases
need to be replicated or which tables. By default everything is replicated
which is probably fine.

> >4. Get the master and slave in sync (via rsync, load/dump or whatever)
>
> Is this mandatory? There is not a lot of data, hundred rows or so,
> can I use LOAD DATA FROM MASTER; ?

I think that only works in MySQL 4, and have never used it on our production
servers (5.0). Yes, checking the MySQL 5. documentation it says: http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master .html

--quote--
12.6.2.2. LOAD DATA FROM MASTER Syntax

LOAD DATA FROM MASTER

This feature is deprecated. We recommend not using it anymore. It is
subject to removal in a future version of MySQL.
--quote--

If you've only got hundreds of rows then just do a normal mysqldump.
The problem is that if you have a database with GB or hundreds of GB of data
then this process is really slow, and if at the same time you can't afford
to stop your master then that makes life harder.

> Seems most instructions say to use a dump. This does not make a lot
> of sense to me, I am setting up replication, is it not the point to be
> able to pull the data down? Why does it need "priming" like this?

For 5.0 and above because the you can't load DATA from master, so just
use the dump, and don't change the master while you are doing this.

> >5. Run show master status on the master (assuming binlogging is
> >enabled)
> > to get the current position on the master
>
> I can do this now, gives back a position. It seems to change over
> time. Since it is a moving target, if I am using LOAD DATA FROM
> MASTER; I take it I need to lock the tables while the first load is
> happening?

If you're using 5.0 you shouldn't be using LOAD DATA FROM MASTER.
Do a mysqldump and load from that, or if you use some sort of unix with
snapshotting possibilities then make a (lvm) snapshot of the filesystem
and copy that. That's what we typically do at work and it leaves the
master down for just a second or so. The later copy can take place
while the master is running.

> >6. use CHANGE MASTER TO on the slave providing the appropriate
> >permissions.

This just tells the slave where to start replicating from. That is
which statements or rows in the binlog to download from the master
and apply on the slave.

> >7. Run: START SLAVE

This starts the replication process.

> While not part of my plan, if the master goes down and I want to start
> using the slave as the master while I am fixing the master server....
>
> What is the best way to do this? Can the slave be treated like a
> master by just pointing any client to the slave assuming I set a user
> to allow it?

You can do this from the point of view of the database users but then
the slave will be more up to date than the master and if you've not
configured things properly and don't have the right information you
won't be able to get the master back in sync.

So you can't just switch between boxes without taking special care.

> With the slave temporarily becoming the master, the data will of
> course change. When I bring the master back online, what is the best
> way to reverse sync and get back to where I was? Probably take the
> entire thing thing down, copy the database from the current temp live
> slave that has been used as a master, and go from there?

If the end that may be necessary. You can configure master / master
replication but as I said you have to be careful with this as it can
be quite critical how you actually setup your tables. If you don't do
things correctly it won't work. I think it is documented however in
the MySQL manual so I'd suggest you read that.

Simon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Replication config

Hi Scott

You may use the script below to reload replication if you can ensure that
the master db doesn't change during the dump operation. Otherwise you may
set a lock on the master manually.

Regards, Thomas


#!/bin/bash
#
# replicate-reload
#
# This is free software. There is no warranty at all.
# The program may melt your computer and kill your cat.
# Use at your own risk.
#
# restart new replication of DBASE on localhost; dump from MASTER
#
# Note: No changes to DBASE may take place on the master during
# the dump operation. See comments below.
#
# Set your values here:
DBASE=adbtoreplicate
MASTER=host.domain.tld
MYUSER=useronlocalhost
MYPWD=thisisagoodpassword
# Set replication user and password
REPLUSER=replicationuser
REPLPWD=replicationuserpassword
# End of user configuration

SPACE=' '
TAB=$(echo -ne "\t")

MASTER_ALIAS=$(echo $MASTER | sed -e "s/\\..*//")
MASTER_POS=$(echo "FLUSH TABLES WITH READ LOCK;
SHOW MASTER STATUS;" | mysql -u $MYUSER -h $MASTER -p$MYPWD $DBASE \
| sed -e "/^${MASTER_ALIAS}-bin/ !d")

############################################################ #####
# Beware: From this point on no changes on the master may be made
# until the dump has finished. If this can't be enforced you
# have to place a lock manually on the master and release it
# once the dump is complete.
############################################################ #####

MASTER_FILE=$(echo "$MASTER_POS" | cut -s -d "$TAB" -f 1)
MASTER_LOGPOS=$(echo "$MASTER_POS" | cut -s -d "$TAB" -f 2)

#echo MASTER_POS="$MASTER_POS"
echo MASTER_FILE=$MASTER_FILE
echo MASTER_LOGPOS=$MASTER_LOGPOS

# Get the dump
echo "Dumping '$DBASE' from $MASTER"
#
# User: set your own dump options here as needed
mysqldump -u $MYUSER -h $MASTER -p$MYPWD \
--skip-opt \
--add-drop-table \
--max_allowed_packet=1M \
--character-sets-dir=/usr/share/mysql/charsets \
--skip-set-charset \
--extended-insert --lock-all-tables --quick \
--quote-names --master-data=2 $DBASE \
| sed -e "/^SET / d" > ${DBASE}.sql

############################################################ #####
# Note: Changes on the master are allowed from here on
############################################################ #####

echo -e "\nCHANGE MASTER TO MASTER_HOST='$MASTER', \
MASTER_USER='$REPLUSER', MASTER_PASSWORD='$REPLPWD', \
MASTER_LOG_FILE='$MASTER_FILE', MASTER_LOG_POS=${MASTER_LOGPOS};" \
> ${DBASE}.sync.sql

echo "STOP SLAVE;" | mysql -u $MYUSER -h localhost -p$MYPWD $DBASE

# reload dumped database
echo "Reloading '${DBASE}' on localhost"
cat ${DBASE}.sql ${DBASE}.sync.sql | mysql \
-u $MYUSER -h localhost -p$MYPWD $DBASE

echo "Starting slave $(hostname)"
echo "START SLAVE;" | mysql \
-u $MYUSER -h localhost -p$MYPWD -E $DBASE

sleep 2

echo "SHOW SLAVE STATUS;" | mysql \
-u $MYUSER -h localhost -p$MYPWD -E $DBASE

rm -f ${DBASE}.sql ${DBASE}.sync.sql
exit 0

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Replication config

On May 12, 2009, at 11:11 PM, Simon J Mudd wrote:

> talklists@newgeo.com (Scott Haneda) writes:
>
>> Hello, I am confused about repliction setup. Reading a config file,
>> and the docs, leads me to believe this is an either code choice, pick
>> #1 or #2. If that is the case, why would I want to use #1 over #2?
>>
>> My confusion comes from several online references where there is a
>> combination of #1 and #2 going on:
>>
>> # To configure this host as a replication slave, you can choose
>> between
>> # two methods :
>> #
>> # 1) Use the CHANGE MASTER TO command (fully described in our
>> manual) -
>> # the syntax is:
>> #
>> # CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
>> # MASTER_USER=, MASTER_PASSWORD= ;
>> #
>> # where you replace , , by quoted strings
>> and
>> # by the master's port number (3306 by default).
>
> Use this method. it works and is the correct way to do things. It
> also will keep
> working if you stop and restart the server with replication carrying
> on
> from where it left off.
>
> The procedure is quite simply:
>
> 1. Ensure binlogging is enabled on the master.
> 2. Ensure you setup grant permissions so the slave can connect to
> the master.
> 3. Configure on the slave the replication (which databases need to
> be replicated)
> 4. Get the master and slave in sync (via rsync, load/dump or whatever)
> 5. Run show master status on the master (assuming binlogging is
> enabled)
> to get the current position on the master
> 6. use CHANGE MASTER TO on the slave providing the appropriate
> permissions.
> 7. Run: START SLAVE
> 8. Use: show slave status\G to check how the replication is working,
> and
> and adjust as necessary.
>
> It's true that initial mysql replication setup is a bit fiddly, but
> once you've
> done it once or twice it's not so hard.


I have it set up and working in test. I will redo it again once I get
a better handle on it. I am still a little confused on one aspect.
In the mysql sample cfg file, the section that has:
#Replication Slave there is a very clear "OR" to use either #1 OR #2.

I did the suggestions of #2, issuing
# CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
# MASTER_USER=, MASTER_PASSWORD= ;
on the slave.

I also, in section [mysqld]
# Begin slave config 05/14/2009
server-id = 2
master-host = ip.add.re.ss
master-user = user-replicate
master-password = passw3rd
master-port = 3306
# End slave config

Am I correct in that this is not needed. I know I for certain need
server_id, but is that all I need, and I have redundant data? I
figure also better to not have raw user and pass in a cnf file if it
is not needed.

I would say 99% of the examples on the internets are using both
methods, the MySql docs are not entirely clear to me on this specific
issue. Conflicting data on the comments for sure.

I also have seen a good deal more options specified than I have. To
be clear, I am only looking for one way replication, the most basic, a
master that is read/write by clients, and a slave that is neither read/
write by clients, but only reading in data from the master, syncing it.

Here is a example of other options I am seeing, comments interspersed...
server-id = 1
log-bin = /usr/local/mysql/var/bin.log
I take it I do not need this if I am ok with default data dir?
log-slave-updates
I am pretty sure I do not not need this, since I am only doing
master to slave, and no cascading replication, or circular rep.
log-bin-index = /usr/local/mysql/var/log-bin.index
Same as log-bin, of I am ok with default data dir?
log-error = /usr/local/mysql/var/error.log
Again, if I am ok with default data-dir?

relay-log = /usr/local/mysql/var/relay.log
I do not seem to have this file anywhere.

relay-log-info-file = /usr/local/mysql/var/relay-log.info
relay-log-index = /usr/local/mysql/var/relay-log.index
I do not see that I have these on the master, I have it on the
slave. Maybe all these logs are best to be defined. Perhaps
if they are not, the hostname may be used in the naming
of the logs, and if a hostname ever changes, I would
have issues on getting replication to fine the logs?

auto_increment_increment = 10
auto_increment_offset = 1
I am definitely not clear on these. I want a 100% replica.
Why do I need to worry of key id collisions at all? Or is
This n/a to a simple master/slave setup

master-host =
master-user =
master-password =
These I wonder if are needed at all, if I use the sql CHANGE

replicate-do-db = somedbname1
replicate-do-db = somedbname2
Good to know, wondered how to add more, if you comma seperated
or just add more of the same name-value pairs.

Thank you for any advise on this.
--
Scott * If you contact me off list replace talklists@ with scott@ *


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Replication config

On May 12, 2009, at 11:11 PM, Simon J Mudd wrote:

> 1. Ensure binlogging is enabled on the master.
> 2. Ensure you setup grant permissions so the slave can connect to
> the master.
> 3. Configure on the slave the replication (which databases need to
> be replicated)
> 4. Get the master and slave in sync (via rsync, load/dump or whatever)
> 5. Run show master status on the master (assuming binlogging is
> enabled)
> to get the current position on the master
> 6. use CHANGE MASTER TO on the slave providing the appropriate
> permissions.
> 7. Run: START SLAVE
> 8. Use: show slave status\G to check how the replication is working,
> and
> and adjust as necessary.


Also, how do I set the slave to be read only? I set read-only in
my.cnf and it made all databases read only. I want to limit just the
replicated database to be read only. The rest of them are production
databases in normal use by clients.

I suppose just not having a login and pass to the replicated database
is more than enough?
--
Scott * If you contact me off list replace talklists@ with scott@ *


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Replication config

On Thu, May 14, 2009 at 04:45:44PM -0700, Scott Haneda wrote:

> >It's true that initial mysql replication setup is a bit fiddly, but
> >once you've done it once or twice it's not so hard.
>
> I have it set up and working in test. I will redo it again once I get
> a better handle on it. I am still a little confused on one aspect.
> In the mysql sample cfg file, the section that has:
> #Replication Slave there is a very clear "OR" to use either #1 OR #2.
>
> I did the suggestions of #2, issuing
> # CHANGE MASTER TO MASTER_HOST=, MASTER_PORT=,
> # MASTER_USER=, MASTER_PASSWORD= ;
> on the slave.

Sounds fine.

> I also, in section [mysqld]
> # Begin slave config 05/14/2009
> server-id = 2
> master-host = ip.add.re.ss
> master-user = user-replicate
> master-password = xxxxxx
> master-port = 3306
> # End slave config

No. not necessary as the information is stored in the master info file.

> Am I correct in that this is not needed. I know I for certain need
> server_id, but is that all I need, and I have redundant data? I
> figure also better to not have raw user and pass in a cnf file if it
> is not needed.

The server-id IS needed and MUST be different on each server.

....

> log-bin = /usr/local/mysql/var/bin.log

This can be in the datadir just fine. If you server is very busy with updates
some people recommend putting this on a different filesystem to spread the I/O.
Depending on your setup that may or may not help. If you don't need it now
don't bother.

> log-slave-updates

Only needed if you have a daisy-chained replication environment you need this.
Without it the salve will only store the commands run on the slave itself
thus missing the commands run on the original master. If you want to make a
slave from the SLAVE server then without this option you won't pick up
all the replication commands.

> auto_increment_increment = 10

Unless you are running master-master replication ignore this.
>
> replicate-do-db = somedbname1
> replicate-do-db = somedbname2

required if you don't want to replicate all the dbs on the server.

Simon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Replication config

On Fri, May 15, 2009 at 12:48:18AM -0700, Scott Haneda wrote:
>
> Also, how do I set the slave to be read only? I set read-only in
> my.cnf and it made all databases read only.


SET GLOBAL read_only = true;
and as you've done in the my.cnf file.

Unless the user has SUPER rights he can't change things in the database.

There are some minor exceptions:
- you can create temporary tables
- you can run ANALYZE TABLE ....

These are normally not an issue.

Simon

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Replication config

On May 16, 2009, at 12:28 AM, Simon J Mudd wrote:

>> I also, in section [mysqld]
>> # Begin slave config 05/14/2009
>> server-id = 2
>> master-host = ip.add.re.ss
>> master-user = user-replicate
>> master-password = xxxxxx
>> master-port = 3306
>> # End slave config
>
> No. not necessary as the information is stored in the master info
> file.


Thanks Simon, I will test. Looks like if this is the case, literally,
99% of every tutorial out there is wrong, as they all do this in
duplicate, along with the "CHANGE MASTER" SQL command.
--
Scott * If you contact me off list replace talklists@ with scott@ *


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org