Import data to mysql database from .txt files using “Sed”

Review the post :http://sanjaybdalal.wordpress.com/2009/05/08/export-mysql-tables-in-txt-files/  . In this post we have export the mysql database data into the .txt files. 

Now we are import the data from .txt files to mysql database. For that we have to reformate the .txt files data which will support in the sql format.

#cat example.txt

1,SANJAY,AHMEDABAD,SYSTEM ADMIN

1,PRIYA,PUNE,PERL DEVELOPER

1,AKSHAY,GONDAL,PERL DEVELOPER

1,MIHIR,MUMBAI,PERL DEVELOPER

Now using “sed” command we will format this file to support sql.

 

sed -e ’s/,/”,”/g’ -e ’s/^/insert into example values(“/g’ -e ’s/$/”);/g’ example.txt

#sed -e ’s/,/”,”/g’ -e ’s/^/insert into example values(“/g’ -e ’s/$/”);/g’ example.txt > example.txt.new

where ^ represent start to the line and $ represent end of the line.

Output :

#cat example.txt.new

 insert into example values(“1″,”SANJAY”,”AHMEDABAD”,”SYSTEM ADMIN”);

insert into example values(“1″,”PRIYA”,”PUNE”,”PERL DEVELOPER”);

insert into example values(“1″,”AKSHAY”,”GONDAL”,”PERL DEVELOPER”);

insert into example values(“1″,”MIHIR”,”MUMBAI”,”PERL DEVELOPER”);

Export mysql tables in .txt files

mysql> use test;

mysql> SELECT *FROM example;

+——+——–+———–+—————-+

| ID   | NAME   | ADDRESS   | REMARKS        |

+——+——–+———–+—————-+

|    1 | SANJAY | AHMEDABAD | SYSTEM ADMIN   |

|    1 | PRIYA  | PUNE      | PERL DEVELOPER |

|    1 | AKSHAY | GONDAL    | PERL DEVELOPER |

|    1 | MIHIR  | MUMBAI    | PERL DEVELOPER |

+——+——–+———–+—————-+

4 rows in set (0.00 sec)

#mysqldump -u root -p –fields-terminated-by=, –tab=/location –table test example 

Above command will create the file example.txt which contains the export data in /location directory

Output: example.txt

#cat /location/example.txt

1,SANJAY,AHMEDABAD,SYSTEM ADMIN

1,PRIYA,PUNE,PERL DEVELOPER

1,AKSHAY,GONDAL,PERL DEVELOPER

1,MIHIR,MUMBAI,PERL DEVELOPER

Read File using Shell Script

#!/bin/bash

FILENAME=”filename.txt”

exec 0< $FILENAME

while read LINE

do

echo $LINE

done

 

#!/bin/bash

FILENAME=”filename.txt”

exec 0< $FILENAME

while read LINE

do

     echo $LINE

done

Remove Blank lines from file

 

sed ‘/./!d’ backupfilelist.txt > temp1.txt

sed ‘/^$/d’ backupfilelist.txt > temp1.txt

grep -v ‘^   

filename.txt > temp1.txt

Tech 1 :   sed ‘/./!d’ filename.txt > temp1.txt

Tech 2 :  sed ‘/^$/d’ filename.txt > temp1.txt

Tech 3 :  grep -v ‘^$’ filename.txt > temp1.txt

Shell Script to take Backup Files & Directory

Step 1 : create a file /path/to/backupfilelist.txt which contains the files & directory which you want to take bacup

Step 2 : Write a script backup_files_and_directory.sh

#!/bin/sh

#Script for take a backup of list of files and directory in one compress file

 

#file format which contains backup files & directory

FILENAME=”`date +%d-%m-%Y`.tar.gz”

#This file contains the files & directory which we are going to take backup

BACKUPFILELIST=”/path/to/backupfilelist.txt”

#Directory where backup kept

BACKUPDIRECTORY=”/root/filebackup”

TAR=`which tar`

 

#create backup directory if not exist

if [ ! -d $BACKUPDIRECTORY ]; then

mkdir -p /root/filebackup

fi

#check backupfilelist.txt is available or not

if [ -f $BACKUPFILELIST ]; then

        BACKUPFILE=`cat $BACKUPFILELIST | grep -E -v “^#”`

else

        echo “File not Exist…Please create Backup file first”

        exit 1

fi

#check for the backupfilelist.txt null or not

if [ $BACKUPFILELIST = "" ]; then

        echo “Backup file list is empty..Please add some files or directory to take backup”

        exit 2

fi

#take a bakcup

$TAR -zcvf $BACKUPDIRECTORY/$FILENAME $BACKUPFILE > /dev/null

Step 3 : make script executable 

#chmod +x backup_file_and_directory.sh

Step 4 : execute file

#./backup_file_and_directory.sh

Mysql database Backup Script

#!/bin/bash

MYSQL=`which mysql`             #mysql version

MYSQLDUMP=`which mysqldump`     #mysqldump file

HOSTNAME=`hostname`             #hostname of backup server

USERNAME=”username”                 #mysql server user

PASSWORD=”password”            #mysql user password

LOCALSERVER=”localhost”         #mysql local server name

BACKUPDIRECTORY=/root/mysqldbbackup     #backup directory where backup is stored

NOW=”$(date +”%d-%m-%Y”)”               #Backup file format

DATATBASELIST=”"                        #Mysql database list

IGNOREDATABASE=”exampledb”                  #Ignore database not to be include in backup

GZIP=`which gzip`                       #Gzip to compress the backup file

FILENAME=”"                             #Database backup file names

#store the database list in DATABASELIST

DATABASELIST=`$MYSQL -u $USERNAME -h $LOCALSERVER -p$PASSWORD -Bse ’show databases’`

#Fetch database on by one and take a backup

for db in $DATABASELIST

do

        skipdatabase=-1

        if [ "$IGNOREDATABASE" != "" ]; then

                for i in $IGNOREDATABASE        #check the ignore database list

                do

                        if [ "$db" = "$i" ]; then

                                skipdatabase=1        #it will skip this database and move to second database list

                        fi

                done

        fi

        if [ "$skipdatabase" = "-1" ]; then

                FILE=”$BACKUPDIRECTORY/$db.$HOSTNAME.$NOW.gz” #Backup file format & Backup directory

                #ALL in one command to take backup & compress the backup file

 $MYSQLDUMP -u $USERNAME -h $LOCALSERVER -p$PASSWORD $db | $GZIP -9 > $FILE

 

Mysql Replication status notification

Using the script you can get the alert message from the replication server if replication is down or not working.

——————————————————————————————–

#!/bin/bash

PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/sbin:/bin

#script checking the replication is running or not.

#If replication is down then sent the alert mail.

slave_server_hostname=192.168.10.1

###check if already notified###

cd /root

if [ -f slave_problem.txt ]; then

rm -rf /root/slave_problem.txt

exit 1;

fi

 

###Check if slave running###

(

echo “show slave status \G;”

) | mysql -u username -h $slave_server_hostname -ppassword 2>&1 | grep “Slave_IO_Running: No”

if [ "$?" -ne "1" ]; then

echo “Replication Failed”

echo “Replication failed” > /root/slave_problem.txt

fi

 

###Send notification if replication down###

cd /root

if [ -f slave_problem.txt ]; then

#mail -s “Replication problem” mail_id@domainname.com< /root/slave_problem.txt

echo “Problem in replicaition”

fi

———————————————————————————————-

Setup this script in conrtab of Master server

#chmod +x /path/to/Checkreplication.sh

#crontab -e

* * * * * /path/to/Checkreplication.sh

Disable Users to login into the server

Suppose we want to take a backup of user’s account. So for that first of all we have to block the user to login into their account to maintain data integrity of user’s backup files. So using below technique we can do that very easily.enjoy !!!

Edit the pam file located in /etc/pam.d/ directory for the service you want to control.

 Example : Suppose you want to do control  ssh service

Step 1: Add below line in /etc/pam.d/sshd file if it is not available.

account required pam_nologin.so

Step 2: Create the /etc/nologin file,

# touch /etc/nologin

This should disable the login from ssh for every user except administrator user(root).

Step 3: To re-enable the login just remove /etc/nologin

# rm –rf /etc/nologin

Split the Large files

Split on a 300mb example.zip file:

#split -b 100mb example.zip

It will generate 3 files with the following file sizes:

100MB xaa
100MB xab
100MB xac

After split use: cat to combine a file

#cat xa* > example-new.zip 

 

Time bases iptables rules

If you want to restrict/allow access to certain service on timely basis using iptables. 

Use : iptables patch-o-matic extension (pom or p-o-m)

That allows us to match a packet based on its arrival or departure  timestamp.

Syntax : iptables RULE -m time –timestart TIME –timestop TIME –days DAYS -j ACTION

                –timestart TIME: Time start value (format is 00:00-23:59)

               –timestop TIME: Time stop value (the same format)

               –days DAYS: a list of days to apply, from (format: Mon, Tue, Wed, Thu, Fri, Sat, Sun).

Example :  We want to  restrict access to SSH between 10:00 pm – 8:00am on weekdays.

#iptables -A INPUT -p tcp -d 192.168.10.1 –dport 22 -m time –timestart 22:00 –timestop 8:00 -days Mon,Tue,Wed,Thu,Fri -j DROP

Enjoy !!!!!!!!!!!!