Since I’m a PHP developer and I run Ubuntu on my laptop, I find it very easy to development directly on the machine. This means that my laptop also has PHP and MySQL running (and until the new PHP 5.4 dev server, Apache). While PHP doesn’t is only used on-demand, the MySQL server is a daemon that’s always running. Since I use my laptop for plenty of things other than dev with MySQL, I wanted a way to disable the autostart so I just need to start MySQL when I’m ready to use it.
A quick google found a useful answer on superuser.com:
To prevent mysql from starting on boot
- go to the /etc/init directory
- open the mysql.conf file
- comment out the “start on” line near the top of the file, the “start on” might be spread across two lines, so comment out both
If you want to manually start mysql, use the following command –
service mysql start
Which solves the first problem nicely, but how about backups?
I use a fantastic little script called automysqlbackup which for basic MySQL backup and versioning needs is perfect. It makes a database dump daily, saving a weekly and monthy backup periodically. So you end up with backups for the last 7 days, weekly backups for the last 5 weeks, and monthly backups are kept forever.
Installation on Ubuntu (or any Debian based system) is a piece of cake:
sudo apt-get install automysqlbackupOnce installed, it will start working automatically and save your backups into:
/var/lib/automysqlbackupConfiguration, which has quite sensible defaults, is managed through the config file:
sudo vim /etc/default/automysqlbackupFor a normal setup, you can leave it there, however, my MySQL database is no longer always online. This means that automysqlbackup is likely to attempt to backup when it is offline… and fail. So we have to get clever. I want to check if the MySQL database is online before running the backup. If it is offline, then we need to start it and save a tmp file. Once the backup is finished, check for the tmp file and shut down the database if found. This should result in automysqlbackup starting MySQL, running the backup, and stopping MySQL automatically when MySQL is offline, and just doing a backup when it’s online.
I wrote two little scripts to handle the pre-backup and post-backup tasks:
valorin@gandalf:~/mysql$ cat pre-backup.sh #!/bin/bash# Remove left over tmp fileif [ -f /tmp/mysql-started-for-backup.tmp ]; then rm /tmp/mysql-started-for-backup.tmpfi# Start MySQL if offlineif [ -z "$(pgrep -u mysql -f /usr/sbin/mysqld)" ]; then service mysql start touch /tmp/mysql-started-for-backup.tmpfivalorin@gandalf:~/mysql$ cat post-backup.sh #!/bin/bash# Some magic to get 'service mysql stop' to workPATH=/usr/sbin:/usr/bin:/sbin:/bin# Check for tmp fileif [ -f /tmp/mysql-started-for-backup.tmp ]; then rm /tmp/mysql-started-for-backup.tmp service mysql stopfiAutomysqlbackup provides PREBACKUP and POSTBACKUP commands in the config file. My first assumption was to use the PREBACKUP script to check if MySQL is online, and if not, start it. But there is a problem… near the top of the configuration file is this line:
DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | grep -v ^performance_schema$ | grep -v ^information_schema$ | tr \\\r\\\n ,\ `Which attempts to connect to the MySQL database… so we need to inject our PREBACKUP script before this line is reached. To get around this, we can call the PREBACKUP script from the top of the config file, above the DBNAMES line:
...# Command to run before backups /var/lib/automysqlbackup/pre-backup.sh...DBNAMES=`mysql --defaults-file=/etc/mysql/debian.cnf --execute="SHOW DATABASES" | awk '{print $1}' | grep -v ^Database$ | grep -v ^mysql$ | grep -v ^performance_schema$ | grep -v ^information_schema$ | tr \\\r\\\n ,\ `But we can use the POSTBACKUP command to finish up the job since there are no more MySQL calls after it in the script:
# Command run after backups (uncomment to use)POSTBACKUP="/var/lib/automysqlbackup/post-backup.sh"There we have it – automated backups of an on-demand MySQL database using automysqlbackup. If you have a different way to handle this situation, I’d love to hear it!