Data Consistency Verification for data migration and data migration
It is useful to perform some necessary verification during database migration, for example, whether the number of data items before and after migration is consistent or whether the data is consistent. What should I do at this time? The number of verification items is okay, if the data is verified to be consistent, of course there will be no errors for each piece of important data, and random sampling verification will certainly not work. In case of omission, it will be troublesome, in addition, the two tables are no longer on the same server. What should we do at this time? There is one way:
The above method was developed at the same time, but it is also good, but I think there is room for improvement:
- First, it is not all fields, and different may still appear in non-main fields.
- Low overall efficiency
My idea is as follows:
The advantage of the second method is that the output file will be reduced to a certain extent, which is easier to compare, but it also has some disadvantages. You cannot directly locate different data locations through the keyword segment like the first method.
The following describes the effects and code implementation of the second method:
<? Php/*** usage: * php-f mysql_diff.php yes dir 10 * Whether to calculate the number of items and output d5 and save it to the file merging data level **/if (php_sapi_name ()! = 'Cli ') {die ("Run in cli mode");} array_shift ($ argv); if (empty ($ argv )) {die ("at letase contain one info") ;}$ is_count = array_shift ($ argv); $ is_md5 = empty ($ argv )? False: array_shift ($ argv); $ conbine_num = empty ($ argv )? 1: intval (array_shift ($ argv); if ($ is_md5 &&! Is_dir ($ is_md5 )&&! Mkdir ($ is_md5, 777, true) {die ("error info: md5 info must be input to a file ");} $ dbinfos = array ('host' => 'localhost', 'Port' => '123', 'user' => 'root ', 'pswd '=> '123', 'charset' => 'utf8', 'tables' => array ('lagou. pos ', 'lagou. pos_innodb',),); // verify the format if (! $ Link = mysql_connect ($ dbinfos ['host']. ":". $ dbinfos ['Port'], $ dbinfos ['user'], $ dbinfos ['pswd ']) {die ("connect to [{$ host} @ {$ port}] failed !! ");} If (! Mysql_query ("set names {$ dbinfos ['charset']}") {die ("set charset error :". mysql_error ();} foreach ($ dbinfos ['table'] as $ table) {if ($ is_count) {$ SQL = "select count (*) as nums from {$ table} "; $ ret = mysql_query ($ SQL); if (! $ Ret) {die ("error :". mysql_error ();} $ ret = mysql_fetch_array ($ ret, MYSQL_ASSOC); echo "{$ table }:{ $ ret ['nums']} \ n ";} if ($ is_md5) {$ path = $ is_md5.DIRECTORY_SEPARATOR. $ table; $ SQL = "select * from {$ table}"; $ ret = mysql_query ($ SQL); $ flag = 0; $ fields = ''; while ($ _ ret = mysql_fetch_array ($ ret, MYSQL_NUM) {$ flag ++; while ($ _ ret) {$ fields. = array_pop ($ _ ret);} if ($ flag % $ conbine_num = 0) {file_put_contents ($ Path, md5 ($ fields). "\ n", FILE_APPEND); $ fields = '';}} if ($ flag % $ conbine_num! = 0 & $ flag> 0) {file_put_contents ($ path, md5 ($ fields ). "\ n", FILE_APPEND);} echo "save to file info :". realpath ($ path ). "\ n ";}}