Planet MySQL Planet MySQL: Meta Deutsch Español Français Italiano 日本語 Русский Português 中文
Showing entries 1 to 10 of 11 1 Older Entries

Displaying posts with tag: MySQL 101 (reset)

MySQL 101: Monitor Disk I/O with pt-diskstats
+1 Vote Up -0Vote Down

Here on the Percona Support team we often ask customers to retrieve disk stats to monitor disk IO and to measure block devices iops and latency. There are a number of tools available to monitor IO on Linux. iostat is one of the popular tools and Percona Toolkit, which is free, contains the pt-diskstats tool for this purpose. The pt-diskstats tool is similar to iostat but

  [Read more...]
MySQL 101 - Replication
+1 Vote Up -0Vote Down

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...]
MySQL 101 - More Transactions
+0 Vote Up -0Vote Down

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.

Transactions and Replication

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...]
MySQL 101 - Transactions
+0 Vote Up -0Vote Down

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...]
MySQL 101 - Referential Integrity
+0 Vote Up -0Vote Down

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 »

A word on Storage Engines

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...]
MySQL 101 - Changing data and schema, UPDATE, ALTER
+0 Vote Up -0Vote Down

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 ».

Updating Data

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`,
 `book`.`title`, `book`.`price`
 FROM `author` INNER JOIN `book` ON

  [Read more...]
MySQL 101 - Sorting and Searching: ORDER BY, WHERE, GROUP BY
+1 Vote Up -0Vote Down

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:

SOURCE mysql101_bookshop_20110912.sql;

You can also pass the file to the mysql command line interpreter from the shell:


  [Read more...]
MySQL 101 - Retrieving data: SELECT and JOIN
+0 Vote Up -0Vote Down

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...]
MySQL 101 - Creating your first database
+2 Vote Up -0Vote Down

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


  [Read more...]
MySQL 101 - Connecting to a MySQL server
+0 Vote Up -1Vote Down

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

  [Read more...]
Showing entries 1 to 10 of 11 1 Older Entries

Planet MySQL © 1995, 2014, Oracle Corporation and/or its affiliates   Legal Policies | Your Privacy Rights | Terms of Use

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.