Database and LDAP backup scripts

It is a common task to make backups of database or LDAP on the server. Here are examples of such scripts for Linux.

For MySQL database backup I use this script

#!/bin/sh
export PATH=$PATH:/bin:/sbin:/usr/bin:/usr/sbin
umask 077

savedays="180"
backupdir="/mnt/backups/db"

now=`date "+%Y%m%d_%H%M"`

if [ ! -d ${backupdir} ] ; then
    echo Creating ${backupdir}
    mkdir -p  ${backupdir}; chmod 700 ${backupdir}; 
fi

mysqldump -hlocalhost -uuser -ppassword --routines database | gzip > ${backupdir}/database_${now}.sql.gz  2>>/var/log/dbbackups.log
find ${backupdir} -name 'database_*' -a -mtime +${savedays} -print -delete

For LDAP backup I use this script:

#!/bin/sh
export PATH=$PATH:/bin:/sbin:/usr/bin:/usr/sbin
umask 077

savedays="180"
backupdir="/mnt/backups/ldap"

now=`date "+%Y%m%d_%H%M"`

if [ ! -d ${backupdir} ] ; then
    echo Creating ${backupdir}
    mkdir -p  ${backupdir}; chmod 700 ${backupdir}; 
fi
        
slapcat > ${backupdir}/ldap.${now}.ldif  2>>/var/log/ldapbackups.log         
find ${backupdir} -name 'ldap_*' -a -mtime +${savedays} -print -delete

These script will save your database and LDAP backups to /mnt/backups/db and /mnt/backups/ldap accordingly. Backup files will be kept for 180 days and then they will be deleted.

To automate backup you can simply place the scripts to the appropriate cron directory (e.g. /etc/cron.daily) and make sure that cron has rights to execute this file and has rights to write to /mnt/backups/db and /mnt/backups/ldap folders.

Read more...

Search and replace text in multiple files

It's very common task to find some text in some files and replace it. Here is my recipe to deal with it

In this example I will find all 'varchar' text in my '.sql' files and replace it with 'char'.
At first let's search for the files that contain the text:

grep -i 'varchar' *.sql

In the output you can see the list of files with corresponding match within it.
Now let's take this list of files and replace the searched text within with the new one:

grep -il 'varchar' *.txt | xargs sed -i 's/varchar/char/gi'

This was a case-insensitive case. But if you need to be case-sensitive then use:

grep -l 'varchar' *.txt | xargs sed -i 's/varchar/char/g'

Read more...

LDAP recovery

If you have suffered from LDAP wreckage then probably you already know how difficult it is to make it work again. In my case all problems started with such message in system log:

slapd[11705]: bdb(dc=xxx,dc=com): PANIC: fatal region error detected; run recovery

Note: my bdb version is 4.7, yours may differ;
slapd stands for Stand-alone LDAP Daemon, your service name may be ldap

At first I tried to recover LDAP using

db4.7_verify -h /var/lib/ldap
db4.7_recover -v -h /var/lib/ldap 

But LDAP got broken again and again.
Thus I decided to recreate whole LDAP folder. Here is the scenario:

1. Stop the LDAP server
   

   service slapd stop

2. Dump the directory structure to a text file
   

   slapcat -l /etc/ldap/backup/ldap_old.ldif

3. Recover LDAP
   

   db4.7_recover -v -h /var/lib/ldap

4. Dump the directory structure to a text file
   

   slapcat -l /etc/ldap/backup/ldap.ldif

5. Verify that ldap.ldif contains your entries. If it does not, or if slapcat returned errors in step 3, try running db4.7_recover in catastrophic mode:
   

   db4.7_recover -v -h /var/lib/ldap -c

   and repeat step 3
6. Delete the LDAP directory
   

   rm -fr /var/lib/ldap/*

7. Load LDAP directory from the file you have create in step 3
   

   slapadd -l /etc/ldap/backup/slap.ldif

8. Make openldap the owner of the LDAP directory
   

   chown -R openldap:openldap /var/lib/ldap/*

9. Start the LDAP server
   

   service slapd start

Read more...

MySQL - SELECT ... INTO OUTFILE ... - ERROR 1 (HY000)

There is a very useful functionality in MySQL for exporting data into a file. The syntax is like:

SELECT <FIELDS> INTO OUTFILE <FILE_NAME> FROM <TABLE_NAME>;

If you use Linux then when you execute this export command with FILE_NAME that is not absolute than the file will be saved in the database folder (e.g. FILE_NAME = '1.csv'); if you specify to use /tmp folder then it will be saved there(e.g. FILE_NAME='/tmp/1.csv').
But if you specify some other directory (e.g. FILE_NAME='/home/anton/mysql/1.csv') then you will get error:

ERROR 1 (HY000): Can't create/write to file '/home/anton/mysql/1.csv' (Errcode: 13)

Even if you will set MySQL user as an owner of the directory and will provide all access to it, this error will appear nevertheless.

The reason lays in the Linux kernel security module AppArmor. This module restricts programs possibilities and we have stumbled upon one of those. Thus the solution is pretty obvious - we need to configure AppArmor. For this we will add at the end (but before the closing brace) of the file /etc/apparmor.d/usr.sbin.mysqld (for Ubuntu) such lines:

/home/anton/mysql/ w,
/home/anton/mysql/* rwk,

Then we need to reload this configuration file with command (for Ubuntu):

sudo /etc/init.d/apparmor reload

Now check that the directory that you want to use (/home/anton/mysql) has 755 rights and mysql us is the owner of it. Also make sure that there is enough free space on the volume.
Repeat export SELECT command

Read more...