Fed up with the world of commercial software licensing? Are you looking
for a stable database platform for your organization but sweating at
the hefty price tags attached to mainstream products from Microsoft and
Oracle? MySQL may be for you!
Once the refuge of geeky hackers, MySQL is now a
well-respected product that is more than capable of commercial
operation. In fact, the entire Google search engine is built upon MySQL
technology!
What about features? MySQL actually supports most of the functionality
you’ve come to expect in a commercial RDBMS. It ensures that
transactions comply with the ACID Model,
allows the building of indexes, supports standard data types, and
allows for database replication, among other features. One major area
where MySQL currently falls short is its lack of support for stored
procedures and triggers. However, both of these features are slated for
the next major release – MySQL 5.0.
If you’re a heavy user of web applications, you’ll be pleased
to learn that MySQL meshes perfectly with the Perl Hypertext
Preprocessor (PHP) dynamic web development language. If you’re a
Microsoft IIS user, it works quite well with Active Server Pages (ASP),
as well. In fact, if your ASP code is ANSI SQL-compliant, you may be
able to simply plug and play a MySQL server into your production
environment very easily!
Like the popular Linux operating system, MySQL is released as
open source software under the GNU Public License (GPL). In most cases,
you can download, install and work with MySQL without paying a cent.
There are some exceptions for those who create software built upon
MySQL for resale. If you think you might fit this exception, take a
look at the terms of the MySQL commercial license.
If that’s enough for you to decide that you’d like to give MySQL a try, you can download a copy from MySQL.com. That site is also a great resource for information on the MySQL platform geared toward administrators and developers alike.
Be sure to read the next page of this article for a look at installing MySQL on your system!
So you want to take the plunge and install MySQL on your
system? It's not as difficult as you might think. In fact, if you're
using a popular operating system, you can probably bypass the intricate
compilation process and download a ready-to-install binary file.
The first step in installing MySQL is to make sure that MySQL
supports your operating system. If you're a Microsoft user, that means
any version of Windows later than Windows 95. Users of other operating
systems should consult the Operating Systems Supported by MySQL.
Next, you'll need to choose the version of MySQL that you'd like to
install. As of this writing, the most recent production version is
MySQL 4.1. You'll probably find that there have been several minor
releases that follow the release of 4.1. These will be indicated by
adding a third number to the filename. For example, as of October 31,
2004, the current release is 4.1.7. In general, you should select the
highest numbered release that doesn't have the word "alpha" "beta" or
"gamma" after it. Those keywords indicate pre-release versions of MySQL
that are currently being tested by the open source community.
Then it's time to download the correct file! You can obtain a complete listing of available MySQL downloads
from the MySQL site. Select the file that corresponds to the version
you wish to install and your operating system. Note that unless you
have a specific reason to do otherwise, you should select the
"standard" version. If you're attempting to install MySQL on a
non-supported operating system, you'll need to download the source
files instead. Note that this is not a process suggested for beginners.
In fact, if you aren't familiar with the word "compile," steer clear of
this process.
Once you've downloaded the correct file, you'll need to follow
the installation procedures for your operating system. You can find
common OS instructions using the links below:
That’s all there is to it! In the next page of this article, we take a look at configuring MySQL on a Windows system.
Configuring MySQL for Windows
Now that you’ve installed MySQL, it’s time to perform some basic
administrative and configuration tasks to ensure that your database
environment is ready for production. In this installment of our MySQL
series, we’ll look at the process of configuring MySQL on a Windows
system. Next time, we’ll explore getting up and running in a Linux/Unix
environment.
If you installed MySQL on a Windows system and used the
Windows Installation Wizard, you’re probably already good to go! When
that wizard completed, it most likely launched the MySQL Configuration
Wizard which walked you through the process of configuring the
database. If you didn’t go through the configuration wizard or wish to
repeat the process, you may manually invoke it by navigating to the bin directory of your MySQL installation and issuing the command MySQLInstanceConfig.exe.
When the wizard starts for the first time, it asks you if you’d like to
perform a standard configuration or a detailed configuration. Unless
you have a specific reason to do otherwise, you should start by
selecting the less complex standard configuration process. If your
system will be running more than one instance of MySQL or you have
unusually complex requirements, you should consult the MySQL Documentation and use the detailed configuration process.
The standard configuration process consists of two steps: service
options and security options. You’ll first see a screen asking you if
you’d like to install MySQL as a service. In most cases, you should
select this option. Running the database as a service lets it run in
the background without requiring user interaction. You also probably
want to select the option to launch the MySQL server automatically at
startup. The combination of these two options allows MySQL to restart
automatically upon process or system failure.
The second phase of the standard configuration process allows you to
set two types of security settings. The first is the use of a root
password, which is strongly recommended. This root password controls
access to the most sensitive administration tasks on your server. If
you’re running in an environment where the administrator can access the
system running MySQL, you might also want to check the box that
specifies the root account may only connect from the localhost. This
prevents administrative connections from remote network location. The
second option you’ll select on this screen is whether you’d like to
have an anonymous user account. I recommend that you do not enable this
option unless absolutely necessary to increase the security of your
system.
That’s all there is to configuring a Windows MySQL
installation. On the next page, we take a brief look at the
configuration process on Linux systems and in future articles we’ll
dive into using your new MySQL installation!
Configuring MySQL for Linux
On the previous page of
this article, we took a look at the process used to configure MySQL on
a Windows system. If you're using a Linux system (like the majority of
MySQL users), the process is a bit different. Let's take a look.
You have three main tasks to complete the basic configuration of your Linux MySQL server:
Create the grant tables
Start the server
Verify server function
You first create MySQL's grant tables and fill them with the
default data used to determine access privileges for the database
server. This is accomplished using the mysql_install_db program. First,
you need to locate this file. On most installations, you'll find it in
either the /bin or /scripts directory under the MySQL root. Once you
locate it, run it using the --user=mysql flag.
IMPORTANT NOTE: The initial account settings created by mysql_install_db have no passwords associated with the accounts. After completing this configuration process, you should follow the instructions in the article Securing the Initial MySQL Accounts to secure your server.
Next, it's time to start the server. Simply execute the
mysqld_safe program found in the /bin directory under the MySQL root.
You should also explicitly provide MySQL with the base directory of the
MySQL installation using the --basedir flag. For example, if your base
directory is /usr/local/mysql, you'd start the server using the command
mysqld --basedir=/usr/local/mysql.
You can verify that the server is up and running using the
mysqladmin administrative interface. Simply execute the command
mysqladmin version and you'll get output similar to that shown below:
./mysqladmin Ver 8.40 Distrib 4.0.22, for pc-linux on i686
Copyright (C) 2000 MySQL AB & MySQL Finland AB & TCX DataKonsult AB
This software comes with ABSOLUTELY NO WARRANTY. This is free software,
and you are welcome to modify and redistribute it under the GPL license
Server version 4.0.22
Protocol version 10
Connection Localhost via UNIX socket
UNIX socket /tmp/mysql.sock
Uptime: 45 sec
Threads: 1 Questions: 1 Slow Queries: 1 Opens: 6 Flush Tables: 1 Open Tables: 0 Queries per second avg: 0.022
Once the server is up and running, test it out! Put your SQL
knowledge to work and ensure that you're able to create tables and work
with data. Don't forget that one of your first tasks should be to add
security to the default accounts created during the installation
process.
