In our last episode we found out how to connect to a MySQL server.  This time we learn how to lay out a database and start creating it. For this, and following episodes, we will be looking at creating a database to support an online bookshop.

Creating the database

Using the mysql command line client, you can connect to the server and then create the database.  We need a name for the database, and in this case we'll call it 'bookshop'.   We'll also create a user who is specifically allowed to add and update the database, but not alter its structure:

mysql> CREATE DATABASE `bookshop`;
Query OK, 1 row affected (0.01 sec)

mysql> GRANT INSERT, SELECT, UPDATE, DELETE ON `bookshop`.* to 'bookuser'@'localhost' identified by 'bookpass';
Query OK, 0 rows affected (0.00 sec) …

In our last episode we looked at getting MySQL, today we will be looking at how you connect to a MySQL server and what that implies.

Connection basics

Before a client can connect to a MySQL server it needs a path by which that connection can be made. One method that is almost universal is the 'socket'.  As its name implies it is a way of plugging two (or more) applications together.  Sockets can either be end points for a network connection (for instance a TCP socket) or can use the same system-level functions but use a local connection.  This is sometimes called a UNIX socket, and relies on there being a special file that the two applications can use to initiate a connection.  MySQL can use both.

For a network connection you need a number of pieces of information.  As a connection …

For those that weren’t able to attend the fantastic NoSQL Now Conference in San Jose last week, but are still interested in the slides about how people are doing Ad Hoc analytics on top of NoSQL data systems, here’s my slides from my presentation:

No sql now2011_review_of_adhoc_architectures View more presentations from ngoodman We obviously continue to hear from our community that LucidDB is a great solution sitting in front of a Big Data/NoSQL system. Allowing easy SQL access (including super fast, analytic database cached views) is a big win for reducing …

This is the first in a series of posts on MySQL® for those new to the database, or those migrating from another DBMS.

So you've made the decision to try MySQL.  Now you just have to get it installed. Luckily for most purposes MySQL is quite often already available.  If you have a Linux installation then chances are that both the server and client are installed.  If you are planning on using MySQL for your website, chances are the hosting provider gives you several MySQL databases for your use.

Before diving in too deep though, let's get some background and terminology out of the way.

What is MySQL?

This depends.  MySQL was a company, is a trademark, is an ecosystem and is the name of arguably the most popular relational database management system (RDBMS) on the planet.  Originally developed by MySQL AB, MySQL (both the database and the trademark) are now owned by Oracle Corporation. But …

A friend recently bought a GM car. I proceeded to inform him that I am shorting GM stock (technically a put option). He was shocked. “But they make great cars,” he exclaimed. I responded, “I’m not shorting the cars, I’m shorting the company.” Why am I recounting this exchange? Because I believe that the new wave of NoSQL companies—as opposed to the rebranded ODBMS—presents the same situation. I am long the products, but short the companies.
Let me explain. NoSQL companies have built some very cool products that solve real business problems. The challenge is that they are all open source products serving niche markets. They have customer funnels that are simply too small to sustain the companies given their low conversion/monetization rates.
These companies could certainly be tasty acquisition targets for companies that actually make money. But as standalone companies, sadly, I would short them. On that note, I am off to …

Linuxcon was a celebration of twenty years of Linux. I remember colleagues telling me to try the funny little operating system for PCs instead of a BSD variant, their excitement, and making distro ‘floppies’ for . The time has flown, Linux has become a core infrastructure for IT, and many technologies built upon upon Linux has flourished. But could it be better? At least for the database world, things could be better.

Databases are a ‘full body workout’ for Linux according to Josh Berkus. The CEO of PostgreSQL Experts had a session and a BOF at Linuxcon. The goals of these session was to make Linux developers aware of some of the special problems for databases and to get some hints of what database developers can do to better exploit Linux. Linux is the overwhelming OS choice for both the multi-process databases (PostgreSQL and Oracle) and the …

I will be presenting The Care and Feeding of a MySQL Database for Linux System Administrators at Linuxcon. This is a short talk on what Linux Admins can do to get their MySQL instances performing properly, where to budget your server money, and some other tips to make life easier. It can not turn a good system admin into a good DBA no more than a 40 minute talk on being a Linux Admin will make a DBA a good Linux Admin. But it is a good overview of the subject. And for the sake of the environment, I will be recycling all my old jokes again. So see you Friday, 3:00 in Plaza B in the Hyatt Regency in Vancouver.

…

Speaking of conferences, in general, and OpenSQLCamps in particular, there is one a week from now, and I will be speaking! It is organized as a single room track at Froscon, Germany, by Felix Schupp (Blackray/Softmethod) and Volker Oboda (Primebase). The content is mostly a collection of database related talks originally submitted via the main Froscon call for papers. (In other words, unlike many previous camps, the schedule is all set.)

I'm a little excited about this one, because for the first time in my career as speaker I will be giving the keynote. The title of my talk is

How I learned to use SQL and how I learned not to use it

read more

Percona has announced Percona Live MySQL Conference and Expo 2012. Kudos for their vision and entrepreneurship. I have seen comments praising their commitment to the community and their willingness to filling a void. I have to dot a few i's and cross some t's on this matter.
That was not the only game in town.By the end of June, there were strong clues that O'Reilly was not going to organize a conference. The question of who could fill the void started to pop up. The MySQL Council started exploring the options for a community-driven conference to replace the missing one. The general plan was along the lines of "let's see who is in, and eventually run a conference without the big organizer. If nobody steps up, the IOUG can offer a venue in Las Vegas for an independent MySQL conference". The plan required general …

Dear Kettle users,

Most of you usually use a data integration engine to process data in a batch-oriented way.  Pentaho Data Integration (Kettle) is typically deployed to run monthly, nightly, hourly workloads.  Sometimes folks run micro-batches of work every minute or so.  However, it’s lesser known that our beloved transformation engine can also be used to stream data indefinitely (never ending) from a source to a target.  This sort of data integration is sometimes referred to as being “streaming“, “real-time“, “near real-time“, “continuous” and so on.  Typical examples of situations where you have a never-ending supply of data that needs to be processed the instance it becomes available are JMS (Java Message Service), RDBMS log sniffing, on-line fraud analyses, web or application …