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=<my.domain.com>,D=demo,t=verified,u=<DB username>,p=<DB password> --dest h=<my.domain.com>,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=<my.domain.com>,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:
[nickname]
host=my.domain.com
port=3306
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.