MySQL :: MySQL 5.0 Reference Manual :: 16.1.1 How to Set Up

v九天〓云龙

Recommended Servers for MySQL
The world's most popular open source database
Contact a MySQL Representative
Login | Register


MySQL.com
Downloads
Developer Zone
Partners & Solutions
Customer Login
DevZone
Documentation
Librarian
Articles
Forums
Bugs
Forge
Planet MySQL
Labs
   

Documentation Library
Table of Contents
MySQL 5.4 Reference Manual
MySQL 5.1 Reference Manual
MySQL 5.0 Reference Manual
MySQL 3.23/4.0/4.1 Manual
Search manual:
   


Additional languages
French
Spanish






MySQL 5.0 Reference Manual :: 16 Replication :: 16.1 Replication Configuration :: 16.1.1 How to Set Up Replication
? 16.1 Replication Configuration


16.1.1.1 Creating a User for Replication ?
Section Navigation      [Toggle]
16.1 Replication Configuration
16.1.1 How to Set Up Replication
16.1.1.1 Creating a User for Replication
16.1.1.2 Setting the Replication Master Configuration
16.1.1.3 Setting the Replication Slave Configuration
16.1.1.4 Obtaining the Master Replication Information
16.1.1.5 Creating a Data Snapshot Using mysqldump
16.1.1.6 Creating a Data Snapshot Using Raw Data Files
16.1.1.7 Setting Up Replication with New Master and Slaves
16.1.1.8 Setting Up Replication with Existing Data
16.1.1.9 Introducing Additional Slaves to an Existing Replication Environment
16.1.1.10 Setting the Master Configuration on the Slave
16.1.2 Replication and Binary Logging Options and Variables
16.1.3 Common Replication Administration Tasks
16.1.1.聽How to Set Up Replication
[+/-]
16.1.1.1. Creating a User for Replication
16.1.1.2. Setting the Replication Master Configuration
16.1.1.3. Setting the Replication Slave Configuration
16.1.1.4. Obtaining the Master Replication Information
16.1.1.5. Creating a Data Snapshot Using mysqldump
16.1.1.6. Creating a Data Snapshot Using Raw Data Files
16.1.1.7. Setting Up Replication with New Master and Slaves
16.1.1.8. Setting Up Replication with Existing Data
16.1.1.9. Introducing Additional Slaves to an Existing Replication Environment
16.1.1.10. Setting the Master Configuration on the Slave
This section describes how to set up complete replication of a MySQL server. There are a number of different methods for setting up replication, and the exact method that you use will depend on how you are setting up replication, and whether you already have data within your master database.
There are some generic tasks which may be required for all replication setups:
You may want to create a separate user that will be used by your slaves to authenticate with the master to read the binary log for replication. The step is optional. See Section聽16.1.1.1, 鈥淐reating a User for Replication鈥?/a>.
You must configure the master to support the binary log and configure a unique ID. See Section聽16.1.1.2, 鈥淪etting the Replication Master Configuration鈥?/a>.
You must configure a unique ID for each slave that you want to connect to the master. See Section聽16.1.1.3, 鈥淪etting the Replication Slave Configuration鈥?/a>.
Before starting a data snapshot or the replication process, you should record the position of the binary log on the master. You will need this information when configuring the slave so that the slave knows where within the binary log to start executing events. See Section聽16.1.1.4, 鈥淥btaining the Master Replication Information鈥?/a>.
If you already have data on your master and you want to synchronize your slave with this base data, then you will need to create a data snapshot of your database. You can create a snapshot using mysqldump (see Section聽16.1.1.5, 鈥淐reating a Data Snapshot Using mysqldump鈥?/a>) or by copying the data files directly (see Section聽16.1.1.6, 鈥淐reating a Data Snapshot Using Raw Data Files鈥?/a>).
You will need to configure the slave with the master settings, such as the host name, login credentials and binary log name and positions. See Section聽16.1.1.10, 鈥淪etting the Master Configuration on the Slave鈥?/a>.
Once you have configured the basic options, you will need to follow the instructions for your replication setup. A number of alternatives are provided:
If you are establishing a new MySQL master and one or more slaves, then you need only set up the configuration, as you have no data to exchange. For guidance on setting up replication in this situation, see Section聽16.1.1.7, 鈥淪etting Up Replication with New Master and Slaves鈥?/a>.
If you are already running a MySQL server, and therefore already have data that will need to be transferred to your slaves before replication starts, have not previously configured the binary log and are able to shut down your MySQL server for a short period during the process, see Section聽16.1.1.8, 鈥淪etting Up Replication with Existing Data鈥?/a>.
If you are setting up additional slaves to an existing replication environment then you can set up the slaves without affecting the master. See Section聽16.1.1.9, 鈥淚ntroducing Additional Slaves to an Existing Replication Environment鈥?/a>.
If you want to administer a MySQL replication setup, we suggest that you read this entire chapter through and try all statements mentioned in Section聽12.6.1, 鈥淪QL Statements for Controlling Master Servers鈥?/a>, and Section聽12.6.2, 鈥淪QL Statements for Controlling Slave Servers鈥?/a>. You should also familiarize yourself with the replication startup options described in Section聽16.1.2, 鈥淩eplication and Binary Logging Options and Variables鈥?/a>.
Note
Note that certain steps within the setup process require the SUPER privilege. If you do not have this privilege then enabling replication may not be possible.
Previous / Next / Up / Table of Contents


User Comments
Posted by B.L. Choy on December 18 2002 3:27pm[Delete] [Edit]

For those who want their server to be both master
and slave at the same time, please note that you
should have only one 'server-id' in your my.cnf file; if
you copy the two sections of statements (Paras. 5
and 7) above into
your my.cnf , you will end up having a connection
problem like:
020821 17:34:47 Slave: connected to
master 'repl@192.168.60.1:3306', replication
started in log 'FIRST' at position 32
020821 17:34:47 Slave: received 0 length
packet from server, apparent master
shutdown:
020821 17:34:47 Slave: Failed reading log
event, reconnecting to retry, log 'FIRST' position
32
020821 17:34:47 Slave: reconnected to
master 'repl@192.168.60.1:3306',replication
resumed in log 'FIRST' at position 32
020821 17:34:47 Slave: received 0 length
packet from server, apparent master
shutdown:


Posted by [name withheld] on September 22 2002 1:38am[Delete] [Edit]

You do not need the line
master-port=<TCP/IP port for master>

of the slave, if you not changed the port. Normal :
3306

To add a user for the master server you need this
line
mysql> GRANT FILE ON *.* TO repl@"%"
IDENTIFIED BY '<password>';

You have to change "%" to the IP Adress from the
SLAVE.
Example: mysql> GRANT FILE ON *.* TO
repl@192.168.0.2 IDENTIFIED BY 'yourpass';

Posted by Jacob C on December 18 2002 3:27pm[Delete] [Edit]

A few things I came across while setting up
replication:

- Passwords can only be 16 characters long. This will
cause 'Access Denied' errors while trying to connect
to the master if set too long.

- When running replication numerous files are
created that can cause problems getting back on
track if something goes wrong. If there are
problems after you edit your my.cnf and restart
mysqld here's some cleaning up that needs to be
done while the server is shutdown (your file names
might differ):

1) On the slave (in the mysql data dir): remove
master.info file, remove all binary files created and
their indexes, remove the .err and .pid files, remove
the log.info file.

2) On the master (in the mysql data dir): remove all
binary files created and their indexes, remove
the .err and .pid files.

3) If for some reason you need to redo replication I
have found it is best to tar up the mnaster and put a
fresh copy of the database on the slave and start
again rather than trying to resolve every issue the
slave spits out. Although, it should be noted that this
is not always possible - it's a judgement call.




Posted by [name withheld] on October 4 2002 8:54am[Delete] [Edit]

I beat my head against the wall trying to figure
this one out:

These instructions assume that you do not
previously have binary loggin on the master
server. If you do have binary logging on (which
you should have if you follow the install
instructions), and you follow these instructions,
you will have problems.

For instance, if logging is on the master and you
create a database and then follow the replication
instructions, replication will not work. This is
because the replication process will try to
replicate the create database command on the slave
and fail because the database is already exists
(because you brought it over with the tar file
from the master).

To work around this you can either drop the
database on the slave or do a 'reset master;' on
the master (this will delete inactive binary logs
on the master so be careful).

Posted by Michael Babcock on October 31 2002 11:05am[Delete] [Edit]

Its worth mentionning that if your server-id
values are too large, the communication dies every
time it starts and you'll have to change the IDs,
then reset things to get started again.

Posted by Nicolas Ross on February 17 2003 9:13am[Delete] [Edit]

Our Main mysql server (3.23.54) has many,many databases. Curently I run a replication setup for redundency purpose, and it's working well.

I want to setup a server with only one of the db's replicated. I can't grant a user file priv only one one db. Is there a way to setup so that the salve won't be able to read all of the dbs' ?

Posted by Justin Finkelstein on February 24 2003 11:46pm[Delete] [Edit]

When setting up mysql replication, you may find it necessary to test your username and password settings; in my scenario, I do replication through an SSH tunnel to our live server. If you're doing this, there're a couple of things to note:

1. the GRANT FILE command should be set so that the replication user is allowed access from the server

2. when testing the username/password using the mysql login prompt, this will fail if you've followed the instructions above AND have specified a database to connect to. If you leave the dB name out, the login will work.


Posted by Michael Grabenstein on April 22 2003 9:54am[Delete] [Edit]

Items 3 and 4 seem to be reversed, or at least part of 4 has to be done before you can accomplish all of #3 on a system with no previous replication set up. Specifically "show master status" won't show anything until you update the my.cnf as directed in step #4.

Also surprised the replication FAQ and here do not have anything about: "Error updating slave list: query error" in the error log. I am running 4.0.7-gamma and I had to still "grant file" before this error would go away (from step #2). Found at least a direction to go in from this site:
http://www.faqts.com/knowledge_base/index.phtml/fid/398
There they have a question: "What does the Error updating slave list: query error mean ?" ...

Also step 8 should probably mention something about master_port for people like me that have changed the Port number MySQL daemon is running on. Thanks to the earlier note on that...

Also in step #4 you might want to add a line for binlog-ignore-db=< > or at least mention it...





Posted by Patrick Greenwell on April 26 2003 6:47am[Delete] [Edit]

Executing a FLUSH TABLES WITH READ LOCK doesn't block write queries globally across all of your databases. You have to connect to each database individually and execute the command for it to be effective. (at least on 3.23.56)

Posted by Patrick Greenwell on April 26 2003 7:10am[Delete] [Edit]

The syntax for starting a slave thread is backwards in the documentation. It's "SLAVE START" not "START SLAVE" (again at least under 3.23.56)

Posted by Kayra Otaner on May 15 2003 4:26pm[Delete] [Edit]

Tips for minimizing downtime on Hot MySQL Database servers :
- Start with increasing max_connections value in MySQL before restarting server for binary logging. This is necessary since when you lock tables all new connections will be in que for processing.
- Always copy update log to a safe place too. If you forget getting position of update log for some reason, you can always use mysqlbinlog utility to find out position on binary log.
- While getting copies of database files from MySQL
use unix 'cp' instead of tar. If possible try to use different destination hard drive instead of copying into same hard drive (eg don't copy database files on hda1 to hda1 try to copy hdb1)


Posted by Iuri Fiedoruk on July 17 2003 5:08am[Delete] [Edit]

Here goes a tip that could saved me a LOT of problems if was here instead of in the CHANGE MASTER command page (where wasn't exactaly what I'm posting here besides):
to make a computer stoping to sync from another computer run STOP SLAVE, shutdown the server, delete `master.info'.
I had aproblem when the master previously was a slave, then when I set up the replication both where replicating from the other... just destroyied a database... :(

Posted by snix on July 27 2003 2:21pm[Delete] [Edit]

Here is what I had to do to set up replication with LOAD DATA FROM MASTER:

1. Edit the my.cnf file on the slave and on the master server:
master my.cnf:
[mysqld]
server-id = 1
log-bin

slave my.cnf:
[mysqld]
server-id = 2
master-host = master-host.net
master-user = repl
master-password = repl_pass
replicate-do-db = database1
replicate-do-db = database2
log-warnings

2. Restart both the slave and the master.

3. On the master do:
GRANT SUPER,REPLICATION CLIENT,REPLICATION SLAVE,RELOAD ON *.* TO repl@"%" IDENTIFIED BY 'repl_pass';

4. On the slave do:
LOAD DATA FROM MASTER;

The slave should now download the databases specified with replicate-do-db in the slaves my.cnf, and be replicating new data.

Posted by Juha Palomaki on September 18 2003 7:18am[Delete] [Edit]

I couldn't get the replication working on Solaris (SunOS 5.8) with MySQL MAX 4.0.15 precompiled binary with IP addresses. When I tried to start the replication it was always complaining about not being able to login as repl@localhost. Everything started working after I put the IP address to /etc/hosts and put the name of the machine instead of IP address to /etc/my.cnf.


Posted by Aleksey Maksimov on December 30 2003 3:36pm[Delete] [Edit]

There is a limitation on the length of password stored in master.info file. The password can not be longer than 16 characters, otherwise it will be truncated by MySQL (possibly causing "Access Denied" error messages when slave connects to master).

Posted by [name withheld] on January 15 2004 2:01am[Delete] [Edit]

Im not sure but about this,someone correct me if im wrong:

If you have Master database on different platform than the slaves (For example, Sun/ Intel) you should not copy the binary format databases from master straight to the slaves as there might be endian problems. You should use use mysqldump instead to do the copy from slave to master.



Posted by Issac Goldstand on January 29 2004 1:00am[Delete] [Edit]

Here's how to replicate via LOAD DATA FROM MASTER; with InnoDB tables: (this is problematic for "hot" databases)

On master:
Export any databases with InnoDB tables via mysqldump. Keep the export on the master server.
Drop all of the above tables.
Prepare the master for replication as described above (lock tables, check log status, unlock tables).

On slave:
Prepare slave for replication (CHANGE MASTER...)
LOAD DATA FROM MASTER;
(If you get ERROR 1189: Net error reading from master that may mean that you have InnoDB tables left on the master - if so go back to master, export and drop those databases (or at least the tables). I've also seen this error when the file system on the slave is full)
If that goes well, START SLAVE;

Back to master:
Now import the databases with InnoDB changes. The databases will propogate to the slave server, InnoDB and all :-)

[An alternate plan, I suppose, would be to simply export ALL tables on master, set up replication, and re-import all tables - messy but it seems to support more things that way]

Posted by Steve Brendtro on February 4 2004 4:49pm[Delete] [Edit]

This is a correction to snix's comments:

On the master do:
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO repl@'%' IDENTIFIED BY 'repl_pass';

On the slave do:
LOAD DATA FROM MASTER;



Posted by [name withheld] on March 23 2004 1:07am[Delete] [Edit]

tar -cvf does not realy work, since it does not copy empty files. therefore on linux i used zip instead of tar

Posted by Sean Mullaney on May 20 2004 4:13pm[Delete] [Edit]

I am using MySQL 4.0.18 and kept getting an exception in the logs saying the user did not have REPLICATION_SLAVE permission even after I submitted the GRANT REPLICATION_SLAVE command.

Then i realised there was no RELICATION_SLAVE field in the user table. I had to run the mysql_fix_privilege_tables.sql script to upgrade the privilage tables like this

SOURCE C:\mysql\scripts\mysql_fix_privilege_tables.sql

then it worked fine.

Posted by Jack Xiao on June 4 2004 2:19pm[Delete] [Edit]

If you are trying to replicate just one database, the replication won't work unless you add:

replicate-do-db=[name of database to replicate]

to the my.cnf file of the slave database (under [mysqld]).

ex:
replicate-do-db=backup2


Posted by matt macdonald on October 1 2004 12:17am[Delete] [Edit]

I did a replication using the mysqldump procedure.

The directions state to use the information from the Show Master Status command. However when I used the POS number from the command the slave sql process would fail with a 'duplicates' warning.

I opened the dump file and looked at the very bottom. I used the POS number listed in the dump file and that worked.

Posted by Eric Brunson on October 7 2004 1:44pm[Delete] [Edit]

My first experiences with using mysqldump to prepare a slave for replication didn't work out so well, but since I've moved to 4.1 I've had great success with importing from a dump of the master. This also gets around the need to run all the migration scripts when using the tarball method to go from an older master to a newer client.

Don't forget to lock tables before taking your dump (hehe), then get the master status before you unlock.


Posted by Ajay Sharma on June 11 2005 6:17am[Delete] [Edit]

If you're running low on space where you can't have two copies of your data on the same system, then you can use rsync to create a copy of your data. I setup an rsync server on the master and then synced up the slave while it was still running. Obviously this isn't a clean snapshot, so when I performed the "READ LOCK", I did another sync and it went very quick so the master was only locked for a few minutes. Then I setup the slave like the instructions say and now I have a working slave setup with very minimal downtime for the master.

--Ajay

Posted by Larry Hill on June 15 2005 4:03am[Delete] [Edit]

Be sure both master and slave have the same collation_server variable value.

Posted by Kai Palomaki on December 16 2005 2:25pm[Delete] [Edit]

If you see that your slave is stuck in "Registering slave on master" phase (with SHOW SLAVE STATUS command) after SLAVE START check that your slave's configuration includes report-host option. This applies at least to MySQL 4.1

Posted by Syed Ehtasham Qadri on March 6 2006 10:04am[Delete] [Edit]

Tested on MySQL versions 4.0.20

1. Edit the my.cnf file on the slave and on the master server:
-master my.cnf:
[mysqld]
server-id = 1
log-bin

-slave my.cnf:
[mysqld]
server-id = 2
#replicate-do-db = database1 # for Replicating specific databases

2. Restart both the slave and the master.

3. SQL SYNTAX:
--On the master do

mysql> GRANT SUPER,REPLICATION CLIENT,REPLICATION SLAVE,RELOAD ON *.* TO repl@"slave.host" IDENTIFIED BY 'password';

--On the SLAVE do

mysql> CHANGE MASTER to MASTER_HOST='master.host鈥?MASTER_PORT=3306,
MASTER_USER='repl',MASTER_PASSWORD='password',MASTER_LOG_FILE='',MASTER_LOG_POS=4;

mysql> STOP SLAVE; #--If already started

mysql> START SLAVE;


4. On the slave do:

mysql> LOAD DATA FROM MASTER;

The slave should now download the databases specified with replicate-do-db in the slaves my.cnf, and be replicating new data.

NOTE: Please sure that the MASTER_USER had required privilege .



Posted by James Cohen on April 3 2006 7:51am[Delete] [Edit]

One idea I've not tested when setting up a new slave (when you already have at least one slave) is:

- Stop the existing slave (slave a)
- Copy the /var/lib/mysql data from slave a to the new slave (slave b)
- Query slave a for its replication log position
- Start slave a again
- Start slave b from the given replication log position

This method had the advantage that you don't have to put any write locks in place on the master.

Posted by Roumen Semov on April 20 2006 7:24pm[Delete] [Edit]

In response to [name withheld] on March 23 2004 2:07am who said:
========================================
tar -cvf does not realy work, since it does not copy empty files. therefore on linux i used zip instead of tar
========================================

This is not correct. tar DOES COPY empty files.
Roumen Semov

Posted by Fred Mitchell on July 12 2006 3:53am[Delete] [Edit]

If you are setting up replication through an SSH channel, be sure to specify "127.0.0.1" as the address of the database, not "localhost", as MySQL will use "localhost" as a trigger to use a pipe for the connection instead of TCP/IP, and thus will fail.


Posted by Jeremy Postlethwaite on September 7 2006 6:53pm[Delete] [Edit]

LOAD DATA FROM MASTER is deprecated.

See:

http://dev.mysql.com/doc/refman/5.0/en/load-data-from-master.html

Posted by Michel Vansaingele on December 18 2006 9:52am[Delete] [Edit]

For Debian users : after copying all mysql data files from master to slave you can get the following error message :
---
/usr/bin/mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'debian-sys-maint'@'localhost' (using password: YES)'
---
* Which means that Mysql password for user 'debian-sys-maint' isn't the same as in file /etc/mysql/debian.cnf.
* Because this password is randomly generated, they are different for each installation, and we have to 'synchronize' this password between debian.cnf file and Mysql privileges.
1 - On the master, get a copy of the password string in /etc/mysql/debian.cnf
2 - On the slave, stop the Mysql server, edit /etc/mysql/debian.cnf and replace the password by the one of the master. Start the slave server.

If you want to change it
1 - Launch a Mysql client and select 'mysql' database
2 - run this : UPDATE `user` SET `Password` = password('[password]') where user='debian-sys-maint'
(the password seems to be encrypted but is not)
3 - stop the Mysql server (you get an error for 'debian-sys-maint')
4 - change and put same [password] in /etc/mysql/debian.cnf file

Posted by Coen Spoor on February 19 2007 1:56pm[Delete] [Edit]

For people getting:
"ERROR 1218 (08S01): Error connecting to master: Lost connection to MySQL server during query"
Make sure that ,on you're master replication server, you change the following line in your my.cnf:

bind-address 127.0.0.1

to

#bind-address 127.0.0.1

Or change the address to your FQDN or ip-addres

Posted by Chris Hart on April 23 2007 7:29am[Delete] [Edit]

I can confirm that James Cohen's idea for creating a new slave from an old slave works :-)

Our slaves are on a Linux virtual server so we can take a slave offline, back it up and create a new slave without the database going down at all.

Here is a shell script to backup a slave and save log position to file slave_pos.txt

# as root
# Stop the slave and grab the binary log info
echo "SLAVE STOP; FLUSH TABLES WITH READ LOCK; show slave status\G;" | mysql -u root -ppassword MYDATABASE > slave_pos.txt
# Backup the data base
tar cvfz MYDATABASE.tgz /var/lib/mysql/MYDATABASE
# Restart slave
echo "UNLOCK TABLES; START SLAVE;" | mysql -u root -ppassword MYDATABASE

# restore on new slave with cd / and tar xvzf MYDATABASE.tgz


Posted by Jarrod Lowe on April 25 2007 10:05am[Delete] [Edit]

It is possible to sync a database to a master while the master is in active use. Commands starting "M:" are run on the master, "S:" are run in the slave. Until the end, the slave state is undefined.

This works for InnoDB tables, if you have set transactionality to REPEATABLE-READ.


M: begin;
M: flush tables with read lock;
M: show master status;
M: show databases;
M: (for each db:)
M:__ show tables in $db;
M:__ (for each table:)
M:____ select 1 from $db.$table limit 1;
M: unlock tables;
M: set time_zone = '+00:00';
S: set foreign_key_checks = 0;
S: stop slave io_thread;
S: stop slave;
S: reset slave;
S: reset master;
S: set time_zone = '+00:00';
_: (for each database:)
S:__ show databases like '$db';
_:__ (if it does not exist:)
S:____ create database $db;
_:__ (for each table:)
M:____ show create table $db.$table;
S:____ show create table $db.$table;
_:____ (if different:)
S:______ drop table $db.$table;
_:____ (if different or not on slave:)
S:______ create table $db.$table .... ;
S:____ delete from $db.$table;
M:____ select * from $db.$table;
S:____ insert into $db.$table values ([...from above...]);
S:__ show tables in $db;
_:__ (for each table that shouldn't be there:)
S:____ drop table $db.$table;
S: show databases;
_: (for each db that shouldn't be there:)
S:__ drop database $db;
S: change master to [...details from show master above...];
S: start slave io_thread;
S: start slave;
M: rollback;


(Urghh... I see no way to embed code into these posts. Hence the ugly "____" thing to indicate indentation. I suggest copying into something with a non-proportinal font, to make it easier to read)

Posted by Jay Long on May 28 2007 3:37pm[Delete] [Edit]

When setting up replication from a Windows installation and replicated to a Unix installation of 5.0.x; Remember that the database tables are Case Sensitive under the Unix variant (See 9.2.2. Identifier Case Sensitivity). So if your application does not take this into effect, sql commands work fine with the Windows copy, but break when ran against the backup/replicated server, or worse, the replication breaks due to the fact the Unix server can't find the table.

Be Safe, target your SQL for the Unix standard of "LowerCase" and you will be a happy developer/administrator!

Posted by Sean Yap on August 1 2007 1:47am[Delete] [Edit]

These are the configuration i had try out:


MySQL Failover Circular Replication
===================================
Assume we have 2 servers: Server1 and Server2.


Server1 Settings
================
1. Put the option file my.cnf to Server1 path /etc with these settings:
[mysqld]
log-bin=mysql-bin
server-id = 1
auto_increment_increment = 10
auto_increment_offset = 1


2. Change mode/permission for my.cnf to _rw_r__r__ else mysql will igonore it.
sudo chmod 644 my.cnf

3. Stop and start mysql.
cd /Library/StartupItems/MySQLCOM
sudo ./MySQLCOM stop
sudo ./MySQLCOM start


4. Configure the server:

# create a user for replication process:
cd /usr/local/mysql/bin
./mysql -p -u root

create user replicant@'%' identified by 'password';


# Grant access rights:
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
Flush Privileges;


# Specify the info for the serve2:
CHANGE MASTER TO
MASTER_HOST='ip_of_server2',
MASTER_USER='replication_user_name_on_server2',
MASTER_PASSWORD='replication_password_on_server2';

# Start the listerner:
Start slave;


# Verify whether the replication is working:
show slave status\G


Server2 Settings
================
1. Put the option file my.cnf on to Server2 path /etc
with these settings:
[mysqld]
log-bin=mysql-bin
server-id = 2
auto_increment_increment = 10
auto_increment_offset = 2

2. Change mode/permission for my.cnf to _rw_r__r__ else mysql will igonore it.
sudo chmod 644 my.cnf

3. Stop and start mysql.
cd /Library/StartupItems/MySQLCOM
sudo ./MySQLCOM stop
sudo ./MySQLCOM start


4. Configure the server:

# create a user for replication process:
cd /usr/local/mysql/bin
./mysql -p -u root

create user replicant@'%' identified by 'password';


# Grant access rights:
GRANT SELECT, PROCESS, FILE, SUPER, REPLICATION CLIENT, REPLICATION SLAVE, RELOAD ON *.* TO replicant@'%';
Flush Privileges;


# Specify the info for the serve1:
CHANGE MASTER TO
MASTER_HOST='ip_of_server1',
MASTER_USER='replication_user_name_on_server1',
MASTER_PASSWORD='replication_password_on_server1';

# Example:
#
# CHANGE MASTER TO MASTER_HOST='125.564.12.1',
# MASTER_USER='replicant', MASTER_PASSWORD='password';



# Load data from Server1:
Load Data from Master;



# Start the listerner:
Start slave;








Posted by Nils Hammar on August 24 2007 9:13am[Delete] [Edit]

It is worth to mention that "SELECT ... FOR UPDATE" doesn't distribute the locks to all slaves, which creates room for "funny" results.

What will happen in a two-node solution with circular replication as described at http://www.onlamp.com/pub/a/onlamp/2006/04/20/advanced-mysql-replication.html is that the update is copied to the other node while the local update is overwritten.

This is a flaw that has to be considered, and the right way to resolve this is probably through the MySQL Cluster.


Posted by Nick Glencross on December 5 2007 11:55pm[Delete] [Edit]

If you are using SSL and find that you can connect using the command line, yet get 'Access Denied' in the replication logs, check the permissions on your certificates! Mine turned out to be readable by root, but not by the mysql user that the replication runs as.


Posted by Andy Grove on September 14 2009 2:59pm[Delete] [Edit]

Here is a video tutorial for setting up replication that shows what happens in the event of the master server failing http://www.codefutures.com/mysql-replication-howto

Add your own comment.
Top / Previous / Next / Up / Table of Contents
? 1995-2008 MySQL AB, 2008-2009 Sun Microsystems, Inc.
Online Shop
Site Map
About MySQL
Legal
Privacy Policy
Contact Us
Job Opportunities