Open Tech Notes

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

Make a SSL Certificate

Step 1 make a temporary key file, which includes a passphrase.

      openssl genrsa -des3 -out temp.key 1024

Step 2 remove the passphrase from the key file
  
      openssl rsa -in temp.key -out www.domainname.com.key

Step 3

      a) make CSR file,then to buy a certificate

      openssl req -new -key www.domainname.com.key -out www.domainname.com.csr
   
       or

      b) generate CRT file directly

     openssl req -new -key www.domainname.com..key -x509 -out www.domainname.com.crt -days 999

Got error message when using SVN client

Using –with-apr  and –with-apr-util when compiling  SVN  AND the paths pointing to  apache’s  package, namely like this

./configure --with-apxs=/usr/local/apache2/bin/apxs \
–with-apr=/source/httpd-2.2.4/srclib/apr \
–with-apr-util=/source/httpd-2.2.4/srclib/apr-util

updated by May 21, 2007
######################################################
if you got error massage like this:

you can do this first:
./configure --with-apxs=/usr/local/apache2/bin/apxs

I don’t know why, it is really weird.
######################################################

Notice that folder /source is for my case, you have to change it to your folder where the Apache’s source resides