Ubuntu 12.04 LTS Precise Pangolin: Introducing MySQL Server

One of the fastest if not one of the best known database systems on today’s market is MySQL. Forming one of the core packages in any LAMP (Linux, Apache, MySQL, PHP) or LEMP (Linux, Nginx, MySQL, PHP) ‘stack’ and in this article I will show you how to install, configure and manage MySQL on Ubuntu 12.04 LTS Precise Pangolin.

So let’s begin …

Installation

In classic fashion let’s get the process underway by updating our system:
Open Terminal or turn to your console and type:

sudo apt-get update && sudo apt-get upgrade

Accept any updates that are available to you and then install MySQL Server like so:

sudo apt-get install mysql-server mysql-client

The process will not take long but during the installation process you will be prompted to set a password for the MySQL ‘root user’. So choose a strong password and keep it in a safe place for future reference.

When complete, run the following command to secure your installation:

sudo mysql_secure_installation

This utility allows you to limit access to the ‘root’ account, it removes the test database, and allows you to remove all anonymous accounts. It is very simple to use but in order to assist you I have included the following example:

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...
 ... Success!
 - 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!

On completion you will now exit the secure installation process and finalise your installation of MySQL.

Configuration

Well done, MySQL server is now installed on your system but before you begin using it in any environment you should spend a few minutes configuring the service to support your networking needs.

Again, this is a relatively straight forward process and most of the files on a typical system can be found in:

/etc/mysql/

You can edit the /etc/mysql/my.cnf file at any time to configure the basic settings such as the log file, port number, binding etc … but for the purposes of this article we will stick with the most common practices.

When ready, desktop users should open Terminal type:

sudo gedit /etc/mysql/my.cnf

Whereas all server-based users should type:

sudo nano /etc/mysql/my.cnf

Server users can substitute ‘nano’ with their favourite ‘text editor’

Having made any changes to ‘/etc/mysql/my.cnf’ remember to save and close the file before restarting the MySQL service like so:

sudo service mysql restart

bind-address

By default MySQL restricts all access to the localhost, like so:

bind-address		= 127.0.0.1

This is ideal for a closed or secure server installation or even a standalone desktop development environment but it doesn’t always suit everyone needs, and for those of you who would prefer global access or would like to provide a tcp/ip connection from remote system simply locate and comment the following line:

bind-address		= 127.0.0.1

So it looks like this:

#bind-address		= 127.0.0.1

The final example would be as follows:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
#bind-address		= 127.0.0.1

Alternatively, you can configure MySQL to listen for connections from a specific network address like so:

bind-address		= XXX.XXX.XXX.XXX

By replacing XXX.XXX.XXX.XXX with an ip address relevant to your needs (i.e. your local network or internet-based IP address).
An example would be as follows:

# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address		= 192.168.1.100

When complete, save and close the file before restarting the MySQL service by running:

sudo service mysql restart

Query Cache (optional)

The MySQL query cache is used to increase the performance of any query and as most web developers and database administrators are aware ‘query caching’ can significantly improve the speed of your web application.

It works by allowing MySQL Server to store the most recent SELECT statement. If the same SELECT statement is requested later (by the same or another user/visitor), the server then retrieves the results from the query cache rather than performing the same statement again and incurring additional server-load.

Managing the ‘Query Cache’ is not an exact science and much trial and error can be involved as it generally depends on the hardware used and the nature of applications in use, but with the periodic testing of your working applications the ‘query cache’ offers the potential to provide substantial improvements to the overall performance of your database(s) and your server as a whole.

You can make any alterations by opening the following file:

/etc/mysql/my.cnf

Scroll down to find the following lines and make the required changes:

# * Query Cache Configuration
#
query_cache_limit	= 1M
query_cache_size        = 16M

Where (M=MB) ‘Query Cache Limit’ is the maximum size query (in bytes) that will be cached and ‘Query Cache Size’ is the size of the cache in bytes. A good working ‘rule of thumb’ would be to start-out by estimating your cache between 32M-256M (depending on the total amount of RAM available) but if your data is constantly updated then a reduced cache may prove to be better work-around.

Setting the ‘query_cache_size’ value to 0 will effectively disable caching.

An typical example of such a change could be as follows:

# * Query Cache Configuration
#
query_cache_limit	= 2M
query_cache_size        = 32M

As always, when complete, save and close the file before restarting the MySQL service by running:

sudo service mysql restart

Service status

To stop your MySQL Server use:

sudo mysql stop

To start your MySQL Server use:

sudo mysql start

To restart your MySQL Server use:

sudo mysql restart

To check the status of your MySQL Server use:

sudo netstat -tap | grep mysql

But for those of you who prefer even more information about your MySQL Server, you can always use:

/usr/sbin/mysqld --help --verbose

And yes, this final command does provide for a lot of information :-)

MySQL Tools

There are many packages are available to assist you in using your very own MySQL Server. These include, but are not limited to:

• MySQL Work Bench

  sudo apt-get install mysql-workbench

• MySQL Navigator

  sudo apt-get install mysql-navigator

• Emma

  sudo apt-get install emma

• MySQL Admin

  sudo apt-get install mysql-admin

• PHPMyAdmin

  sudo aptitude install phpmyadmin

  (requires Apache with PHP)

To install any of the above, simply open the Ubuntu Software Centre and search for the relevant tool or use the command-line code as shown.

I would suggest looking at MySQL Work Bench first :-)

MySQL Commands

MySQL uses SQL or “Structured Query Language”, and although it is beyond the scope of this article to give you a a concise tutorial I will provide you with a starting point on how to manage your MySQL Server on the command line:

General Access

You are able to access your MySQL server by using:

mysql -u root -p

Authenticate yourself with your MySQL ‘root’ password and and you will be granted access to the MySQL Management Console like so:

Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 37
Server version: 5.5.22-0ubuntu1 (Ubuntu)

Copyright (c) 2000, 2011, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

mysql>

From this point onwards you are working within MySQL until you exit the application with the following instruction. At the mysql> prompt type:

exit;

Having exited the application your original Terminal or Console environment will be restored.

Adding database users

To add a user called ‘personsname’ and grant access from localhost:

mysql> GRANT ALL ON databasename.* TO personsname@localhost IDENTIFIED BY 'Add-Your-Password-Here';
FLUSH PRIVILEGES;

Alternatively, you can use the network variation:

mysql> GRANT ALL ON databasename.* TO personsname@192.168.1.10 IDENTIFIED BY 'Add-Your-Password-Here';
FLUSH PRIVILEGES;

Viewing database catalogues

To list your current databases, type the following sql command at the mysql> prompt:

mysql> show databases;

Creating database catalogues

To add a new database called XXX, type the following sql command at the mysql> prompt:

mysql> create database XXX;

If you want to learn more then I would suggest looking at Simply SQL by Sitepoint for a more in-depth review of the SQL syntax. It’s a pretty good read :-)

Log files

Just before I leave you and depending on your configuration changes, for a typical installation all log files can be found in:

/var/log/

Remember, if you are running a firewall, you will need to open port 3306 for your MySQL Server

So until next time …
I hope that you continue to enjoy using Ubuntu 12.04 LTS Precise Pangolin.

If you enjoyed reading this post, you’ll love Learnable; the place to learn fresh skills and techniques from the masters. Members get instant access to all of SitePoint’s ebooks and interactive online courses, like Ubuntu Linux.