How to generate CSV in user's home directory through mysql

In this post I will try to explain how you can generate CSVs through MySQL queries in user's home directory so that only that user and MySQL has read/write access on that directory.  For example, there is a 'zaeem' with home directory '/home/zaeem/'. If you try to generate CSVs in /home/zaeem then MySQL gives Errcode: 13 that it doesn't have permission to write on this directory. To resolve this you need to create a group and add both users 'zaeem and 'mysq' to that group and change group of '/home/zaeem' to new group and giver permissions on the directory, restart MySQL and your issue will be resolved. Following are the commands to accomplish that.

1. useradd zaeem
2.  
3. groupadd csv
4. usermod -G csv  mysql
5. usermod -G csv  zaeem
6. chgrp -R csv zaeem/
7. chmod 0770 -R /home/zaeem/
8. service mysql restart
9. Login to MySQL and try to generate CSV in /home/zaeem

Hope this will help. 

Cheers!

Replication Monitor Shell Script

High Performance MySQL: Optimization, Backups, Replication, and MoreHigh Performance MySQLMySQL Transactions and Replication HandbookThere should be automatic way to initiate alerts if MySQL replication has stopped working because it is impossible for human being to monitor replication all the time and fix it if there is any issue. Following is the shell script which is very useful.




status=0
MasterHost="192.168.0.14"
SlaveHost="192.168.0.12"
emails="zaeem@zaeem.com" #multiple emails space separated
DownSubject="Replication status - Down"
GoodSubject="Replication status - Good"
GoodMessage="Everything regarding MySQL replication on $SlaveHost is good.\nHave a great day!\n\n"

#Grab the lines for each and use Gawk to get the last part of the string(Yes/No)
SQLresponse=`mysql -u root --password=password test -e "show slave status \G" |grep -i "Slave_SQL_Running"|gawk '{print $2}'`
IOresponse=`mysql -u root --password=password test -e "show slave status \G" |grep -i "Slave_IO_Running"|gawk '{print $2}'`
# Sometimes there is scheduled backup which takes backup by stopping slave but if this script finds slave stopped then it start it immediately. So when there is backup going on a file is created under tmp partition and this scripts keep checking it and if file exist it assume backup is going on so it doesn't start slave, otherwise it start slave.
FILE=/tmp/backup.back
if [ -f $FILE ];
then
echo "Backup is in progress"
echo -e "Backup is taking place on 192.168.0.12"
else

if [ "$SQLresponse" = "No" ]; then
       error="Replication on the slave MySQL server($SlaveHost) has stopped working.\nSlave_SQL_Running: No\n. \nTrying to start it\n"
       status=1
       mysqladmin --user=root --password=password start-slave
fi

if [ "$IOresponse" = "No" ]; then
      error="Replication on the slave MySQL server($SlaveHost) has stopped working.\nSlave_IO_Running: No\n. \nTrying to start it\n"
      status=1
      mysqladmin --user=root --password=password start-slave
fi

# If the replication is not working
if [ $status = 1 ]; then
      for address in $emails; do
               # echo -e $error | mail -s $DownSubject $address
                echo -e $error | mail -s "Replication is Down" $address
                echo "Replication down, sent email to $address"
      done
fi

# If the replication is working fine
if [ $status = 0 ]; then
for address in $emails; do
                echo -e $GoodMessage | mail -s $GoodSubject $address
                 echo "Replication is up, still sent email to $address"
      done
fi
fi


You can schedule this script to run after each five minutes to make sure that replication is up and running all the times.

High Performance MySQL: Optimization, Backups, Replication, and More
MySQL High Availability: Tools for Building Robust Data Centers
High Performance MySQL
MySQL Transactions and Replication Handbook

Command to calculate number of rows in a file

Sometimes there are so many files and you want to count number of rows in each file the a command or a shell script is handy to get it done otherwise its almost impossible to count number of rows in file. Following are some ways to calculate this.

1. wc -l *.csv (This command will count rows per csv file)
2. #!/bin/bash
   rows=0;
   for f in `*.CSV`;
     do
       let rows+=`wc $f | awk '{print $1}'`
   
     done
   
   echo "Total rows=$rows"

I hope this small tutorial is of help to you guys.

[Advanced ]Mysqldump, a database backup/restore utility

I will try to cover all possible methods to take backup and restore backup using mysqldump utility.


How to take database backup using mysqldump
mysqldump -u[username] -p[password] [database name] > dum_file_name.sql
e.g
mysqldump -uroot -ppassword mysql > mysql.sql

How to take backup of multiple databases at the same time

mysqldump -u[username] -p[password] [database 1 name]..[database 2 name]
e.g

mysql -uroot -ppassword DB1 DB2 DB3

How to take backup all the databases
mysqldump -u[username] -p[password] -all-databases > [dumpfile.sql]

How to take backup of specific table of a database

mysqldump -u[username] -p[password] [database name] [table Name] > dumpFIle.sql

e.g.

mysqldump -uroot -ppassword mysql user > user.sql

How to backup of table structures:

mysqldump -d -uroot -ppassword database01 > dumpfile.sql

How to restore backup
Database backup can be restored using mysqldump as well as mysql command.

mysql -u[user] -p[password] database name < dumpfile.sql

e.g.
mysql -uroot -ppassword mysql < mysql.sql

I hope you find this article helpful.

Suggestions are always welcome :)

500 OOPS: cannot locate user entry:nobody

While routine examination I saw a process running on server of user nobody. I thought this user is suspicious so I killed all the processes of this user (pkill -u nobody) and then deleted the user using command (userdel -r nobody) however delete command gave me error userdel: / not owned by nobody, not removing. Due to this people are unable to connect to my server through FTP and when they try they get error like 500 OOPS: cannot locate user entry:nobody.


How to Fix: Just all add "ftp_username=nobody" in /etc/vsftp.conf. Then restart vsftpd service (service vsftpd restart).

Enjoy :)

What are control files and how we can back them up

Control files [Oracle]
       
The control files of a database store the status of the physical structure of the database. The control file is absolutely crucial to database operation. It contains (but is not limited to) the following types of information:

    * Database information (RESETLOGS SCN and their time stamp)
    * Archive log history
    * Tablespace and datafile records (filenames, datafile checkpoints, read/write status, offline or not)
    * Redo threads (current online redo log)
    * Database's creation date
    * database name
    * current archive log mode
    * Log records (sequence numbers, SCN range in each log)
    * RMAN catalog
    * Database block corruption information
    * Database ID, which is unique to each DB

The location of the control files is specified through the control_files init param.
 

Backing up Controlfiles
When a controlfile (multiplexed or not) becomes unavailable (because for example of a media failure, the database cannot continue to operate.Because control files keep track of datafiles and the control file should be backed up whenever a change is made to the database (alter database).

The command to back the control file up is: alter database backup controlfile to 'filename' e.g

alter database backup controlfile to trace as '/home/oracle/controlfile.txt';.
 

Recovering database when controlfile is lost

 
If the controlfile is lost, it is somewhat difficult to do a recovery because the database cannot be mounted for a recovery. The controlfile must be recreated (with create controlfile whose syntax is a bit awkyard.
 

e.g.

create controlfile database name-of-database

In order to recover from a lost spfile, the DBID is required.

Size of control files
The size of the control files is governed by the following parameters:

    * maxlogfiles
    * maxlogmembers
    * maxloghistory
    * maxinstances
    * control_file_record_keep_time

Sections

 
The controlfile contains the following sections:

    * ARCHIVED LOG (reusable)
    * BACKUP CORRUPTION (reusable)
    * BACKUP DATAFILE (reusable)
    * BACKUP PIECE (reusable)
    * BACKUP REDOLOG (reusable)
    * BACKUP SET (reusable)
    * BACKUP SPFILE
    * CKPT PROGRESS
    * COPY CORRUPTION (reusable)
    * DATABASE
    * DATAFILE
    * DATAFILE COPY (reusable)
    * DATAFILE HISTORY
    * DATABASE INCARNATION
    * DELETED OBJECT (reusable)
    * FILENAME
    * FLASHBACK LOG
    * INSTANCE SPACE RESERVATION
    * LOG HISTORY (reusable)
    * MTTR
    * OFFLINE RANGE (reusable)
    * RECOVERY DESTINATION
    * REMOVABLE RECOVERY FILES
    * RMAN STATUS
    * RMAN CONFIGURATION
    * REDO THREAD
    * REDO LOG
    * TABLESPACE
    * TEMPORARY FILENAME
    * THREAD INSTANCE NAME MAPPING
    * PROXY COPY

The minimum number of days that a reusable record is kept in the controlfile is controlled by the control_file_record_keep_time parameter.
These sections consist of records. The size, total number and number of used record are exposed through v$controlfile_record_section.
Misc
SQL*Loader's behaviour is specified with the SQL*Loader control file. Although they sound similar, they have nothing in common.
ASM simplifies the optimal layout of controlfiles.

How to resize or re-create online redo log files in oracle

In oracle 10g by default there are three groups having one online redo log file of 50 MB in each group. It is recommended to re-size them before going to use oracle 10g in production environment. In this article I will explain how to resize redo log files from 50 MB to 250 MB. Please follow the steps give below.

Step 1 SQL> select a.group#, a.member, b.bytes/1024/1024 mb from v$logfile a, v$log b where a.group# = b.group#;

GROUP#     MEMBER                                                       MB
3                   /u01/app/oracle/oradata/ORCL/redo03.log        50
2                  /u01/app/oracle/oradata/ORCL/redo02.log         50
1                 /u01/app/oracle/oradata/ORCL/redo01.log          50

This query will show current group with redo log members and their size.

Step 2 Make the last redo log CURRENT one

To find which group is current at this moment use following query

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                    CURRENT
2                    INACTIVE
3                    INACTIVE
Now as you can see that the first group is marked as current but we need to make group 3 as current. So force group 3 to become current one by switching log file. To switch log file use following query.

SQL> alter system switch logfile;

GROUP#         STATUS
---------- ----------------
1                     INACTIVE
2                    CURRENT
3                    INACTIVE

SQL> alter system switch logfile;

GROUP#        STATUS
---------- ----------------
1                     INACTIVE
2                     INACTIVE
3                     CURRENT

Step 3 Drop the first online redo log

After making the last online redo log file the CURRENT one, drop the first online redo log:

SQL> alter database drop logfile group 1;

Database altered.

Note:

As a DBA, you should already be aware that if you are going to drop a logfile group, it cannot be the current logfile group.However, where attempting to drop the logfile group resulted in the following error as a result of the logfile group having an active status:

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

ALTER DATABASE DROP LOGFILE GROUP 1

*

ERROR at line 1:

ORA-01624: log 1 needed for crash recovery of instance ORA920 (thread 1)

ORA-00312: online log 1 thread 1: ''

Easy problem to resolve. Simply perform a checkpoint on the database:

SQL> ALTER SYSTEM CHECKPOINT GLOBAL;

System altered.

SQL> ALTER DATABASE DROP LOGFILE GROUP 1;

Database altered.

Step 4 You need to re-create dropped online redo log group with different size. Use the following query to achieve this.

SQL> alter database add logfile group 1 (
2 '/u01/app/oracle/oradata/ORCL/redo01.log',
3 '/u01/app/oracle/oradata/ORCL/redo01.log') size 250m reuse;

Database altered.

Step 5 Force another log switch

After re-creating the online redo log group, force a log switch. The online redo log group just created should become the "CURRENT" group:

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                     UNUSED
2                     INACTIVE
3                     CURRENT

SQL> alter system switch logfile;

SQL> select group#, status from v$log;

GROUP#         STATUS
---------- ----------------
1                      CURRENT
2                      INACTIVE
3                      ACTIVE

Step 6 # Loop back to Step 3 until all logs are rebuilt

After re-creating an online redo log group, continue to re-create (or resize) all online redo log groups until all of them are rebuilt.

I hope this article helps you in recreating and resizing the redo log files.

Varius Commands

route -r
This command shows default gateway of the network. The row with UG flag is the default gateway of network

cat /proc/cpuinfo
This command shows CPU information

cat /proc/meminfo

This command show memory (RAM) info

finger

This command shows the IP address from where people have accessed the server.

How to disable Direct Root access on Linux server.

In this article I will show you how to disable direct root access on your Linux server. I will also a good idea to secure your server form hacker as hacker first has to login to server using ordinary (non-root) user and then gain root access (su -) from there. So hacker has to undergo two security checks. Please find below steps to disable direct root access.

Step 1

You must create a user to access the server. Let suppose I want to create user 'zaeem'. Following commands are used.

[root@Localhost]# groupadd zaeem

[root@Localhost]# useradd zaeem –gzaeem

[root@Localhost]# passwd zaeem

Enter password that you want to set against user 'zaeem'. So now you are done with user creation. You must test connecting to server using this user before root access is disabled because many people disable root access and either they forget to define user or defined user is unable to connect. Use following command to test the connection.

ssh zaeem@192.168.0.121

if it is successful then use following process to disable direct root access.

Step 2

open the file /etc/ssh/sshd_config and find line '#Protocol 2,1' so modify it to look like as 'Protocol 2'.

After that locate 'PermitRootLogin yes' so un-comment it and change it to look like 'PermitRootLogin no'. Save the file and restart sshd (service sshd restart)

That's it. Cheers!

How to change SSH port.

Running a Linux server on default SSH port 22 is risky as there may be some brute force attack. So it is always good idea to change default SSH port. It is very simple to change ssh port as you just need to edit sshd_config and reload the new configuration and you're done with it. Please find below steps in achieving that.

Step 1:
Edit sshd_config normally placed at /etc/ssh/. Open it and there is variable "Port 22" so change it with new desired port as "Port 2222"

Step 2
You've with the configuration so you just need to reload sshd service so that changes can take effect. Use following command to achieve that.

root@localhost> service sshd reload

I hope you find this article helpful.

How to perform database recovery with a corrupt control file in Oracle

How can I perform a database recovery with a corrupt control file in Oracle?

You'll have to manually create the control file. First, take a backup of your database's files so that you can always start over should something go wrong. Then, STARTUP NOMOUNT the database and issue the CREATE CONTROLFILE command.

You will have to know things like where your datafiles are. Once you have successfully issued the CREATE CONTROLFILE command, then you can do the following:

RECOVER DATABASE USING BACKUP CONTROLFILE UNTIL CANCEL;

Specify CANCEL when you have applied all of your redo logs for recovery. Then specify:

ALTER DATABASE OPEN RESETLOGS;

SHUTDOWN IMMEDIATE

Next, take a backup of your database. If you have succeeded, you do not want to go through this again.

Shell script to monitor Hard disk space

It is very critical that you keep monitoring disk spaces so you can install additional hard disk or remove the un-necessary file from hard disk in order to free up some space.

Below given script will monitor hard disk space and when it detects that hard disk is used around 70 % then it generate an email alert at specified email address.


df -h | grep -v ‘Use’ | awk ‘{print $5″ : “$6}’ | replace ‘%’ ” > /tmp/diskfile
for i in `cut -f 1 -d : /tmp/diskfile`
do
if [ $i -ge "70" ];
then
echo partition `grep $i /tmp/diskfile| cut -f 2 -d :` Critical disk space on `hostname` >> /tmp/disk-overusage
fi
done
if [ -f /tmp/disk-overusage ];
then
mail -s “URGENT `hostname` running out of disk space” test@test.com < /tmp/disk-overusage
fi
rm -f /tmp/disk-overusage /tmp/diskfile

How to change Time Zone of Linux server

In this article I will try to briefly explain about how you can change Time Zone of your Linux box. Please following below mentioned points and you are done with it.

1. Logged in as root, check which timezone your machine is currently using by executing `date`. You'll see something like Mon 17 Jan 2005 12:15:08 PM PST, PST in this case is the current timezone.

2. Go to the directory /usr/share/zoneinfo here you will find a list of time zone regions. Choose the most appropriate region, if you live in Canada or the US this directory is the "America" directory.

3. If you wish, backup the previous timezone configuration by copying it to a different location. Such as

mv /etc/localtime /etc/localtime-old

4. Create a symbolic link from the appropiate timezone to /etc/localtime. Example:

ln -sf /usr/share/zoneinfo/Europe/Amsterdam /etc/localtime

5. If you have the utility rdate, update the current system time by executing

/usr/bin/rdate -s time.nist.gov

6. Set the ZONE entry in the file /etc/sysconfig/clock file (e.g."America/Los_Angeles")

7. Set the hardware clock by executing:

/sbin/hwclock --systohc

That's it. I hope you will find this article helpful.

How to disable or turn off Security Enhanced Linux (SElinux)

SELinux is a collection of extra security implementations/restrictions on top of the normal Linux security tools. It gives the systems administrator a deeper control than what the kernel typically provides.

Sometimes SElinux get in your way. Let me give you an example of that, I have webservices which used Apache and I'd installed it on my linux server. So when I start it, it starts correctly without any issue but it still inaccessible from outside world because I forgot to give apache user rights to open the ports. You can disable SElinux but you must be sure of what you are doing and what threats are then on the way if you disable it.

You can disable it in /etc/selinux/config file and modify it as follows

SELINUX=disabled

I hope this article helps you to understand about how to disable SElinux feature.

How to install and configure VPN with PPTP (Poptop)

VPN (Vitual Private Network) is extensively used to access workplace with security. So Let me briefly go through installation and configuration settings.

Potop is a package by using which you can creates VPN. You can download package from web or you can install it by using yum commands on shell.

yum install pptp

Once the installation is completed, you can find Poptop's configuration files located in /etc directory. There are four configurations which we need to take care in order to work with pptp.

* /etc/ppp/chap-secrets
* /etc/pptp.conf
* /etc/modules.conf
* /etc/ppp/options.pptp


So Lets go through the each file and I will briefly explain about what each file contains.

/etc/pptp.conf

It contains the default configurations of the pptp, like the executable name etc. The default settings of this file work and no change is required except any exceptional scenario.

/etc/modules.conf

It contains the list of installed Poptop modules. You shold not modify this file.

/etc/ppp/chap-secrets


This is the main file that you need to modify. Basically you need to define username and password in this file which will be used to connect to VPN. I have created a test user 'sheraxi'.
——————-

# Secrets for authentication using CHAP
# client server secret IP addresses
sherazi * sherazi *

—————————–

Once changes are completed, run the pptp service by following command:

service pptp restart

Now you can connect to VPN using the user defined in chap-secret file on the main IP address of the server.