MySQL Configuration

MySQL Directories on Windows

The installation & data directory used during installation

Path Description
c:\mysql\mysql-5.1\ MySQL installation directory
c:\mysql\data\ Data root directory (Store DB data)

MySQL Configuration File on Windows

  • Windows Location
    c:\mysql\mysql-5.1\my.ini
    

my.ini

# MySQL base directory
# MySQL data directory
[mysqld]
basedir="C:/mysql/mysql-5.1/"
datadir="C:/mysql/data/"
...

MySQL Directory on Ubuntu

Directory Description
/usr/sbin Holding MySQL Server (mysqld) program
/var/lib/mysql Data root directory (Store DB data)
/usr/bin MySQL client applications & scripts
/usr/share/mysql/sql-bench Benchmark script

MySQL Configuration File on Ubuntu

/etc/mysql/my.cnf

my.cnf

# MySQL Base directory
# MySQL data directory
[mysqld]
basedir=/usr
datadir=/var/lib/mysql
...

MySQL Configuration

MySQL Server/Client configuration can be set by

  • Command line option when starting the program
  • Option file

Change MySQL Configuration using Command line option

Command line option(s) for mysqld

% mysqld --log-error --console

Command line option with parameter

% mysqld --pid-file=process_name
% mysqld --pid-file="process_name"

Some options may have an abbreviation (Start with a single "-")

% mysql -u root
% mysql --user root

Use space instead of "="

% mysqld --pid-file process_name
% mysqld --pid-file "process_name"

With one exception, space is not allowed between -p and the password

% mysql --password="mypassword"
% mysql -pmypassword

Change MySQL Configuration using Configuration File

Windows option file

c:\mysql\mysql-5.1\my.ini

Unix option file

/etc/mysql/my.cnf

Example:

[mysqld]
pid-file=process_name
log-error
port=3306

[client]
port=3306

[mysql]
character-set-server=utf8

  • Options are grouped into different groups [group_name]
  • MySQL server/client program reads from the group with the same name as the program name
  • Some program reads options from additional groups
Program Reading from Group
mysqld [mysqld] [server]
mysqld_safe [mysqld] [server] [mysqld_safe] [safe_mysqld]
mysql.server [mysqld] [mysql.server]

Show MySQL Configuration

Show MySQL Configuration Using MySQL

Login to mysql

% mysql -u root -p
SQLs Description
mysql> SHOW VARIABLES Show the MySQL startup options/variables
mysql> SHOW VARIABLES LIKE 'some_variable%' Show specific system variables with prefix some_variable
mysql> SHOW VARIABLES LIKE 'datadir' Show the root of the data directory

Sample output for SHOW VARIABLES:

...
| lower_case_file_system   | ON          |
| lower_case_table_names   | 1           |
| max_allowed_packet       | 1048576     |
| max_binlog_cache_size    | 4294963200  |
| max_binlog_size          | 1073741824  |
...

Show MySQL Configuration Using mysqld

Show all MySQL system variables

% mysqld --verbose --help

Show MySQL Configuration Using mysqladmin

Show all MySQL system variables

% mysqladmin -u root -p variables

Options Recommended for MySQL Configuration

Add the following options to the Option file for production system

Add under [mysqld] in the Option file Description
log-bin=log-bin Enable binary logging and name it log-bin.

Need for recovery & replication
myisam_recover_options Enable automatic MyISAM recovery in restart after a crash
log-error[=file_name] Enable error logging
log-slow-queries[=file_name] Log slow SQL queries



 

MySQL Options Recommended For Security

Under [mysqld] in Option file What it does What it avoids
skip-show-database Do not list database names that clients have no access Avoid hacker knowing the names of the DB
local-infile=0 Do not allow a client to load/export data file in a remote machine * Avoid hacker import/export data to/from a DB when the OS directory is not secured probably
skip-symbolic-links Do not allow symbolic link inside data directory * Avoid linking to hacker's data if OS directory is not secured probably

* These settings may be too restrictive, and the risk can be mitigated by secure the directory in the installation section. Evaluate the impact before making such changes