Skip to main content

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

    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

 

No Comments
Back to top