Comments on How To Set Up Database Replication In MySQL

Nice and concice howto (too bad I only found it now while I figured this stuff out a month ago)! I'd like to remark that when editing the slave my.cnf config file, the master details do not necessarilly have to be filled in there, since the slave mysql server doesn't read this config when restarting. When issuing the CHANGE MASTER TO command, the slave mysql server creates a master.info file in the mysql data directory where it stores the master details, along with it's current synchronization position. The master.info file contains thus everything that the slave needs when restarting. Reason for not having the master details (including slave_user password) in the my.cnf config file is by default world readable (at least in debian GNU/Linux it is) and the master.info can only be read by mysql (and root, obviously). Furthermore I'd like to point out another (fast) way of getting the master data to the slave when setting up replication: with the read lock still on (don't close the mysql client in which you issued the FLUSH TABLES WITH READ LOCK command, otherwise the lock will be gone); create a tarball of the entire mysql data directory (or only the desired databases, the filenames to include are obvious), release the lock, copy the tarball to the slave machine using ftp, scp or whatever, change to the data directory overthere and extract the tarball. Take special care when copying the mysql database to the slave this way, because you will override any existing account on the slave. In debian, dpkg-reconfigure mysql-server might be needed to resolve problems with the debian-sys-maint-user that arise when replacing the entire mysql database. Good luck, Thomas

When copying everything, including the mysql database on Debian, you will screw up the password for the debian-sys-main account. Just grab the password on your master server from /etc/mysql/debian.cnf and put this password in the same file on your new slave.

How can i use it on my server with the same replication structure.

I think this is going on the board to get things done.

 Very nice article, how to make MySQL Multi-Master Replication?

Thank you

Nice article.  We have Mysql running on a windows server.  Is the setup similar?  I want to set up replication or syncronization.  Not for sure which one is harder since I am not too familar with Mysql.  Thanks

When locking the tables with FLUSH TABLES WITH READ LOCK; do not quit the mysql shell else you will lose the lock, use another shell to do the db dump instead.

 FreeBSD version 4.0.26

MySQL version 4.10

Found this article very helpful. Followed it step by step and found no problems at all. Used to think mysql replication to be a big deal but this article made it look so simple. Thanks Guys

Cheers

Lionel

 Developer at Shopnics

This is a great tutorial - I used it to set everything up myself. However, I found a few details lacking about what was going on behind the scenes, and also how to recover after a server crash. Anyway, I've written everything up here: http://aciddrop.com/2008/01/10/step-by-step-how-to-setup-mysql-database-replication/ It's for MySQL 5.0 on Centos 4, but is good for other distros. (I've credited you with a link at the bottom).

 Cheers,

 Leon

 Hi,

 Very good artice find much useful and easy understandable. I have bookmarked it. Step by step configuration made it easy to configure.

Very good article. I am new to mysql, still could configure replication by following steps.
Thanks a lot.

I found the same thing as the poster above. Exiting the shell removes the READ LOCK.

However, keeping the shell open and starting the dump from another shell prevents the dump from working. It just hangs until I run UNLOCK TABLES in the first shell.

 There must be a better way to do this. Run the dump from inside the shell that's doing the locking perhaps?

Hi Steve, 

 I'm encountering the same problem as you. What did you do in the end to solve this ?

 Thanks - Andrew

Hello,

I am using this replication on my server. Thank a lot.

I'm curious how well replication is actually working and the types of environments that people are using it in / with.  We tried a few projects and had difficulty in getting it working property and consistently.    

Hello, I am very new to MySQL and I have set up two server and configured them for replication.  The servers are replicating but I had a question.  I was under the impression that if the master has some records added or updated while the slave is stopped, the slave would copy these records.  There are records on an exiting table on both servers

 Not sure if I am missing something to make that happen.  I thought that's what 'seconds behind master' meant

yes, the replication is working properly, the slave can't copy any updated data from the master, in-fact it should not know what's going on the master's side, it should just update the master table when its table is updated.