Articles

Home/Articles
Articles2019-05-13T05:14:42+00:00

Backup MySQL on Debian or Ubuntu

MySQL is one of the worlds most popular opensource databases in use today.  Much of MySQL’s success can be attributed to its general ease of installation and usability.  A functional MySQL installation is a single package install on most Linux distributions.

It’s so easy to hit the ground running with MySQL that we frequently encounter scenarios where backups haven’t been considered.  Backups are just as easy to get going as MySQL itself. We’ll walk you through the process in this article by creating a script that utilizes mysqldump, MySQL’s backup utility.

MYSQL BACKUP SCRIPT

This script is designed to be generic and run on the database server directly.  You can copy and paste it into your environment, set the executable bit, and run it immediately.  We deploy this script to /usr/local/bin/mysql_backup.sh.

The script will create an individual file for each database on your server in the /dumps path.  If you want to change where the dumps are written to you can change the STORE_PATH variable.

The arguments we use are defined in MYSQL_ARGS and provide the following.

–single-transaction Provides a consistent state of the database for reliable restore.
–routines Includes stored procedures and functions in the database dump.
–events Includes any scheduled events in the database dump.
–databases We specify the databases option to have the CREATE DATABASE statements added to our dump.

Copy to Clipboard

SCHEDULING MYSQL BACKUPS VIA CRON

It’s great to be able to take database backups manually, but the real power is in scheduling them to occur automatically.  For this task, we make use of crontab.

You must set the executable bit on the script by running

Copy to Clipboard

Run crontab -e as root which will open your $EDITOR.

Copy to Clipboard

Add the following line to the crontab file.  This will run daily at 01:00.

Copy to Clipboard

The leading fields before our command are defined as

 field #   meaning        allowed values
 -------   ------------   --------------
    1      minute         0-59
    2      hour           0-23
    3      day of month   1-31
    4      month          1-12 (or names, see below)
    5      day of week    0-7 (0 or 7 is Sun, or use names)

RESTORING FROM MYSQL BACKUPS

Now that we have backups of our databases running daily, we can revert to any previous backup by running the following command.

Copy to Clipboard

This command parses the compressed backup file we have in /dumps and will restore the database revenni_app to the state when the database backup was taken.

This post is part of the Revenni Open SysAdmin series.

Revenni is a Toronto based IT consulting firm specializing in Linux System Administration, Linux Consulting, Managed Linux Services, and 24×7 Emergency Linux Support.

The Open SysAdmin series is a collection of howtos, scripts, and documents that we publish to give back to the community.  Our staff have dedicated their careers to Open Source Software and firmly believe in sharing as much as possible.

May 12th, 2019|Categories: Howto, Open SysAdmin|Tags: , , , , , , |

Configuring Postfix on Debian & Ubuntu to relay via GSuite

We are occasionally requested to set up and deploy mail services for our clients so that their services can send reminders, notifications, billing runs, etc.  Thankfully, mail is one of our fortes.  We have built and maintained mail clusters utilizing open source software (OSS) such as postfix and dovecot with over 8 million accounts on the platform.

We can practically converse in SMTP, IMAP, and POP protocols.  TLS is making that a bit more difficult for us to use spoken word, however.

Mail is simple if you have a single server running your entire shop. You set up all of your services including Sender Policy Framework (SPF), DomainKeys Identified Mail (DKIM), and Domain-based Message Authentication, Reporting & Conformance (DMARC), and you don’t have to touch it again save for regular updates and patching.  Once you introduce a handful of servers, the situation becomes a bit more complex as you now have to manage and update all of those policies to account for your new server(s), and in the time of the cloud, your servers can be on completely different networks.

Host Based Relaying

In order to simplify things and centralize your mail services, there are two main infrastructure designs.  It doesn’t make sense to have every server configured as a complete mail server, so the first option is what we will call “Host Based Relaying”.  This involves each individual node having credentials to a mail service, Gmail for example, and sending messages to Google for ultimate delivery.  It would look something like Figure 1.

Host Based Relaying Image

Figure 1: Host Based Relaying

In this scenario, each machine has a stripped down version of postfix and its sole job is to get the message to Google.  We do not have any sort of SPF, DKIM, or DMARC configured on each machine, instead, those services are configured on the Google platform.  This decreases the administrative overhead significantly.

Dedicated Relaying

Host based relaying works well with a handful of machines, but we still manage credentials on each machine.  We can further reduce our overhead and streamline our configuration by creating a dedicated relay.  The purpose of this single machine, or a load balanced pool of machines is the same as every machine in Host Based Relaying, to get the message to Google for final delivery.

Figure 2: Dedicated Relaying

Figure 2: Dedicated Relaying

In this scenario, the Dedicated Relay servers are the only machines that store the Google credentials. The configuration of all the other machines can be done with host or network based relay permissions and we eliminate most of the complexity.

Scaling and SaaS

So what happens when you have hundreds of machines?  Surely you might overstay your welcome at Google.  In that scenario, your dedicated relays become the nodes configured with SPF, DKIM, and DMARC.

There are also several Software as a Service (SaaS) solutions built specifically to handle high volumes of email.  They all have APIs so integrating with them is quite trivial.

What about Postfix and Ansible?

Now that you have a decent grasp of the options, we’ve published a basic Ansible script on our Github to configure a machine to relay via Google.  You can find the documentation and code at https://github.com/Revenni/Ansible-Postfix-GSuite-Relay.

Can you do it for us?

Absolutlely. Drop us a line.

January 8th, 2019|Categories: Howto, Open SysAdmin|Tags: , , , , |