Backup AWS MySQL RDS Instance and Restore on Remote Machine

Was looking for a way to back up MySQL RDS to local copy to do some work a bit faster offline.

If you want to use replication to move data from one database to another - if you have the appropriate network access to do so:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/MySQL.Procedural.Exporting.NonRDSRepl.html

I used another option since my database was pretty small and I run my database access through a bastion host.

I have set up network access to RDS database from the bastion host and installed MySQL Workbench on it. I connected to my RDS instance in MySQL workbench, then click on "Data Export" in left menu and followed the prompts to dump a file.

Then I compressed and copied the dump files to my local machine.

Then reversed the process with local copy of MySQL workbench and choose "Data Import/Restore" on the local machine.

This is a quick hack. If doing this repeatedly would optimize and automate.

Can't connect to MySQL server on 'localhost' (10061)

Installed MySQL on Windows 2008 and could not connect to database running on local host via command line.

Can't connect to MySQL server on 'localhost' (10061)

Turns out if you ping localhost you'll get an IPV6 response [::1] instead of IPV4 127.0.0.1 and mysql can't translate.

To fix map vp4 to vp6 using netsh:

netsh interface portproxy add v4tov6 listenport=3306 connectaddress=::1 connectport=3306

If you see this error:

ERROR 2013 (HY000): Lost connection to MySQL server at 'reading initial communication packet', system error: 0

Make sure MySQL is running in your services list. If you did something dumb like I did and didn't install it as a service for some unknown reason, you can add it to your services list using the following:

"C:\Program Files\MySQL\MySQL Server x.x\bin\mysqld" --install

Converting SQL Server Stored Procedures to MySQL

I ran the MySQL migration tool to import my tables and data into MySQL from SQL server as noted in a prior post. It went decently well.

The conversion of stored procedures was not quite as simple. Here are some modifications I needed to make to the output script from the conversion tool:
 

• Remove 'or Replace' after CREATE (see drop/create syntax below)
• Remove AS (Views require AS, procedures don't
• Put parentheses around parameter list
• Remove @ from parameter names
• Add IN, OUT, etc. to parameter list
• Set the delimiter before to something after than semicolon and then back to semicolon 
• Semicolon after statements within the stored procedure 
• Remove 'set nocount on' 
• Remove default values for parameters (not supported) 
• Declare a delimiter which is used like GO (e.g. DELIMITER $$ and put $$ where you would normally put GO)

So for example this procedure generated by the migration tool (which won't compile):


----------------------------------------------------------------------------
Routine mydb.SP_CUSTOMER
----------------------------------------------------------------------------
DELIMITER $$

USE `mydb`$$
 CREATE or REPLACE PROCEDURE mydb.SP_CUSTOMER

@id int

AS

 Select * from mydb.customer WHERE customer_id = @id

$$

DELIMITER ;


Becomes this


/*****************************************
Routine mydb.SP_CUSTOMER
*****************************************/
DELIMITER $$

USE `raddata`$$
CREATE PROCEDURE mydb.SP_CUSTOMER

(IN id int)

Select * from mydb.customer WHERE customer_id = id;

$$

DELIMITER ;

Syntax for Altering Stored Procedures

DROP PROCEDURE IF EXISTS foo;
    delimiter $$
    create PROCEDURE foo (args)
    begin
      blah
    end$$
    delimiter ;

Deciphering Error messages
 
First of all if you're looking for error messages in the output you'll often find a generic error message like:

Error Code: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near [...code here]

To get a closer clue as to what the problem is, if you are using MySQL Workbench there's a red symbol next to the line that has the problem and the error code is underlined. The red symbol does nothing. However over the text underlined in red and a more descriptive (possibly still not helpful) box pops up.

Other MySQL and SQL Server Syntax Differences

For some additional syntax differences between MySQL and SQL Server check out this post:

http://websitenotebook.blogspot.com/2013/12/sql-server-to-mysql-syntax-differences.html

Required option -O is missing using AWS CLI (Command Line Interface)

If you get error required option -O is missing using AWS CLI it's because you need to provide the

AWS Access Key ID and AWS Secret Access Key when sending commannds.

You could add these keys to every command you type by tacking them on when you type out the command but that will get a bit tiresome.

The other option is to add environment variables which contain the keys: AWS_ACCESS_KEY and AWS_SECRET_KEY

Or you can put the configuration in a file and set the AWS_CONFIG_FILE environment variable pointing to the file as explained here:

http://websitenotebook.blogspot.com/2013/11/setting-aws-command-line-interface-on.html 

Note that if you are trying to run an application on an EC2 instance you should probably be using roles: 

http://websitenotebook.blogspot.com/2013/11/aws-keys-and-login-credentials-roles.html

AWS CLI [Errno 10060] - Max retries exceeded with url: Caused by class'socket.error'

If you are trying to run issue commands to the AWS Command Line Interface and get an error like this:

HTTPSConnectionPool(host='xxx', port=443): Max retries exceeded with url: / <Caused by <class'socket.error'>: [Errno 10060] A connection attempt failed because thee connected party did not properly response after a period of time, or an established connection failed because connected host has failed to respond)

The problem is the machine doesn't have https access to the AWS url it's trying to reach to issue your command.

If you're trying to run the command from an EC2 instance:
 
First of all your machine needs to have an elastic IP and be opened up to the Internet for some services. Check the service you are trying to access to determine if public Internet access is required or not.

Next make sure the security group your machine is in can access the service IP address on port 443.

Finally make sure the OS firewall is configured with 443 access to the IP of the AWS service you are trying to reach.

For example -- if you are trying to run this command:

ec2-describe-availability-zones --region us-east-1

You'll get the error message above which will contain this address (at time of this writing) as the address the service is trying to reach to run your command.

ec2.us-east-1.amazonaws.com

You can ping that address to get the specific IP address for that domain and add it to all the rules above, or you can open up your systems and security groups for the machine you are running the Command Line Interface tool to have outbound 443 access for any IP address.

The problem with plugging in a specific IP address to your rules is that Amazon might decide to change the IP address for the above domain name. You might want to limit your configuration and risk by creating a NAT instance as explained VPC documentation for all outbound traffic on port 443 and only allow that particular machine full access out, while configuring all other machines to send outbound traffic on port 443 to your NAT instance.

Creating Triggers and Stored Procedures on MySQL AWS RDS instances

MySQL Server Parameters MySQL has a bunch of server parameters you can configure for your database (use with care because can adversely affect security, performance and data integrity):

http://dev.mysql.com/doc/refman/5.5/en/server-parameters.html

AWS RDS Parameter Groups
In order to configure these values on an AWS RDS instance, you'll need to use AWS DB Parameter Groups:

http://docs.aws.amazon.com/AmazonRDS/latest/UserGuide/USER_WorkingWithParamGroups.html

There's a default parameter list which you cannot modify. You can create your own parameter group and save the settings, then apply that group to your RDS instance(s).

Allow Creation of Triggers and Stored Procedures

You can't create triggers or stored procedures out of the box on AWS RDS instances because you won't get super access to the database server so you'll get an error like this:

ERROR 1419 (HY000) at line 49: You do not have the SUPER privilege and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

To get around this you can set this server parameter to 1 (true): log_bin_trust_function_creators

Note that this can adversely affect replication if you don't know what you're doing.

Then apply it to your RDS instance using command line tools

rds-modify-db-instance --db-instance-identifier [your instance name here] --db-parameter-group-name [your group name here] --apply-immediately

Or AWS command line tools

aws rds modify-db-instance --db-instance-identifier bmxdb --db-parameter-group-name bmx --apply-immediately
 
Restart your instance.

Installing Command Line Interface:

http://websitenotebook.blogspot.com/2013/11/setting-aws-command-line-interface-on.html

Caveats Enabling These Settings - Impact on Replication

There are some issues with replication developers should be aware of when enabling this setting, if setting up fault tolerant master-slave architecture with replication.

http://dev.mysql.com/doc/refman/5.1/en/stored-programs-logging.html

... The implication is that although a user must have the CREATE ROUTINE privilege to create a function, the user can write a function containing a dangerous statement that will execute only on the slave where it is processed by a thread that has full privileges...
 

• To create or alter a stored function, you must have the SUPER privilege, in addition to the CREATE ROUTINE or ALTER ROUTINE privilege that is normally required. (Depending on the DEFINER value in the function definition, SUPER might be required regardless of whether binary logging is enabled. See Section 13.1.15, “CREATE PROCEDURE and CREATE FUNCTION Syntax”.)
• When you create a stored function, you must declare either that it is deterministic or that it does not modify data. Otherwise, it may be unsafe for data recovery or replication.
  By default, for a CREATE FUNCTION statement to be accepted, at least one of DETERMINISTIC, NO SQL, or READS SQL DATA must be specified explicitly. Otherwise an error occurs:
  

  ERROR 1418 (HY000): This function has none of DETERMINISTIC, NO SQL,
  or READS SQL DATA in its declaration and binary logging is enabled
  (you *might* want to use the less safe log_bin_trust_function_creators
  variable) 

  ....

  If you do not want to require function creators to have the
            SUPER privilege (for example,
            if all users with the CREATE
            ROUTINE privilege on your system are experienced
            application developers), set the global
            log_bin_trust_function_creators
            system variable to 1. 

AWS Keys and Login Credentials + Roles

This is an interesting post on the different types of AWS keys and login credentials you'll need with AWS:

http://alestic.com/2009/11/ec2-credentials

Only thing I'd also add is the use of IAM roles which are basically set up so you don't have to store credentials on a machine running AWS services. The services can make use of the role which is built into AWS EC2 instances when running on an EC2 instance -- that way you don't need to store the credentials anywhere on the machine:

http://docs.aws.amazon.com/AWSSdkDocsJava/latest/DeveloperGuide/java-dg-roles.html

Setting Up AWS Command Line Interface on Windows 2008 64 bit EC2 instance

The AWS documentation combines a lot of information for different operating systems to install AWS command line interface.

http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-set-up.html#cli-test-the-setup

Abbreviated version for Windows 2008 64 bit EC2 instance:

1. Download and install the Windows 64 bit version of the command line tools -

http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-set-up.html#install-msi-on-windows

2. Open a new command window (go to start, run, type cmd on Windows 2008)

3. Type "aws help" to verify CLI is working.

4. Type "aws configure" and enter info when prompted -- will look something like this but use your key:

AWS Access Key ID [None]: AKIAIOSFODNN7EXAMPLE
AWS Secret Access Key [None]: wJalrXUtnFEMI/K7MDENG/bPxRfiCYEXAMPLEKEY
Default region name [None]: us-west-2
Default output format [None]: json

Then you'll need to add an environment variable to tell the tool where to find your configuration file:

Start, Control Panel
Search box in top right - type 'Env' and search
Click Edit the System Environment Variables
Click Environment Variables button
Click New button under bottom box
 Enter name or variable: AWS_CONFIG_FILE
Enter the value which is the path to your conifg file, something like this: C:\Users\UserNameHere\.aws\config
Enter, ok, yada yada

Configuration Details:

http://docs.aws.amazon.com/cli/latest/userguide/cli-chap-getting-started.html

If you forgot your access key: Go to Services - IAM - Click on Users -- choose an existing user and create a new key or create a new user and a new key depending on what you're doing.

Your region name is part of the availability zone name listed for your EC2 instance in the details - Regions:

http://docs.aws.amazon.com/AWSEC2/latest/UserGuide/using-regions-availability-zones.html#concepts-regions-availability-zones

Important

The configuration file may contain confidential information, such as your security credentials. We recommend setting the file permissions of the configuration file so that it is readable only by the current user.

Install Python on Amazon EC2 Windows 2008 Instance

Download and Install Python:

http://python.org/download/

That page has all the versions with or without source for different operating systems - the lasted for windows without source at time of this writing:

http://python.org/ftp/python/3.3.3/python-3.3.3.msi

Add python to your Path environment variable:
-- Go to start, control panel
-- In search box on top right type "env" and hit enter.
-- Click on Edit the environment variables
-- Click on Environment Variables button
-- In bottom box scroll down until you see the "Path" environment variable - click on it and then edit button
-- At the of the path string add a semicolon and the path to your Python folder. Mine by default was: ;C:\Python33\;
-- hit all the ok, yeah, sure buttons...

Open a new command line window and type the following to see that Python is running:

python --help

You'll see a bunch of output like this line followed by a bunch of commands you can run:

usage: python [option] ...  

Setting up a Bastion Host on AWS

Edit: 5/20/2017 There's another option for creating a bastion host on AWS here: https://aws.amazon.com/blogs/mt/replacing-a-bastion-host-with-amazon-ec2-systems-manager/

In order to reduce attack vector and secure assets on AWS you may want to limit RDP and/or SSH access to instances through a bastion host. Bastion hosts are for inbound traffic and fully exposed to attack. These are typically computers that you want to harden very carefully and audit regularly.

http://en.wikipedia.org/wiki/Bastion_host

The nice thing on AWS is that you can simply turn off your bastion host when not in use and fire it back up as needed.

In my case I would set up one for Linux and one for Windows.

- create bastion security group(s) within your VPC
- create instances for bastion host(s) in bastion security group
- only allow remote access traffic bastion hosts to other instances in your VPC

Create one bastion security group if same people manage all the servers. If you have different people managing different server groups may further break it down based on separation of duties, say Linux server managers and Window server managers or server management and db access to RDS.

For example:

Create a bastion-windows security group. Allow traffic in only from machines allowed to access servers via RDP by limiting to IP range, VPN, etc.

Create a bastion-linux security group. Allow trafffic only from machines allowed to SSH into Linux instances at AWS.

Limit RDP to any windows boxes to only be allowed from the bastion-windows security group.

Limit SSH access to any linux machines only from the bastion security group.

That will funnel all your remote access through one channel to limit your attack vector and access points to manage.

Other options:

You might install client tools to access database on this machine or force people to log into this machine, then remote to the machine that has the client db tools depending on what type of separation of duties and groups you require.

You might want to segregate this further based on your organizational needs for separation of duties or set up additional instances to handle the load.

For example, you may want developer traffic to go straight to their instances and not through the bastion hosts for production machines.

You might also want to break it down to limit exposure of third party software security flaw. For example if you created a bastion host for accessing MySQL RDS and installed MySQL WorkBench on that host but limited that hosts access to any other machine remotely, that limits exposure if any remote execution flaws exist in MySQL WorkBench. (Not that there are any that was just an example of third party software).

Note that putting any extraneous software on your bastion host is a security risk and would be better to avoid altogether if possible.

About arbitrary execution flaws:
http://en.wikipedia.org/wiki/Arbitrary_code_execution

Can't install MySQL WorkBench on AWS instance - Solved

If you get an error stating you have to install 32 bit version of MySQL workbench when you try to install it on an AWS instance, try installing this, then attempt the MySQL workbench install again.

Microsoft Visual C++ 2010 Redistributable Package (x86)

http://www.microsoft.com/download/en/details.aspx?id=5555

Migrating SQL Server to AWS MySQL RDS Instance

Just went through the process of migrating a SQL Server database hosted on an EC2 instance to an RDS instance of MySQL. Installing Microsoft SQL Server database on AWS EC2 instance is just like putting it on any other server. Did that a while ago to test out functionality. Next wanted to migrate that data over to RDS.

Here's how I did it.

1. Created a user on my database with appropriate permissions and SQL Server password (as opposed to Windows login).

2. Set up Windows EC2 instance in private database subnet/security group where my SQL Server database was located.

3. Added the security group to itself for inbound and outbound connections to ensure instances in the group could connect to each other (see previous post).

4. Set up an instance of MySQL on RDS in database specific subnet but same security group as my SQL Server database.

5. Set up ODBC connection (Administrative tools / ODBC data sources) on EC2 instance to SQL Server database, connected and tested it out. Just created a user connection, enter IP address of EC2 instance running SQL Server, user name, password.

6. Downloaded MySQL installer for Windows from Oracle web site that does not require Internet connection.

7. Copy MySQL installer to EC2 instance and installed. Ran and failed.Said I have to run 32 bit version (which I was).

9. Spent way too long figuring out that I need to instsall this to get MySQL workbench running on small/medium AWS EC2 instances.

Microsoft Visual C++ 2010 Redistributable Package (x86)

http://www.microsoft.com/download/en/details.aspx?id=5555

10. Replace the batteries in my mouse (Just seeing if you are still paying attention. It's been a long day.).

11. Run MySQL Workbench - go to Database Menu and choose Migration Wizard.

12. Click Start Migration

13. For Source RDBMS selected:
Database System = Microsoft SQL Server
Connection Method=ODBC
Selected DSN I created above
Entered user name
Clicked store in vault and entered password
> Test Connection
> click next

14. For destination RDBMS:
Connection Method: Stardard TCP/IP
Host Name: domain name of RDS instance from AWS console
Default Port
Enter user name and password
> Test connection (at bottom)
> Next

15. Next screen - runs the script that creates the schema in the RDS instance based on analysis of MS SQL database. Note in previous post I ended up deleting a table to work around a problem with the reverse engineering of the schema step.
> Next

16. Select schemas and options from databases you want to migrate.
> Next

17. Choose objects you want to migrate
> Next

18. No migration errors.
> Next

19. Check create schema in target RDBMS (I also opted for script file)
> Next

20. Review. No errors or warnings.
> Next

21. Online copy of data to target database
> Next

22. This is where the bulk data copy occurs and the server gets really busy...
...wait...

23. At this point my MySQL WorkBench locked up and machine CPU utilization spiked to 100%  for a long period of time. I ran it for a few hours, then fired up MySQL WorkBench on another instance and spot checking see that my tables and data seem to be present on the other machine.

24. Killed it - was taking too long. Could see most of tables were created by no stored procedures.

Round 2 - tried running the whole process again with new machine and only selected tables. Also corrected my selection and chose to keep my schema in tact this time around.

25. Checked logs here by default [insert your user name] while process was running because it appeared when I hit "show logs" mysql workbench locked up:

C:\Users\[user name]\AppData\Roaming\MySQL\Workbench\log

26. Saw that my script seems to lock up on dbo objects starting with dtproperties.

27.Checked tables and all in my own schema (not dbo) appear to have data.

28. Attempted to run stored procedures, functions and views - kept hanging.

29. Tried to create a script for procedures, functions and views and output to file.

30. Ran script and figured out user account created in RDS set up process does not have permissions to execute.

31.How to enable triggers and stored procedures:

http://websitenotebook.blogspot.com/2013/11/enabling-triggers-and-stored-procedures.html

32. Got stored procedure migration script output to a file but there are numerous issues and syntax differences not handled you will need to work through to get the stored procedures to run:

http://websitenotebook.blogspot.com/2013/11/converting-sql-server-strored.html

33. For loading large amounts of data consider these recommendations...my machine kept locking up using the migration tool:

http://aws.amazon.com/articles/2933

Error in MySQL Migration Tool: UnicodeDecodeError("'utf16' codec can't decode byte 0x2e in position 38: truncated data")

Using MySQL WorkBench 6.0.7.11215 on Windows 2008 tried running the migration tool to migrate SQL Server 2008 R2 database to local MySQL database and got this error:

ERROR: Reverse engineer selected schemata: UnicodeDecodeError("'utf16' codec can't decode byte 0x2e in position 38: truncated data"): error calling Python module function DbMssqlRE.reverseEngineer

The script failed at a particular table which only had a few rows of data in it and really wasn't needed anymore so I deleted all the rows - that didn't solve the problem as expected because I figured it was only copying the schema.

I checked the column names and nothing odd there - so I just dropped the whole table since I didn't need it and that solved the problem.

There may have been something in the meta data for that table which caused the problem such as single quote, but brute force method worked for me.

Copying Files to AWS Instance Using RDP

If you want to copy files to AWS EC2 instance...

Before you connect to the instance, wander through the tabs and find check the box under "Local drives and resources" for your C: drive. Once connected to the instance, open Windows Explorer and in the address bar, type "\\tsclient\c" (without quotes). your local PC's C: drive will (slowly) open up in Explorer and you can drag&drop.

This doesn't require any additional ports to be opened up; it goes over the RDP connection you already using for remote desktop.

AWS Instances in Same Security Group Can't Connect to Each Other

I was setting up two databases in the same security group to try to migrate data from one to the other - both installed on EC2 instances, not RDS. I couldn't initially ping one machine from the other, even though I had opened up the firewall. I guess I assumed two machines in the same security group would be able to talk to each other...not the case. To resolve this I put the security group into itself in the security group configuration for both inbound and outbound traffic and then it worked.

In other words:

Edit your security group, click on details and copy the group id.

Click on the inbound tab.

Add a rule to allow whatever traffic you want to go from one server in the group into another server in the group and add the security group id as the source.





Click on the outbound tab and repeat.

Assuming you've set up appropriate firewall rules you should now be able to ping one box from another to verify the connection is working.

The Login Packet Used to Open The Connection is Structurally Invalid. 17832 SQL Server

I set up an ODBC connection to SQL Server 2008 R2 running on an AWS instance and got this error in my SQL Server logs (the login on the client was more cryptic):

The Login Packet Used to Open The Connection is Structurally Invalid. 17832 SQL Server

I had Downloaded the SQL Server ODBC driver version 11.

This article led me down a wild goose chase setting token sizes and other things unrelated to my particular SQL Server login problem:

http://support.microsoft.com/kb/169521

I was just about to try another driver version when I noticed there was a default driver in the AWS Windows 2008 instance I fired up (yes it has been a very long time since I installed MS ODBC for SQL Server - been using Java). And I had a former operations guy at my house geeking out with me who told me I needed to download the driver...so I will just blame him because it's convenient. Ha.

I set up the connection using the default ODBC driver that came in Windows 2008 and the problem magically went away. Connection successful.