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 …
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:
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.
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:
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
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:
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
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 :-)
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
sudo apt-get install emma
- MySQL Admin
sudo apt-get install mysql-admin
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 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:
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:
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 firstname.lastname@example.org 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 :-)
Just before I leave you and depending on your configuration changes, for a typical installation all log files can be found in:
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.