MySQL: MySQL for starters

In this post I intend to give some pointers to those who are starting with RDBMS (Relational Database Management Systems) usage. I don’t intend to start any flame wars saying that this or that database is better, but simply to explain a few starter topics and point to some handy URLs. I chose MySQL because that’s what I believe people start with – it’s light, fast, has triggers and subselects, and it also has several options of neat front-ends. Keep reading if this has caught your attention.

RDBMs (Relational Database Management Systems)

RDBMs are programs designed to store and provide information in table format. Each system follows main standards but also includes unique features that others implement differently or not at all. They follow the client-server architecture. Each server will listen to a TCP/IP port (or Unix socket, but we’re not going that way here), and a client application (such as Toad for MySQL, Oracle SQL Developer, or phpMyAdmin) will connect to its port to manipulate its data.

The most famous RDBMs today are:

Oracle
MySQL
PostgreSQL
IBM DB2

Follow the links above to go to their respective download pages.

I believe most websites today are using MySQL databases, for its growing improvement with each version, its speed, reliability, and extensive documentation. It will be the focus of this post.

Structured Query Language

If you are looking into using any database in your applications, the least you need to know is some SQL.

Amazon.com has some books you can use for starters:

• Introduction to SQL: Mastering the Structured Query Language (3rd Edition)
• Head First SQL: Your Brain on SQL — A Learner’s Guide (Head First)
• Learning SQL (Learning)

Installing MySQL server on Windows

Installing MySQL server on Windows is a piece of cake. Follow these steps and there’s no getting it wrong:

1. Download the latest stable MySQL server from here (the link opens in a new window). The Community server is the Free (as in speech and as in beer) version. We’ll use the Windows Essentials Installer version.
2. Execute the .msi file, select the Typical install and do the next, next, next mantra. There will be a checkbox asking to “Configure the MySQL Server Now”. Leave it checked. It’s for the next step.
3. The MySQL Server Instance Configuration Wizard will be started. Click next to get started.

Standard port (3306) install

1. If your computer is not using TCP port 3306, then select the Standard Configuration radio button and click next. Read the section Install for non-standard TCP port if you need to use a different port.
2. You can have MySQL run as a Windows service. It’s the recommended selection for modern Windows versions. You can change the Service Name to your liking. Also consider checking the “Include Bin Directory in Windows PATH” checkbox. It will come in handy if you need to use the bundled MySQL command-line programs such as the client, mysqladmin, or mysqldump. Click next.
3. Set your root password to something safe. If you’re only setting up a server for development and testing, or if you’re not behind a firewall (you ARE behind a firewall, right?), then don’t check the option to “Enable root access from remote machines”. Once you fill in your new password and confirmation, you’ll be allowed to click Next.
4. Last step is to click Execute to start the configuration.

Install for non-standard TCP port

1. In the MySQL Server Instance Configuration Wizard select the Detailed Configuration radio button and click next.
2. You will have to select between Developer Machine, Server Machine, or Dedicated MySQL Server Machine. The difference is the amount of memory that will be allocated to the MySQL server. Select the Developer Machine for this tutorial.
3. MySQL allows you to select different engines according to your needs. MyISAM is the first engine that was released to the public. It has no support for transactions. InnoDB is the newer engine that supports transactions and subselects. It is not as fast as the MyISAM engine, but it’s much more powerful. Having said that, select the Multifunctional Database option and click Next.
4. Choose where you want to put your InnoDB drive. The default settings should be fine. Click Next.
5. For develpment and testing servers, the Decision Support (DSS)/OLAP selection in the Concurrent Connection screen should be fine.
6. You now have the chance to select which port you want listening to incomming connections. MySQL listens to port 3306 by default. You can add a firewall exception (again, you ARE behind a firewall, right?) by checking the appropriate box. Leave Enable Strict Mode checked. Click Next.
7. Select the character set most appropriate to your region. Latin1 does the trick in the west.
8. You can have MySQL run as a Windows service. It’s the recommended selection for modern Windows versions. You can change the Service Name to your liking. Also consider checking the “Include Bin Directory in Windows PATH” checkbox. It will come in handy if you need to use the bundled MySQL command-line programs such as the client, mysqladmin, or mysqldump. Click next.
9. Set your root password to something safe. If you’re only setting up a server for development and testing, or if you’re not behind a firewall (you ARE behind a firewall, right?), then don’t check the option to “Enable root access from remote machines”. Once you fill in your new password and confirmation, you’ll be allowed to click Next.
10. Last step is to click Execute to start the configuration.

MySQL clients

As mentioned before, you will need a client to access your database. The server installations provide console clients such as Oracle sql*plus, and mysql command line tool. However, the visual clients are not only easy on the eyes, but they are a good way for you to learn by following the menu items (user management, table/database creation, views, triggers, etc). Now don’t get me wrong – command line manipulation is very important, but this post is for real beginners, right?

Some clients that I recommend:

Toad for MySQL
SQLYog (for Linux users, installing it with Wine is just a case of “next, next, next, finish”, although they do have Linux binaries)
MySQL Administrator and Query Browser

Each client will give you detailed steps on how to install and configure them. Remember your MySQL server port, username, and password. You will need them during the setup phase.

Well, this is all I have to say about installing MySQL right now. In a future post I will discuss LAMP/WAMP, and some MySQL techniques that I find useful.