Thursday 26 October 2017

Useful SQL command for MySQL database on Linux Operating System

This post related to MySQL database SQL command which is used in daily database work by database administrator.

Please find the below points which is very useful to database admin on Linux operating system.

How to create new database:

 CREATE DATABASE <DATABASENAME>;

 e.g. CREATE DATABASE XYZ;

In this example I have created the "XYZ" named database, you can replace database name according to your choice.

How to create mater user with all level access to all available databases:

 GRANT ALL ON *.* TO <USERNAME> IDENTIFIED BY '<PASSWORD>';
 e.g.  GRANT ALL ON *.* TO TESTDBUSER IDENTIFIED BY 'TESTDBUSER';

In this example I used "testdbuser" MySQL user on the server.

How to take backup (dump) of a database:

For this command you need not to login into the database. Once you login on the database please run the below command to take a dump of server.

 mysqldump --lock-all-tables -u <USERNAME> -p -h <DATABASESERVERNAME/IP ADDRESS> <DATABASENAME> > <NEWDUMPFILENAME>.sql

 e.g. mysqldump --lock-all-tables -u ABC -p -h MACHINE1 XYZ > XYZ_16022017.sql

After this command you have to provide DB password only and dump will be done in the folder in which u are working currently.

How to take backup (dump) of a table's:

For this command you need not to login into the database.

 mysqldump -u <USERNAME> -p <DATABASENAME> <TABLENAME> > <NEWDUMPFILENAME>.sql

 e.g. mysqldump -u testdbuser -p testdb test_book > test.sql

After this command you have to provide DB password only and dump will be done in the folder in which u are working currently. for dumping multiple table use space between the table names.

How to run a database dump to another machine:

Go to the folder in which sql dump file is placed and then connect with your Database in which you want to run dump and then run below command.

 source <FILENAME>.sql;

 e.g source XYZ_16022017.sql;

How to take dump only triggers and procedure from database:

 mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt -u <USERNAME> -p <DATABASENAME> > <NEWDUMPFILENAME>.sql

 e.g. mysqldump --routines --no-create-info --no-data --no-create-db --skip-opt -u TESTDBSUER -p XYZ > XYZ_16022017.sql

After this command you have to provide DB password only and dump will be done in the folder in which u are working currently.

How to show procedure/function code:

 SHOW CREATE PROCEDURE <STORED PROCEDURE/FUNCTION NAME>\G

 e.g. SHOW CREATE PROCEDURE FLIGHT_INVOICETOSAP\G;

How to increase filed width of a table attribute:

 ALTER TABLE <TABLENAME> CHANGE <EXISTING_FIELD_NAME> <NEW_FIELD_NAME> <DATATYPE>(<NEW_FIELD_WIDTH>);

 e.g. ALTER TABLE TRILOK CHANGE PASSENGER_NO PASSENGER_NUMBER VARCHAR(30);

field name and data type change is not recommended.

How to display all existing databases:

 SHOW DATABASES;

Using this command you can show all the created database list on the Linux server.

How to display all existing tables:

 SHOW TABLES;
 SHOW TABLES LIKE '<CHARACTERS>%';

Using this command you can check all the tables which is created on the database.

How to recover a MySQL root password:

This is one of the best way to recover the MySQL root password if you forget. I always used below method to reset the root password.
Stop the MySQL server process.

  # /etc/init.d/mysql stop
Start again with no grant tables.

  # mysqld_safe --skip-grant-tables &
Login to MySQL as root. Set new password.

  # mysql -u root
  mysql> use mysql;
  mysql> update user set password=PASSWORD("newrootpassword") where User='root';
  mysql> flush privileges;
  mysql> quit
Exit MySQL and restart MySQL server.

  # /etc/init.d/mysql stop
  # /etc/init.d/mysql start
Set a root password if there is on root password.

  # mysqladmin -u root password newpassword
Update a root password.

  # mysqladmin -u root -p oldpassword newpassword

How to grant privileges to a user:

If we need to provide the privileges to user we will use below two method. I will describe the step by step method for this one.

METHOD 1

Allow the user "bob" to connect to the server from localhost using the password "passwd". Login as root. Switch to the MySQL db. Give privs. Update privs.

  # mysql -u root -p
  mysql> use mysql;
  mysql> grant usage on *.* to bob@localhost identified by 'passwd';
  mysql> flush privileges;

Give user privileges for a db. Login as root. Switch to the MySQL db. Grant privs. Update privs.

  # mysql -u root -p
  mysql> use mysql;
  mysql>INSERT INTO user (Host,Db,User,Select_priv,Insert_priv,Update_priv,Delete_priv,Create_priv,Drop_priv) VALUES ('%','databasename','username','Y','Y','Y','Y','Y','N');
  mysql> flush privileges;

METHOD 2

  mysql> grant all privileges on databasename.* to username@localhost;
  mysql> flush privileges;

To update info already in a table.

Load a CSV file into a table:

 mysql> LOAD DATA INFILE '/tmp/filename.csv' replace INTO TABLE [table name] FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n' (field1,field2,field3);

Dump all databases for backup:

Backup file is SQL commands to recreate all databases.

  # [mysql dir]/bin/mysqldump -u root -ppassword --opt >/tmp/alldatabases.sql

Dump one database for backup.

  # [mysql dir]/bin/mysqldump -u username -p password --databases databasename >/tmp/databasename.sql

Dump a table from a database.

  # [mysql dir]/bin/mysqldump -c -u username -p password databasename tablename > /tmp/databasename.tablename.sql

Restore database (or database table) from backup.

  # [mysql dir]/bin/mysql -u username -p password databasename < /tmp/databasename.sql
  Create Table Example 1.

Using above method we can take database back very fast. I hope through this post you can easily able to create MySQL database, MySQL dump, table creation etc work.

Friday 20 October 2017

Solaris zone: error: net0: failed to create VNIC: operation not supported

In this post, I will discuss with you one of the most interesting error which I am facing when I boot the local zone on Solaris 11.3. The description of this interesting issue as describe below.

Description of error:

!-[solaris]# zoneadm -z sun01 boot

zone 'sun01': error: net0: failed to create VNIC: operation not supported

zoneadm: zone sun01: call to zoneadmd(1M) failed: zoneadmd(1M

I have try to create and configure the  VNIC on Solaris 11.3 operating server but it get failed with the same error.

!-[solaris]#dladm create-vnic -l net0 vnic01

dladm: vnic creation failed: operation not supported

If you are also facing a such error while booting the local zone on solaris 11 server, then please use the below solution to resolve such issues.

Solution of error:

➥ This error "failed to create VNIC: operation not supported" would normally come when there are not enough mac addresses to assign to the zone. So now we need to add alternate mac addresses to the network interface.So before adding the new mac address we will stop LDM.

!-[solaris]#ldm list-domain

NAME            STATE     FLAGS  CONS   VCPU MEMORY  UTIL NORM UPTIME

primary         active    -n-cv- UART   8    8G      2.0% 2.0% 41d 20h 14m

0004fb0000060000ff1d3d8336112f6f active    -n---- 5001   50   64G     0.1% 0.1% 18h 23m

➥ Now log in to the Solaris global zone and check if net0 have additional MAC addresses or not. Please use the below command to check the status.

!-[solaris]# dladm show-phys -m

LINK               SLOT    ADDRESS           INUSE CLIENT

net0               primary 0:21:f6:d6:d3:e5  yes  net0

                   1       0:14:4f:f9:6d:8d  no   --

                   2       0:14:4f:fb:10:2b  no   --

                   3       0:14:4f:f9:41:d6  no   --

                   4       0:14:4f:f8:dd:c8  no   --

net1               primary 0:21:f6:51:be:4d  yes  net1

➥ Now zone will start without any issue, as we have assigned the new mac address to this zone.

!-[solaris]# zoneadm -z sun01 start

Please let me know if you are facing any issue regarding this error.

How to mount CIFS file system on Linux Operating Server

Step by Step method for Mount CIFS on Linux Server:

➤ In the initial step we will gather all rpm's or packages information which is required for CIFS file system.

[root@localhost]# rpm -qa | grep cifs 
cifs-utils-4.8.1-20.el6.x86_64

if you see the above output, this packages is required for CIFS file system on Linux system. So you can install this rpm using rpm command if you have rpm packages on the server otherwise you will install the packages using YUM utility.

[root@localhost]#rpm -ivh cifs-utils-4.8.1-20.el6.x86_64.rpm

if you are using YUM, then please install the packages using below command.

[root@localhost]#yum install cifs-utils*

it is installed all required dependency related to CIFS file system.

➤ In this step, we will create the mount point on the server where we need to mount the CIFS file system.

[root@localhost]# mkdir -p /backup/cifs

We will create the above mount directory where i will mount the file system.

➤ Now, we will create a CIFS user and assign a password so the user can access mount folder.

[root@localhost]# touch /etc/cifspasswd
[root@localhost]# chmod 600 /etc/cifspasswd

[root@localhost]#vi /etc/cifspasswd

user=castwebsvc
password=*******

➤ In this step we need to make a permanent entry of mounted file system so once we reboot the machine mounted file system not umount.

[root@localhost]# vi /etc/fstab

ADD fstab entry :

//WindowsServer/share /mount/point cifs rw,mand,user=USER,password=PASS 0 0

Example :
//192.168.0.1/CAST_data4/AICCodeUpload  /backup/cifs cifs   rw,mand,credentials=/etc/cifspasswd         0 0

You can take an example for your system. Please change the mount point as per your requirement.

➤ In the final step you need to mount the file system using below command.

[root@localhost]# mount /backup/cifs
[root@localhost]# mount -a

Using above command the CIFS file system has been mounted successfully.

How to configure NTP server and client on Solaris 10 and Solaris 11

As you know NTP ( Network time Protocol) is one of the oldest internet protocol still in use and it allows the synchronization of computer clocks distributing UTC (Coordinated Universal Time) over the network.

NTP Service on Solaris 10 and Solaris 11:

Solaris 10 used the SMF utility (Service Management Facility) and the NTP service is now managed by SMF (Service Management Facility). NTP daemon configured using Service management facility (svc:/network/ntp:default) and a bunch of sample ntp.conf files to quickly configure a machine as a client or as a server. On Solaris 11 only ships with NTP v. 4, the NTP v. 4 service is identified by the name ntp4. You can check the ntp status using below command.

!-[solaris]# svcs status ntp

STATE  STIME   FMRI
online 10:14:23 svc:/network/ntp:default

If you see the above command output it is shown that network time protocol services is enable and online on the server.

Steps for Configuring a NTP client:

Suppose your machine is just a client machine, then you can just take the /etc/inet/ntp.client file and copy it to /etc/inet/ntp.conf.

multicastclient 127.0.0.1

If you see the configuration it's a passive configuration for a Server host which listens for NTP server putting packets on the NTP multicast network, 127.0.0.1. If your machine is on LAN without NTP server then in that case we are not recveied any packet and for this we need to use Public NTP server for host.

In my case, I'm using the Indian pool in.pool.ntp.org and my configuration file contains:

server 2.in.pool.ntp.org
server 1.asia.pool.ntp.org
server 3.asia.pool.ntp.org

Normally NTP requires a poll period to elapse before starting synchronizing your clock. If you want NTP to start immediately, which you most probably will if you're configuring a desktop environment, you can take advantage of iburst keyword, introduced in NTP v. 4: it instructs NTP to start the synchronization almost right away.

server 2.in.pool.ntp.org ibrust
server 1.asia.pool.ntp.org ibrust
server 3.asia.pool.ntp.org ibrust

You must make sure you're configuring NTP implementation corresponding to the syntax you're using.

Setting up the drift file:

The last thing which is remaining for NTP server setup in the client machine is to set up  drift file location. On my machine it is 

driftfile /var/ntp/ntp.drift

After setup the drift file configuration we will start the NTP servivce again 

!-[solaris]# svcadm restart svc:/network/ntp:default
!-[solaris]# svcs status svc:/network/ntp:default
STATE  STIME   FMRI
online 12:20:12 svc:/network/ntp:default 

Once the service is running, you can check which server you're using with ntpq, Please run the below command to check the ntpq.

!-[solaris]# ntpq -p

Setting up an NTP server:

Now in above step you see the NTP service has been started successfully, so now, you'll probably want to setup all of your machines.

If you're in a LAN, you can setup an internal NTP server which will provide data to other clients on your LAN. As before, you can take inspiration from the server configuration file shipped with Solaris 10 or Solaris 11, /etc/inet/ntp.server.

After setting up the drift file and the clients you're going to use, you can examine the other options and fine-tune them at your taste. Let's give a quick look at it.

server 127.127.XType.0

Now you have configured the NTP server properly. Please comment on the post if you have any suggestion.

Monday 11 September 2017

Solaris – Add/remove network interface to a running zone (dynamic Change)

This will describe how to add a network interface to a running non-global zone, without having to reboot the zone. The new interface will persist between reboots.

First you add the entry to the zone configuration. This is the part that lets it persist between reboots. This is done from the global zone:

!-[solaris]# zonecfg -z sunz01
zonecfg:slabsunz01> add net
zonecfg:slabsunz01:net> set address=XXX.XXX.XX.XXX
zonecfg:slabsunz01:net> set physical=bge0
zonecfg:slabsunz01:net> end
zonecfg:slabsunz01> verify
zonecfg:slabsunz01> commit
zonecfg:slabsunz01> exit

Now we have to manually add a new interface to the running zone. Do this from the global zone as well

!-[solaris]# ifconfig bge0 addif XXX.XXX.XX.XXX netmask XXX.XXX.X.X zone sunz01 up

Created new logical interface bge0:3

Note: The ‘addif’ tells ifconfig to create a logical interface using the next available.

!-[solaris]# ifconfig -a
lo0:1: flags=2001000849 mtu 8232 index 1 inet 127.0.0.1 netmask ff000000
bge0:1: flags=1000843 mtu 1500 index 2 inet XXX.XXX.XX.XXX netmask ffffff00 broadcast XXX.XXX.XX.XXX
bge0:3: flags=1000843 mtu 1500 index 2 inet XXX.XXX.XX.XXX netmask ffffff00 broadcast XXX.XXX.XX.XXX

That's it! you're done.

In case you want to remove the interface -

To remove the interface from a running zone. From the global zone, remove the interface. You must first determine which logical interface [alias] you wish to remove.

!-[solaris]# ifconfig bge0:3 down
!-[solaris]# ifconfig bge0:3 unplumb
!-[solaris]# zonecfg -z sunz01
zonecfg:slabsunz01> remove net address=XXX.XXX.XX.XXX
zonecfg:slabsunz01> commit
zonecfg:slabsunz01> exit

Thursday 31 August 2017

Remotely shutdown Linux & windows machine using shell script

Please find the below script for shutdown the Unix & Windows server remotely.

#!/bin/bash

 SSH=/usr/bin/ssh
 POWERCMD="/sbin/shutdown -h now"
 HOSTS="server1 server2 server3"

 # Shutdown remote machines
 for i in $HOSTS;
 do
   $SSH -l root $i $POWERCMD;
 done

  telnet=/usr/bin/telnet
  POWER="C:\Windows\System32\shutdown.exe \s"
  IP="server1 server2 server3"

  # Shutdown window machine
  for i in $IP;
  do
        $telnet -l administrator $i $POWER;
  done

 # Shutdown ourself
 /sbin/shutdown -h now

ifconfig command not found in centos 7 minimal installation

By default in Centos 7 minimal installation , ifconfig command is not found, so for obtaining this command , first you need to check which packages yum provide for this.

[root@localhost]# yum provides ifconfig

Loaded plugins: fastestmirror
Repository 'Packages' is missing name in configuration, using id
Loading mirror speeds from cached hostfile
Packages/filelists_db                                                                                                                              
No matches found

Note: here no packages is installed on the system.

So, using yum we will install the net-tool rpm which is required for ifconfig command

[root@localhost]# yum install net-tools*

Loaded plugins: fastestmirror
Repository 'Packages' is missing name in configuration, using id
Loading mirror speeds from cached hostfile
Resolving Dependencies
--> Running transaction check
---> Package net-tools.x86_64 0:2.0-0.17.20131004git.el7 will be installed
--> Finished Dependency Resolution

Dependencies Resolved

 Package                  Arch           Version                                   Repository                    Size

Installing:
 net-tools                x86_64         2.0-0.17.20131004git.el7         Packages                        304 k

Transaction Summary
========================================================================
Install  1 Package

Total download size: 304 k
Installed size: 917 k
Is this ok [y/d/N]: y
Downloading packages:
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
Installing : net-tools-2.0-0.17.20131004git.el7.x86_64                                                                     1/1
Verifying  : net-tools-2.0-0.17.20131004git.el7.x86_64                                                                    1/1

Installed:
net-tools.x86_64 0:2.0-0.17.20131004git.el7

Complete!

Now run the ifconfig command after successfully installed the packages.

[root@localhost]# ifconfig -a
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.26.12.1  netmask 255.255.0.0  broadcast 192.26.255.255
        inet6 fe80::250:56ff:febf:f6  prefixlen 64  scopeid 0x20<link>
        ether 00:50:56:bf:00:f6  txqueuelen 1000  (Ethernet)
        RX packets 18939  bytes 1251073 (1.1 MiB)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 433  bytes 63109 (61.6 KiB)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

lo: flags=73<UP,LOOPBACK,RUNNING>  mtu 65536
        inet 127.0.0.1  netmask 255.0.0.0
        inet6 ::1  prefixlen 128  scopeid 0x10<host>
        loop  txqueuelen 0  (Local Loopback)
        RX packets 0  bytes 0 (0.0 B)
        RX errors 0  dropped 0  overruns 0  frame 0
        TX packets 0  bytes 0 (0.0 B)
        TX errors 0  dropped 0 overruns 0  carrier 0  collisions 0

[root@localhost]# yum provides ifconfig

Loaded plugins: fastestmirror
Repository 'Packages' is missing name in configuration, using id
Loading mirror speeds from cached hostfile
net-tools-2.0-0.17.20131004git.el7.x86_64 : Basic networking tools
Repo: @Packages
Matched from:
Filename: /usr/sbin/ifconfig