Skip to content

Backup database tables to web storage services

Jump to bottom
Nate Williams edited this page Apr 18, 2017 · 6 revisions

Backup database tables to web storage services

User problem

Bob manages the data infrastructure for a small business that include a set of MySQL databases. He knows he should set up a process to periodically make a backup of these databases, but hasn't gotten around to it. To take care of this, he wants to store a dump of each database on Amazon S3, along with a CSV copy of all master data tables (item master, etc.) that he can use for data verification and to quickly check different historical versions of the master data.

Solution

Create a pipe with a MySQL connection for each database as the source and Amazon S3 as the destination. Select the source tables from MySQL, convert them to a CSV format, and output them to S3 with a time-stamp added to the file name. Create a second pipe to copy the MySQL dump file generated each night to an archive folder on Amazon S3.

Workflow

  • Select and authenticate input connection for the database
  • Select and authenticate output connection for web storage services
  • Select source tables to include in the backup
  • Select destination folder where the files are backed-up to
  • Run a test of the pipe to verify the backup process works
  • Schedule pipe to run periodically
  • Turn the pipe "on" to put it into production

To-Do List

  • Add a connection dialog for setting up database connections
  • Clarify what is needed (firewall, etc.) to hook up to a database
  • Clarify database permissions/access rights used in the connection
  • Add control to add tables to input list
  • Add ability to add "select" query criteria to the input
  • Add information box to show the query criteria and indicate that a query is being used
  • Add control to edit the query criteria
  • Add control to remove tables from the file list
  • Update the file list to show only selected tables

Connections

  • MySQL
  • Postgres

Examples

Pipe example 01:

  • Input from Postgres
  • Select a set of tables
  • Output to Dropbox

Pipe example 02:

  • Input from MySQL
  • Add a selection query to import first 100 rows
  • Output to Google Drive

Clone this wiki locally