#!/bin/csh -f
# mysqldb
# ------------------------------------------------------------------------------
# Shell script to start, stop, and manage MySQL DB server.
# ------------------------------------------------------------------------------
# Usage: See Usage section below or run with -h or --help option to see usage.
# Assumptions:
# Effects:
# - Performs operations on the MySQL DB server.
# Notes:
# Implementation Notes:
# Portability Issues:
# Revision History:
# $Log$
# ------------------------------------------------------------------------------
if ("$1" == "-h" || "$1" == "--help") then
echo "Usage:"
echo " $0:t Start MySQL client"
echo " $0:t start Start MySQL server"
echo " $0:t stop Stop MySQL server"
echo " $0:t ping Ping the server to see if it is alive"
echo " $0:t status Detailed status of the server"
echo " $0:t conn Show connection counts"
echo " $0:t show Show names of DBs"
echo " $0:t show db_name Show tables of specified DB"
echo " $0:t users db_name Show defined users of specified DB"
echo " $0:t desc obj_name db_name"
echo " Describe specified object of specified DB,"
echo " defaulting to information_schema DB."
echo " $0:t schema db_name"
echo " Show schema for specified DB."
echo " $0:t admin Show help for MySQL Admin client"
echo " $0:t admin params Start MySQL Admin client with specified params"
echo " $0:t exec db_name statement "
echo " Execute specified SQL statement against "
echo " specified DB"
echo " $0:t dump db_name Dump DDL and INSERTs of specified DB to stdout."
exit 1
endif
if ($#argv == 0) then
settitle "MySQL root"
mysql -u root -p${MYSQL_PW}
settitle -h
else if ($#argv == 1) then
if ("$1" == "start") then
sudo mysqld_safe --console
else if ("$1" == "stop") then
mysqladmin -u root -p${MYSQL_PW} shutdown
else if ("$1" == "ping") then
mysqladmin -u root -p${MYSQL_PW} ping
else if ("$1" == "status") then
mysqladmin -u root -p${MYSQL_PW} version status processlist extended-status variables | more
else if ("$1" == "show") then
mysqlshow -u root -p${MYSQL_PW}
else if ("$1" == "admin") then
mysqladmin -u root -p${MYSQL_PW} | more
else if ("$1" == "conn") then
shift
mysql -u root -p${MYSQL_PW} -t -vvv -e "show status like '%onnect%'" $*:q | more
else
# Pass 1 other param to mysql so that we can tolerate single
# mysql options like --max_allowed_packet=100M
mysql -u root -p${MYSQL_PW} $*
endif
else if ($#argv >= 2) then
if ("$1" == "show") then
shift; mysqlshow -u root -p${MYSQL_PW} $*:q
else if ("$1" == "users") then
shift; mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from user" $*:q | more
else if ("$1" == "admin") then
shift; mysqladmin -u root -p${MYSQL_PW} $*:q
else if ("$1" == "exec") then
mysql -u root -p${MYSQL_PW} -t -vvv -e "$3 $4 $5 $6 $7 $8 $9" $2:q | more
else if ("$1" == "dump") then
shift; mysqldump -u root -p${MYSQL_PW} --order-by-primary --complete-insert --skip-extended-insert --routines --triggers --databases $*:q | more
# --order-by-primary = Sorted is easier to diff w/other dumps
# --complete-insert = Include column names in INSERTS
# --skip-extended-insert = Only one database row per INSERT
# --routines = Stored procedures/functions also
# --triggers = Triggers also (on by default anyhow)
# --databases $*:q = Generate CREATE DATABASE, DROP DATABASE, USE
# statements for each specified database.
else if ("$1" == "desc") then
set DB_NAME = $3:q
if ("$DB_NAME" == "") set DB_NAME = "information_schema"
mysql -u root -p${MYSQL_PW} -t -vvv -e "describe $DB_NAME.$2" | more
else if ("$1" == "schema") then
echo ""
echo "*************************"
echo "Schemas"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.schemata where '$2' like schema_name" | more
echo ""
echo "*************************"
echo "Schema_Privileges"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.schema_privileges where '$2' like table_schema" | more
echo ""
echo "*************************"
echo "mysql.db"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.db" | more
echo ""
echo "*************************"
echo "Tables"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.tables where '$2' like table_schema" | more
echo ""
echo "*************************"
echo "Table_Constraints"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.table_constraints where '$2' like table_schema" | more
echo ""
echo "*************************"
echo "Key_Column_Usage"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.key_column_usage where '$2' like table_schema" | more
echo ""
echo "*************************"
echo "Table_Privileges"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.table_privileges where '$2' like table_schema" | more
echo ""
echo "*************************"
echo "mysql.Tables_Priv"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.tables_priv" | more
echo ""
echo "*************************"
echo "Columns"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.columns where '$2' like table_schema" | more
echo ""
echo "*************************"
echo "Column_Privileges"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.column_privileges where '$2' like table_schema" | more
echo ""
echo "*************************"
echo "mysql.Columns_Priv"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.columns_priv" | more
echo ""
echo "*************************"
echo "Views"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.views where '$2' like table_schema" | more
echo ""
echo "*************************"
echo "Routines"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.routines where '$2' like routine_schema" | more
echo ""
echo "*************************"
echo "mysql.Func"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.func" | more
echo ""
echo "*************************"
echo "mysql.Proc"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.proc" | more
echo ""
echo "*************************"
echo "mysql.Procs_Priv"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.procs_priv" | more
echo ""
echo "*************************"
echo "Triggers"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.triggers where '$2' like trigger_schema" | more
echo ""
echo "*************************"
echo "mysql.Host"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.host" | more
echo ""
echo "*************************"
echo "mysql.User"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from mysql.user" | more
echo ""
echo "*************************"
echo "User_Privileges"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.user_privileges" | more
echo ""
echo "*************************"
echo "Statistics"
echo "*************************"
mysql -u root -p${MYSQL_PW} -t -vvv -e "select * from information_schema.statistics where '$2' like table_schema" | more
else
# Pass 2+ other params to mysql so that we can tolerate multiple
# mysql options like --max_allowed_packet=100M
mysql -u root -p${MYSQL_PW} $*
endif
endif
exit 0