MySQL (Ubuntu)

This page contains information specific to running MySQL on Ubuntu, for information on MySQL generally, see the MySQL page.

Installation

1. Run the following command to update the package database
   • apt-get update
2. Run the following command to install MySQL
   • apt-get install mysql-server

To allow access from remote hosts...

1. Open MySQL service TCP/IP port by editing the /etc/mysql/my.cnf config file and restarting
   • Change bind IP to server's IP, EG bind-address = 192.168.1.123
   • Restart service /etc/init.d/mysql restart
2. Allow remote access to a user account
   • EG GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;

Backup

Based on http://www.cyberciti.biz/faq/ubuntu-linux-mysql-nas-ftp-backup-script/

1. Create the required folders using...
   • mkdir backup
   • mkdir backup/mysql
2. Create the file below (editing as required) as /backup/mysql.sh
3. Make the file executable
   • chmod +x /backup/mysql.sh
4. Perform a test run of the backup
5. Schedule the script to run with crontab
   • crontab -e
   • 30 1 * * * /bin/bash /backup/mysql.sh

#!/bin/bash

### MySQL Server Login and local backup info ###
MUSER="root"
MPASS="password"
MHOST="localhost"
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
BAK="/backup/mysql"
LOG="/backup/mysql.log"
GZIP="$(which gzip)"
NOW=$(date -u +%Y%m%d)

## FTP info
FTPDIR="/Backup/db"
FTPUSER="backup"
FTPPASS="backup"
FTPSERVER="ftphost"

## Functions
Logger()
{
        echo `date "+%a %d/%m/%y %H:%M:%S"`: $1 >> $LOG
}

## Main Script
Logger "Started backup script..."

[ ! -d $BAK ] && mkdir -p $BAK
[ ! -d $BAK/tmp ] && mkdir -p $BAK/tmp
mv $BAK/* $BAK/tmp

DBS="$($MYSQL -u $MUSER -h $MHOST -p$MPASS -Bse 'show databases')"
for db in $DBS
do
 FILE=$BAK/$db.$NOW.gz
 Logger "Backing up $db to $FILE"
 $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS $db | $GZIP -9 > $FILE
done

Logger "Completed local backup"

## FTP to remote server
ftp -in <<EOF
open $FTPSERVER
user $FTPUSER $FTPPASS
bin
cd $FTPDIR
lcd $BAK
mput *
close
bye
EOF

if [ "$?" == "0" ]; then
 Logger "FTP upload completed successfully"
 /bin/rm -f $BAK/tmp*
 Logger "Previous local backup files removed"
else
 Logger "FTP upload failed !!!"
fi

In some versions of MySQL you will receive an error similar to...

mysqldump: Got error: 1044: Access denied for user 'root'@'localhost' to database 'information_schema' when using LOCK TABLES

It appears to be a bug, which seems to keep cropping up. As a workaround change the $MYSQLDUMP line to

 $MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS --skip-lock-tables $db | $GZIP -9 > $FILE

Note that you won't backup the information_schema table if you need to implement this workaround