Comments on MySQL Master Master Replication

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
   
   

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

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. 

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

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 ?

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

How will this setup solve collisions in data changes?

"First wins?"

"Don't care?"

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.

 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...

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.

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...)

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.

There are breaking changes in Mysql 5.6 see:

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

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.

 Try setting on both servers my.cnf:

auto_increment_increment=1

auto_increment_offset=2

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

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.

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

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

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

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. 

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

 Thanks

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

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"

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 "";

Yes, but the final check should be:

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

if [ "$Master1_Master_File" == "$Master2_Slave_Master_Log_File" ] && [ "$Master1_Master_Position" == "$Master2_Slave_Read_Master_Log_Pos" ] && [ "$Master2_Slave_IO_Running" == "Yes" ] && [ "$Master2_Slave_SQL_Running" == "Yes" ];

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

if [ "$Master2_Master_File" == "$Master1_Slave_Master_Log_File" ] && [ "$Master2_Master_Position" == "$Master1_Slave_Read_Master_Log_Pos" ] && [ "$Master1_Slave_IO_Running" == "Yes" ] && [ "$Master1_Slave_SQL_Running" == "Yes" ];

Sorry, i am really new to this. 

where do i place 

mysql-env.sh 

check-mysql-replication.sh

restore-mysql-replication.sh 

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 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”.

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?

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;;"

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

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

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

Hopefully it works for you too.

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

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

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

Thanks for the script to check DB replication and restore DB replication. However restore DB replication script will end up into further trouble since it will make both the DB out of sync in terms of data which can break the replication at later point of time. If restore script can include taking DB dump from one DB and restoring it to another DB and then do all the settings, that would be a real restore.

Hi, i tried with step 4, but I have this problem:

sudo /etc/init.d/mysqld start

MySQL Daemon failed to start.

Starting mysqld:                                           [FAILED] 

Can I help me please?

Bye

In a Master-Master Replication, If one server goes down for a long period. Whats happen when this server goes up again? The replication system will automatic sync the server without any manual intervention ?

Yes, as long as the outage is not longer than the blinlig storage time.

Thank you for your reply!

Dear Sir ,

   I did both way communication by replication on PC1 and  PC2 its work fine . Problem is Another pc PC3 is only slave and i set its master is PC1 . When any change Occure in PC1 its hits Both PC2 and PC3 .But When any change occure in PC2 its Only Occure in PC2  , PC3 Doesn't receive any change whats problem .

NOTE . PC1 and PC2 Both  are Master and salve ,           PC3 Only Slave Connected PC1 as master

Hey everyone. I'd thought i share my automated resync mariadb/mysql script. 

It’s possible that the master-master (or master-slave) setup get out of sync. This can happen when, for example, you set back snapshot, or in some really exceptional cases, when two queries are send at the same time to different databases with the same database/table as destination. When a out-of-sync occurs, without you knowing it, it’ll cause a big problem. because from that point on, the servers won’t be synchronising data anymore. And because we have a load-balancer with multiple databases, people will keep sending and storing data in different databases. if you find out, after lets say a week, that you’re databases aren’t in sync anymore, than you’ll have to drop a week of data for one database. To overcome this problem, i wrote a script that checks if the database is in sync every 10 minutes, and if not, automatically synchronizes with the master database.

[code]

#First we grab the master status of server 2(the other server) and the slave state (of this server)

#----------------------------------------------------------------

#MAKE SURE YOU EDIT THE *MASTERSERVERIP* TO THE IP OF YOUR SECOND SERVER AND *PASSWORD* TO YOUR MYSQL PASSWORD

MASTER_STATUS=$(ssh root@*MASTERSERVERIP* 'mysql -uroot -p*PASSWORD* -e"SHOW MASTER STATUS;";' | awk '{$1=$1} NR==2')

SLAVE_STATUS=$(mysql -uroot -p'*PASSWORD*' -e "SHOW SLAVE STATUS\G" | awk '{$1=$1} NR==2 || NR==7 || NR==8')

#----------------------------------------------------------------

#The variable MASTER_STATUS contains the MASTER_LOG_FILE and the MASTER_LOG_POS, if a out-of-sync occurs we want to provide our slave with these values.

#Since MASTER_STATUS is now containing exactly for example: "mariadb-bin.000073 317858042"

#We'll make a new variable from this variable, so we have two clean variables containing the log file and position. We'll use these later as variable in our query to update the sync status of the database.

# the awk '{print $1;} makes sure that we only get the first word (mariadb-bin.000073) and awk '{print $2;} the second word (317858042)

MASTER_LOG_FILE=$(echo "$MASTER_STATUS" | awk '{print $1;}')

MASTER_LOG_POS=$(echo "$MASTER_STATUS" | awk '{print $2;}')

#----------------------------------------------------------------

#For our comparisation (the check if everything is running fine) we need to check the SLAVE_IO_STATE.

#SLAVE_STATUS contains the following 3 lines at the moment:

#

#Slave_IO_State: Waiting for master to send event

#Master_Log_File: mariadb-bin.000073

#Read_Master_Log_Pos: 317858042

#

#We want to extract each part after the ": " part. So we can compare the string "Waiting for master to send event". This can be done with: "awk 'NR==1' | sed -n -e 's/^.*: //p'".

#We use awk to get a certain line number and sed to print the part after a certain pattern, in this case ": "

#If the string contains something else, it means that the database is out of sync.

#The slave master log file and log position can be printed afterward to make sure that the master and slave are in sync now.

SLAVE_IO_STATE=$(echo "$SLAVE_STATUS" | awk 'NR==1' | sed -n -e 's/^.*: //p')

#----------------------------------------------------------------

#We don't need these variables. Altough they could come in handy for later (for some reason, don't know what yet?) for now i'll coment these two out

SLAVE_LOG_FILE=$(echo "$SLAVE_STATUS" | awk 'NR==2' | sed -n -e 's/^.*: //p')

SLAVE_LOG_POS=$(echo "$SLAVE_STATUS" | awk 'NR==3' | sed -n -e 's/^.*: //p')

#----------------------------------------------------------------

printf "\n\n\n\n"

#----------------------------------------------------------------

#We now have all the information needed to check if the server is still in sync, and if not, sending a query with the right log pos and file.

if [ "$SLAVE_IO_STATE" != "Waiting for master to send event" ]

then

printf "\n\nTHE SLAVE STATE IS: \n"

echo "$SLAVE_IO_STATE"

printf "\n\nTHE MASTER LOG FILE IS: \n"

echo "$MASTER_LOG_FILE"

printf "\n\nTHE SLAVE LOG FILE IS: \n"

echo "$SLAVE_LOG_FILE"

printf "\n\nTHE MASTER LOG POSITION IS: \n"

echo "$MASTER_LOG_POS"

printf "\n\nTHE SLAVE LOG POSITION IS: \n"

echo "$SLAVE_LOG_POS"

printf "\n\n\nSERVER IS OUT OF SYNC \nRESYNCING SERVER NOW!!! \nPLEASE  WAIT!!!\n\n\n"

mysql -uroot -p'*PASSWORD*' -e "STOP SLAVE;"

mysql -uroot -p'*PASSWORD*' -e "CHANGE MASTER TO MASTER_LOG_FILE='$MASTER_LOG_FILE', MASTER_LOG_POS=$MASTER_LOG_POS;"

mysql -uroot -p'*PASSWORD*' -e "START SLAVE;"

MASTER_STATUS=$(ssh root@*MASTERSERVERIP* 'mysql -uroot -p*PASSWORD* -e"SHOW MASTER STATUS;";' | awk '{$1=$1} NR==2')

SLAVE_STATUS=$(mysql -uroot -p'*PASSWORD*' -e "SHOW SLAVE STATUS\G" | awk '{$1=$1} NR==2 || NR==7 || NR==8')

MASTER_LOG_FILE=$(echo "$MASTER_STATUS" | awk '{print $1;}')

MASTER_LOG_POS=$(echo "$MASTER_STATUS" | awk '{print $2;}')

SLAVE_IO_STATE=$(echo "$SLAVE_STATUS" | awk 'NR==1' | sed -n -e 's/^.*: //p')

SLAVE_LOG_FILE=$(echo "$SLAVE_STATUS" | awk 'NR==2' | sed -n -e 's/^.*: //p')

SLAVE_LOG_POS=$(echo "$SLAVE_STATUS" | awk 'NR==3' | sed -n -e 's/^.*: //p')

printf "\n\nTHE SLAVE STATE IS: \n"

echo "$SLAVE_IO_STATE"

printf "\n\nTHE MASTER LOG FILE IS: \n"

echo "$MASTER_LOG_FILE"

printf "\n\nTHE SLAVE LOG FILE IS: \n"

echo "$SLAVE_LOG_FILE"

printf "\n\nTHE MASTER LOG POSITION IS: \n"

echo "$MASTER_LOG_POS"

printf "\n\nTHE SLAVE LOG POSITION IS: \n"

echo "$SLAVE_LOG_POS"

else

echo "SERVER IS IN SYNC"

printf "\n\nTHE SLAVE STATE IS: \n"

echo "$SLAVE_IO_STATE"

printf "\n\nTHE MASTER LOG FILE IS: \n"

echo "$MASTER_LOG_FILE"

printf "\n\nTHE SLAVE LOG FILE IS: \n"

echo "$SLAVE_LOG_FILE"

printf "\n\nTHE MASTER LOG POSITION IS: \n"

echo "$MASTER_LOG_POS"

printf "\n\nTHE SLAVE LOG POSITION IS: \n"

echo "$SLAVE_LOG_POS"

fi

printf "\n\n\n\n"

[/code]

Is this is required to perform databse equalization, if i have existing server with diffrent no. of database and different amount of data in both server? Before starting the master to master replication. While i want to replicate only some of them database.

Hello.. What happen if sometimes slave is offline? Will replication continue working when slave become online again? Thanks for explanation.

Thanks for your explanation, btw im trying master to master replication, but theres only one slave whom waiting for master to send event, while the other one is connecting to master. The replication success only on master to slave, 2 host can ping each other, i performed the crud on the master and its ok, any solution?