How To Set Up Database Replication In MySQL - Page 2

Do you like HowtoForge? Please consider supporting us by becoming a subscriber.
Submitted by falko (Contact Author) (Forums) on Tue, 2005-11-29 23:55. ::

2 Configure The Slave

On the slave we first have to create the database exampledb:

mysql -u root -p
Enter password:
CREATE DATABASE exampledb;
quit;

If you have made an SQL dump of exampledb on the master and have transferred it to the slave, then it is time now to import the SQL dump into our newly created exampledb on the slave:

mysql -u root -p<password> exampledb < /path/to/exampledb.sql (Replace <password> with the real password for the MySQL user root! Important: There is no space between -p and <password>!)

If you want to go the LOAD DATA FROM MASTER; way then there is nothing you must do right now.

Now we have to tell MySQL on the slave that it is the slave, that the master is 192.168.0.100, and that the master database to watch is exampledb. Therefore we add the following lines to /etc/mysql/my.cnf:

server-id=2
master-host=192.168.0.100
master-user=slave_user
master-password=secret
master-connect-retry=60
replicate-do-db=exampledb

Then we restart MySQL:

/etc/init.d/mysql restart

If you have not imported the master exampledb with the help of an SQL dump, but want to go the LOAD DATA FROM MASTER; way, then it is time for you now to get the data from the master exampledb:

mysql -u root -p
Enter password:
LOAD DATA FROM MASTER;
quit;

If you have phpMyAdmin installed on the slave you can now check if all tables/data from the master exampledb is also available on the slave exampledb.

Finally, we must do this:

mysql -u root -p
Enter password:
SLAVE STOP;

In the next command (still on the MySQL shell) you have to replace the values appropriately:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

  • MASTER_HOST is the IP address or hostname of the master (in this example it is 192.168.0.100).
  • MASTER_USER is the user we granted replication privileges on the master.
  • MASTER_PASSWORD is the password of MASTER_USER on the master.
  • MASTER_LOG_FILE is the file MySQL gave back when you ran SHOW MASTER STATUS; on the master.
  • MASTER_LOG_POS is the position MySQL gave back when you ran SHOW MASTER STATUS; on the master.

Now all that is left to do is start the slave. Still on the MySQL shell we run

START SLAVE;
quit;

That's it! Now whenever exampledb is updated on the master, all changes will be replicated to exampledb on the slave. Test it!


Links

up

Copyright © 2005 Falko Timme
All Rights Reserved.
add comment | view as pdf view as pdf | print: this | all page(s) |
Please do not use the comment function to ask for help! If you need help, please use our forum.
Comments will be published after administrator approval.
Turnoff replication
Submitted by Marijan (not registered) on Mon, 2009-07-27 10:46.

This is great tutorial. 

 Thank you very much.

 I have one question. How I can turn off replication?

 

reply | view as pdf view as pdf
Great tutorial, many
Submitted by Christophe (not registered) on Sat, 2009-07-25 00:19.

Great tutorial, many thanks!

I also face some small pbs, but nothing really bad.

The privileges to grant to the slave user are:

 GRANT SUPER,REPLICATION CLIENT,RELOAD, REPLICATION SLAVE ON *.* TO 'slave_user'@'%' IDENTIFIED BY '<pass>';

 And I regret only one thing:

What must be done when the master break down ?

The slave is supposed to run instead, so I think there should be some commands to set ip up back as a normal server.. right ?

And finally when the master is back again, how to set the replication back as before, because the former slave has been running on its own for some time ?

Some answers to theses would be very appreciated.

Christophe

reply | view as pdf view as pdf
replication
Submitted by Anonymous (not registered) on Fri, 2009-07-24 17:56.

Thanks for the great tutorial. This really streamlines things compared to the mysql manual.

 One thing to remember when trying to do replication between different versions is that the slave needs to be a higher version than the master. Otherwise it may not work. For more info see http://dev.mysql.com/doc/refman/5.0/en/replication-compatibility.html

reply | view as pdf view as pdf
Thanks.My mysql replication
Submitted by herald setiadi (not registered) on Mon, 2009-05-11 17:42.

Thanks.My mysql replication running well.

Herald BSD

reply | view as pdf view as pdf
The issue I've faced:ERROR
Submitted by Valery (not registered) on Thu, 2008-12-11 09:28.

The issue I've faced:

ERROR 1218 (08S01): Error connecting to master: Master is not configured
 

 In my case it was wrong master configuration placement in my.conf. Don't put it in the end of file. Place it somewhere in the middle ;)

 

reply | view as pdf view as pdf
remark
Submitted by Jason (not registered) on Fri, 2008-11-21 15:42.
putting the settings in the my.cnf file and doing:

CHANGE MASTER TO MASTER_HOST='192.168.0.100', MASTER_USER='slave_user', MASTER_PASSWORD='<some_password>', MASTER_LOG_FILE='mysql-bin.006', MASTER_LOG_POS=183;

in the mysql command line does the same. so only setting the my.cnf and restarting mysql does the trick.

reply | view as pdf view as pdf
Won't work on newer MYSQL versions
Submitted by xabin (registered user) on Wed, 2008-03-12 19:25.

The syntax 'LOAD DATA FROM MASTER' will not work on the newer versions of MySQL-server, see this page; http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html

reply | view as pdf view as pdf
another thing that will help here
Submitted by nephish (registered user) on Wed, 2007-05-02 00:00.

    if you start mysql with /etc/init.d/mysql restart, you cannot just put the lines above for the my.cnf file anywhere in the file for the slave.

they must be in the [mysqld] block.

took me two hours to figgure that one out.

 

 

 

reply | view as pdf view as pdf
keep in mind this note on r
Submitted by Anonymous (not registered) on Tue, 2006-06-06 11:00.

keep in mind this note on replication (found on mysql doc site), I lost 2 days trying to understand why my DBs where not replicating!

Note that if you client does not do a "USE
dbname", binlog-do-db=dbname will not binlog a
query like: "update in dbname.foobar set foo=1"

You explicitly have to do a USE before a query in
order to have your query binlogged, it looks
like. Replication on the slave side can do
wildcard matches .. but the master cannot (a la
binlog-wild-do-table=dbname.%). So make sure your
clients do a use, if you plan to replicate those
tables it updates.

reply | view as pdf view as pdf
Missing a few options, more detailed commands for slave
Submitted by Anonymous (not registered) on Mon, 2006-01-16 19:02.

http://dev.mysql.com/doc/refman/4.1/en/replication-howto.html

You need to read the database replication documents a little more in depth; depending on your version of MySQL and wether or not you use InnoDB, you want to also include something like the following on the master server:

innodb_flush_log_at_trx_commit = 1
innodb_safe_binlog
sync-binlog = 1
log-bin = /path/to/mysql/data/master-bin
log-bin-index = /path/to/mysql/data/master-bin.index

On the slave, you want to enable (usually) read-only behaviour to revent accidental commits as well as the relay logs:

read-only
relay-log = /path/to/mysql/data/slave-relay-bin
relay-log-index = /path/to/mysql/data/slave-relay-bin.index

A better method to ensure exact replication would go like the below:

1) start master

2) grant replication to slave

3) run "show master status" and record the index file and it's offset position

4) start the slave

5) run "stop slave"

6) run "change master to master_host='[MASTER HOST IP]', master_user='[USER]', master_password='[PASSWORD]', master_log_file='[NAME]', master_log_pos=[POSITION]" (from #3)

7) run "start slave"

Now go ahead and set your root password, create your databases, etc. Everything done on the master will replicate faithfully over to the slave.

reply | view as pdf view as pdf
Little tip
Submitted by Anonymous (not registered) on Mon, 2006-01-16 17:26.
I've done the exact same thing a while ago. I wrote a little perlscript to keep the databases in sync (when run it makes sure they're synced). Some people might find it helpful. http://files.printf.dk/software/clustersync.txt
reply | view as pdf view as pdf
Sponsored Links: Unified Communications: Thoughts, Strategies and Predictions
Join the discussion.
www.seamlessenterprise.com

IP Convergence
Integrate your wireless and wireline networks.
Learn how from the experts at Sprint.
www.seamlessenterprise.com

Wireless & Wireline Integration
Thoughts, strategies and solutions: join the discussion
www.seamlessenterprise.com

Unified Communications 2009
Join the Discussion. Now.
www.seamlessenterprise.com