From: Scott Haneda (talklistsnewgeo.com)
Date: Wed May 13 2009 - 12:19:21 CDT

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]

Thanks, a few comments inline below...

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

> talklistsnewgeo.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=<host>, MASTER_PORT=<port>,
>> # MASTER_USER=<user>, MASTER_PASSWORD=<password> ;
>> #
>> # where you replace <host>, <user>, <password> by quoted strings
>> and
>> # <port> 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

• Messages sorted by: [ date ] [ thread ] [ subject ] [ author ]