Neohapsis is currently accepting applications for employment. For more information, please visit our website www.neohapsis.com or email firstname.lastname@example.org
From: Simon J Mudd (sjmuddpobox.com)
Date: Wed May 13 2009 - 17:22:10 CDT
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
188.8.131.52. 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.
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
> > 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
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
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.
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql