MasterDB / SlaveDB 간 Consistency 확인하기 (pt-table-checksum, md5, diff)

http://cafe.naver.com/mysqlpg

Consistency.txt

pt-table-checksum.txt

MySQL은 MasterDB / SlaveDB 간

최고의 유연성을 보장한다는 장점이 있는 반면,  이 때문에 다소의 정합성이 깨질 수 있다는 단점을 가지고 있습니다.

이에 정합성이 맞는지 수시로 확인을 해야하는데요, 이와 관련된 내용이 있어 공유드립니다.

 

출처 : Oracle Press - Effective MySQL Backup and Recovery (p.68~)

 

=========================================================
Data Consistency : pt-data-checksum
=========================================================

[각 노드에서 percona-toolkit 설치]

sudo su -

yum install -y percona-toolkit

 

[MasterDB]

sudo su - mysql

 

cd /usr1/mysql/dba/tools

 

pt-table-checksum --host localhost --user root --ask-pass --replicate=zzdba.pt_checksum --databases=globaldb,gamedb,logdb --create-replicate-table --no-check-binlog-format --nocheck-replication-filters

 

------------------------------------------------------------------------------------------------------------
Waiting for the --replicate table to replicate to slavedb-s...
Waiting for the --replicate table to replicate to slavedb-s...
Waiting for the --replicate table to replicate to slavedb-s...

 

Checksumming globaldb.achievements:  55% 00:24 remain

            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
02-07T18:24:06      0      0  1032199      26       0  37.582 globaldb.achievements
02-07T18:24:06      0      0      670       1       0   0.022 globaldb.achievementsdata
02-07T18:24:20      0      0  1032184      25       0  13.456 globaldb.achievementsreward
02-07T18:24:20      0      0        1       1       0   0.261 globaldb.boss
02-07T18:24:20      0      0       15       1       0   0.009 globaldb.bossattack
02-07T18:24:26      0      0  1582272      10       0   6.235 globaldb.chattingdfield
02-07T18:24:32      0      0  1539837      10       0   6.172 globaldb.chattingnotify
...
# ------------------------------------------------------------------------------------------------------------
# --ask-pass                        : 암호를 입력받을 수 있게 (ask password)
# --replicate=zzdba.pt_checksum     : 체크섬 데이터를 저장할 테이블명
# --create-replicate-table          : 체크섬 테이블이 없다면 만들어서
# --databases=globaldb,gamedb,logdb : 체크할 데이터베이스명
# --no-check-binlog-format          : binlog-format='STATEMENT' 가 아니면 에러가 나는데, 이를 무시하라 (우리는 MIXED 이기 때문에 사용해야함)
# --nocheck-replication-filters     : 복제되지않는 DB들 체크하지 않아도 됨
# ------------------------------------------------------------------------------------------------------------
# Waiting for the --replicate table to replicate to ld-db-s... 
#   : Slave DB 에 체크섬 테이블(zzdba.pt_checksum) 이 만들어지지 않으면 위의 오류 발생
#     만일 2~3번까지도 waiting 된다면 slave node 에 replication 에 문제가 없는지 확인 필요 (slavedb : show slave status\G)
# ------------------------------------------------------------------------------------------------------------

[SlaveDB]

sudo su - mysql

cd /usr1/mysql/dba/tools

 

mysql -u root -p

 

SELECT db, tbl, chunk
     , IF(this_crc <> master_crc OR ISNULL(master_crc) <> ISNULL(this_crc), 'YES', 'NO') AS diff
     , this_cnt-master_cnt AS rowcount_diff
  FROM zzdba.pt_checksum
 WHERE master_cnt <> this_cnt
    OR master_crc <> this_crc
    OR ISNULL(master_crc ) <> ISNULL(this_crc);

 

+-----------+-------------------+-------+------+---------------+
| db        | tbl               | chunk | diff | rowcount_diff |
+-----------+-------------------+-------+------+---------------+
| globaldb  | log_ranking_cache |     1 | YES  |            -1 |
+-----------+-------------------+-------+------+---------------+

 

=========================================================
Object Consistency : md5(), length(), diff -u
=========================================================

sudo su - mysql

mysql -u root -p

 

use zzdba;

 

create view dba_checksum_proc
as
select routine_schema,routine_name, routine_type

     , length (routine_definition) as size, md5(routine_definition) as checksum
  from information_schema.routines
 where ROUTINE_SCHEMA not in ('bmt_schema2a','common_schema','ps_helper','test','mysql','information_schema')
 order by routine_schema,routine_name, routine_type;

 

cd /usr1/mysql/dba/monitor

 

mysql -uroot -p -h master_host -e "set sql_log_bin=0; select * from zzdba.dba_checksum_proc" > zztmp_object_master.log

 

mysql -uroot -p -h slave_host  -e "set sql_log_bin=0; select * from zzdba.dba_checksum_proc" > zztmp_object_slave.log

 

diff -u zztmp_object_master.log zztmp_object_slave.log

 

=========================================================
Schema Consistency : mysqldump, diff -u
=========================================================

The layman's approach to detecting schema differences is to use the mysqldump utility

and to generate the schema only and compare.

 

cd /usr1/mysql/dba/monitor

 

mysqldump --all-databases --no-data --skip-dump-date -uroot -p -h ld-db-m   | sed -e "s/AUTO_INCREMENT=[^\ ] //" > zztmp_schema_master.log

 

mysqldump --all-databases --no-data --skip-dump-date -uroot -p -h localhost | sed -e "s/AUTO_INCREMENT=[^\ ] //" > zztmp_schema_slave.log

 

diff -u zztmp_schema_master.log zztmp_schema_slave.log

 

This process is not ideal as there is no guarantee the output is ordered, and the format does differ between MySQL versions; however,this process can be used to confirm that no schema differences exist.  ^^;

 

=======================================================================
참고. pt-table-checksum 소스버젼으로 실행하면서 에러 발생시!
=======================================================================
erroe : 02-07T08:43:57 Cannot read response; is Term::ReadKey installed?
        Can't locate Term/ReadKey.pm in @INC (@INC contains: /usr/local/lib64/perl5 /usr/local/share/perl5 /usr/lib64/perl5/vendor_perl
        /usr/share/perl5/vendor_perl /usr/lib64/perl5 /usr/share/perl5 .) at ./pt-table-checksum line 2629.
=======================================================================

yum install perl-CPAN yaml

cpan YAML

perl -MCPAN -e shell

install Term::ReadKey

[출처] MasterDB / SlaveDB 간 Consistency 확인하기 (pt-table-checksum, md5, diff) (MySQL Power Group) |작성자 준경대디