Admin/SetupGuides/MySQL setup on flexo-vm02

Setting up flexo-vm02 for MySQL Server

After Base Install Check for Debian Updates ->

apt-get update
apt-get install
apt-get upgrade

Install mysql ->

apt-get install mysql-server-4.1 mysql-client-4.1

Set mysql root password ->

The Method below is used beacause the mysqladmin only changes the password of root@localhost and not root@machinename

use mysql;
select User, Password, Host from user;
update user set password = PASSWORD(' ') where User='root';
FLUSH PRIVILEGES;
select User, Password, Host from user;

Allow mysql to accept incoming connections ->

vi /etc/mysql/my.cnf
Change line from: 
bind-address    = 127.0.0.1
to
bind-address    = 0.0.0.0

Restart mysql ->

/etc/init.d/mysql restart

Create Admin/AuthorisedRoot ->

(# vim -x /root/AuthorisedRoot)
See: https://wiki.skynet.ie/Admin/AuthorisedRoot

Setting up Scripts & Backup Directory

Place the mysql root password in root home dir. (This allows the scripts to run without passwords)

vi /root/.my.cnf and Insert these two lines:
[client]
password="mysql password"

chmod 400 /root/.my.cnf

Then Make Directories for scripts and backups:

mkdir /root/scripts
mkdir /root/backups

There are two main scripts commonly used: mysql_dbadduser.sh and mysql_fullbackup.sh and are placed in /root/scripts

mysql_dbadduser.sh

#!/bin/bash
#modified by Ewan Oughton 2002/11 to use optarg
#modified by Stephen Shirley (diamond) 2005/10/06 to work with debian standard mysql.
#modified by Stephen Burke (steviewdr) 2006/02/23 for new Skynet IP range

MYSQL=/usr/bin/mysql
MYSQL_ARGS="-u root"

while [ -z "$NEWUSER" ]; do
        echo -n "Please enter new user name: "
        read NEWUSER
done

while [ -z "$NEWPASSWORD" ]; do
        echo -n "Please enter new user's password: "
        stty -echo
        read NEWPASSWORD
        stty echo
        echo
done

echo Creating database, setting privileges and password.
$MYSQL $MYSQL_ARGS -e "CREATE DATABASE $NEWUSER;
GRANT ALL PRIVILEGES ON $NEWUSER.* TO $NEWUSER@'193.1.99.64/255.255.255.192' IDENTIFIED BY '$NEWPASSWORD' WITH GRANT OPTION;
FLUSH PRIVILEGES;"

mysql_fullbackup.sh

#!/bin/bash
# Created by Bryan Le Gear (fractal) 2005/10/12
# Modified by Stephen Burke (steviewdr) 2006/02/23 for flexo-vm02 setup
#
# This script must be run by dbadmin
# and this user must have the root password
# configured in ~/.my.cnf
#

COMPRESS=gzip
MYSQLDUMP=mysqldump
MYSQLDUMP_ARGS="-u root --all-databases"
CURRDATE=`date +%d-%m-%Y`
BACKUPDIR=/root/backups
OUTFILE=$BACKUPDIR/mysqlbackup_$CURRDATE.sql.gz

echo
echo "Backing up to:     " $OUTFILE
echo "Compressing using: " $COMPRESS
$MYSQLDUMP $MYSQLDUMP_ARGS | $COMPRESS > $OUTFILE

echo "Chmoding to:         400"
chmod 400 $OUTFILE

Extra: Logging? - Not Implemented

Perhaps for the first few days to see how things are going - logging could be done. If so agreed, this line needs to be added into /etc/mysql/my.cnf

log             = /var/log/mysql/mysql.log

Thats It. Now moving onto ../../Moving_MySQL_to_flexo-vm02

Logging of Connections to mysql.skynet.ie

If lots of ppl use persistant mysql db connections, this will eat up the available ones. Debian had a default of 150 connections. That was increased to 350. If this number is increased anymore - more setting such as timeouts should be implemented.

To log connections to the mysql db, the following script was created and added as a cronjob (not the prettiest):

#!/bin/bash
mysqladmin processlist | gzip > /var/tmp/mysqlconnlog$(date +%d%m%y_%H%M%S).gz

#Cronjob line (every 5 min):
*/5 * * * * /root/logmysqlconn.sh

last edited 2007-04-03 17:15:12 by 136