MySQL Backup Script in Bash / Cron Job

The following is a handy cron job that backs up a MySQL database, compress it with gzip and will delete the ones older than 30 days. Make sure the user running the job has write permissions (in my case the user levente@testsrv) on the backup output directory. The mysql backup job will be executed daily at 2:15am and the cleanup job at 2:20am.

levente@testsrv:~$ crontab -e


# ....
# For more information see the manual pages of crontab(5) and cron(8)
#
# m h  dom mon dow   command

 15 2 * * * mysqldump -u mysqluser -pmysqlpassword db_name | gzip > /var/backups/mysql/db_name_`date '+\%Y\%m\%d-\%H\%M\%S'`.sql.gz
 20 2 * * * find /var/backups/mysql/db_name* -mtime +30 -exec rm {} \;

If you want to run the MySQL backup script directly at the shell, you have to remove the backslash characters before the % because these are escaped in cron.

levente@testsrv:~$ mysqldump -u mysqluser -pmysqlpassword db_name | gzip > /var/backups/mysql/db_name_`date '+%Y%m%d-%H%M%S'`.sql.gz

MySQL 5.5 service won't start: Job failed to start

Trying to connect to my local MySQL 5.5 on an Ubuntu Server 12.10 x64 gave me the following error:

   levente@linuxvm:/$ mysql
   ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)

Turned out that the MySQL Service is not running.

   levente@linuxvm:/$ ps -ef | grep mysql
   levente 5347 2842 0 08:47 pts/1 00:00:00 grep --color=auto mysql

Trying to start it failed:

   levente@linuxvm:/var/log$ sudo service mysql start
   start: Job failed to start

Syslog shows like this:

   levente@linuxvm:/$ cat /var/log/syslog
   Nov 16 08:28:49 linuxvm kernel: [ 1307.007001] type=1400 audit(1353072529.161:42): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=4600 comm="apparmor_parser"
   Nov 16 08:28:50 linuxvm kernel: [ 1308.658583] init: mysql main process (4604) terminated with status 1
   Nov 16 08:28:50 linuxvm kernel: [ 1308.658625] init: mysql main process ended, respawning
   Nov 16 08:28:51 linuxvm kernel: [ 1309.291148] init: mysql post-start process (4605) terminated with status 1
   Nov 16 08:28:51 linuxvm kernel: [ 1309.306516] type=1400 audit(1353072531.461:43): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=4651 comm="apparmor_parser"
   Nov 16 08:28:52 linuxvm kernel: [ 1310.791380] init: mysql main process (4655) terminated with status 1
   Nov 16 08:28:52 linuxvm kernel: [ 1310.791400] init: mysql main process ended, respawning
   Nov 16 08:28:53 linuxvm kernel: [ 1311.380212] init: mysql post-start process (4656) terminated with status 1
   Nov 16 08:28:53 linuxvm kernel: [ 1311.395653] type=1400 audit(1353072533.549:44): apparmor="STATUS" operation="profile_replace" name="/usr/sbin/mysqld" pid=4702 comm="apparmor_parser"
   Nov 16 08:28:55 linuxvm kernel: [ 1312.886555] init: mysql main process (4706) terminated with status 1
   Nov 16 08:28:55 linuxvm kernel: [ 1312.886635] init: mysql respawning too fast, stopped

The problem is that I had some incorrect values in /etc/mysql/my.cnf. In my case it was an incorrect bind address that I just uncommented.

  #
  # Instead of skip-networking the default is now to listen only on
  # localhost which is more compatible and is not less secure.
  # bind-address = 172.17.1.1
  #

  levente@linuxvm:/$ sudo start mysql
  mysql start/running, process 5432

Yay!