MySQL 5.6 Data Replication Syncronization Checker Between Servers (Master & Slave)

Mubashar Iftikhar

Hi Everyone

I have one Master and Slave

OS : MySQL5.6 & RHEL 7.0

Replication Status : Running Fine

Everything working fine and correctly

    I want to check that the data is correctly replicated between servers (Master to Slave)

     and with the help of "mysqlrplsync", i execute this command

mysqlrplsync --master=repuser:[email protected]:3306 \ --slaves=repuser:[email protected]:3306,repuser:[email protected]:3306

but this command show that error

[[email protected] common]# mysqlrplsync --master=root:[email protected]:3306 \ --slaves=rpl:[email protected]:3306

WARNING: Using a password on the command line interface can be insecure.

Usage: mysqlrplsync --master=user:[email protected]:port --slaves=user:[email protected]:port \

                    [<db_name>[.<tbl_name>]]

mysqlrplsync: error: Option --discover-slaves-login or --slaves is required.

Note : Even i have tried by adding the database name in the above command

My this method to check Data synchronization is correct ?

If not then can you please guide me how to perform Synchronization of Data Step by Step ?

Best Regards

Mubashar Iftikhar

Dave Stokes-MySQL Community Team-Oracle

Can you show the output of  'mysqlrplcheck --master=root:[email protected]:3306 --slave=rpl:[email protected]:3306'   please ??

Mubashar Iftikhar

Dave Stokes-MySQL Community Team-Oracle

If you are typing the command on one line you can omit the '\'

Mubashar Iftikhar

Well Thank you

Can you provide me complete syntax as you telling above ...

Mubashar Iftikhar

Dear Waiting your response

Dave Stokes-MySQL Community Team-Oracle

mysqlrplcheck --master=root:[email protected]:3306 --slaves=rpl:[email protected]:3306

Mubashar Iftikhar

Hi Dave
                     I hope you are doing fine. I have a running environment of Master Slave Replication. Everything is working fine now. I can check data consistency, but when i shutdown or restart mysql service error like this is appear again i.e.

[[email protected] ~]# mysqlrplsync --master=repuser:[email protected]@localhost:3306 --slaves=repuser:[email protected]@192.168.10.22:3306  -vvv

WARNING: Using a password on the command line interface can be insecure.

# Checking users permission to perform consistency check.

#

# WARNING: Replication filters found on checked servers. This can lead data consistency issues depending on how statements are evaluated.

# More information: http://dev.mysql.com/doc/en/replication-rules.html

# Master '[email protected]':

#   - binlog_do_db: rep

ERROR: Master must support GTIDs and have GTID_MODE=ON.

My question is how to permanently enable GTID ?

for enabling i have run these commands

mysql> show global variables like '%GTID%';

@GLOBAL.GTID_MODE = ON;

@GLOBAL.GTID_MODE = ON_PERMISSIVE;

@GLOBAL.GTID_MODE = ON;

after running these commands everything working fine but after restart the service or shutdown then again i have to run above commands for enabling the GTID MODE and for checking the data consistency .

How i can enable permanenty GTID mode ?

Gaz in Oz

You should put:

gtid_mode=on

in your my.cnf files on both servers.

Make sure it is not overwritten by later read my.cnf files.

# mysql --help | sed -ne '/^\/etc\|^Default/p'Default options are read from the following files in the given order:/etc/mysql/my.cnf /etc/my.cnf ~/.my.cnf

...so check all of those files, if they exist. If you set the vars in /etc/my.cnf, make sure it is not reset in any subsequent my.cnf file.

Note the documentation says you also need to have "enforce_gtid_consistency=true", that I think depends on your 3 digit version of mysql. e.g. 5.7.6

Again, this is all in the documentation.

https://dev.mysql.com/doc/refman/5.7/en/replication-options-gtids.html

Mubashar Iftikhar

Thanks Gaz
                  Working now even after shutdown or restart. again thanks for your time and help

Gaz in Oz

You're welcome. Happy to hear you got it sorted.