Wiki Index All Recent Edit Bottom

MySQL Server Configuration

1.   MySQL Server
1.1   Install MySQL Server
1.2   Secure the server
1.3   Reducing the MySQL resource requirements
2.   MySQL Backups
2.1   Virtual Hostess Integration

MySQL Server

Install MySQL Server

 aptitude install mysql-server mysql-client

Secure the server

You should create a password for MySQL 'root' user, remove remote access for the root user and also remove the test databases and anonymous user created by default. This is strongly recommended for production servers.

Run the command below and answer the prompts.

 /usr/bin/mysql_secure_installation

Setting Password

NOTE! This is for reference only, use the 'mysql_secure_installation' script above after initial installation.

Create a password for the MySQL user root (replace yourrootsqlpassword with the password you want to use):

 mysqladmin -u root password yourrootsqlpassword

Then check which addresses MySQL is listening on.

 netstat -tap | grep mysql

If the output looks something like this...

 tcp        0      0 localhost:mysql         *:*                     LISTEN     5059/mysqld

...it means MySQL is listening on 'localhost.localdomain' only, and you're safe. But if the output looks like this...

 tcp        0      0 *:mysql *:*                     LISTEN     2713/mysqld

...you should set a MySQL password for your hostname too, because otherwise anybody can potentially access your database and modify data.

 mysqladmin -h server1.example.com -u root password yourrootsqlpassword

Reducing the MySQL resource requirements

I am running my system an old or resource constrained servers, therefore I try to optimise the configuration as much as possible.

 cp /etc/mysql/my.cnf /etc/mysql/my.cnf.original
 cp /usr/share/doc/mysql-server-5.0/examples/my-small.cnf /etc/mysql/my.cnf
 /etc/init.d/mysql restart

MySQL Backups

AutoMySQLBackup is a script that takes daily, weekly and monthly backups of your MySQL databases using 'mysqldump'.

Installing AutoMySQLBackup

 wget http://heanet.dl.sourceforge.net/sourceforge/automysqlbackup/automysqlbackup.sh.2.5 -O /usr/local/bin/automysqlbackup.sh
 chmod 700 /usr/local/bin/automysqlbackup.sh

Configuring AutoMySQLBackup

Now update the configuration to suit your needs.

 vi /usr/local/bin/automysqlbackup.sh

Find the settings below and change them to suit your preferences and configuration.

 # Username to access the MySQL server e.g. dbuser
 USERNAME=dbuser

 # Username to access the MySQL server e.g. password
 PASSWORD=password

 # List of DBNAMES for Daily/Weekly Backup e.g. "DB1 DB2 DB3"
 DBNAMES="all"

 # Backup directory location e.g /backups
 BACKUPDIR="/var/backups/mysql"

 # Mail setup
 # What would you like to be mailed to you?
 # - log   : send only log file
 # - files : send log file and sql files as attachments (see docs)
 # - stdout : will simply output the log to the screen if run manually.
 # - quiet : Only send logs if an error occurs to the MAILADDR.
 MAILCONTENT="quiet"

 # Email Address to send mail to? (user@domain.com)
 MAILADDR="root@yourserver.com"

Manually run /usr/local/bin/automysqlbackup.sh and check that all your databases have been correctly backed up to /var/backups/mysql. If everything looks OK, schedule a daily backup of your MySQL databases.

 ln -s /usr/local/bin/automysqlbackup.sh /etc/cron.daily/automysqlbackup

References

• http://members.lycos.co.uk/wipe_out/automysqlbackup/

Virtual Hostess Integration

This explains how to integrate automysqlbackup with Virtual Hostess, so that My SQL database backups for virtual hosts will automatically be copied to the appropriate virtual host home directory, under '/var/backups/mysql'.

 vi /usr/local/bin/automysqlbackup.sh

Update the POSTBACKUP parameter and shown.

 # Command run after backups (uncomment to use)
 POSTBACKUP="/usr/local/bin/virtual-mysql-backup.sh"

Now put create /usr/local/bin/virtual-mysql-backup.sh

 vi /usr/local/bin/virtual-mysql-backup.sh

$Id: MySQLServer,v 1.17 2008/08/12 12:07:28 martin Exp $

Wiki Index All Recent Edit Top