Tuesday, 12 November 2013

MySQL Auditing

I got a task of auditing user activities for some sensitive database in one of our MySQL database servers. Auditing user activity was a tough task with earlier version of MySQL. We had to go through the slow query log or general log and find out our required data from these two files by scanning through lots of data. Which is obviously not a trivial task. But MySQL started supporting plugin API since MySQL 5.1 version and that changed the game. That leads to the arrival of MySQL AUDIT Plugin, which is a MySQL plugin from McAfee and this plugin provides audit capabilities for MySQL.

So in this example I will show you how to audit user activities like update,delete,drop,truncate for a particular database say okcl_sets_app. For this example I have used Ubuntu 12.04 with MySQL 5.5.24.

First I will show you how to install the plugin and then auditing some user activity without restarting the MySQL server.

Download the MySQL Audit plugin for your version of MySQL from the links provided in the page


We have to copy the plugin file into MySQL’s plugin directory. To find the location of the plugin directory we can use the following command:


The plugin file is available in the zip binary distribution. Extract the zip file

# unzip

The actual plugin file is inside of the lib folder of the extracted zip folder. Copy the plugin file ( into MySQL’s plugin directory.

# cp ./audit-plugin-mysql-5.5/lib/ /usr/lib/mysql/plugin

Once the plugin file is copied, we can install the plugin using the following command:


Note: The above command requires INSERT privilege for the mysql.plugin table.

The INSTALL PLUGIN command loads and initializes the plugin and makes the plugin available for use. So there is no need to restart the MySQL server.

We may also install this plugin by inserting the following line

in the [mysqld] section of MySQL configuration file. But this will require MySQL server restart to load the plugin.

To check whether the plugin has been installed and loaded successfully, we can use the SHOW PLUGINS command and check the line for AUDIT plugin.


We can find the version of our loaded audit plugin using the following command:


Our audit plugin is installed and loaded successfully, now we can see the default values for the configuration system variables of the audit plugin:


Audit plugin writes the auditing activities in JSON format. It supports writing auditing activities directly to a file, or to a unix socket.

Now I will enable JSON file auditing using the dynamic system variable.
audit_json_file: json log file Enable|Disable (1|0)


By default the plugin creates mysql-audit.json file inside MySQL datadir and writes audit trail to this file. We can change the file name and location by changing the audit_json_log_file system variable.

After enabling auditing, the plugin starts auditing all the user activities on the server, which will be large amount of data. We may restrict auditing data by specifying the commands that are to be audited and also we can specify the database/table that we need to audit.

As per my requirement I have to audit update,delete,drop,truncate for the database  okcl_sets_app

For that I will first specify the commands that are to be audited by changing the audit_record_cmds system variable.


Next I will specify the object(s) that I need to audit by changing the audit_record_objs system variables.


All done, we can check the audit settings whether everything is changed as per our requirement


Note: To make our audit configurations to persist across MySQL restart, add the required audit plugin system variables into the [mysqld] section of MySQL configuration file.

All the Audit Plugin’s system variables are available in this page:

Now I will update the table named TableName1 in okcl_sets_app database and lets see what we get in the Audit log file


Wow, wealth of information that we can collect transparently Smile .

Monday, 11 November 2013

Diary of a dengue patient

Last few days I had gone through some horrific experience of having dengue fever (which is also called breakbone fever). It was during Diwali season and I was alone in my flat in Kharghar, Mumbai as my wife was away in my hometown. I had just shifted to this flat in Kharghar on Saturday, 26th October 2013 from my previous accommodation in Koparkhairane. Things were very hectic and very stressful because of the shifting and rearranging all the stuffs in the new flat. I was busy with fixing all the things in the new flat as well as work in my office. Suddenly dengue virus came into my blood and every plan and appointments were scattered.


Day 1 29th October 2013, Tuesday:

On 29th October, Tuesday after having my supper, I felt like feverish and felt a bit of pain in my body. So that night I decided to take early sleep. I just thought may be because of heavy workload and stress in past 4-5 days, I may be not feeling well. I could not sleep well that night (without knowing that the deadly dengue virus had entered in my body and my immunity system was fighting hard to kick it out of my body) because of the body pain and headache.


Day 2  30th October 2013, Wednesday:

After I woke up in the morning, I had full-fledged fever and pains in my back, thigh and hip. Also the headache was also at its best Smile . I mailed to my boss, I am not coming to office as I am not feeling well. The body temperature was 102F, I thought may be I am having some kind of flu as at that time because of changing weather many people were having flu. I thought I should have a paracetamol tablet as the body temperature was not coming down from 102F. I took the paracetamol tablet but it did not give any relieve, my temperature did not come down. Rather the temperature went up to 103F. At the evening I called up my doctor and he asked me to visit his clinic immediately. I walked to his clinic (it was around half a kilometer from my place) and he inspected everything and found that my temperature went up to 104F.
He asked me “how you came to the clinic? anybody accompanied you?”
I said “I walked and I am alone”
He said “ you should be very careful as you are having very high temperature”

He gave me some medicines and asked me to start taking those medicines immediately and if my condition does not improve in next 24 hours then he asked me to do some blood tests. Also he advised me to take lots of fluid like coconut water, Enerzal Energy Drink, juices and glucose water for not getting dehydrated.

I came home and started taking the medicines. I took the paracetamol the doctor gave me, it was around 8PM and I was waiting for the body temperature to come down, but it was not. Around 9.30PM I checked my body temperature and I was touching 105F and in my whole life I never saw my body temperature reaching 105F. I was getting anxious, what should I do? Then I thought, lets wait for 1/2 hour, if temperature does not come down, I will call up my doctor. Fortunately after some time I was sweating and the temperature came down to 101F and the muscle pains and headache also reduced a bit. After that I could take a good sleep.

The whole day, I was not feeling like to eat anything. In the morning I took some wheat flakes, milk and banana, I did not feel like having anything during lunch. Evening I took coconut water, few biscuits and one banana before I took medicines. 

I was badly feeling the need of some company, alas my wife was not with me.


Day 3 31st October, 2013, Thursday:

Woke up with pains in my body and headache, checked body temperature and it was around 101F. Had some muesli, milk and one banana. Whole day I was lying on the bed and taking rest. The body pain, headache and temperature was not going away. Around 4PM I called my doctor and told him my condition, he said don’t waste time and do the blood tests immediately. I went to lab and gave blood samples for malaria, jaundice, typhoid, complete blood profile and also for dengue. Around 7PM I got all the reports, except the dengue report. The lab did not have dengue testing machine, so they said it will take 24 hours to get the dengue test report. All the other tests were –ve (that means I am not having malaria, jaundice, typhoid), but my platelet count was on the lower side and it was 1,50,000 (range 1,50,000 – 4,50,000). So seeing my symptoms/condition doctor said may be you are having dengue. So he asked me to repeat the platelet count test next day also. Also he gave stress on having more fluids intake.

My appetite was gone, I was not feeling like to eat something and I was not feeling hungry. I had a glass of watermelon juice, coconut water, some papaya, banana etc. Also I was taking the Enerzal Energy Drink, and glucose water drink.

In the night the body temperature went upto 102F, also I had body pain, headache so I took one paracetamol table. Also I had little pain in my left ear, it was not continuous pain, but it was like pinching me in regular intervals. After taking the paracetamol tablet, I could sleep, but the sleep was not good, many times I woke up in the night and the my back was paining.

As I was alone, so I had to walk to the testing lab to give samples, collecting reports, going to doctor’s clinic etc. I was still feeling that I am not having dengue its normal flu or viral fever I am having.

It was festive mood everywhere and I was fighting with the virus Smile.


Day 4 1st November, 2013, Friday:

It was same story in the morning, body pain was there but the temperature was bit down, it was in the range of 100F-101F. In the breakfast had banana, milk and some biscuits. Whole day spent on bed and around 4PM went out to check my platelet count. Evening got the dengue report and it was +ve


< 9 Panbio Units -Negative
9 – 11 Panbio Units - Equivocal
>11 Panbio Units - Positive

My platelet count was again 1,50,000 same as previous day.

In the evening I went to my doctor, he advised me to take proper rest and have lots of fluid. Also he asked me to monitor my platelet count daily. He said if platelet count comes down then I may have to get admitted in to the hospital. He asked me not to take tension and stay relaxed.

In the night again I had pains in my ear (it was like pinching in regular interval of say 30 secs) I was sitting in the bed and hoping the pinching going away. After some time the pinching moved from my left ear to left head. I felt like restless, could not sleep, I was around 2AM in the night, I started walking inside my flat, trying to get the pains out of my mind. Somehow around 3AM I could get some sleep.

I was feeling helpless but my morals were not down and decided to fight it out myself.   


Day 5 2nd November, 2013, Saturday:

It was 1st day of Diwali festival, and we celebrate it as Goddess Kali Puja. There was a Puja ceremony in the temple of my native home. I was lying on the bed and missing the festival badly and everywhere there were lights and sound of crackers. My body temperature was below 100F and also the body pain reduced quite a bit. As food I had milk, little papaya, one apple, banana etc. also the fluids (coconut water, enerzal, glucose, watermelon juice etc.)   

I tested my platelet count and it was 1,30,000 that day. It was 20,000 less than previous day, I was bit tensed, went to my doctor and he said not much to worry and my condition is much better. He also kept options open for hospitalization, and said as you are alone and nobody is there to look after you, so you may get admitted to the hospital. But problem with the hospitals nearby my place is that, they do no provide patient's food, as I was alone supplying food for me in hospital would have been a problem. Also he strictly asked me not to take hotel spicy food for some days. He said there are two Hospitals in Vashi where I can get food also, but these two hospitals were very expensive. Also Vashi is around 15-20KMs from my place and also I have to go through the mediclaim tensions. So I had decided not to get admitted to the hospital till the time I have strength and can manage myself. 

Some of my relatives and friends suggested that I should take papaya leaf juice, this will increase platelet count. Also few suggested that I should take Giloy (Tinospora cordifolia) juice for increasing platelet count. But for me it was like impossible to find papaya tree in Mumbai, fortunately one of my friend and colleague Mr. Sammeer Rane called me to know about my health, he asked me if I need anything. I told him I need papaya leaf and giloy juice. He was kind and helpful enough to find these two items for me and delivered at my home. I made some juice from papaya leaf and eat 2 teaspoons (it was very very bitter in taste) and kept the remaining juice in the freezer.  Also I tool 20ml of Giloy juice.

Back home my family was very tensed (as they keep listening news of dengue deaths regularlyin TV). Also as I was alone at that time and that was also a big worry for them. I kept telling them I am fine and my condition was very good.

In the night my temperature went up to 101F again, so I took one paracetamol and after that I could sleep.


Day 6 3rd November, 2013, Sunday:

It was main Diwali day, so the lab where I do the platelet count check was closed, there was another lab nearby but they were also about to close. Somehow I could convinced the girl in that lab to get my blood sample and give the report in 15 minutes. Platelet count was 1,27,000, which was only 3,000 less than the previous day (may be previously I was doing the test during afternoon hours and that day I did it in the morning hours). I consulted my doctor and he said my condition is very stable and as I do not have high temperature and my body pain and headache had reduced a lot so nothing to worry much. Enjoyed the Diwali festival lying on the bed and watching Chennai Express in my laptop Winking smile (which tried to increase my headache like the dengue virus).

I started cooking at night and taking boiled rice/daal and mashed potato/egg in supper.

Also I was taking 2 teaspoons of papaya leaf juice twice daily and 15ml of Giloy juice twice daily.


Day 7 4th November, 2013, Monday:

Body pain, headache reduced quite a bit and body temperature was under 100F. I was feeling fine but weakness was increasing. I was taking rest and also taking more fluid diet. Checked platelet count and it came down to 1,04,000 (23,000 less than previous day) and panicking I consulted my doctor and he said, you are not having temperature and pains, you are much stable, platelet count will come down, that is normal, you need not to worry. I was worried and panicked but never let it to overcome myself. I kept myself internally strong and never showed anybody that I was worried that day. I thought lets see one more day, if platelet count comes below 1,00,000 next day, then I will get myself admitted into some hospital. Also as I read that the dengue virus stays for almost 7 days, so in my case it was 7 days, so I felt like it may improve from next day. Internally I was feeling much better but platelet count was the only worrying factor.


Day 8 5th November, 2013, Tuesday:

I was feeling fine but weakness was not going, neither my appetite improved. I was laying in the bed, watching some movie and passing my time with my mobile. Towards the afternoon I had itching in my hands and legs, after that I saw those areas became reddish and was like rashes. I was afraid and thought may be my platelet count had gone down drastically, immediately went to check my platelet count, the lab where I do the tests was closed, so I went to some other one. I was bit tensed and thinking about hospitalization if platelet count goes down. But after getting the report I was delighted to see my platelet count had increased by 6,000 and now it was 1,10,000. I was very relieved and thanked god. Immediately I called up my wife and informed her and she was also very happy and relaxed.


Day 9 6th November, 2013, Wednesday:

Weakness & loss of appetite was the problem, also some itching in my legs and hands. Otherwise I was fine and there was no sign of headache, body pain and temperature. Checked platelet count and it went up to 1,35,000 (increased by 25,000). I consulted my doctor and he said, your recovery is on the right track. Now you do not need to check your platelet daily, check it after 2-3 days. Also he asked me to be in rest and take proper food.


Day 10-11 7th-8th November, 2013, Thursday-Friday:

Rest, rest and rest………


Day 12 9th November, 2013, Saturday:

Checked platelet count and it was 2,01,000 Smile well above the minimum level. Stopped taking papaya leaf juice as my platelet count came to normal level. Weakness was there but I felt that dengue recovery is well on course.

I thank god for taking me out of this situation and recovering me. As I was alone during that time, I felt like god was with me who gave me strength and power to overcome the difficulties.