Percona Toolkit
Experimenting each percona toolkit tools to efficiently replicate from the master to slave server
1. Prerequestics
Test Database
- Download sakila db
https://dev.mysql.com/doc/index-other.html
- Extract the installation archive to a temporary location
- Connect to the MySQL server
mysql -uroot -p
- Execute the sakila-schema.sql script to create the database structure, and execute the sakila-data.sql script
SOURCE /home/<usr>/Downloads/sakila-db/sakila-schema.sql; SOURCE /home/<usr>/Downloads/sakila-db/sakila-data.sql;
2. pt-archive
In this test case, data exchange from one table to another in the same db on the same server
- Connect to the MySQL server
mysql -uroot -p
- Create a new table
USE sakila; CREATE TABLE old_rental LIKE rental; SELECT COUNT(*) FROM rental WHERE rental_date < "2006-01-01"; SELECT * FROM old_rental;
- Locate pt-archiver
find / -name pt-archiver 2>/dev/null
- This will show the location of the pt-archiver
/usr/bin/pt-archiver
- using pt-archiver command
/usr/bin/pt-archiver --source D=sakila,t=rental --dest D=sakila,t=old_rental --where "rental_date < '2006-01-01'" --statistics --user <user> --password <password> --host <host>
- user and hosts examples
- output of the above pt-archiver command
Started at 2024-10-08T10:02:00, ended at 2024-10-08T10:07:33 Source: D=sakila,p=...,t=rental,u=sakila Dest: D=sakila,p=...,t=old_rental,u=sakila SELECT 15861 INSERT 15861 DELETE 15861 Action Count Time Pct commit 31724 311.6698 93.55 select 15862 6.5446 1.96 deleting 15861 6.3438 1.90 inserting 15861 5.2972 1.59 other 0 3.3111 0.99
- This will remove the data from the source table and insert them in the target table
pt-summary
- Summarizes the status and configuration of a server
- Locate the pt-summary
find / -name pt-summary 2>/dev/null
usr/bin/pt-summary
- Usage
sudo /usr/bin/pt-summary | less
- Output
# Percona Toolkit System Summary Report ###################### Date | 2024-10-08 05:36:02 UTC (local TZ: +0530 +0530) Hostname | dev2-Latitude-E7270 Uptime | 2:57, 2 users, load average: 0.89, 1.44, 1.61 System | Dell Inc.; Latitude E7270; vNot Specified (Laptop) Service Tag | BFKTR72 Platform | Linux Release | Ubuntu 22.04.5 LTS (jammy) Kernel | 6.8.0-45-generic Architecture | CPU = 64-bit, OS = 64-bit Threading | NPTL 2.35 SELinux | No SELinux detected Virtualized | No virtualization detected # Processor ################################################## Processors | physical = 1, cores = 2, virtual = 4, hyperthreading = yes Speeds | 1x2898.822, 1x2900.040, 1x2914.082, 1x2986.415 Models | 4xIntel(R) Core(TM) i5-6300U CPU @ 2.40GHz Caches | 4x3072 KB Designation Configuration Size Associativity ========================= ============================== ======== ====================== L1 Cache Enabled, Not Socketed, Level 1 64 kB 8-way Set-associative L1 Cache Enabled, Not Socketed, Level 1 64 kB 8-way Set-associative L2 Cache Enabled, Not Socketed, Level 2 512 kB 4-way Set-associative L3 Cache Enabled, Not Socketed, Level 3 3 MB 12-way Set-associative # Memory ##################################################### Total | 15.4G Free | 1.5G Used | physical = 5.9G, swap allocated = 2.0G, swap used = 0.0, virtual = 5.9G Shared | 1.1G Buffers | 8.1G Caches | 8.1G Dirty | 1344 kB UsedRSS | 13.7G Swappiness | 60 :