webcp.hostinghacks.net/slackware | mysql
MySQL configuration on Slackware
PREREQUISITES: /usr/libexec/mysqld binary preinstalled with Slackware.
The installation commands can be run from a Putty window in a "cut-and-paste" style layout or copied to a script. Notes on Putty best practices can be found here.
install the mysql databases:
su mysql mysql_install_db exit
chmod +x /etc/rc.d/rc.mysqld /etc/rc.d/rc.mysqld start
set the root passwd:
mysql -u root mysql
mysql>
update user set password=password("dbpasswd") where user="root";
exit
test:
mysqladmin -u root reload mysql -u root --password=dbpasswd
mysql>
exit
create the mysql config file:
cat > /etc/my.cnf << "EOF" [mysqld] datadir =/var/lib/mysql port =3306 socket =/var/run/mysql/mysql.sock [mysql.server] user =mysql basedir =/var/lib [safe_mysqld] err-log =/var/log/mysqld.log pid-file =/var/run/mysql/mysql.pid EOF
logrotate mysql files:
cat > /etc/logrotate.d/mysqld << "EOF"
/var/log/mysqld.log {
missingok
create 0640 mysql mysql
prerotate
[ -e /var/lock/subsys/mysqld ] &&
/bin/kill -HUP `cat /var/run/mysqld/mysqld.pid 2> /dev/null ` || /bin/true
endscript
postrotate
[ -e /var/lock/subsys/mysqld ] &&
/bin/kill -HUP `cat /var/run/mysqld/mysqld.pid 2> /dev/null ` || /bin/true
endscript
}
EOF
watch mysql log files:
cat > /etc/cron.weekly/watch.mysql << "EOF" #! /bin/sh tail -100 /var/log/mysqld.log | mail -s "mysql log" servadmin@localhost EOF chmod +x /etc/cron.weekly/watch.mysql /etc/cron.weekly/watch.mysql
starting mysql manually:
mysqld_safe --user=mysql 2>&1 >/dev/null &
initial setup of the databases:
The Slackware MySQL package
is build to run as the user "mysql:mysql" and the data directory /var/lib/mysql should be
owned by this user and the associated mysql group.
A problem users run into is that they run the mysql_install_db root user, this causes
incorrect permissions to be set on the MySQL data dir and as a result the server can't start
since it cannot read its database files. The proper way to run the mysql_install_db script
is to run it as the mysql user. If you have already run the script as root, then simply run
the command chown -R mysql:mysql /var/lib/mysql to reset the permissions. Then su mysql to
become the proper user and re-run the script. Now you should be able to start the MySQL server
by running safe_mysqld --user=mysql as root.
the slack mysql init script:
#!/bin/sh
# Start/stop/restart mysqld.
#
# Copyright 2003 Patrick J. Volkerding, Concord, CA
# Copyright 2003 Slackware Linux, Inc., Concord, CA
#
# This program comes with NO WARRANTY, to the extent permitted by law.
# You may redistribute copies of this program under the terms of the
# GNU General Public License.
# To start MySQL automatically at boot, be sure this script is executable:
# chmod 755 /etc/rc.d/rc.mysqld
# To disallow outside connections to the database (if you don't need them, this
# is recommended to increase security), uncomment the next line:
#SKIP="--skip-networking"
# Start mysqld:
mysqld_start() {
if [ -x /usr/bin/mysqld_safe ]; then
# If there is an old PID file (no mysqld running), clean it up:
if [ -r /var/run/mysql/mysql.pid ]; then
if ! ps ax | grep mysqld 1> /dev/null 2> /dev/null ; then
echo "Cleaning up old /var/run/mysql/mysql.pid."
rm -f /var/run/mysql/mysql.pid
fi
fi
/usr/bin/mysqld_safe --datadir=/var/lib/mysql --pid-file=/var/run/mysql/mysql.pid $SKIP &
fi
}
# Stop mysqld:
mysqld_stop() {
# If there is no PID file, ignore this request...
if [ -r /var/run/mysql/mysql.pid ]; then
killall mysqld
# Wait at least one minute for it to exit, as we don't know how big the DB is...
for second in 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 \
0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 60 ; do
if [ ! -r /var/run/mysql/mysql.pid ]; then
break;
fi
sleep 1
done
if [ "$second" = "60" ]; then
echo "WARNING: Gave up waiting for mysqld to exit!"
sleep 15
fi
fi
}
# Restart mysqld:
mysqld_restart() {
mysqld_stop
mysqld_start
}
case "$1" in
'start')
mysqld_start
;;
'stop')
mysqld_stop
;;
'restart')
mysqld_restart
;;
*)
echo "usage $0 start|stop|restart"
esac
setting the root password
mysqladmin password dbpasswd mysqladmin -u root --password=dbpasswd shutdown
Database Manipulation
Create a database:
mysqladmin –u root –p create database
Drop a database:
mysqladmin -u root -p drop database
Connect to MySQL:
mysql -u Username -p
While connected:
use DbName; show tables; select * from TableName; drop database mybase; show columns from table_1 grant all on rabbitbase.* to user
mysql –D mydatabase –u user –p < sql.dump; mysqldump -u user -p mydatabase > sql.dump
MySQL permissions
mysql> show databases; +-----------+ | Database | +-----------+ | mysql | | test | | timeclock | | webcp | +-----------+ 4 rows in set (0.00 sec)
mysql> use mysql; Database changed mysql> show tables; +-----------------+ | Tables_in_mysql | +-----------------+ | columns_priv | | db | | func | | host | | tables_priv | | user | +-----------------+ 6 rows in set (0.00 sec)
db: controls access of USERS to databases.
'host','db','user','select','insert','update','delete','index','alter','create','drop','grant'
mysql> select * from db; +-----------+-----------+----------+-------------+-------------+-------------+ | Host | Db | User | Select_priv | Insert_priv | Update_priv | +-----------+-----------+----------+-------------+-------------+-------------+ | % | test | | Y | Y | Y | | localhost | timeclock | nutuser | Y | Y | Y | +-----------+-----------+----------+-------------+-------------+-------------+ -------------+-------------+-----------+------------+-----------------+------------+------------+ Delete_priv | Create_priv | Drop_priv | Grant_priv | References_priv | Index_priv | Alter_priv | -------------+-------------+-----------+------------+-----------------+------------+------------+ Y | Y | Y | N | Y | Y | Y | Y | Y | Y | N | Y | Y | Y | -------------+-------------+-----------+------------+-----------------+------------+------------+ 3 rows in set (0.00 sec)
"%" is a wildcard meaning any host can access that database.
user table: everything after "password" is a privelege granted (Y/N).
This table controls individual user global access rights.
'host','user','password','select','insert','update','delete','index','alter','create','drop'
'grant','reload','shutdown','process','file'
mysql> select * from user; +-------------------+----------+------------------+-------------+-------------+-------------+-------------+ | Host | User | Password | Select_priv | Insert_priv | Update_priv | Delete_priv | +-------------------+----------+------------------+-------------+-------------+-------------+-------------+ | localhost | root | 74ec53585e40090e | Y | Y | Y | Y | | psinode.com | root | | Y | Y | Y | Y | | localhost | | | N | N | N | N | | psinode.com | | | N | N | N | N | | localhost | nutuser | 38fb5gre77682a6f | N | N | N | N | +-------------------+----------+------------------+-------------+-------------+-------------+-------------+ -----------+-------------+---------------+--------------+-----------+------------+-------------- Create_priv | Drop_priv | Reload_priv | Shutdown_priv | Process_priv | File_priv | Grant_priv | -------------+-----------+-------------+---------------+--------------+-----------+------------+ Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | Y | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | N | -------------+-------------+-----------+-------------+---------------+--------------+----------- ----+------------+------------+------------+ References_priv | Index_priv | Alter_priv | -----------------+------------+------------+ Y | Y | Y | Y | Y | Y | N | N | N | N | N | N | N | N | N | -----------------+------------+------------+ 5 rows in set (0.00 sec)
Passwords are stored in encrypted format.
host table: this controls which HOSTS are allowed what global access
rights.
'host','db','select','insert','update','delete','index','alter','create','drop','grant'
mysql> select * from host; Empty set (0.00 sec)
So nothing to see here. Lets at least look at the structure:
mysql> show columns from host;
+-----------------+-----------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+-----------------+------+-----+---------+-------+
| Host | char(60) binary | | PRI | | |
| Db | char(64) binary | | PRI | | |
| Select_priv | enum('N','Y') | | | N | |
| Insert_priv | enum('N','Y') | | | N | |
| Update_priv | enum('N','Y') | | | N | |
| Delete_priv | enum('N','Y') | | | N | |
| Create_priv | enum('N','Y') | | | N | |
| Drop_priv | enum('N','Y') | | | N | |
| Grant_priv | enum('N','Y') | | | N | |
| References_priv | enum('N','Y') | | | N | |
| Index_priv | enum('N','Y') | | | N | |
| Alter_priv | enum('N','Y') | | | N | |
+-----------------+-----------------+------+-----+---------+-------+
12 rows in set (0.00 sec)
Note that the HOST table, USER table, and DB table are very closely connected -- if an authorized USER attempts an SQL request from an unauthorized HOST, it is denied. If a request from an authorized HOST is not an authorized USER, it is denied. If a globally authorized USER does not have rights to a certain DB, it is denied.
mysql> select * from columns_priv; Empty set (0.00 sec) mysql> select * from func; Empty set (0.00 sec) mysql> select * from tables_priv; Empty set (0.00 sec)
/usr/sbin/useradd -M -o -r -d /var/lib/mysql -s /bin/bash \ -c "MySQL Server" -u 27 mysql > /dev/null 2>&1
-M : don't create bash profile files
-o :
-r :
how lfs adds the mysql user: groupadd mysql && useradd -c mysql -d /dev/null -g mysql -s /bin/false mysql
InnoDB is a table type in MySQL which offers several advantages to its users: InnoDB tables are transactional: they provide rollback and commit capabilities. InnoDB is the only table type in MySQL which supports foreign key constraints. InnoDB tables are fast, even faster than MyISAM tables in many simple benchmarks. See the benchmark page. InnoDB tables have row level locking: they allow higher concurrency than MyISAM tables which use table level locking, or BDB tables, which use page level locking. High concurrency is reflected in high multiuser performance. InnoDB tables provide an Oracle-style consistent read, also known as multiversioned concurrency control. SELECTs do not need to set any locks and need not interfere with inserts and updates to the same table. No other MySQL table type has this property. There is a true hot backup tool available for InnoDB, which allows you to make backups of a running database in background, without setting any locks or disturbing database operation. Multiversioning also allows you to dump tables from your database with SELECT INTO OUTFILE without setting locks on the tables: the database can keep working while a backup is made. InnoDB tables have automatic crash recovery. You do not need to repair your tables if the operating system or the database server crashes, when there is no disk image corruption. InnoDB tables can be any size, also on those operating systems where file size is restricted to < 2 GB.