force 404 page message to center
[disinclined.org.git] / _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>