Shulou(Shulou.com)06/01 Report--

The following brings you the tools used to check whether MySQL data is consistent. I believe you must have seen similar articles. What's the difference in what we bring to you? Let's take a look at the body part. I believe that after reading the tools used to check whether MySQL data is consistent, you will definitely gain something.

MySQL checksum command

When executing checksum command, the table will be added with a read lock (read lock), checksum table principle is to the data in the table row by row comparison and calculation, so for large tables, this is a very time-consuming process.

If you add CHECKSUM=1 to the myisam table, it will be very fast to checksum such a table

checksum table test.tbname;

root@test09:43:36>checksum table test.tbname;

+-------------+------------+

| Table | Checksum |

+-------------+------------+

| test.tbname | 1105600045 |

+-------------+------------+

1 row in set (0.00 sec)

See MySQL source code for specific logic (MySQL 5.7.17):

sql/sql_table.cc

bool mysql_checksum_table(THD thd, TABLE_LIST tables,HA_CHECK_OPT *check_opt)

2、mysqldiff

mysqldiff is a script from the official mysql-utilities toolset that can be used to compare table structures between databases or within the same database.

Download Address:

https://dev.mysql.com/downloads/utilities/

Example 1: Check whether a table structure on server1 and server2 is consistent.

mysqldiff --server1=user:pwd@host:port --server2=user:pwd@host:port --changes-for=server2 dbname.tablename:dbname.tablename

Example 2: Check whether the table structures in the two databases on server1 and server2 are consistent.

mysqldiff --server1=user:pwd@host:port --server2=user:pwd@host:port dbname:dbname --changes-for=server2

3、mysqldbcompare

mysqldiff This tool is a script of the official mysql-utilities toolset, which can be used to check data consistency between different databases, including database character sets, table structures, and data content. As long as there is a difference, the check does not pass.

Examples:

mysqldbcompare --server1=user:pwd@192.168.56.110:3306 --server2=user:pwd@192.168.56.111:3306 --changes-for=server1 dbname:dbname

4、pt-table-checksum

pt-table-checksum is an online master-slave data consistency checking tool, which can efficiently check the consistency of master-slave data for databases with large data volume, automatically control the size of the checked data volume, and avoid causing great impact on online services. The following shows several common uses of the tool, and more details can be found in the pt-table-checksum official help documentation.

Example 1: Checking consistency of a particular table

pt-table-checksum --host='192.168.56.110' --user='admin' --password='pwd' --port=3306 --recursion-method=processlist --replicate=percona.checksums --no-check-binlog-format --chunk-time=0.5 --tables='dbname.tb1,dbname2.tb2'

Example 2: Checking the consistency of a specific database

pt-table-checksum --host='192.168.56.110' --user='admin' --password='pwd' --port=3306 --recursion-method=processlist --replicate=percona.checksums --no-check-binlog-format --chunk-time=0.5 --databases='dbname1,dbname2'

Example 3: Checking consistency of all databases

pt-table-checksum --host='192.168.56.110' --user='admin' --password='pwd' --port=3306 --recursion-method=processlist --replicate=percona.checksums --no-check-binlog-format --chunk-time=0.5

For the tools used above to check whether MySQL data is consistent, do you think it is what you want? If you want to know more about it, you can continue to pay attention to our industry information section.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.