09 Apr

mysql database backup shell script with status email

This post is for the backup script for MySQL database on Linux with mail. It’s a linux shell script for taking logical backup using mysqldump and sending status email.

The backup shell script works as follows:
– The script takes backup using mysqldump and compresses it.
– Upon success, it will attempt to ship the backup to specified offsite location.
– Upon detecting failure in any of the above step, it will send out failure email.
– Upon overall success, it will sendout success email with execution time and present backups list.

You might be looking for setting up physical backups for mysql using Holland backup framework.

Following is the code for MySQL backup shell script using mysqldump with status email.

29 Jun

Restore A Table / Database From Full Backup – Yet Another Way

Restore requests are common and so are the restores of specific entities: a database, or one or more table(s). This has been discussed a lot and we have plenty of tools and solutions already available.
In this blog post we will cover an interesting solution that I came across when I received a restoration request from a client with a specific scenario.

The scenario? Well, the client was on a Windows server with 400GB of mysqldump and wanted to restore a table.

As Linux players we already know of some tools and techniques to export a table or database from mysqldump – for example, using sed command or using the script mysqldumpsplitter (based on sed itself). But on Windows we are powerless by not being able to use sed (we’re sad without sed.) Also, there was no cygwin to ease up the pain.

We had to come-up with a solution that works on Windows as well. During this discussion, my Pythian colleague, Romuller, suggested a cool but simple trick which enlightens us and offers one more way of exporting or recovering a table from a full mysqldump.

So the trick here is as follows:

29 Jan

Export MySQL database table to CSV (delimited / Excel) file

Today lets talk a little about converting a MySQL table to CSV (Excel). My friend was looking to export MySQL to Excel, I saw couple of questions for export MySQL tables to CSV on forums. Since I saw the question often, I thought of writing out all the ways I can think of for exporting Delimited (CSV / TSV / …) data from MySQL table. Pretty chewed & basic but frequent topic.

Following are the ways to export CSV data from MySQL database / table(s).

1. Using SELECT INTO … OUTFILE statement to export from MySQL to CSV

SELECT ... INTO OUTFILE writes the selected rows to a file. Column and line terminators can be specified to produce a specific output format. Just to mention, SELECT ... INTO OUTFILE is the complement of LOAD DATA INFILE, which you may use to load CSV (generally speaking delimited) files to MySQL.

Here’s a sample command to export “tablename” table of “db” database as a CSV:

SELECT * INTO OUTFILE '/csv_files/db.tablename.txt'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
LINES TERMINATED BY '\n'
FROM db.tablename;

09 Jan

Linux shell script: Pause (Suspend) a process with kill signals

Linux KILL command gives you killing opportunities to use SIGNALs. In this post we’re going to Pause (or suspend) and Start an ongoing Linux process.

Problem: The requirement here is to pause a process for certain time and run it only during off hours. The process itself doesn’t have this option and also we cannot stop it in-between, as it will start from the scratch again.

Solution: To resolve this we can think of sending out signals to the process to hint it to Pause or Progress.

A signal is an asynchronous notification sent to a process or to a specific thread within the same process in order to notify it of an event that occurred. When a signal is sent, the operating system interrupts the target process’ normal flow of execution to deliver the signal.

14 Dec

Decoding Siebel Audit Trail with PLSQL

In this post we will understand how to decode Seibel Audit Trail using PLSQL.

Siebel Audit Trail is feature in Siebel CRM which allows customer to track changes to important UI fields. Though it is very useful feature, the changes are stored in encoded text in DB. We are aiming to decode this complex data and allow user to extract complete Audit Trail information in files or tables. This can be further used to enhance customer experience.

This extract process is written completely in PL-SQL procedure. Similarly a script based solution would follow. This PL SQL procedure is tested with version 8.0 & 8.1. It should also be applicable for newer 8.1.1.16 versions as well.
So let’s get started with this. We will go step by step and understand the purpose of this code while going along with a scenario.

Category Archives: Technical