|
|
If you install the ready to use MySql package (binaries from Red Hat or Debian), you should not have any trouble to configure
MySql. But if you compile MySql with the "--prefix" option (for configure), then things become messy: the problem
comes from the file "my.cnf". I tried to change the content of this file but it did not work...
Make sure you ran the script bin/mysql_install_db before you start the server for the first time.
So I have read all the scripts and I finally decided to write my own "start / stop" script.
The configuration file "my.cnf" is read by the binary "bin/my_print_defaults". This program takes the content of
the file and convert it into mysqld's command line options.
|
my.cnf |
[client]
port = 3307
socket = /export/users/dbeurive/asynchronous_accounting/mysql/tmp/mysql.sock
[mysqld]
port = 3307
socket = /export/users/dbeurive/asynchronous_accounting/mysql/tmp/mysql.sock
|
This configuration allows me to run a MySql server on a specific port (3307) and with a specific UNIX socket (used for local
connexions). If you aply the program "my_print_defaults" on the previous file, you get the following output:
|
./my_print_defaults --config-file=../etc/my.cnf mysqld |
--port=3307
--socket=/export/users/dbeurive/asynchronous_accounting/mysql/tmp/mysql.sock
|
This is pretty simple... Writing a specific "start / stop" script is pretty simple.
|
File "conf" |
########################################################################################
# Configuration file for the start / stop scripts used by the MySql server and client. #
########################################################################################
########################################################################################
# MYSQL_BASE_DIR Absolute path to the MySql base directory. #
# MYSQL_DATA_DIR Absolute path to the directory used by the MySql server to #
# store the database files. #
# MYSQL_BIN_DIR Absolute path to the directory where to find MySql binaries. #
# MYSQL_PID_FILE Absolute path to the MySql PID file. #
# MYSQL_CNF_FILE Absolute path to the MySql '.cnf' file. #
# MYSQL_PRINT_DEFAULTS Absolute path to the MySql utility 'my_print_defaults'. #
# MYSQL_SERVER Absolute path to the MySql server binary. #
# MYSQL_CLIENT Absolute path to the MySql client bunary. #
########################################################################################
MYSQL_BASE_DIR=/export/users/dbeurive/asynchronous_accounting/mysql
MYSQL_DATA_DIR=/export/users/dbeurive/asynchronous_accounting/mysql/var
MYSQL_BIN_DIR=/export/users/dbeurive/asynchronous_accounting/mysql/bin
MYSQL_PID_FILE=/export/users/dbeurive/asynchronous_accounting/mysql/tmp/mysql.pid
MYSQL_CNF_FILE=/export/users/dbeurive/asynchronous_accounting/mysql/etc/my.cnf
MYSQL_PRINT_DEFAULTS=/export/users/dbeurive/asynchronous_accounting/mysql/bin/my_print_defaults
MYSQL_SERVER=/export/users/dbeurive/asynchronous_accounting/mysql/libexec/mysqld
MYSQL_CLIENT=/export/users/dbeurive/asynchronous_accounting/mysql/bin/mysql
|
|
File "mysql.server" |
#!/bin/bash
########################################################################################
# Few functions used to make the code easier to read. #
# #
# + is_absolute_path(): Make sure that the first argument is an absolute path. #
# Returns 1 if the first argument is an absolute path. #
# Returns 0 if the first argument is not an absolute path. #
# #
# + usage(): Print the usage. #
########################################################################################
EXE=`basename "${0}"`
function is_absolute_path()
{
aux="`echo "${1}" | sed -e '/^[^\/]/d'`"
if [ -z $aux ]
then
return 0
fi
return 1
}
function usage()
{
echo " "
echo "usage:"
echo " "
echo " ${EXE} (start | stop)"
echo " "
exit 1
}
########################################################################################
# CONFIG Absolute path to the file that contains the configuration value for this #
# script. #
########################################################################################
CONFIG=/export/users/dbeurive/asynchronous_accounting/mysql/etc/conf
is_absolute_path "${CONFIG}"
if [ $? -eq 0 ]
then
echo "Sorry, '${CONFIG}' not an absolute path. Please set variable CONFIG accurately"
exit 1
fi
. $CONFIG
########################################################################################
# Sometimes you'd better check who you are #
########################################################################################
echo "'${EXE} ${1}' executed on host '`hostname`' by user '`id`'"
########################################################################################
# First make sure all paths are absolutes. #
########################################################################################
is_absolute_path "${MYSQL_BASE_DIR}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_BASE_DIR}' not an absolute path. Please set variable MYSQL_BASE_DIR accurately"
exit 1
fi
is_absolute_path "${MYSQL_DATA_DIR}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_DATA_DIR}' not an absolute path. Please set variable MYSQL_DATA_DIR accurately"
exit 1
fi
is_absolute_path "${MYSQL_BIN_DIR}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_BIN_DIR}' not an absolute path. Please set variable MYSQL_BIN_DIR accurately"
exit 1
fi
is_absolute_path "${MYSQL_PID_FILE}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_PID_FILE}' not an absolute path. Please set variable MYSQL_PID_FILE accurately"
exit 1
fi
is_absolute_path "${MYSQL_CNF_FILE}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_CNF_FILE}' not an absolute path. Please set variable MYSQL_CNF_FILE accurately"
exit 1
fi
is_absolute_path "${MYSQL_PRINT_DEFAULTS}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_PRINT_DEFAULTS}' not an absolute path. Please set variable MYSQL_PRINT_DEFAULTS accuratel
y"
exit 1
fi
is_absolute_path "${MYSQL_SERVER}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_SERVER}' not an absolute path. Please set variable MYSQL_SERVER accurately"
exit 1
fi
########################################################################################
# Make sure that the configuration file exists #
########################################################################################
if [ ! -f "${MYSQL_CNF_FILE}" ]
then
echo "Sorry, can not find file '${MYSQL_CNF_FILE}'. Please set MYSQL_CNF_FILE accurately"
exit 1
fi
########################################################################################
# Everything seems OK ... #
########################################################################################
case "${1}" in
"start" )
echo "Starting MySql server:"
if [ -f ${MYSQL_PID_FILE} ]
then
echo "WARNING: File ${MYSQL_PID_FILE} already exists."
echo " "
echo "The MySql server should be running."
exit 1
fi
args="--basedir=${MYSQL_BASE_DIR} --datadir=${MYSQL_DATA_DIR} --pid-file=${MYSQL_PID_FILE}"
for i in `$MYSQL_PRINT_DEFAULTS --config-file=$MYSQL_CNF_FILE mysqld`
do
args="${args} ${i}"
done
echo "Arguments: ${args}"
echo ""
$MYSQL_SERVER $args &
exit 0
;;
"stop" )
echo "Stoping publicity server:"
if [ ! -f ${PUB_PID} ]
then
echo "WARNING: File ${MYSQL_PID_FILE} does not exist."
echo " "
echo "Please check if the MySql server is running."
exit 1
fi
pid=`cat ${MYSQL_PID_FILE}`
echo "Kill process which PID is: ${pid}"
echo ""
kill ${pid}
rm -f MYSQL_PID_FILE
if [ -f ${MYSQL_PID_FILE} ]
then
echo "ERROR: File ${MYSQL_PID_FILE} still exists. Can not remove it!"
exit 1
fi
exit 0
;;
* )
echo "Invalid option '${1}' ??? -- Abort"
usage
;;
esac
|
The following script starts the MySql client using options defined in the mysql client/server configuration file (my.cnf).
Specific MySql options can be given using the command line.
|
File "conf" |
#!/bin/bash
########################################################################################
# #
# Special Start script for the client written by Denis BEURIVE #
# #
# Configuration files are: #
# #
# o /home/users/dbeurive/BIN/mysql/etc/mysql.conf #
# o /home/users/dbeurive/BIN/mysql/etc/my.cnf #
# #
# Note: Make sure to set the configuration variable 'CONFIG' to the right value (see #
# later in this code). #
# #
########################################################################################
########################################################################################
# Few functions used to make the code easier to read. #
# #
# + is_absolute_path(): Make sure that the first argument is an absolute path. #
# Returns 1 if the first argument is an absolute path. #
# Returns 0 if the first argument is not an absolute path. #
# #
# + usage(): Print the usage. #
########################################################################################
EXE=`basename "${0}"`
function is_absolute_path()
{
aux="`echo "${1}" | sed -e '/^[^\/]/d'`"
if [ -z $aux ]
then
return 0
fi
return 1
}
function usage()
{
echo " "
echo "usage:"
echo " "
echo " ${EXE} [mysql_option] [mysql_option] ..."
echo " "
echo "See man page for mysql for specific mysql's options."
echo " "
exit 1
}
########################################################################################
# CONFIG Absolute path to the file that contains the configuration value for this #
# script. #
########################################################################################
CONFIG=/home/users/dbeurive/BIN/mysql/etc/mysql.conf
is_absolute_path "${CONFIG}"
if [ $? -eq 0 ]
then
echo "Sorry, '${CONFIG}' not an absolute path. Please set variable CONFIG accurately"
exit 1
fi
. $CONFIG
########################################################################################
# Sometimes you'd better check who you are #
########################################################################################
echo "'${EXE} ${1}' executed on host '`hostname`' by user '`id`'"
########################################################################################
# First make sure all paths are absolutes. #
########################################################################################
is_absolute_path "${MYSQL_BASE_DIR}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_BASE_DIR}' not an absolute path. Please set variable MYSQL_BASE_DIR accurately"
exit 1
fi
is_absolute_path "${MYSQL_DATA_DIR}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_DATA_DIR}' not an absolute path. Please set variable MYSQL_DATA_DIR accurately"
exit 1
fi
is_absolute_path "${MYSQL_BIN_DIR}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_BIN_DIR}' not an absolute path. Please set variable MYSQL_BIN_DIR accurately"
exit 1
fi
is_absolute_path "${MYSQL_PID_FILE}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_PID_FILE}' not an absolute path. Please set variable MYSQL_PID_FILE accurately"
exit 1
fi
is_absolute_path "${MYSQL_CNF_FILE}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_CNF_FILE}' not an absolute path. Please set variable MYSQL_CNF_FILE accurately"
exit 1
fi
is_absolute_path "${MYSQL_PRINT_DEFAULTS}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_PRINT_DEFAULTS}' not an absolute path. Please set variable MYSQL_PRINT_DEFAULTS accuratel
y"
exit 1
fi
is_absolute_path "${MYSQL_CLIENT}"
if [ $? -eq 0 ]
then
echo "Sorry, '${MYSQL_SERVER}' not an absolute path. Please set variable MYSQL_SERVER accurately"
exit 1
fi
########################################################################################
# Make sure that the configuration file exists #
########################################################################################
if [ ! -f "${MYSQL_CNF_FILE}" ]
then
echo "Sorry, can not find file '${MYSQL_CNF_FILE}'. Please set MYSQL_CNF_FILE accurately"
exit 1
fi
########################################################################################
# Print the usage ? #
########################################################################################
if [ ${#} -gt 0 ]
then
if [ "${1}" = "--help" ]
then
usage
exit 0
fi
fi
########################################################################################
# Everything seems OK ... start a client for connection #
########################################################################################
echo "Starting MySql client:"
args=""
for i in `$MYSQL_PRINT_DEFAULTS --config-file=$MYSQL_CNF_FILE client`
do
args="${args} ${i}"
done
until [ -z "${1}" ]
do
args="${args} ${1}"
shift
done
echo "Arguments: ${args}"
echo ""
$MYSQL_CLIENT $args
|
This is a simple script that performs the following actions:
- Create the database my_database.
- Craate 2 tables whithin the database my_database: table1 and table2.
- Create the user my_user and gives him access permissions.
|
SQL script used to create database |
DROP DATABASE IF EXISTS my_database;
CREATE DATABASE my_database;
USE my_database;
CREATE TABLE IF NOT EXISTS table1
(
FIELD1 INT(4) NOT NULL,
FIELD2 VARCHAR(25) DEFAULT 'default value',
FIELD3 INT(4) DEFAULT -1
);
CREATE TABLE IF NOT EXISTS table2
(
FIELD1 INT(4) NOT NULL,
FIELD2 VARCHAR(25) DEFAULT 'default value',
FIELD3 INT(4) DEFAULT -1
);
GRANT INSERT ON my_database.table1 TO my_user@'%' IDENTIFIED BY 'liberty';
GRANT INSERT ON my_database.table1 TO my_user@localhost IDENTIFIED BY 'liberty';
GRANT SELECT,UPDATE,DELETE ON my_database.table2 TO my_user@'%' IDENTIFIED BY 'liberty';
GRANT SELECT,UPDATE,DELETE ON my_database.table2 TO my_user@localhost IDENTIFIED BY 'liberty';
|
To use this script, log yourself to the MySql server as "root" and type the following command:
source path_to_the_previous_script
|
SELECT |
SELECT
FIELD1,
FIELD2,
FIELD3
FROM
table1
WHERE
FIELD1='1'
LIMIT
10 |
|
UPDATE |
UPDATE
table1
SET
FIELD1='10',
FIELD2='NEW VALUE'
WHERE
FIELD1='0' AND
FIELD3='-1' |
|
DELETE |
DELETE
FROM
table1
WHERE
field1='0' AND
field2='OLD VALUE' |
|
