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