Percona Toolkit

Experimenting each percona toolkit tools to efficiently replicate from the master to slave server

1. Prerequestics
2. pt-archive
pt-summary

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
: