The MySQL database is one of the world's most popular open source databases because of its consistent fast performance, high reliability and ease of use. It's used by individual Web developers as well as many of the world's largest and fastest-growing organizations to save time and money powering their high-volume Web sites, business-critical systems and packaged software.

If did not yet have the chance to run your own copy of MySQL, then this article provides to you a step-by-step guide how to install and master your first steps with this database engine.

1. About MySQL

MySQL is the one of the world's most popular open source databases. Beside other powerful solutions like PostgreSQL it's become the database of choice for a new generation of applications built on the LAMP stack (Linux, Apache, MySQL, PHP / Perl / Python). MySQL runs on a large variety of platforms including Linux, Windows, Mac OS, Solaris, HP-UX and IBM AIX.

To receive a list of technical features refer to the home page of MySQL.

2. Installation

The following installation process is dedicated to Debian based Linux distributions like Ubuntu. Installation and configuration on other distributions and platforms should be similar.

To install the database via command line utilize the following commands.

# update the package repositories
$ apt-get update
$ apt-get safe-upgrade 
 
# if you are interested in installing the most current version of
# MySQL for your distribution execute the following command
$ apt-get install mysql-server mysql-client
 
# to install MySQL 5.1 the following command is valid 
# for Ubuntu 10.04
$ aptitude install  mysql-server-5.1 mysql-client-5.1
 

The setup scipts are initalizing your local machine to hold the management database (it is called mysql) that contains details regarding security releated settings beside other details. If you are setting up MySQL manually or if your software package is missing this step you have to perform this step manually. Don't worry, there is a simple command line tool available called mysql_install_db that cares about this job. For details refer to the appropriate man page.

3. Configuration

3.1. Secure the Installation

MySQL provides a script to perform a lot of security related contigurations at once. It is called mysql_secure_installation and it should be part of your installation. This script is intended to be used on production servers.

Hint: Before executing this script you should have been created accounts for database administration, else you could run into the problem that you won't be able to connect to the database!

$  sudo mysql_secure_installation
   
 
NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MySQL
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!
 
 
In order to log into MySQL to secure it, we'll need the current
password for the root user.  If you've just installed MySQL, and
you haven't set the root password yet, the password will be blank,
so you should just press enter here.
 
Enter current password for root (enter for none):
OK, successfully used password, moving on...
 
Setting the root password ensures that nobody can log into the MySQL
root user without the proper authorisation.
 
Set root password? [Y/n] Y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
 ... Success!
 
 
By default, a MySQL installation has an anonymous user, allowing anyone
to log into MySQL without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.
 
Remove anonymous users? [Y/n] Y
 ... Success!
 
Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.
 
Disallow root login remotely? [Y/n] Y
 ... Success!
 
By default, MySQL comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.
 
Remove test database and access to it? [Y/n] Y
 - Dropping test database...
ERROR 1008 (HY000) at line 1: Can't drop database 'test'; database doesn't exist
 ... Failed!  Not critical, keep moving...
 - Removing privileges on test database...
 ... Success!
 
Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.
 
Reload privilege tables now? [Y/n] Y
 ... Success!
 
Cleaning up...
 
 
 
All done!  If you've completed all of the above steps, your MySQL
installation should now be secure.
 
Thanks for using MySQL!
 

3.2. Create New Databases

When you are running PostgreSQL or Oracle databases you would create a new schema / role to hold all your application related data structures, data and server-side scripts. In MySQL you have to create a new database. Users are created in a separate step and permissions to access your databases have to be granted.

There are several ways to create a new database. The following one describes an alternative by utilizing the command line. If you have to supply a password to create databases you have to add the parameter '-p' to your command line.

# create a new database
$ mysqladmin create mytestdb
 
# create a new database and supply a password 
$ mysqladmin create mytestdb -p
 

3.3. Create New Users

The next example session illustrates how to create a new user.

# connect as admin
$ mysql -u root -p
 
mysql> GRANT ALL PRIVILEGES ON mytestdb.* 
       TO 'myuser'@'localhost'
       IDENTIFIED BY 'mypassword' 
       WITH GRANT OPTION;
 

The previously created database is now accessible via the database user account 'myuser'. In this example 'myuser' has been created with all permissions to create and drop database objects.

3.4. Athentication and Networking

The file my.cnf contains a number of configuration options to tune your local installation of MySQL. By default the database engine is bound to localhost. So you are able to connect to the database from the local machine only. To modify this behaviour you may change the following line in the file.

bind-address            = 127.0.0.1
 

By assigning a different IP address you will be able to connect via dedicated network interfaces. If you decide to disable this limitation you may comment out this line. This will result in a configuration that accepts connection requests from any server.

Please note, that there are serveral tables in the database mysql to control user permissions. If you want to allow dedicated users to connect to your database from remote hosts, you have to grant permissions to these users or you have to modify table entries in the database mysql.

If you did perform any modification to the file my.cnf you have to tell your database engine to reload the configurtion files. There are two options:

• instruct the database engine to reload the configuration files without restarting the engine
• restart your MySQL database engine (shutdown the database server and start it again)

# reload configuration settings
$ service mysql reload
 
# restart the database engine
$ service mysql restart
or 
$ /etc/init.d/mysql restart
 

4. Working with MySQL

4.1. Create a Database

As mentioned above MySQL utilizes a different semantic regaring databases. Creating databases in MySQL is similar to the creation of schemas or roles in others. By creating a new database you are providing a new namespace and container for data and data structures. User accounts are managed completely independant.

# create a database at command line
$ mysqladmin create mytestdb
 
# do the same strick via a database client
$ mysql 
mysql> create database mytestdb2;
Query OK, 1 row affected (0.00 sec)
 
# connect to the new database
mysql> use mytestdb2
Database changed
 
# show a list of available tables
mysql> show tables;
Empty set (0.00 sec)
 
mysql>
 

4.2. MySQL and Online Help

mysql is the native command line client that is part of the core MySQL release (some Linux distributions split the MySQL binaries into multiple packages - in this case you have to install the MySQL client package). It is a powerful tool to analyse, administer and monitor your databases. The following example shows you how to connect to the previously created database and how to access the online help at console.

$ mysql mytestdb -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 37
Server version: 5.1.41-3ubuntu12.3 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> \h
 
For information about MySQL products and services, visit:
   http://www.mysql.com/
For developer information, including the MySQL Reference Manual, visit:
   http://dev.mysql.com/
To buy MySQL Enterprise support, training, or other products, visit:
   https://shop.mysql.com/
 
List of all MySQL commands:
Note that all text commands must be first on line and end with ';'
?         (\?) Synonym for `help'.
clear     (\c) Clear the current input statement.
connect   (\r) Reconnect to the server. Optional arguments are db and host.
delimiter (\d) Set statement delimiter.
edit      (\e) Edit command with $EDITOR.
ego       (\G) Send command to mysql server, display result vertically.
exit      (\q) Exit mysql. Same as quit.
go        (\g) Send command to mysql server.
help      (\h) Display this help.
nopager   (\n) Disable pager, print to stdout.
notee     (\t) Don't write into outfile.
pager     (\P) Set PAGER [to_pager]. Print the query results via PAGER.
print     (\p) Print current command.
prompt    (\R) Change your mysql prompt.
quit      (\q) Quit mysql.
rehash    (\#) Rebuild completion hash.
source    (\.) Execute an SQL script file. Takes a file name as an argument.
status    (\s) Get status information from the server.
system    (\!) Execute a system shell command.
tee       (\T) Set outfile [to_outfile]. Append everything into given outfile.
use       (\u) Use another database. Takes database name as argument.
charset   (\C) Switch to another charset. Might be needed for processing binlog with multi-byte charsets.
warnings  (\W) Show warnings after every statement.
nowarning (\w) Don't show warnings after every statement.
 
For server side help, type 'help contents'
 
mysql>
 

4.3. Create Tables and More...

If you did master the previous steps successfully, then you are ready to work with the model and data to be contained in the database. This section contains just a simple example to get an idea how to utilize the command line utility mysql to interact with MySQL. However, there are a number of mature GUI based database frontends available, that do run on operating systems like Linux, FreeBSD and other Unices, MacOS X and MS Windows.

mysql comes with a set of strong features. To get an comprehensive overview, please refer to the homepage of MySQL.

$ mysql -uroot -p mytestdb
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 39
Server version: 5.1.41-3ubuntu12.3 (Ubuntu)
 
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
 
mysql> CREATE TABLE employees (employee_id int, first_name varchar(10), last_name varchar(10));
Query OK, 0 rows affected (0.00 sec)
 
mysql> INSERT INTO employees VALUES (1, 'John', 'Doe');
Query OK, 1 row affected (0.00 sec)
 
mysql> DELETE FROM employees;
Query OK, 1 row affected (0.00 sec)
 
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
 
mysql>
 

Related resources:

• Homepage of the MySQL project
• MySQL 5.1 Reference Manual
• PHP API for MySQL Clients
• Perl API for MySQL Clients
• Python API for MySQL Clients

Virtually every programming language supports MySQL, so it should be simple to attach any application or software component to MySQL database engines.