I am having problems with getting a crontab to work. I want to automate a MySQL database backup.
The setup:
From the shell this command works
mysqldump -u user -p[user_password] [database_name] | gzip > dumpfilename.sql.gz
When I place this in a crontab using crontab -e
* * /usr/bin/mysqldump -u user -pupasswd mydatabase | gzip> /home/user/backup/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz >/dev/null 2>&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> /home/user/backup/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz >/home/user/backup2/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz 2>&1
I would greatly appreciate an explanation.
Thanks
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 > one.gz > two.gz > three.gz
$> 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 > one.gz; gzip -c one.gz > two.gz; gzip -c two.gz > three.gz
$> 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
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 && 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 "&& gzip" instead of "| gzip"
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] > dumpfilename.sql && gzip dumpfilename.sql
-> 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] > dumpfilename.sql && gzip dumpfilename.sql
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 ;-))
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
You can use the tee command to redirect output:
/usr/bin/mysqldump -u user -pupasswd my-database | \
tee >(gzip -9 -c > /home/user/backup/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz) | \
gzip> /home/user/backup2/mydatabase-backup-`date +\%m\%d_\%Y`.sql.gz 2>&1
see documentation here
Source: Stackoverflow.com