How to setup automatic MySQL backup on Ubuntu/Debian and CentOS


   This tutorial is to guide you on setting up a simple, automatic MySQL database backups using mysqldump and a cron job.



  •        Installed and running MySQL server

  •        SSH access

  •        root or sudo user

   Step 1: Backing up  a MySQL database

   You can use the ‘mysqldump’ that comes in any standard MySQL installation to do a database dump from the shell. To backup the ‘exampledb’ database to a file do:


       mysqldump -uroot -p exampledb > exampledb.sql

   Note that there is no space between the ‘-u’ switch and the user (root). With the ‘-p’ switch and paramater after it the ‘mysqldump’ will prompt you to enter a password.

   This command will output plain SQL queries in the exampledb.sql, but as they are text they can be compressed really good to save space. So to make a compressed backup, let’s do:


       mysqldump -uroot -p exampledb | gzip > exampledb.sql.gz

   You can use these commands to make manual backups of your databases. If you want to backup all databases on a server, use the ‘–all-databases’ switch instead of database name, like this:


       mysqldump -uroot -p –all-databases| gzip > mysql_server.sql.gz

   Step 2: Setting an automated scheduled backup of a MySQL database

   Now that you know how to make manual database backups, let’s automate them with a cron job. You must set up a cron job that calls the ‘mysqldump’ tool on a schedule.

   First let’s make a directory  for storing the backups:


       mkdir /var/backups/mysql

   As backups are more usefull when they provide history, let’s give the backup file with more meaningful name by adding the date and time when the backup was taken:


       mysqldump -uroot -p exampledb | gzip > /var/backups/mysql/exampledb.$(date +”%Y_%m_%d_%I_%M”).sql.gz

   To automate the backup process ‘mysqldump’ must have the user password inline, so it’s highly recommended to set up an additional database user specifically for backups.

   Open a crontab editor:


       crontab -e

   and add the following line at the bottom:


       0 1 * * *    mysqldump -uroot -p exampledb | gzip > /var/backups/mysql/exampledb.$(date +”\%Y_\%m_\%d_\%I_\%M”).sql.gz

   The “0 1 * * *” stands for a schedule on every 01:00 or everyday at one after midnight. The cron line format is “m h  dom mon dow   command”, where: 


  •        m – is for minutes

  •        h – is for hour

  •        dom – is the day of the month

  •        mon – is the month 

  •        dow – day of the week

   The  ‘*’ symbol in any of these means “on every” minute, hour, day and so.


   Saving the crontab will set your backup on schedule and you are good to go.

How to open SSH port on Ubuntu / Debian with ‘ufw’

   Ufw or ‘uncomplicated firewall’ is a really nice tool that allows you to easily manage ‘iptables’ firewall in Linux with a bit more user friendly frontend instead of ‘iptable’ chains and rules.

   It’s included in the most recent version of Debian and Ubuntu and we’ll start with a simple example – how to open or restrict SSH access to your cloud server.

   To allow the SSH port:


       ufw allow ssh



       ufw allow 22/tcp

   This will open the SSH port ( TCP 22 ) to the entire world.

   Your cloud server is a public machine and you might want to protect it a bit better than being open to the whole Internet. To restrict the SSH access from only one IP address you can do:


       ufw allow from your_ip_address to any port 22

   for example


       ufw allow from to any port 22

   You can also allow SSH access to you server from a range of addresses using the network slash notation:


       ufw allow from to any port 22


   Before we are ready to go with your new firewall setup with ‘ufw’ we can check if it’s enabled by:


       ufw status

   and eventually if you get “status: inactive” enable it:


       ufw enable


   Get updates on cloud server security topics by following CloudBalkan.

Recursive search for a word in directory contents with grep and rgrep

   An easy way to search for a word within directory contents is the ‘rgrep’ command:


       rgrep “word /some/directory

   If you don’t have the rgrep command installed on your distribution, you can use grep with the following flags:


       grep -rnw “word/some/directory




       grep -rnw “Failed password” /var/log/auth.log

   will list you all unsuccessful login attempts on your machine.



How to create an ‘tar’ archive from files newer than specified date

   If you need to archive only the recently modified files you can use this simple syntax of the ‘tar’ command:


       tar czf target-filename -N “8 hours ago” source-directory
   The ‘-N’ flag (newer) accepts standard GNU relative date formate like “8 hours ago”, “1 day ago” and “-1 month” aswell
   The ‘tar’ archive preserves the directory tree with all subfolders, making this command quite useful for easy file system incremental backups.
   An example use of the command to backup your web files modified during the last day, will look like:


       tar czf /var/backup/archive.tar.gz -N “1 day ago” /var/www

Install phpMyAdmin from source


   In this tutorial we will give a quick instruction on downloading and installing phpMyAdmin from the source on a general setup.

   phpMyAdmin is a popular web based MySQL management environment used by many developers. On popular distributions you can quickly install phpMyAdmin using package managers. This tutorial focuses on the installation from the source files when installing with package manager is not applicable.

   Full script


       cd /usr/share




       tar xzf phpMyAdmin-


       mv phpMyAdmin- phpmyadmin


       chmod -R 0755 phpmyadmin


   In order to install phpMyAdmin you might need administrative priviledges on your server as well as administator user for the MySQL database server.

   Our recommended location for installing phpMyAdmin is ‘/usr/share’, so we start with:


       cd /usr/share

   Download the latest version of phpMyAdmin from the official site (



   and extract the files from the archive:


       tar xzf phpMyAdmin-

   rename the folder:


       mv phpMyAdmin- phpmyadmin

   change permissions:


       chmod -R 0755 phpmyadmin

   Adding Apache2 alias

   The last step to enable access to the phpMyAdmin is to add an alias to the folder in the Apache2 configuration.

   To do it either edit the apache2.conf or add a new ‘phpmyadmin.conf’ file in the Apache2 configuration folder.

   To add an alias, you must include the following lines in your Apache2 configuration:


       Alias /phpmyadmin “/usr/share/phpmyadmin/”
       <Directory “/usr/share/phpmyadmin/”>
            Order allow,deny
            Allow from all
            Require all granted

   After changing the configuration you must restart the Apache2 service:

   RHEL / CentOS:


       service httpd restart

   Debian / Ubuntu:


       service apache2 restart


   With that your phpMyAdmin should be ready to use and accessible at your server address – “/phpmyadmin”.

Installing Monit to watch and restart Linux services on CentOS 7

   Monit is an awesome small, free tool that can save you a lot of troubles by simply monitoring and eventually restarting a service. It’s simplicity and yet fairly large flexibility make it SysAdmins and DevOps best friend.



   To setup monit you should start from installing it:


       yum install monit



   Once set up, you can configure the service to be monitored by adding configuration files for each service in the ‘/etc/monit.d/’ directory.


   The service templates are pretty straight forward. You can find a plenty of example templates at:


   Build your solid applications with monit and CloudBalkan

Generating a certificate signing request (CSR) with OpenSSL

   Certificate Signing Request is most often used to request a signed SSL certificate.

   The CSR contains your company and domain information, encrypted with a strong asymetric encryption key.

   To generate an CSR you can use OpenSSL:


       openssl req -new -newkey rsa:2048 -nodes -keyout server.key -out server.csr

   This one line generates both the encryption key and the CSR itself. As a result you’ll get a ‘.csr’ file which will be used for requesting the SSL certificate and a ‘.key’ file containing the encryption private key. 

   Important!: You will need the private key later, to install the SSL certificate on your web server. Keep it safe and do not send your private key to anyone.



Backup files on a remote Linux server using ‘tar’ and SSH

   Have you ever been in the situation where you have to backup a server that is 100% full and you really can’t free up any space even to create an archive.

   Yeah if you’re on a cloud server you can just add some more storage, but how about when you’re actually migrating a physical one to the Cloud?


   There’s an easy, little one liner that could save you in that case:


       tar czf – /var/src_folder | ssh “cat > /var/dst_file.tar.gz”

   What it does is that it archives or tarbals the source folder, compresses the output and directly streams it over ssh to a remote server and save it into a file there.


   This way you don’t need any free space to temorarily store the archive on the source server.

   You could also use those command to backup your servers on a remote machine on a schedule using a cron.


   Your ideas on solid ground



CloudBalkan – PCI DSS compliance certified

   We believe that Cloud means security, and often security needs some solid proof for it. CloudBalkan is proud to declare its compliance to PCI DSS security standards, for all forms of payment and personal data processing on our platforms. Trust is important and we will keep working hard on providing top quality services on solid ground.