Buy Percona ServicesBuy Now!

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

Collapse
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

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

    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:
    Code:
    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:
    Code:
    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:
    Code:
    [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.

  • #2
    Try F=<path-to-my-file> D=demo,t=verified.
    Notice the space in between. D=<database>,t=<table> is a separated parameter

    Comment


    • #3
      Originally posted by carlos.salguero View Post
      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.

      Comment

      Working...
      X