# 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 ```bash mysql -uroot -p ``` - Execute the sakila-schema.sql script to create the database structure, and execute the sakila-data.sql script ```mysql SOURCE /home//Downloads/sakila-db/sakila-schema.sql; SOURCE /home//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 ```bash mysql -uroot -p ``` - Create a new table ```mysql 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**```bash find / -name pt-archiver 2>/dev/null ``` - This will show the location of the pt-archiver ``` /usr/bin/pt-archiver ``` - using pt-archiver command ```bash /usr/bin/pt-archiver --source D=sakila,t=rental --dest D=sakila,t=old_rental --where "rental_date < '2006-01-01'" --statistics --user --password --host ``` - user and hosts examples [![image.png](http://wiki.lalco.la/uploads/images/gallery/2024-10/scaled-1680-/image.png)](http://wiki.lalco.la/uploads/images/gallery/2024-10/image.png) - 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 ```bash find / -name pt-summary 2>/dev/null ``` ``` usr/bin/pt-summary ``` - Usage ```bash 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 : ```