The Backup MySQL Server action works by launching a helper EC2 instance inside your AWS account. This EC2 instance connects to your MySQL server and uses "mysqldump" to pull the data to the instance. The data is downloaded from your MySQL server to the instance as an SQL script and it is compressed using GZIP compression. Finally, it is uploaded to your S3 bucket.

This action works with any MySQL server: it will work with an Amazon RDS MySQL server, and it will work with a MySQL server you manage yourself.

Some notes about this guide and the Backup MySQL Server action:

The default MySQL server port is 3306. This guide assumes this port is being used. If your server is using a different port, please substitute the correct port where necessary.
If MySQL server user permissions, security groups and rules make you uneasy, then this action may not be for you.

Preparation

Before using the Backup MySQL Server action, some preparation is required.

1. Ensure your MySQL server is accessible from an outside source.

If you are using Amazon RDS, this is already possible. However, if you are managing your own MySQL server, then outside access may be restricted.

By default, many MySQL server installations restrict the root user to only localhost connections. A MySQL user dedicated to Skeddly is recommended with proper permissions to use "mysqldump" to download the database data.

Also, if you are managing your own MySQL server, ensure any firewall internal to the server is allowing access on the MySQL server port (default of 3306).

Many LAMP installations of MySQL provide a web-based control panel with which you can administer your MySQL server. Such administrators include phpMyAdmin and others. Usernames and passwords for the web-based administrator is not the same thing as usernames and passwords for the MySQL server.

In addition, you need to make note of the IP address and/or DNS name of your MySQL server. You will need this information. If you are accustomed to connecting using "localhost", that is not sufficient. Localhost implies a connection to the same machine and if you use this, the helper will attempt to make a MySQL connection to itself, which is not what is needed.

2. Ensure your MySQL server's security group allows access to the EC2 helper instance.

When you configure your Backup MySQL Server action, you select an EC2 security group with which the helper EC2 instance will use. Any security group that your MySQL server is using should allow access to this security group for the MySQL server port (default of 3306).

If your MySQL server is using RDS or self-hosted EC2 inside a VPC:

Using the VPC Management Console, create a new security group for the same VPC. For example, call is "skeddly-sg". Allow outbound traffic on ports 80 and 3306.
Still in the VPC Management Console, for the MySQL server's security group, add a new rule allowing access from the "skeddly-sg" security group for port 3306.

If your MySQL server is using RDS in EC2-Classic:

Using the EC2 Management Console, create a new security group that is not associated with a VPC. For example, call it "skeddly-sg".
Using the RDS Management Console, edit your RDS server's security group by adding a new "EC2 Security Group" authorization for your "skeddly-sg".

If your MySQL server is self-hosted in EC2-Classic:

Using the EC2 Management Console, create a new security group that is not associated with a VPC. For example, call it "skeddly-sg".
Still in the EC2 Management Console, for the MySQL server's security group, add a new rule allowing access from the "skeddly-sg" security group for port 3306.

3. Test using mysqldump.

At this point, you should be able to perform the following test.

Launch a new Linux EC2 instance using your "skeddly-sg" security group.
Once the instance is up and running, login using ssh.
Install mysql client using a package manager such as apt-get or yum.
Run mysqldump from your command line using your MySQL server's endpoint, username, and password.

If this test fails, then the Backup MySQL Server action will fail as well. So it's worthwhile to get this test working properly.

Configuring Your Action

Sign in to Skeddly and create a new Backup MySQL Server action.

Region: Select the correct region for the action. It should match the region that contains your MySQL server.

Schedule: Create your desired schedule, daily, weekly, monthly.

Instance Type: Select an EC2 instance type based on the size of your database. The bigger the instance type, the faster the data will be backed up, but also the more money it will cost to run.

Security Group: Specify the security group that you created for the helper. If you are using EC2-Classic, then specify the name of the group (such as "skeddly-sg"). But if you are using VPC, then you must specify the security group ID instead (such as sg-12345678).

VPC Subnet ID: If you are using EC2-Classic, then you can leave VPC Subnet ID blank. Also, if your MySQL server is in a Default-VPC, then you can also leave this field blank. But if you are using a non-Default-VPC, then you will need to specify the ID of the VPC subnet that the helper EC2 instance should launch into.

This may be the same subnet as your MySQL server or it could be a different subnet. However, since the backed-up data must be saved to Amazon S3, the helper EC2 instance must have access to the outside world. So either the select subnet must be a public subnet, or it must be a private subnet with outside access via a NAT. If the helper instance cannot have access to the outside world, then the S3 upload will fail.

Endpoint: Specify the IP address or hostname of your MySQL server. As mentioned above, using "localhost" for this will not work. If you are inside a VPC, this can be the internal/private IP address or hostname of your MySQL server.

Username: Specify the MySQL server username to use. This is not the same thing as your MySQL server web-admin's username.

Password: Specify the password to use for the username above.

Running Your Action

Once the action is configured, it will execute on schedule or when manually executed.

The action will launch a helper EC2 instance inside your AWS account. This instance will be labelled as a helper instance for Skeddly and should terminate itself when the backup is complete. If for some reason, the instance does not terminate itself when the action completes, then it can be terminated manually.