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
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
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
| 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
|
