본문 바로가기
database/mysql

pt-table-checksum,sync 를 통한 mysql replication sync 체크 및 조절

cyuu 2016. 6. 5.
mysql replication sync 체크 및 조절

기본적으로 mysql replication 운영시 master 와slave (혹은 master) 간의 데이터의 sync 가 맞지 않거나 깨지는 경우가 있다. 이럴때 mysql 에서 기본적으로 제공하는 tool 이 아닌pt-table-checksum을 이용하여 checksum 을 비교 틀린 부분을 체크하고 틀린 부분에 대하여 pt-table-sync 를 이용하여 조절 할 수 있다.

* TSET환경             

hostname 

IP 

OS 

DB 

 

node202

192.168.110.202 

 centos 7.2 64bit

 mysql 5.6.30

 master node

node203 

192.168.110.203 

 centos 7.2 64bit 

 mysql 5.6.30 

 slave node 

* mater,slave replication 은 이전 포스팅 참고 


 # pt-table-checksum,sync를 사용 하기 위해서는
 # 아래 사이트에서 percona 에서 제공하는 percona toolkit 를 설치될 OS 버전에 따라서 다운 받는다 
 https://www.percona.com/doc/percona-toolkit/2.0/installation.html




  
[root@node202 ~]# wget percona.com/get/percona-toolkit.rpm
--2016-06-05 00:41:59--  http://percona.com/get/percona-toolkit.rpm
Resolving percona.com (percona.com)... 74.121.199.234
Connecting to percona.com (percona.com)|74.121.199.234|:80... connected.
HTTP request sent, awaiting response... 301 Moved Permanently
Location: https://www.percona.com/get/percona-toolkit.rpm [following]
--2016-06-05 00:42:00--  https://www.percona.com/get/percona-toolkit.rpm
Resolving www.percona.com (www.percona.com)... 74.121.199.234
Connecting to www.percona.com (www.percona.com)|74.121.199.234|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm [following]
--2016-06-05 00:42:01--  https://www.percona.com/downloads/percona-toolkit/2.2.16/RPM/percona-toolkit-2.2.16-1.noarch.rpm
Reusing existing connection to www.percona.com:443.
HTTP request sent, awaiting response... 200 OK
Length: 1714826 (1.6M) [application/x-redhat-package-manager]
Saving to: ‘percona-toolkit.rpm’

100%[=========================================================================================>] 1,714,826    212KB/s   in 13s   

2016-06-05 00:42:14 (129 KB/s) - ‘percona-toolkit.rpm’ saved [1714826/1714826]
## perl 관련 의존성 파일 다운 및 설치 진행 
[root@node202 ~]#  yum install perl-DBI perl-IO-Socket-SSL perl-DBD-mysql perl-Time-HiRes

[root@node202 ~]# wget http://pkgs.repoforge.org/perl-TermReadKey/perl-TermReadKey-2.30-3.el6.rfx.x86_64.rpm

[root@node202 ~]# rpm -ivh perl-TermReadKey-2.30-3.el6.rfx.x86_64.rpm
경고: perl-TermReadKey-2.30-3.el6.rfx.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 6b8d79e6: NOKEY
준비 중...                         ################################# [100%]
Updating / installing...
   1:perl-TermReadKey-2.30-3.el6.rfx  ################################# [100%]
## percona-toolkit 설치 및 확인 
[root@node202 ~]# rpm -ivh percona-toolkit.rpm
경고: percona-toolkit.rpm: Header V4 DSA/SHA1 Signature, key ID cd2efd2a: NOKEY
준비 중...                         ################################# [100%]
Updating / installing...
   1:percona-toolkit-2.2.16-1         ################################# [100%]
[root@node202 ~]# ls -al /usr/bin/pt-*
-rwxr-xr-x. 1 root root  40852 11월  7  2015 /usr/bin/pt-align
-rwxr-xr-x. 1 root root 261876 11월  7  2015 /usr/bin/pt-archiver
-rwxr-xr-x. 1 root root 168211 11월  7  2015 /usr/bin/pt-config-diff
-rwxr-xr-x. 1 root root 165435 11월  7  2015 /usr/bin/pt-deadlock-logger
-rwxr-xr-x. 1 root root 164145 11월  7  2015 /usr/bin/pt-diskstats
-rwxr-xr-x. 1 root root 168157 11월  7  2015 /usr/bin/pt-duplicate-key-checker
-rwxr-xr-x. 1 root root  49262 11월  7  2015 /usr/bin/pt-fifo-split
-rwxr-xr-x. 1 root root 148953 11월  7  2015 /usr/bin/pt-find
-rwxr-xr-x. 1 root root  66409 11월  7  2015 /usr/bin/pt-fingerprint
-rwxr-xr-x. 1 root root 132405 11월  7  2015 /usr/bin/pt-fk-error-logger
-rwxr-xr-x. 1 root root 191457 11월  7  2015 /usr/bin/pt-heartbeat
-rwxr-xr-x. 1 root root 225247 11월  7  2015 /usr/bin/pt-index-usage
-rwxr-xr-x. 1 root root  32345 11월  7  2015 /usr/bin/pt-ioprofile
-rwxr-xr-x. 1 root root 247940 11월  7  2015 /usr/bin/pt-kill
-rwxr-xr-x. 1 root root  21753 11월  7  2015 /usr/bin/pt-mext
-rwxr-xr-x. 1 root root 100986 11월  7  2015 /usr/bin/pt-mysql-summary
-rwxr-xr-x. 1 root root 383964 11월  7  2015 /usr/bin/pt-online-schema-change
-rwxr-xr-x. 1 root root  24594 11월  7  2015 /usr/bin/pt-pmp
-rwxr-xr-x. 1 root root 517540 11월  7  2015 /usr/bin/pt-query-digest
-rwxr-xr-x. 1 root root  72542 11월  7  2015 /usr/bin/pt-show-grants
-rwxr-xr-x. 1 root root  37724 11월  7  2015 /usr/bin/pt-sift
-rwxr-xr-x. 1 root root 144771 11월  7  2015 /usr/bin/pt-slave-delay
-rwxr-xr-x. 1 root root 126975 11월  7  2015 /usr/bin/pt-slave-find
-rwxr-xr-x. 1 root root 178774 11월  7  2015 /usr/bin/pt-slave-restart
-rwxr-xr-x. 1 root root  69559 11월  7  2015 /usr/bin/pt-stalk
-rwxr-xr-x. 1 root root  89868 11월  7  2015 /usr/bin/pt-summary
-rwxr-xr-x. 1 root root 418326 11월  7  2015 /usr/bin/pt-table-checksum
-rwxr-xr-x. 1 root root 395628 11월  7  2015 /usr/bin/pt-table-sync
-rwxr-xr-x. 1 root root 222599 11월  7  2015 /usr/bin/pt-table-usage
-rwxr-xr-x. 1 root root 329999 11월  7  2015 /usr/bin/pt-upgrade
-rwxr-xr-x. 1 root root 176246 11월  7  2015 /usr/bin/pt-variable-advisor
-rwxr-xr-x. 1 root root 101650 11월  7  2015 /usr/bin/pt-visual-explain
## mysql 쉘에 들어가서 tool-kit 이 접근할 계정을 생성 해준다. 기본적으로 replicawatch 라는 계정으로 운영되며, ## 이때 이계정은 SELECT, INSERT, UPDATE, DELETE, PROCESS 이라는 명령어를 사용 할 수 있도록 권한을 추가 해준다. 
[root@node202 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 65
Server version: 5.6.30-log Source distribution

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql> CREATE USER 'replicawatch'@'192.168.110.%' IDENTIFIED BY '12345';
GRANT CREATE, SELECT, INSERT, UPDATE, DELETE, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicawatch'@'192.168.110.%';
flush privileges;
Query OK, 0 rows affected (0.21 sec)

mysql> GRANT CREATE, SELECT, INSERT, UPDATE, DELETE, PROCESS, REPLICATION SLAVE, REPLICATION CLIENT ON *.* TO 'replicawatch'@'192.168.110.%';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
## pt-table-checksum 명령어를 통하여 h host 이름, u 유저 , p 비밀번호로 체크 를 한다. ## 이때 다른 DB와 틀린 부분이 있다면 DIFFS에서 해당 틀린 부분이 counting 된다. 
[root@node202 ~]# pt-table-checksum h=192.168.110.202,u=root,p='12345';
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 together with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/bin/pt-table-checksum line 331.
*******************************************************************
 Using the default of SSL_verify_mode of SSL_VERIFY_NONE for client
 is deprecated! Please set SSL_verify_mode to SSL_VERIFY_PEER
 together with SSL_ca_file|SSL_ca_path for verification.
 If you really don't want to verify the certificate and keep the
 connection open to Man-In-The-Middle attacks please set
 SSL_verify_mode explicitly to SSL_VERIFY_NONE in your application.
*******************************************************************
  at /usr/bin/pt-table-checksum line 331.
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
06-05T01:12:28      0      0      504       1       0   0.222 dummy.dummy
06-05T01:12:28      0      0        0       1       0   0.159 mysql.columns_priv
06-05T01:12:31      0      0        0       1       0   3.019 mysql.db
06-05T01:12:31      0      0        0       1       0   0.072 mysql.event
06-05T01:12:31      0      0        0       1       0   0.071 mysql.func
06-05T01:12:31      0      0       40       1       0   0.073 mysql.help_category
06-05T01:12:32      0      0      611       1       0   0.030 mysql.help_keyword
06-05T01:12:32      0      0     1218       1       0   0.052 mysql.help_relation
06-05T01:12:32      0      0      583       1       0   0.088 mysql.help_topic
06-05T01:12:32      0      0        0       1       0   0.084 mysql.ndb_binlog_index
06-05T01:12:32      0      0        0       1       0   0.054 mysql.plugin
06-05T01:12:32      0      0        0       1       0   0.039 mysql.proc
06-05T01:12:32      0      0        0       1       0   0.344 mysql.procs_priv
06-05T01:12:32      0      0        2       1       0   0.135 mysql.proxies_priv
06-05T01:12:32      0      0        0       1       0   0.033 mysql.servers
06-05T01:12:32      0      0        0       1       0   0.046 mysql.tables_priv
06-05T01:12:32      0      0        0       1       0   0.023 mysql.time_zone
06-05T01:12:33      0      0        0       1       0   0.075 mysql.time_zone_leap_second
06-05T01:12:33      0      0        0       1       0   0.036 mysql.time_zone_name
06-05T01:12:33      0      0        0       1       0   0.053 mysql.time_zone_transition
06-05T01:12:33      0      0        0       1       0   0.025 mysql.time_zone_transition_type
06-05T01:12:33      0      0        7       1       0   0.022 mysql.user
## 만약 slave 데이터가 틀려질 경우 아래와 같이 DIFF 에 틀린 갯수를 보여 준다. ## 이러한 경우 pt-table-sync 를 통하여 sync 를 맞추게 된다.(https://www.percona.com/doc/percona-toolkit/2.0/pt-table-sync.html) 
[root@node202 ~]# pt-table-checksum h=192.168.110.202,u=root,p='12345';
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
06-05T01:15:41      0      1      504       1       0   0.091 dummy.dummy
06-05T01:15:41      0      0        0       1       0   0.309 mysql.columns_priv
06-05T01:15:41      0      0        0       1       0   0.049 mysql.db
06-05T01:15:41      0      0        0       1       0   0.036 mysql.event
06-05T01:15:41      0      0        0       1       0   0.072 mysql.func
06-05T01:15:42      0      0       40       1       0   0.089 mysql.help_category
06-05T01:15:42      0      0      611       1       0   0.020 mysql.help_keyword
06-05T01:15:42      0      0     1218       1       0   0.023 mysql.help_relation
06-05T01:15:42      0      0      583       1       0   0.046 mysql.help_topic
06-05T01:15:42      0      0        0       1       0   0.023 mysql.ndb_binlog_index
06-05T01:15:42      0      0        0       1       0   0.028 mysql.plugin
06-05T01:15:42      0      0        0       1       0   0.025 mysql.proc
06-05T01:15:42      0      0        0       1       0   0.064 mysql.procs_priv
06-05T01:15:42      0      0        2       1       0   0.017 mysql.proxies_priv
06-05T01:15:42      0      0        0       1       0   0.020 mysql.servers
06-05T01:15:42      0      0        0       1       0   0.037 mysql.tables_priv
06-05T01:15:42      0      0        0       1       0   0.018 mysql.time_zone
06-05T01:15:42      0      0        0       1       0   0.053 mysql.time_zone_leap_second
06-05T01:15:42      0      0        0       1       0   0.017 mysql.time_zone_name
06-05T01:15:42      0      0        0       1       0   0.019 mysql.time_zone_transition
06-05T01:15:42      0      0        0       1       0   0.022 mysql.time_zone_transition_type
06-05T01:15:42      0      0        7       1       0   0.021 mysql.user
## node202(master) 에 데이터 기준으로 node203 의 slave 의 다른부분을 변경 하기 위해서 아래와 같이 진행 한다. ## 아래와 같은 방법은 전체 데이터 베이스 기준이며, 추가적으로 -D데이터 베이스 , -t 테이블 기준으로 디테일 한 sync조절 도 가능 하다. 
[root@node202 ~]# pt-table-sync  --execute h=node202,u=root,p=12345 h=node203,u=root,p=12345
## pt-table-sync 이후 정상적으로 sync가 맞는것을 확인 할 수 있다. 
[root@node202 ~]# pt-table-checksum h=192.168.110.202,u=root,p='12345';                     
            TS ERRORS  DIFFS     ROWS  CHUNKS SKIPPED    TIME TABLE
06-05T01:41:53      0      0      504       1       0   0.796 dummy.dummy
06-05T01:41:53      0      0        0       1       0   0.042 mysql.columns_priv
06-05T01:41:53      0      0        0       1       0   0.021 mysql.db
06-05T01:41:53      0      0        0       1       0   0.021 mysql.event
06-05T01:41:53      0      0        0       1       0   0.036 mysql.func
06-05T01:41:53      0      0       40       1       0   0.020 mysql.help_category
06-05T01:41:53      0      0      611       1       0   0.022 mysql.help_keyword
06-05T01:41:53      0      0     1218       1       0   0.019 mysql.help_relation
06-05T01:41:53      0      0      583       1       0   0.018 mysql.help_topic
06-05T01:41:54      0      0        0       1       0   0.020 mysql.ndb_binlog_index
06-05T01:41:54      0      0        0       1       0   0.020 mysql.plugin
06-05T01:41:54      0      0        0       1       0   0.050 mysql.proc
06-05T01:41:54      0      0        0       1       0   0.018 mysql.procs_priv
06-05T01:41:54      0      0        2       1       0   0.018 mysql.proxies_priv
06-05T01:41:54      0      0        0       1       0   0.020 mysql.servers
06-05T01:41:54      0      0        0       1       0   0.021 mysql.tables_priv
06-05T01:41:54      0      0        0       1       0   0.019 mysql.time_zone
06-05T01:41:54      0      0        0       1       0   0.019 mysql.time_zone_leap_second
06-05T01:41:54      0      0        0       1       0   0.018 mysql.time_zone_name
06-05T01:41:54      0      0        0       1       0   0.022 mysql.time_zone_transition
06-05T01:41:54      0      0        0       1       0   0.021 mysql.time_zone_transition_type
06-05T01:41:54      0      0        7       1       0   0.020 mysql.user


반응형
저작자표시

'database > mysql' 카테고리의 다른 글

Mysq-5.6 MHA 구성  (0) 2016.08.15
Mysql 5.7 replication (Master-Slave) on Centos7  (0) 2016.06.04
centos7 mysql 5.6 install  (0) 2016.06.04
mysqldump 를 통한 mysql 백업,복구  (0) 2016.05.23
XtraBackup install ,full backup  (0) 2016.05.14

'database/mysql' 관련글

티스토리툴바