Refer to the data verification after mysql imports the sample database employees. You can use md5 or sha.
Principle and idea: First, generate each row and column of data in each table in the database to be backed up. The md5 value is calculated cumulatively, and then hardcoded in the test unit file as the expected value.
The following is the verification method of md5
USE employees; SELECT 'TESTING INSTALLATION' as 'INFO'; SET storage_engine=MyISAM; DROP TABLE IF EXISTS expected_values, found_values; CREATE TABLE expected_values ( table_name varchar(30) not null primary key, recs int not null, crc_sha varchar(100) not null, crc_md5 varchar(100) not null ) ENGINE=MyISAM; CREATE TABLE found_values LIKE expected_values; INSERT INTO `expected_values` VALUES ('employees', 300024,'4d4aa689914d8fd41db7e45c2168e7dcb9697359', '4ec56ab5ba37218d187cf6ab09ce1aa1'), ('departments', 9,'4b315afa0e35ca6649df897b958345bcb3d2b764', 'd1af5e170d2d1591d776d5638d71fc5f'), ('dept_manager', 24,'9687a7d6f93ca8847388a42a6d8d93982a841c6c', '8720e2f0853ac9096b689c14664f847e'), ('dept_emp', 331603, 'd95ab9fe07df0865f592574b3b33b9c741d9fd1b', # 'f16f6ce609d032d6b1b34748421e9195c5083da8', Bug#320513 'ccf6fe516f990bdaa49713fc478701b7'), # 'c2c4fc7f0506e50959a6c67ad55cac31'), ('titles', 443308,'d12d5f746b88f07e69b9e36675b6067abb01b60e', 'bfa016c472df68e70a03facafa1bc0a8'), ('salaries', 2844047,'b5a1785c27d75e33a4173aaa22ccf41ebd7d4a9f', 'fd220654e95aea1b169624ffe3fca934'); SELECT table_name, recs AS expected_records, crc_md5 AS expected_crc FROM expected_values; DROP TABLE IF EXISTS tchecksum; CREATE TABLE tchecksum (chk char(100)) ENGINE=myisam; SET @crc= ''; INSERT INTO tchecksum SELECT @crc := MD5(CONCAT_WS('#',@crc, emp_no,birth_date,first_name,last_name,gender,hire_date)) FROM employees ORDER BY emp_no; INSERT INTO found_values VALUES ('employees', (SELECT COUNT(*) FROM employees), @crc,@crc); TRUNCATE tchecksum; -- if BlackHole is not available SET @crc = ''; INSERT INTO tchecksum SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,dept_name)) FROM departments ORDER BY dept_no; INSERT INTO found_values values ('departments', (SELECT COUNT(*) FROM departments), @crc,@crc); TRUNCATE tchecksum; SET @crc = ''; INSERT INTO tchecksum SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,emp_no, from_date,to_date)) FROM dept_manager ORDER BY dept_no,emp_no; INSERT INTO found_values values ('dept_manager', (SELECT COUNT(*) FROM dept_manager), @crc,@crc); TRUNCATE tchecksum; SET @crc = ''; INSERT INTO tchecksum SELECT @crc := MD5(CONCAT_WS('#',@crc, dept_no,emp_no, from_date,to_date)) FROM dept_emp ORDER BY dept_no,emp_no; INSERT INTO found_values values ('dept_emp', (SELECT COUNT(*) FROM dept_emp), @crc,@crc); TRUNCATE tchecksum; SET @crc = ''; INSERT INTO tchecksum SELECT @crc := MD5(CONCAT_WS('#',@crc, emp_no, title, from_date,to_date)) FROM titles order by emp_no,title,from_date; INSERT INTO found_values values ('titles', (SELECT COUNT(*) FROM titles), @crc,@crc); TRUNCATE tchecksum; SET @crc = ''; INSERT INTO tchecksum SELECT @crc := MD5(CONCAT_WS('#',@crc, emp_no, salary, from_date,to_date)) FROM salaries order by emp_no,from_date,to_date; INSERT INTO found_values values ('salaries', (SELECT COUNT(*) FROM salaries), @crc,@crc); DROP TABLE tchecksum; SELECT table_name, recs as 'found_records ', crc_md5 as found_crc from found_values; SELECT e.table_name, IF(e.recs=f.recs,'OK', 'not ok') AS records_match, IF(e.crc_md5=f.crc_md5,'ok','not ok') AS crc_match from expected_values e INNER JOIN found_values f USING (table_name); DROP TABLE expected_values,found_values;
The above is the content of the verification program after mysql imports data. For more related content, please pay attention to the PHP Chinese website (www.php.cn)!