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