LOG FILES IN MySQL(Article -09)

LOG FILES IN MySQL.

Before going to look on logfiles we need to know what is the use of my.cnf file.

By default my.cnf file will be in /etc/my.cnf location, when ever you try to restart MySQL server look for my.cnf in /etc/ location.

Some of the default values in my.cnf should be changed when you need it for business critical applications. Lines starting with “#” are comments, and they mostly document what the different settings are for. There are lines in the configuration file that just contain a word in square brackets, like “[client]” or “[mysqld]”. Those are “config groups” and they tell the programs that read the configuration file which parts they should pay attention to. The “client” configuration section controls the mysql client, and the “mysqld” section controls the server configuration. By listing an option in this group, you make it easier to invoke not only mysql, but also other programs such as mysqldump and mysqladmin. Make sure that any option you put in this group is understood by all client programs. Otherwise, invoking any client that does not understand it results in an “unknown option” error.

 

In mysql prompt if you type mysql >show variables; command it will show 454 variables but when you see my.cnf file you don’t find all of those variables, variables not shown in my.cnf file are loaded by default.

You can change variables either in my.cnf file or from mysql prompt when ever you change variable you must restart mysql to take effect.

 

How to change variable value in my.cnf file?

 

For example if we need to change log file path in mysql execute show variable like ‘%error%’; this command shows log file path configured in my.cnf file.

Types of log files in mysql

There are five types of MySQL Server log files.

1) Error log.

2) Slow query log.

3) General query log.

4) Binary log.

5) Relay log.

1.Error log file:

It contains information indicating when mysqld was started and stopped and also any critical errors that occur while the server is running. For example, if mysqld notices a table that needs to be automatically checked or repaired, it writes a message to the error log. The error log contains a stack trace if mysqld dies. The trace can be used to determine where mysqld died.

Problems encountered on starting, running, or stopping mysqld it is ENABLED by default related parameter is log-error[=FILENAME/PATH_TO_FILENAME]

Now lets try to change log file path from /var/log/mysqld.log to /var/lig/mysql/mysqld.log

Open my.cnf file in [mysqld] (server session) look for log_error variable change the value of log_error to /var/lig/mysql/mysqld.log. once changes made in my.cnf restart mysql using restart mysql.service command.

Now restart mysql service .

Now execute show variable like ‘%error%’; command as shown below log path has changed.

You can see log file is created in new location.

 

2.Slow query log or performance log:

• The slow query log consists of SQL statements that took more than long_query_time seconds to execute and required at least min_examined_row_limit rows to be examined. It can be used to find queries that take a long time to execute and can be used for optimization. However, examining a long slow query log can become a difficult task. To make this easier, you can process a slow query log file using the mysqldumpslow command to summarize it.
  mysqld writes a statement to the slow query log after it has been executed and after all locks have been released, so log order might differ from execution order. Also, the slow query log should be protected because logged statements might contain passwords.Queries that took more than long_query_time seconds to execute. DISABLED by default. Relevant parameters are slow-query-log[={0,1}], slow_query_log_file[=FILENAME], log-output=PATH_TO_FILE/TABLE/NONE, log-slow-admin-statements[={OFF,ON}], log-queries-not-using-indexes[={OFF,ON}], log-slow-slave-statements[={OFF,ON}], log-short-format[={FALSE,TRUE}], long_query_time[=NUMERIC], min-examined-row-limit[=NUMERIC].NOTE: If you change above log parameter using mysql statement such as mysql>SET GLOBAL slow_query_log = 1; this value will be in effect only till mysql is running once mysql is restarted this value will be set to default because when mysql restart it reads data from my.cnf configuration file in configuration file above parameter is set to default value to make these changes permanent you need to add/modify respective values in mysql configuraion file.

 

To enable above parameters we need to edit configuration file using $vi /etc/my.cnf file in server session enter variable and its value and save the changes and restart mysql server.

 

Alternatively you can enable by executing mysql prompt (but this setting is temporary when mysql is restarted changed parameters are set to default).

 

Below screen shot shows slow_query_log is set to 1 (0 is for disable, 1 is for enable), you can verify show variable like ‘%slow%’; is set to ON.

Now set long_query_time to 1 (compare below screen shot with previous screen shot).

 

 3.Binary log:

The binary log contains “events” that describe database changes such as table creation operations or changes to table data. It also contains events for statements that potentially could have made changes (for example, a DELETE which matched no rows), unless row-based logging is used. But it is not used for statements such as SELECT or SHOW that do not modify data. It also contains information about how long each statement took that updated data.
Binary logging is done immediately after a statement or transaction completes but before any locks are released or any commit is done. This ensures that the log is logged in commit order. You can display the contents of binary log files with the mysqlbinlog utility. You can also reprocess the statements in the log for a recovery operation.The binary log has 2 important purposes.

• For replication, the binary log on a master replication server provides a record of the data changes to be sent to slave servers. The master server sends the events contained in its binary log to its slaves, which execute those events to make the same data changes that were made on the master.
  Certain data recovery operations require use of the binary log. After a backup has been restored, the events in the binary log that were recorded after the backup was made are re-executed. These events bring databases up to date from the point of the backup.
• When binary log is enabled mysqld-bin.000001(contain changes to your data) and mysqld-bin.index(contain all binary log files (mysqld-bin.00000X) which are already created) will be created, each time mysql database is created one new binary file will be created.

 

 

How to check binary logs are enables or not ?

 

we can check using variables.

Here binary log has been disabled.

To enable binary logs we need to add on e parameter called log-bin in my.cnf (we can’t do in mysql prompt).

After writing log-bin save and quit the file.

Then restart the service

Execute mysql> show binary logs; this command will list all binary logs crated so far. Mysql> show master status; command will show which binary file is used now.

Where these binary are stores ??

 

There are 3 formats supported by Binary logs.

• Statement based logging
  Master writes events to the binary log on SQL statements. This is the default binary logging statement. With statement-based replication, there may be issues with replicating non-deterministic statements. If MySQL cannot guarantee that the statement can be replicated using statement-based logging, it marks the statement as potentially unreliable and issues the warning, “Statement may not be safe to log in statement format”.
  •  Row based logging
  Master writes events to the binary log that indicate how individual table rows are affected.
  •  Mixed base logging
  Statement-based logging is used by default, but the logging mode switches automatically to row-based under the following conditions.

  When a function contains UUID().

 When one or more tables with AUTO_INCREMENT columns are updated and a trigger or stored function is invoked. Like all other unsafe statements, this generates a warning if binlog_format = STATEMENT.

  When the body of a view requires row-based replication, the statement creating the view also uses it. For example, this occurs when the statement creating a view uses the UUID () function.

  When a call to a UDF is involved.

  When any INSERT DELAYED is executed for a nontransactional table.

4.General query log:

The General log file is a file which contains every statements that comes to mysql. every log file like error or slow log file will come to general log file .

This log is a general record of what mysqld is doing. The server writes information to this log when clients connect or disconnect, and it logs each SQL statement received from clients. It can be very useful when you suspect an error in a client and want to know exactly what the client sent to mysqld. mysqld writes statements to the query log in the order that it receives them, which might differ from the order in which they are executed.

Established client connections and statements received from clients
DISABLED by default relevant parameter is general-log[={0/1}, general_log_file[=FILENAME], log-output=PATH_TO_FILE/TABLE/NONE.

By default general log will be disabled. we need to enable manually.

Lets enable it and see the content.

Lets check whether its enable or not .

To check the content we should in operating system  prompt and will use tail mode.

When i issued any command on mysql  it will be recorded into  general logfile .

will issue few more commands.

Now will check General log file .

Now i will try to use one of the databases from list

When is issued use world command in mysql  in general log file so many lines are recording .

That is the reason General log file is not recommended (It’s record everything means it will  fillup rapidly).

5.Relay log:

The relay log is used only on slave replication servers, to hold data changes from the master server that must also be made on the slave. It is just like the binary log, consists of a set of numbered files containing events that describe database changes, and an index file that contains the names of all used relay log files. They have the same format as binary log files and can be read using mysqlbinlog.
If you are using replication, you should not delete old binary log files on the master until you are sure that no slave still needs to use them.

Data changes received from a replication master server. ENABLED by default, if replication is ENABLED.

Relevant parameters are relay-log=FILENAME, relay-log-index=FILENAME, relay-log-info-file[=FILENAME], master-info-file[=FILENAME], log-slave-updates, log-warnings[=NUMERIC], max_relay_log_size[=SIZE], relay_log_purge={0/1}, relay-log-recovery={0/1}, relay_log_space_limit[=SIZE], sync_relay_log[=NUMERIC], sync_relay_log_info[=NUMERIC].

 

Thank you for giving your valuable time to read the above information. Please click here to subscribe for further updates

KTEXPERTS is always active on below social media platforms.

 

Facebook : https://www.facebook.com/ktexperts/
LinkedIn : https://www.linkedin.com/company/ktexperts/
Twitter : https://twitter.com/ktexpertsadmin
YouTube : https://www.youtube.com/c/ktexperts
Instagram : https://www.instagram.com/knowledgesharingplatform