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. 

If you want o replicate more than one database, just duplicate the conf directive, e.g.

binlog-do-db=database_1 binlog-do-db=database_2

The same goes for the slave side as well although they don't have to match, e.g. some Master DBs replicated to one slave and different databases to another slave.

Also, you can declare specific tables within a given database as well.

It should be noted that the server-id and log parameters go in the [mysqld] section of the configuration file. (DOH!)

YES!! An article update noting this would be very appropriate!! 

Will this sync method work on different MySql versions? Im looking a way to have mySql 4.0 data to be syncted with version 5.X. Is that possible?

should grant permissions for replication user with server ip 

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<some_password>';

Also

GRANT REPLICATION SLAVE ON *.* TO 'slave_user'Server_IP'%' IDENTIFIED BY '<some_password>';

To those who have the problem of locking tables, there is my best solution:

mysqldump -u root -p<password> --master-data --single-transaction exampledb > /algun/path/exampledb.master.sql

Then, in the archive exampledb.master.sql in some place there is the information of MASTER_LOG_FILE and MASTER_LOG_POS needed for the slave configuration.

Loof for a line of the similar to:

CHANGE MASTER TO MASTER_LOG_FILE='mysql-bin.000019', MASTER_LOG_POS=1017207641;

With this procedure mysqldump will lock the tables while is making the dump. Also, it registers the MASTER_LOG_FILE and the MASTER_LOG_POS needed to the slave.
 

 Here is another smimple article describing MySQL Replication process. http://bit.ly/16pklCL

don't use -p<password> but only -p

when using -p<password> your password will remain in your history. with -p only you will propted for your password

Hi Fellow Mysql Admins,

 

 I have a problem, hope I will be able to get it right for you to understand.

 

 I have a test master Slave setup. We have bin logs stored on / Partition & due to the big size of the db the / volume is fulled up & the server became unrespoisve.

 

 Now we had added more space to / & had deleted both the databases on master & slave & removed all bin logs.

 

 Now if we create database with same name, will that have any negative impact on future replications.?

Thanks in Advance

Howdy

 

In these example i see for one database, but how can i do it with all databases?

Thanks

Witoszek

hi , nice tuts! but is this working with diffrent domain name ? im working on wordpress. so i have 2 websites lets say A and B ,

A and B has same post but diffrent themes and diffrent design (logo etc) , if i use this tuts ,  can i still keep design for each sites ?

Hi, can i replicate from live server to another 2 backupservers as master and 2 slaves. will it work???