Linux

MySQL on Linux – Checking Log files

Daniel Adeniji Configuration ( MySQL), Linux, Log files ( MySQL), MySQL, Putty, Technical

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

SQL Server – Analysis Services – Cannot Connect ( Microsoft.AnalysisServices.AdomdClient )

Daniel Adeniji Connectivity ( Analysis Services ), Microsoft, MS SQL Server, Network Tracing, Networking, SQL Server Analysis Services (SSAS), Technical , , , ,

Background

Users are having problems connecting to the Analysis Services Server.

 

Error Message

Image

Text


TITLE: Connect to Server
------------------------------

Cannot connect to ....sql01.

------------------------------
ADDITIONAL INFORMATION:

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

------------------------------

A connection attempt failed because the connected party did not properly respond after a period of time, 
or established connection failed because connected host has failed to respond z.y.76.188:2383 (System)

Text


===================================

Cannot connect to d-isql01.

===================================

A connection cannot be made. Ensure that the server is running. (Microsoft.AnalysisServices.AdomdClient)

------------------------------
Program Location:

at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenTcpConnection(ConnectionInfo connectionInfo)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.OpenConnection(ConnectionInfo connectionInfo, Boolean& isSessionTokenNeeded)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.Connect(ConnectionInfo connectionInfo, Boolean beginSession)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Connect(Boolean toIXMLA)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.XmlaClientProvider.Microsoft.AnalysisServices.AdomdClient.AdomdConnection.IXmlaClientProviderEx.ConnectXmla()
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.ConnectToXMLA(Boolean createSession, Boolean isHTTP)
at Microsoft.AnalysisServices.AdomdClient.AdomdConnection.Open()
at Microsoft.SqlServer.Management.SqlStudio.Explorer.ObjectExplorerService.ValidateConnection(UIConnectionInfo ci, IServerType server)
at Microsoft.SqlServer.Management.UI.ConnectionDlg.Connector.ConnectionThreadUser()

===================================

A connection attempt failed because the connected party did not properly respond after a period of time, or established connection failed because connected host has failed to respond xx.xx.xx.xx:2383 (System)

------------------------------
Program Location:

at System.Net.Sockets.TcpClient..ctor(String hostname, Int32 port)
at Microsoft.AnalysisServices.AdomdClient.XmlaClient.GetTcpClient(ConnectionInfo connectionInfo)

TroubleShooting

Server

Resource Monitor

If the server has Microsoft’s Resource Monitor built-in let us use to it to review Network Ports.

Image

Explanation

  1. Listening Ports
    • Image :-
      • msmdsrv.exe
    • Address :-
      • IPV4 unspecified
      • IPV6 unspecified
    • Port :-
      • 2383
    • Protocol
      • TCP
    • Firewall Status
      • Allowed, not restricted

 

Client

netstat

Script


netstat -an | find "SYN_SENT"

Sample Output

Explanation

  1. Request was sent to port 2383
  2. Status is SYN_SENT
    • SYN_SENT means waiting on a reply

Remediation

Open up TCP Port 2383.

 

Review

Once the Network Port, 2383, is opened up retry access with SSMS.

 

Network Flow

Also, it makes sense to review the Network Traffic to get a full list of ports to be opened.

WireShark

Output

 

Explanation

Seems Analysis Services exclusively relies on port 2383.

Linux – Finding Text in Log Files – Day 1

Daniel Adeniji grep, Linux, Linux Commands, Microsoft, Technical

Background

Don’t really spend much time in the Linux World.

But, there are many Application Servers that sit on Linux Servers and rely on SQL Server on Windows.

 

Log Files

When error occurs a good Application Server write these errors in Log Files.

After a while needs develop to trend the type of DB Connectivity and more serious hard errors.

 

Sample Log Files

As we discussed in a recent post, we copied over SQL Server Error Log files over to Linux.

 

Text Search

grep

Let us use grep to perform simple Text Searches.

Scenario

Look for Text Matching Manufacturer

Script

grep -w -i -l -n  "Manufacturer" ERRORLOG
Output

Explanation
  1. No matches

 

Remediation

Attempted Workarounds

We tried the following workarounds

  1. Worked
    • Created a much simple text file and was able to grep against it
  2. Did Not Help
    • Modified copy over mode to binary and back to text
    • Command Issued
      • cat ERRORLOG | grep -i “manufacturer”

 

Worked

Code


cat ERRORLOG | tr -d '\000' | grep -i "manufacturer"

Output

Why does it work

  1. Cat
    • Outputs a file’s content
  2. tr – stands for translate
    • tr -d ‘\000’
      • -d means delete
      • \000 means null character

Crediting

What makes grep consider a file to be binary?
Link

When you are top 13% on stackexchange, you are one of the Big Boys and Girls.

 

Linux & Windows – Simple File Transfer Using FileZilla

Daniel Adeniji FileZilla, Linux, Microsoft, Technical

Background

There is a little insight that I need to reach for.

It has to do with LogFiles on a couple of Application Servers that run on Linux.

 

Preparation

I tried creating some sample text files on the Linux host, but I could only type in three lines or so.

I know that I generate a lot of errors on my SQL Server boxes, so opted to avail those on the Linux boxes and play with those instead.

 

Tools

FileZilla

FileZilla is free, Open Source, and there is a Portable Version.

Download

Download it from here.

 

Avail

Availed it by launching the *.paf.exe file

 

Launch

Configure Host through Site Manager

Let us explicitly configure our remote Host.

Access Site Manager through menu items “File” \ “Site Manager …”

Image

Explanation
  1. Enter Host Name
  2. Protocol :- SFTP – SSH File Transfer Protocol
  3. Logon Type :- interactive
  4. User :- Enter your user name
    • In our case dadeniji
  5. Password :- Enter your password
    • As we chose Interactive for Logon Type, the password text box is disabled
  6. Click the Connect to connect to the remote host

 

File Listing

Image
Before

 

After

Please navigate your directory structure, select the files you want to copy over, and drag and drop them onto the Target Folder.

Linux – Listing Files Based on Date

Daniel Adeniji Linux, Scripting - Bash, Technical ,

 

Background

I have a need to list files on a Linux Host sorted by date.

 

ls Command

Earliest First

Command


ls -ltr -G -g | grep -v '^total' | head -5

 

Output

 

Latest First

Command


ls -lt -G -g | grep -v '^total' | head -5

Output

 

ls Command In a ditch

Unfortunately, when there are numerous files in the target folder the ls command and other file utilities can get in a ditch.

Argument List Too Long

Command


ls *.LOG

Output

Image

Text

bash: /bin/ls: Argument list too long

 

Find Command

Earliest First

Command


find . -name '*.LOG' | sort | head -n 5

Output

Command


find . -name '*.LOG' | sort | tail -n 5

Output

 

Script

Let us get a bit more complicated.

And, we will do do by picking up individual files, process them, and exit after N Number of files have been processed.


# get current folder
echo "Current working folder is $PWD"


#echo list all files in current log
#find . -name "*.LOG" | xargs -i echo " {} "


declare -i iFileID
declare -i iFileIDMax
declare FORMAT_FILE_PROCESSING

iFileID=1
iFileIDMax=1000
iFileIDMax=50
FORMAT_FILE_PROCESSING="%d  file %s \n"

#echo $FORMAT_FILE_PROCESSING

#list earliest top N LOG files
find . -name "*.LOG" 2> /dev/null  | sort | head -n $iFileIDMax  | while  IFS="" read name;
do



     printf "$FORMAT_FILE_PROCESSING" $iFileID  $name

     iFileID=$((iFileID+1));

     if [ $iFileID  -gt  $iFileIDMax ]
      then


        break;

     fi


done

 

 

Dedicated

Dedicated to ….

The Electronic Toolbox
Argument list too long when copying/deleting/moving files on Linux
Link

Summary

When needing to list files by date, one can use the ls command,

In cases where there are numerous files, the type of filtering one can do is hampered by the fact that the ls command tries to accept all the files as a batch.

A good workaround is the find command.

 

Next Up

The error that I really need to fix was the one from running “gzip S066*”

Image

Textual

bash: /usr/bin/gzip: Argument list too long

 

I know it has something to do with 666

 

Reference

  1. Error – Argument list too long …
    • The Electronic Toolbox
      • Argument list too long
        Link
  2. ls command
    • TheGeekStuff.com
      • Linux ls command examples
        Linux
    •  linuxcommand.org
  3. Find Command
    • Unix.com
      • While loop, input from find command
        Link
    • StackOverflow.com
      • Display modified datetime with Find Command
        Link
    • unix.stackexchange.com
      • Only find first few matched files using find
        Link
    • Superuser.com
      • How can I find the oldest file in a directory tree
        Link
  4. Loop Control
    • The Linux Documentation Project ( tldp.org )
    • LinuxCommand.org
      • Flow Control – Part 3
        Link
  5. If Command
    • TheGeekStuff.com
      • Bash If Statement Examples ( If then fi, If then else fi, If elif else fi, Nested if )
        Link
  6. Math
    • K-State Polythecnic
      • Math in Shell Script
        Link
  7.  printf
    • Linuxconfig.org
      • Bash printf basic commands
        Link
    • Bash Hackers wiki
      • The printf command
        Link
    • Unix.StackExchange.org
      • Printf formatting with variable format – what does this var reference?
        Link
  8. variables
    • Ryan

 

Change Prompt in Bash

Daniel Adeniji Linux, Prompt ( Bash ), Scripting, Scripting - Bash

Background

For us that like to journal and keep notes of every command we issue and capture screens, having long prompts get in the way a bit.

Especially, when you get an incident and pick up that phone and  it says one of the DB/2 hosts is running out of Log space again.

And, the best you can do is say that if it is only at 90% it can likely suffer till the morning.

 

Current Prompt

To get the current prompt issue “echo $PS1”

Image

Text

\u@\h:\w>

Revised Prompt

Let us rid ourselves of all the clutter by dropping all three; username, machine name and the current working directory are all out of here.

SESSION


export PS1='>'

 

Permanently

To make the change permanently, please edit ~/profile in your editor of choice and add same line.


					

		
		
	




			
				


	

		

					
SSH Connection to Linux Over Active Directory and Centrify

								

								 Daniel Adeniji
															Centrify, Identity Access management, Linux, Putty, SSH, Technical					
												

					


				

			
Background


Need to start providing On call support to  some of our DB Servers that are running on Linux Hosts.


Being able to ssh to the boxes is essential.


 


Downloading


Downloaded Putty from here.




Usage


Putty


Connection Attempt


Launched Putty.exe, thanks goodness no installation needed.


The Putty Screen is very minimal:


    

  1. Host Name or IP address

      

    • xxxx01
      • 

    

    2. 

  2. Port

      

    1. 22
      2. 

    

    3. 



 


session


 


Connection Attempt


Image


accessdenied


Textual



Using keyboard-interactive authentication.
Password :
Access denied





Active Directory


To do


Have your Active Directory support group add you to the impacted AD Group


 


Validate


DOS


Script


Syntax


net group [ADgroup] /domain



Sample


net group grpLinuxDBA /domain



 


Output


netgroup_20161208_0320pm


Centrify


Have Centrify Administrators do the following:


    

  1. Create an account for you
    2. 

  2. Add you to an existing group that has access to the Linux DB Hosts
    3. 

  3. Grant explicit sudo access  to created account or inherit from group membership
    4. 



BTW, the Centrify product that we use is listed here.