MySQL: Introduction
MySQL database is so popular that it hardly needs any introduction! MySQL is a free open-source relational-database management system (RDBMS) and is a very popular choice for web-based applications. MySQL also has a few paid-versions that offer additional functionality.
Basics
This module presents several aspects of using a MySQL database: SQL queries, both basic and advanced, transactions, locking, trigger, and managing user-accounts. This page provides an overview of these concepts; later pages revisit these concepts in additional detail.
SQL stands for Structured Query Language and is a standard language for interacting with not only MySQL databases but also with other databases like Oracle, PostgreSQL, Microsoft ACCESS etc. An SQL query is a statement that instructs a database to process a request. This module presents several SQL queries along with MySQL-based examples. Since SQL is standardized, the queries presented here would apply equally to other databases as well.
SQL provides queries to view an existing record, can add new records, or can update/delete existing records. SQL also provides two types of advanced queries. The first set consists of queries that operate on multiple tables using JOIN and UNION queries. The second set of queries depend upon yet another query for its input; such queries are also known as nested queries.
Next, this module discusses three important SQL features: triggers, transactions, and locking. Triggers allow us to run a pre-configured query (or queries) upon occurrence of an event. Transactions/locking allow us to do a set of queries in an atomic fashion.
Lastly, one of the key roles of managing a database is to manage user accounts and their privileges. Managing user accounts is crucial since it is a more secure design if we do not expose the default (and all capable!) root account to the outside world. Instead, we can minimize the risk by creating a user (or a set of users) with just the required amount of access as permitted by the design. This module discusses both of these aspects: adding/removing users and hardening of database design by restricting user access to as minimum as possible.
For the sake of consistency, we use a fictitious database to support a company that sells Toys and we call it "Blue Sky Toys". The database manages a chain of stores distributed across various locations and the database keeps a record of employees, locations, items, and inventory. We use examples from this database throughput all the pages in this (MySQL) module.
Installing MySQL
Fedora (Linux)
For Fedora, the installation is quite simple; we can use yum to install "mysql" package which is the client package that contains MySQL client. To run the server as well, we need to install "mysql-server" package as well. We can do both using: "yum install mysql mysql-server". MySQL client is a simple command line based interface (also known as MySQL monitor) to access a MySQL database server.
Windows
For installation on Windows, we can get the software from "http://dev.mysql.com/downloads/". We might have to register before being able to download the version. From the list, we can select the right executable for downloading, and once downloaded, we can double-click it to install it.
Once downloaded, we should follow the installation instructions. For a standard installation, when prompted, we can and select "Typical" for installation choice. Towards the end of installation, "MySQL Server Instance Configuration" wizard would appears. With this wizard, we can choose the "Standard Configuration" option if this is the first time, we are installing MySQL. Once installed, you can use "MySQL 5.5 Command Line Client" from Windows -> All Programs -> MySQL.
Mac OSX
For Mac OSX, we can use the LAMP package and that would contain both MySQL monitor as well the MySQL database server.
Post-Installation Steps
The first thing to do once we have installed MySQL server is to configure password for MySQL root user. This is an important step since by default the root user has no password and this can be a security risk! Therefore, we should configure password for root as soon as we install the MySQL server.
We can do this using mysqladmin utility; this is available as part of the standard MySQL installation. Here is how we can configure root password using mysqladmin: "mysqladmin -u root password 'root_password'".
Once the password is set, we can login using the MySQL monitor (mysql command): "mysql -u root -p"; when prompted, we need to type the password. Here are the steps:
[user@codingbison ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 8 Server version: 5.1.58-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql>
In the above mysql command, we have provided two options "-u" and "-p"; these are short for "--user" and "--password" options.
The mysql command takes additional options as well. We can pass the host on which the MySQL server is running using "--host" option. Thus, if we were to connect to a host name xyz, then we can use "mysql --user root --host xyz -p" or "mysql -u root -h xyz -p" for short. We can also pass "--port" (or -p for short) to specify the port number on which database server is running. Thus, if we were to connect to the database running on port 3306, we can use "mysql -u root --port 3306 -p" or " mysql -u root -P 3306 -p" for short.
Getting Started
Let us say that we have a MySQL database running on the localhost, then we can connect to the MySQL server using the MySQL monitor (mysql command). To run the database, we need to start the MySQL service. On Linux, we can use "/etc/init.d/mysqld start" to start MySQL service; for Fedora Linux, the same can be achieved using "service mysqld start".
Once the server is started, let us connect to the server as a root and issue some MySQL statements or for that matter, SQL queries!
[user@codingbison ~]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 10 Server version: 5.1.58-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | mysql | | test | +--------------------+ 3 rows in set (0.04 sec) mysql> mysql> USE mysql; Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed mysql> SHOW TABLES; +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 23 rows in set (0.00 sec) mysql>
In the above output, "SHOW DATABASES" show all the available databases on the database server. "USE" command allows us to use a given database -- once we issue this statement, all commands are executed in the context of the selected databases. Lastly, we use "SHOW TABLES" to see all the tables available in the selected database. For our case, the three databases, information_schema, mysql, and test are available by default.
The above "mysql -u root -p" could also fail. Here are some of the reasons.
The first reason for error could be that the mysqld (MySQL daemon) may not be running; we did start the mysqld server above running mysqld, but, if we did not then, we would run into this error. When server is not running, then we would get an error of type, "ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/lib/mysql/mysql.sock' (2)". On Linux, the command "/etc/init.d/mysqld status" ("service mysqld status" for Fedora/Red Hat) should tell us if the mysqld is running or not.
The second reason for error could be if we are passing an incorrect credentials when using password. If we were to pass incorrect combination of username and password, then the error would be "ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)".
Yet another reason for error could be if we pass an incorrect hostname to mysql command, let us say "mysql -u root -p --host localhost1234". Assuming that there is no host with name localhost1234, then we get a timeout error as "ERROR 2003 (HY000): Can't connect to MySQL server on 'localhost1234' (110)".
For MySQL monitor, we can either type the queries one at a time or we can also put these queries in a simple text file and use the "SOURCE" command to do the same. Here is a simple example, where we store the above MySQL statements into a text file "sqlinput.sql" and then SOURCE it later.:
[user@codingbison]$ cat sqlinput.sql SHOW DATABASES; USE mysql; SHOW TABLES; [user@codingbison]$ [user@codingbison]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 6 Server version: 5.1.58-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SOURCE sqlinput.sql +--------------------+ | Database | +--------------------+ | information_schema | | blue_sky_toys | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec) Reading table information for completion of table and column names You can turn off this feature to get a quicker startup with -A Database changed +---------------------------+ | Tables_in_mysql | +---------------------------+ | columns_priv | | db | | event | | func | | general_log | | help_category | | help_keyword | | help_relation | | help_topic | | host | | ndb_binlog_index | | plugin | | proc | | procs_priv | | servers | | slow_log | | tables_priv | | time_zone | | time_zone_leap_second | | time_zone_name | | time_zone_transition | | time_zone_transition_type | | user | +---------------------------+ 23 rows in set (0.00 sec) mysql>
The SOURCE command is very handy when we have a lot of queries that you would like to execute or if we would like to execute the same set of queries several times. Also, when we have to restore the backup, then we can execute SOURCE and pass the backup file.
For Windows, we can use "MySQL 5.5 Command Line Client" from Windows -> All Programs -> MySQL to run MySQL commands. Thus, the client interface is similar to the MySQL monitor.
Figure: MySQL client for Windows
For a detailed description of mysql, we recommend doing "man mysql" on console or doing a Google search for "man mysql".
Example Database
For the sake of consistency, our SQL queries use example of a fictitious company in all our examples. This company sells toys and has stores in different cities. We call this company, Blue Sky Toys. This example database manages a chain of stores distributed across various locations and it keeps a record of employees, locations, items, and their inventory.
For record-keeping, the database has four tables: (a) employees to store information about employees, (b) stores to store (pun intended!) information about various store locations, (c) items to store information about toys being sold, and (d) inventory to store quantities of items available at various stores.
The employees table has 5 columns: employee_id, employee_name, date_of_birth, store_id, and employee_address. The stores table has 2 columns: store_id and store_location. The items table has 4 columns: item_code, item_name, item_type, and item_price. Lastly, the inventory table has 3 columns: store_id, item_code, and item_quantity.
Let us use MySQL monitor to look at the database and the tables.
[user@codingbison]$ mysql -u root -p Enter password: Welcome to the MySQL monitor. Commands end with ; or \g. Your MySQL connection id is 5 Server version: 5.1.58-log Source distribution Copyright (c) 2000, 2010, Oracle and/or its affiliates. All rights reserved. This software comes with ABSOLUTELY NO WARRANTY. This is free software, and you are welcome to modify and redistribute it under the GPL v2 license Type 'help;' or '\h' for help. Type '\c' to clear the current input statement. mysql> SHOW DATABASES; +--------------------+ | Database | +--------------------+ | information_schema | | blue_sky_toys | | mysql | | test | +--------------------+ 4 rows in set (0.00 sec) mysql> USE blue_sky_toys; Database changed mysql> mysql> SHOW TABLES; +-------------------------+ | Tables_in_blue_sky_toys | +-------------------------+ | employees | | inventory | | items | | stores | +-------------------------+ 4 rows in set (0.00 sec)