ä¹é´çæç« ããPercona-toolkitç³»åãPercona-toolkitå·¥å ·å
DBAç»å¸¸ä¼éå°éå®æ对æ°æ®è¿è¡å½æ¡£åæ¸ é¤ï¼å¯å©perconaçpt-archiverå·¥å ·è½å®æè¿ä¸åè½ï¼ä½¿å¾æ°æ®å½æ¡£åå¾æ¹ä¾¿ç®åãpt-archiverå¯ä»¥å¾è½»æ¾çå°ç产ç¯å¢çåå²æ°æ®å½æ¡£å°æ件æè ç´æ¥å é¤ï¼è¿å¯ä»¥ä¸å主æºé´åæ¥æ°æ®ï¼èä¸ç¨å°æ°æ®è½çï¼å®ç°çåè½æç¹ç±»ä¼¼Oracleçæ°æ®æ³µådblinkï¼pt-archiverä¸æ¬¾é常好ç¨çæ°æ®å½æ¡£åæ¸ çåå²æ°æ®çå·¥å ·ï¼å·¥ä½ä¸å¯ä»¥èµ·å°äºåååçææï¼
pt-archiver使ç¨çåºæ¯:
1ãæ¸ ç线ä¸è¿ææ°æ®
2ãæ¸ çè¿ææ°æ®ï¼å¹¶ææ°æ®å½æ¡£å°æ¬å°å½æ¡£è¡¨ä¸ï¼æè è¿ç«¯å½æ¡£æå¡å¨
3ãä¸¤å¼ è¡¨ä¹é´çæ°æ®ä¸å®å ¨ç¸åï¼å¸æå并ãæ¤æ¶å ä¸âignoreæâreplaceé项ï¼å¯ä»¥è½»æ¾å®ç°
4ã导åºçº¿ä¸æ°æ®ï¼å°çº¿ä¸æ°æ®ä½å¤ç
æµè¯ç¯å¢
MYSQL 5.7å¤å®ä¾ç¯å¢ï¼ç«¯å£åå«æ¯3306å3307
ä¸ãå°åå²æ°æ®å½æ¡£å°æ件ä¸
ç±äºå¨ä½¿ç¨pt-archiveråæ°æ®å½æ¡£åè¦ä¾èµäºperl-DBIåperl-DBD-MySQLå ï¼å¦åå°±ä¼åºç°å¦ä¸çæ¥éä¿¡æ¯ï¼æ以å¨å·¥å ·ä½¿ç¨åï¼å好å°ç¸å ³çä¾èµå å®è£ 好ï¼
[root@VM_54_118_centos ~]# pt-archiver --source h=localhost,u=root,p=XXXXXX,S=/tmp/mysql3306.sock,D=wjq,t=wjq_innodb_count1,A=utf8mb4 --file '/tmp/arch/%Y-%m-%d-%D.%t.arch' --where "1=1" --progress 5000 --statistics --limit=10000 --txn-size 1000 --no-delete
Cannot connect to MySQL because the Perl DBI module is not installed or not found. Run 'perl -MDBI' to see the directories that Perl searches for DBI. If DBI is not installed, try:
Debian/Ubuntu apt-get install libdbi-perl
RHEL/CentOS yum install perl-DBI
OpenSolaris pkg install pkg:/SUNWpmdbi
å¤å¶
解å³æ¹æ³ï¼
[root@VM_54_118_centos ~]# yum install perl-DBI
[root@VM_54_118_centos ~]# pt-archiver --source h=localhost,u=root,p=XXXXXX,S=/tmp/mysql3306.sock,D=wjq,t=wjq_innodb_count1,A=utf8mb4 --file '/tmp/arch/%Y-%m-%d-%D.%t.arch' --where "1=1" --progress 5000 --statistics --limit=10000 --txn-size 1000 --no-delete
Cannot connect to MySQL because the Perl DBD::mysql module is not installed or not found. Run 'perl -MDBD::mysql' to see the directories that Perl searches for DBD::mysql. If DBD::mysql is not installed, try:
Debian/Ubuntu apt-get install libdbd-mysql-perl
RHEL/CentOS yum install perl-DBD-MySQL
OpenSolaris pgk install pkg:/SUNWapu13dbd-mysql
å¤å¶
[root@VM_54_118_centos ~]# yum install perl-DBD-MySQL
å¤å¶
示ä¾ï¼
å°ç«¯å£ä¸º3306MYSQLå®ä¾ä¸wjqæ°æ®åºä¸wjq_innodb_count1表çæ°æ®å½æ¡£å°/tmp/archç®å½ä¸
[root@VM_54_118_centos ~]# pt-archiver --source h=localhost,u=root,p=XXXXXX,S=/tmp/mysql3306.sock,D=wjq,t=wjq_innodb_count1,A=utf8mb4 --file '/tmp/arch/%Y-%m-%d-%D.%t.arch' --where "1=1" --progress 5000 --statistics --limit=10000 --txn-size 1000 --no-delete
TIME ELAPSED COUNT
2019-03-09T16:26:49 0 0
2019-03-09T16:26:49 0 5000
2019-03-09T16:26:49 0 10000
2019-03-09T16:26:49 0 15000
2019-03-09T16:26:49 0 20000
2019-03-09T16:26:49 0 25000
2019-03-09T16:26:50 0 30000
2019-03-09T16:26:50 0 35000
2019-03-09T16:26:50 1 40000
2019-03-09T16:26:50 1 45000
2019-03-09T16:26:50 1 50000
2019-03-09T16:26:50 1 55000
2019-03-09T16:26:50 1 60000
2019-03-09T16:26:50 1 65000
2019-03-09T16:26:51 1 70000
2019-03-09T16:26:51 1 75000
2019-03-09T16:26:51 2 80000
2019-03-09T16:26:51 2 85000
2019-03-09T16:26:51 2 90000
2019-03-09T16:26:51 2 95000
2019-03-09T16:26:51 2 100000
2019-03-09T16:26:51 2 100001
Started at 2019-03-09T16:26:49, ended at 2019-03-09T16:26:51
Source: A=utf8mb4,D=wjq,S=/tmp/mysql3306.sock,h=localhost,p=...,t=wjq_innodb_count1,u=root
SELECT 100001
INSERT 0
DELETE 0
Action Count Time Pct
select 12 0.1744 6.59
commit 101 0.0048 0.18
print_file 100001 -0.0452 -1.71
other 0 2.5118 94.93
å¤å¶
[root@VM_54_118_centos ~]# ll /tmp/arch/
total 2340
-rw-r--r-- 1 root root 2388903 Mar 9 16:26 2019-03-09-wjq.wjq_innodb_count1.arch
å¤å¶
éè¿ä¸é¢ç»æå¯ä»¥çåºï¼æ们å°æ°æ®å¯¹ä¸å°äº2019-03-09-wjq.wjq_innodb_count1.archæ件ä¸ï¼
é对ä¸è¿°pt-archiverçç¸å ³åæ°ç®å解éä¸ä¸ï¼
âsourceï¼æå®ç®æ åºç¸å ³çä¿¡æ¯ï¼å¦æåªæ¯å°è¡¨å¤ä»½å°æ件ï¼åªéæå®sourceå³å¯ï¼å¦ææ¯ä¸å主æºä¹é´åæ¥æ°æ®çè¯ï¼è¿éè¦å¶å®âdest
âshare-lockï¼ç»è¡¨å ä¸LOCK IN SHARE MODE,æä¾è¯»ä¸è´æ§
hï¼ä¸»æºIP
Sï¼æ°æ®åºçsockæ件
uï¼æ°æ®åºç¨æ·
pï¼å¯ç
Dï¼æ°æ®åºå
tï¼è¡¨å
A:å符é
âno-deleteï¼å½æ¡£æ°æ®åï¼ä¸å é¤æºè¡¨æ°æ®(å¦æéè¦å é¤æºè¡¨æ°æ®,âno-deleteæ¹ä¸ºâpurgeå³å¯)
âfileï¼æ°æ®åæ¾çæ件ï¼æ好æå®ç»å¯¹è·¯å¾ï¼æ件åå¯ä»¥çµæ´»å°ç»åã
âwhereï¼å é¤è¡¨ä¸æå®çæ°æ®ï¼æ ¹æ®èªå·±çéæ±éå®ï¼å ¨é¨å é¤å°±ç»1=1å³å¯
âstatisticsï¼æå°åºæ´ä¸ªå½æ¡£è¿ç¨çç»è®¡ä¿¡æ¯
âlimitï¼æ¯æ¬¡fecthå¤å°è¡æ°æ®ï¼ç±»ä¼¼æ¸¸æ è·åï¼é»è®¤ä¸º1ãå¢æ¹è¯¥å¼ï¼æå©äºå éå½æ¡£
âprogressï¼æå°å¯¼åºè¿ç¨ä¸çä¿¡æ¯ï¼å½åæ¶é´ï¼å½åä¸å ±èè´¹å¤å°æ¶é´ï¼å½åfetchæ°æ®è¡æ°
âtxn-sizeï¼æ¯ä¸ªäºç©æ交çæ°æ®è¡æ°ï¼æ¹éæ交ãå¢å 该å¼å¯ä»¥æåå½æ¡£æ§è½ã
äºãä¸åå®ä¾ä¹é´åæ¥æ°æ®
使ç¨pt-archiverå¯ä»¥å°åå²æ°æ®æ¬å°å¤ä»½åºï¼æè å¨ä¸¤ä¸ªåºä¹é´å¨çº¿åæ¥æ°æ®ã
示ä¾
å°ç«¯å£3306çemployeesçemployees表ä¸çæ°æ®åæ¥å°ç«¯å£3307çwjqåºä¸çemployees表ä¸ï¼ä¸å é¤æºè¡¨æ°æ®ã
1ãæ¥ç表ç大å°ãè®°å½æ°
root@localhost [3306][employees]>show table status like 'employees'\G;
*************************** 1. row ***************************
Name: employees
Engine: InnoDB
Version: 10
Row_format: Dynamic
Rows: 299157
Avg_row_length: 50
Data_length: 15220736
Max_data_length: 0
Index_length: 0
Data_free: 4194304
Auto_increment: NULL
Create_time: 2018-11-24 20:39:12
Update_time: NULL
Check_time: NULL
Collation: utf8mb4_general_ci
Checksum: NULL
Create_options:
Comment:
1 row in set (0.00 sec)
å¤å¶
2ãæ¥çæºå®ä¾ç表ç»æ
root@localhost [3306][employees]>show create table employees\G;
*************************** 1. row ***************************
Table: employees
Create Table: CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
PRIMARY KEY (`emp_no`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
1 row in set (0.00 sec)
å¤å¶
3ãå¨ç®æ å®ä¾ä¸å建ç¸åç表ç»æï¼æ¤æ¶å¯ä»¥çå°ç®æ 表ä¸çæ°æ®æ¡æ°ä¸º0
root@localhost [3307][wjq]>select count(*) from employees;
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set (0.00 sec)
å¤å¶
4ãå°æ°æ®ä»æºå®ä¾è¡¨ä¸å¯¼å ¥å°ç®æ å®ä¾è¡¨ä¸
[root@VM_54_118_centos ~]# pt-archiver --source h=localhost,u=root,p=XXXXXX,S=/tmp/mysql3306.sock,D=employees,t=employees,A=utf8mb4 --dest h=localhost,S=/tmp/mysql3307.sock,D=wjq,t=employees --where "1=1" --progress 10000 --statistics --limit=20000 --txn-size 1000 --no-delete
TIME ELAPSED COUNT
2019-03-11T13:41:09 0 0
2019-03-11T13:41:11 1 10000
2019-03-11T13:41:12 3 20000
2019-03-11T13:41:13 4 30000
2019-03-11T13:41:15 5 40000
2019-03-11T13:41:16 7 50000
2019-03-11T13:41:18 8 60000
2019-03-11T13:41:19 10 70000
2019-03-11T13:41:21 11 80000
2019-03-11T13:41:22 13 90000
2019-03-11T13:41:24 14 100000
2019-03-11T13:41:25 16 110000
2019-03-11T13:41:27 17 120000
2019-03-11T13:41:28 19 130000
2019-03-11T13:41:29 20 140000
2019-03-11T13:41:31 21 150000
2019-03-11T13:41:32 23 160000
2019-03-11T13:41:34 24 170000
2019-03-11T13:41:35 26 180000
2019-03-11T13:41:36 27 190000
2019-03-11T13:41:38 28 200000
2019-03-11T13:41:39 30 210000
2019-03-11T13:41:41 31 220000
2019-03-11T13:41:42 33 230000
2019-03-11T13:41:43 34 240000
2019-03-11T13:41:45 35 250000
2019-03-11T13:41:46 37 260000
2019-03-11T13:41:48 38 270000
2019-03-11T13:41:49 40 280000
2019-03-11T13:41:51 41 290000
2019-03-11T13:41:52 43 300000
2019-03-11T13:41:52 43 300024
Started at 2019-03-11T13:41:09, ended at 2019-03-11T13:41:52
Source: A=utf8mb4,D=employees,S=/tmp/mysql3306.sock,h=localhost,p=...,t=employees,u=root
Dest: A=utf8mb4,D=wjq,S=/tmp/mysql3307.sock,h=localhost,p=...,t=employees,u=root
SELECT 300024
INSERT 300024
DELETE 0
Action Count Time Pct
inserting 300024 29.7888 69.23
commit 602 1.2850 2.99
select 17 0.2557 0.59
other 0 11.6980 27.19
å¤å¶
5ãåæ¥æååï¼å¯ä»¥çå°ï¼ç®æ å®ä¾è¡¨ä¸çæ°æ®æ¡æ°åæºå®ä¾çæ¡æ°ç¸å
root@localhost [3307][wjq]>select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.05 sec)
å¤å¶
ä¸ãæ¸ é¤è¡¨ä¸åå²æ°æ®
å¨ç产ç¯å¢ä¸ï¼åå²æ°æ®éè¦å®ææ¸ çï¼å¦åå¯è½ä¼å¯¼è´ç£çå ç¨é常ç大ï¼æ¤æ¶pt-archiver就派ä¸ç¨åºå¦ã对äºOLTPäºå¡æ§åºç¨ï¼å¦æä¸æ¬¡æ§å é¤å¤§éæ°æ®ï¼å¯è½ä¼äº§çè¾å¤§æ§è½å½±åï¼å¯ä»¥éæ©åæ¹å é¤ã
1ãå é¤ä¹åï¼è¡¨ä¸çæ°æ®æ¡æ°å¦ä¸
root@localhost [3307][wjq]>select count(*) from employees;
+----------+
| count(*) |
+----------+
| 300024 |
+----------+
1 row in set (0.06 sec)
å¤å¶
2ãå é¤emp_no<100000çæ°æ®
[root@VM_54_118_centos ~]# pt-archiver --source h=localhost,u=root,p=XXXXXX,S=/tmp/mysql3307.sock,D=wjq,t=employees,A=utf8mb4 --where "emp_no<100000" --progress 5000 --statistics --limit=10000 --txn-size 1000 --purge
TIME ELAPSED COUNT
2019-03-11T13:53:56 0 0
2019-03-11T13:53:57 0 5000
2019-03-11T13:53:57 1 10000
2019-03-11T13:53:58 2 15000
2019-03-11T13:53:59 3 20000
2019-03-11T13:54:00 3 25000
2019-03-11T13:54:01 4 30000
2019-03-11T13:54:01 5 35000
2019-03-11T13:54:02 6 40000
2019-03-11T13:54:03 7 45000
2019-03-11T13:54:04 7 50000
2019-03-11T13:54:05 8 55000
2019-03-11T13:54:05 9 60000
2019-03-11T13:54:06 10 65000
2019-03-11T13:54:07 10 70000
2019-03-11T13:54:08 11 75000
2019-03-11T13:54:08 12 80000
2019-03-11T13:54:09 13 85000
2019-03-11T13:54:10 14 89999
Started at 2019-03-11T13:53:56, ended at 2019-03-11T13:54:10
Source: A=utf8mb4,D=wjq,S=/tmp/mysql3307.sock,h=localhost,p=...,t=employees,u=root
SELECT 89999
INSERT 0
DELETE 89999
Action Count Time Pct
deleting 89999 9.9367 70.82
commit 90 0.5391 3.84
select 10 0.0795 0.57
other 0 3.4764 24.78
å¤å¶
3ãæ¥çå é¤åçæ°æ®æ¡æ°
root@localhost [3307][wjq]>select count(*) from employees;
+----------+
| count(*) |
+----------+
| 210025 |
+----------+
1 row in set (0.03 sec)
å¤å¶
pt-archiver使ç¨è¯¦ç»åèå®æ¹æ档说æï¼
https://www.percona.com/doc/percona-toolkit/3.0/pt-archiver.html