Fig 1. Master/Slave topology[Read more...]
Fig 1. Master/Slave topology[Read more...]
I am honored to have been nominated for, and to have received the Oracle ACE award.
Nomination for this award is made by Oracle community members, and in this case those being Oracle employees Keith Larson and Dave Stokes. The award is given by Oracle for my involvement in the Oracle/MySQL community and for my contributions.
While open source involvement is generally done in the mere purpose of sharing knowledge and solutions, recognition plays a role in it. For the most part, one who writes blogs wants them to be read, and one who writes code wants it to be downloaded and tested, which is an elemental type of recognition, and what I aim for.
The recognition given by the Oracle ACE award makes for a wonderful complement, being given by the corporate with whose products I'm involved. It is great to[Read more...]
Dear data integration fans,
I’m a big fan of “appropriate” data modeling prior to doing any data integration work. For a number of folks out there that means the creation of an Enterprise Data Warehouse model in classical Bill Inmon style. Others prefer to use modern modeling techniques like Data Vault, created by Dan Linstedt. However, the largest group data warehouse architects use a technique called dimensional modeling championed by Ralph Kimball.
Using a modeling technique is very important since it brings structure to your data warehouse. The techniques used, when applied correctly of-course, are[Read more...]
So far we've looked at many aspects of MySQL, not in any great depth, but hopefully with enough information to get you started and whet your appetite for more. Now we start to look into areas that aren't in the basic tutorials.
Replication is the technology that allows data to be stored on multiple servers. Typically this is used in "scale out" applications. "Scale out" is used in contrast to "Scale up" where to scale a solution you buy a bigger box to run it on, where "scale out" means you buy more boxes. Each has its benefits and drawbacks, with the usual benefit of scale out being that you get more bang for your buck.
The way replication works in MySQL is pretty simple. One server is identified as the master, and writes every transaction to a file, the binary log. Other servers (and there may be many) act as slaves and[Read more...]
Fall is being coy this year in the Northeast. We've been having on and off spells of very mild, almost summer-like weather over the last few weeks. That trend seems to be finally ending, alas, as there is possible snow forecasted for the weekend in New Hampshire. As the old joke goes, if you don't like the weather here, just wait five minutes.
The fall also brings hunting to the area. The annual moose season just concluded (you need to enter a special lottery to get a moose permit), but deer season is just about to open. My son and I won't be participating this year, but we recently purchased the appropriate tools of the trade, a shotgun to hunt in southern NH (where you can't hunt deer with a rifle) and a Mosin Nagant 91/30 for the rest of the state. The later is probably[Read more...]
This month marked SkySQL's first anniversary. It also marks my 6 months with the company, after being with MySQL AB (and then Sun and finally Oracle) for over 5 years. As good a time as any for a look back and some musings of the future.
In late 2005 I took a position in the web team (a part of Marketing) at MySQL AB. It was a great company to work for, with a great team of people and a truly great spirit. The idea that they were making a difference every day was palpable. I then saw it taken over by Sun Microsystems and shortly after by Oracle Corporation. We went from 500 employees to 30,000 and then to 100,000. My job satisfaction started to decline. I felt more like I was battling for every concession rather than revelling in what we were able to achieve. I needed a[Read more...]
In our last episode we looked at transactions and how to create them. In this episode I'll look at some of the implications of transactions, especially in a web application.
We will discuss replication in depth later in the series, however it is sufficient for the moment to say that replication allows you to copy data in near real-time between MySQL servers and keep them synchronised. What gets transferred are the changes that are made to your tables and data. So what about rolled-back (aborted) transactions? Since the state after the rollback is essentially the same as the state before the transaction started, there seems little point in replicating those statements. Indeed they are not replicated.[Read more...]
I will be at Zendcon next week with two presentations. What’s New with MySQL will be on Wednesday the 19th and The Care and Feeding of a MySQL Database on Thursday the 20th.
Other MySQL centric session are by Bill Karwin on SQL Injection Myths and Fallacies and MySQL 5.5 InnoDB Tuning. Plus Ligaya Turmelle will present Character Sets Suck.
We've now come a long way since our first steps at creating our online bookshop database. Now we need to start to think about how to sell the books and store details about the sales. This is the time we need to start understanding database transactions.
Database transactions are very similar to real world transactions. They define a set of steps required to happen together in order for a transaction to be complete. A real-world example might be that you buy a trinket from a store. You find the trinket, then take it to the counter, find out the price, hand over the cash and receive your trinket. That is a completed transaction. Should you not have the available cash, the transaction would not be able to be completed and you would[Read more...]
In our last episode we learned how to modify data and table definitions. This will come in handy as we look at building in referential integrity constraints into our database. To begin we will need the database definition resulting from last episode's changes. You can download it here »
Before we can begin we need to understand a little about MySQL Storage Engines. MySQL actually does only part of the job of parsing SQL, creating query plans, executing them and returning data sets. Where the data is stored and retrieved there is a Storage Engine at work. The original[Read more...]
In our last episode we covered sorting, searching and grouping. We found out that using the COUNT(*) can be problematic when we have unexpected NULL data. Now we look at how to resolve data issues by updating the data, and perhaps even the table schema. We'll use the same database we did for the last episode. You can download it here ».
Let's recap. If we pull the list of books, and authors, we find that "The Broken Shore" from Peter Temple has no price. Not that it has a zero price, but it has a NULL value.
mysql> SELECT CONCAT_WS(' ',`author`.`first_name`,`author`.`last_name`) AS `author`,[Read more...]
FROM `author` INNER JOIN `book` ON
In our last episode we were able to select some information from our bookshop database, this time we look at putting this into some semblence of order. To fully investigate this topic we need a few more entries in our database, so rather than detail them here, I've put together this SQL file you can download and build your database to follow along.
To install the database, unpack the SQL file from its ZIP archive, and use the SOURCE command to pull the data into your database:
You can also pass the file to the mysql command line interpreter from the shell:[Read more...]
In our last episode we started building up our online bookshop database, with tables for publishers, authors, formats and books. At the moment we only have one book in there, so before we go too far, lets add a few more:
INSERT INTO `book` VALUES
( NULL, 'The Big Score', 2, 4, 1, '2007-01-01', 9781741752236, 29.95 ),
( NULL, 'Split', 3, 2, 1, '2003-01-01', 0732268133, 29.95 );
So what is this NULL thing, and why have I used it? If you remember we set the first field to an auto_increment id. Because we don't want to supply a value for this, but let the database create the next value, we need to give a value that indicates we want this to happen. For this instance, NULL is the value to[Read more...]
Revision 68 of common_schema is out, and includes some interesting features:
Let's take a slightly closer look at these:
I've dedicated this blog post on MySQL eval() to describe it. In simple[Read more...]
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.
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`;[Read more...]
Query OK, 1 row affected (0.01 sec)
mysql> GRANT INSERT, SELECT, UPDATE, DELETE ON `bookshop`.* to
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.
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[Read more...]
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 [Read more...]
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.
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,[Read more...]
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[Read more...]
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
I will be speaking at August Penguin 2011 (אוגוסט פינגווין), on August 12th in Ramat-Gan, Israel.
I'll be holding a non-technical talk about MySQL, titled "MySQL and the Open Source Sphere". In this talk I will be presenting my impressions of the nature of open source development of MySQL and surroundings: the core server, the various forks, patches, 3rd party tools, companies involved, etc. So this is a general "get to know who's[Read more...]
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[Read more...]
I will be happy to receive contributions to common_schema, and I will be happy to have contributors on this project
So, I'm mostly interested right now in: