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=arch...@jab.org
