How To: Perform an unattended MySQL DB dump restore

Say you have the following setup -

1. A MySQL DB on which you have to restore a previously taken DB DUMP;
2. A *INX based O.S. / Server (could be RHEL etc.)
3. An SSH connection / session to the same

In a normal scenario, you either access your DB via a DB client (like SQLYog) or via a remote SSH (using putty).

When you need to restore a DB DUMP on an existing DB instance, you use the mysql command as -

[ssh@xyz]# mysql mydb < /dbdumps/mydb_dump.sql

This will start the restore process. For a small DB this should be done quickly. But considering a realistic live scenario with your DB in GBs of data, you will need at least upwards of 30 minutes.

Now what if you need to disconnect your SSH for any reason? Maybe step out for a meeting, or pack-up your rig for your drive home?

The reason may be anything but the considering the fact that the restore process is launched specifically under the SSH shell process, if the SSH disconnects during the restore, it would kill the restore too.

So the only logical choice is to leave the terminal open until the dump is completely restored, with the normal termination of the process.

Here-in comes the nohup.

Quoting from Wikipedia,

Nohup is a Unix command, used to start another program, in such a way that it does not terminate when the parent process is terminated. This is accomplished by ignoring the signal 'SIGHUP'. This is what distinguishes nohup from simply running the command in the background with '&'.

So in essence, the nohup allows the restore process to continue running even if the remote SSH session is terminated when the SSH client disconnects.

Also, since we may want to use the SSH session after the process to restore is started (via nohup), we use the nohup in conjunction with the ‘&’ command.

The ampersand as a suffix ensures the command is started as a background daemon (under the system process). It will leave the command line and proceed to run in the background.

The entire goal above is thus achieved via the following command -

[ssh@xyz]# nohup mysql mydb < /dbdumps/mydb_dump.sql &

Running this gives you the below as a feedback result on prompt -

[1] 5624
[ssh@xyz]# nohup: appending output to `nohup.out'

Let’s decrypt the above.

Meet the Process-id - 5624

Any process running on a Unix system is always having a PID or a process-id. This is a unique identifiable unit with which a process is associated and can be manipulated.

For example, the kill command (which is used to terminate a process) uses the following format -

$ kill <process_id>

Thus the process id for the background is printed for convenience.

The nohup.out file

Also, as it is a background process, the standard I/O is not used for any prompts or error communication, Instead the same is redirected to the nohup.out file, created at the location where the process is launched (this can be located by the $ pwd – which will give the present working directory)

Status of nohup

All this is good, now that the restore is running merrily in the background.

But how to know if and when this completes? How to know the status?

The answer is using $ jobs.

[ssh@xyz]# jobs
[1]+  Running nohup mysql mydb < /dbdumps/mydb_dump.sql &

So the current process is shows to be still running. If it would have completed, the status is changed from Running to Done.

That’s it! Hope it helps someone out there!

Let me know your comments and suggestion too.

Note: I will be covering the DB DUMP command later in detail, with its usage in conjunction with the CRON scheduler script.

Technorati Tags: Unix,MySQL,Jobs,nohup,dump,restore,DB,RHEL

To help save a life…

(The below post is replicated in entirety from my Facebook page dated February 28. It is an actual incident.)

Something happened today evening on my way back from the office. An accident.

Not mine. A random guy on his bike. Going back home like everyone else.

This happened around 8:00 PM on the Noida extension route, just 200 meters before the Gaur roundabout.

The guy (whom I don’t want to disclose) going back home in Crossings Republic from his IT firm, was driving fine but unluckily could not see and slow down in time for the dirt rubble on the road (probably left behind by one of the many callous trucks and tractors which ply on that route).

End result a bad skid, hit with the pavement and countless injuries. By the time I stopped and reached him, he was lying all covered in blood, with tattered clothes and desperate cries of pain.

Lots of people stopped to help, some to watch but many slowed down, looked but chose to drive off.

Back to their destination, their homes and back to their comfortable lives.

Do you know how real blood looks on the asphalt? It's so red; bloody frightening red. And it was everywhere.

I was there with the others. We helped lift him up and got him in a car for the nearest hospital (bless that driver for having the sense of responsibility and courage).

The bike was a wreck. Me and another random guy (who was on his bike) stayed behind and waited for the cops to turn up. Someone had taken the onus to inform them down the road so that they could come and take care of the vehicle.

So we waited to hand over the same to the cops, who as expected, didn't turn up. In the end, we had to park his bike on the roadside and decided to hand the keys over to the guy himself (whom we expected to have reached the hospital by that time).

This way, we hoped to check on the guy's well-being too. Reaching the nursing home, we found him all clothes torn, swollen up and unable to speak (due to the bandages around his face) and nursing some broken bones. but he was able to identify and acknowledge us.

It seems he would survive an recover, thankfully.

I just reached home a few minutes back. And all along the way back I had but only one thought.

How sudden and unpredictable life is.. and how much dependent we all are for our survival on the virtues of our society, on the mood and courage of our fellow beings!

An incident like this puts many things in perspective. Seeing what is known to all, and happens time and again, I wondered why people don’t have the courage and the heart to stop and help someone in need?

And what was most disheartening was that many of those who moved on, they were those my age, the youth!

We, the ones, who on one hand are responsible for changing conventional mind-sets of our society, who bring in waves of change in politics and what not; and on the other, we still follow the pretend play of closing our eyes and moving on! Such double standards, I ask?

I have never stepped back from my social responsibility and will continue to do so till I can, but will someone stop by for me in my time of need?

With so many instances all around us daily, I cannot help but think that if even 20 people stop to help someone in need daily, 20 lives can be saved in a day!

Think about them, those who can be helped, their families, their future, their lives which would follow thereafter!

I have nothing more to say but this, only if I have the courage today to be selfless, to stop and save someone in need, will someone tomorrow do the same for me.

Think about it.
(sorry for the long rant, but I had to do it)
-----------------------------------------------------------------------------------------------------------------