==== Background ====
If you have an application that updates the data tables heavily, you may want to relocate the data files to RAM to protect the CF card from wearing out quickly.
----
==== Required Files ====
**backup-mydb.sh script**
#!/bin/sh
# shell script to backup all mysql db to disk(/var/lib/mysql)
#
# last update: 2009-11-09
# edit to your needs
PROGNAME=$0
ROOTPW="0000"
DUMPFILE="/var/lib/mysql/alldb"
# protect dumpfile from error
set -e
mysqldump --add-drop-table --quote-names -u root -p$ROOTPW --all-databases | gzip -c > $DUMPFILE.tmp
sync
# shift the current dump to backup dump
[ -f $DUMPFILE.current ] && mv -f $DUMPFILE.current $DUMPFILE.bak
mv -f $DUMPFILE.tmp $DUMPFILE.current
sync
exit 0
**mysql init script**
#!/bin/bash
#
### BEGIN INIT INFO
# Provides: mysql
# Required-Start: $remote_fs $syslog mysql-ndb
# Required-Stop: $remote_fs $syslog mysql-ndb
# Should-Start: $network $named $time
# Should-Stop: $network $named $time
# Default-Start: 2 3 4 5
# Default-Stop: 0 1 6
# Short-Description: Start and stop the mysql database server daemon
# Description: Controls the main MySQL database server daemon "mysqld"
# and its wrapper script "mysqld_safe".
### END INIT INFO
#
set -e
set -u
${DEBIAN_SCRIPT_DEBUG:+ set -v -x}
entry_name=$0
test -x /usr/sbin/mysqld || exit 0
. /lib/lsb/init-functions
SELF=$(cd $(dirname $0); pwd -P)/$(basename $0)
CONF=/etc/mysql/my.cnf
MYADMIN="/usr/bin/mysqladmin --defaults-file=/etc/mysql/debian.cnf"
# priority can be overriden and "-s" adds output to stderr
ERR_LOGGER="logger -p daemon.err -t /etc/init.d/mysql -i"
# Safeguard (relative paths, core dumps..)
cd /
umask 077
# mysqladmin likes to read /root/.my.cnf. This is usually not what I want
# as many admins e.g. only store a password without a username there and
# so break my scripts.
export HOME=/etc/mysql/
## Fetch a particular option from mysql's invocation.
#
# Usage: void mysqld_get_param option
mysqld_get_param() {
/usr/sbin/mysqld --print-defaults \
| tr " " "\n" \
| grep -- "--$1" \
| tail -n 1 \
| cut -d= -f2
}
## Do some sanity checks before even trying to start mysqld.
sanity_checks() {
# check for config file
if [ ! -r /etc/mysql/my.cnf ]; then
log_warning_msg "$0: WARNING: /etc/mysql/my.cnf cannot be read. See README.Debian.gz"
echo "WARNING: /etc/mysql/my.cnf cannot be read. See README.Debian.gz" | $ERR_LOGGER
fi
# check for diskspace shortage
datadir=`mysqld_get_param datadir`
if LC_ALL=C BLOCKSIZE= df --portability $datadir/. | tail -n 1 | awk '{ exit ($4>4096) }'; then
log_failure_msg "$0: ERROR: The partition with $datadir is too full!"
echo "ERROR: The partition with $datadir is too full!" | $ERR_LOGGER
exit 1
fi
}
## Checks if there is a server running and if so if it is accessible.
#
# check_alive insists on a pingable server
# check_dead also fails if there is a lost mysqld in the process list
#
# Usage: boolean mysqld_status [check_alive|check_dead] [warn|nowarn]
mysqld_status () {
ping_output=`$MYADMIN ping 2>&1`; ping_alive=$(( ! $? ))
ps_alive=0
pidfile=`mysqld_get_param pid-file`
if [ -f "$pidfile" ] && ps `cat $pidfile` >/dev/null 2>&1; then ps_alive=1; fi
if [ "$1" = "check_alive" -a $ping_alive = 1 ] ||
[ "$1" = "check_dead" -a $ping_alive = 0 -a $ps_alive = 0 ]; then
return 0 # EXIT_SUCCESS
else
if [ "$2" = "warn" ]; then
echo -e "$ps_alive processes alive and '$MYADMIN ping' resulted in\n$ping_output\n" | $ERR_LOGGER -p daemon.debug
fi
return 1 # EXIT_FAILURE
fi
}
#
# main()
#
case "${1:-''}" in
'start')
# check if needed to restore to tmpfs
if [ ! -d /var/mysqldata/mysql ] ; then
# restore db
DUMPFILE="/var/lib/mysql/alldb"
if [ ! -f $DUMPFILE.current ] ; then
log_failure_msg "$DUMPFILE.current not found! Using the backup instead."
if [ ! -f $DUMPFILE.bak ] ; then
log_failure_msg "$DUMPFILE.bak also not found! Abort."
exit 1
else
src="$DUMPFILE.bak"
fi
else
src="$DUMPFILE.current"
fi
log_progress_msg "Restoring db "
/usr/bin/mysqld_safe --skip-grant-tables > /dev/null 2>&1 &
# wait max 14 seconds for it to start
for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
sleep 1
if mysqld_status check_alive nowarn ; then break; fi
log_progress_msg "."
done
zcat $src | mysql
mysqladmin shutdown
fi
sanity_checks;
# Start daemon
log_daemon_msg "Starting MySQL database server" "mysqld"
if mysqld_status check_alive nowarn; then
log_progress_msg "already running"
log_end_msg 0
else
/usr/bin/mysqld_safe > /dev/null 2>&1 &
# 6s was reported in #352070 to be too few when using ndbcluster
for i in 1 2 3 4 5 6 7 8 9 10 11 12 13 14; do
sleep 1
if mysqld_status check_alive nowarn ; then break; fi
log_progress_msg "."
done
if mysqld_status check_alive warn; then
log_end_msg 0
# Now start mysqlcheck or whatever the admin wants.
output=$(/etc/mysql/debian-start)
[ -n "$output" ] && log_action_msg "$output"
else
log_end_msg 1
log_failure_msg "Please take a look at the syslog"
fi
fi
# Some warnings
if $MYADMIN variables | egrep -q have_bdb.*YES; then
echo "BerkeleyDB is obsolete, see /usr/share/doc/mysql-server-5.0/README.Debian.gz" | $ERR_LOGGER -p daemon.info
fi
if [ -f /etc/mysql/debian-log-rotate.conf ]; then
echo "/etc/mysql/debian-log-rotate.conf is obsolete, see /usr/share/doc/mysql-server-5.0/NEWS.Debian.gz" | $ERR_LOGGER -p daemon.info
fi
;;
'stop')
# * As a passwordless mysqladmin (e.g. via ~/.my.cnf) must be possible
# at least for cron, we can rely on it here, too. (although we have
# to specify it explicit as e.g. sudo environments points to the normal
# users home and not /root)
# if called from system shutdown or reboot, do db backup first
set +e
echo $entry_name | egrep -q "rc[06].d"; r=$?
if [ "$r" = "0" ] ; then
log_progress_msg "Backing up all db "
/usr/local/bin/backup-mydb.sh
fi
log_daemon_msg "Stopping MySQL database server" "mysqld"
if ! mysqld_status check_dead nowarn; then
set +e
shutdown_out=`$MYADMIN shutdown 2>&1`; r=$?
set -e
if [ "$r" -ne 0 ]; then
log_end_msg 1
[ "$VERBOSE" != "no" ] && log_failure_msg "Error: $shutdown_out"
log_daemon_msg "Killing MySQL database server by signal" "mysqld"
killall -15 mysqld
server_down=
for i in 1 2 3 4 5 6 7 8 9 10; do
sleep 1
if mysqld_status check_dead nowarn; then server_down=1; break; fi
done
if test -z "$server_down"; then killall -9 mysqld; fi
fi
fi
if ! mysqld_status check_dead warn; then
log_end_msg 1
log_failure_msg "Please stop MySQL manually and read /usr/share/doc/mysql-server-5.0/README.Debian.gz!"
exit -1
else
log_end_msg 0
fi
;;
'restart')
set +e; $SELF stop; set -e
$SELF start
;;
'reload'|'force-reload')
log_daemon_msg "Reloading MySQL database server" "mysqld"
$MYADMIN reload
log_end_msg 0
;;
'status')
if mysqld_status check_alive nowarn; then
log_action_msg "$($MYADMIN version)"
else
log_action_msg "MySQL is stopped."
exit 3
fi
;;
*)
echo "Usage: $SELF start|stop|restart|reload|force-reload|status"
exit 1
;;
esac
----
==== Steps ====
Put ''backup-mydb.sh'' in ''/usr/local/bin''
Edit (set ROOTPW, ...etc) and run the ''backup-mydb.sh'' script once:
# /usr/local/bin/backup-mydb.sh
Create mount point for new datadir:
# mkdir /var/mysqldata
Edit /etc/fstab , add lines:
# for mysql:
tmpfs /var/mysqldata tmpfs defaults,size=16M 0 2
Adjust size to your needs.
Test mounting:
# mount /var/mysqldata
And
# df -h
You will see something like this line in the output:
tmpfs 16M 1.2M 15M 8% /var/mysqldata
Now, shutdown mysql:
# invoke-rc.d mysql stop
Edit ''/etc/mysql/my.cnf'', set:
datadir = /var/mysqldata
And replace ''/etc/init.d/mysql'' with the modified version.
(the modified one is based on 5.0.51a-24+lenny2)
start mysql:
# invoke-rc.d mysql start
The init script will automatically restore all data files to ''/var/mysqldata''
You can also run ''backup-mydb.sh'' by cron periodically.
In case of normal system shutdown, the current data files is auto backed up.
When the system restarts, the data files will be auto restored.
In case of system crash(eg. loss of power), data files at the last ''backup-mydb.sh'' run will be auto restored after restart.