MySQL Default Configuration File

• Option Files
  • also called as Configuration Files
  • most of MySQL programs can read the start-up options from the option files also know as configuration files
  • meaning programs such as mysqld , mysqladmin , mysqlimport when they start they takes some options from option files , those options can be defined on command line or they can be put and saved in a configuration or option file
  • to retrieve which default options a MySQL program we use the following syntax program --verbose --help mysql --verbose --help
    
    
    
  • any program starts with -no-default option read no option file other than .mylogin.cnf
• Option Files Format
  • option files are plain-text files – expect .mysql.cnf , encrypted by mysql_config_editor
• Option Files Processing Order
  • Global Options : /etc/my.cnf etc/mysql/my.cnf
  • Server Only Options : $MYSQL_HOME/my.cnf
  • User-Specific Options : ¬/.my.cnf
  • Client-only Options :¬/.mylogin.cnf

Locate Default Option File 🌟

reference link

we will start by locating mysqld options file using the below command
mysqld --verbose --help
on the beginning of the page we should fine details of the program and the option files
to make the view better we can pipe line with less and it will show you the output in pages that you can between them using the space bar
mysqld --verbose --help | less

to exist out we simply jus press ”q’ button .

in the above image you can see that mysqld read option from given file in order
one of the files not present in the sequence , it will go to the other option file
let’s check if the file are there in the OS

we are able to find only /etc/my.cnf remaining are not there , for ~/.my.cnf as motioned before is User-Specific Options
so mysqld start-up using options file /etc/my.cnf

MySQL Option/Configuration File Syntax

• any comment in option file start with # sign
• there are option groups in the sometime is called stanza
• when editing option file we provide the option name follow by = then value option = value
• space is allowed either side between option and “=andvalue`
• value can be without quote , single-quote , double-quote , recommended that any value with some pound sign or any special to be in double-quote
• any option that may be given at command-line can be given in the option file as well
• for example for starting mysqld we are saying mysqld --server-id in the command line , in the option file it will be server-id = value
• variable cannot have - the have _ and they will show up in system variables as an option
• **option IS NOT variable **
• option groups
  • there could be many Group in the option file , some of common are mysqld,mysqladmin,client,mysql,server
  • note client option group is read by all client programs expect mysqld

Re-Write Default Option File

First thing I will check variables for instance server-id
show variables like '%server%';

You can see the variable name is server_id bit if you came back to command line and type
mysqld --verbose --help | less you will find option is written as --server-id so this is considered as option not variable

Now we will add option for server-id in my.cnf
sudo vi /etc/my.cnf and then add at end of file server-id = 3

Now restart mysql using systemct restart mysqld

Variable or Option in Option File?

• In the option file, you can specify variables that appear when using the command show variables like '%server%'; in MySQL.
• MySQL recommends that any option appearing when running mysqld --verbose --help | lesscan also be used without the need to log in to MySQL and retrieve system variables, which could be time-consuming.
• Essentially, you can use both the variable name and the option name interchangeably in the option file.

Changing Default Option Files Location

reference link

In this section we will change the default option file location using the following steps

1. Create directory /etc/mysql
2. copy /etc/my.cnf to /etc/mysql/my.cnf
3. Rename the existing /etc/my.cnf to /etc/my.cnf.old
4. Restart mysqld and verify
   remember that mysqld program read the following diretcory /etc/my.cnf /etc/mysql/my.cnf /usr/etc/my.cnf ~/.my.cnf you can verfiy this uisng the command mysqld --verbose --help | less

1. Create directory /etc/mysql

sudo mkdir /etc/mysql

2. copy /etc/my.cnf to /etc/mysql/my.cnf

sudo cp /etc/my.cnf /etc/mysql/my.cnf

3. Rename the existing /etc/my.cnf to /etc/my.cnf.old

sudo mv /etc/my.cnf  /etc/my.cnf.old

4. Restart mysqld and verify

sudo systemct	restart mysqld
sudo systemct	status  mysqld

Validating MySQL Configurations File

STRACE & LSOF With MySQL

This section addresses essential aspects of troubleshooting MySQL server startup issues. When diagnosing why mysqld failed to start, it’s crucial to examine:

• The option file that was read during startup. To verify this, you can stop the MySQL service and then initiate its startup with the strace utility.
• The files accessed by MySQL during startup. This can be determined by utilizing the lsof command.

START MYSQLD WITH STRACE
When initiating strace to observe how mysqld starts and the files it attempts to access, the process might fail due to MySQL being managed by systemd. However, despite this failure, the output from strace can still provide valuable insights, including the location of the log file and the option file being accessed.

sudo systemctl stop mysqld
sudo strace mysqld

sudo strace mysqld

scroll up and look for stat and look for stat regarding my.cnf

LSOF With MySQL
now we will use the utility LSOF to see which files mysqld has opened

sudo lsof -u mysql

Option File Inclusions

When configuring MySQL, you have two options to include additional option files:

• Use !include = file to specify a single additional configuration file.
• Use !includedir = directory to include all configuration files within a specified directory.

Syntax:

• To include a single file: !include = file_path
• To include files from a directory: !includedir = directory_path

Example:

• Include a single file: !include /home/bob/bob-options.cnf
• Include all .cnf files from a directory: !includedir /home/bob

Note:

• All included files must have a .cnf extension to be recognized as configuration files.

using Option File Inclusions

• we will start by creating directory called /etc/percona
sudo mkdir /etc/percona
  
• copy the /etc/mysql/my.cnf to /etc/percona/my.cnf
sudo cp /etc/mysql/my.cnf /etc/percona/my.cnf
  
• edit /etc/mysql/my.cnf and add !includedir
sudo vi /etc/mysql/my.cnf
  

and remove every content of the file

and only add the !includedir /etc/percona

• restart mysqld and verify
  sudo systemctl restart mysqld sudo systemct status mysqld

MySQL Data Directory

When MySQL is installed:

• It defaults its data directory path to /var/lib/mysql.
• A system user named mysql is created, which uses the data directory as its home.
• Ownership of the data directory is assigned to the mysql user.
• It is advisable to place the data directory on its own dedicated file system.
• The location of the data directory can be customized by setting the datadir variable in the my.cnffile.
• if we change the location of data directory then we need to make appropriate adjustments in my.cnffile with datadir as an option

Move Data_DIR MySQL Data Directory

To relocate the MySQL data directory to a separate filesystem, follow these steps:

1. Shut down the MySQL server to prevent any data corruption or access issues.
2. Create the new directory on the separate filesystem and change its ownership to the MySQL user.
3. Update the my.cnf configuration file to set the new data directory location using the datadir variable.
4. Restart the MySQL server to apply the changes and start using the new data directory.

note : for runinng the below command you need root user or sudo privilages
1- Shut down the MySQL server.

systemctl	stop mysqld

systemctl status mysqld

2- Create the new directory on the separate filesystem and change its ownership to the MySQL user.

mkdir mysqldata
chown -R mysql:mysql mysqldata/

-3 move data file from default path to the new path

default path is located in /var/lib/mysql
use mv to move data files to the new directory

cd /var/lib/mysql

usng * you will basicly telling to move everting on currnt diretcory to the new diretcory

mv * /mysqldata/

4- Update the my.cnf configuration file to set the new data directory location using the datadirvariable.
we will update data_dir and add the new directory instat of the default one
vi /etc/my.cnf

5- Restart the MySQL server to apply the changes and start using the new data directory.
systemct restart mysqld
systemctl status mysqld

MySQL Binary Logs

• MySQL Binary Logs log any chnages that has happen on databases in special encrypted file called binaray logs
• and the changes in the file are recoreded as database evenets
• binary log are encrypted in special format we cannot read it , in order to read it in text format we will use mysql utility called mysqlbinlog
• binary log contain information on how long each DML statement took to complete executing
• binary log play a critical role in the Replication part such as Master Slave setup , which relay heavily on binary logs
• binary log provide point-in-time recovery , bring back the database to a data from point of backup
• binary log play critical role in backup , in insist where backup is restored , the events in binary log file after backup was made are re-executed
• by default binary log is enabled in MySQL Server
• default size for binary log is 1GB – the value controlled by variable called max_binlog_size
• Retention determine for how long you want to keep binary logs , this is controlled by system variable called binlog_expire_logs_seconds

Enable Binary Logging
you have option customize binary log

• to enable binary log we will use system variable called log_bin and specify value to be ON
• system variable log_bin_basename control the naming convention of binary log file , you can change the name of the file as follow mysqld-bin , binlog , prod-bin
• log_bin_index is a system variable also give option to give binary log any name but the name must end with .index , binlog.index , mysqld-bin.index , prod-bin.index

Disable Binary Logging
to disable bin log for database used for UAT or Test simply add in my.cnf file one of the below variables without the need to specify any value
1.skip-log-bin
2.disable-log-bin

Purging Binary Log Files

we will run some basic command that are important we managing binlog ,
show binray logs; will show the list of binary logs files and the files are numbered in a sequential form

show binlog events in 'binlog.number'; allow us to view the events in one of binlog files

the last command is concern when the you filesystem is filled and database transaction halted because lack of space and you want to quickly delete binlog files
purge binary logs to 'which number';
this will allow you to delete the binlog file to end file you specify

enable and disable binary log

we will demonstrate two action typically done by DBA

1. Disable binary logging
2. Enable Binary logging and moving it to new location to make binary log file stored in separate filesystem

Disable binary logging
use cat or vi to edit the my.cnf file
vi /etc/my.cnf

at the end of the file we will add the below variable
disable-log-bin or skip-log-bin

now we will restart MySQL server and verify if binlog is disabled

systemctl restart mysqld
systemct; status mysqld

mysql> show binary logs;

since we are not using binlog anymore we can remove binlog leftover safely using rm -f

we will do MySQL server service restart to see if MySQL is function probably and binlog files are not generating again
systemctl restart mysqld

Enable Binary logging and moving it to new location
we will now enable binary log but we want to configure binary log in way that binary log file should be stored in separate filesystem
we will create separate filesystem.
mkdir binlog

next we will change the owner to MySQL user
chown -R mysql:mysql binlog/

now we will edit my.cnf file using either nano or vi and add option

to get the option we can mysqld --verbose --help | grep -i bin

we will use two option log-bin & log-bin-index copy both option and go to vi /etc/my.cnf

remove or comment out disable-log-bin

add the below line
log-bin = /binlog/binlog log-bin-index =/binlog/binlog.index

now we will restart mysql server and check if the files are created in the new directory

systemctl restart mysqld

ls /binlog/

mysql > show global variables like '%bin%';

Binary Logs Retention

we will set a expiry date for our binary log files by setting retention days for binlog

first let’s check what is the current retention period for our binlog by runing the below command on MySQL shell
show variables like '%expire%';

the value is showing in seconds we will have to convert them to days
which will come to 30 days

if we want to keep them for 5 days which will come to 86400 second we will edit the option binlog_expire_logs_seconds in my.cnf file

vi /etc/my.cnf

then we will restart MySQL server and check if the retention value is changed
systemctl restart mysqld

MySQL Error Log File

• MySQL error log contain the record of MySQL startup and shutdown time
• is also contains diagnostic messages like errors , warning that occurs during startup or shutdown , and while the server is running .
• this file is very important will help to understand why the server is not starting up or not shutting down
• different MySQL components writes log events in the errors log example , system , innodb etc
• log-error is the system variable
• default path for error log is /var/log/mysqld.log

Error Logging
how to configuring error log falls under three points

1. if we have not given log-error variable , we have told where to defiant and where to locate the log file , then MySQL will write the error log to the console
2. if log-error is given but we have not given the name of the log file , then MySQL will write the error logs to file called host_name.err
3. if log-error is given with path and the file name , then MySQL will write the error log on file you specify

change MySQL Error log location

we will place the error log under directory called binlog
we will create empty file using touch under binlog
touch mysqld.log

next ensure that file owner is MySQL system user .

chown -R mysql:mysql mysqld.log

now open the my.cnf file using nano or vi
vi /etc/my.cnf
and add log-error option with value of error log path we have created
log-error = /binlog/mysqld.log

final steps is to restart MySQL services and then check if new error log file have error written on it
systemctl restart mysqld

vi /binlog/mysqld.log