Installing MySQL on Ubuntu Desktop

0018-fi-mysql-logo
MySQL is the most popular open source database management system in use today and is a component of the LAMP architecture (Linux, Apache, MySQL, PHP). In the articles listed below, I have been building out a LAMP stack in a Virtual Machine.

In this article we complete the LAMP stack by installing MySQL on the platform. I’ll demonstrate how to install MySQL Server as well as a couple of useful development/administration tools: phpMyAdmin and MySQL Workbench. I’ll also walk through the creation of a basic Web page that tests the integration of the components.

Installing MySQL Server

If you’re following along from the last article listed above you will have a virtual machine with Ubuntu, Apache, and PHP installed and operational. Log in to Ubuntu as the administrative user and open a terminal window. Type the command sudo apt-get install mysql-server as shown below. Enter your password when prompted and press Enter. When you see the prompt Do you want to continue [Y/n]? press y followed by Enter.

MySQL can be installed via the mysql-server package

MySQL can be installed via the mysql-server package.

You should see a dialog box prompting you to enter the password for the MySQL root user. Enter a password and press Tab to navigate to the Ok button. Press Enter as shown below.

Enter MySQL root password

Enter the password for the MySQL server root user.

You will next see a confirmation dialog. Re-enter your password. Press Tab to navigate to the Ok button and press Enter.

Repeat the password

Repeat the password.

Once the installation script completes, MySQL will be installed and running (gee, that was easy). Let’s do a quick test to ensure that MySQL is operational before proceeding to the next section. Enter the command mysql -u root -p at the shell prompt in the terminal window as shown below. The -u option specifies the logon name of the user (root), while the -p option instructs the program to prompt for the password. This will start the MySQL Monitor, a text-based interface to MySQL. Enter the MySQL root password you chose during the installation and press Enter. You should now see the mysql> prompt.

MySQL monitor

MySQL Monitor is a text interface to MySQL. Start the MySQL monitor as root.

Enter the command show databases; at the mysql> prompt and press Enter. You will see that four databases are listed as shown below. Enter \q to exit from MySQL Monitor.

List databases

Test the MySQL installation by listing the currently-installed databases.

Installing phpMyAdmin

phpMyAdmin is a web-based tool that you can use to administer MySQL. To install it, type the command sudo apt-get install myphpadmin at the shell prompt in the terminal window as shown below. Press y and Enter when prompted to continue.

Insall phpMyAdmin

Install phpMyAdmin with the phpmyadmin package.

You will eventually see the dialog below. Press the Spacebar to mark the apache2 selection and press Tab to navigate to the Ok button. Press Enter.

Choose Apache to configure

Choose Apache to configure to use phpMyAdmin.

You will next see the dialog below. Since we have already installed and configured MySQL, tab to No and press Enter.

Choose No

Choose No since we already have a MySQL database configured.

Once the installer completes, launch the Firefox web browser from the Launcher. Enter the URL http://localhost/phpmyadmin in the address bar. You should see the phpMyAdmin logon screen as shown below. Choose a language from the drop-down box, enter root in the Username field, and your MySQL root password in the Password field. Click Go.

Login to phpMyAdmin as root

Log in to phpMyAdmin as the root user.

You should now see the phpMyAdmin home screen. Click the Databases tab to see the same list of available databases that you saw with MySQL Monitor earlier.

Select Database tab

Select the Database tab to see the list of currently installed databases.

Installing MySQL Workbench

MySQL Workbench is a graphical administration and development tool for MySQL databases. We’ll install it by typing the following command at the shell prompt in a terminal window: sudo apt-get install mysql-workbench. When you see the prompt to continue, press y and Enter as shown below.

Install MySQL Workbench

Install MySQL Workbench with the mysql-workbench package.

Once the installation completes, click the Dash icon at the top of the Launcher and type mysql into the search box. When the icon for MySQL Workbench appears, click it.

Launch MySQL Workbench

Launch MySQL Workbench.

The MySQL Workbench application will launch and display its main window. Double-click New Connection as shown below.

Create Connection

Create a new connection to the local database.

You will next see the Setup New Connection dialog box. Enter a name for the connection (I chose Local) and click Ok.

Create a connection

Enter a name for the connection. The default hostname, port, and username should be correct for this local database. You can test the connection with the Test Connection button.

Test the Stack

Let’s put together a small PHP program that tests the integration of our three AMP components: Apache, MySQL, and PHP. This will be a simple PHP program that connects to the local MySQL database, displays some data from a custom table in HTML, and then disconnects from the database.

We’ll first launch the MySQL Monitor text interface to perform a few administrative actions. Enter the command mysql -u root -p as you did earlier. Enter your MySQL root password and press Enter. You should see the mysql> prompt as shown below.

Connect as root

Connect to MySQL as the root user.

Because we don’t want to use the MySQL root user for application access, we’ll create a new user called stocks. At the mysql> prompt, enter the CREATE USER statement as shown below.

CREATE USER 'stocks'@'localhost'
IDENTIFIED BY 'stocks';
CREATE USER command

Create a new user with the CREATE USER command.

We will now create a database (schema for you Oracle folks) to hold the table for our stocks application. Enter the CREATE DATABASE command as shown below.

CREATE DATABASE stocks;
CREATE DATABASE command

Create a new database (schema) with the CREATE DATABASE command.

We will also create a table in the stocks database called prices. This table will store a symbol (e.g. AAPL), a date, and four prices representing the open, close, high, and low prices for each day. Because we want to store this table in our new stocks database, first switch to that database by entering the USE statement. You can then create a table in this database with the CREATE TABLE statement as shown below.

USE stocks;

CREATE TABLE prices (
  symbol      VARCHAR(10) NOT NULL,
  day         DATE NOT NULL,
  open_price  FLOAT(5,2),
  close_price FLOAT(5,2),
  high_price  FLOAT(5,2),
  low_price   FLOAT(5,2) );
CREATE TABLE command

Change to the new database and create a table in it using the CREATE TABLE command.

You can examine the structure of the table with the DESCRIBE command as shown below. Our prices table contains six columns of various data types. The symbol column is of VARCHAR(10) type and can store strings up to maximum length of 10 characters. The day column is of type DATE and can store calendar dates. The four price columns are of type FLOAT(5,2) and can store floating point numbers in the format 999.99.

DESCRIBE prices;
DESCRIBE command

Take a look at the table’s structure with the DESCRIBE command.

Now that we have a table we can put some data in it. We’ll use the SQL INSERT statment to add five days of test data (the code block below should scroll horizontally).

INSERT INTO prices VALUES ('AAPL', '2013-10-07', 486.56, 487.75, 492.65, 485.35);
INSERT INTO prices VALUES ('AAPL', '2013-10-08', 489.94, 480.94, 490.64, 480.54);
INSERT INTO prices VALUES ('AAPL', '2013-10-09', 484.64, 486.59, 487.79, 478.28);
INSERT INTO prices VALUES ('AAPL', '2013-10-10', 491.32, 489.64, 492.38, 487.04);
INSERT INTO prices VALUES ('AAPL', '2013-10-11', 486.99, 492.81, 493.84, 485.16);
INSERT statement

Enter new records into the table with the INSERT statement.

You can view the rows of the table with the SQL SELECT statement below. This statement retrieves all of the columns (*) from the prices table and sorts the rows by the symbol and day columns.

SELECT *
FROM prices
ORDER BY symbol, day;
SELECT statement

Retrieve (query) data from the table with the SELECT statement.

Since our PHP program will connect to MySQL as the stocks user, this user must be granted access to the prices table. This can be accomplished with the GRANT statement below. This statement grants the stocks user the privilege to select from (query) the prices table.

GRANT SELECT ON prices TO stocks@localhost;
GRANT statement

Grant access to the table with the GRANT statement.

We’ll test the GRANT by connecting to MySQL as the stocks user and trying to query the table. Enter \q at the mysql> prompt to exit from the Monitor where you are currently connected as root. Then, launch the MySQL Monitor again with the command mysql -u stocks -p. Enter the stocks user password when prompted. Switch to the stocks database with the USE command. Query the prices table as before. Exit from the MySQL monitor with \q. This sequence is shown below.

SELECT statement

While connected as the stocks user, select data from the prices table to check the new GRANT.

Now that we have a MySQL database, user, table, and data to work with we can create a simple PHP program to display the data on a Web page. I’ll build up the page step-by-step and explain the code as I go.

Create a new file in /var/www called stock_prices.php. You can use any editor you like, such as vi or nano, but remember to precede the editor command with sudo so that you have the necessary privilege to create a file in the directory. Enter the following code in stock_prices.php. This is just some template HTML code to test that Apache is up and running. Be sure to save your file.

<!doctype html>
<html>
<head>
  <title>Stock Prices</title>
</head>
<body>
  <h1>Stock Prices</h1>
</body>
</html>

Launch your Firefox browser from the Launcher and navigate to the URL http://localhost/stock_prices.php. You should see the page below.

Basic Web page template

Basic Web page template in a PHP file.

Below the <h1> heading line, enter the code highlighted below. The code from lines 11-14 saves the connection parameters to variables for ease of maintenance. The code on line 17 uses the mysqli() class to connect to the MySQL database. I pass the connection information from the variables to the constructor. If there is an error connecting to MySQL, lines 20-21 are executed to display the error number and message. If the connection is successful, line 26 prints the server version and the connection is closed on line 29.

<!doctype html>
<html>
<head>
  <title>Stock Prices</title>
</head>
<body>
  <h1>Stock Prices</h1>

  <?php
  /* Save connection info */
  $dbHost   = 'localhost';
  $dbUser   = 'stocks';
  $dbPw     = 'stocks';
  $dbSchema = 'stocks';

  /* Connect to MySQL */
  $connId = new mysqli($dbHost, $dbUser, $dbPw, $dbSchema);

  /* If connect fails, display error message */
  if ($connId->connect_error) {
    echo "<p>Error: $connId->connect_errno $connId->connect_error </p>\n";

  /* Interact with the database */
  } else {
    /* Print server version */
    echo "<p>Server version: $connId->server_version</p>\n";

    /* Close connection */
    $connId->close();
  }
  ?>

</body>
</html>

If you refresh your browser you should see the server version displayed as shown below. The version number is shown as an integer but can be interpreted as 5.5.32. If there is a connection error, you will instead see an error message. You can test this by coding an incorrect username, password, or database (schema) value.

Successful MySQL Connection

A successful connection from a PHP page to MySQL. The server version is 5.5.32.

Next, we’ll query our prices table. Just prior to the server version code insert the highlighted code shown below (the code window should scroll horizontally). The statement at line 27 uses the query() method to send a SQL SELECT statement to the database as a string. Note that this is the same statement that we used earlier in the MySQL Monitor interface. If the query is successful, the result is returned to $queryResult, an object of type mysqli_result. If the query fails for some reason, the result is false and the code on line 31 displays the associated error message.

Assuming that the query succeeds we will display the output in an HTML table. The code from lines 35-37 generates the table header row. We then enter a while loop that begins to fetch results from the $queryResult object into an associative array using the fetch_assoc() method. The $row array is indexed by the column names from the prices table. Each echo within the loop generates a column of output, including the HTML markup. The loop exits once all of the data from the prices table has been fetched.

<!doctype html>
<html>
<head>
  <title>Stock Prices</title>
</head>
<body>
  <h1>Stock Prices</h1>

  <?php
  /* Save connection info */
  $dbHost   = 'localhost';
  $dbUser   = 'stocks';
  $dbPw     = 'stocks';
  $dbSchema = 'stocks';

  /* Connect to MySQL */
  $connId = new mysqli($dbHost, $dbUser, $dbPw, $dbSchema);

  /* If connect fails, display error message */
  if ($connId->connect_error) {
    echo "<p>Error: $connId->connect_errno $connId->connect_error </p>\n";

  /* Interact with the database */
  } else {

    /* Query the prices table */
    $queryResult = $connId->query("SELECT * FROM prices ORDER BY symbol, day");

    /* If query fails, display error message */
    if (!$queryResult) {
      echo "<p>Query error: $connId->error</p>\n";
    } else {

      /* Display query results */
      echo "<table>\n";
      echo "<tr><th>Symbol</th><th>Date</th><th>Open</th>",
           "<th>Close</th><th>High</th><th>Low</th></tr>\n";
      while ($row = $queryResult->fetch_assoc()) {
        echo "<tr>";
        echo "<td>", $row["symbol"],      "</td>";
        echo "<td>", $row["day"],         "</td>";
        echo "<td>", $row["open_price"],  "</td>";
        echo "<td>", $row["close_price"], "</td>";
        echo "<td>", $row["high_price"],  "</td>";
        echo "<td>", $row["low_price"],   "</td>";
        echo "</tr>\n";
      }
      echo "</table>\n";
    }

    /* Close result set */
    $queryResult->close();

    /* Print server version */
    echo "<p>Server version: $connId->server_version</p>\n";

    /* Close connection */
    $connId->close();
  }
  ?>

</body>
</html>

If you refresh your browser, you should see the output shown below.

Displaying data from a table

The PHP program retrieves data from the prices table and displays it in an HTML table.

That was a simple PHP program to display data retrieved from MySQL on a web page. If you like, you can drop the user and database created earlier with the following statements (use MySQL Monitor as root).

drop user stocks@localhost;
drop database stocks;

This completes the build-out of the LAMP stack in a virtual machine. This would be an excellent time to take another VM snapshot as we did after installing Ubuntu back in the article Installing Ubuntu Desktop on Oracle VirtualBox. A snapshot taken now will allow us to roll the state of the VM back to this point after using it for application development and testing.

VirtualBox Snapshot

After installing Apache, MySQL, and PHP it’s a good time for another VM snapshot.

In this article we installed MySQL and a couple of useful tools: phpMyAdmin and MySQL Workbench. We tested the MySQL server using the command-line interface and both of the graphical tools. We also created a simple PHP application that connected to the database, queried a table, and displayed the retrieved data on a web page. We then took a snapshot of the VM so that we can return to this point in the future if desirable…or necessary :)

Future articles will use this platform for various projects that I hope to undertake in the coming months. Stay tuned…

Speak Your Mind

*