Backing Up MySQL Databases on Ubuntu Server

Having your own server comes with a lot of maintenance considerations. It can be your worst nightmare to experience a disastrous server crash and damaging (or loosing!!!) your database. Prevention is better than cure. An easy prevention of such an unfortunate problem is to backup your databases regularly, on a rotational basis.

I have some ubuntu servers running on Amazon AWS. I am well aware of the high quality of the services provided by amazon. But I am just pretty obsessed with fault prevention and recovery. Backing up databases daily on rotational basis seems to be a rather good idea.

There are many ways to do that. You can employ a shell script or cronjob or some third party tool/utility. I preferred to use cronjob and mysqldump utility which ships with MySQL itself.

Cron is a software utility in Unix like (that means ALL Linuxes) operating Systems. Its a job scheduler, speaking crudely. Much like windows Task Scheduler. Now there is a lot to learn about cron. For the purpose of this article I will provide the steps I took to backup my databases.

First of all in the terminal lets open up “/etc/crontab” file with an editor of your choice (my choice is nano) with root privileges (this essentially means using “sudo” and all).

Now at the bottom of the file add following line:

0 13 * * * root mysqldump -u root -p database_name | gzip > /home/backup/database_`date +\%m-\%d-\%Y`.sql.gz

Here 0 13 * * * specifies the time when you want to take the backup. There are 5 terms , MINUTE HOUR DAY_OF_MONTH MONTH DAY_OF_WEEK. So our backup is created at 1 PM everyday, because we have not provided the values for other parameters.

root mysqldump -u root -p database_name is the command executed with root privileges. It invokes mysqldump utility and creates a SQL dump of the database whose name is provided.

Then using |gzip we pipe this sql file to gzip utility which compresses this file to gzip format.

Finally we are providing a location’s path to save this file by /home/backup/database_`date +\%m-\%d-\%Y`.sql.gz

After adding this line its required to restart the cron daemon. Following command will do this.

sudo /etc/init.d/cron restart

Although this process is applied and tested on my ubuntu server. Any linux distro will be having almost the same setup.

Advertisements

Connecting to Remote MySQL Hosts with HeidiSQL

Spending quite of hours after struggling with this. I finally found the correct method to connect to remote mysql hosts using an excellent GUI for mysql. I use HeidiSQL all the time for my local development and also the database maintenance on remote servers.

Lately  have been working with Amazon AWS. Their EC2 instances are totally awesome. Apart from little configuration problems I am lovin it.

So here are the quick steps to be followed.

  1. Use puttygen to convert your .pem file which you get from amazon server. It will provide you with a .ppk file. This ppk file is used by HeidiSQL for connecting to remote servers using SSH Tunnel.
  2. Now from Session Manager in HeidiSQL select MySQL (SSH Tunnel) for the network type.
  3. Always use Hostname/IP localhost or 127.0.0.1 irrespective of the name/IP of the remote host. We will come to this in the next step.
  4. Fill in your MySQL username and passwords.

Now in the SSH Tunnel Tab

  1. Browse for the location of the plink.
  2. Hostname/IP of your remote host this time.
  3. Port 22 will work in almost all cases, of course, if you have not tweaked (or messed?) the configuration in your remote host.
  4. Here in the username field fill in your SSH username. Mostly in case of EC2 ubuntu instances its “ubuntu” itself by default.
  5. You can safely leave the password field blank as in next step we will be using the ppk file (remember we generated it a while ago!) for authentication.
  6. TaDa! Done.

There might be some errors regarding connectivity and/or your configuration of the MySQL Server.

Most of the connectivity issues can be easily resolved by extending the plink.exe timeout. You can increse it easily from HeidiSQL Sesison Manager Settings right there on the SSH Tunnel Tab. Default value is 4 seconds. I found 10 seconds to be a fair deal as my connection gets crazy sometimes.

Another problem which may occur is the nasty error

reading initial communication packet, system error: 0

This error occurs mostly when MySQL drops the communication packet silently. There is a simple workaround for this.

You would need to login to your server via SSH and edit following file:

/etc/mysql/my.cnf”

Add following line:

bind_address = 0.0.0.0

This ensures that mysql responds to all the IPs.

 

Ciao!!