mysql

If disk space has run low, you will observe:

1. Application query response time increases.
2. Few application queries will randomly fail with lock wait timeout.
3. /mnt/eprint/mysql/mysqld.err error log will display the message: /usr/libexec/mysqld: Disk is full writing '/mnt/eprint/mysql/replication/mysql-bin.000033' (Errcode: 28). Waiting for someone to free space... (Expect up to 60 secs delay for server to continue after freeing disk space)


sudo su
mysql -u root -popelin
show databases;
use cpgDB;
show tables;
show create table tablename;
drop table tablename;
set foreign_key_checks=0;
select database();
drop database testdb;
create database testdb;
describe tablename;
SHOW TABLE STATUS FROM db_name LIKE 'tbl_name';
SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SET tx_isolation = 'READ-COMMITTED';
exit;
tx_isolation system variable. Has both global and session scope.
The default transaction isolation level. Defaults to REPEATABLE-READ.
You can refer to the values of specific global or sesson system variables in expressions by using one of the @@-modifiers. For example,
you can retrieve values in a SELECT statement like this:
SELECT @@global.sql_mode, @@session.sql_mode, @@sql_mode;
For SHOW VARIABLES, if you specify neither GLOBAL nor SESSION, MySQL returns SESSION values.
autocommit is a session variable and must be set for each session.
use \G for displaying columns linearly
You can also use scripts from the mysql prompt by using the source command or \. command:
mysql> source C:\srini\IPG\code\CloudPrint\database-main\src\main\sql\cpgdb\00289.DE11469.dmapi.cpgDB.insert.reserved.email.addresses.into.BlockedEmailAddress.table.sql
mysql> \. filename
SHOW INDEX FROM tbl_name
mysql -uroot -popelin -e 'show engine innodb status\G' >> /home/cp_user/srini/out.txt
mysql> tee /tmp/out.txt    //this commands makes mysql to write its output to the given file along with console
mysql> notee                //this command disables the effect of tee command
mysqld --defaults-file="C:\ProgramData\MySQL\MySQL Server 5.5\my2.ini"
mysqladmin -u root –p shutdown
mysqldump --no-data -u root -p cpgDB > /tmp/cpgDB.sql    //This option exports only database and table schema, not data.
--no-create-info, -t     //This option instructs the utility not to add CREATE TABLE statements to the export file.
--no-data, -d            //This option exports only database and table schema, not data.
mysqldump -u russell -p workrequests work_req clients > /tmp/workreq_clients_tables.sql   //Here the database is workrequests and the tables to be backed up are
work_req and clients. Their table structures and data will be copied into the text file workreq_clients_tables.sql.
mysqldump --no-create-info --insert-ignore --where="service_name='storage'" -u root -p appconfig appconfig > /tmp/appconfig.sql
mysqldump -u root -p puma_db > /tmp/puma_db.sql
mysql -u root -p < /tmp/puma_db.sql
mysql -h host -u user -p
alter table deviceemailaddressalias add unique key (deviceId,creationTimeInMilliSec); to add unique key constraint
alter table deviceemailaddressalias add index index_name (deviceId,creationTimeInMilliSec); to drop unique key constraint
alter table deviceemailaddressalias drop index deviceId; to drop unique key constraint
show procedure status
show function status
CREATE INDEX idx_actor_first_name ON actor (first_name); The simplest CREATE INDEX statement adds a non-unique index to a table.
du -sk * | sort -nr | more
----------------------------
use cpgDB;
CREATE TABLE innodb_lock_monitor(a int) ENGINE=INNODB;//this statement starts writing (more than thrice per minute) enhanced output of 'show engine innodb status\G' to error log which in our case is /opt/apps/ms/mysql/mysqld.err. This is usfull for continuous monitoring.
drop table innodb_lock_monitor; //to disable lock monitoring, the table should be dropped.
-----------------------------
CREATE TABLE innodb_monitor (a INT) ENGINE=INNODB;
DROP TABLE innodb_monitor;
----------------------------------
select Token from AccessToken where expiryTime<=1414728000000 order by expiryTime asc into outfile 'c:\\srini\\testmail\\prodissues\\renew tokens\\finalTokens.txt';
LOAD DATA INFILE 'c:\\srini\\oauth1_clients.txt' INTO TABLE ConsumerDetails;
select Token from AccessToken where expiryTime<=1414728000000 into outfile 'c:\\srini\\testmail\\prodissues\\renew tokens\\latest tokens\\googleTokens.txt';
mysql> show variables like '%general%';
mysql> set global general_log=ON;
mysql> show open tables in testdb; //shows table locks on all the tables
mysql> analyze table SessionToken; //allows query optimizer to choose  better indexes
mysql> optimize table SessionToken; //reorganizes data on the disk.

cat > /opt/apps/ms/mysql/mysqld.err
csplit -k -f finalTokens -n 3 finalTokens.txt 50000 {*}


enabling general log for mysql 5.0.x versions:

1. log variable value should be set to ON.
2. Mysql should be started with command line option --log=/mnt/eprint/mysql/genlog.log as below
/bin/sh /usr/bin/mysqld_safe --defaults-file=/mnt/eprint/mysql/my.cnf --datadir=/mnt/eprint/mysql/data --socket=/mnt/eprint/mysql/mysql.sock --log-error=/mnt/eprint/mysql/mysqld.err --pid-file=/mnt/eprint/mysql/mysqld.pid --log=/mnt/eprint/mysql/genlog.log --user=mysql

 

tomcat remote debugging using netbeans

Tomcat:

tomcat/bin/catalina.sh checks for jpda argument. If jpda argument is passed then if other required variables are not available, it automatically sets defaults values for those variables. Below is the snippet of /mnt/eprint/tomcat/bin/catalina.sh file:

if [ "$1" = "jpda" ] ; then
  if [ -z "$JPDA_TRANSPORT" ]; then
    JPDA_TRANSPORT="dt_socket"
  fi
  if [ -z "$JPDA_ADDRESS" ]; then
    JPDA_ADDRESS="8000"
  fi
  if [ -z "$JPDA_SUSPEND" ]; then
    JPDA_SUSPEND="n"
  fi
  if [ -z "$JPDA_OPTS" ]; then
    JPDA_OPTS="-agentlib:jdwp=transport=$JPDA_TRANSPORT,address=$JPDA_ADDRESS,server=y,suspend=$JPDA_SUSPEND"
  fi
  CATALINA_OPTS="$CATALINA_OPTS $JPDA_OPTS"
  shift
fi

 /mnt/eprint/tomcat/bin/startup.sh run the command "catalina.sh start $@". $@ represents all the argument passed to startup.sh. /mnt/eprint/tomcat/bin/startup.sh has the below snippet:

PRGDIR=`dirname "$PRG"`
EXECUTABLE=catalina.sh
.
.
.
exec "$PRGDIR"/"$EXECUTABLE" start "$@" 


To start tomcat in remote debug mode the command run by /mnt/eprint/tomcat/bin/startup.sh should look like "catalina.sh jpda start $@". After doing the changes /mnt/eprint/tomcat/bin/startup.sh looks like below:

PRGDIR=`dirname "$PRG"`
EXECUTABLE=catalina.sh
.
.
.
exec "$PRGDIR"/"$EXECUTABLE" jpda start "$@"


If you don't want catalina.sh to set default values for jpda parameters then you can even declare them in the /mnt/eprint/tomcat/bin/startup.sh as below:

export JPDA_ADDRESS=8000

export JPDA_TRANSPORT=dt_socket


Netbeans:

To debug an externally started application call Debug . Attach Debugger. Select the Java Debugger
(JPDA) and use SocketAttach as connector. Additionally, you indicate the host’s name on which the
application runs, the port number on which the application accepts requests, as well as a timeout.
Press OK after doing the settings.

useful linux commands

Shell:

The shell is an interactive command interpreter environment within which commands may be typed at a prompt or entered into a file in the form of a script and executed.

A variety of shell environments have been developed over the years (Bourne shell, C shell, Korn shell, Bash shell). The default shell on CentOS 6 is the Bash shell (shorthand for Bourne again shell) and is based on features provided by both the Bourne Shell and the C Shell.

Bash provides a wide range of command line editing options as outlined in the following table:

Ctrl-b //Move cursor back one position
Ctrl-f  //Move cursor forward on position
Delete  //Delete character currently beneath the cursor
Backspace //Delete character to the left of the cursor
Ctrl-_  //Undo previous change (can be repeated to undo all previous changes)
Ctrl-a //Move cursor to the start of the line
Ctrl-e  //Move cursor to the end of the line
Esc then f  //Move cursor forward one word
Esc then b //Move cursor back on word
Ctrl-l  //Clear the screen of everything except current command
Ctrl-k  //Delete to end of line from current cursor position
Esc then d  //Delete to end of current word
Esc then DEL  //Delete beginning to current word
Ctrl-w  //Delete from current cursor position to previous white space 

File Name and Path Completion 

press the Esc key twice //complete the filename for you with the first file or path name in the directory that matches the characters you entered.

press Esc =  //Displays a list of possible matches 

Shell commands:

history //Displays a list of previously executed commands. In addition, Ctrl-p (or up arrow) and Ctrl-n (or down arrow) may be used to scroll back and forth through previously entered commands. Another option is to enter the ‘!’ character followed by the first few characters of the command to be repeated followed by the Enter key.

which ls //The available shell commands are either built into the shell itself, or reside on the physical file system. The location on the file system of a command may be identified using the which command.

cat list?.txt //Single character matches may be specified using the ‘?’ character. ‘*’ wildcard character to can be used to represent any number of characters.

ls *.txt > files.txt //redirect the output from an ls command to a file named files.txt 

wc –l < files.txt //The contents of a file may be fed into a command in place of stdin. This displays the number of lines contained in files.txt file.

df -h //Displays available partitions with their free space. -h allows human readable format

dd if=/home/srinivas/downloads/CentOS-6.5-x86_64-bin-DVD1.iso of=/dev/sdb //we can create bootable linux image usb device

sudo [options] [command]  //If you are allowed, execute command as the superuser. Authorized users of sudo and the commands they are permitted to execute are listed in the sudo configuration file, /etc/sudoers. If an unauthorized
user attempts to run a command, sudo informs an administrator via email. By default, it sends the message to the root account. Users attempting to run commands are prompted for their password. Once authenticated, sudo sets a timestamp for the user. For five minutes from the timestamp, the user may execute further commands without being prompted for her password. This grace period may be overridden by settings in the /etc/sudoers file. Also see /etc/sudoers for configuration examples. The sudoedit form of sudo is equivalent to running sudo -e.

su [option] [user] [shell_args]  //Create a shell with the effective user ID user. If no user is specified, create a shell for a privileged user (i.e., become a superuser). Enter EOF to terminate. You can run the shell with particular options by passing them as shell_args (e.g., if the shell runs bash, you can specify -c command to execute command via bash, or -r to create a restricted shell).

env //Shell environment variables provide temporary storage of data and configuration settings. Displays list of currently defined environment variables

echo $PATH //Displays the value of PATH environment variable. PATH defines the directories in which the shell will search for commands entered at the command prompt prompt, and the order in which it will do so.

export PATH=$PATH:$HOME/scripts //you wanted the shell to also look for commands in the scripts directory located in your home directory, you would modify the PATH variable like this.

export DATAPATH=/data/files //You can create your own environment variables using the export command. If there are environment variable or alias settings that you need to be configured each time you enter the shell environment, they may be added to a file in your home directory named .bashrc.

export NOW=`date` //Assign the output from a command to an environment variable involves the use of back quotes (`) around the command.

vmstat

top

rpm -Uvh package_file //updates existing installation

rpm -i package_file //installs package  

rpm -e package_name --noscripts //if normal erase didn't work because of any installation problems, the --noscripts option forcefully erases the package.

rpm -qa | grep java   //finds all the installed java rpm's on the machine

rpm -qf /user/bin/vim //displays which package is responsible for the file installation

rpm -ql package-name //list all files installed by that package

yum search package_file // * can be used at either end of the package file name

yum install package_file   //yum searches the repositories listed under /etc/yum.repos.d/ for specified package file. yum does dependency resolution also.

yum localinstall jdk-7u60-linux-i586.rpm  //package-file is on the local machine. This takes the help of yum for installation along with dependency resolution.

yum erase package_name //uninstall the package 

 yum update //apply any available updates to the installed packages

yum info package_name //displays information about the package 

yum repolist //display your current yum repositories

yum repolist all //to display all the repositories (both enabled and disabled) 

yum --enablerepo=fedora-source install vim-X11.x86_64 //install from a disabled repositories

yum list | less //list all the packages available in the yum database
yum list installed | less //view all the packages that are installed on your system
yum provides /etc/sysconfig/nfs //know which package a particular file belongs to

chmod +x filename.bin //well bin files you typicallly have to change the file permissions to be able to execute it by typing

./filename.bin //and then run. If instead you want to only extract the RPM file but not install it, you can run the .bin file with the -x argument.

ls /bin /usr/bin /usr/sbin | sort | uniq | grep -i Postfix //this command returned no entries. This means Postfix binaries are not installed on this machine.

groupadd groupname  //adds a new group. The /etc/group file contains all the grops
usermod -a -G groupname username //Add an Existing User to a Group
usermod -g groupname username //Change a User’s Primary Group
id username  //to see what groups the user is assigned to
groups username  //to see what groups the user is assigned to
groups //To view all the groups on the system

service mysqld start //runs mysqld available under /etc/rc.d/init.d. To automatically start and stop a service create symbolic links for /etc/rc.d/init.d/mysqld under /etc/rc.d/rc3.d/S99mysqld and /etc/rc.d/rc0.d/K01mysqld

service mysqld stop //runs mysqld available under /etc/rc.d/init.d

chkconfig --list //displays whether a service is enabled at various run levels.

nautilus . //opens the current directory in the file browser




Shell scripting:

Shell scripts are essentially text files containing sequences of statements that can be executed within the shell environment to perform tasks. In addition to the ability to execute commands, the shell provides many of the programming constructs such as for and do loops and if statements that you might reasonably expect to find in a scripting language.

The first step in creating a shell script is to create a file (for the purposes of this example we name it simple.sh) and add the following as the first line:

#!/bin/sh

The #! is called the shebang and is a special sequence of characters indicating that the path to the interpreter needed to execute the script is the next item on the line (in this case the sh executable located in /bin). This could equally be, for example, /bin/csh or /bin/ksh if either were the interpreter you wanted to use.

The next step is to write a simple script:

#!/bin/sh
for i in *
do
echo $i
done

All this script does is iterate through all the files in the current directory and display the name of each file. This may be executed by passing the name of the script through as an argument to sh:

sh simple.sh

In order to make the file executable (thereby negating the need to pass through to the sh command) the chmod command can be used:

chmod +x simple.sh

Once the execute bit has been set on the file’s permissions, it may be executed directly. For example:

./simple.sh




Filesystem layout (http://www.centos.org/docs/5/html/Deployment_Guide-en-US/s1-filesystem-fhs.html):

File systems break files down into two logical categories:

• Shareable vs. unsharable files
  
• Variable vs. static files
  

Shareable files are those that can be accessed locally and by remote hosts; unsharable files are only available locally. Variable files, such as documents, can be changed at any time; static files, such as binaries, do not change without an action from the system administrator.

/boot/ directory contains static files required to boot the system, such as the Linux kernel.

/dev/ directory contains device nodes that either represent devices that are attached to the system or virtual devices that are provided by the kernel. The udev demon takes care of creating and removing all these device nodes in /dev/.
Devices in the /dev directory and subdirectories are either character (providing only a serial stream of input/output) or block (accessible randomly). Character devices include mouse, keyboard, modem while block devices include hard disk, floppy drive etc.

The /etc/ directory is reserved for configuration files that are local to the machine. No binaries are to be placed in /etc/. Any binaries that were once located in /etc/ should be placed into /sbin/ or /bin/.

/lib/ directory should contain only those libraries needed to execute the binaries in /bin/ and /sbin/. These shared library images are particularly important for booting the system and executing commands within the root file system.

/media/ directory contains subdirectories used as mount points for removeable media such as usb storage media, DVDs, CD-ROMs, and Zip disks. 

/mnt/ directory is reserved for temporarily mounted file systems, such as NFS file system mounts. For all removeable media, please use the /media/ directory. Automatically detected removeable media will be mounted in the /media directory.

/opt/ directory provides storage for most application software packages.
A package placing files in the /opt/ directory creates a directory bearing the same name as the package. This directory, in turn, holds files that otherwise would be scattered throughout the file system, giving the system administrator an easy way to determine the role of each file within a particular package.
For example, if sample is the name of a particular software package located within the /opt/ directory, then all of its files are placed in directories inside the /opt/sample/ directory, such as /opt/sample/bin/ for binaries and /opt/sample/man/ for manual pages.
Packages that encompass many different sub-packages, data files, extra fonts, clipart etc are also located in the /opt/ directory, giving that large package a way to organize itself. In this way, our sample package may have different tools that each go in their own sub-directories, such as /opt/sample/tool1/ and /opt/sample/tool2/, each of which can have their own bin/, man/, and other similar directories.

/proc/ directory contains special files that either extract information from or send information to the kernel. Examples include system memory, cpu information, hardware configuration etc.

/sbin/ directory stores executables used by the root user. The executables in /sbin/ are used at boot time, for system administration and to perform system recovery operations. Of this directory, the FHS says:

/sbin contains binaries essential for booting, restoring, recovering, and/or repairing the system in addition to the binaries in /bin. Programs executed after /usr/ is known to be mounted (when there are no problems) are generally placed into /usr/sbin. Locally-installed system administration programs should be placed into /usr/local/sbin.

/usr/ directory is for files that can be shared across multiple machines. The /usr/ directory is often on its own partition and is mounted read-only. At a minimum, the following directories should be subdirectories of /usr/:

/usr
   |- bin/
   |- etc/
   |- games/   
   |- include/   
   |- kerberos/   
   |- lib/   
   |- libexec/        
   |- local/   
   |- sbin/   
   |- share/   
   |- src/   
   |- tmp -> ../var/tmp/ 

/usr/local hierarchy is for use by the system administrator when installing software locally. It needs to be safe from being overwritten when the system software is updated. It may be used for programs and data that are shareable among a group of hosts, but not found in /usr.

/var/. Since the FHS requires Linux to mount /usr/ as read-only, any programs that write log files or need spool/ or lock/ directories should write them to the /var/ directory. The FHS states /var/ is for:

...variable data files. This includes spool directories and files, administrative and logging data, and transient and temporary files.

Below are some of the directories found within the /var/ directory:

/var
   |- account/   
   |- arpwatch/   
   |- cache/   
   |- crash/   
   |- db/   
   |- empty/   
   |- ftp/   
   |- gdm/   
   |- kerberos/   
   |- lib/   
   |- local/   
   |- lock/   
   |- log/   
   |- mail -> spool/mail/   
   |- mailman/   
   |- named/   
   |- nis/   
   |- opt/   
   |- preserve/   
   |- run/   
   +- spool/
       |- at/        
       |- clientmqueue/        
       |- cron/        
       |- cups/        
       |- exim/               
       |- lpd/        
       |- mail/        
       |- mailman/        
       |- mqueue/        
       |- news/        
       |- postfix/         
       |- repackage/        
       |- rwho/        
       |- samba/         
       |- squid/        
       |- squirrelmail/        
       |- up2date/        
       |- uucp         
       |- uucppublic/        
       |- vbox/   
|- tmp/   
|- tux/   
|- www/   
|- yp/

 System log files, such as messages and lastlog, go in the /var/log/ directory. The /var/lib/rpm/ directory contains RPM system databases. Lock files go in the /var/lock/ directory, usually in directories for the program using the file. The /var/spool/ directory has subdirectories for programs in which data files are stored.