2

Below is the BASH(Version 4.1.2) script i have placed on cronjob which runs daily at 23:59 to backup a table with records created on that particur day 

/usr/bin/mysqldump --single-transaction --no-create-db 
--no-create-info databasename salestable1 
--where="acctstarttime 
LIKE '2016-12-28%'" >  /dbbackup/`date +%F`-salestable1.sql

Here the problem is i have to manually change the date part LIKE '2016-12-28%' inside mysql execute command manually.

How to construct the current script so that it can backup the table with records created on particular day withouthaving to change date manually ?

solution for my case

sys_date=`date +%F -d '-1 day'`
/usr/bin/mysqldump --single-transaction --no-create-db   \ 
--no-create-info ctradius radacct     \
--where="acctstarttime LIKE '${sys_date}%'" >      \
/backup/freeradius/radacct-`date +%F -d '-1 day'`.sql
sherpaurgen
  • 3,028
  • 6
  • 32
  • 45
  • Possible duplicate of [Automated or regular backup of mysql data](http://stackoverflow.com/questions/38916163/automated-or-regular-backup-of-mysql-data) – e4c5 Dec 28 '16 at 11:05

3 Answers3

3

try this 

#!/bin/bash
/usr/bin/mysqldump --single-transaction --no-create-db --no-create-info databasename salestable1 --where="acctstarttime LIKE '$(date +%F)%'" > /dbbackup/databasename/`date +%F`-salestable1.sql
Kamal Lama
  • 690
  • 2
  • 5
  • 20
2

You can try to introduce one variable, i.e:

The script:

root@h1 ~# cat backup_table.sh

#!/bin/bash

DATE=$(date +%F)
echo "/usr/bin/mysqldump --single-transaction --no-create-db --no-create-info databasename salestable1 --where="acctstarttime LIKE \'${DATE}%\'" >  /dbbackup/`date +%F`-salestable1.sql"

Launched script with dynamic date:

root@h1 ~# bash backup_table.sh
/usr/bin/mysqldump --single-transaction --no-create-db --no-create-info databasename salestable1 --where=acctstarttime LIKE '2016-12-28%' >  /dbbackup/2016-12-28-salestable1.sql
DevDio
  • 1,525
  • 1
  • 18
  • 26
  • You cannot wrap quotes like in your `echo` statement. – tripleee Dec 28 '16 at 11:18
  • Running `date +%F` a second time to create the date stamp in the file name is useless, since you already have this value in a variable. – tripleee Dec 28 '16 at 11:19
  • hi triplee, that was just a purpose to verify if the date is printed correctly,I believe it could be resolved in similar way. Still, satch_boogie can improve it – DevDio Dec 28 '16 at 11:29
  • You have to put `\"` inside the double quotes to get a literal double quote. Alternatively, don't put quotes around the argument to `echo` (but understand the perils). – tripleee Dec 28 '16 at 11:30
1
#!/bin/bash
_cmd="/usr/bin/mysqldump --single-transaction --no-create-db 
--no-create-info databasename salestable1 
--where=\"acctstarttime 
LIKE '$(date +%F)%'\" >  /dbbackup/`date +%F`-salestable1.sql"

echo $_cmd

eval $_cmd

and if you exec it, output like is this

wzq@~ >_ ./foo.sh

/usr/bin/mysqldump --single-transaction --no-create-db --no-create-info databasename salestable1 --where="acctstarttime LIKE '2016-12-29%'" > /dbbackup/2016-12-29-salestable1.sql

./foo.sh: line 9: /dbbackup/2016-12-29-salestable1.sql: No such file or directory

dormi330
  • 1,273
  • 11
  • 18