Solved Automatically backup MySQL with a cron job

[kaith]

My configuration for daily backup:

#crontab -e
0 0 * * * mysqldump -uroot database > backup.sql

or

0 0 * * * /usr/local/bin/mysqldump -uroot database > backup.sql

or
make a shell script bakup.sh

#crontab -e
0 0 * * * /bin/sh /shell/bakup.sh

With all of the methods above my backup.sql file has size of 0 bytes after automatic backup. It's an empty file. Please help me with this problem.

I want to automatically backup MySQL daily.

 

[wblock@]

Please see question #2 here: http://www.wonkity.com/~wblock/docs/html/interrupted.html.

 

[ShelLuser]

Also; I wouldn't use the MySQL root account for this since it creates a severe security risk; the root user can basically do whatever he wants. Especially since you'll most likely also provide the password in the same file in order for the user to connect. So if someone else would come across this information they immediately gain full control over your MySQL server..

Instead of using root you should create a new account which is only allowed to retrieve data and nothing else. For example by using this on the MySQL console (while logged on as root): mysql> grant select on *.* to backup@localhost identified by 'password';.

This will create an account which can only login from the localhost, uses password and can also only be used to retrieve ("select") data. There's still a risk factor of third parties who can optionally access your data should they gain access to your script, but they won't be able to gain full control "just like that".

 

[kaith]

It's working thank you very much

 

[allen.konstanz]

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
#Set 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

 

[hardwired]

Have you tried using databases/autobackupmysql ? I use that on all my servers because it's easy to setup and does automatic daily, weekly, and monthly MySQL backups.