MySQL Survival Guide

This is a survival guide to MYSQL on FreeBSD and Linux. This guide is based in MySQL 5.1.x, versions 5.1.6 and .7 made significant changes to query logging which are noted.

We use FreeBSD 5.x, 6.x and Fedora Core. Where there are differences they are marked [FC] and [BSD] - lines with no identifier either represent an oversight or no difference! We also had to build a trvial PHP MySQL Web Viewer 'cos we could not find one.

MySQL User Manual 5.1
Installation
Post Installation
Security Issues
Location of binaries, scripts, Database Files, configuration files
Starting and Stopping
Configure my.cnf
Logs - where, configuration, diagnostics, maintenance.
Setting and Changing Passwords
Change Passwords
Reset lost MySQL Password
Command Summary - login, add user, create database, tables etc.
Login to MySQL
MySQL commands to Create Database or Table
MySQL commands to show databases, tables, fields and data
MySQL commands to delete databases, indexes and tables
MySQL commands to add, delete and show users
MySQL commands to check and repair tables
Import and Export table definitions (schema)
Save and Restore Databases (Data)

Installation and Update

For Linux (Fedora Core) use the following RPMs:

to be supplied

For FreeBSD use the following commands to install from the ports collection:

cd /usr/ports/databases/mysqlxx-server
make install clean

Where xx is the MySQL version number. By default FreeBSD will install the client as well for the same version (but does not deinstall by default).

Update MySQL

FreeBSD - use portupgrade (after doing a cvsup for ports-databases as a minimum) to install the latest release of the version currently installed. Assume you have version 4.1.3, then portupgrade would update to, say, 4.1.4 - it will not upgrade to version 5.1.

portupgrade mysql

To upgrade to a new version you need to make deinstall first as in this sequence:

cd /usr/ports/databases/mysql41-server
make deinstall

# if the client is also installed (default) you will need to 
# separately deinstall as shown
cd ../mysql41-client
make deinstall

# then install new version
cd ../mysql51-server
make install clean
# which will also install the same version of the client

make deinstall does NOT remove databases from /var/db/mysql you will be delighted - and relieved - to hear.

Post Install

After a new installation of MySQL the following should be performed:

Check that the install has created a mysql user and group and then run the following script to ensure that the administration databases are installed:
```
# check that install had added a user mysql (it does by default)
id mysql

# if not add these groups by running this script
[bsd] /usr/local/bin/mysql_install_db --user=mysql
[fc] /usr/bin/mysql_install_db --user=mysql
```
The normal install process should do this by default but running it again has no negative effect on a new install.
Set root password
Select a suitable my.cnf
Configure logging and other options
add databases
add users

After an upgraded installation of MySQL the following should be performed:

Upgrade if necessary system tables by running mysql_fix_privilege_tables (from 5.1.7 superseded by mysql_upgrade) which defaults to use user=root:

# normal command for a password protected root account
[bsd] /usr/local/bin/mysql_upgrade --password=root-password
[fc] /usr/bin/mysql_upgrade --password=root-password

# on MySQL 5.1.15 this consistently failed with access denied errors
# to bypass the problem the server was stopped and restarted with 
mysqld_safe --user=mysql --skip-grant-tables &
# then (no password required)
mysql_upgrade
# then stop and start server with normal options:
mysqld_safe --user=mysql &

configure logging and other options
Set add users

Security Issues

After installation - and setting the initial passwords - you should consider a number of security issues:

Disable Remote Access: By default MySQL is configured to allow client access from remote hosts using port 3306 (configurable in my.cnf). If client access is only required from the server (localhost) then networking access should be disabled by adding (or uncommenting) the following line in the my.cnf file:
```
[mysqld]
skip-networking
```
Remove the Anonymous Account: The anonymous account allows users to login to mysql without specifying a user name. If this is not required it can be disabled using:
```
mysql -u root - p [prompts for password]
> DELETE FROM mysql.user WHERE User = '';
> FLUSH PRIVILEGES;
```
Limit any root Login: To limit any root login attempts to localhost only do the following:
```
mysql -u root - p [prompts for password]
> DELETE FROM mysql.user WHERE user = 'root' AND host = '%'
> FLUSH PRIVILEGES;
```
The same process can be used for any user account.
Change the name of root: This is 'security by obscurity' but forces any attack to first find the name of the root account and then the password - arguably doubling the scale of the attacker's challenge. To change the name of the root account to 'thingy' do the following:
```
mysql -u root - p [prompts for password]
> UPDATE mysql.user SET user='thingy' WHERE user='root';
> FLUSH PRIVILEGES;
```
Next login must use mysql -u thingy -p.

Locations

Defines the standard installation locations when using RPMs (Linux) or the Port system (FreeBSD). Two key variables control the location process DATADIR (location of the database files and most logs) and BASEDIR (where the applications are located). The run-time value of these variables, and tons of others) may be read using mysqladmin variables (with appropriate passwords as required).

Binaries

All applications and utilities are stored in:

[FC] /usr/bin
[BSD] /usr/local/bin

Configuration Files

Options may be specified on the command line (preceeded with two dashes (--) or in a configuration file or any combination. The standard configuration file is my.cnf (*nix) or my.ini (windows). The location of the file is looked for in:

/etc/my.cnf 
then /usr/local/etc/my.cnf 
then home-dir/my.cnf
# or may be defined on the command line using
--defaults-file=/path/and/file/name (all options)
# or 
--defaults-extra-file=/path/and/file/name (additional options)

PID File

defined in my.cnf. Defaults to:

[FC] /var/run/mysqld/mysqld.pid
[BSD] DATADIR/FQDN.pid (default = /var/db/mysql/FQDN.pid)
# where FQDN = fully qualified domain including the host part
# example FQDN = host.example.com.

Database File Locations

MySQL stores its databases at the following default locations:

[bsd] /var/db/mysql/ - all log files (default location)
[bsd] /var/db/mysql/mysql/ - mysql system database and tables
[bsd] /var/db/mysql/database-name/ - files for database-name

# files named table-name.frm contain the table-name defintion
# files named table-name.MYD contain the table-name data
# files named table-name.MYI contain the table-name index(es)

Configure my.cnf

The default install places in /usr/local/share/mysql a number of my.cnf prototype files (my-xx.cnf) select the most suitable and copy to make operational (my.cnf may be located in a number of places):

cd /usr/local/share/mysql 
cp my-xxx.cnf /etc/my.cnf

Log Files

Manual section 5.2. The type and location of the various logs may be defined in my.cnf (my.cnf locations) or on the command line. The default location for all log files is the DATADIR (BASEDIR in 5.x?) ([BSD]/var/db/mysql):

Logging changed after 5.1.6 and again in 5.1.7 to allow the possible use of mysql system tables as looging destinations (and these are now the default location). Directives that control the functionality are --log and --log-output on the command line or log and log_output in my.cnf as defined in the table below:

                      |   -     |  log=          | log=/path/file
-----------------------------------------------------------------
-                     |	TABLE   | TABLE          |  TABLE
log-output=FILE       |FQDN.log | FQDN.log       | /path/file		
log-output=TABLE      | TABLE   | TABLE          | TABLE
log-output=NONE       |  NONE   | NONE           |  NONE
log-output=FILE,TABLE | TABLE   | TABLE+FQDN.log | TABLE+/path/file
log-output=FILE,NONE  |  NONE   | NONE           | NONE
# where FQDN is the fully qualified host name e.g. host.example.com.

my.cnf logging directives in the [mysqld] section.

# my.cnf log directives in [mysqld] section 
log=[/path/and/file/name] (connections and SQL statements) not defaulted
# if no file name given defaults to DATADIR/FQDN.log

log-output[=FILE | TABLE | NONE] (see above)

log-error=/path/and/file/name (defaults to DATADIR/FQDN.err)

log-bin=/path/and/file/name (not defaulted)
# if no file name defaults to the DATADIR/hostname-bin.seq-no
# if an extension is given in log-bin it is silently discarded

max_binlog_size=2m
# forces binary log swap when file size reaches 2 meg.

log-slow-queries=/path/and/file/name (not defaulted)
#if no name given defaults to DATADIR/hotname-slow.log

# Notes and keys-words used in the above descriptions:
# FQDN = fully qualified name of the host including the domain part
# example FQDN = host.example.com
# hostname = the name of the host without the domain part
# seq-no = is an incrementing sequence number

Log File Rotation

Mysql can build up serious log files. To rotate the logs there is a script supplied in /usr/local/share/mysql/log-file-rotate which is automatically installed under Fedora Core. For FreeBSD the following should be added to /etc/newsyslog.conf:

# rotates the mysql logs when they reach 1 meg
/var/db/mysql/file-name.log mysql:mysql 600 7 1000 * J
/var/db/mysql/file-name.err mysql:mysql 600 7 1000 * J

Alternatively from version 5.1.12 the log can stopped and started from mysql admin without stopping the server by doing the following:

# login to mysql
mysql -u root -p (prompts for password)
SET GLOBAL general_log = 'OFF';
# log file may be renamed/moved/deleted
SET GLOBAL general_log = 'ON';

MYSQL Start and Stop

The preferred method to startup MySQL is using mysqld_safe:

mysqld_safe --user=mysql &
# where & causes it to detach from the console

Permanent startup

[BSD] There is a startup script in /usr/local/etc/rc.d/mysql-server.sh but enable mysql in rc.conf by adding:

mysql_enable="YES"
mysql_args="--user=mysql"
# and any other argument required

[FC] The normal install places a control script in /etc/rc.d/init.d/mysql which supports start|stop|restart. To load at boot time thus script must be called from the relevant run level by adding:

ln /etc/rc.d/init.d/mysql-server /etc/rc.d/rcX.d/KYYmysql
ln /etc/rc.d/init.d/mysql-server /etc/rc.d/rcX.d/SYYmysql

# where:
# YY is arbitrary
# X defines the run level e.g.
#           3 is multi-user no GUI
#           5 is multi-user with GUI (X-windows)

Stopping

[bsd] killall mysqld
# OR
[fc]/etc/rc.d/init.d/mysql-server [stop | start | restart]
[fc]service mysql-server [stop | start | restart]
[bsd]/usr/local/etc/rc.d/mysql-server.sh [start | stop | restart]

# OR stop using long manual method 
[bsd] more /var/db/mysql/host_name.pid [gives xxx = pid]
# then 
kill xxx

# OR another manual method
ps ax|grep mysql [gives xxx= pid]
# then
kill xxx

# OR
mysqladmin -u root -p shutdown [prompts for password]

MYSQL Commands

This section covers common functions using mysql. Commands are terminated are with a ';' and the mysql session may be terminated with either exit or \q. The sequences all assume that the operations are performed as the root user (called root for convenience) and that a password has been assigned.

mysqladmin commands are defined in manual section 8.9.

Login to MYSQL

# single-line but insecure method (password in clear)
mysql [-h hostname] -u username -ppassword
# no space between p and password in this version of command
# -h is optional (defaults to localhost)

# OR a variation in syntax (still insecure)
mysql [-h hostname] -u username --password=password

# OR secure method
mysql -u root -p [will prompt for password]
# password prompts on following line with no echo
# to terminate mysql session use 'exit' or \q

Create Database or Table

Manual section 13.1.3 (database) 13.1.5 (table). Database name can also be referred to as the DSN (Data Source Name).

mysql -u root -p [will prompt for password]
CREATE DATABASE database-name;
CREATE TABLE database-name.table-name (field1, field2,..);
\q
# OR
mysqladmin -u root -p create database-name
[will prompt for password]

Display Databases, Tables, Fields and Data

Manual sections 13, 13.5.4.6, 13.5.4.4

mysql - root -p [will prompt for password]
show databases;

# display tables in database
USE db-name;
show tables;
# OR show tables from db-name;

# display fields in table
USE db-name;
show columns from table-name;
# OR show fields from table-name;
# [fields is synonym for columns]
# OR show columns from table-name from db-name;
# OR show columns from db-name.table-name;

# to display data you must use an SQL query like
SELECT field1, field2 FROM table-name [WHERE clause];

# to delete data you must use a simple SQL query like
DELETE FROM table-name WHERE field-name="unique data";

# or a more complex SQL query like
DELETE FROM table-name WHERE ( field1="data" AND field2="other data");
exit | \q

Delete Database and Tables

The relatively innocuous word DROP means DELETE (as in gone, forever) use with extreme caution. Manual section 13.1.6 (database),13.1.7 (table index) 13.1.8 (table).

mysql - root -p [will prompt for password]
# delete database
DROP DATABASE database-name;

# delete table index
DROP index index-name ON database-name.table-name;

# delete table
DROP TABLE database-name.table-name;
exit | \q

Add, Delete and Display User

Manual section 5.8.2.

mysql - root -p [will prompt for password]

# add user
# grants privileges on all databases
GRANT ALL PRIVILEGES ON *.* TO 'name'@'localhost' 
 IDENTIFIED BY 'password' WITH GRANT OPTION;

# grants privileges on specific database (db must exist)
GRANT ALL PRIVILEGES ON db-name.* TO 'name'@'localhost' 
 IDENTIFIED BY 'password' WITH GRANT OPTION;
# use of 'localhost' limits access to local machine only
# use 'hostname' or 'ip' if external access is required
# or '%' if any host is valid

# delete user 
DROP USER user-name[,user-name2...]
# where user-name takes the form 'user-name'@'host-name'
# if simple user-name format used assumes 'user-name'@'%'
# where % is the wildcard and means any host

# display users
USE mysql;
SELECT user, host from user
#lists all users and the valid hostnames
exit | \q

Maintain - Check and Repair Tables

MySQL supports commands to check and repair tables within a database. While they do not always fix problems they can be remarkably effective - once you identify the failing table. Manual section 12.5.

mysql - root -p [will prompt for password]
# check table
CHECK TABLE table-name [option];

# where options may be
# FOR UPGRADE | QUICK | FAST | MEDIUM | EXTENDED | CHANGED
# default option is MEDIUM
# EXTENDED can take a long time

# repair table
# requires SELECT and INSERT priviliges
REPAIR TABLE table-name [option];

# where option may be:
# QUICK EXTENDED USE_FRM
# QUICK - only index tree
# EXTENDED - row-by-row 
# USE_FRM - if .myi corrupt/lost (consult manual before using)
# CAUTION: make sure you have a current database archive before using
# REPAIR

\q

Setting and changing passwords

Setting the initial root password:

Manual section 2.4.15.3 (tutorial) and 13.5.1.6. Set initial password for root:

# to set the initial password
mysqladmin -u root password new-password

# if remote access is allowed also do this
mysqladmin -u root -h host-name password new-password

# OR to set the initial password via mysql
mysql -u root mysql
SET PASSWORD FOR 'root'@'localhost'=PASSWORD("new-password");

# if remote access allowed also do this
SET PASSWORD FOR 'root'@'host-name'=PASSWORD("new-password");

# set password for anonymous account (use different password)
SET PASSWORD FOR ''@'localhost'=PASSWORD("anon-password");

# if remote access allowed also do this
SET PASSWORD FOR ''@'host-name'=PASSWORD("anon-password");
exit | \q

Change Passwords

To change password (Manual section 13.5.1.6):

# command line
mysqladmin -u user-name -p password new-password [prompts for password]

# OR via mysql
mysql -u root -p[prompts for password]
SET PASSWORD FOR 'user-name'@'host-name'=PASSWORD("new-password");
FLUSH PRIVILEGES;
# above necessary for new password to take effect
exit | \q

Reset Passwords

To reset the MySQL password without knowing the password (you have forgotten it or because the person who set-up the MySQL system is (a) on vacation (b) left the company (c) died):

# stop mysql using any technique here
# then restart with following command
mysqld_safe --user=mysql --skip-grant-tables &

# set new password
mysqladmin -u root flush-privileges password “newpassword”

# stop and re-start mysql with normal command 
mysqld_safe --user=mysql

# OR via mysql
mysql -u root -p[prompts for password]
SET PASSWORD FOR 'user-name'@'host-name'=PASSWORD("new-password");
FLUSH PRIVILEGES;
# above necessary for new password to take effect
exit | \q

Import and Export Database Schema

If you only want to save the database schema (the SQL commands that build the tables within the database) and not the data then use this command on a existing database.

# assume database name is mybd
mysqldump -d -u root -p mydb > mydb.sql [prompts for password]

The -d option means no data will be saved. If the data is required use the commands shown under Archive and Restore below.

Import Schema

To import and create a database schema from the saved file use:

mysql -u user-name -p db-name < mydb.sql [prompts for password]
# omit db-name if sql file creates database, if not db-name must exist

Archive and Restore Database

mysqldump is the generic program used to archive a MySQL database. Specific databases or all databases may be saved with this program.

mysqldump -u root -p mydb > mydb+data.sql [prompts for password]
# saves only the database mydb to the file mydb+data.sql

# if you want to run this from a script without typing 
# the password, add the password after the -p WITHOUT A SPACE
mysqldump -u root -pmypassword mydb > mydb+data.sql

# to save all databases use the -A argument (does not need a database name)
mysqldump -A -u root -p > mydb+data.sql [prompts for password]
# OR no password prompt for a script
mysqldump -A -u root -pmypassword > mydb+data.sql

# to run as a cron job with date based file use a script like this
#!/bin/sh -
NOW=$(date +%b-%d-%Y)
export NOW
mysqldump -A -u root -pmypassword > $NOW.sql
# NOW gives format Feb-12-2007
# then place script in your periodic directory for the 
# frequency required (daily, weekly, monthly) 
# OR add to your cron tabs location (/var/cron/tabs)
# in which case you will need this format
SHELL=/bin/sh
15 2 * * * /path/to/your/script
# runs daily at 2:15 AM
# consult man cron for more info

Saves the schema and data for the databases defined.

Restore Database

To restore a database when saved using mysqldump above:

mysql -u root -p mydb < mydb+data.sql [prompts for password]

Restores the schema and data for the database mydb.

Problems, comments, suggestions, corrections (including broken links) or something to add? Please take the time from a busy life to 'mail us' (at top of screen), the webmaster (below) or info-support at zytrax. You will have a warm inner glow for the rest of the day.