How To, MySQL to CSV - Script

Hi
I wanted to share a new script that I was writing,
This script knows how to take SQL queries, convert them to csv files and send them by eMail.

the script is divided to 2 files:
the first one contain all the sql queries, and the second do all the amazing job.

The fist file called SQL.reports can be download from HERE, contain the sql queries, the syntax must be like the example:

ReportName your_report_name
sql your query

ReportName your_second_report_name
sql select * from tableA where [...]

The script file called MySQL_to_csv.sh can be download from HERE,
the only things you need to do for this to work is to fill the next parameters in the script:

DBhost="localhost"
DBname="your DB name"
DBuser="your DB user name"
DBpass="your DB password"
Mail_to='list of mail with space between them'

please do not touch the rest of the script.

After you have been download those 2 file, placed them in the same directory on your server lets say /opt/reports
and make sure you give them a permission

# chmod +x /opt/reports/*

now all left to do is to run the MySQL_to_csv.sh script
# /opt/reports/MySQL_to_csv.sh
and check you mailBox.
you can also add the script to corntab to run in your own scheduling

Enjoy.
And please comment (-;

Read more >>

How To sort and find duplicate records

Hi,
I'm was asked to sort a password file, to show all duplicate users and their UIDs.
For Example if you have a file like this:

UID    UserName   Password
----   --------   ----------
1236   john       oe93kf9034j
936    max        fkl03kf032j
9381   keren      fg38uf6124t
8988   john       fj3589gjf01
9834   roma       fgj390jf203
8915   max        gf23j09g305
341    john       mf9244t24t4
9841   david      f02jflp3053
43745  david      23hkfg03g35
4956   ron        04fk054f2e4
69595  max        kvf9035g022
7765   john       mg30gk30gkw

I want to show all the duplicate users and all their UIDs like this:

john - 1236 8988 341 7765
max - 936 8915 69595
david - 9841 43745

Solution
To find all duplicate users we'll use the next script:
$ cut -f2 yourFile | sort | uniq -d
it'll display:

john
max
david

Now we want to know all the UIDs of those users
create a new file with vi
$ vi dup_users.sh
and copy the folloing lines:

dup=`cut -f2 yourFile | sort | uniq -d`

for a in $dup
do
        echo $a - `cat yourFlie | grep $a | cut -f1`
done

It will search for each user all UIDs he has and display them like the example on the top.

More
$ sort yourFlie | uniq -u # only the unique, non-repeated lines
$ sort yourFile | uniq -d # repeated lines
$ sort yourFile | uniq -c # all lines + count repeated lines

Read more >>

linux/kernel/panic.c Wallpaper

A New Wallpaper I found    

  Discover Simple, Private Sharing at Drop.io 

   

Read more >>

How To change user name and UID

Hi,
I was asked to change the ID of the mysql user and gourp on one of my servers.
this is a very simple to do.

first type
# id mysql

uid=101(mysql) gid=103(mysql) groups=103(mysql) 

to change the user id type:
# usermod -u 25 mysql
to change the group id run the next command under root user:

# vigr

search the mysql group mysql:x:103: the change it to 25 mysql:x:25:
(save with /wq like regular vi)
OR
# groupmod -g 25 mysql
now run again:
# id mysql

uid=25(mysql) gid=25(mysql) groups=25(mysql)

to change user name run:
# usermod -l old_name new_name
to change home directory run:
# usermod -d /home/user_name user_name
to change group name run:
# groupmod -n old_name new_name

Read more >>

How to configure MySQL Cluster with Heartbeat

In this post we'll discuss how to set up a MySQL cluster with two serves, storage and Heartbeat.
I'm using CentOS 5 distribution on both machines, MySQL 5.1 and a mounted directory on both servers to /var/lib/mysql

Pre-Configuration
You need to install MySQL on both machines.
Assign hostname dbserver01, dbserver02.
dbserver01 is the primary node with IP address 192.168.1.101 to eth0.
and dbserver02 is the slave one with ip address 192.168.1.102.
192.168.1.103 is the virtual IP that will be used for MySQL.

Configuration (do those steps on both servers)
install the Heartbeat package:
# yum install heartbeat

copy the next configuration files to the /etc/ha.d directory:
# cp /usr/share/doc/heartbeat-version/authkeys /etc/ha.d
# cp /usr/share/doc/heartbeat-version/ha.cf /etc/ha.d
# cp /usr/share/doc/heartbeat-version/haresources /etc/ha.d

First we will edit the authkeys file,
# vi /etc/ha.d/authkeys
copy from here:

auth 2
2 sha1 test-HA

change the permission of the authkeys file:
# chmod 600 /etc/ha.d/authkeys

Now let's edit the most important file (ha.cf)
# vi /etc/ha.d/ha/cf
add the following lines into the file:

logfile /var/log/ha-log
logfacility local0
keepalive 2
deadtime 30
initdead 120
bcast eth0
udpport 694
auto_failback on
node dbserver01
node dbserver02 

The last file we need to edit is haresources:
# vi /etc/ha.d/haresources
add the following line:

dbserver01 192.168.1.103 netfs mysqld 

Now all we need to do is to start the hearbeat service on both machines:
# /etc/init.d/heartbeat start
the virtual IP address 192.168.1.103 is now on dbserver01 and MySQL is up and running.
if dbserver01 will crashed from any reason the IP address will jump to dbserver02 the the MySQL service will start automatic.

Enjoy.
And please comment (-;

Read more >>

How to mirror a folder among 2 servers

Hi, in this post I'll show you how to mirror ,synchronize a folder from serverA to serverB with rsync for a backup purpose or what ever you want and need.

First we have to install rsync on both machines for RedHat/Fedora/CentOS you would use:
# yum install rsync
for Debian systems:
# apt-get install rsync
or if you work with SuSE use: yast


Now we need to create a user that will be used by rsync on both servers:
# useradd -d /home/syncuser -m -s /bin/bash syncuser
and give this user a password:
# passwd syncuser


The next step is to make sure that serverA can log into serverB without password so we could create a crontab script which do the synchronization automatic without human interaction.
for this step please go read my post - how to ssh without password

After we test we can ssh from serverA to serverB without password, we can test the rsync,
make sure you have a folder on serverB that you want to backup all your data to and run the next command on serverA:
# rsync -raz --progress --size-only --delete /DirOnServerA/* syncuser@serverB:/DirOnServerB


Now go the serverB and check if you data are there.

All you left to do is to to add the rsync script to crontab and you can sleep well at night.




.

Read more >>

How to mount dir via NFS

Hi, for mount dir from another server you have to use NFS service
NFS - Network File System
First we need to install nfs service on both servers.
logon to your server with root user, and run:

# yum install nfs-utils nfs-utils-lib nfs-utils-lib-devel

After installation finished we need to tell the client machine (the one with the existing folder) that we want to share a folder.
edit /etc/exports like this:

# vi /etc/exports
and add the next line:

/the/folder/you/want *(rw,no_root_squash,async)

Now you need to be shore the NFS ports are open on your server: 2049/tcp 2049/udp and 111/tcp 111/udp

After everything done you can start the NFS service on both machines by:

#/etc/init.d/nfs start

Now you can mount your dir via NFS but first you need to open a folder to the mount one, by:

# mkdir /mnt/DIR
# mount servername:/the/folder/you/want /nmt/DIR

that's it, you done.

If you want the mount to be permanently (mount automatic when restart), you can do it by edit fstab file
and add it the next line:

# vi /etc/fstab

/servername:/the/folder/you/want /mnt/DIR nfs defaults 0 0

# mount -a


ENJOY.

Read more >>