pt-table-sync工具的使用(一)
一、MySQL8.0 gtid主从同步搭建步骤
1.二进制安装
0）、修改主机名，配置hosts
hostnamectl set-hostname node1
hostnamectl set-hostname node2
127.0.0.1 localhost localhost.localdomain localhost4 localhost4.localdomain4
::1 localhost localhost.localdomain localhost6 localhost6.localdomain6
192.168.100.202 node1
192.168.100.203 node2
1）、创建操作系统用户：
groupadd mysql
useradd -g mysql mysql
2）、解压二进制包，建立软链接
cd /usr/local
tar xvf mysql-8.0.25-linux-glibc2.12-x86_64.tar.xz
ln -s mysql-8.0.25-linux-glibc2.12-x86_64 mysql
3）、修改配置文件my.cnf
主：192.168.100.202
[client]
socket=/usr/local/mysql/data/mysql.sock
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
port=3307
user=mysql
log_timestamps=system
default_authentication_plugin = mysql_native_password
log-bin=mysql-bin
server-id=2023307
binlog_format=STATEMENT
gtid-mode=on
enforce-gtid-consistency=1
从1：192.168.100.203
[client]
socket=/usr/local/mysql/data/mysql.sock
[mysqld]
basedir=/usr/local/mysql
datadir=/usr/local/mysql/data
socket=/usr/local/mysql/data/mysql.sock
log-error=/usr/local/mysql/data/mysqld.err
port=3307
user=mysql
log_timestamps=system
default_authentication_plugin = mysql_native_password
log-bin=mysql-bin
server-id=2033307
binlog_format=STATEMENT
gtid-mode=on
enforce-gtid-consistency=1
4）、创建数据目录，并修改其属主、属组。
mkdir -p /usr/local/mysql/data
chown mysql.mysql /usr/local/mysql/data
4.1）、配置互信，每台都要做
ssh-keygen -t rsa
ssh-copy-id 192.168.100.202
ssh-copy-id 192.168.100.203
4.3、关闭防火墙以及selinux
systemctl stop firewalld.service
systemctl disable firewalld.service
systemctl status firewalld.service
vim /etc/selinux/config
SELINUX=disabled
setenforce 0
5）、初始化实例：
/usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize
6）、启动实例
/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
7）、登录实例（使用初始化过程中生成的随机密码）
grep 'temporary password' mysqld.err
8）、配置mysql环境变量
echo "PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH" >> /etc/profile
echo "export PATH" >> /etc/profile
source /etc/profile
9）、修改root用户密码
mysql -uroot -p'>H0VQ;ZEm:FK';
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
create user root@'%' IDENTIFIED WITH mysql_native_password BY '123456';
10）、创建同步用户repl以及赋权
create user 'repl'@'%' identified with mysql_native_password by '123456';
GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%';
flush privileges;
11）、验证用户远程登录是否成功
mysql -urepl -p123456 -h192.168.100.202 -P 3307
12）、检查同步用户权限
show grants for repl@'%'\G
13)、主库(192.168.100.202)进行全库备份，并传输到备库（192.168.100.203），同时备库导入全库备份
mysqldump -uroot -p'Password123' --single-transaction --master-data=2 -E -R --triggers -A > full_bakcup.sql
[root@node1 ~]# scp -r full_bakcup.sql root@192.168.100.203:/root
导入报错：
ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'WARNING: --master-data is deprecated and will be removed in a future version. Us' at line 1
删除第一行：WARNING
vim full_bakcup.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
备库进行全库导入
[root@node2 ~]# mysql -uroot -pPassword123 < full_bakcup.sql
14）、搭建MySQL8.0gtid主从同步
主：192.168.100.202
mysql> show master status \G;
*************************** 1. row ***************************
File: mysql-bin.000001
Position: 156
Binlog_Do_DB:
Binlog_Ignore_DB:
Executed_Gtid_Set:
1 row in set (0.00 sec)
ERROR:
No query specified
从1：192.168.100.203
change master to master_host="192.168.100.202",master_user="repl",master_password="123456",master_port=3307,master_auto_position=1;
start slave;
show slave status \G;
mysql> change master to master_host="192.168.100.202",master_user="repl",master_password="123456",master_port=3307,master_auto_position=1;
Query OK, 0 rows affected, 8 warnings (0.06 sec)
mysql>
mysql>
mysql> start slave;
Query OK, 0 rows affected, 1 warning (0.04 sec)
mysql> show slave status \G;
*************************** 1. row ***************************
Slave_IO_State: Waiting for master to send event
Master_Host: 192.168.100.202
Master_User: repl
Master_Port: 3307
Connect_Retry: 60
Master_Log_File: mysql-bin.000001
Read_Master_Log_Pos: 156
Relay_Log_File: node2-relay-bin.000002
Relay_Log_Pos: 371
Relay_Master_Log_File: mysql-bin.000001
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Exec_Master_Log_Pos: 156
Relay_Log_Space: 580
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 663307
Master_UUID: fbf4026c-5572-11ee-a740-000c29db9875
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set:
Executed_Gtid_Set: fd29ee69-b82f-11ee-9515-000c296d8999:1-7
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
Master_public_key_path:
Get_master_public_key: 0
Network_Namespace:
1 row in set, 1 warning (0.00 sec)
ERROR:
No query specified
15）、如主从环境搭建失败备库清理步骤
主：
reset master;
show master status \G;
从：
stop slave;
reset slave all;
show slave status \G;
以上为测试环境实验，生产环境谨慎操作。
二、环境初始化
1.测试数据下载地址：https://github.com/datacharmer/test_db
2.Download ZIP包 test_db-master.zip，并上传到服务器中
3.解压
unzip test_db-master.zip
4.执行导入命令
mysql -uroot -pPassword123 < employees.sql
mysql -uroot -pPassword123 < employees_partitioned.sql
mysql -uroot -pPassword123 -t < test_employees_md5.sql
[root@node1 test_db-master]# mysql -uroot -pPassword123 -t < test_employees_md5.sql
mysql: [Warning] Using a password on the command line interface can be insecure.
+----------------------+
| INFO |
+----------------------+
| TESTING INSTALLATION |
+----------------------+
+--------------+------------------+----------------------------------+
| table_name | expected_records | expected_crc |
+--------------+------------------+----------------------------------+
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
+--------------+------------------+----------------------------------+
+--------------+------------------+----------------------------------+
| table_name | found_records | found_crc |
+--------------+------------------+----------------------------------+
| departments | 9 | d1af5e170d2d1591d776d5638d71fc5f |
| dept_emp | 331603 | ccf6fe516f990bdaa49713fc478701b7 |
| dept_manager | 24 | 8720e2f0853ac9096b689c14664f847e |
| employees | 300024 | 4ec56ab5ba37218d187cf6ab09ce1aa1 |
| salaries | 2844047 | fd220654e95aea1b169624ffe3fca934 |
| titles | 443308 | bfa016c472df68e70a03facafa1bc0a8 |
+--------------+------------------+----------------------------------+
+--------------+---------------+-----------+
| table_name | records_match | crc_match |
+--------------+---------------+-----------+
| departments | OK | ok |
| dept_emp | OK | ok |
| dept_manager | OK | ok |
| employees | OK | ok |
| salaries | OK | ok |
| titles | OK | ok |
+--------------+---------------+-----------+
+------------------+
| computation_time |
+------------------+
| 00:00:48 |
+------------------+
+---------+--------+
| summary | result |
+---------+--------+
| CRC | OK |
| count | OK |
+---------+--------+
5 percona-toolkit安装
5.1 二进制包下载地址
https://downloads.percona.com/downloads/percona-toolkit/3.6.0/binary/tarball/percona-toolkit-3.6.0_x86_64.tar.gz
5.2 用户手册下载地址
https://learn.percona.com/download-percona-toolkit-3-0-manual
5.3 安装依赖包
yum install perl perl-DBI perl-DBD-MySQL perl-IO-Socket-SSL perl-Time-HiRes perl-devel perl-Digest-MD5 -y
5.4 安装percona-toolkit工具包
[root@node1 software]# tar -zxvf percona-toolkit-3.6.0_x86_64.tar.gz
5.5 配置环境变量
vim /etc/profile
export PATH=$PATH:/root/percona-toolkit-3.6.0/bin
source /etc/profile
[root@node1 ~]# pt-summary --version
pt-summary 3.6.0
三 pt-table-sync工具的使用
3.1 主库，备库创建admin用户
主库：192.168.100.202
create user 'admin'@'%' identified with mysql_native_password by '123456';
GRANT all privileges ON *.* TO 'admin'@'%';
flush privileges;
show grants for 'admin'@'%'\G
mysql -uadmin -p123456 -h192.168.100.202 -P3307
备库：192.168.100.203
create user 'admin'@'%' identified with mysql_native_password by '123456';
GRANT all privileges ON *.* TO 'admin'@'%';
flush privileges;
show grants for 'admin'@'%'\G
mysql -uadmin -p123456 -h192.168.100.203 -P3307
3.2 主库创建测试表employees_ptsync
表employees_ptsync信息
mysql admin@192.168.100.202:employees> create table employees_ptsync as select * from employees;
Query OK, 300024 rows affected (2.48 sec)
Records: 300024 Duplicates: 0 Warnings: 0
添加主键
mysql admin@192.168.100.202:employees> alter table employees_ptsync add primary key(emp_no);
Query OK, 0 rows affected
Time: 2.537s
mysql admin@192.168.100.202:employees> show create table employees_ptsync;
+------------------+--------------------------------------+
| Table | Create Table |
+------------------+--------------------------------------+
| employees_ptsync | CREATE TABLE `employees_ptsync` ( |
| | `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 |
| | ) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+------------------+--------------------------------------+
1 row in set
Time: 0.025s
3.3 从库更新部分数据，使得主从出现数据不一致
mysql admin@192.168.100.203:employees> set sql_log_bin = 0;
Query OK, 0 rows affected
Time: 0.002s
mysql admin@192.168.100.203:employees> update employees_ptsync set first_name = 'Georgi_ptsync' where first_name = 'Georgi';
Query OK, 252 rows affected
Time: 0.248s
mysql admin@192.168.100.203:employees> set sql_log_bin = 1;
Query OK, 0 rows affected
Time: 0.002s
3.4 从库执行pt-table-sync命令
只有1个DSN主机,只指定选项--sync-to-master
因为只有1个DSN主机并且指定了选项--sync-to-master，则DSN主机对应为从库的连接串，先使用选项--dry-run查看执行信息。
从库执行：
# pt-table-sync h=192.168.100.203,P=3307,u=admin -- --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --dry-run
Enter password for 192.168.100.203:
# A software update is available:
# NOTE: --dry-run does not show if data needs to be synced because it
# does not access, compare or sync data. --dry-run only shows
# the work that would be done.
# Syncing A=utf8,P=3307,h=192.168.100.203,p=...,u=admin in dry-run mode, without accessing or comparing data
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 0 0 0 Chunk 23:26:30 23:26:30 0 employees.employees_ptsync
从以上输出信息可得知并无报错信息，但因为使用了选项--dry-run，所以工具并不会进行校验和同步操作。
将选项--dry-run换成选项--execute再次执行。
[root@node2 bin]# pt-table-sync h=192.168.100.203,P=3307,u=admin --charset=utf8 --ask-pass --databases=employees --tables=employees_ptsync --sync-to-master --transaction --verbose --execute
Enter password for 192.168.100.203:
# Syncing A=utf8,P=3307,h=192.168.100.203,p=...,u=admin
# DELETE REPLACE INSERT UPDATE ALGORITHM START END EXIT DATABASE.TABLE
# 0 253 0 0 Chunk 23:28:57 23:29:59 2 employees.employees_ptsync
可以看出在REPLACE字段变更了253行记录，跟之前构建从库的数据行数一致，可以查询从库刚才表更新数据的情况进行验证：
3.5 查询修复从库employees_ptsync表之前的数据量
mysql admin@192.168.100.203:employees> select count(*) from employees_ptsync where first_name = 'Georgi_ptsync';
+----------+
| count(*) |
+----------+
| 0 |
+----------+
1 row in set
Time: 0.197s
