To install MySQL Server 5.5 on Ubuntu 12.04 Precise Pangolin, make your way to a Terminal window or a command prompt, and type this command:
sudo apt-get install mysql-server-5.5
(As of this writing, MySQL Server version 5.5 is the latest version available in the repositories, though future versions of Ubuntu may receive the newer versions of MySQL.)
Enter your password to authenticate, and apt will download the MySQL files and install them for you. It’s a big set of files, so depending on the speed of your Internet connection, it might take a while to download. After the files are downloaded and are installing, the installer will ask you for a password for MySQL’s root user. Just like the root user in Linux, the root user in MySQL has absolute control over all databases, tables, permissions, and users. For obvious security reasons, you’ll want to create an extremely strong password (a mixture of uppercase, lowercase, numbers, and punctuation, the longer the better) for your MySQL root user.
(Note that in the password dialog box, you can’t get the <OK> field selected, you can use the tab key to jump from the text input line to the <OK> field.)
After you enter the root password, the installer will finish working with the MySQL files, and return you to the command line. You’ll then need to activate MySQL with the following command:
This will set up MySQL Server for use with your Ubuntu 12.04 Precise Pangolin system. Next, you’ll want to run the mysql_secure_installation script to tighten up security on your new MySQL server. Run this command from the prompt:
First, the mysql_secure_installation script will ask you to enter the current password for the MySQL root user. After you do that, it will ask if you want to change the root password. Since you already set a root password, you can hit “n” (unless you want to change it again for some reason).
Next, the script will ask if you want to remove the anonymous user. The anonymous user, like anonymous access in FTP, lets someone log into MySQL without having a proper user account. For security reasons, it’s always best to remove the anonymous user, so hit “y” to continue.
After that, the script will ask if you want to prevent the MySQL root user from logging in remotely to the MySQL server. Always hit “y” to forbid root remote access, since if an attacker guesses your root password, he can destroy your databases or steal the information they contain.
After this, the script will ask if you want to remove the test database. MySQL includes a test database that anyone can access. Again, this is a security hole, so you’ll want to hit “y” to remove the test database.
The script will then ask to reload the privilege tables so the changes take effect. Hit “y”, and the mysql_secure_installation script will conclude and return you to the command line.
MySQL server is now installed on your Ubuntu 12.04 Precise Pangolin system. In the next section, we’ll discuss using the MySQL command-line client to create databases and users.
The most common use for MySQL on a Ubuntu 12.04 Precise Pangolin system is to provide a backend for a dynamic website of some kind, usually content management systems like WordPress or MediaWiki. To host these websites on a Ubuntu 12.04 Precise Pangolin machine, you’ll need to create a database and a database user for the website. MySQL Server can support many different databases, limited by the amount of hardware and disk space available on the machine. (Hosting multiple active databases at the same time places a high level of demand upon a hard drive.)
Like Samba, MySQL has its own collection of user accounts, separate from the main system accounts, called “database users.” A database user is simply a MySQL user that has access to a particular database, or specific tables within a database. In this section, we’ll show you how to create database and users with the command-line client.
First, you’ll need to get to the MySQL client prompt. The MySQL command-line client, like the command-line clients for FTP and SFTP, has its own prompt. To access that prompt and start the client, use this command:
However, if you simply type the command, it will bounce back with an error message. To use the MySQL command-line client, you need to add the -u and the -p switches as well. The -u switch tells it what MySQL user you want to use to log into the client (similar to the -l switch with SSH). The -p switch tells it to ask for the password. So, to use the MySQL client as the MySQL root user, the command should look like this:
mysql -u root -p
Enter the password for the MySQL root user, and you’ll find yourself at the MySQL command prompt, which will look like this:
Our next steps are to create a database, create a user to access that database, and grant our new user all rights to that database. Generally, when you’re installing a web application like WordPress, you’ll give the database user you create for the WordPress application all rights to the database so it can function properly. Not giving the application user all rights to the database can cause the application to act erratically or even fail entirely (though some applications can work with limited access to its database).
An important note before we continue, though. All commands made from the MySQL prompt must end with a semicolon to denote the end of the statement. Any commands that do not end with a semicolon will not work. With that in mind, let’s first create a database.
To create a database, use this command at the mysql> prompt:
CREATE DATABASE newdatabase;
The MySQL client will respond with a message that should say “Query OK, 1 row affect (0.00 sec).” This means the command was successful, and a new database named “newdatabase” has been created.
Next, you’ll create a user who will access that database:
CREATE USER newdatabaseruser;
You should get the “Query OK” message again. Once the new user is created, we’ll need to set a password for the user before we can assign any permissions. In this example, we’ll assign a password of “1234″. However, in real life, just as with the root user, you’ll want to assign a strong password. (This database user won’t have full control over the MySQL user as the root user does, but we will give it full control over the database we just created, and a malicious user who guesses the password could cause all kinds of trouble.) To create the password, use this command:
SET PASSWORD FOR newdatabaseuser= PASSWORD(“1234”);
Again you should get the “Query OK” confirmation message. The final step is to assign all privileges on the “newdatabase” database to the “newdatabaseuser” user. Use this command to assign the permissions:
GRANT ALL PRIVILEGES ON newdatabase.* TO newdatabaseuser IDENTIFIED BY ‘1234;
That isn’t a typo – those are single quote marks (‘) instead of the usual double quote marks (“) that were used in the command to set the password. MySQL’s internal syntax, alas, isn’t always consistent. Anyway, if you typed the command correctly, you should get the “Query OK” confirmation again.
Once you are done, use this command to quit the MySQL command-line interface:
You’ve now set up a database, created a database user, and given that user full control over that database.