RDS Patrick Custom

title

MySQL for Amazon RDS Training Course

author

Patrick Mazzotta

MySQL for Amazon RDS Training Course ⌘

+

A developer's primer on MySQL with RDS orientation.

About Us⌘

• Training & Consultancy Provider
• Founded in 2005
• Currently offering over 400 courses (remote & on-site)
• Continuously exapnding through franchishing opportunities
  • http://training-franchise.com

About Me⌘

• Consulting in IT & BI for 20 years
• CEO & Co-Founder of Craedone
  • Analytics
  • Business Intelligence
  • Corporate Strategy/Red Team services
• Partnered with NobleProg since 2013

Expectations⌘

• customization vs. course standards
  • content
  • ugly slides
• SQL Server -> MySQL high-level
• Dialogue

MySQL Programs ⌘

• SERVER
  • mysqld - the MySQL server
  • mysqld_safe - a script which starts mysqld, recommended of starting the server
    • restarts the server when an error occur
    • logs run-time and failure information
  • mysqld_multi - script that can start or stop multiple servers installed on the

system (different ports or sockets)

• CLIENT
  • mysql - a command line client
  • mysqladmin - administrative operations
    • relading the grant tables
    • creating/dropping database
    • flushing tables to disk
    • managing log files
    • shutdown the database

MySQL Client⌘

• Windows [dev.mysql.com/downloads/windows/]
  • MySQL Installer
  • MySQL Connectors (drivers & connectivity)
  • MySQL Workbench
  • Excel Import/Export
• Ubuntu/Debian
  • For Debian packages: [dev.mysql.com/downloads/repo/apt/]
  • or...

apt-get install mysql-client

• OS X
  • Native package: [dev.mysql.com/doc/refman/5.7/en/macosx-installation-pkg.html]
  • Or try Homebrew: brew install mysql

We can get the RDS command line tools if we want:

• [docs.aws.amazon.com/AmazonRDS/latest/CommandLineReference/Welcome.html]

AWS vs AWS⌘

EC2 vs RDS⌘

• This is not a pros/cons scale!
• driven by preference and tech needs
• direct cost comparisons are hard

EC2		RDS
Pros	Cons	Pros	Cons
full admin control	traditional admin tasks are time consuming	less time on admin tasks	no ssh (no tweaking)
traditional SSH access and administration	plan for more (logs, patching, etc.)	automated patching/updates	apps must not be version-dependant
vertical scaling is easy	more planning for resources	very good performance out of the box	some simple things become much more complex
theoretically capable of better performance			uesr management is good, but may not be intuitive
			debugging can be more work (logs)

RDS Server Configuration⌘

Amazon’s relational database service [RDS] offers multiple platforms for their customers:

• MySQL
• Oracle
• SQL Server
• PostgreSQL

RDS is syntactically the same as the platforms it’s runs, but there are some minor differences in administrative concerns.

Benefits of RDS⌘

There are a number of factors that make RDS an attractive choice when evaluating relational databases for your architecture.

Pre-configured (and reusable) parameters	DB Event notices (SMS or email)
Built-in monitoring and metrics	Multi-Availability Zone (off-site failovers)
Automatic updates/patches	Provisioned IOPS
Automated backups	Push-button scaling
Snapshot management	Automatic Host Replacements
Isolation & Security	Replication across Multi-AZ
Resource-level permissions	Programmable instance control

Even Cheaper than RDS?⌘

Reserved instances ([pricing page]) are an option for RDS deployments known to be long-term solutions. From AWS: "Amazon RDS Reserved Instances give you the option to make a low, one-time payment for each DB instance you want to reserve and in turn receive a significant discount on the hourly charge for that instance."

• much cheaper than dynamic instances

m3 type	reserved price/h	dynamic price/h
single AZ	$0.089	$0.090
multi AZ	$0.151	$0.180

• can resell reserved instances through the marketplace
• comes in light/medium/heavy utilization groupings
• can still dynamically spin up or down multiple instances
  • as long as you're in your reserved instance zone, the instances will assume your reserved pricing rates
• requires a one or three year commitment

The Server⌘

Deployment of all database instances in RDS can be managed through the Amazon Web Services (AWS) Console.

• no SSH access
• no direct log access

Launching MySQL Instance in RDS⌘

From [console.aws.amazon.com/rds/] we select the instance type.

Deployment Type⌘

• When deploying an RDS instance you are asked if the instance is intended for production use or not.
• This tells AWS where the instance should go, and what kind of options should be made available (i.e. the Free Usage Tier lacks provisioning options).

Specify DB Details⌘

DB Details Field Summary⌘

Field	Description
License Model	MySQL has only one license model: general-public-license
DB Engine Version	Unless specifically required, you should select the default version of MySQL. Note that RDS supports different versions based on region of deployment.
DB Instance Class	This correlates directly with the instances in the RDS pricing table (see [aws.amazon.com/rds/pricing]). You should know [approximately] what class of instance you'll need before launching.
Multi-AZ Deployment	This instructs AWS to create a failover replica instance. Strongly recommended for production environments.
Allocated Storage	The amount of physical storage (in GB) your DB will be provisioned with. In some cases, allocating a higher amount of storage for your DB instance than the size of your DB can improve I/O performance (ref: [Amazon Relational Database Service Features].
Use Provisioned IOPS	Provisioned IOPS (I/O operations Per Second) setting. This is typically used for high-performance storage needs in applications that are I/O intensive; such as transactional (OLTP) database workloads.
DB Instance Identifier	The name for the DB instance that is unique for your account in the region you selected. You may chose to add some intelligence to the name such as including the region and DB engine you selected, for example west2-mysql-instance1 [helpful for sysops work].
Master Username	Type a name using alphanumeric characters that you will use as the master user name to log on to your DB instance. This will be the user name you use to logon to your DB on the instance for the first time.
Master Password/Confirmation	8-16 ASCII characters (excluding /, *, and @) for your master user password.

• Advanced settings: [RDS getting started page].

Security⌘

AWS Identity & Access Managment⌘

Identity and Access Management (IAM) is how we control access to AWS (and thereby RDS).

• it's a permission system
• rather elaborate (complicated but comprehensive)
• can manage access to:
  • DB instances
  • DB snapshots
  • Read replicas
  • Reserved instances
  • DB security groups
  • DB option groups
  • DB parameter groups
  • Event subscriptions
  • DB subnet groups
  • and all sorts of EC2 and other AWS stuff!

Standard IAM Practice⌘

1. Create IAM users (user identities) under your AWS account for all users who will manage your Amazon RDS resources. Each user can have a separate password (for console access) and access keys (for programmatic and CLI access). You can organize IAM users into groups, which makes it easier to manage permissions for multiple users at a time.
2. Determine what tasks each user and group will have regarding your Amazon RDS resources. For example, you could have groups for administrators, security personnel, DBAs, and developers.
3. Optionally, you can tag the Amazon RDS resources you want to control access to. You can assign a tag, a key-value pair, to any Amazon RDS resource, and use that tag as a way to specify a particular resource in an IAM policy..
4. Create the IAM policies that define the actions a user can take, and specify the Amazon RDS resources required for each task using Amazon Resource Names (ARNs). If you have used tags for your Amazon RDS resources, you can add conditions to the policy to test for those tag values.
5. Attach the policies to the applicable users or groups.

IAM Policies⌘

IAM Policies (con't)⌘

• By default newly created IAM users do not have permission to access any AWS resources
• users require IAM policies to access the required Amazon RDS API (or any AWS) actions and resources
• policies can be attached to users, groups, or roles
• An IAM policy is a JSON document that consists of one or more statements

{
  "Version":"2012-10-17", 
  "Statement":[{
    "Effect":"Allow",           
    "Action": "rds:CreateDBInstance",
    "Resource":"arn:aws:rds:us-east-1:1234567890:db:test*", 
    "Condition": {"streq":[
        {"rds:DatabaseEngine":"mysql"},
        {"rds:DatabaseClass":"db.t1.micro"}
  ]}
 }]
}

• The Version is required, and the value should be "2012-10-17"
  • this is the date of the most current version of the AWS IAM policy system
• The Effect element is set to either "Allow" or "Deny"
  • Actions are denied by default, so you have to specify "Allow"
• The Action element lists which AWS APIs the policy will allow.
  • here the Action element lists one action from the Amazon RDS API - so it will be the only action allowed by this policy statement
  • Note that the action is identified by both service name (rds) and action (CreateDBInstance)
  • policies can list actions from any AWS service. You can use wildcards (*) to specify actions
    • i.e. the action rds:Describe* would allow the user to perform any Amazon RDS action that begins with Describe (DescribeDBInstances, DescribeDBLogFiles, DescribeDBParameterGroups,DescribeDBSnapshots, etc.)
• The Resource element lets you specify which resources the user can perform the actions on or with
  • here the user can only create DB instances that have the prefix "test" in the DB instance name
  • You specify resources using an Amazon Resources Name (ARN) that includes the name of the service that the resource belongs to (rds), the region (us-east-1), the account number, and the type of resource (a DB instance)
• Finally, the optional Condition element lets you specify additional restrictions on the policy such as:
  • date/time
  • source IP address
  • region
  • tags
• In this example the Condition element indicates that the actions are allowed only on instances with the MySQL DB engine and the micro DB instance class

Attaching Policies⌘

Policies can be attached to

• an individual IAM user
  • in this case, only that user would be allowed to perform the listed actions
• an IAM group
  • then every IAM user in that group would have these permissions
• or a role
  • where delegated or federated users could perform the action

Users vs. Roles vs. Groups⌘

• Users
  • have permanent long-term credentials
  • are used to directly interact with AWS services
• Roles
  • do not have any credentials
  • cannot make direct requests to AWS services
  • meant to be "assumed" by authorized entities, such as IAM users, applications, or an AWS service like EC2
• Groups
  • are a collection of IAM users that share the same permissions
  • primarily a management convenience to manage the same set of permissions for a set of IAM users
  • groups cannot have roles added to themselves (yet)

Can I Haz Consoles?⌘

The following example policy statement shows permissions that users typically need in order to work in the Amazon RDS console. Notice that this includes RDS actions that start with the word "Describe," a number of EC2 and CloudWatch actions that likewise pertain to describing (listing) resources, and all SNS actions.

{	
    "Version":"2012-10-17",  
    "Statement":[{
    "Effect": "Allow",
    "Action": [
        "rds:Describe*",
        "rds:ListTagsForResource",
        "ec2:DescribeAvailabilityZones",
        "ec2:DescribeVpcs",
        "ec2:DescribeAccountAttributes",
        "ec2:DescribeSecurityGroups",
        "ec2:DescribeSubnets",
        "cloudwatch:GetMetricStatistics",
        "cloudwatch:DescribeAlarms",
        "sns:*"
            ],
   "Resource": "*"
   }]
}

Authorization Request Chain⌘

• an IAM authorization request is generated for each resource identified (or implied) in the request
• easy to trigger multiple resource authorizations, i.e.a point-in-time-restore to a new database instance will generate two authorization requests:
  1. An authorization request will be generated for the target database instance.
  2. An authorization request will be generated for the snapshot that is being restored
• a policy can be used to limit the possible values a resource can have
  • i.e. storage or compute size can be limited to specific values or ranges.
  • More elaborate coverage of [IAM Policy Evaluation Logic here]

Keys and Tags⌘

When creating an IAM policy, you can specify conditions in two ways

1. You can create a condition that is based on a tag associated with a resource
2. You can use a predefined key, such as the DB engine type or the DB engine class.

The following tables shows the predefined keys you can use when defining IAM policy for Amazon RDS. Note that tag key/value pairs and predefined keys are case sensitive.

Predefined Keys (AWS)⌘

AWS Predefined Key	Description	Value Type
aws:CurrentTime	The current time. Used for date conditions.	Date/Time
aws:EpochTime	The current time in epoch or UNIX time format. Used for date conditions.	Date/Time
aws:principaltype	The type of principal (user, account, federated user, etc.) for the current request.	String
aws:SourceIp	The requester's IP address (see IP Address ). Note that if you use aws:SourceIp, and the request comes from an Amazon EC2 instance, the instance's public IP address is evaluated.	IP Address
aws:UserAgent	The requester's client application.	String
aws:userid	The requester's user ID.	String
aws:username	The requester's user name	String

Predefined Keys (RDS)⌘

Amazon RDS also has predefined keys that you can include in Condition elements in an IAM policy. Amazon RDS predefined keys do not apply to all actions; the Amazon RDS predefined keys apply to the following actions:

• CreateDBInstance
• ModifyDBInstance
• DeleteDBInstance
• DescribeDBLogFiles
• AddTagsToResource
• RemoveTagsFromResource
• RestoreDBInstanceToPointInTime
• RestoreDBInstanceFromDBSnapshot
• DownloadDBLogFilePortion
• DescribeDBInstances

RDS Predefined Key	Description	Value Type
rds:DatabaseClass	The DB instance class of a DB instance	String
rds:DatabaseEngine	The DB engine of the DB instance	String
rds:DatabaseName	The name of the database on the DB instance	String
rds:MultiAz	Indicates if the DB instance is running in multiple availability zones. 1 indicates that the DB instance is using multi-AZ.	Integer
rds:Piops	This key will be present when a request is made for a DB instance with PIOPs enabled. The value will contain the number of provisioned IOs that an instance supports. 0 indicates does not have PIOPs enabled.	Integer
rds:StorageSize	The storage volume size (in GB)	Integer
rds:Vpc	Indicates if the database instance is running in a virtual private cloud	Boolean

Conditional Elements⌘

You can add your own tags to an Amazon RDS resource and then create policies that use the tag values in determining whether to grant or deny access. Amazon RDS resource tags are formatted slightly differently than predefined keys in aCondition element. For example, the following Condition element uses a predefined key and specifies that the condition applies to the DB engine MySQL:

"Condition":{"streq":{"rds:DatabaseEngine": "mysql" } }

The following Condition element uses an Amazon RDS tag and specifies that the condition applies to a tag with a key named environment and a value of production.

"Condition":{"streq":{"rds:db-tag/environment": ["production"]} }

More on the IAM policy Condition element [here]

Example 1: Permit a user to perform any Describe action on any RDS resource⌘

The following statement allows a user to run all the actions whose names begin with "Describe," which shows information about an RDS resource such as a DB instance. Note that the “*” in the Resource element indicates that the actions are allowed for all Amazon RDS resources.

{
"Version":"2012-10-17",  
"Statement":[{
"Effect":"Allow",
"Action":"rds:Describe*",
"Resource":"*"
  }]
}

Example 2: Permit a user to create a DB instance that uses a specified DB engine⌘

The following statement uses a predefined Amazon RDS key and allows a user to create only DB instances that use the MySQL DB engine. The Condition element indicates that the DB engine requirement is MySQL.

{ 
"Version":"2012-10-17",  
"Statement":[{
"Effect":"Allow",
"Action": "rds:CreateDBInstance",
"Resource": "*",
"Condition":{"strneq":{"rds:DatabaseEngine":"mysql"}}
  }]
}

Example 3: Permit a user to create a DB instance that uses the specified DB parameter and security groups⌘

The following statement allows a user to only create a DB instance that must use the mysql-production DB parameter group and the db-production DB security group.

{ 
"Version":"2012-10-17",  
"Statement":[{
"Effect":"Allow",            
"Action": "rds:CreateDBInstance",
"Resource": [ 
    "arn:aws:rds:us-east-1:1234567890:pg:mysql-production",
    "arn:aws:rds:us-east-1:1234567890:secgrp:db-production" ]
  }]
}

Example 4: Prevent a user from creating a DB instance that uses specified DB parameter groups⌘

The following statement prevents a user from creating a DB instance that uses DB parameter groups with specific tag values. You might apply this policy if you require that a specific customer-created DB parameter group always be used when creating DB instances. Note that statements that use Deny are most often used to restrict access that was granted by a broader statement.

{ 
"Version":"2012-10-17",  
"Statement":[{
  "Effect":"Deny",
  "Action": "rds:CreateDBInstance",
  "Resource": "*",
  "Condition": {"streq": {"rds:db-tag/usage" : "prod" } }
  }]
}

Example 5: Prevent users from creating DB instances for certain DB instance classes and from creating DB instances that use Provisioned IOPS⌘

The following statement prevents users from creating DB instances that use the DB instance classes m2.2xlarge and m2.4xlarge, which are the largest and most expensive instances. This example also prevents users from creating DB instances that use Provisioned IOPS, which is an additional cost.

{
"Version":"2012-10-17",
"Statement":[
  {
    "Effect":"Deny",
    "Action":"rds:CreateDBInstance",
    "Resource": "*",
    "Condition":{"streq":{"rds:DatabaseClass":["db.m2.4xlarge", "db.m2.2xlarge"]}}
  },
  {
    "Effect":"Deny",
    "Action":"rds:CreateDBInstance",
    "Resource": "*",
    "Condition":{"NumericNotEquals":{"rds:Piops":"0"}}
  }
]
} 

You can add a tag to an Amazon RDS resource, and then use that tag in a policy to specify a particular resource. The following examples use Amazon RDS resource tags as part of the IAM policy to specify a particular resource.

Example 6: Permits a user to perform an action on a resource tagged with two different values⌘

This following statement allows a user to perform the ModifyDBInstance and CreateDBSnapshot actions on instances with either the “stage” tag set to “development” or “test.”

{ 
"Version":"2012-10-17",  
"Statement":[{
"Effect":"Allow",            
"Action": [
    "rds:ModifyDBInstance",
    "rds:CreateDBSnapshot" ],
"Resource":"*",
"Condition":{"streq":{"db-tag/stage": [ "development", "test" ] } }
  }]
}

Example 7: Permits a user to perform actions on a DB instance with a DB instance name prefixed with the user name⌘

This following statement allows a user to perform any action (except to add or remove tags) on a DB instance that has a DB instance name that is prefixed with the user's name and that has a tag called "stage" equal to "devo" or that has no tag called "stage."

{ 
"Version":"2012-10-17",  
"Statement":[{ 
  "Effect":"Allow",            
  "NotAction": ["rds:AddTagsToResource","rds:RemoveTagsFromResource"],
  "Resource": "arn:aws:rds:*:314195462963:db:${aws:username}*",
  "Condition":{"StringEqualsIfExists":{"rds:db-tag/stage":"devo"}}
}]
}

RDS Security Groups⌘

Security groups control the access that traffic has in and out of a DB instance.

Three types of security groups are used with Amazon RDS:

1. DB security groups
2. VPC security groups (virutal private network cloud)
3. EC2 security groups

DB Security Groups⌘

Each DB security group rule enables a specific source to access a DB instance that is associated with that DB security group. The source can be a range of addresses (e.g., 203.0.113.0/24), or an EC2 security group. When you specify an EC2 security group as the source, you allow incoming traffic from all EC2 instances that use that EC2 security group. Note: DB security group rules apply to inbound traffic only; outbound traffic is not currently permitted for DB instances.

• You do not need to specify a destination port number when you create DB security group rules; the port number defined for the DB instance is used as the destination port number for all rules defined for the DB security group.
• DB security groups can be created using the Amazon RDS APIs or the Amazon RDS page of the AWS Management Console.

VPC Security Groups⌘

• Each VPC security group rule enables a specific source to access a DB instance in a VPC that is associated with that VPC security group.
  • The source can be a range of addresses (e.g., 203.0.113.0/24), or another VPC security group
  • specifying a VPC security group as the source allows incoming traffic from all instances (typically application servers) that use the source VPC security group
  • VPC security groups can have rules that govern both inbound and outbound traffic, though the outbound traffic rules do not apply to DB instances
  • Note: you must use the Amazon EC2 API or the Security Group option on the VPC Console to create VPC security groups.
• DB instances deployed within a VPC can be configured to be accessible from the Internet or from EC2 instances outside the VPC
  • if a VPC security group can specify a port access such as TCP port 22 you will not be able to access the DB instance
    • the firewall for the DB instance provides access only via the IP addresses specified by the DB security groups the instance is a member of and the port defined when the DB instance was created

You should use TCP as the protocol for any VPC security group created to control access to a DB instance. The port number for the VPC security group should be the same port number as that used to create the DB instance.

DB Security Groups vs. VPC Security Groups⌘

The following table shows the key differences between DB security groups and VPC security groups.

DB Security Group	VPC Security Group
Controls access to DB instances outside a VPC	Controls access to DB instances in VPC.
Uses Amazon RDS APIs or Amazon RDS page of the AWS Management Console to create and manage group/rules	Uses Amazon EC2 APIs or Amazon VPC page of the AWS Management Console to create and manage group/rules.
When you add a rule to a group, you do not need to specify port number or protocol.	When you add a rule to a group, you should specify the protocol as TCP, and specify the same port number that you used to create the DB instances (or Options) you plan to add as members to the group.
Groups allow access from EC2 security groups in your AWS account or other accounts. Groups allow access from other VPC security groups in your VPC only.

Security Group Scenario⌘

A common use of an RDS instance in a VPC is to share data with an application server running in an EC2 instance in the same VPC and that is accessed by a client application outside the VPC. For this scenario, you would do the following to create the necessary instances and security groups. You can use the RDS and VPC pages on the AWS Console or the RDS and EC2 APIs.

1. Create a VPC security group (for example, "sg-appsrv1") and define inbound rules that use as source the IP addresses of the client application. This security group allows your client application to connect to EC2 instances in a VPC that uses this security group.
2. Create an EC2 instance for the application and add the EC2 instance to the VPC security group ("sg-appsrv1") you created in the previous step. The EC2 instance in the VPC shares the VPC security group with the DB instance.
3. Create a second VPC security group (for example, "sg-dbsrv1") and create a new rule by specifying the VPC security group you created in step 1 ("sg-appsrv1") as the source.
4. Create a new DB instance and add the DB instance to the VPC security group ("sg-dbsrv1") you created in the previous step. When you create the instance, use the same port number as the one specified for the VPC security group ("sg-dbsrv1") rule you created in step 3.

The following diagram shows this scenario.

Security Group Scenario⌘

Working with DB Security Groups⌘

A DB security group controls network access to a DB instance that is not inside a VPC. By default, network access is turned off to a DB instance. You can specify rules in a security group that allows access from an IP address range, port, or EC2 security group. Once ingress rules are configured, the same rules apply to all DB instances that are associated with that security group. You can specify up to 20 rules in a security group.

If you are a new customer to Amazon RDS or if you are an existing customer who is using a new region, your DB instance is most likely in a default VPC. You cannot use a DB security group for a DB instance inside a VPC; you must create a VPC security group. For information on creating a VPC security group, see Security Groups for Your VPC. To determine if you have a default VPC, see step 2 in the following procedure. Read More:

• [Creating a DB Security Group]
• [Listing Available DB Security Groups]
• [Viewing a DB security group]
• [Authorizing Network Access to a DB Security Group from an IP Range]
• [Authorizing Network Access to a DB Instance from an Amazon EC2 Instance]
• [Revoking Network Access to a DB Instance from an IP Range]
• [Related Topics]

Creating DB Security Groups⌘

To create a DB security group, you need to provide a name and a description.

From AWS Management Console:

1. Sign in to the AWS Management Console and open the Amazon RDS console at https://console.aws.amazon.com/rds/.
2. Determine what platforms are supported for your AWS account in your current region.

If Supported Platforms indicates EC2,VPC, your AWS account in the current region does not use a default VPC. You can continue following the steps below to create a DB security group that will enable access to your DB instance.

Creating DB Security Group (con't)⌘

If Supported Platforms indicates VPC, your AWS account in the current region uses a default VPC. This means that you must create a VPC security group to enable access to a DB instance instead of a DB security group. For information on creating a VPC security group, see [Security Groups for Your VPC].

Creating DB Security Group (con't)⌘

3. Click Security Groups in the navigation pane on the left side of the window.
4. Click Create DB Security Group.

Creating DB Security Group (con't)⌘

5. Type the name and description of the new DB security group in the Name and Description text boxes. Note that the security group name cannot contain spaces and cannot start with a number.

6. Click Yes, Create. The DB security group will be created. Note that a newly created DB security group does not provide access to a DB instance by default. You must specify a range of IP addresses or an Amazon EC2 security group that can have access to the DB instance. To specify IP addresses or an Amazon EC2 security group for a DB security group, see Authorizing Network Access to a DB Security Group from an IP Range.

Creating DB Security Group (CLI)⌘

Use the command rds-create-db-security-group with the following parameters:

$>rds-create-db-security-group mydbsecuritygroup -d "My new security group"

Note that a newly created DB security group does not provide access to a DB instance by default. You must specify a range of IP addresses or an Amazon EC2 security group that can have access to the DB instance. To specify IP addresses or an Amazon EC2 security group for a DB security group, see [Authorizing Network Access to a DB Security Group from an IP Range].

Creating DB Security Group (API)⌘

• Call CreateDBSecurityGroup with the following parameters:
  • DBSecurityGroupName = mydbsecuritygroup
  • Description = "My new security group"

https://rds.amazonaws.com/
	?Action=CreateDBSecurityGroup
	&DBParameterGroupName=mydbsecuritygroup
	&Description=My%20new%20db%20security%20group
	&Version=2012-01-15						
	&SignatureVersion=2
	&SignatureMethod=HmacSHA256
	&Timestamp=2012-01-20T22%3A06%3A23.624Z
	&AWSAccessKeyId=<AWS Access Key ID>
	&Signature=<Signature>

Note that a newly created DB security group does not provide access to a DB instance by default. You must specify a range of IP addresses or an Amazon EC2 security group that can have access to the DB instance. To specify IP addresses or an Amazon EC2 security group for a DB security group, see [Authorizing Network Access to a DB Security Group from an IP Range].

Listing Available DB Security Groups⌘

You can list which DB security groups have been created for your AWS account.

• AWS Console
  1. Sign in to the AWS Management Console and open the Amazon RDS console athttps://console.aws.amazon.com/rds/.
  2. Click Security Groups in the navigation pane on the left side of the window.
  3. The available DB security groups appear in the DB Security Groups list.
• CLI
  • Use the command rds-describe-db-security-groups to list all available DB security groups for your AWS account

PROMPT>rds-describe-db-security-groups

• API
  • Call DescribeDBSecurityGroups with no parameters

https://rds.amazonaws.com/
	?Action=DescribeDBSecurityGroups
	&MaxRecords=100
	&Version=2009-10-16
	&SignatureVersion=2
	&SignatureMethod=HmacSHA256
	&AWSAccessKeyId=<AWS Access Key ID>
	&Signature=<Signature>

Viewing DB Security Group⌘

You can view detailed information about your DB security group to see what IP ranges have been authorized.

• AWS Console - select the details icon for the DB security group you want to view

• CLI - use the rds-describe-db-security-groups to view a DB security group specifying the DB security group you want to view

PROMPT>rds-describe-db-security-groups <mydbsecuritygroup>

• API - call DescribeDBSecurityGroups with the parameter DBSecurityGroupName = <mydbsecuritygroup>

https://rds.amazonaws.com/
	?Action=DescribeDBSecurityGroups
	&DBParameterGroupName=mydbsecuritygroup
	&Version=2009-10-16
	&SignatureVersion=2
	&SignatureMethod=HmacSHA256
	&Timestamp=2009-10-16T22%3A23%3A07.107Z
	&AWSAccessKeyId=<AWS Access Key ID>
	&Signature=<Signature>

SSL⌘

Amazon RDS supports SSL connections with DB instances running the MySQL database engine.

• Amazon RDS creates an SSL certificate and installs the certificate on the DB instance when Amazon RDS provisions the instance
• These are signed by a certificate authority. The SSL certificate includes the DB instance endpoint as the Common Name (CN) for the SSL certificate to guard against spoofing attacks. The public key is stored at https://rds.amazonaws.com/doc/mysql-ssl-ca-cert.pem.
• To encrypt connections using the default mysql client, launch the mysql client using the --ssl_ca parameter to reference the public key, for example:

mysql -h myinstance.c9akciq32.rds-us-east-1.amazonaws.com --ssl_ca=rds-ssl-ca-cert.pem --ssl-verify-server-cert

• You can use the GRANT statement to require SSL connections for specific users accounts. For example, you can use the following statement to require SSL connections on the user account encrypted_user:

GRANT USAGE ON *.* TO 'encrypted_user'@'%' REQUIRE SSL

And many more!⌘

• most of the controls you need can be found in the AWS/RDS console
• API documentation at [http://docs.aws.amazon.com/AmazonRDS/latest/APIReference/Welcome.html]
• CLI documentation at [http://docs.aws.amazon.com/AmazonRDS/latest/CommandLineReference/Welcome.html]

NOTE

• You can't authorize an Amazon EC2 security group that is in a different AWS Region than your DB instance
• By default network access is turned off to a DB instance - if you want to access a DB instance that is not in a VPC, you must set access rules for a DB security group to allow access from specific EC2 security groups
• revoking rules and permissons aren't always immediate (users vs ingress rules)

MySQL Log Files⌘

(kinda)

MySQL Logging Formats⌘

Binary log traditionally uses three formats:

• Replication capabilities in MySQL originally were based on propagation of SQL statements from master to slave. This is called statement-based logging. You can cause this format to be used by starting the server with --binlog-format=STATEMENT.
• In row-based logging, the master writes events to the binary log that indicate how individual table rows are affected. You can cause the server to use row-based logging by starting it with --binlog-format=ROW.
• A third option is also available: mixed logging. With mixed logging, statement-based logging is used by default, but the logging mode switches automatically to row-based in certain cases as described below. You can cause MySQL to use mixed logging explicitly by starting mysqld with the option --binlog-format=MIXED.

"With statement-based replication, there may be issues with replicating nondeterministic statements. In deciding whether or not a given statement is safe for statement-based replication, MySQL determines whether it can guarantee that the statement can be replicated using statement-based logging. If MySQL cannot make this guarantee, it marks the statement as potentially unreliable and issues the warning 'Statement may not be safe to log in statement format.'"

Logging in RDS⌘

• all access is through AWS console or configured through AWS console
• Amazon RDS supports mixed and row-based binary logging (version-dependant)
• viewing and listing logs: [DRY]

Slow Query Log⌘

• enable via DB Parameters in the RDS console
  • 
• default setting for long_query_time is 10 seconds
• query against mysql.slow_log
• rotate the "log" by calling stored procedure CALL rds_rotate_slow_log
• can log queries that are faster than the long_query_time by enabling log_queries_not_using_indexes

You can monitor the MySQL error log, slow query log, and the general log directly through the Amazon RDS console, Amazon RDS API, Amazon RDS CLI, or AWS SDKs. You can use the mysqlbinlog utility to download or stream a binary log. The MySQL error log is generated by default; you can generate the slow query and general logs by setting parameters in your DB parameter group. Amazon RDS rotates all of the MySQL log files, the intervals for each type are given below

So how to manage it?⌘

• Splunk
• Logstash
• old-school

MySQL vs. SQL Server⌘

Advantages of Mysql over SQL Server in RDS⌘

Or, "Preaching to the choir".

1. Read-Replicas: You can utilize the mysql built-in replication features For read-heavy database workloads by creating Read Replicas.
2. Availability: To increase database availability you could use the Multi-azfeature.In the event of DB Instance failure or an Availability Zone failure, Amazon RDS will automatically failover to the standby so that database operations.
3. VPC's: Only Mysql instances can be configured in VPC.With Amazon VPC, you can define a virtual network topology and customize the network configuration to closely resemble a traditional network that you might operate in your own datacenter.
4. High SLA: Amazon RDS recently came out of Beta and also offered a higher SLA(99.95%) for Multi-az instances which is currently available only for MysQl and Oracle

Amazon RDS SLA

The Comp Train⌘

The following slides compare the different platforms and related features. Where possible, some best practices are included.

Standard	The latest official version of SQL is SQL:2008.
MS SQL Server	MS SQL Server 2005 on Windows XP. Microsoft's SQL implementation is sometimes namedTransact-SQL, or TSQL. In this document, I'll generally write MSSQL as a short-hand for Microsoft's SQL Server product. Documentation
MySQL	MySQL Database Server 5.0.18 on Fedora Linux (i.e. MySQL AB's "classic" DBMS product�not MaxDB).

The products are running with their default settings. This is important for MySQL and MSSQL: Their interpretation of SQL may be changed rather drastically by adjusting certain configuration options, potentially increasing the level of standard

Views⌘

Standard	Views are part of the standard, and they may be updated, as long as it 'makes sense'.SQL:2008 has a rather complicated set of rules governing when a view is updatable, basically saying that a view is updatable, as long as the update-operation translates into an unambiguous change.SQL-92 was more restrictive, specifying that updatable views cannot be derived from more than one base table.
MSSQL	Conforms to at least SQL-92.
MySQL	Conforms to at least SQL-92.

Join types and features⌘

All the DBMSes support basic INNER JOINs, but vary in their support for other join types. In the following feature chart, a ✓ means yes; an empty table cell means no.

Join type/feature	MSSQL	MySQL
Natural joins (only tested: NATURAL LEFT JOIN)		✓
USING-clause		✓
FULL joins1 (tested: SELECT...FULL JOIN...ON...=...)	✓
Explicit CROSS JOIN (cartesian product)	✓	✓

Note that FULL joins may be emulated with a union of a left and a right join.

Copying structure⌘

Objective: An existing table, t1 needs to be copied to a new table, t2, without copying data. I.e., only the structure/definition of the table is copied.

Standard	Optional feature T171 defines LIKE clause in table definition: CREATE TABLE t2 ( LIKE t1 )The DBMS may support an extension of this (feature T173) which allows for more table properties to be copied: CREATE TABLE t2 ( LIKE t1 INCLUDING IDENTITY INCLUDING DEFAULTS INCLUDING GENERATED )If INCLUDING DEFAULTS is not specified, column defaults will not be part of t2; likewise with IDENTITYand GENERATED properties.Triggers, CHECK constraints, and other 'non-trivial' table features are not copied to the new table.
MSSQL	Does not support the standard. Instead, MSSQL has a special SELECT ... INTO ... FROM ...construct which can be combined with an impossible WHERE-clause to copy structure only: SELECT * INTO t2 FROM t1 WHERE 1<>1The source (t1) may be a view, as well as a table.SELECT ... INTO copies NOT NULL column attributes, but nothing else.
MySQL	Complies with the core of the feature (T171), but not with the extended features (T173).MySQL does not allow you to copy the structure of a view into a table.

Ordering result sets⌘

Standard	The SQL-standard states that relations are unordered, but result sets may be ordered when returned to the user through a cursor:DECLARE cursorname CURSOR FOR SELECT ... FROM ... WHERE ... ORDER BY' column_name1''','''column_name2''',...The DBMS may additionally allow ORDER BY outside cursor definitions (optional feature IDs F850, F851, F852, F855).(Since SQL:2008)The standard doesn't specify how NULLs should be ordered in comparison with non-NULL values, except that any two NULLs are to be considered equally ordered, and that NULLs should sort either above or below all non-NULL values. However, the DBMS may optionally (as part of feature ID T611, "Elementary OLAP operations") allow the user to specify whether NULLs should sort first or last: ... ORDER BY ... NULLS FIRST or ... ORDER BY ... NULLS LAST
MSSQL	As well as in cursor definitions, it allows ORDER BY in other contexts. NULLs are considered lowerthan any non-NULL value.DOCUMENTATION
MySQL	As well as in cursor definitions, it allows ORDER BY in other contexts.NULLs are considered lower than any non-NULL value, except if a - (minus) character is added before the column name and ASC is changed to DESC, or DESC to ASC; this minus-before-column-name feature seems undocumented.

Simple limit⌘

Objective: Want to only get n rows in the result set. Usually only makes sense in connection with an ORDER BY expression.

Note: This is not the same as a top-n query � see next section. Also: Some of the queries below may not be legal in all situations, such as in views or sub-queries. Read more at Use The Index, Luke!

Standard	The SQL standard provides three ways of performing a 'simple limit': Using FETCH FIRST:(since SQL:2008)Non-core feature IDs F856, F857, F858, and F859 describe using SELECT ... FROM ... WHERE ... ORDER BY ... FETCH FIRST'''n'''ROWS ONLYYou may write ROW instead of ROWS. Using a Window function:(since SQL:2003)Non-core Feature ID T611 specifies window functions, of which one is ROW_NUMBER() OVER: SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber, columns FROM tablename ) AS foo WHERE rownumber <= n Using a cursor:If your application is stateful (in contrast to web applications which normally have to be seen as stateless), then you might look at cursors (core feature ID E121) instead. This involves: DECLARE cursor-name CURSOR FOR ... OPEN cursor-name FETCH ... CLOSE cursor-name
MSSQL	Supports the ROW_NUMBER()(since MSSQL 2005) and cursor standards-based approaches; doesn't support FETCH FIRST.MSSQL 2000 didn't support ROW_NUMBER(). Instead, a MSSQL 2000-specific syntax was needed: SELECT 'TOP' n columns FROM tablename ORDER BY key ASC The TOP construct is still available in MSSQL 2008, and it's handy for casual SQL work.
MySQL	Doesn't support the standard. Alternative solution:SELECT columns FROM tablename ORDER BY key ASC 'LIMIT' n

=Top-n query⌘

Objective: Like the simple limit-query above, but include rows with tie conditions. Thus, the query may return more than n rows. Some call this a quota-query. The following examples are based on this table:

SELECT * FROM person ORDER BY age ASC;
+----------+-------------+-----+
|PERSON_ID | PERSON_NAME | AGE |
+----------+-------------+-----+
|        7 | Hilda       |  12 |
|        8 | Bill        |  12 |
|        4 | Joe         |  23 |
|        2 | Veronica    |  23 |
|        3 | Michael     |  27 |
|        9 | Marianne    |  27 |
|        1 | Ben         |  50 |
|       10 | Michelle    |  50 |
|        5 | Irene       |  77 |
|        6 | Vivian      |  77 |
+----------+-------------+-----+

Now, we only want the three (n=3) youngest persons displayed, i.e. a result set like this:

+----------+-------------+-----+
|PERSON_ID | PERSON_NAME | AGE |
+----------+-------------+-----+
|        7 | Hilda       |  12 |
|        8 | Bill        |  12 |
|        4 | Joe         |  23 |
|        2 | Veronica    |  23 |
+----------+-------------+-----+

=Top-n query (con't)⌘

Standard	With standard SQL, there are two principal ways to obtain the wanted data: The fast variant:One of the major additions in SQL:2003 was the addition of non-core (i.e. optional) OLAP (online analytic processing) features. If the DBMS supports elementary OLAP (feature ID F611), then the top-n query may be formulated using a window function, such asRANK() OVER:SELECT * FROM ( SELECT RANK() OVER (ORDER BY age ASC) AS ranking, person_id, person_name, age FROM person ) AS foo WHERE ranking <= 3(Change ASC to DESC in the position marked like this in order to get a top-3 oldest query instead.) The slow variant:If the DBMS doesn't support the elementary OLAP features, then the top-n solution may be obtained in an alternative way which is so slow that it's not a real option in most situations:Correlated subquery method, mentioned in the book Practical Issues in Database Management (chapter 9: Quota Queries) by Fabian Pascal (who, again, quotes Date for the solution):SELECT * FROM person AS px WHERE ( SELECT COUNT(*) FROM person AS py WHERE py.age < px.age ) < 3The query may make more sense if the objective is re-phrased as "Find all persons (px) such that the number of younger, other persons (py) is less than 3".(Change < to > in the position marked like this in order to get a top-3 oldest query instead.)In the article Going To Extremes by Joe Celko, there is a description of yet another principle for performing quota queries, using scalar subqueries. Scalar subqueries are more tedious to write but might yield better performance on your system.
MSSQL	Supports the fast standard SQL variant.MSSQL 2000 supported the slow standard SQL variant. In practice, a MSSQL-only expression had to be used instead, in order to obtain acceptable query performance: SELECT TOP 3 WITH TIES * FROM person ORDER BY age ASC (Change ASC to DESC in the position marked like this in order to get a top-3 oldest query instead.)
MySQL	Supports the slow standard SQL solution. In practice, this MySQL-specific solution should be used instead, in order to obtain acceptable query performance:SELECT * FROM person WHERE age <= COALESCE( -- note: no space between "COALESCE" and opening parenthesis ( SELECT age FROM person ORDER BY age ASC LIMIT 1 OFFSET 2 -- 2=n-1 ), ( SELECT MAX(age) FROM person ) )(Change <= to >= and ASC to DESC and MAX to MIN in the positions marked like this in order to get a top-3 oldestquery instead.)The offset-value 2 is the result of n-1 (remember: n is 3 in these examples).The second argument to the COALESCE call makes the query work in cases where the cardinality of the table is lower than n.

Limit with offset⌘

Objective: Want to only get n rows in the result set, and we want the first skip rows in the result set discarded. Usually only makes sense in connection with an ORDER BY expression.

In the recipes below, basic ordering is ASCending, i.e. lowest-first queries. If you want the opposite, then change ASC->DESC and DESC->ASC at the places emphasized like this.

Standard	The SQL standard provides three ways of performing 'limit with offset': Using OFFSET and FETCH FIRST:(since SQL:2008)SELECT... FROM ... WHERE ... ORDER BY ... OFFSET'''skip'''ROWS' FETCH FIRST n ROWS ONLY'You may write ROW instead of ROWS. Using a window function:(since SQL:2003)Non-core Feature ID T611 specifies window functions, one of which is ROW_NUMBER() OVER: SELECT * FROM ( SELECT ROW_NUMBER() OVER (ORDER BY key ASC) AS rownum, columns FROM tablename ) AS foo WHERE rownum > skip AND rownum <= (n+skip) Using a cursor:You may use a cursor (core feature ID E121), if the programming environment permits it. This involves: DECLARE cursor-name CURSOR FOR ... OPEN cursor-name FETCH RELATIVE number-of-rows-to-skip ... CLOSE cursor-name
MSSQL	Supports the window function and cursor based approaches.MSSQL 2000 didn't support ROW_NUMBER(); instead, a MSSQL-specific syntax had to be used: SELECT * FROM ( SELECT TOP n * FROM ( SELECT TOP z columns -- (z=n+skip) FROM tablename ORDER BY key ASC ) AS FOO ORDER BY key DESC -- ('FOO' may be anything) ) AS BAR ORDER BY key ASC -- ('BAR' may be anything)Documentation
MySQL	Doesn't support the standard approaches. Alternative solution: SELECT columns FROM tablename ORDER BY key ASC' LIMIT' n 'OFFSET' skipIn older versions of MySQL, the LIMIT-syntax is less clear: ... LIMIT [skip,] n (i.e. the skip argument is optional). The old syntax is still supported by later MySQL versions (the old syntax is widely used).

Inserting several rows at a time⌘

Standard	An optional SQL feature is row value constructors (feature ID F641). One handy use of row value constructors is when inserting several rows at a time, such as:INSERT INTO tablename VALUES (0,'foo') , (1,'bar') , (2,'baz');� which may be read as a shorthand forINSERT INTO tablename VALUES (0,'foo'); INSERT INTO tablename VALUES (1,'bar'); INSERT INTO tablename VALUES (2,'baz');
MSSQL	Supported.(since version 2008)
MySQL	Supported.

BOOLEAN Type⌘

Standard	The BOOLEAN type is optional (has feature ID T031), which is a bit surprising for such a basic type. However, it seems that endless discussions of how NULL is to be interpreted for a boolean value is holding BOOLEAN from becoming a core type.The standard says that a BOOLEAN may be one of the following literals: TRUE FALSE UNKNOWN or NULL (unless prohibited by a NOT NULL constraint)The DBMS may interpret NULL as equivalent to UNKNOWN. It is unclear from the specification if the DBMS must support UNKNOWN, NULL or both as boolean literals. In this author's opinion, you should forget about the UNKNOWN literal in order to simplify the situation and let the normal SQL three-way logic apply.It's defined that TRUE > FALSE (true larger than false).
MSSQL	Doesn't support the BOOLEAN type.Possible alternative type: the BIT type which may have 0 or 1 (or NULL) as value. If you insert an integer value other than these into a field of type BIT, then the inserted value will silently be converted to 1.Rudy Limeback has some notes about oddities with the MSSQL BIT type.
MySQL	Offers a non-conforming BOOLEAN type. MySQL's BOOLEAN is one of many aliases to its TINYINT(1) type.(Take care if you use TINYINT(1) and JDBC with MySQL and expect to get non-boolean values from it.)MySQL accepts the literals TRUE and FALSE as aliases to 1 and 0, respectively. However, you may also assign a value of � e.g. � 9 to a column of type BOOLEAN (which is non-conforming).If you use JDBC with MySQL, then BOOLEAN is the preferred type for booleans: MySQL's JDBC-driver implicitly converts between Java's boolean and MySQL's pseudo-BOOLEAN type.Side note: MySQL has a BIT type which may be interesting for people with enormous amounts of boolean-type data.

The CHAR type⌘

For the following section, I have used this test-SQL to try to illuminate differences (unfortunately, even standard SQL as simple as this has to be adjusted for some products): Test steps:

CREATE TABLE chartest (
  charval1 CHAR(10) NOT NULL,
  charval2 CHAR(10) NOT NULL,
  varcharval VARCHAR(30) NOT NULL
);
INSERT INTO chartest VALUES ('aaa','aaa','aaa');
INSERT INTO chartest
  VALUES ('aaaaaa      ','aaa','aaa'); -- should truncate to 'aaaaaa    '
INSERT INTO chartest
  VALUES ('aaaaaaaaaaaa','aaa','aaa'); -- should raise error
SELECT * FROM chartest; -- should show two rows
DELETE FROM chartest WHERE charval1='aaaaaa';
SELECT * FROM chartest; -- should show one row
SELECT * FROM chartest WHERE charval1=varcharval;
SELECT charval1 || 'X' AS res FROM chartest;
SELECT CHAR_LENGTH(charval1 || charval2) AS res FROM chartest;
SELECT CHAR_LENGTH(charval1) + CHAR_LENGTH(charval2)
  AS res
  FROM chartest;

Expected results:

SELECT * FROM chartest; -- should show two rows
CHARVAL1   CHARVAL2   VARCHARVAL
========== ========== ==============================
aaa        aaa        aaa
aaaaaa     aaa        aaa

DELETE FROM chartest WHERE charval1='aaaaaa';
SELECT * FROM chartest; -- should show one row
CHARVAL1   CHARVAL2   VARCHARVAL
========== ========== ==============================
aaa        aaa        aaa
SELECT * FROM chartest WHERE charval1=varcharval;
CHARVAL1   CHARVAL2   VARCHARVAL
========== ========== ==============================
aaa        aaa        aaa 

SELECT charval1 || 'X' FROM chartest AS res;
   res
===========
aaa       X

SELECT CHAR_LENGTH(charval1 || charval2) AS res FROM chartest;
   res
===========
        20

SELECT character_length(charval1) + character_length(charval2)
AS res
FROM chartest;
    res
============
          20

Actual Results⌘

Standard	Return with an exception state if the inserted string is too long, unless the characters exceeding the limit are all spaces. Pad CHAR columns with spaces if the inserted string is shorter than the specified CHAR-length. Pad with trailing spaces as needed when casting or comparing to other string-like values (e.g. VARCHARs).
MSSQL	Generally follows standard, but (conceptually) truncates trailing white-space before performing some functions (at least before LEN()).
MySQL	Breaks the standard by silently inserting the string, truncated to specified column CHAR-length. (It's actually not completely silent, as it issues warnings if values were truncated: If you manually check for warnings, you will know that something bad happened, but not which of the rows are now invalid.) Violates the standard by effectively truncating all trailing spaces. The documentation states that MySQL truncates trailing spaces when CHAR values are retrieved. That may be true, but it seems that truncation even happens before the CHAR values are used as input in functions like CONCAT,CHAR_LENGTH, etc.Documentation

The TIMESTAMP type⌘

Standard	Part of the Core requirements, feature ID F051-03. Stores year, month, day, hour, minute, second (with fractional seconds; default is 6 fractional digits). Extension to Core SQL (feature ID F411): TIMESTAMP WITH TIME ZONE which also stores the time zone.Examples of TIMESTAMP literals: TIMESTAMP '2003-07-29 13:19:30' TIMESTAMP '2003-07-29 13:19:30.5'Examples of TIMESTAMP WITH TIME ZONE literals: TIMESTAMP '2003-07-29 13:19:30+02:00' TIMESTAMP '2003-07-29 13:19:30.5+02:00'It's strange that TIMESTAMP WITH TIME ZONE literals are not represented as, e.g.,TIMESTAMP WITH TIME ZONE '2003-07-29 13:19:30+01:00', but according to Melton & Simon's book, they aren't.
MSSQL	Note that MSSQL's choice of words related to date and time is confusing: In MSSQL's vocabulary, datetime is a concrete data type, whereas in the SQL standard, datetime is a general term covering the DATE, TIME and TIMESTAMP types.MSSQL has a strange pseudo-type called TIMESTAMP, but has deprecated it; don't use it in new code.The closest match to the SQL standard's TIMESTAMP type is DATETIME. This type stores the combination of date and time. It has a maximum of three fractional digits for seconds.Performs good sanity checks on inserted timestamp values; e.g. this will work: INSERT INTO tablename (columnname) VALUES ('2003-02-28 00:05:00') while this will fail: INSERT INTO tablename (columnname) VALUES ('2003-02-29 00:05:00')
MySQL	No matter what date/time data type chosen in MySQL, storage of fractional seconds and time zones are not supported (the TIME type accepts time literals with fractional seconds, but discards the fractional part when storing the value). You will have to invent your own systems for such information. Note also, that MySQL's choice of words related to date and time is confusing: In MySQL's vocabulary, datetime is a concrete data type, whereas in the SQL standard, datetime is a general term covering the DATE, TIME and TIMESTAMP types.MySQL has a type called TIMESTAMP, but it is quite different from the standard TIMESTAMP: It's a 'magic' data type with side effects in that it's automatically updated to the current date and time if some criteria are fulfilled.MySQL has a type called DATETIME. Like MySQL's TIMESTAMP type, it stores a combination of date and time without fractional seconds. There are no side effects associated with the DATETIME type�which makes it the closest match to the SQL standard's TIMESTAMP type.By default, MySQL's sanity checks with regard to dates and time are (deliberately) poor. For example, MySQL accepts DATETIME values of '2003-02-29 00:05:00' and '2003-01-32 00:00:00'. Such values yield warnings (which you must check for if you want to be warned), but result in a value of zero being stored.

SQL Functions⌘

Character Length

Standard	CHARACTER_LENGTH(argument) If the optional feature T061 is implemented, the function may be augmented with an indication ofstring unit: CHARACTER_LENGTH(argument USING string-unit) string-unit may be UTF8, UTF16, UTF32.Returns NUMERIC. Returns NULL if the input is NULL. Alias: CHAR_LENGTH. The argument may be of type CHAR or VARCHAR. Part of the Core SQL requirements (feature ID E021-04). Related function: OCTET_LENGTH.
MSSQL	Doesn't have CHARACTER_LENGTH. Provides the LEN and DATALENGTH functions instead (the latter is especially valid for 'special' data types like the TEXT type). Note that MSSQL's LEN-function removes trailing (not leading) spaces from CHAR values before counting; MSSQL's DATALENGTH doesn't discard spaces.
MySQL	Provides CHARACTER_LENGTH. Aliases: CHAR_LENGTH, LENGTH. Note that MySQL removes trailing (not leading) spaces from CHAR values before counting.

SUBSTRING⌘

Standard	The standard defines two variants of the SUBSTRING function:1. To comply with Core SQL (Feature E021-06), the DBMS must support an 'ordinary' SUBSTRING function which extracts characters from a string: SUBSTRING(input FROM start-position [FOR length]) Strings start at position 1. The start-position argument is a numeric value, as is the optional length-argument. If no length parameter is indicated, length becomes infinite(The standard specifies an extra optional argument�USING x�that has to do with Universal Character Sets, e.g. Unicode. x may be one of OCTETS or CHARACTERS.)The result is NULL if any of the arguments is NULL.Some cases of out-of-range values for start-position and length are allowed. Examples:o SUBSTRING('12345' FROM 6) yields the empty string.o A start-position less than 1 effectively sets start-position to 1 and reduces the value of length by 1+abs('start-position'). I.e., if start-position is -3 and length is 6, then the length value becomes 2. Another way to put it is that when start-position is negative, a bunch of arbitrary/blank characters are prepended to the input-value. bunch=1-'start-position.For an exact definition: see item three in the "General Rules" part of section 6.29 in the standard.2. The DBMS may optionally offer a regular expression variant (Feature T581) of SUBSTRING: SUBSTRING(input SIMILAR pattern ESCAPE escape-char) Pattern deserves some explanation. It's a string which needs to consist of three parts: A part matching before the wanted sub-string, the wanted substring, and a part matching afterthe wanted substring. The parts must be separated by a combination of the indicated escape-char (escape-character) and a double-quote ("). Example: SUBSTRING('abc' SIMILAR 'a#"b#"c' ESCAPE '#') should yield b The pattern description rules in SQL don't completely resemble POSIX regular expressions, as far as I can see.
MSSQL	MSSQL has a SUBSTRING function, but its syntax differs from that of the standard. The syntax is:SUBSTRING(input, start, length)where start is an integer specifying the beginning of the string, and length is a non-negative integer indicating how many characters to return.MSSQL has no regular expression functionality.
MySQL	MySQL supports the standard's ordinary SUBSTRING function, with some twists (see below). No regular expression based substring extraction is supported. MySQL breaks the standard when negative values are used as either start-position or length: According to the standard, SUBSTRING('abc' FROM -2 FOR 4) should yield 'a';in MySQL, the result is 'bc'. According to the standard, SUBSTRING('abc' FROM 2 FOR -4) should yield an error; MySQL returns an empty string.

REPLACE⌘

Standard	Not mentioned. May be obtained through a combination of other functions (have a look at the OVERLAY, POSITION and CHARACTER_LENGTH functions). A de facto standard seems to have emerged with regard to REPLACE: REPLACE (haystack:string,needle:string,replacement:string) which means 'replace needle with replacement in the string haystack'. Replacement is done case-sensitively unless otherwise stated. The REPLACE function may be handy for correcting spelling errors (and other situations): UPDATE tablename SET fullname=REPLACE(fullname,'Jeo ','Joe ')
MSSQL	Follows de facto standard with the exception that MSSQL by default works case insensitively.
MySQL	Follows de facto standard. MySQL even works case sensitively.1 Note that the REPLACE-function is different from MySQL's non-standard REPLACE INTO expression.

TRIM⌘

Standard	Core SQL feature ID E021-09: TRIM(where characters FROM string_to_be_trimmed)where may be one of LEADING, TRAILING or BOTH�or omitted which implies BOTH.characters indicates what character(s) to remove from the head and/or tail of the string. It may be omitted which implies the value ' ' (space character).In other words, the shortest form is TRIM(string_to_be_trimmed) which in effect meansTRIM(BOTH ' ' FROM string_to_be_trimmed).Trimming NULL returns NULL.
MSSQL	Doesn't support the standard TRIM function.Provides LTRIM(string_to_be_trimmed) and RTRIM(string_to_be_trimmed)Documentation: LTRIM and RTRIM
MySQL	Follows the standard.

LOCALTIMESTAMP⌘

It's often important to get the value of current date and time. Below are the functions used to do that in the different implementations.

Standard	The current timestamp (without time zone) is retrieved with the LOCALTIMESTAMP function which may be used as:SELECT LOCALTIMESTAMP ... or SELECT LOCALTIMESTAMP(precision) ...Note that "SELECT LOCALTIMESTAMP() ..." is illegal: If you don't care about the precision, then you must not use any parenthesis.If the DBMS supports the non-core time zone features (feature ID F411), then it must also provide the functions CURRENT_TIMESTAMP and CURRENT_TIMESTAMP(precision) which return a value of type TIMESTAMP WITH TIME ZONE. If it doesn't support time zones, then the DBMS must notprovide a CURRENT_TIMESTAMP function.
MSSQL	Doesn't have the LOCALTIMESTAMP function.Instead, it has CURRENT_TIMESTAMP which�however�doesn't return a value of TIMESTAMP WITH TIME ZONE, but rather a value of MSSQL's DATETIME type (which doesn't contain time zone information).
MySQL	Follows the standard.

Concatenation⌘

Standard	Core feature ID E021-07: Concatenating two strings is done with the || operator:string1 || string2If at least one operand is NULL, then the result is NULL.It's unclear to me if the DBMS is allowed to try to automatically cast the operands to concatenation-compatible types.
MSSQL	Breaks the standard by using the '+' operator instead of '||'.Does not automatically cast operands to compatible types. If an operand is NULL, then the result is NULL.
MySQL	Badly breaks the standard by redefining || to mean OR.Offers instead a function, CONCAT(string, string), which accepts two or more arguments.Automatically casts values into types which can be concatenated. If an operand is NULL, then the result is NULL.

Constraint handling⌘

The UNIQUE constraint

Standard	As the constraint name indicates, a (set of) column(s) with a UNIQUE constraint may only contain unique (combinations of) values.A column�or a set of columns�which is subject to a UNIQUE constraint must also be subject to a not NULL constraint, unless the DBMS implements an optional "NULLs allowed" feature (Feature ID 591). The optional feature adds some additional characteristics to the UNIQUE constraint:1. Columns involved in a UNIQUE constraint may also have NOT NULL constraints, but they do not have to.2. If columns with UNIQUE constraints do not also have NOT NULL constraints, then the columns may contain any number of NULL-'values'. (Logical consequence of the fact that NULL<>NULL.) In the standard-parlance, the constraint is satisfied, ifthere are no two rows in [the relation] such that the value of each column in one row is non-null and is not distinct from the value of the corresponding column in the other row
MSSQL	Follows the standard�with a twist:MSSQL offers the NULLs allowed feature, but allows at most one instance of a NULL-'value', if NULLs are allowed; i.e. breaks characteristic 2 in the above description of the standard.
MySQL	Follows the standard, including the optional NULLs allowed feature.

Automatic key generation⌘

It's sometimes handy to have the platform handle generation of keys. The platforms offer various means for this.

Standard	The standard specifies a column attribute of: GENERATED ... AS IDENTITY (non-core feature ID T174+T175).When creating a table, an IDENTITY clause may be declared for certain types of columns (INTEGER being one):CREATE TABLE tablename ( tablename_id INTEGER GENERATED ALWAYS AS IDENTITY ... )orCREATE TABLE tablename ( tablename_id INTEGER GENERATED BY DEFAULT AS IDENTITY ... )The column with the IDENTITY attribute will be given values in increasing order, possibly with 'holes' (...,3,4,7,...).A base table may at most contain one column with the IDENTITY attribute. NOT NULL is implied for an IDENTITY column. Normally, a column declared with IDENTITY will also be declared PRIMARY KEY, but it's not implied.The examples differ in their 'ALWAYS' vs. 'BY DEFAULT' clauses: When ALWAYS is specified, the user cannot specify a value for the column which means that the DBMS can guarantee successful insertion of a unique value on each table insert. When BY DEFAULT is specified, the user may manually specify what value to put in the identity field of a row. The flip side is that the DBMS cannot guarantee that this will work.The standard specifies several extended options which may be declared for a generated IDENTITY column.
MSSQL	MSSQL offers IDENTITY as a column property, but with a different syntax than the standard's specification. An example of creating a table with an IDENTITY column:CREATE TABLE tablename ( tablename_id INT IDENTITY PRIMARY KEY, ... )With MSSQL's IDENTITY attribute, the user cannot manually insert the value, unless the user has first run SET IDENTITY_INSERT tablename ON MSSQL refuses to update values in IDENTITY columns.I.e., MSSQL's IDENTITY type is closest to the standard's GENERATED ... ALWAYS AS IDENTITYvariant.
MySQL	MySQL doesn't support the standard's IDENTITY attribute.As an alternative, an integer column may be assigned the non-standard AUTO_INCREMENTattribute:CREATE TABLE tablename ( tablename_id INTEGER AUTO_INCREMENT PRIMARY KEY, ... )Columns with the AUTO_INCREMENT attribute will�under certain conditions�automatically be assigned a value of <largest value in column>+<at least 1>. Look in MySQL's documentation for the (rather extensive) details.A table can have at most one column with the AUTO_INCREMENT attribute; that column must be indexed (it doesn't have to be a primary key, as in the example SQL above) and cannot have a DEFAULT value attribute.It's probably not too far fetched to think of MySQL's AUTO_INCREMENT feature as this equivalence: MySQL: CREATE TABLE tablename ( columnname INTEGER AUTO_INCREMENT PRIMARY KEY ... ) Standard SQL: CREATE TABLE tablename ( columnname INTEGER DEFAULT some_func() PRIMARY KEY ... ) where some_func() is a function which finds 1 plus the currently largest value of columnname.The nice thing about this approach is that the automatic value insertion should never fail, even though some of the column's values might have been manually set�i.e. the combined advantages of the standard's ALWAYS and BY DEFAULT variants.The drawback is that it might result in more house-keeping: The system may need extra table locks when performing row updates/insertions to protect against ghost updates in concurrent transactions�thus slowing down the system in case of many concurrent updates/insertions.

TRUNCATE TABLE⌘

It's sometimes useful to be able to remove all rows from a large table in a quick way. And often, DELETE isn't as quick as we want. So several DBMSes implement a TRUNCATE operation. Typically, truncating means that deletion isn't associated with triggers which may exist for the table, and typically, truncating involves little (if any) transaction log activity.

Standard	The SQL standard defines the TRUNCATE TABLE tablename statement (optional feature ID F200, new in SQL:2008) as: Delete all rows of a base table without causing any triggered action.Unfortunately, the standard doesn't specify1. whether TRUNCATE TABLE should be allowed in a transaction involving other statements, or not2. whether TRUNCATE TABLE should imply an immediate COMMIT, or not
MSSQL	Follows the standard.In MSSQL, TRUNCATE TABLE is allowed in a transaction involving other operations, andTRUNCATE TABLE does not imply an immediate COMMIT operation.You need to have at least ALTER-permission on the table to be truncated.
MySQL	MySQL has a TRUNCATE TABLE statement, but it doesn't always follow the standard.Note that in some cases, MySQL's truncate command is really the equivalent of an unrestricted DELETE command (i.e.: potentially slow and trigger-invoking). Its behaviour depends on whichstorage engine the table is managed by.When using InnoDB (transaction safe) tables, TRUNCATE TABLE is allowed in a transactioninvolving other operations, however TRUNCATE TABLE implies an immediate COMMIT operation.

Command line procedures / metadata⌘

The following are not necessarily SQL operations, but rather a description of how different operations are performed in the command line interface provided by each product.

• some people hate using CLI
• some people can't imagine not using it for everything

Starting the command line interface⌘

Standard	Not defined.
MSSQL	The command line interface is started by running sqlcmdsqlcmd is not nice to work with. It's bad at formatting result sets. It doesn't have command line completion. You have to say go after your commands. A positive thing about sqlsmd: It has command history, so you may press arrow-up for previous commands in the current sqlsmd session.In MSSQL 2000, the command line interface was started by running osql.An alternative to osql�apart from HenPlus, mentioned above�is SQSH which should work on any modern open source operating system, except it doesn't seem to support Kerberos, so you need to log into the database using a database-account (not a Windows-account).
MySQL	Run: mysqlIf you need help on the optional command line options, see the man page.On platforms like Linux and FreeBSD (which have decent readline-capabilities), MySQL's command line interface is simply great; not much else to say. MySQL's command line interface is said to be rather poor on Windows, though.

Databases vs. Schemas⌘

The SQL specification makes clear what an "SQL schema" is; however, different databases implement it incorrectly. To compound this confusion the functionality can, when incorrectly implemented, overlap with that of the parent-database. An SQL schema is simply a namespace within a database, things within this namespace are addressed using the member operator dot ".". This seems to be a universal amongst all of the implementations.

Who cares?⌘

• example problem: former MySQL users may create multiple databases for one project. In this context, MySQL databases are analogous in function to Postgres-schemas, insomuch as Postgres lacks off-the-shelf cross-database functionality that MySQL has. Conversely, PostgreSQL has applied more of the specification implementing cross-table, cross-schema, and then left room for future cross-database functionality.
• MySQL aliases schema with database behind the scenes, such that CREATE SCHEMA and CREATE DATABASE are analogs. It can therefore be said that MySQL has implemented cross-database functionality, skipped schema functionality entirely, and provided similar functionality into their implementation of a database. In summary, Postgres fully supports schemas but lacks some functionality MySQL has with databases, while MySQL does not even attempt to support true schemas

Getting a list of databases⌘

Standard	Not specified. (FYI: The SQL standard doesn't have the concept of adatabase as a container of schemas; instead, the standard specifies that schemas are contained in a catalog.)
MSSQL	EXEC SP_HELPDB
MySQL	SHOW DATABASES

Getting a list of schemas⌘

Standard	SELECT SCHEMA_NAME FROM INFORMATION_SCHEMA.SCHEMATA
MSSQL	Follows the standard.
MySQL	MySQL doesn't support schemas. (but there's a hack in the mysql DB)

Getting a list of tables⌘

Standard	Part 11 of the SQL standard specifies the INFORMATION_SCHEMA schema which must be part of all database catalogues. The schema may be used like this:SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE'or (often more relevant):SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_SCHEMA='SCHEMA-NAME'See a warning about potential case sensitivity problems below.
MSSQL	Follows that standard. Sometimes, the SP_TABLES system stored procedure is easier to use.
MySQL	Follows the standard, except that MySQL doesn't support schemas, so one might say that MySQL's INFORMATION_SCHEMA is really an 'INFORMATION_DATABASE or 'INFORMATION_CATALOGUE.In command-line context, it's easier to use the following non-standard SQL: SHOW TABLES

Getting a table description⌘

Standard	Part 11 of the SQL standard specifies the INFORMATION_SCHEMA schema which must be part of all database catalogues. The schema may be used like this: SELECT column_name,data_type,column_default,is_nullable FROM information_schema.tables AS t JOIN information_schema.columns AS c ON t.table_catalog=c.table_catalog AND t.table_schema=c.table_schema AND t.table_name=c.table_name WHERE t.table_name='TABLE-NAME'�or like this (more verbose):SELECT column_name, data_type, character_maximum_length, numeric_precision, column_default, is_nullable FROM information_schema.tables as t JOIN information_schema.columns AS c ON t.table_catalog=c.table_catalog AND t.table_schema=c.table_schema AND t.table_name=c.table_name WHERE c.table_schema='TABLE-SCHEMA' AND c.table_name='TABLE-NAME'To get information about constraints, involved columns and (possibly) referenced columns, a query like this may be used: SELECT tc.CONSTRAINT_NAME, CONSTRAINT_TYPE, ccu.COLUMN_NAME, rccu.COLUMN_NAME, rccu.TABLE_CATALOG, rccu.TABLE_SCHEMA, rccu.TABLE_NAME, CHECK_CLAUSE FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS tc LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE ccu ON tc.CONSTRAINT_CATALOG=ccu.CONSTRAINT_CATALOG AND tc.CONSTRAINT_SCHEMA=ccu.CONSTRAINT_SCHEMA AND tc.CONSTRAINT_NAME=ccu.CONSTRAINT_NAME AND tc.TABLE_CATALOG=ccu.TABLE_CATALOG AND tc.TABLE_SCHEMA=ccu.TABLE_SCHEMA AND tc.TABLE_NAME=ccu.TABLE_NAME LEFT JOIN INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS rc ON rc.CONSTRAINT_CATALOG=ccu.CONSTRAINT_CATALOG AND rc.CONSTRAINT_SCHEMA=ccu.CONSTRAINT_SCHEMA AND rc.CONSTRAINT_NAME=ccu.CONSTRAINT_NAME LEFT JOIN INFORMATION_SCHEMA.CONSTRAINT_COLUMN_USAGE rccu ON rc.UNIQUE_CONSTRAINT_CATALOG=rccu.CONSTRAINT_CATALOG AND rc.UNIQUE_CONSTRAINT_SCHEMA=rccu.CONSTRAINT_SCHEMA AND rc.UNIQUE_CONSTRAINT_NAME=rccu.CONSTRAINT_NAME LEFT JOIN INFORMATION_SCHEMA.CHECK_CONSTRAINTS cc ON tc.CONSTRAINT_CATALOG=cc.CONSTRAINT_CATALOG AND tc.CONSTRAINT_SCHEMA=cc.CONSTRAINT_SCHEMA AND tc.CONSTRAINT_NAME=cc.CONSTRAINT_NAME WHERE tc.TABLE_CATALOG='CATALOG-NAME' AND -- see remark tc.TABLE_SCHEMA='SCHEMA-NAME' AND -- see remark tc.TABLE_NAME='TABLE-NAME' ORDER BY tc.CONSTRAINT_NAME If you don't care about potential namespace conflicts, you may leave out the lines commented with "-- see remark".
MSSQL	Follows the standard, except that MSSQL uses non-standard names for some standard datatypes, i.e. varchar instead of the standard's CHARACTER_VARYING MSSQL's INFORMATION_SCHEMA doesn't have all SQL:2008's columns (an example: MSSQL's INFORMATION_SCHEMA.COLUMNS view does not contain the IS_IDENTITY column)Often, the SP_HELP tablename system stored procedure is easier to use.
MySQL	Follows the standard, except that MySQL doesn't support schemas, so one might say that MySQL's INFORMATION_SCHEMA is really an 'INFORMATION_DATABASE or 'INFORMATION_CATALOGUE. MySQL's INFORMATION_SCHEMA doesn't have all SQL:2008's columns (an example: MySQL's INFORMATION_SCHEMA.COLUMNS view does not contain the IS_IDENTITY column). As MySQL's namespaces don't match the SQL standard fully, the standard queries mentioned above will not work. The reason is that in MySQL, the value of TABLE_CATALOG isNULL for all tables and columns. To obtain the wanted information, you need to remove the table_catalog join-conditions. I.e., the first (and simplest) of the above queries must be re-written to:SELECT column_name,data_type,column_default,is_nullable FROM information_schema.tables AS t JOIN information_schema.columns AS c ON t.table_schema=c.table_schema AND t.table_name=c.table_name WHERE t.table_name='TABLE-NAME'In command-line context it's easier to use this non-SQL command: DESCRIBE tablename

Of course, there's always

• describe
• show create

Getting a query explanation⌘

Standard	Not standardized.
MSSQL	MSSQL can be put in a query explanation mode where queries are not actually executed, but a query explanation is returned instead: SET SHOWPLAN_TEXT ONThe query explanation mode is turned off by running SET SHOWPLAN_TEXT OFF
MySQL	EXPLAIN <query>

Turning on query timing⌘

Standard	Not standardized.
MSSQL	SET STATISTICS TIME ON
MySQL	MySQL's command line interface prints query times by default.

JDBC⌘

MSSQL	Microsoft's driver: sqljdbc.jar - read more Alternative: The open source JTDS driver: jtds - read more
MySQL	The MySQL Connector/J driver: mysql-connector-java-[version]-bin.jar

Dummy table⌘

Some DBMSes let you perform a query like this:

SELECT 1+1 

answering

2

With other DBMSes, you need to insert a dummy-table expression to obtain the same result:

SELECT 1+1 FROM dummy-table

MSSQL	No need for dummy-table.
MySQL	No need for dummy-table, although MySQL allows you to refer to a DUAL dummy-table (for Oracle compatibility).

Getting Versions⌘

Standard	SELECT CHARACTER_VALUE FROM INFORMATION_SCHEMA.SQL_IMPLEMENTATION_INFO WHERE IMPLEMENTATION_INFO_NAME='DBMS VERSION'
MSSQL	MSSQL's implementation of the IMPLEMENTATION_SCHEMA doesn't seem to include the SQL_IMPLEMENTATION_INFO view. In stead, you may use SELECT SERVERPROPERTY('ProductVersion') (just the version), or SELECT @@VERSION (verbose, harder to parse).
MySQL	MySQL's INFORMATION_SCHEMA doesn't include the SQL_IMPLEMENTATION_INFO view.Work-around: SELECT VERSION()