MySQL Master Master Repliction Tutorial

This tutorial describes how to set up MySQL master-master replication. We need to replicate MySQL servers to achieve high-availability (HA). In my case I need two masters that are synchronized with each other so that if one of them drops down, other could take over and no data is lost. Similarly when the first one goes up again, it will still be used as slave for the live one.

Here  is a basic step by step tutorial, that will cover the mysql master and slave replication and also will describe the mysql master and master replication.

Notions: we will call system 1 as master1 and slave2 and system2 as master2 and slave 1.

Step 1:

Install mysql on master 1 and slave 1. configure network services on both system, like

Master 1/Slave 2 ip: 192.168.16.4

Master 2/Slave 1 ip : 192.168.16.5 

 

Step 2:

On Master 1, make changes in my.cnf:

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

log-bin
binlog-do-db=<database name>  # input the database which should be replicated
binlog-ignore-db=mysql            # input the database that should be ignored for replication
binlog-ignore-db=test

server-id=1

[mysql.server]
user=mysql
basedir=/var/lib


[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


Step 3:

On master 1, create a replication slave account in mysql.

mysql> grant replication slave on *.* to 'replication'@192.168.16.5 \
identified by 'slave';

and restart the mysql master1. 

 

Step 4:

Now edit my.cnf on Slave1 or Master2 :

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
old_passwords=1

server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid


 Step 5:

Restart mysql slave 1 and at

mysql> start slave;
mysql> show slave status\G;


*************************** 1. row ***************************

             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.16.4
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: MASTERMYSQL01-bin.000009
        Read_Master_Log_Pos: 4
             Relay_Log_File: MASTERMYSQL02-relay-bin.000015
              Relay_Log_Pos: 3630
      Relay_Master_Log_File: MASTERMYSQL01-bin.000009
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 4
            Relay_Log_Space: 3630
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 1519187

1 row in set (0.00 sec)

Above highlighted rows must be indicate related log files and  Slave_IO_Running and   Slave_SQL_Running: must be to YES.

 

Step 6:

On master 1:

mysql> show master status;
+------------------------+----------+--------------+------------------+
| File                   | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------------+----------+--------------+------------------+
|MysqlMYSQL01-bin.000008 |      410 | adam         |                  |
+------------------------+----------+--------------+------------------+
1 row in set (0.00 sec)

The above scenario is for master-slave, now we will create a slave master scenario for the same systems and it will work as master master.

 

Step 7:

On Master2/Slave 1, edit my.cnf and master entries into it:

 [mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock
# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1
server-id=2

master-host = 192.168.16.4
master-user = replication
master-password = slave
master-port = 3306

log-bin                     #information for becoming master added
binlog-do-db=adam

[mysql.server]
user=mysql
basedir=/var/lib

[mysqld_safe]
err-log=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

 

Step 8:

Create a replication slave account on master2 for master1:

mysql> grant replication slave on *.* to 'replication'@192.168.16.4 identified by 'slave2';

 

Step 9:

Edit my.cnf on master1 for information of its master.

[mysqld]
datadir=/var/lib/mysql
socket=/var/lib/mysql/mysql.sock

# Default to using old password format for compatibility with mysql 3.x
# clients (those using the mysqlclient10 compatibility package).
old_passwords=1


log-bin
binlog-do-db=adam
binlog-ignore-db=mysql
binlog-ignore-db=test

server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306

[mysql.server]user=mysqlbasedir=/var/lib 

 

Step 10:

Restart both mysql master1 and master2.

On mysql master1:

mysql> start slave;

On mysql master2: 

mysql > show master status;

On mysql master 1:

mysql> show slave status\G;


*************************** 1. row ***************************
             Slave_IO_State: Waiting for master to send event
                Master_Host: 192.168.16.5
                Master_User: replica
                Master_Port: 3306
              Connect_Retry: 60
            Master_Log_File: Mysql1MYSQL02-bin.000008
        Read_Master_Log_Pos: 410
             Relay_Log_File: Mysql1MYSQL01-relay-bin.000008
              Relay_Log_Pos: 445
      Relay_Master_Log_File: Mysql1MYSQL02-bin.000008
           Slave_IO_Running: Yes
          Slave_SQL_Running: Yes
            Replicate_Do_DB:
        Replicate_Ignore_DB:
         Replicate_Do_Table:
     Replicate_Ignore_Table:
    Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
                 Last_Errno: 0
                 Last_Error:
               Skip_Counter: 0
        Exec_Master_Log_Pos: 410
            Relay_Log_Space: 445
            Until_Condition: None
             Until_Log_File:
              Until_Log_Pos: 0
         Master_SSL_Allowed: No
         Master_SSL_CA_File:
         Master_SSL_CA_Path:
            Master_SSL_Cert:
          Master_SSL_Cipher:
             Master_SSL_Key:
      Seconds_Behind_Master: 103799
1 row in set (0.00 sec)

ERROR:
No query specified

Check for the hightlighted rows, make sure its running. Now you can create tables in the database and you will see changes in slave. Enjoy!! 

Share this page:

33 Comment(s)

Add comment

Comments

From: Chris Dean at: 2009-05-06 11:41:11

Here are some additional notes on issues you might encounter during this process:



  1. If your first Master DB (Master 1) contains views (at the time of writing) you'll not be able to use the command "load data from master" to clone Master 1 to Master 2 due to this bug: and you'll have to replicate the existing system some other way e.g. copy and paste data files or stream data via mysqldump


  2. If you use auto incremeneting id's and you're using Master Master with both masters active you might want to consider setting the following options on Master 1 and Master 2 so that the auto increment values don't end up clashing in the case where an insert happens to occur at exactly the same time on both servers:

    Make Master 1 only auto-increment odd numbers by adding this to my.cnf under [mysqld]:
    auto_increment_increment= 2
    auto_increment_offset   = 1


    Make Master 2 only auto-increment even numbers by adding this to my.cnf under [mysqld]:
    auto_increment_increment= 2
    auto_increment_offset   = 2

    This is particularly important if you're using auto increment id columns as primary keys

From: Anonymous at: 2009-04-17 16:49:10


for the autoincrement field if you run it buck wild on both u get data for a single field from both servers.


this breaks replication. error duplicate yadda yadda yadda


serverID 1


auto_increment_increment        2                               
auto_increment_offset            1   


------------------------------------------------                             


serverID 2

auto_increment_increment        2                               
auto_increment_offset            2


serverID 1 will start at 1 and increment 2 always giving odd numbers


serverID2 will start at 2 and increment by 2 always giving even numbers.


this will prevent the autoincrement field having duplicates when cross replicating. 

From: at: 2007-06-14 14:39:16

One thing that is missing from the above, is how to deal with auto-increments so that you do not get collisions if you add data to both masters at the same time. There is an easy way to deal with this if you are using MySQL 5.0.2 or above.


In the my.cnf file on server1 add:


auto_increment_increment=2
auto_increment_offset=1


In the my.cnf file on server2 add:


 auto_increment_increment=2
auto_increment_offset=2


This will make the auto-increment on server1 go, 1,3,5,7,9,etc... and on server2 go, 2,4,6,8,etc... thereby preventing collisions.


Andrew
www.andrewdixon.co.uk

From: Andreas at: 2008-10-05 14:44:42

How will this setup solve collisions in data changes?


"First wins?"


"Don't care?"

From: Matt at: 2008-10-16 07:18:59

It doesn't.  If both nodes attempt to edit the same record at the same time, you could have problems.  Not sure on the behavior, but most likely the last one to update would win.


 The post described a way to deal with auto-increment data type, which is often use to uniquely index new records.  The clear use of increment interval and offset allows new records to be created without collision.

From: Marco Fretz at: 2008-10-21 13:38:57

thank you for this great how-to. works pretty well for me.


 


 

From: Fabio Cecaro at: 2008-11-27 09:53:29

Hi sheikhsa,


In a Master-Master replication there are many problems with the autonumber field tables.


Because when a client insert a value in a table in a master on the other master we can have another client with another insert in the same table. So we have a possible conflict when the replication mechanism replies these two records, because if this ID is relationate with another table I think we corrupting the relational logical.


We must fix addend two lines in the my.cnf of two masters


auto_increment_increment


auto_increment_offset


Example in this case we can set to the first Master the values:


auto_increment_increment = 2


auto_increment_offset = 1


 and in the other:


auto_increment_increment = 2


auto_increment_offset = 2.


So in one master we generate only the pairs number and  dispair in the other, when the replication come we haven't conflicts.

From: tom at: 2008-12-03 00:55:31

hi adam,


thanks for putting this up. just one possible typo:


in step 2 you have


....
 old_passwords=1

log-bin
binlog-do-db=<database name>  # input the database which should be replicated
....


shouldn't log-bin be followed with a filename like ?


log-bin    =   /var/log/mysql/mysql-bin.log

cheers,
tom


 


 

From: Anonymous at: 2009-01-26 08:06:55

Dear, I have done the Multi master Replication with the proceedure described above. No records are being replicated on both servers. and the status of the Slave shows: Slave_IO_Running: NO Slave_SQL_Running: Yes And same thing are shown on both Servers when looking at the 'show slave status\G;' Please help me out how i can rectify this problem. Regards, Majid

From: Will Fitch at: 2009-03-05 02:50:39

Agreed. InnoDB's row-level locking (or any locking for that matter) doesn't propagate via the replication protocol.  This is a big disadvantage and the only solution is to implement a solution on the client code.

From: Glen Bremner-Stokes at: 2009-07-16 20:12:37

Thank you for a great post and for taking the time to put it out there.


I now have my sugarcrm system on the web and a local sugarcrm on my laptop working bi-directionally. So I can update a record in either system and the other is instantly updated.


Very very cool and really easy following your instructions. Well done. 


 

From: krex at: 2009-08-14 06:19:05

 Try setting on both servers my.cnf:


auto_increment_increment=1


auto_increment_offset=2

From: at: 2009-11-23 17:27:15

Today , While trying this article, I was keep getting this error for Step 9( Changing Master1 as Slaves for Master2)


---------------------------


091123 18:59:13 [ERROR] Slave I/O: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the --replicate-same-server-id


option must be used on slave but this does not always make sense; please check the manual before using it). Error_code: 1593

 --------------------------------

I don't know if I was doing anything wrong or not.

but by adding  "log-slave-updates" solved my issue.

so it was like this :

server-id=1
#information for becoming slave.
master-host = 192.168.16.5
master-user = replication
master-password = slave2
master-port = 3306

log-slave-updates

 

Ref :

(a) http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_replicate-same-server-id

 

(b) http://dev.mysql.com/doc/refman/5.0/en/replication-options-slave.html#option_mysqld_log-slave-updates

 Thanks

From: Hassan at: 2009-11-11 08:39:06

do you have any steps for us to setup load balancer with master-master replication what you described above?


 if we are using PHP5, where does the connection point to in the master-master replication?


 I'm looking for failover in this setup, how does it work?


regards

From: Anonymous at: 2009-09-17 05:44:52

Do you think this setting is required for active-passive  master-master replication?

I don't think as there will be no simulteneous writes that may require alternating auto increment requied!

From: Fred59 at: 2010-03-17 15:38:20

Thanks for this good howto.


Despite writing different auto_increment_offset in master 1 and master2, all my records are always with even number. How is it possible ?

From: grozours at: 2010-06-13 18:44:41

I suppose you have the same mysql username and password for both databases.


I suppose you have the same replication username and password for both databases. 


 


An environment script  mysql-env.sh :


export mysql_username=your_mysql_user


export mysql_password=your_mysql_user_password


export master1=hostname_of_master1


export master2=hostname_of_master2


export master1_ip=ip_of_master1


export master2_ip=ip_of_master2


export replication_user=your_replication_user


export replication_password=your_replication_password


 


Check mysql replication status script check-mysql-replication.sh:


 


#!/bin/bash


source mysql-env.sh




Master1_Slave_IO_Running=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep Slave_IO_Running | awk '{ print $2 }';)


Master1_Slave_SQL_Running=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep Slave_SQL_Running | awk '{ print $2 }';)


Master1_Slave_Master_Log_File=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep "\ Master_Log_File" | awk '{ print $2 }';)


Master1_Slave_Read_Master_Log_Pos=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep Read_Master_Log_Pos | awk '{ print $2 }';)




Master2_Slave_IO_Running=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep Slave_IO_Running | awk '{ print $2 }';)


Master2_Slave_SQL_Running=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep Slave_SQL_Running | awk '{ print $2 }';)


Master2_Slave_Master_Log_File=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep "\ Master_Log_File" | awk '{ print $2 }';)


Master2_Slave_Read_Master_Log_Pos=$(mysql -Bse "show slave status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep Read_Master_Log_Pos | awk '{ print $2 }';)




Master1_Master_File=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep File | awk '{ print $2 }';)


Master1_Master_Position=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep Position | awk '{ print $2 }';)




Master2_Master_File=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep File | awk '{ print $2 }';)  


Master2_Master_Position=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep Position | awk '{ print $2 }';)




# Master 1($master1) / Slave 1($master2)




echo "";


echo "-------------------------------------------";


echo "Master 1($master1) / Slave 1($master2) Status :";


echo "-------------------------------------------";


echo "Master : $Master1_Master_File - Position : $Master1_Master_Position"


echo "Slave  : $Master2_Slave_Master_Log_File - Position : $Master2_Slave_Read_Master_Log_Pos"


echo "Slave_IO_Running  : $Master1_Slave_IO_Running"


echo "Slave_SQL_Running : $Master1_Slave_SQL_Running"


echo "";


if [ "$Master1_Master_File" == "$Master2_Slave_Master_Log_File" ] && [ "$Master1_Master_Position" == "$Master2_Slave_Read_Master_Log_Pos" ] && [ "$Master1_Slave_IO_Running" == "Yes" ] && [ "$Master1_Slave_SQL_Running" == "Yes" ];


then


echo "Status OK";


else


echo "Status KO";


fi


echo "";






# Master 2($master2) / Slave 2($master1)




echo "-------------------------------------------";


echo "Master 2($master2) / Slave 2($master1)"


echo "-------------------------------------------";


echo "Master : $Master2_Master_File - Position : $Master2_Master_Position"


echo "Slave  : $Master1_Slave_Master_Log_File - Position : $Master1_Slave_Read_Master_Log_Pos"


echo "Slave_IO_Running  : $Master2_Slave_IO_Running"


echo "Slave_SQL_Running : $Master2_Slave_SQL_Running"


echo "";


if [ "$Master2_Master_File" == "$Master1_Slave_Master_Log_File" ] && [ "$Master2_Master_Position" == "$Master1_Slave_Read_Master_Log_Pos" ] && [ "$Master2_Slave_IO_Running" == "Yes" ] && [ "$Master2_Slave_SQL_Running" == "Yes" ];


then   


        echo "Status OK";


else   


        echo "Status KO";


fi


echo ""; 


 


 


 


Restore mysql replication script restore-mysql-replication.sh: 


 #!/bin/bash


source mysql-env.sh




ps -ef | grep -v grep | grep -q  mysql-$master1


if [ "$?" -eq "1" ];


then


screen -dmS mysql-$master1 mysql -u ${mysql_username} -p${mysql_password} -h $master1


fi




ps -ef | grep -v grep | grep -q  mysql-$master2


if [ "$?" -eq "1" ];


then


screen -dmS mysql-$master2 mysql -u ${mysql_username} -p${mysql_password} -h $master2


fi




screen -S mysql-$master1 -X -p0 eval "stuff 'STOP SLAVE;'^M"


screen -S mysql-$master1 -X -p0 eval "stuff 'FLUSH TABLES WITH READ LOCK;'^M"




screen -S mysql-$master2 -X -p0 eval "stuff 'STOP SLAVE;'^M"


screen -S mysql-$master2 -X -p0 eval "stuff 'FLUSH TABLES WITH READ LOCK;'^M"




Master2_Master_File=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep File | awk '{ print $2 }';)


Master2_Master_Position=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master2 | grep Position | awk '{ print $2 }';)




screen -S mysql-$master1 -X -p0 eval "stuff 'CHANGE MASTER TO MASTER_HOST="'"'${master2_ip}'"'", MASTER_PORT=3306,MASTER_USER="'"'${replication_user}'"'", MASTER_PASSWORD="'"'${replication_password}'"'"';^M"


screen -S mysql-$master2 -X -p0 eval "stuff 'CHANGE MASTER TO MASTER_HOST="'"'${master1_ip}'"'", MASTER_PORT=3306,MASTER_USER="'"'${replication_user}'"'", MASTER_PASSWORD="'"'${replication_password}'"'"';^M"






screen -S mysql-$master1 -X -p0 eval "stuff 'CHANGE MASTER TO MASTER_LOG_FILE="'"'$Master2_Master_File'"'",MASTER_LOG_POS=$Master2_Master_Position;'^M"




Master1_Master_File=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep File | awk '{ print $2 }';)


Master1_Master_Position=$(mysql -Bse "show master status\G" -u ${mysql_username} -p${mysql_password} -h $master1 | grep Position | awk '{ print $2 }';)




screen -S mysql-$master2 -X -p0 eval "stuff 'CHANGE MASTER TO MASTER_LOG_FILE="'"'$Master1_Master_File'"'",MASTER_LOG_POS=$Master1_Master_Position;'^M"




screen -S mysql-$master2 -X -p0 eval "stuff 'START SLAVE;'^M"


screen -S mysql-$master2 -X -p0 eval "stuff 'UNLOCK TABLES;'^M"




screen -S mysql-$master1 -X -p0 eval "stuff 'START SLAVE;'^M"


screen -S mysql-$master1 -X -p0 eval "stuff 'UNLOCK TABLES;'^M"




screen -S mysql-$master2 -X -p0 eval "stuff 'quit;'^M"


screen -S mysql-$master1 -X -p0 eval "stuff 'quit;'^M"

From: Eggert at: 2010-09-27 14:54:18

While this is a great tutorial, there seems to be a glitch in step 2:


binlog-do-db=<database name>  # input the database which should be replicated


should read:


replicate-do-db=<database name>  # input the database which should be replicated


Agree?

From: Charles-Alban Allard at: 2010-09-09 12:18:01

Thank you for those scripts.
On the first script, there is a little error, I think.

The end shoul be:



# Master 1($master1) / Slave 1($master2)


echo "";

echo "-------------------------------------------";

echo "Master 1($master1) / Slave 1($master2) Status :";

echo "-------------------------------------------";

echo "Master : $Master1_Master_File - Position : $Master1_Master_Position"

echo "Slave  : $Master2_Slave_Master_Log_File - Position : $Master2_Slave_Read_Master_Log_Pos"

echo "Slave_IO_Running  : $Master2_Slave_IO_Running"

echo "Slave_SQL_Running : $Master2_Slave_SQL_Running"

echo "";

if [ "$Master2_Master_File" == "$Master1_Slave_Master_Log_File" ] && [ "$Master2_Master_Position" == "$Master1_Slave_Read_Master_Log_Pos" ] && [ "$Master2_Slave_IO_Running" == "Yes" ] && [ "$Master2_Slave_SQL_Running" == "Yes" ];

then   

        echo "Status OK";

else   

        echo "Status KO";

fi

echo "";


# Master 2($master2) / Slave 2($master1)


echo "-------------------------------------------";

echo "Master 2($master2) / Slave 2($master1)"

echo "-------------------------------------------";

echo "Master : $Master2_Master_File - Position : $Master2_Master_Position"

echo "Slave  : $Master1_Slave_Master_Log_File - Position : $Master1_Slave_Read_Master_Log_Pos"

echo "Slave_IO_Running  : $Master1_Slave_IO_Running"

echo "Slave_SQL_Running : $Master1_Slave_SQL_Running"

echo "";

if [ "$Master1_Master_File" == "$Master2_Slave_Master_Log_File" ] && [ "$Master1_Master_Position" == "$Master2_Slave_Read_Master_Log_Pos" ] && [ "$Master1_Slave_IO_Running" == "Yes" ] && [ "$Master1_Slave_SQL_Running" == "Yes" ];

then

echo "Status OK";

else

echo "Status KO";

fi

echo "";

From: Luis Loriente at: 2010-11-08 22:10:49

Great post, I have applied this configuration and works fine, but I think there is a problem if we want a point-in-time recovery for data-loss or accidental corruption of database.


 To get this in an usual configuration (just one mysql server) we have to start from a snapshot with mysqldump (or other) and then apply the log_bin of the server to the point we want, or we can manipulate this log-bin to extract undesired lines.


 With master-master configuration we have two log-bin groups (one from each server) to apply to the initial mysqldump snapshot, so we would need some kind of tool to merge these two log_bin groups in chronological order to get just one and apply it to the initial snapshot.


Due to the master-master configuration we can't activate the log_slave_updates which would generate a full log_bin of changes.


Has anyone thought about this?

From: Anonymous at: 2010-10-07 13:49:11

 I read many comments about how good is using autoincrement to avoid collision.


No one cares about data?  IDs are important for a lot of applications. Correlative IDs are expected usually. What would you think if you see a table an IDs are only even? "I'm loosing data!"


Since it's not the default behaviour for databases, it could create odd situations...

From: Ross at: 2010-10-17 03:00:57

I think it's a mistake to use id's for anything other than unique identification. I specifically think it's a mistake to attempt to extract business logic from an auto-generated id column. If you want an ordinal column that correlates to row creation time, add a creation_date column with a time value in it.

From: Lars at: 2011-01-26 08:35:13

Please make sure that you have given each server a uniqe server-id


From: Adam at: 2011-01-15 17:32:52

Hi,


 At the end of the session you're getting the "ERROR: No query specified" because your "show slave status" ends with "\G;".


The "\G" token ends a query, so following that you have zero bytes and then another end query token, same as if you'd done "show slave status;;"

From: Anonymous at: 2011-03-11 12:43:13

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

pls help me...............

From: Ramanath at: 2011-03-11 08:37:56

Master2/Slave1 is showing both slave staus and master status.but master1/slave2 is showing only master status and if i given start slave command then the below error is coming.So please help me

ERROR 1200 (HY000): The server is not configured as slave; fix in config file or with CHANGE MASTER TO

From: Michael Pawlowsky at: 2011-04-19 01:52:19


From the mysql.com site:


 The server evaluates the --binlog-do-db and --binlog-ignore-db options in the same way as it does the --replicate-do-db and --replicate-ignore-db options. For information about how this is done, seeSection 15.2.3.1, “Evaluation of Database-Level Replication and Binary Logging Options”.


From: Anonymous at: 2011-04-20 12:41:01

try "FLUSH SLAVE", then do your "CHANGE MASTER TO ..." and then "START SLAVE" again, this worked for me,

 

Hopefully it works for you too.

From: Aamir Hussain at: 2011-05-23 12:51:48

Hi, its really a great tutorial can anybody tell me how i can check High Availability in case of  failover?

From: Al at: 2012-03-19 19:42:16

a step by step for creating replication with examples is listed at How to create Replication on Mysql: Step by step

From: Iain at: 2012-09-05 02:59:54

Heh, +1 on that front.  We had a system at work that used sequential ID numbers on entries (nothing to do with row IDs in a database but...), then we ran out of usable ID space, and switched to a random "try some numbers till you find a free one" (and later increased the usable ID space anyway).  Turns out people were using it to count the number of new entries created every day and suddenly got nothing, then 1000x the normal volume due to high random numbers.  Tsk tsk, told em how to do it *properly* (there's a created date field, they just weren't using it...)

From: Mohammad Khan at: 2013-01-23 17:22:22

Hi,

I am following the tutorial and trying to set up master master replication.In step 4, I added

master - host = <IP>

to .ini file on machine 2 ( which is Master 2). The IP I am adding here is the IP of machine1(
which is master1).

However, when restart mysql server on machine 2, It does not start and following error
is shown in logs:

unknown variable master-host=<IP>.

Any suggestions to resolve this issue will be appreciated.

Thanks.

From: Anonymous at: 2013-03-07 12:35:42

There are breaking changes in Mysql 5.6 see:

 http://dev.mysql.com/doc/refman/5.6/en/replication-options-slave.html