#!/bin/bash
# Shell script to backup MySql database
# To backup Nysql databases file to /backup dir and later pick up by your
# script. You can skip few databases from backup too.

MyUSER="SET-MYSQL-USER-NAME"     # USERNAME
MyPASS="SET-PASSWORD"       # PASSWORD
MyHOST="localhost"          # Hostname

# Linux bin paths, change this if it can't be autodetected via which command
MYSQL="$(which mysql)"
MYSQLDUMP="$(which mysqldump)"
CHOWN="$(which chown)"
CHMOD="$(which chmod)"
GZIP="$(which gzip)"

# Backup Dest directory, change this if you have someother location
DEST="/backup"

# Main directory where backup will be stored
MBD="$DEST/mysql"

# Get hostname
HOST="$(hostname)"

# Get data in dd-mm-yyyy format
NOW="$(date +"%d-%m-%Y")"

# File to store current backup file
FILE=""
# Store list of databases
DBS=""

# DO NOT BACKUP these databases
IGGY="test"

[ ! -d $MBD ] && mkdir -p $MBD || :

# Only root can access it!
$CHOWN 0.0 -R $DEST
$CHMOD 0600 $DEST

# Get all database list first
DBS="$($MYSQL -u $MyUSER -h $MyHOST -p$MyPASS -Bse 'show databases')"

for db in $DBS
do
    skipdb=-1
    if [ "$IGGY" != "" ];
    then
	for i in $IGGY
	do
	    [ "$db" == "$i" ] && skipdb=1 || :
	done
    fi

    if [ "$skipdb" == "-1" ] ; then
	FILE="$MBD/$db.$HOST.$NOW.gz"
	# do all inone job in pipe,
	# connect to mysql using mysqldump for select mysql database
	# and pipe it out to gz file in backup dir :)
        $MYSQLDUMP -u $MyUSER -h $MyHOST -p$MyPASS $db | $GZIP -9 > $FILE
    fi
done

#netstat -l
tcp        0      0 127.0.0.1:3306          0.0.0.0:*
LISTEN     1768/mysqld

#bind-address           = 127.0.0.1

#netstat -l
tcp        0      0 0.0.0.0:3306            0.0.0.0:*
LISTEN     14134/mysqld

mysql> update user set password = old_password('passwordhere') where user = 'phpbb'; flush privileges;
Query OK, 1 row affected (0.04 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select user, password from user; +-------+-------------------------------------------+ | user | password | +-------+-------------------------------------------+ | root | *BA3E2F47E409A6ABA83D219D70631A02FE28539E | | root | *BA3E2F47E409A6ABA83D219D70631A02FE28539E | | | | | | | | phpbb | 09a6e3834c6d11c9 | +-------+-------------------------------------------+ 5 rows in set (0.00 sec)

/etc/init.d/mysql* restart

mysql -e "update user set password = old_password('newpassword') where user = 'root'" mysql

/etc/init.d/mysql* restart

03 Jul 2007 MySQL via PHP Cheat Sheet

Connect to MySQL and to the database

// Connect to MySQL

$connect = mysql_connect("localhost", "mysqluser", "userpassword") or die(mysql_error());

// Select the database

mysql_select_db("databasename", $connect) or die(mysql_error());

PHP Manual Reference
us2.php.net/manual/en/function.mysql-connect.php

Close connection

// Use the variable that was used to connect to MySQL

mysql_close($connect) or die(mysql_error());
// PHP Manual Reference: us2.php.net/manual/en/function.mysql-close.php

Create a database

mysql_create_db("dbname") or die(mysql_error());

// PHP Manual Reference: us2.php.net/manual/en/function.mysql-create-db.php

// W3Schools Reference: www.w3schools.com/sql/sql_create.asp

Delete (drop) a database

$query_drop_db = "DROP DATABASE dbname";

mysql_query($query_drop_db, $connect) or die(mysql_error());

// PHP Manual Reference: us2.php.net/manual/en/function.mysql-drop-db.php

// W3Schools Reference: www.w3schools.com/sql/sql_drop.asp

Create a table

/* If you don't want your script return an error if the table

already exists, you can replace "CREATE TABLE" by

"CREATE TABLE IF NOT EXISTS" which creates the table only

if the table does not already exist, without returning any errors. */

$table = "CREATE TABLE tablename (

table_id int(11) NOT NULL auto_increment,

table_default int(11) NOT NULL default 1,

table_varcharfield varchar(255) NOT NULL,

table_textfield text NOT NULL,

PRIMARY KEY (table_id)

)";

// Actually create the new table in the database.

$result = mysql_query($table) or die (mysql_error());

// W3Schools Reference: www.w3schools.com/sql/sql_create.asp

Select data

// Select specific fields in the table $query_select = "SELECT tablefield, tablefield2 FROM tablename WHERE tablefield = '" .$variable. "' AND tablefield2 = '" .$variable2. "'";

$result_select = mysql_query($query_select) or die(mysql_error());

// W3Schools Reference: www.w3schools.com/sql/sql_select.asp

// W3Schools Reference ("where" clause): www.w3schools.com/sql/sql_where.asp

// Select all the fields in the table $query_select = "SELECT * FROM tablename WHERE tablefield = '" .$variable. "' AND tablefield2 = '" .$variable2. "'";

$result_select = mysql_query($query_select) or die(mysql_error());// W3Schools Reference: www.w3schools.com/sql/sql_select.asp

Select data: Retrieve and display

// Retrieve as several variables
$row_select = mysql_fetch_array($result_select);
extract($row_select);
echo $tablefield;
// Retrieve as a loop
while ($row_select = mysql_fetch_array($result_select)) {
extract($row_select);
echo $tablefield;
}

Insert data

$query_insert = "INSERT INTO tablename(tablefield, tablefield2) VALUES('" .$variable. "', '" .$variable2. "')";
$result_insert = mysql_query($query_insert) or die(mysql_error());

// PHP Manual Reference: us2.php.net/manual/en/function.mysql-insert-id.php
// W3Schools Reference: www.w3schools.com/sql/sql_insert.asp

Insert data: Insert multiple rows

$query_insert = "INSERT INTO tablename(tablefield, tablefield2) VALUES ('" .$variable. "', '" .$variable2. "'),
('" .$variable. "', '" .$variable2. "'),
('" .$variable. "', '" .$variable2. "')";
$result_insert = mysql_query($query_insert) or die(mysql_error());

Update data

$query_update = "UPDATE tablename SET tablefield = '" .$variable. "', tablefield2 = '" .$variable. "' WHERE tablefield3 = '" .$variable3. "'";
$result_update = mysql_query($query_update) or die(mysql_error());

// W3Schools Reference: www.w3schools.com/sql/sql_update.asp

Delete data

// Delete a row from a table
$query_delete = "DELETE FROM tablename WHERE tablefield = '" .$variable. "'";
$result_delete = mysql_query($query_delete) or die(mysql_error());

// W3Schools Reference: http://www.w3schools.com/sql/sql_delete.asp

When you create database tables, you need field types. Certain field types are for certain reasons. Here is a small list of some of the more commonly used field types.

Field Name	Description
int(length)	Integer field that can range from negative 2147483648 to positive 2147483647. length can limit the length of the integer.
int(length) unsigned	Same as int, but can store up to 4294967295, but no negative numbers.
char(length)	Any character can be in this field, but the field is at a static length.
varchar(length)	Any character can be in this field. The field value length can be from 1 to 255 characters. length is the maximum length it will store.
text	Any characters can be in this field. The maximum size the value can be is 65536 characters.
decimal(length, decimal)	A field for decimals, where length defines the length of the decimal (as a whole), and the decimal parameter is the number of decimal places.
datetime	Field that stores both date and time. Stores as yyyy-mm-dd hh:mm:ss.

Tags: Database, MySQL, PHP

Posted by Hans-Henry Jakobsen

#!/bin/bash
# Shell script to restart MySQL server if it is killed or not working
# due to ANY causes.
# When script detects mysql is not running (it basically sends ping request
# to MySQL) it try to start using /etc/init.d/mysql script; and it sends an
# email to user indicating the status.
# This script must be run from Cron Job so that it can monitor mysql server.
# For more info visit following url:
# http://www.cyberciti.biz/nixcraft/vivek/blogger/2005/08/linux-mysql-server-monitoring.html
# --------------------------------------------------------------------------
# Copyright (C) 2005 nixCraft project 
# This script is licensed under GNU GPL version 2.0 or above
# -------------------------------------------------------------------------
# This script is part of nixCraft shell script collection (NSSC)
# Visit http://bash.cyberciti.biz/ for more information.
# -------------------------------------------------------------------------

# mysql root/admin username
MUSER="root"
# mysql admin/root password
MPASS="SET-ROOT-PASSWORD"
# mysql server hostname
MHOST="localhost"
#Shell script to start MySQL server i.e. path to MySQL daemon start/stop script.
# Debain uses following script, need to setup this according to your UNIX/Linux/BSD OS.
MSTART="/etc/init.d/mysql start"
# Email ID to send notification
EMAILID="notification@somewhere-corp.com"
# path to mail program
MAILCMD="$(which mail)"
# path mysqladmin
MADMIN="$(which mysqladmin)"

#### DO NOT CHANGE anything BELOW ####
MAILMESSAGE="/tmp/mysql.fail.$$"

# see if MySQL server is alive or not
# 2&1 could be better but i would like to keep it simple and easy to
# understand stuff :)
$MADMIN -h $MHOST -u $MUSER -p${MPASS} ping 2>/dev/null 1>/dev/null
if [ $? -ne 0 ]; then
	echo "" >$MAILMESSAGE
	echo "Error: MySQL Server is not running/responding ping request">>$MAILMESSAGE
	echo "Hostname: $(hostname)" >>$MAILMESSAGE
	echo "Date & Time: $(date)" >>$MAILMESSAGE
	# try to start mysql
	$MSTART>/dev/null
	# see if it is started or not
	o=$(ps cax | grep -c ' mysqld$')
	if [ $o -eq 1 ]; then
		sMess="MySQL Server MySQL server successfully restarted"
	else
		sMess="MySQL server FAILED to restart"
	fi
	# Email status too
	echo "Current Status: $sMess" >>$MAILMESSAGE
	echo "" >>$MAILMESSAGE
	echo "*** This email generated by $(basename $0) shell script ***" >>$MAILMESSAGE
	echo "*** Please don't reply this email, this is just notification email ***" >>$MAILMESSAGE
	# send email
	$MAILCMD -s "MySQL server" $EMAILID < $MAILMESSAGE
else # MySQL is running :) and do nothing
	:
fi
# remove file
rm -f $MAILMESSAGE