Not the answer you need?
Register and ask your own question!

Confusion on my.cnf for use with scheduled pt-archiver

buee16buee16 EntrantCurrent User Role Beginner
Just started using pt-archiver today. I have a client and we have the need to get rid of old data. It's a remote MySQL instance (Google Cloud to be exact). The idea is to, on a regular bases, take records older than a year and move them to an archive table, so it's all in the same DB. I copied the real database to a "demo" one so I can test this without messing with the live data. Here's what I have that works:
pt-archiver --source h=<>,D=demo,t=verified,u=<DB username>,p=<DB password> --dest h=<>,D=demo_archive,t=verified --limit=100 --statistics --progress=10 --sleep=5 --why-quit --where 'time_connected < DATE_SUB(NOW(), INTERVAL 12 MONTH)'

Cool, it works. Now, I need to put this in my crontab to run probably once a week or so. I don't want to leave credentials in the command itself, so what I want to do (which I've read can be done) is to use a my.cnf file and the syntax would look like this:
pt-archiver --source F=/path/to/my.cnf,D=demo,t=verified --dest h=<>,D=demo_archive,t=verified --limit=100 --statistics --progress=10 --sleep=5 --why-quit --where 'time_connected < DATE_SUB(NOW(), INTERVAL 12 MONTH)'

And I have structured the my.cnf file:
user=<DB username>
password=<DB password>

But I can't seem to get it to work. Most of the time, I get an error saying it can't connect to localhost so that tells me I'm either supplying the filename wrong or some parameter that I'm unaware of that needs to be changed. like maybe the significance of [nickname] in the my.cnf file.

I've been chipping at this for the last 3 hours and can't get over this hurdle. For clarity, I really just want the command to pull the creds from a file and that's it, I don't want the my.cnf referenced in this post to be used for all MySQL connections, just for pt-archiver.


  • carlos.salguerocarlos.salguero Percona Toolkit Developer Percona Staff Role
    Try F=<path-to-my-file> D=demo,t=verified.
    Notice the space in between. D=<database>,t=<table> is a separated parameter
  • buee16buee16 Entrant Current User Role Beginner
    Try F=<path-to-my-file> D=demo,t=verified.
    Notice the space in between. D=<database>,t=<table> is a separated parameter

    That didn't work. It specifically complained about the command syntax being wrong citing D and t flags. However, I changed the command to exactly how its shown in my OP and now it wants to work. I tried this many many many times before posting here with various filenames, file permissions, and different parameters in the file itself, including full paths to the file and it didn't work. The only other change I made after trying your suggestion was chmod 600 [my file], but it's running as I type this so I guess I'm set! Thanks for the assistance.
Sign In or Register to comment.

MySQL, InnoDB, MariaDB and MongoDB are trademarks of their respective owners.
Copyright ©2005 - 2020 Percona LLC. All rights reserved.