Backup MySQL Database

Full backup:
echo "============================================================"
echo "Backup $dbName database to $dbFile"

# The following commands will be put to the beginning of the database dump file.
echo " 1. Creating header.tmp temporary file"
echo "SET AUTOCOMMIT = 0;SET FOREIGN_KEY_CHECKS=0;" > $dbDir/header.tmp

# Dumping the database.
echo " 2. Creating $dbName.tmp temporary file"
mysqldump --opt --user=$dbUSER --password=$dbPASSWORD $dbName > $dbDir/$dbName.tmp

# The following commands will be put to the end of the database dump file.
echo " 3. Creating tailer.tmp temporary file"
echo "SET FOREIGN_KEY_CHECKS = 1;COMMIT;SET AUTOCOMMIT = 1;" > $dbDir/tailer.tmp

echo " 4. Create today's backup file : $dbFile"
cat $dbDir/header.tmp $dbDir/$dbName.tmp $dbDir/tailer.tmp > $dbDir/$dbFile

Backup Schema Only:
$ mysqldump --no-data --user=$dbUSER --password=$dbPASSWORD $dbName > Schema20081003.sql

CONCAT Strings in Oracle v.s. MySQL

Oracle uses the CONCAT(string1, string2) function or the || operator. The Oracle CONCAT function can only take two strings so the above example would not be possible as there are three strings to be joined (FirstName, ' ' and LastName). To achieve this in Oracle we would need to use the || operator which is equivalent to the + string concatenation operator in SQL Server / Access.

-- Oracle
SELECT FirstName || ' ' || LastName As FullName FROM Customers

MySQL uses the CONCAT(string1, string2, string3...) function. The above example would appear as follows in MySQL

-- MySQL
SELECT CONCAT(FirstName, ' ', LastName) As FullName FROM Customers

bash examples

Calulation in bash
bash$
bash$ echo "1.5 * 2" | bc -l
3.0
bash$ echo "1/3" | bc -l
.33333333333333333333
bash$
bash$ echo "scale=3; 10/3" | bc
3.333


Change Prompt
$
$ PS1="myPrompt> "
myPrompt>
myPrompt>

Get Create Table command from MySQL
$ sql "show create table my_table\G" | sed -e '1,2d;s/Create Table: //'
Note: sed -e '1,2d' removes the first 2 lines.

if statement examples

Use Regular Expression in if in bash script
if echo "4567" | grep -q "^4"; then
echo "4567 is started with a 4."
elif echo "4567" | grep -q "^A"; then
echo "4567 is started with a A."
else
echo "4567 is NOT started with a 4 or A."
fi

Float/Real Number Comparison in if statement
if [ $( echo "5 > 10" | bc ) -eq 1 ]; then
echo 5 is greater than 10.
else
echo 5 is NOT greater than 10.
fi
=> 5 is NOT greater than 10.



case statement examples
case $1 in
-s) sec=1; shift;;
-m) sec=60; shift;;
-h) sec=3600; shift;;
-d) sec=86400; shift;;
*) sec=86400;;
esac

while statement examples
C-style while loop:

#!/bin/bash
# wh-loopc.sh: Count to 10 in a "while" loop.
LIMIT=10
a=1
while [ "$a" -le $LIMIT ]
do
echo -n "$a "
let "a+=1"
done           # No surprises, so far.
echo; echo
# +=================================================================+

# Now, repeat with C-like syntax.

# Double parentheses permit space when setting a variable, as in C.
((a = 1))      # a=1

# Double parentheses, and no "$" preceding variables.
while (( a <= LIMIT ))
do  
 echo -n "$a "   
 ((a += 1)) 
 # Double parentheses permit incrementing a variable with C-like syntax.
done
exit 0 

statement examples

Add new examples here....

mysql examples

Create a temporary table:
CREATE TABLE TMP_TEST SELECT * FROM MY_TEST;

Populate a temporary table:
insert into sms_dailyrsi select * from tmp_dailyrsi;

MINUS alternative:
select *
from tmp_test left join my_test
on tmp_test.pk1 = my_test.pk1 and tmp_test.pk2 = my_test.pk2
where my_test.pk1 is NULL and my_test.pk2 is NULL;