Open Tech Notes

MySQL Could not start on Apache Friends

Today, I setup security for  Apache Friends package on one of my CentOS (5.0 final) servers. After I followed the command:

./lampp security

setup password for the lampp demo web page
turn off access Mysql via the network

Then, the Mysql server could not start. Even I use command:

./lampp startmysql

it said

Mysql Starting and exit to command line.

(note: the apache friends package version is 1.63a)

So, I went to apachefriends.org to find the answer and unfortunately I did not find anything that can help. I decided to upgrade it to the latest version(1.63b) because this server is currently not in service. I don’t worry about the data lose. Upgrading was smooth, but it did not work too.

Anyway, the last result, I removed the whole folder /lampp from /opt and re-installed  apache friends version 1.63b. It worked fine.

One thing I need to remember: Don’t forget stop lampp before remove the folder /lampp, otherwise, it will waste a lot of time to kill some process manually.

Improving MySQL database backup script

The original script is here

Reason: As usual, I checked the database backup file and found the size was really small. It meant the backup script had some problems. After then, I was told that the database’s root password had been changed. Oh, that was the culprit!

Now that I know what the problem is, how can I improve it?

The first thing comes to my mind is: I must add a flow controller statement which will tell me (by email)  if the backup process is successful.  But what if I read the email too late and the backup time is missed? Fortunately, the password is stored in a configuration file. Whenever the DB admin changes the password, she has to change the configuration file. So the better solution is: read the password from the configuration file instead of hard code in the script file.

MySQL database backup script

#!/bin/bash

#=============================================================================
# MySQL Backup Script (file name: dbbackup)
# Ver. 0.2
# Author Jason Qi (email@yahoo.com)
#
# This script works under CentOS with Appache Friends.
# Before you start, please create folder
#   /opt/lampp\dbbackup
#     and
#   /opt/lampp/htdocs/cams2/dbbackup
# Put this file into /opt/lampp/bin and chmod +x dbbackup.
# Create dbbackupmsg file and put it into /opt/lampp/bin. The content inside
# this file is "Backup is done"
#  
# The last step is create a crontab
#   crontab -e
#   30 3 * * * /opt/lampp/bin/dbbackup
#=============================================================================
#
# Please change the params to fit the system needs.
#
#=============================================================================

USER="root";
PASSWORD="password";
DATABASE="dbname";
EMAIL="email@yahoo.com";
MYSQLDUMP="/opt/lampp/bin/mysqldump";

MPATH="/opt/lampp/dbbackup";
CPATH="/opt/lampp/htdocs/cams2/dbbackup";

MDATE=`/bin/date "+%Y-%m-%d-%Hh%Mm"`;
CDATE=`/bin/date "+%A"`;

MDUMP="$DATABASE-$MDATE";
CDUMP="$DATABASE-$CDATE";

EXT1=".sql";
EXT2=".tar";
EXT3=".gz";

#=============================================================================
# Change log
#=============================================================================
# VER 0.2 - 2007-04-28 Revise thid document by changing all "\" to "/" in pathname
#                      Add crontab command to this document
# VER 0.1 - 2007-04-15
#   Initial Release
#=============================================================================

$MYSQLDUMP –add-drop-table –user=$USER –password=$PASSWORD $DATABASE >> "$DUMP$EXT1";

/bin/tar -cf $MPATH/$DATABASE-$MDATE$EXT2 $DUMP$EXT1;
/bin/gzip -f $MPATH/$DATABASE-$MDATE$EXT2;

/bin/tar -cf $CPATH/$DATABASE-$CDATE$EXT2 $DUMP$EXT1;
/bin/gzip -f $CPATH/$DATABASE-$CDATE$EXT2;

/bin/rm -f $DUMP$EXT1;

cat "dbbackupmsg" | mail -s $MDATE-"Backup is done" $EMAIL;

“Rename” database name in MySQL

Currently, there is no method to rename database name in MySQL, but you could easily use  mysqldump command backup  your database and then read it back in to a new database.

Step 1 Dump the database structure and content:

  mysqldump -u username -p dbname >> dump.sql

Step 2 Create a new empty database.
 
  mysql -u username -p dbname
  mysql> create database newdbname;

Step 3 Reload the database into a new database with a different name:

  mysql -u username -p newdbname < dump.sql