MySQL on Linux – Checking Log files

Background

I am am On-Call this week.

As we have a System Reconfiguration slated for late hours when changes that includes system downtime are often scheduled to occur, my manager checked-in with me to see whether I was OK being assigned the Task.

I said Yes, and so here I find myself gauging my actual readiness.

 

On-Call Check Preparedness

Our team lead has written a very nice detailed document that covers each database platform( DB/2, Oracle, MySQL on Linux and SQL Server on Windows).

 

Outline

As all Databases that run on Linux have pretty much the system administrative steps, here is a condense straightforward list:

  1. Initiate SSH to Linux Host
  2. Authenticate
  3. sudo to DB Engine User
  4. Connect to DB Instance
  5. Change context to problematic database
  6. Review Error Logs
  7. Review System Metrics ( Disk / Memory / and CPU )

 

MySQL on Linux

SSH to Linux

Putty

  1. Hostname = Hostname
  2. Port = 22
  3. Connection Type :- SSH

Image

 

Authenticate

Steps

  1. Login as :- <username>
  2. Password :- <password>

Image

Authenticated

Image

 

Confirm user running mysql

Code


ps aux | grep 'sql' | grep -v 'grep'

Output

Explanation

  1. There are two mysql processes
    • /usr/bin/mysql_safe
      • user :- root
    • /usr/sbin/mysqld
      • user :- mysql
      • daemon process :- mysqld
      • basedir :- /usr
      • datadir :- /data/mysql
      • user :- myssql/err_log/mysql-err.log
      • port :- 3306
  2. What is the difference between the two processes
    • /usr/bin/mysql_safe
      • It is the bootstrap process
      • It ensures that mysqld is running and restarted if knocked down
    • /usr/sbin/mysqld
      • Actual mysql daemon process

sudo to DB Engine User

We are interested in the actual daemon and so we will target mysql.

sudo command

Syntax

sudo su - [account]

Code

sudo su - mysql

Output

Entered password

 

validate sudo user

Syntax

env | grep "USER"

Output

Connect to mysql

Code


mysql -h localhost

Output

 

Get Configuration Information – log file

Code


show global variables like '%log%'

Output

Explanation

variable_name value meaning
general_log OFF Debug Mode. Should only be used during brief troubleshooting sessions.  Logs all connections and query submissions.
general_log_file /logs/mysql/mysqld.log  Log file
log OFF  In MySQL v5.1.29, deprecated in favor of general-log
log_error  /logs/mysql/err_log/mysql-err.log Errors and Startup messages
log_output FILE This option determines the destination for general query log and slow query log output. The option value can be given as one or more of the words TABLE, FILE, or NONE. TABLE select logging to the general_log and slow_log tables in the mysql database as a destination. FILE selects logging to log files as a destination. NONE disables logging. ( Link )
log_queries_not_using_indexes OFF To include queries that do not use indexes for row lookups in the statements written to the slow query log
log_throttle_queries_not_using_indexes OFF  To include queries that do not use indexes for row lookups in the statements written to the slow query log, enable the log_queries_not_using_indexessystem variable. When such queries are logged, the slow query log may grow quickly. It is possible to put a rate limit on these queries by setting the log_throttle_queries_not_using_indexes system variable. By default, this variable is 0, which means there is no limit. Positive values impose a per-minute limit on logging of queries that do not use indexes. The first such query opens a 60-second window within which the server logs queries up to the given limit, then suppresses additional queries. If there are suppressed queries when the window ends, the server logs a summary that indicates how many there were and the aggregate time spent in them. The next 60-second window begins when the server logs the next query that does not use indexes.
( Link )
log_slow_queries OFF  Slow query log.
Deprecated in favor of slow_query_log ( 5.1.29 ).
Please see here and here.
log_warnings 2  Print out warnings such as Aborted connection… to the error log. This option is enabled (1) by default. To disable it, use –log-warnings=0. Specifying the option without a level value increments the current value by 1. Enabling this option by setting it greater than 0 is recommended, for example, if you use replication (you get more information about what is happening, such as messages about network failures and reconnections). If the value is greater than 1, aborted connections are written to the error log, and access-denied errors for new connection attempts are written.
( Link )
long_query_time 2  If a query takes longer than this many seconds, the server increments the Slow_queries status variable. If the slow query log is enabled, the query is logged to the slow query log file. This value is measured in real time, not CPU time, so a query that is under the threshold on a lightly loaded system might be above the threshold on a heavily loaded one. The minimum and default values of long_query_time are 0 and 10, respectively.
slow_query_log OFF Whether the slow query log is enabled. The value can be 0 (or OFF) to disable the log or 1 (or ON) to enable the log.

The destination for log output is controlled by the log_output system variable; if that value is NONE, no log entries are written even if the log is enabled.

slow_query_log_file /logs/mysql/slow-queries.log  File name to log Slow query

 

 

More Concise Queries

Query -01

show global variables where variable_name in 
( 
      'general_log'
    , 'general_log_file'
    , 'log_error'
    , 'log_warnings'
    , 'long_query_time'
    , 'slow_query_log'
    , 'slow_query_log_file'
);

Output -01

View Log files

mysql_err.log

tail


tail /logs/mysql/err_log/mysql-err.log

output

Explanation

  1. Not much bad going on

 

References

  1. mysql
    • Show Variables
      • Log Destinations
        Link
      • Show Variables
        Link
    • Server System Variables
      • System Server Variables
        Link
    • log Warnings
      • MySQL 5.7 Reference Manual / .. / Communication Errors and Aborted Connections // B.5.2.11 Communication Errors and Aborted Connections
        Link
    • General Query & Slow Query Log Output
      • 5.4.1 Selecting General Query and Slow Query Log Output Destinations
        Link
    • log_slow_queries
      • server-options.html#option_mysqld_log-slow-queries
        Link
    • Slow Query Log
      • MySQL 5.7 Reference Manual /Slow Query Log
        Link
  2. mySQL VS mysqld
    • How to start mysqld through mysqld_safe
      Link
  3. pontikis.net
    • How and When To Enable MySQL Logs
      Link
  4. Unix Commands
    • ps
      • nixcraft
        • Warning: bad syntax, perhaps a bogus ‘-‘? See /usr/share/doc/procps-3.2.7/FAQ
          Link
        • Quick Shell Tip: Remove grep command while grepping something using ps command
          Link
    • sudo
      • StackExchange
        • Where does sudo get the currently logged in username from?
          Link

Leave a Reply

Please log in using one of these methods to post your comment:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s