Installs and configures a MySQL 5 server.

mysql5.sh

#!/bin/bash -x
# Dependency: Webmin should be installed before this script is executed.
# Dependency: UFW should be installed before this script is executed.
# <UDF name="aq_password" Label="Administrator Password" />
# <UDF name="new_hostname" Label="Computer Hostname (all lowercase)" example="basicshared1" />

# Include http://www.linode.com/stackscripts/view/?StackScriptID=1
source <ssinclude StackScriptID="1">

#
# Install MySQL5
# Note: Webmin (if it is used) should be installed before this script is executed.
#

# install
echo "mysql-server-5.1 mysql-server/root_password password $AQ_PASSWORD" | debconf-set-selections
echo "mysql-server-5.1 mysql-server/root_password_again password $AQ_PASSWORD" | debconf-set-selections
aptitude -y install mysql-server
mysql_secure_installation

# set initial permissions
echo "GRANT SHOW DATABASES, SELECT, LOCK TABLES ON *.* TO backup@localhost IDENTIFIED BY 'backup';" | mysql -u root -p{$AQ_PASSWORD} # user for local backups

# If the server needs to listen for remote connections, adjust the bind-address.
sed -i 's/bind-address            = 127.0.0.1/bind-address = 0.0.0.0/' /etc/mysql/my.cnf

# set the configuration
echo "[mysqldump]" > /etc/mysql/conf.d/AQ_mysqldump.cnf
echo "quick" >> /etc/mysql/conf.d/AQ_mysqldump.cnf
echo "quote-names" >> /etc/mysql/conf.d/AQ_mysqldump.cnf
echo "comments" >> /etc/mysql/conf.d/AQ_mysqldump.cnf
echo "hex-blob" >> /etc/mysql/conf.d/AQ_mysqldump.cnf
echo "add-drop-table" >> /etc/mysql/conf.d/AQ_mysqldump.cnf
echo "skip-add-locks" >> /etc/mysql/conf.d/AQ_mysqldump.cnf
echo "skip-disable-keys" >> /etc/mysql/conf.d/AQ_mysqldump.cnf
echo "skip-tz-utc" >> /etc/mysql/conf.d/AQ_mysqldump.cnf
echo "complete-insert" >> /etc/mysql/conf.d/AQ_mysqldump.cnf
echo "skip-extended-insert" >> /etc/mysql/conf.d/AQ_mysqldump.cnf
# also consider --single-transaction (Issue a BEGIN SQL statement before dumping data from the server.)
# also consider --quick (Enforce dumping tables row by row. This provides added safety for systems with little RAM and/or large databases where storing tables in memory could become problematic.)
# also consider --lock-tables=false (Do not lock tables for the backup session.)

echo "[mysqld]" > /etc/mysql/conf.d/AQ_local_infile.cnf
echo "# Disable the use of LOCAL INFILE" >> /etc/mysql/conf.d/AQ_local_infile.cnf
echo "local-infile=0" >> /etc/mysql/conf.d/AQ_local_infile.cnf

# Create mysqldump script
# from http://openconcept.ca/mysql_permissions_for_backup
# reference: http://dev.mysql.com/doc/refman/4.1/en/mysqldump.html
# TODO move to /var/backups?
# have to run `mysql_config_editor set --login-path=backup --host=localhost --user=backup --password` first
echo '#!/bin/sh' > /var/lib/mysql/backup.sh
echo '' >> /var/lib/mysql/backup.sh
echo 'DIR=/var/lib/backups' >> /var/lib/mysql/backup.sh
echo 'BIN=/usr/bin' >> /var/lib/mysql/backup.sh
echo '' >> /var/lib/mysql/backup.sh
echo 'umask 007' >> /var/lib/mysql/backup.sh
echo 'mkdir -p ${DIR}' >> /var/lib/mysql/backup.sh
echo '' >> /var/lib/mysql/backup.sh
echo 'DB_LIST=`${BIN}/mysql --login-path=backup -Bs -e"show databases;"`' >> /var/lib/mysql/backup.sh
echo 'for DB in $DB_LIST;' >> /var/lib/mysql/backup.sh
echo 'do' >> /var/lib/mysql/backup.sh
echo '  if [ ${DB} != "information_schema" ] && [ ${DB} != "performance_schema" ] && [ ${DB} != "sys" ]' >> /var/lib/mysql/backup.sh
echo '  then' >> /var/lib/mysql/backup.sh
echo '    FILENAME=${DIR}/${DB}.sql.gz' >> /var/lib/mysql/backup.sh
echo '    ${BIN}/mysqldump --login-path=backup --opt --complete-insert --skip-extended-insert $DB | gzip > $FILENAME' >> /var/lib/mysql/backup.sh
echo '  fi' >> /var/lib/mysql/backup.sh
echo 'done' >> /var/lib/mysql/backup.sh
chmod u+x,go-rwx /var/lib/mysql/backup.sh
echo '0 0 * * * root nice /var/lib/mysql/backup.sh >/dev/null' > /etc/cron.d/mysql-backup

# setup MySQL module in Webmin
sed -i 's/mysql_libs=\/usr\/local\/mysql\/lib/mysql_libs=\/usr\/lib\/mysql/' /etc/webmin/mysql/config # is this right?
echo "login=root" >> /etc/webmin/mysql/config
echo "pass=$AQ_PASSWORD" >> /etc/webmin/mysql/config
sed -i 's/mysql=0/mysql=1/' /etc/webmin/installed.cache

# Remove the history file for security
cat /dev/null > ~/.mysql_history

touch /tmp/restart-webmin
touch /tmp/restart-mysql

#
# Add firewall rules
#
ufw allow mysql/tcp
touch /tmp/restart-ufw

#
# Restart changed services.
#
restartServices