Useful Linux commands

***********NETWORK COMMANDS***********
#To know if a host/ip is reachable

ping hostname/IP

#To see the network route how a host is reachable. Packet flow path.

traceroute hostname/IP 

or

tracepath hostname

# To add route to reach through network when you have multiple network/ethernet NIC cards in a server
route add -net 192.168.49.0 netmask 255.255.255.0 gw 192.168.200.1
        - Here any ip in 192.168.49.XX network is routed via the gateway 192.168.200.1
        - when there are more than 1 NIC, this can be helpful.

#To delete the route
route del -net 192.168.49.0 netmask 255.255.255.0 

#To check if a particular port is open or reachable in a remote server

telnet IP port

or 

nc -zvw10 IPaddress portnumner --> in case of tcp port

nc -zuw10 IPaddress portnumber --> in case of udp port

You will get "succeeded" as output if port is reachable

"Failed: no route to host" when port is not open

"failed: Connection refused" when port is open and no service available on the port.

10 is the time in seconds to wait for the response.

#Quick look on IP addresses available

ip addr

# detailed look on ip address

ifconfig

***********PACKET CAPTURE**************
#To capture packets in the network or ethernet card

tcpdump -s packetsize -i ethernetinterface -w writetofile.pcap host <ipaddress> port <number> or port <number2> 

# Quick read from pcap file one liner.

tcpdump -r filetoread.pcap

*************SYSTEM INFO****************
#To know if a linux machine is 64bit or 32 bit

uname -a 

this will tell u the kernel version, and all.

if u see x86_64, it's a 64bit or i386,i586,etc, it's 32bit

#To know the cpu info

cat /proc/cpuinfo

#To know the memory info

cat /proc/meminfo

#To know the OS name

cat /etc/*release

#To find the disk usage of a directory

du -sh <directory>

try du -sh * for summary of each sub directories and files in current directory.

***************FILE COMMANDS************
# To see top n lines of a file

head -n number filename

# To see botton n lines of a file

tail -n number filename

# To see a particular line of a file like 25th line

head -n 25 filename | tail -n1

# To cut a line into multiple fields

cut -d'<delimiter>' -f<field#> filename

where delimiter is one character and field# is the field number

#To sort above output and print unique values with the count

cut -d'<delimiter>' -f<field#> filename | sort | uniq -c

To be Continued.. 

MySQL useful functions

#To know the current database used
select database();

#To know the version of the database server
select version();

#To know the current logged in user
select user();

#To diff two dates in days,years,etc.
TimeStampDiff(YEAR, date1, date2);
   Param1- const, like YEAR,MONTH,DAY,HOUR,etc
   param2 & 3 - actual dates

#To fetch the year part of a date
select year(<date>);

#To fetch the month part of a date
select month(<date>);

#To fetch the day part of date
select dayofmonth(<date>);

#To get the mod i.e. 5mod3
select mod(5,3);

# Ifnull
select ifnull(exp1,exp2) - this will output exp1 if it's true or else exp2
select ifnull(null,10) - will return 10


# _ represents a character
To find names containing exactly five characters, use five instances of the “_” pattern character:
 SELECT * FROM pet WHERE name LIKE '_____';

# regular expressions

regexp 'w' -> containing w
regexp '^b' -> starting with b
regexp 'fy$' -> ending with fy
regexp '^…..$' -> containing 5 letters
regexp '^.{5}$' -> containing 5 letters

“.” matches any single character.

A character class “[...]” matches any character within the brackets. For example, “[abc]” matches “a”, “b”, or “c”. To name a range of characters, use a dash. “[a-z]” matches any letter, whereas “[0-9]” matches any digit.

“*” matches zero or more instances of the thing preceding it. For example, “x*” matches any number of “x” characters, “[0-9]*” matches any number of digits, and “.*” matches any number of anything.

A REGEXP pattern match succeeds if the pattern matches anywhere in the value being tested. (This differs from a LIKE pattern match, which succeeds only if the pattern matches the entire value.)

To anchor a pattern so that it must match the beginning or end of the value being tested, use “^” at the beginning or “$” at the end of the pattern

# To fetch total records from all tables of a database.
mysqlshow --count databasename -uroot -p


#User defined variable
mysql> SELECT @min_price:=MIN(price),@max_price:=MAX(price) FROM shop;
mysql> SELECT * FROM shop WHERE price=@min_price OR price=@max_price;


To be continued..

MySQL errors and resolutions.

Common Errors & Resolutions

Error 113 - MySQL server/host is not reachable. Check the iptables if MySQL port (default 3306) is open

Error 13 - Permission denied. MySQL service is not able access the file, check the owner/group and the mode of the required file.

Definer Error:

The user specified as a definer ('user1'@'10.10.26.%') does not exist

Reason: The functions,triggers,procedures which was created by some other user than the current user. Mostly happens in dump restoration.

Solution: give grant all privileges on the database to the user you are using.

# perror 28

OS error code  28:  No space left on device

Here’s the complete list:

OS error code   1:  Operation not permitted

OS error code   2:  No such file or directory

OS error code   3:  No such process

OS error code   4:  Interrupted system call

OS error code   5:  Input/output error

OS error code   6:  No such device or address

OS error code   7:  Argument list too long

OS error code   8:  Exec format error

OS error code   9:  Bad file descriptor

OS error code  10:  No child processes

OS error code  11:  Resource temporarily unavailable

OS error code  12:  Cannot allocate memory

OS error code  13:  Permission denied

OS error code  14:  Bad address

OS error code  15:  Block device required

OS error code  16:  Device or resource busy

OS error code  17:  File exists

OS error code  18:  Invalid cross-device link

OS error code  19:  No such device

OS error code  20:  Not a directory

OS error code  21:  Is a directory

OS error code  22:  Invalid argument

OS error code  23:  Too many open files in system

OS error code  24:  Too many open files

OS error code  25:  Inappropriate ioctl for device

OS error code  26:  Text file busy

OS error code  27:  File too large

OS error code  28:  No space left on device

OS error code  30:  Read-only file system

OS error code  31:  Too many links

OS error code  32:  Broken pipe

OS error code  33:  Numerical argument out of domain

OS error code  34:  Numerical result out of range

OS error code  35:  Resource deadlock avoided

OS error code  36:  File name too long

OS error code  37:  No locks available

OS error code  38:  Function not implemented

OS error code  39:  Directory not empty

OS error code  40:  Too many levels of symbolic links

OS error code  42:  No message of desired type

OS error code  43:  Identifier removed

OS error code  44:  Channel number out of range

OS error code  45:  Level 2 not synchronized

OS error code  46:  Level 3 halted

OS error code  47:  Level 3 reset

OS error code  48:  Link number out of range

OS error code  49:  Protocol driver not attached

OS error code  50:  No CSI structure available

OS error code  51:  Level 2 halted

OS error code  52:  Invalid exchange

OS error code  53:  Invalid request descriptor

OS error code  54:  Exchange full

OS error code  55:  No anode

OS error code  56:  Invalid request code

OS error code  57:  Invalid slot

OS error code  59:  Bad font file format

OS error code  60:  Device not a stream

OS error code  61:  No data available

OS error code  62:  Timer expired

OS error code  63:  Out of streams resources

OS error code  64:  Machine is not on the network

OS error code  65:  Package not installed

OS error code  66:  Object is remote

OS error code  67:  Link has been severed

OS error code  68:  Advertise error

OS error code  69:  Srmount error

OS error code  70:  Communication error on send

OS error code  71:  Protocol error

OS error code  72:  Multihop attempted

OS error code  73:  RFS specific error

OS error code  74:  Bad message

OS error code  75:  Value too large for defined data type

OS error code  76:  Name not unique on network

OS error code  77:  File descriptor in bad state

OS error code  78:  Remote address changed

OS error code  79:  Can not access a needed shared library

OS error code  80:  Accessing a corrupted shared library

OS error code  81:  .lib section in a.out corrupted

OS error code  82:  Attempting to link in too many shared libraries

OS error code  83:  Cannot exec a shared library directly

OS error code  84:  Invalid or incomplete multibyte or wide character

OS error code  85:  Interrupted system call should be restarted

OS error code  86:  Streams pipe error

OS error code  87:  Too many users

OS error code  88:  Socket operation on non-socket

OS error code  89:  Destination address required

OS error code  90:  Message too long

OS error code  91:  Protocol wrong type for socket

OS error code  92:  Protocol not available

OS error code  93:  Protocol not supported

OS error code  94:  Socket type not supported

OS error code  95:  Operation not supported

OS error code  96:  Protocol family not supported

OS error code  97:  Address family not supported by protocol

OS error code  98:  Address already in use

OS error code  99:  Cannot assign requested address

OS error code 100:  Network is down

OS error code 101:  Network is unreachable

OS error code 102:  Network dropped connection on reset

OS error code 103:  Software caused connection abort

OS error code 104:  Connection reset by peer

OS error code 105:  No buffer space available

OS error code 106:  Transport endpoint is already connected

OS error code 107:  Transport endpoint is not connected

OS error code 108:  Cannot send after transport endpoint shutdown

OS error code 109:  Too many references: cannot splice

OS error code 110:  Connection timed out

OS error code 111:  Connection refused

OS error code 112:  Host is down

OS error code 113:  No route to host

OS error code 114:  Operation already in progress

OS error code 115:  Operation now in progress

OS error code 116:  Stale NFS file handle

OS error code 117:  Structure needs cleaning

OS error code 118:  Not a XENIX named type file

OS error code 119:  No XENIX semaphores available

OS error code 120:  Is a named type file

OS error code 121:  Remote I/O error

OS error code 122:  Disk quota exceeded

OS error code 123:  No medium found

OS error code 124:  Wrong medium type

OS error code 125:  Operation canceled

MySQL error code 126: Index file is crashed

MySQL error code 127: Record-file is crashed

MySQL error code 128: Out of memory

MySQL error code 130: Incorrect file format

MySQL error code 131: Command not supported by database

MySQL error code 132: Old database file

MySQL error code 133: No record read before update

MySQL error code 134: Record was already deleted (or record file crashed)

MySQL error code 135: No more room in record file

MySQL error code 136: No more room in index file

MySQL error code 137: No more records (read after end of file)

MySQL error code 138: Unsupported extension used for table

MySQL error code 139: Too big row

MySQL error code 140: Wrong create options

MySQL error code 141: Duplicate unique key or constraint on write or update

MySQL error code 142: Unknown character set used

MySQL error code 143: Conflicting table definitions in sub-tables of MERGE table

MySQL error code 144: Table is crashed and last repair failed

MySQL error code 145: Table was marked as crashed and should be repaired

MySQL error code 146: Lock timed out; Retry transaction

MySQL error code 147: Lock table is full;  Restart program with a larger locktable

MySQL error code 148: Updates are not allowed under a read only transactions

MySQL error code 149: Lock deadlock; Retry transaction

MySQL error code 150: Foreign key constraint is incorrectly formed

MySQL error code 151: Cannot add a child row

MySQL error code 152: Cannot delete a parent row

Reference:

https://dev.mysql.com/doc/refman/5.6/en/error-messages-server.html

MySQL quick tips

MySQL useful information:

Info retrieval:

#To retrieve the list of index and primary keys of a table, below query can be used.
show index from tablename;

#To get the list of triggers in the database
show triggers;

#To see triggers related to particular table
show triggers like 'tablename';

#To see the create statement of a table
show create table tablename;

#To create a table just like another existing table
create table newtable like oldtable;

#To see the query execution plan, how MySQL would execute or which index would be used/considered
explain select * from tablename;
Look at the key column to know which index is actually going to be used by MySQL while this query execution. To understand explain output, check http://dev.mysql.com/doc/refman/5.6/en/explain-output.html

#You can force MySQL to use your index, if you wish to.
Have a look at http://dev.mysql.com/doc/refman/5.6/en/index-hints.html

#To see the partitions of table
explain partitions select * from tablename;

#To see all database threads or process or to know what queries are currently being run:
show processlist;

#To know about the database replication status ( slave-IO-thread and Slave-SQL-thread are Yes, then replication is working fine)
show slave status\G

#To know the master co-ordinates and file
show master status\G

# To get the count of rows in all table in a database.
mysqlshow --count -hDBIP -uUSER -p databasename 
     - This will count rows in each table and populate.

Backup:

#To take the backup of MySQL database,
mysqldump --singletransaction -hDBIP -uUSER -p databasename > backupfile.sql

#To take only few particular tables of a database
mysqldump --singletransaction -hDBIP -uUSER -p  databasename table1 table2 > backupfile.sql

#To extract only schema and routines/functions without any data
mysqldump --no-data --routines -hDBIP -uUSER -p databasename > backupfile.sql

#To skip / exclude any tables while doing backup.

mysqldump --singletransaction -hDBIP -uUSER -p  dbname --ignore-table=dbname.table1 --ignore-table=dbname.table2 > backupfile.sql

Restore it:

#To restore database using a backup file
#Login to database server and ensure the file is present in the server.
mysql -uUSER -p databasename < backupfile.sql

Note: You cannot restore few/partial tables out of a backup file which holds data of whole database.
     Always check if there is any error thrown in the output

More options and details are available at the reference link below.


Reference:

http://dev.mysql.com/doc/refman/5.6/en/mysqldump.html