Not the answer you need?
Register and ask your own question!

How to verify data consistency for replication(master:window OS, slave:Ubuntu OS)?

gpusergpuser ContributorInactive User Role Beginner
Hi,

My replication solution mysql 5.5 version setup is using master as window 8 OS and slave is ubuntu OS. How can I verify my slave data consistency since percona seems like need to verify data from master (linux OS)

Chern
«1

Comments

  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    You'll want to checkout pt-table-checksum, which does just what you are looking for:
    https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    pt-table-checksum usually run on master machine in linux right? In my case (master in window, slave in ubuntu), how should i proceed ?
  • martin.arrietamartin.arrieta Member Inactive User Role Beginner
    You can run pt-table-checksum from another server and use the hostname to connect to the master.

    For example:

    pt-table-checksum [options] h=your_master_ip

    Regards,

    Martin.
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Martin,

    For example:
    my master (window) IP is 192.168.1.100
    my slave (ubuntu) IP is 192.168.1.101

    your suggestion now is I use 3rd server to run the command?
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    You can run pt-table-checksum from any Linux host, so you should be able to run it on the slave. The only caveat is that you must be able to connect to the master MySQL instance, so you have to have a database account that will allow the connection (i.e. the account host is "%" to allow a connection from anywhere, or it matches whatever host you are running pt-table-checksum from).

    If you want to check all the tables in a database, the command is fairly straight forward:

    pt-table-checksum --databases=<your database> --host=<master host> --port=<master port> --user=<db user> --password=<db password>

    If you do not want to put your password on the command line, you can use --ask-pass for it to task you when you run it. By default, pt-table-checksum will then connect to the master and run a "show processlist" to find your slave, and then will start doing the checksums and comparison. I would also recommend running the pt-table-checksum command in a screen session if your database is large, as it can take quite a while to run.

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    I want to check all the databases, the command should be " --databases=all"?
    192.168.104.197 is my master IP

    When i run the command :
    pt-table-checksum --databases=all --host=192.168.104.197 --port=3306 --user=root --password= <password>

    It returns error to me as below:

    Cannot connect to h=REPLICATION,p=...,u=root
    Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.

    Please advise
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    You would just leave off the --databases option if you actually want it to check all the databases. It looks like it is complaining about not being able to connect, so I would try to manually connect to mysql from the server you are trying to run pt-table-checksum on and see if you can actually connect or not.

    mysql --host=192.168.104.197 --port=3306 --user=root --password= <password>

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    I able to run below checksum command
    pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password= <pass>

    Because of the database too large, I tried to save the output in a txt file but it return me error
    command:
    pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password=pass > output.txt

    It return me error:

    Cannot connect to h=REPLICATION,p=...,u=root
    Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.

    Any suggestion?
  • gpusergpuser Contributor Inactive User Role Beginner
    Scott,

    One more inquiry need your advise.

    After a long run of checksum, can I just get a summary of the result? if yes, what is the command?
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    To get a progress update, you can add:
    --progress=time,300

    That will output progress every 5 minutes.

    As for why your command would fail after adding the redirection at the end (>), I do not know. That should not affect the connection parameters so that is a bit strange. I just tried your same command with and without the output redirection and they both worked. So I would just verify that nothing is changing in your second command (i.e. the password).

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    I knew why like this already.

    whenever I run command:
    pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password= <pass>
    or
    pt-table-checksum --replicate=percona.checksums --create-replicate-table --empty-replicate-table --no-check-binlog-format h=192.168.104.197,u=root,p=<pass>

    It will output me then start the process.
    Cannot connect to h=REPLICATION,p=...,u=root
    Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.

    TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
    06-04T15:46:35 0 0 0 1 0 0.062 mysql.columns_priv
    06-04T15:46:35 0 0 47 1 0 0.058 mysql.db
    06-04T15:46:35 0 0 0 1 0 0.051 mysql.event
    06-04T15:46:35 0 0 0 1 0 0.050 mysql

    When I tried to the get summary of differences (if no differences were found this will come up empty) with command:
    pt-table-checksum --replicate=percona.checksums --replicate-check --replicate-check-only --no-check-binlog-format h=192.168.104.197,u=root,p=<pass>

    It return me output only because of no differences were found:
    Cannot connect to h=REPLICATION,p=...,u=root
    Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information

    In case differences are found I should run pt-table-sync like below? "h=localhost", or put my slave IP? (i'm running command in slave only)
    pt-table-sync --print --replicate=percona.checksums --sync-to-master h=localhost,u=root,p=<pass>

    If no differences are found, it will return error?
    DBD::mysql::db selectall_arrayref failed: Table 'percona.checksums' doesn't exist [for Statement "SELECT db, tbl, CONCAT(db, '.', tbl) AS `table`, chunk, chunk_index, lower_boundary, upper_boundary, COALESCE(this_cnt-master_cnt, 0) AS cnt_diff, COALESCE(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 0) AS crc_diff, this_cnt, master_cnt, this_crc, master_crc FROM percona.checksums WHERE master_cnt <> this_cnt OR master_crc <> this_crc OR ISNULL(master_crc) <> ISNULL(this_crc)"] at /usr/bin/pt-table-sync line 4957.
    Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle ;host=localhost;mysql_read_default_group=client at /usr/bin/pt-table-sync line 4957.
    Issuing rollback() due to DESTROY without explicit disconnect() of DBD::mysql::db handle ;host=192.168.104.197;port=3306;mysql_read_default_group=client at /usr/bin/pt-table-sync line 4957.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    It appears it is not detecting the slave correctly, as the basic use case is pretty straight forward and does not require many of the options you are giving it.

    I would try creating the dsns table to specify your slave explicitly and see if that helps.

    On your master database:

    CREATE DATABASE percona;
    USE percona;
    CREATE TABLE `dsns` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `parent_id` int(11) DEFAULT NULL,
    `dsn` varchar(255) NOT NULL,
    PRIMARY KEY (`id`)
    );
    INSERT INTO dsns VALUES (1,NULL,"h=<slave IP/hostname>,P=<slave port>,u=root,p=<password>");

    pt-table-checksum --progress=time,300 --host=192.168.104.197 --port=3306 --user=root --password=<password> --recursion-method dsn=D=percona,t=dsns

    Give that a try and see if it will work.

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    I like simple and straight forward method too. :)

    my master (192.168.104.197,window)
    my slave (192.168.104.192,linux)

    If I run the command from slave to check the data consistency
    pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password= <pass>

    It will go to my master and compare/checksum master and slave database/table? or it only checksum my master database and not comparing with my slave?

    In case, (pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password= <pass>) will compare both master and slave DB, may i know what is the command to get summary of differences and also pt-table-sync if require?

    Or I must creating the dsns table to specify your slave explicitly as what your advice?

    If must create dsns table, after created based on your advice, I run below command at slave to checksum/compare both master and slave DB right?
    pt-table-checksum --progress=time,300 --host=192.168.104.197 --port=3306 --user=root --password=<password> --recursion-method dsn=D=percona,t=dsns

    In this case, after run the checksum, what is the command to get summary of differences ?
    this command?
    pt-table-checksum --replicate=percona.checksums --replicate-check --replicate-check-only --no-check-binlog-format h=192.168.104.197,u=root,p=<pass>

    Sorry for stupid question.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    The idea is that you are getting pt-table-checksum to connect to the master, detect the slave(s) (or you specify them explicitly via the dsns table), and then the tool will run the checksums on the master and the slave(s), and compare them to look for differences.

    The problem is that according to your errors, it is not auto-detecting the slave. So if you create the dsns table to explicitly tell it where your slave is, it should work then. Pt-table-checksum will then connect to the master, look in dsns table to find your slave, connect to your slave, and then run the comparisons. If any differences are found, it should output them. And if you add the --replicate=percona.checksums option like you did, it should also put the differences in that table.

    So if you create the dsns table on the master, you only have to run pt-table-checksum against the master, and it will connect to the slave. You do not run the tool against the slave in this case.

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    I created the table (on master) as per advice and run the command at slave. it return me output below, is it correct?

    [email protected]:/$ pt-table-checksum --progress=time,300 --host=192.168.104.197 --port=3306 --user=root --password=<password> --recursion-method dsn=D=percona,t=dsns

    06-05T02:07:42 Replication filters are set on these hosts:
    replication
    replicate_do_db = subs_010415_jpvby,subs_050215_glxkl,subs_170315_91g7o,subs_240415_6817i,subs_240415_xvxr4,subs_270315_26d96,subs_270315_2cao1,subs_270315_9nkd3,subs_270315_dbpxq,subs_270315_e3q9a,subs_270315_pcirb,subs_270315_xret9,subs_270315_yq5mq,subs_300115_0yh4k,subs_300315_6f3aq,subs_300315_hfh0d,subs_300315_id71e,subs_300315_ijs2h,subs_300315_lt3rp,subs_300315_u5ew2,subs_300315_yv2y1,subs_m
    Please read the --check-replication-filters documentation to learn how to solve this problem. at /usr/bin/pt-table-checksum line 9502.

    [email protected]:/$ pt-table-checksum --host=192.168.104.197 --port=3306 --user=root --password=<password>
    Cannot connect to P=3306,h=REPLICATION,p=...,u=root
    Diffs cannot be detected because no slaves were found. Please read the --recursion-method documentation for information.
    TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
    06-05T02:09:39 0 0 0 1 0 0.052 mysql.columns_priv
    06-05T02:09:39 0 0 47 1 0 0.055 mysql.db
    06-05T02:09:39 0 0 0 1 0 0.051 mysql.event
    06-05T02:09:39 0 0 0 1 0 0.056 mysql.func
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    Yes the first command looks like it connected that time, however you have replication filters setup, so it exited before doing anything. You can run it if you force it to ignore the replication filters, but as the manual suggests, that is dangerous and can break replication.

    https://www.percona.com/doc/percona-toolkit/2.2/pt-table-checksum.html#cmdoption-pt-table-checksum--[no]check-replication-filters

    Since you seem to be fairly new to this area, I would not recommend forcing it, because it could cause you a lot of headache if it does break replication.

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    So what should i do now since it exited before doing anything due to replication filters and it is not recommended to force it run?
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    You could:

    1) Get rid of your replication filters. This is always advised, but requires knowledge as to why they are there to start with and what would need to be done to get rid of them.
    2) Run pt-table-checksum on a single database at a time using the --databases option and --no-check-replication-filters and see what happens.
    3) Not run the tool
    4) Pay for support from Percona or another professional organization/dba

    Unfortunately what I can help you with is limited to general advice, but it sounds like you may need more involved hands-on help.

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    Thanks for your advice.

    If I run the pt-table-checksum with no-check-replication-filters, the worst case will stop the replication only? it wont affect my master database?
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    I tried to run pt-table-checksum on a single database at a time using the --databases option and --no-check-replication-filters

    command:
    [email protected]:/$ pt-table-checksum --databases=SUBS_270315_9NKD3 --no-check-replication-filters --host=192.168.104.197 --port=3306 --user=root --password=<pass> --recursion-method dsn=D=percona,t=dsns
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...

    it is correct? It wont show the process like below output?

    TS ERRORS DIFFS ROWS CHUNKS SKIPPED TIME TABLE
    06-04T15:46:35 0 0 0 1 0 0.062 mysql.columns_priv
    06-04T15:46:35 0 0 47 1 0 0.058 mysql.db
    06-04T15:46:35 0 0 0 1 0 0.051 mysql.event
    06-04T15:46:35 0 0 0 1 0 0.050 mysql
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    It should show you the output with a list of tables it checked after it is finished. When you wrote the above was the tool still running? You could add the --progress=time,300 to see if it's still working on something. It can take quite a while for large tables.

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    Yes, when I type command:
    pt-table-checksum --progress=time,300 --databases=subs_270315_9nkd3 --no-check-replication-filters --host=192.168.104.197 --port=3306 --user=root --password=<pass> --recursion-method dsn=D=percona,t=dsns

    The output is :
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...
    Waiting for the --replicate table to replicate to replication...

    I ran it for whole day still the same output.
    any advice?
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    Depending on your table sizes and number of tables, it could take a long time to run. I have not seen your specific output message before, so not sure if it is stuck waiting on that indefinitely, or if it is just outputting that off and on as it is doing other work. To get a better idea of what it is doing, you could try it in debug mode, but that will output a lot of data, so be careful:

    PTDEBUG=1 pt-table-checksum --progress=time,300 --databases=subs_270315_9nkd3 --no-check-replication-filters --host=192.168.104.197 --port=3306 --user=root --password=<pass> --recursion-method dsn=D=percona,t=dsns > /tmp/pt.log 2>&1

    Other than that, I would check the processlist on the master and slave to see if it is actually doing work still (if you decide not to try debug).

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    I run the debug command and store in google drive.
    https://drive.google.com/file/d/0B_1idIUKpJq_bVI0U0dqNlZvRDQ/view?usp=sharing
    Please assist me have a look into it.

    What I have seen is it keeps looping below error
    # TableParser:4441 18067 Checking `percona`.`checksums`
    # TableParser:4445 18067 SHOW TABLES FROM `percona` LIKE 'checksums'
    # TableParser:4451 18067 DBD::mysql::db selectrow_arrayref failed: Unknown database 'percona' [for Statement "SHOW TABLES FROM `percona` LIKE 'checksums'"] at /usr/bin/pt-table-checksum line 4448.

    Thank you very much.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    It looks like it is complaining about a missing percona database. The issue is probably that your percona database is not getting replicated to the slave due to your replication filters. You could try to manually create the percona database on your slave, and then run it again and see if that works. If that does not work, then what you'll likely need to do is add "percona" to the list of databases to replicate with the replicate-do-db command in your my.cnf configuration file.

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    From Slave dsns table IP, I should put master IP? Because from master dsns I put slave IP
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    I able to run it with manually create the percona database on your slave (dsns put master IP) and add "percona" to the list of databases to replicate with the replicate-do-db command in my.cnf configuration file

    But here return me some errors:
    Below link is the txt file that I had save.
    https://drive.google.com/file/d/0B_1idIUKpJq_cHByeFd2VUU2Y3M/view?usp=sharing


    Below is 2 of the error return:
    06-09T19:24:50 Skipping table subs_270315_9nkd3.gl_gstreturntrack because it has problems on these replicas:
    Table subs_270315_9nkd3.gl_gstreturntrack does not exist on replica replication
    This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
    06-09T19:24:50 Error checksumming table subs_270315_9nkd3.gl_gstreturntrack: DBD::mysql::db selectrow_hashref failed: Table 'subs_270315_9nkd3.gl_gstreturntrack' doesn't exist [for Statement "EXPLAIN SELECT * FROM `subs_270315_9nkd3`.`gl_gstreturntrack` WHERE 1=1"] at /usr/bin/pt-table-checksum line 6521.

    06-09T19:24:59 Skipping table subs_270315_9nkd3.imp_gl_doubleentry because it has problems on these replicas:
    Table subs_270315_9nkd3.imp_gl_doubleentry on replica replication is missing these columns: projectcode, docdate
    This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
    06-09T19:24:59 Skipping table subs_270315_9nkd3.imp_gl_doubleentrykoff because it has problems on these replicas:
    Table subs_270315_9nkd3.imp_gl_doubleentrykoff does not exist on replica replication
    This can break replication. If you understand the risks, specify --no-check-slave-tables to disable this check.
    06-09T19:24:59 Error checksumming table subs_270315_9nkd3.imp_gl_doubleentrykoff: DBD::mysql::db selectrow_hashref failed: Table 'subs_270315_9nkd3.imp_gl_doubleentrykoff' doesn't exist [for Statement "EXPLAIN SELECT * FROM `subs_270315_9nkd3`.`imp_gl_doubleentrykoff` WHERE 1=1"] at /usr/bin/pt-table-checksum line 6521.
  • scott.nemesscott.nemes MySQL Sage Current User Role Patron
    Hi gpuser;

    Do those tables actually exist on the slave? Looks like it is saying one table has a different structure and the other just does not exist at all.

    -Scott
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    I checked the table is in Slave.
    From Slave dsns table IP, I should put master IP? Because from master dsns I put slave IP

    Any advise or how should I troubleshoot it?
  • gpusergpuser Contributor Inactive User Role Beginner
    Hi Scott,

    from the slave percona DB, checksum table do not record the table. But in the actual DB, the table is existed.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.