mysqldump gzip commands to properly create a compressed file of a MySQL database using crontab

Question

I am having problems with getting a crontab to work  I want to automate a MySQL database backup   The setup    Debian GNU Linux 7 3  wheezy   MySQL Server version  5 5 33-0 wheezy1 Debian   directories user  backup and backup2 have 755 permission The user names for MySQL db and Debian account are the same   From the shell this command works  mysqldump -u user -p user password   database name    gzip  gt  dumpfilename sql gz   When I place this in a crontab using crontab -e        usr bin mysqldump -u user -pupasswd mydatabase   gzip gt   home user backup mydatabase-backup- date    m  d   Y  sql gz  gt  dev null 2 gt  amp 1   A file is created every minute in  home user backup directory  but has 0 bytes   However when I redirect this output to a second directory  backup2  I note that the proper mysqldumpfile duly compressed is created in it  I am unable to figure what is the mistake that I am making that results in a 0 byte file in the first directory and the expected output in the second directory        usr bin mysqldump -u user -pupasswd my-database   gzip gt   home user backup mydatabase-backup- date    m  d   Y  sql gz  gt  home user backup2 mydatabase-backup- date    m  d   Y  sql gz 2 gt  amp 1   I would greatly appreciate an explanation   Thanks

User · Answer

if you need to add a date-time to your backup file name (Centos7) use the following:

/usr/bin/mysqldump -u USER -pPASSWD DBNAME | gzip > ~/backups/db.$(date +%F.%H%M%S).sql.gz

this will create the file: db.2017-11-17.231537.sql.gz

User · Answer

Personally  I have create a file sh  right 755  in the root directory  file who do this job  on order of the crontab   Crontab code   10 2       root  root backupautomatique sh  File sh code   rm -f  home mordb-148-251-89-66 sql gz   To erase the old one   mysqldump mor   gzip    home mordb-148-251-89-66 sql gz  what you have done   scp -P2222  home mordb-148-251-89-66 sql gz root otherip  home mordbexternes mordb-148-251-89-66 sql gz    to send a copy somewhere else if the sending server crashes  because too old  like me  -

User · Answer

You can use the tee command to redirect output    usr bin mysqldump -u user -pupasswd my-database     tee  gt  gzip -9 -c  gt   home user backup mydatabase-backup- date    m  d   Y  sql gz       gzip gt   home user backup2 mydatabase-backup- date    m  d   Y  sql gz 2 gt  amp 1   see documentation here

User · Answer

Besides the solution of m79lkm above  my 2 cents on this topic is not to directly pipe the result in gzip but first dump it as a  sql file  and then gzip it   Use  amp  amp  instead of     The dump itself will be faster   for what I tested it was double as fast  Otherwise you tables will be locked longer and the downtime slow-responding of your application can bother the users  The mysqldump command is taking a lot of resources from your server  So I would go for  quot  amp  amp  gzip quot  instead of  quot   gzip quot  Important  check for free disk space first with df -h since you will need more then piping   gzip  mysqldump -u user -p user password   database name   gt  dumpfilename sql  amp  amp  gzip dumpfilename sql  - gt  which will also result in 1 file called dumpfilename sql gz Furthermore the option --single-transaction prevents the tables being locked but still result in a solid backup  So you might consider to use that option  See docs here mysqldump --single-transaction -u user -p user password   database name   gt  dumpfilename sql  amp  amp  gzip dumpfilename sql

User · Answer

First the mysqldump command is executed and the output generated is redirected using the pipe  The pipe is sending the standard output into the gzip command as standard input  Following the filename gz  is the output redirection operator     which is going to continue redirecting the data until the last filename  which is where the data will be saved   For example  this command will dump the database and run it through gzip and the data will finally land in three gz  mysqldump -u user -pupasswd my-database   gzip  gt  one gz  gt  two gz  gt  three gz    gt  ls -l -rw-r--r--  1 uname  grp     0 Mar  9 00 37 one gz -rw-r--r--  1 uname  grp  1246 Mar  9 00 37 three gz -rw-r--r--  1 uname  grp     0 Mar  9 00 37 two gz   My original answer is an example of redirecting the database dump to many compressed files  without double compressing    Since I scanned the question and seriously missed - sorry about that   This is an example of recompressing files   mysqldump -u user -pupasswd my-database   gzip -c  gt  one gz  gzip -c one gz  gt  two gz  gzip -c two gz  gt  three gz    gt  ls -l -rw-r--r--  1 uname  grp  1246 Mar  9 00 44 one gz -rw-r--r--  1 uname  grp  1306 Mar  9 00 44 three gz -rw-r--r--  1 uname  grp  1276 Mar  9 00 44 two gz   This is a good resource explaining I O redirection  http   www codecoffee com tipsforlinux articles2 042 html

[crontab] mysqldump & gzip commands to properly create a compressed file of a MySQL database using crontab

Examples related to crontab

Examples related to mysqldump