# Mysqldump on Cron



## Limbo (Apr 11, 2017)

Please help with my don't working cron file:


```
SHELL=/bin/sh
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin
# Order of crontab fields
# minutehour    mday    month    wday    command
*    *    */1    *    *    mysqldump -u root -pmasterMYSQL CBM | gzip> /usr/local/www/CBM_`date +%m-%d-%Y`.sql.gz
```

Line `date +%m-%d-%Y` is bad for cron, please help me


----------



## SirDice (Apr 11, 2017)

Any output that's generated by a cronjob (typically error messages) will get mailed. So check root's mail.

And I suggest not trying to create too complicated command lines, put it in a script and call the script from cron(8).


```
*    *    */1    *    *  /root/bin/mysql_backup.sh
```
And put all the necessary steps in /root/bin/mysql_backup.sh. This will make it a lot less error-prone and will allow you to make the script as complex as needed without having to cram everything in a single line. It will also allow you to manually start the script if needed.


----------



## ShelLuser (Apr 11, 2017)

Limbo said:


> Please help with my don't working cron file


Apart from what SirDice said (I'd really follow up on his advice to make a script and then run that, it makes your life a whole lot easier too when you need to apply changes) also check crontab(5).



Limbo said:


> ```
> SHELL=/bin/sh
> PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin
> # Order of crontab fields
> ...


You got more problems than that. Do you realize what * * */1 stands for?


```
field         allowed values
           -----         --------------
           minute        0-59
           hour          0-23
           day of month  1-31
           month         1-12 (or names, see below)
           day of week   0-7 (0 or 7 is Sun, or use names)
```
Your script will run every day (the third *) with an interval of 1, so still every day. Then the first two usages of * without any intervals will ensure that your script will pretty much run every minute or so for every hour for the whole duration of that day.

I don't think you're looking to make 60 * 60 * 24 = 86400 backups in one day, do you? 

So also specify a time when this needs to run.


----------



## Limbo (Apr 11, 2017)

This is for test only. SH script not work, nothing in log cron.


```
Jan  2 03:25:00 CBM /usr/sbin/cron[2122]: (root) CMD (/usr/libexec/atrun^M)
Jan  2 03:27:57 CBM /usr/sbin/cron[2148]: (*system*) PARSE (bad minute)
Jan  2 03:27:57 CBM last message repeated 2 times
Jan  2 03:30:00 CBM /usr/sbin/cron[2150]: (root) CMD (/usr/libexec/atrun^M)
Jan  2 03:33:45 CBM /usr/sbin/cron[1897]: (*system*) PARSE (bad minute)
Jan  2 03:33:45 CBM last message repeated 2 times
Jan  2 03:35:00 CBM /usr/sbin/cron[1968]: (root) CMD (/usr/libexec/atrun^M)
```


```
SHELL=/bin/sh
PATH=/sbin:/bin:/usr/sbin:/usr/bin:/usr/local/sbin:/usr/local/bin
# Order of crontab fields
# minutehour    mday    month    wday    command
*/1    *    *    *    *    /usr/bin/MySQL_Backup.sh
```


```
#!/bin/sh
mysqldump -u root -pmasterMYSQL CBM | gzip> /usr/local/www/CBM_`date +%m-%d-%Y`.sql.gz
```

Please help me


----------



## SirDice (Apr 11, 2017)

Use something sensible like:

```
5 0 * * * /root/bin/mysql_backup.sh
```
This will make it run every day at 0:05.


----------



## Limbo (Apr 12, 2017)

Not work


----------



## SirDice (Apr 12, 2017)

Run the script by hand, does it work then?


----------



## Limbo (Apr 12, 2017)

SirDice said:


> Run the script by hand, does it work then?


Yes by hands all works


----------



## SirDice (Apr 12, 2017)

Check root's mail, if a cronjob produces output it will get mailed.


----------



## Limbo (Apr 12, 2017)

SirDice said:


> Check root's mail, if a cronjob produces output it will get mailed.



The patch /var/mail/ is empty


----------



## Limbo (Apr 12, 2017)

Please help me


----------



## forquare (Apr 12, 2017)

Potentially unrelated points:

Make sure your shell script is executable

I would include the full path to `mysqldump` in the shell script t


----------



## IPTRACE (Apr 12, 2017)

Maybe try to use speech marks in date command.

``date "+%m-%d-%Y"`.sql`


----------



## ShelLuser (Apr 12, 2017)

Limbo said:


> Please help me


Definitely specify the full path to the programs you use, first /usr/local/bin/mysqldump and also /usr/bin/gzip. That's the most likely thing which might have gone wrong here.


----------



## ralphbsz (Apr 12, 2017)

Step 1, as already said above: Put all the work you need to do into a small script (which you can install in /usr/local/sbin for example).

Step 2: Make sure that shell script works when called by hand *with a minimal path*.  Remember, cron sets the path to a small set.  If your script is called /usr/local/sbin/foobar, then I would test is as follows: `PATH=/etc:/bin:/sbin:/usr/bin:/usr/sbin /usr/local/sbin/foobar`.  Ideally, you could even inside your script become completely independent from the path, by calling all executables by explicit path (that has both advantages and disadvantages).

Step 3: Temporarily create another really tiny shell script, which you just use to verify that calling things from cron works.  For example, here is test script /usr/local/sbin/blatz:

```
#!/bin/sh
date >> /tmp/blatz.log
```
Put that tiny script in crontab, and make sure (by watching the log file) that it gets run.  Make sure this shell script has execute permissions.

Step 4: Create yet another three tiny shell scripts, to test that getting the output of cron jobs into e-mail works.  The first one should test stdout; it could just contain `echo Hallo `date`` with the appropriate `#!/bin/sh` hash-bang line.  The second one should test stderr, with `echo Hallo `date` > /dev/stderr`.  The third one is like the second one, but it shall fail, by returning a non-zero code: simply add `exit 1` to the end of it.  Let all three run from crontab, and make sure you get all the appropriate e-mails, and that you know how to distinguish success from failure and get the debugging data.

As you see, what I did above is break down the problem to various components, each of which could have problems.  Once all this works to your satisfaction, adding the "real" script to crontab just *has to work*.


----------



## allen.konstanz (Jun 15, 2018)

For me works doing this: 

First set you password and DBDescription to a encrypted file with this command bellow:
`mysql_config_editor set --login-path=DBDescription --host=DB#.pair.com --user=DBUser --port=3306 --password`

See if that works:
`mysql_config_editor print --login-path=DBDescription`

The output will be something like this:

```
[DBDescription]
user = root
password = *****
host = localhost
port = 3306
```

Now create a shell script:


```
#!/bin/sh
#Seta paths
MYDUMP=/usr/local/bin/mysqldump
BACKUPDIR=/usr/local/www/apache24/data
MYGZIP=/usr/bin/gzip
MYFIND=/usr/bin/find

#Dump
$MYDUMP --login-path=DBDescription DBNAME > $BACKUPDIR/mysql-DBNAME.`date '+%d-%B-%Y--%Hh'`.sql

#GZIP the file
$MYGZIP $BACKUPDIR/mysql-DBNAME.`date '+%d-%B-%Y--%Hh'`.sql
```

And finally create a job with cron.
Here I use: 
`# nano /etc/crontab`
At the final line of the file add:

```
00 04 * * * root /usr/local/www/apache24/data/mybackup.sh
```

Based on the information of the page: link


----------

