Sstrutt: Initial creation - content from Ubuntu page

2012-04-16T11:35:35Z

Initial creation - content from Ubuntu page

New page

'''This page contains information specific to running ''[[:Category:MySQL|MySQL]] on [[:Category:Ubuntu|Ubuntu]]'', for information on MySQL generally, see the [[MySQL]] page.'''

= Installation =
# Run the following command to update the package database
#* <code> apt-get update </code>
# Run the following command to install MySQL
#* <code> apt-get install mysql-server </code>

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

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

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

<source lang="bash">
#!/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
</source>

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 [http://bugs.mysql.com/bug.php?id=21527|MySQL bug], which seems to keep cropping up. As a workaround change the <code> $MYSQLDUMP </code> line to
<source lang="bash">
$MYSQLDUMP -u $MUSER -h $MHOST -p$MPASS --skip-lock-tables $db | $GZIP -9 > $FILE
</source>
Note that you won't backup the <code> information_schema </code> table if you need to implement this workaround

[[Category:MySQL]]
[[Category:Ubuntu]]
[[Category:Installation]]

MySQL (Ubuntu) - Revision history

Sstrutt: Initial creation - content from Ubuntu page