Tech Stuff - 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.

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:

# following are default locations but may be overridden using
# the datadir parameter in my.cnf
[fc] /var/lib/mysql/ - all log files (default location)
[fc] /var/lib/mysql/mysql/ - mysql system database and tables
[fc] /var/lib/mysql/database-name/ - files for database-name
[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. There are two major log types to worry about. The General or Slow Query log (log or log-slow-queries options in my.cnf) and Binary logs (log-bin in my.cnf). Foir Linux Redhat/Fedora Core users there is a script supplied in /usr/local/share/mysql/log-file-rotate which is automatically installed and which apperently handles both Genral Query (and Slow Query) and Binary logs.

General and Slow Query Logs

For FreeBSD to rotate the Slow or General Query log the following can be added to /etc/newsyslog.conf:

# rotates the mysql logs when they reach 1 meg
# and keeps 7 copies
/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 now be renamed/moved/deleted
# then restart logging
SET GLOBAL general_log = 'ON';

Finally the MySQL manual states that you can simply rename (or delete) the log file and then FLUSH LOGS:

cd /path/to/datadir
mv log-file.name log-file.name.old
# login to mysql
mysql -u root -p (prompts for password)
FLUSH LOGS;
# creates a new file with log-file.name

Binary Logs

Binary logs are created when the log-bin option appears in the my.cnf file. The option expire_log_days= can be used to create a new log file after the defined number of days. The old files are not deleted. The binary log files can also me manually rotated using:

# login to mysql
mysql -u root -p (prompts for password)
FLUSH LOGS;
# creates an new binary log file but does not
# delete the old one
OR
mysqladmin flush-logs

Note: FLUSH LOGS; starts a new binary log file with the next sequence number, starts a new error log after renaming the current one by adding -old to the file name but only closes and reopens the General or Slow Query logs.

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
# or using standard scripts
[fc] /etc/rc.d/init.d/mysql-server start
[bsd] /usr/local/etc/rc.d/mysql-server start

Permanent startup

[BSD] There is a startup script in /usr/local/etc/rc.d/mysql-server.sh (laster versions seem to be ommitting the .sh suffix) 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 this script must be invoked 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 but defines the order in which the
# script is invoked relative to all others
# 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.

# 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

Assuming you are using the MyISAM DB engine (default) you can also use the myisamchk utilty (see manual section 4.6.3 - the server must be stopped before running this utility.

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.

Move Databases

If you want to move databases to another server while simultaneously providing service then the problem becomes quite complex and will involve replication. First you would set up a replication server on the new system then, when fully operational, change the master to become the new server. Finally you can retire the old server. This scenario is not further described.

If you are simply moving databases and have the ability to shutdown service then you have two choices:

Use mysqldump on the current server, copy the resulting file and restore on the new server for each database using the resoration procedure described above. Since this uses a text based interchange method it is probably the safest and will allow for any changes in MySQL versions.
If your versions of MySQL are compatible (read the various MySQL release notes carefully) you can simply copy the various database binary files to create an exact copy of your current configuration on the new server. This includes copying the MySQL operational database (in /var/db/mysql/mysql or /var/lib/mysql/mysql). This method has the overriding advantage than it does not require knowledge of MySQL passwords - it can done entirely as a black-box transfer. This method is described in detail below.

Note: Assuming that your MySQL configuration is supporting an Internet based service such as a web server, FTP server etc. and assuming you are also changing the IP address of the new server (perhaps also the host name) the actual time to make the transition to the new server is determined by the TTL (Time to Live) value in the DNS records that point to the target service, for example the DNS A record for www.example.com. If these TTLs are long, for example two days is a typical value, then the actual transition will take up to this period of time. For reasons of database intergrity you MUST keep the old server off-the-air during this period and hence worst case users would see a dead service for up to two days in this example. To reduce this out-of-service delay during the transition period you may want to consider changing the DNS TTL values involved prior to making the change. Assume that you have a 48 hour TTL on the DNS A records, then at least 48 hours before making the server change, modify the DNS A record TTL to say 1 hour. While this puts an increased load on the DNS it has the effect of reducing the caching time in remote DNS servers to 1 hour. When you finally cut to the new server and modify the IP address you can also reset the TTL to the original value, say 48 hours. This procedure has the effect of reducing the out of service time to 1 hour for worst case users.

Copy MySQL Binary Files

The following procedure was used to transfer binary files from MySQL version 5.1.17 on the old server to 5.1.26 on a new server.

Copy the my.cnf file from the old to the new server (this can be done while fully operational). If you have a modified startup script you should also copy this to the new server as well.
load mysql on the NEW server using your normal method such as:
```
# using standard scripts
[bsd] /usr/local/etc/rc.d/mysql-server start
[fc] /etc/rc.d/init.d/mysql-server start

# confirm it has loaded successfully
ps ax|grep mysql
# debug if not loaded
```
We found this dummy load was necessary to allow mysql to perform some initialization including establishing directory structures, permissions, paths and certain variables but it is entirely possible that we could have achieved the same result by using the datadir=path/to/db/files parameter of my.cnf. However the dummy load worked perfectly for us without any need to modify my.cnf.

Shut down mysql on the NEW server:

# using standard scripts
[bsd] /usr/local/etc/rc.d/mysql-server stop
[fc] /etc/rc.d/init.d/mysql-server stop

Now you are ready to perform the transfer of server so to avoid copying unnecessary files you may want to flush logs and prune log files as appropriate. Now shutdown mysql on the OLD server. From this point on for reasons of database integrity you cannot restart service on the OLD server unless you decide to abandon the transfer process. Copy all files MySQL binary files using a suitable process such as scp, or via portable media, from the OLD server to the NEW server:

# copy all files from the normal location
[fc] /var/lib/mysql
[bsd] /var/db/mysql

# after copying check that the appropriate permissions are set
# on all directories and files
# in most cases this should mean all the files are 
# owned by user = mysql and group = mysql
# access permissions are typically 0660
# if not use chown and chmod to fix
chown -R mysql:mysql /path/to/mysql/datafiles
chmod -R 0660 /path/to/mysql/datafiles


# You may want to run a check on all files for integrity
myisamchk --silent /path/to/mysql/data/files/*/*.MYI

Start MySQL on the NEW server, confirm it is running and that you can login to MySQL using the same passwords that were valid on the OLD server. Perform any other appropriate tests to confirm the new server is fully operational and a faithful replica of the OLD server.

If the MySQL versions are different between the old and new servers you should run mysql_upgrade as shown:
```
# with mysql running on NEW server
mysql_upgrade -u user-name -p
```
Change your DNS records as appropriate to point to the new server and change any modified TTL values if you used the process described above. Reload the zone (or restart DNS). Wait for transactions to start arriving at the new server.

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.