List:General Discussion« Previous MessageNext Message »
From:Scott Haneda Date:May 14 2009 11:45pm
Subject:Re: Replication config
View as plain text  
On May 12, 2009, at 11:11 PM, Simon J Mudd wrote:

> talklists@stripped (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.
> 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=<host>, MASTER_PORT=<port>,
	#    MASTER_USER=<user>, MASTER_PASSWORD=<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 = <other master hostname>
      master-user = <replication username>
      master-password = <replication 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@ *

Thread
Replication configScott Haneda13 May
  • Re: Replication configSimon J Mudd13 May
    • Re: Replication configScott Haneda13 May
      • Re: Replication configSimon J Mudd14 May
      • Re: Replication configThomas Spahni14 May
    • Re: Replication configScott Haneda15 May
      • Re: Replication configSimon J Mudd16 May
        • Re: Replication configScott Haneda16 May
    • Re: Replication configScott Haneda15 May
      • Re: Replication configSimon J Mudd16 May