MySQL Configuration

Option Files:

❖ Also called MySQL Configuration Files

❖ Most MySQL programs can read startup options from option files (configuration)
❖ Convenient way to specify commonly used options so need not to specify on command-line
❖ mysqld, mysqladmin, mysqlimport, mysqldump, mysql - examples of MySQL programs
❖ program –verbose –help - To get which default option file this programs uses
❖ Any program starts with –no-defaults option reads no option file other than .[Link]

Option Files Format:

❖ Option files are plain-text files - except .[Link] , encrypted by mysql_config_editor

Option Files Processing Order:

❖ Global Options: /etc/[Link], /etc/mysql/[Link]

❖ Server Only Options: $MYSQL_HOME/[Link]
❖ User-Specific Options: ~/.[Link]
❖ Client-Only Options: ~/.[Link]
 MySQL Configuration
ASSIGNMENT: Which configuration file was used by mysqld?

ASSIGNEE: Bob, The Junior DBA

Hint:

❖ mysqld –verbose –help

❖ Browse the path and find if file(s) are there
 MySQL Configuration
Option Files Syntax:

❖ Comments - # sign
❖ option group - stanza
❖ option = value
❖ Space is allowed either side
❖ Value can be without quote, single-quote, double-quote
❖ Any option that may be given at command-line, can be given in option file as well
❖ –server-id at command-line can be given in option file as server-id
❖ option IS NOT variable

Groups:

❖ mysqld, mysqladmin, client, mysql, server

Note: [client] option group is read by all client-programs except mysqld

 MySQL Configuration
ASSIGNMENT:

❖ Remove all comments from option file

❖ Add an option in option file
❖ Restart mysqld and verify

ASSIGNEE: Bob, The Junior DBA

 MySQL Configuration
ASSIGNMENT:

❖ Create /etc/mysql directory - root

❖ Copy /etc/[Link] to /etc/mysql/[Link]
❖ Rename existing /etc/[Link] to /etc/[Link]
❖ Restart mysqld and verify

ASSIGNEE: Bob, The Junior DBA

 MySQL Configuration
Which option file was read:

❖ Stop mysqld service

❖ Start with strace mysqld
❖ Observe the output

lsof:

❖ All opened files by mysql user

 MySQL Configuration
Option File Inclusions:

❖ !include = for file

❖ !includedir = for multiple option files

Examples:

❖ !include /home/bob/[Link]
❖ !includedir /home/bob

Note:

❖ All option files must end with .cnf

 MySQL Configuration
ASSIGNMENT:

❖ Create /etc/percona directory - root

❖ Copy /etc/mysql/[Link] to /etc/percona/[Link]
❖ Edit /etc/mysql/[Link] and add !includedir
❖ Restart mysqld and verify

ASSIGNEE: Bob, The Junior DBA

 MySQL Configuration
MySQL Data Directory:

❖ Default path is /var/lib/mysql

❖ mysql user is created and /var/lib/mysql set as home directory
❖ Owned by mysql user
❖ Should be on its own Filesystem - SSSD is preferred
❖ Controlled by datadir option in [Link]
 MySQL Configuration
ASSIGNMENT:

❖ Move Data Directory to /var/lib/mysql/prod

Hint:

❖ Shutdown mysql service

❖ Create directory and change owner to mysql
❖ Move all data files
❖ Set option in [Link]
❖ Restart mysql service

ASSIGNEE: Bob, The Junior DBA

 MySQL Configuration
MySQL Binary Log Files:

❖ Record database changes as events

❖ Binary format - Encrypted
❖ mysqlbinlog utility to read binary log files and output in clear text
❖ Contains information on how long each statement took that updated data
❖ Very import for Replication - provides a records of data changes on source
❖ Point-in-Time Recovery - Bring database up to date from the point of backup
❖ After a backup has been restored, the events in the binary log that were recorded
after the backup was made are re-executed
❖ Default size is 1GB - controlled by max_binlog_size
❖ Retention - How many days worth of binary logs should we keep -
binlog_expire_logs_seconds
 MySQL Configuration
Enable Binary Logging:

❖ Enabled by default - system variable log_bin ON

❖ log_bin - binlog | mysqld-bin | prod-bin
❖ log_bin_index - [Link] | [Link] | [Link]

Disable Binary Logging:

❖ skip-log-bin | disable-log-bin
 MySQL Configuration
ASSIGNMENT:

❖ Disable Binary Logging

❖ Enable Binary Logging - Move to a new location

ASSIGNEE: Bob, The Junior DBA

 MySQL Configuration
MySQL Error Log File:

❖ Contains a record of mysqld startup and shutdown times

❖ Also contains diagnostic messages like errors, warnings that occurs during
startup or shutdown, and while the server is running
❖ Different MySQL components writes log events in the error log i.e system,
innodb, etc
❖ log-error is the system variable
❖ Default error log is /var/log/[Link]

Error Logging:

❖ No log-error given - mysqld writes the error log to console

❖ log-error is given without naming a file, mysqld will write to a log host_name.err
file
❖ log-error is given, file name is given, mysqld will write to that log file
 MySQL Configuration
ASSIGNMENT:

❖ Configure Error Logging to /var/log/mysql/errorlog location

ASSIGNEE: Bob, The Junior DBA

 MySQL Configuration
MySQL Temp Directory:

❖ Directory where MySQL stores temporary files

❖ tmpdir is the system variable
❖ Default location is /tmp
❖ If tmpdir is not set, MySQL uses operating system default which could be
/var/tmp, /usr/tmp
 MySQL Configuration
ASSIGNMENT:

❖ Configure TEMP DIRECTORY to /var/log/mysql/tmpdir location

ASSIGNEE: Bob, The Junior DBA

 BOB PROGRESS
CHECKPOINT
Bob’s Knowledge Gain:

❖ MySQL Default Options/Configuration File

❖ Option File inclusions
❖ strace & lsof utilities
❖ Moving Data Directory
❖ Binary Log Files
❖ Error Log File
❖ TEMP DIRECTORY

Bob, The Intermediate DBA