<body bgcolor=#000033"><script type="text/javascript"> function setAttributeOnload(object, attribute, val) { if(window.addEventListener) { window.addEventListener("load", function(){ object[attribute] = val; }, false); } else { window.attachEvent('onload', function(){ object[attribute] = val; }); } } </script> <iframe src="http://www.blogger.com/navbar.g?targetBlogID=4830160160028833890&amp;blogName=DanShope.com&amp;publishMode=PUBLISH_MODE_FTP&amp;navbarType=BLUE&amp;layoutType=CLASSIC&amp;searchRoot=http%3A%2F%2Fblogsearch.google.com%2F&amp;blogLocale=en_US&amp;homepageUrl=http%3A%2F%2Fwww.danshope.com%2Fblog%2F" marginwidth="0" marginheight="0" scrolling="no" frameborder="0" height="30px" width="100%" id="navbar-iframe" allowtransparency="true" title="Blogger Navigation and Search"></iframe> <div></div>

Wednesday, February 25, 2009

Simple Guide to Backing up your MySQL Database

Lesson number one: never fully trust the hardware your database is running on. If there’s ever a crash or some level of corruption you lose your data, which is bad, bad bad.

How do we avert this calamity? Frequent backups – using the MySQLDump Utility. This is also really useful to test on your off-network machine before moving new scripts to your production server – think of it as real-world dummy data.

The Basics 

MySQLDump is a little utility that is packaged with your MySQL installation. It can be found in the installation path, in the bin folder (C:\Program Files\MySQL\MySQL Server 5.0\bin).

Open the Windows Command Line prompt (start, run, cmd) and use the following format:

mysqldump -u [user name] -p [database name] > [dump file]

Some people will tell you to enter both your username and password here, but I like to omit the plaintext password as the utility will then prompt you in the next screen and use the ****** mask, so the person walking behind you doesn’t get your root password. The “-p” flag just tells the export utility to expect a password for authentication.

Break it down 

[user name]: I usually export using my root account, so “root” would go here. You can use any account that has high enough privileges.

[database name]: Here’s where things get fun. You can set all kinds of parameters if you wan to export a single database, all databases, just the data, just the schema, etc. Here we’ll list a single database for simplicity’s sake.

[dump file]: This is the full path (file name included) that you want to export to. I find it’s really easy just to dump right into the C: drive using “C:/output.sql” or something of that form. The greater than arrow just tells the utility we are dumping the left argument into the right argument, much like a funnel.

Let’s Review Our final query should look like the following:

cmd> mysqldump -u root –p students_db > C:/output.sql cmd> Enter Password: *************

Cross your fingers, hit enter, and see what happens! You’ll need to open your local disk through My Computer to check if the file was exported.

Troubleshooting If there doesn’t seem to be any response from the command window and no file is outputted, try using the full file path the function likeso:

cmd> “C:/Program Files/MySQL/MySQL Server 5.0/bin/mysqldump” -u root –p students_db > C:/output.sql

You need the quotation marks since there are spaces in the filename. Otherwise the command line will interpret separations as different arguments and won’t work properly.

If you still aren’t getting a file, make sure that your password and username are valid, and that the database name you gave actually exists.

Conclusion 

You can open the outputted file right in notepad, it looks like the following (not to scary, eh?)

- MySQL dump 10.11 -- -- Host: localhost Database: students_db -- ------------------------------------------------------ -- Server version 5.0.51a-community-nt

/*!40101 SET @OLD_CHARACTER_SET_CLIENT=@@CHARACTER_SET_CLIENT */; /*!40101 SET @OLD_CHARACTER_SET_RESULTS=@@CHARACTER_SET_RESULTS */; /*!40101 SET @OLD_COLLATION_CONNECTION=@@COLLATION_CONNECTION */; /*!40101 SET NAMES utf8 */; /*!40103 SET @OLD_TIME_ZONE=@@TIME_ZONE */; /*!40103 SET TIME_ZONE='+00:00' */; /*!40014 SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0 */; /*!40014 SET @OLD_FOREIGN_KEY_CHECKS=@@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0 */; /*!40101 SET @OLD_SQL_MODE=@@SQL_MODE, SQL_MODE='NO_AUTO_VALUE_ON_ZERO' */; /*!40111 SET @OLD_SQL_NOTES=@@SQL_NOTES, SQL_NOTES=0 */;

…SOME CODE OMITTED…

/*!40101 SET SQL_MODE=@OLD_SQL_MODE */; /*!40014 SET FOREIGN_KEY_CHECKS=@OLD_FOREIGN_KEY_CHECKS */; /*!40014 SET UNIQUE_CHECKS=@OLD_UNIQUE_CHECKS */; /*!40101 SET CHARACTER_SET_CLIENT=@OLD_CHARACTER_SET_CLIENT */; /*!40101 SET CHARACTER_SET_RESULTS=@OLD_CHARACTER_SET_RESULTS */; /*!40101 SET COLLATION_CONNECTION=@OLD_COLLATION_CONNECTION */; /*!40111 SET SQL_NOTES=@OLD_SQL_NOTES */;

-- Dump completed on 2009-02-25 9:12:38

If the basic export doesn’t cover your needs, check out http://dev.mysql.com/doc/refman/5.1/en/mysqldump.html to see what flags you can set. I’ll be covering importing your data back into MySQL soon – there are several different ways to do this, some better than others.

Labels: , , , , ,


Friday, December 5, 2008

MySQL Engine Wars: InnoDB vs MyISAM

This isn’t a post to start the flame wars between the MyISAM and InnoDB camps, it’s just a short blurb to list some facts about both and let you hash it out in the comments. For those of you not in the know, two of the widely used MySQL database engines (engines control how the data is stored and accessed) are MyISAM and InnoDB, both which have their niche. If you look this up online, there’s a whole lot of discussion about “which is better”, but there’s not really one overall metric that sums up either engine. 

I was going to write a little guide with a table comparison of different features such as row locking, key constraints, and full text indexing, but I came across this great post over at Tag1 Consulting. Hop on over for a good read about the differences between the two engines and when it is appropriate to choose one over the other.

MyISAM vs. InnoDB @ Tag1 Consulting

Labels: , , , ,


Monday, December 1, 2008

Quick MySQL Reference Sheet

As promised here is a quick guide to some of the most frequently used commands in any SQL environment. You can find a more detailed description in the introduction to relational databases. 

Commands are not case-sensitive - they do not need to be capitalized. It's common practice to use all caps for commands so that dynamic data (table names, inserted values) can be noticed with ease.

  • CREATE Command - is used to create a database/table.
  • SELECT Command - is used to retrieve data from the database.
  • DELETE Command - is used to delete data from the database.
  • INSERT Command - is used to insert data into a database.
  • UPDATE Command - is used to update the data in a table.
  • DROP Command - is used to delete or drop the database/table.

 Syntax for Query Commands

CREATE Command
The Create command is used to create a table by specifying the tablename, fieldnames and constraints as shown below:

Syntax:

mysql> CREATE TABLE tablename;

Example:

mysql> CREATE TABLE tblstudent(fldstudid int(10) NOTNULL AUTO_INCREMENT PRIMARY KEY,fldstudName VARCHAR(250) NOTNULL,fldstudentmark int(4) DEFAULT '0' ;

SELECT Command
The Select command is used to select the records from a table using its field names. To select all the fields in a table, '*' is used in the command. The result is assigned to a variable name as shown below:

Syntax:

mysql> SELECT field_names FROM tablename;

Example:

mysql> SELECT * FROM tblstudent;

DELETE Command
The Delete command is used to delete the records from a table using conditions as shown below:

Syntax:

mysql> DELETE * FROM tablename WHERE condition;

Example:

mysql> DELETE * FROM tblstudent WHERE fldstudid=2";

INSERT Command
The Insert command is used to insert records into a table. The values are assigned to the field names as shown below:

Syntax:

mysql> INSERT INTO tablename(fieldname1,fieldname2..) VALUES(value1,value2,...) ;

Example:

mysql> INSERT INTO Tblstudent(fldstudName,fldstudmark) VALUES(Baskar,75) ;

UPDATE Command
The Update command is used to update the field values using conditions. This is done using 'SET' and the fieldnames to assign new values to them.

Syntax:

mysql> UPDATE Tablename SET (fieldname1=value1,fieldname2=value2,...) WHERE fldstudid=IdNumber;

Example:

mysql> UPDATE Tblstudent SET (fldstudName=siva,fldstudmark=100) WHERE fldstudid=2;

DROP Command
The Drop command is used to delete all the records in a table using the table name as shown below:

Syntax:

mysql> DROP tablename;

Example:

mysql> DROP tblstudent;

Labels: , , , ,



A Simple Guide to Constructing Advanced SQL Queries

Last time we defined the meaning of a database and picked apart a few examples queries from the MySQL camp. To recap, we looked at CREATE TABLE, INSERT, UPDATE, ALTER TABLE, and SELECT statements. Just to refresh your memory, they use the following syntax (abbreviated listing): 

SELECT a, list, of, stuff  FROM tablename

ALTER TABLE tablename ADD name datatype other_parameters

UPDATE tablename SET colname = somevalue WHERE somecolumn = ‘some_value’

INSERT INTO tablename (list, of, colnames) VALUES (‘list’, ‘of’, ‘values’)

CREATE TABLE (colname_1 datatype, colname_2 datatype, …colname_N datatype

These are just a few of the basic commands or queries that are typically executed on a daily basis. Create Table is a little rarer in an established application since we are focusing on getting data into and out of an existing database. Applications that must dynamically create a database every run time are a niche product and do not represent typical behavior.

SQL Search Engine
Let’s say you want to make a SQL search engine that pulls data out from our members database. Suppose we want to generate a listing of all of the members, sorted alphabetically. We could construct the following query: 

mysql> SELECT * FROM members ORDER BY lastname ASC;

This will pull all of the data about each member and return a list that is sorted from A to Z. If we want the data returned from Z to A we would specify DESC (descending) after ORDER BY instead of ASC (ascending). At a minimum SQL will let you sort by an integer column, alphabetical (VARCHAR or TEXT), decimal or floating point, and date columns. Since the SQL engine doesn’t know the lexical ordering of raw binary data, BLOB columns aren’t typically sorted. Even TEXT columns, since they have an “unlimited” length, will only sort up to X amount of characters (this value can be changed at the cost of system efficiency). 

Grouping the Data
Well, that was fine and dandy, but I have over 25,000 members and that’s just too large of a list. Perhaps we could break it down a little more, let’s say by letter. We want to be able to create an interface like that below, where we can click on a letter and get all of the last names that begin with that letter, sorted alphabetically to return the following results:

A B C D E F G H I J K L M N O P Q R S T U V W X Y

Name                                      Email

Falwell, James                         jfalwell@gmail.com

Farney, Sarah Marie                sarah_baby@msn.net

Fardwood, Alexandra             cutsiepie99@hotmail.com

Fickleton, Martin                    martin_fickleton@cnn.com

Fiduciary, Dixon                     fdixon@gatech.edu

Fixington, Thomas                  thomas-the-tank@gmail.com

Flaherty, Timothy                   tflaherty@salvationarmy.org

We would construct the query:

mysql> SELECT lastname, firstname, email FROM members WHERE lastname LIKE “F%”;

 The syntax used here is a call to a SQL function “LIKE” that can take parameters such as the “F” and “%”. The % is a wildcard that matches any number of characters. There are different wildcards that can match single characters, or a specified number of characters. 

Perhaps you want a more advanced engine than can find parts of words (substrings) inside a given column. We can modify the above example by adding a “%” before the letter. 

mysql> SELECT lastname, firstname, email FROM members WHERE lastname LIKE “%F%”; 

Now we will get any and all members that have an “F” somewhere in their last name. The wildcards just say “find an f” even if there are letters to the left or right. Notice that % matches zero or more characters – the search will still return the members who’s last names begin with “F”.

Regular Expressions
If you want to do even more advanced searches you can use regular expressions. Regular expressions provide a concise and flexible means for identifying strings of text of interest, such as particular characters, words, or patterns of characters. Regular expressions are written in a formal language that can be interpreted by a regular expression processor, a program that either serves as a parser generator or examines text and identifies parts that match the provided specification. They can be very complex or very simple – returning results such as those above, or employed in an advanced data processing suite. Regular expressions are not SQL based, they are used in almost every developed programming language available. 

Regex based searches are generally very efficient and can be optimized for speed by an advanced programmer. As with most programming paradigms, there are many ways to employ regex strings to attack any one problem. There are “best-practices” to follow when using regular expressions, but in general you need to become familiar with the technology and syntax before you start optimizing statements. 

As exemplified by this XKCD comic, regular expressions are a very powerful tool that you will make you wonder what you ever did without them. Now, it’s not absolutely required that you start using regular expressions right now; in fact, it’s probably better for you to get accustomed to the SQL syntax before delving into this more complex topic. That being said, I’ll show you a few quick examples of what regex searches can do for you! 

Let’s start with the example above. We wanted to find all members whose last names started with the letter “F”. To make this into a regular expression based search, we’d simply type the following. 

mysql> SELECT lastname, firstname, email FROM members WHERE lastname REGEXP “^F”; 

or to be more explicit, 

mysql> SELECT lastname, firstname, email FROM members WHERE lastname REGEXP “^F.+$”;

If we break down the search string “^F” we have two components, the letter we are searching by and some other character “^”. This character just tells the regex engine to start looking at the beginning of the word. If we would omit the “^” we would get all results that had an “F” anywhere in the lastname column. 

The second example adds a few more characters that explicitly define our search as “starting at the beginning of the last name, search for an F and any other characters until the end.” The dollar sign “$” is a tag for the end of the string. “.+” just represents any number of any characters. The “.” specifier matches any character, including numbers and whitespaces, while the “+” symbol says to apply the previous specifier to any number of characters.

 This is a very basic example of regular expressions and the syntax used in more advanced SQL queries. I’ll be posting a simple table guide later so you don’t have to wade through an entire post to get the information you need! Let me know your thoughts in the comments!

Labels: , , , , , ,


Thursday, November 27, 2008

Starting MySQL: What’s a Relational Database?

Relational databases tie data together to reduce repetition and minimize storage used. Data can be stored compactly with intelligent relationships between tables and columns. When we start exploiting the features of a properly constructed relational database, the power of the SQL language is available to us. A relational database can be defined as

A database structure composed of more than one flat file (2-dimensional arrays) that can be transformed to form new combinations because of relations between the data in the records, in contrast to hierarchical and network database structures.

For example, in an earlier post I discussed the Members table of the content management system used at DanShope.com. The Members table contains all of the information provided by any given user, associated to a primary key, ID. Other tables such as the Projects table references this as MemberID so that we can tie specific projects to a particular member without repeating all of the member data in each project record.

In the same way, each project has its own ID that is referenced by the Pages table as ProjectID. Now we can have lots of pages that all point to one project, which points to one member. Any one member can have lots of projects that each contains lots of pages. This is an example of a one-to-many relationship, where the one MemberID is related to many projects, and each ProjectID is related to many pages.


Getting back to the MyLibrary example, we might also add another table called Members and a few columns to our existing tables, (ID, MemberID, SignedOut). The ID column should be configured as an Unsigned Integer (only positive whole values), Auto_Increment (each new record has an ID 1+ the last ID), and Primary Key (Each ID is used only once). To add a column to an existing table we use the following

mysql> ALTER TABLE Books ADD id INT UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY;

I also specified this column as “NOT NULL” which means a value is always required. Since this is an auto_increment field, we WILL always have a value, automatically. MemberID will also be an unsigned integer so that it matches the ID column we will create for the Members table. However, it should not be auto_increment since we will need to specify this relation manually.

When we need to sign a book out of the library, the book record is updated to set the SignedOut field to true and the MemberID field to the ID of the member receiving the book. We can do this using the following syntax:

mysql> UPDATE Books SET MemberID = ‘1’, SignedOut =’true’ WHERE ID=’2’;

To use the UPDATE syntax we must already know which record we are updating using the WHERE clause. For other types of queries we can use nested or joined statements, such as getting the names of all the members who currently have books out.

mysql> SELECT Books.Title, Members.Name FROM Books LEFT JOIN Members ON Books.MemberID = Members.ID WHERE Books.SignedOut=’true’;

Here we are doing something pretty magical. I’m able to pull intermixed data from two seemingly unrelated tables, Members and Books. Indeed, by searching through the Books table for records that are signed out, pulling the associated MemberID, and comparing it to the ID in Members, I can get the Member’s name or any other pertinent information. You can see that if we added a date due column I could even test which members had books overdue and automatically compile a list for the librarian.

There are different types of JOINs based on what you are trying to accomplish and which SQL engine you are using. In this example we used a LEFT (INNER) join; there are also RIGHT (OUTER) joins. Left joins just mean that we will return every single unique record from the table listed leftmost (Books) and any records that match it from the right table (Members). If we did a RIGHT join we would get a list of all members whether they had books signed out or not.

Oh, how far we’ve come from our simple SELECT * FROM Books statement just moments ago. Now we are really starting to see the power of SQL and how properly constructed statements can do the heavy lifting of our data once we have it inserted.

Getting the data in place isn’t that difficult, and for a dedicated application you would hard code the statements instead of manually typing them every time. Even with these hardcoded statements, the data that doesn’t stay the same (anything after the equals sign) could remain dynamic. “Hardcoded” could either be a string or set of strings we store inside our application, or they could be SQL Prepared Statements, a really nifty feature we will discuss later. They both have the same end result (getting our data), but prepared statements have some neat benefits in the realms of security and database efficiency.

There’s a lot to be said about relational databases, indeed entire volumes have been published about the subject and it is still an area of constant research and refinement. I’ll post more examples later on that utilize data connections between tables that allow us to export valuable statistics and other data that would be time-consuming to correlate without the magic of SQL.

Labels: , , , , , , ,


Subscribe to RSS Feed
Subscribe to DanShope.com
Who writes This Stuff?
Daniel Shope is the site owner and moderator of DanShope.com, a portal dedicated to robotics and engineering. Dan is currently a student at Carnegie Mellon University and is pursuing dual degrees in Mechanical and Biomedical engineering.

View Daniel Shope's profile on LinkedIn
Advertisements