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


# MySQL base directory
# MySQL data directory

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



# MySQL Base directory
# MySQL data directory

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


Unix option file






  • 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