MySQL (Ubuntu)
Jump to navigation
Jump to search
The printable version is no longer supported and may have rendering errors. Please update your browser bookmarks and please use the default browser print function instead.
This page contains information specific to running MySQL on Ubuntu, for information on MySQL generally, see the MySQL page.
Installation
- Run the following command to update the package database
apt-get update
- Run the following command to install MySQL
apt-get install mysql-server
To allow access from remote hosts...
- 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
- Change bind IP to server's IP, EG
- Allow remote access to a user account
- EG
GRANT ALL PRIVILEGES ON *.* TO 'user'@'%' IDENTIFIED BY 'pass' WITH GRANT OPTION;
- EG
Backup
Based on http://www.cyberciti.biz/faq/ubuntu-linux-mysql-nas-ftp-backup-script/
- Create the required folders using...
mkdir backup
mkdir backup/mysql
- Create the file below (editing as required) as
/backup/mysql.sh
- Make the file executable
chmod +x /backup/mysql.sh
- Perform a test run of the backup
- 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