So, this has been an interesting one to work out with a bunch of trial and error. The mysqldump command has a very useful –where option that allows you to only dump out specific rows that match your where clause. Say you want to mysqldump all rows where status=’Active’ but only the last 5 ordered by descending date. The following will do that:
|
1 |
mysqldump database table --where="status='Active' order by date desc limit 5" |
Simple enough, right? Take that same command and put it into a bash script, and it’s not so easy.
|
1 |
eval mysqldump db table --where="status='Active' order by date desc limit 5" > db.sql |
Bash will break arguments at the spaces, so it totally screws up the command, and you’ll get various errors like, cannot find table ‘order’. Also, what I found there isn’t one great solution that works for every where clause, each needs to be escaped in it’s own way depending on the clause itself.
|
1 2 3 4 5 6 7 8 9 10 11 |
# Simple where clause with no spaces: eval mysqldump db table --where="field=\'moo\'" > db.sql # Where clause with an order and a limit, multiple spaces in clause: eval mysqldump db table --where=\"field=\'Active\' order by date desc limit 1\" > db.sql # Where clause with an IN condtion, parenthesis: eval mysqldump db table --where=\'field IN \(1,2,3\)\' > db.sql # Where clause where there are spaces in the match: eval mysqldump db table --where='"field > \"2017-11-01 00:00:00\""' > db.sql |
There are probably many more examples requiring different escape patterns, and there are probably other ways to achieve the same results. But hopefully what I’ve listed will at least be enough to save you some headache on trying to work through this from scratch.