_posts/2011-03-24-automysqlbackup.html

   1 ---
   2 layout: note
   3 ---
   4
   5 automysqlbackup
   6 2011/03/24 01:30AM
   7
   8 <p>I needed a restore solution for my database before launch. I found <a href="https://sourceforge.net/projects/automysqlbackup/">automysqlbackup</a> which does everything I need. Installation took a bit of trial and error, so here's what works:
   9
  10 <pre class="brush: bash">
  11 curl http://tiny.cc/ey3ap -L > automysqlbackup
  12 sudo mv automysqlbackup /opt
  13 sudo ln /opt/automysqlbackup /usr/bin/automysqlbackup -s
  14 sudo chmod +x /usr/bin/automysqlbackup
  15 </pre>
  16
  17 <p>Now the script is installed, but is not configured. The settings are read from /etc/automysqlbackup/automysqlbackup.conf, and a sample is inside the script itself, so:</p>
  18
  19 <pre class="brush: bash">
  20 sudo vim automysqlbackup
  21 /START CFG
  22 ma
  23 /END CFG
  24 :'a,. y
  25 :e /etc/automysqlbackup/automysqlbackup.conf
  26 p
  27 ggVG:norm 0dw
  28 :bn :bd :x
  29 </pre>
  30
  31 <p>All that's left to do here is fill out the information requested by the script and save the file. I wanted the backups written to /var/backups/mysql, so I took a moment to set up the permissions for that.</p>
  32
  33 <pre class="brush: bash">
  34 sudo mkdir /var/backups/mysql
  35 sudo chgrp webdev mysql
  36 sudo chmod 770 mysql
  37 </pre>
  38
  39 <p>I also noticed that automysqlbackup takes a password variable, but uses mysqldump internally. The man page of mysqldump recommends against passing passwords through the shell. Since this is due to a security issue with ps, I wanted to use the recommended method, a configuration file. I found the dbdump() function in the source of automysqlbackup (line 506 for me), and added a --defaults-file option to the mysqldump call. Due to a bug? this <i>must</i> be the first option given.</p>
  40
  41 <pre class="brush: bash; gutter: true; first-line:505;">
  42 # Database dump function
  43 dbdump () {
  44 ${MYSQLDUMP} --defaults-file=/home/dylan/.autobackup.cnf --user=${USERNAME}     --host=${DBHOST} ${OPT} ${1} > ${2}
  45 return $?
  46 }
  47
  48 </pre>
  49
  50 <p>Then finish up by creating your .cnf file wherever you chose.</p>
  51
  52 <pre class="brush: bash;">
  53 [client]
  54 password=your_password
  55 </pre>
  56
  57 <p>At this point, the automysqlbackup script is all set to go, but will still get rejected by MySQL. There is no reason to that automysqlbackup needs to do anything but read the database to back it up, so create a new user constrained to the loopback address with these rights alone. Make sure this user is set in automysqlbackup.cnf.</p>
  58
  59 <pre class="brush: sql">
  60 CREATE USER 'autobackup'@'localhost' IDENTIFIED BY 'password';
  61 GRANT select, lock tables ON *.* TO 'autobackup'@'localhost';
  62 </pre>
  63
  64 <p>All that's left is to schedule it, so toss it into cron.</p>
  65
  66 <pre class="brush: bash">
  67 crontab -e
  68 Go@daily automysqlbackup&lt;Esc&gt;
  69 </pre>