RDS for MySQL on AWS allows you to restore to any point in time for your backup retention period, minus the last 5 minutes or so. Restoration creates a new instance, it does not overwrite whatever instance you’re restoring. AWS’s use of the word restore is a bit confusing because restore often means “take your production database server and overwrite it with data from a backup”. As far as I can tell, Amazon never means this. When you restore, AWS creates another database server and writes all the data to the new instance, both when you’re using restoring to a point-in-time or from a DB snapshot. If you needed to switch servers, you’d have to point your database to the new instance.

Reference

UPDATE: Several people have commented that decimal(10,2) is not correct for money, since sometimes currencies go out to more than 2 decimal places. Others claimed that storing cents (or base unit) as integers make it simpler to perform calculations (thanks, Kevin Farley for your comment). Regardless of what you choose – don’t use floats for money. If you do use integers, I would include the base unit in the name to avoid confusion (AmountInCents).

Data types make all the difference in the world when you’re designing your database. The choices you make now will affect the quality of your data, as well as application performance. I’m going to focus on one issue in this article: why you should always use decimals to represent money. Let’s jump in and see why that’s true.

An example of floats gone wrong

Let’s use a really, really simplified accounting ledger. It’s just three fields, an entry id, …

I work for a firm that’s heavily invested in SQL – a team that needs to have developers who know their way around relational databases and MySQL in particular. I want to show you how I run interviews for our development positions.

Method

Everybody has their own methods and opinions on how to conduct technical interviews. I’ve found that I generally dislike interviews that focus either on whiteboard puzzles or obscure technical details, since they don’t really show how well the candidate is at what really matters: building functioning, quality apps. I really like running the interview like we’re talking about the design for a new product. I want to figure out the requirements, mull over the data model, and write some simple queries to make sure we can show the data we need to.

This process should show two things: the candidate has a good enough grip on the MySQL database that they can comfortably build a system …

I love it when software gives you elegant ways of solving your problem. Programming language designers make me feel like they care when they take the time to include succinct, powerful expressions. I’ve recently discovered some in new things in MySQL, as well as a few rediscoveries. This is the first five, and I’ll cover the next five in another article.

In

You’ve probably used the standard In operator before:

Select 'Oh yeah!' From dual Where 1 In (1,2,3);

As a side note, the dual table is just a dummy table that always returns one row. It’s useful for demonstrating language features or running experiments.

You can also use a subquery with In:

Select 1 From dual Where 1 In (Select 1);

The thing I discovered was that it’s not just scalar values: it’s actually comparing rows, so you can see if a row is present:

Select 1 From dual Where (1,2) In (Select 1,2); …

I want to teach you the difference between an inner and an outer join. We first need to think about what a join is. Simply, it’s when you combine two tables to make a new one. You’re not physically creating a new table when you join them together, but for the purposes of the query, you are creating a new virtual table. Every row now has the columns from both tables. So if TableA has columns Col1 and Col2 and TableB has columns Col3 and Col4, when you join these two tables, you’ll get Col1, Col2, Col3, and Col4. Just as with any query, you have the option of including all columns or excluding some, as well as filtering out rows.

Inner join. A join is combining the rows from two tables. An inner join attempts to match up the two tables based on the criteria you specify in the query, and only returns the rows that match. If a row from the first table in the join matches two rows in the second table, then two rows will be …

Unless you’ve been living under a rock, you’ve probably heard about driverless cars. Google and the large car makers have been creating cars that doen’t need a human driver. This is tremendous news. Over 30,000 people die in car crashes every year in the US. While not all of those could be prevented by removing the meatbag driver, a lot of them could. Driverless cars are the right way forward and I can’t wait until they come. Every time I see a car wreck or hear about a drunk driver, I wish we had this tech right now.

That said, our teens shouldn’t stop learning to drive defensively, our cops …

MySQL has default behavior that’s just plain wrong. I’ve covered some quirks with MySQL not null behavior and offered up using the SQL Mode strict_all_tables as a solution.

As a review, SQL Mode changes the way MySQL executes SQL statements and it’s often used to make MySQL behave. There are several switches that I would turn on by default, like only_full_group_by. You can change settings for yourself (just your connection) or for everybody (change it at the server level).

There’s another SQL Mode option, strict_trans_tables, that’s similar in intent but not in behavior to strict_all_tables. Both strict_all_tables and strict_trans_tables are meant to prevent invalid values from being inserted into your columns (such as preventing null values …

Databases store information, right? Well, what if they could store information about your information so you could query it? Good news! Most database engines such as MySQL implement Information Schema, or a set of views that describe your tables and columns. If you’ve ever used a language like Java or C#, you might be familiar with reflection: the ability to read an object’s metadata. This enables you to do meta-programming, or writing logic about the program itself.

The idea of Information Schema is like reflection: they are views in your database that you can use in your programs or scripts when you need to know what the schema looks like or check the health of the instance. Unlike Show Processlist or Desc Table commands, the results are in tabular format.

I’m going to tour through six important views, and I’ll point you to some others at the end. Let’s get started.

Information_Schema.Tables

Let’s …

One of the new features of MySQL 5.6 is the optimizer trace. What does it do? Well, you know Explain, right? Just add Explain before a query and MySQL will print out how it would execute the query. The optimizer trace does that, except it gives you a little more background into to how it makes its decisions. Think of it as “It’s a Wonderful Life” for indexes – it shows you what the world would be like without that primary key or index.

If you ask, why did MySQL choose to run a full table scan vs. an index seek, the optimizer trace can help you get a better picture of the situation. The other big deal with the optimizer trace is that it’ll show you the relative costs of operations. Yeah, MySQL already tells you how many rows it’ll have to read, but now you know how much those cost given the access path (index seek, scan, etc.)

Example

Let’s jump in to see what it looks like. First, enable the optimizer trace …

One of the first tasks when setting up a new MySQL server is creating users and granting them permissions, or giving them the ability to do stuff in MySQL. By default, a single user, the root user, is created when you setup MySQL. This user is granted all privileges on the entire system, which means you should create separate logins for administrators and applications. Let’s walk through all the basic steps you need to create a new user and give them permissions.

Create a User

The first command I’ll show you is pretty simple:

Create User bob@'%' Identified By 'Astr0ngPhr@$e';

Before we move on, let’s check that user’s permissions:

Show Grants for bob@'%';

Which should show something like:

+----------------------------------------------------------------------------------------------------+
| Grants for bob@% …