Installing and Configuring MySQL
Introduction
We started our LAMP stack when we installed Apache2 on Linux. We added extra functionality when we installed and configured PHP to work with Apache2. In this section, our objective is to complete the LAMP stack and install and configure MySQL.
If you need a refresher on relational databases, see: Introduction to Relational Databases. However in the next section, we will explore the database basics from the command line.
Install and Set Up MySQL
In this section, we'll learn how to install, setup, secure, and configure the MySQL relational database so that it works with the Apache2 web server and the PHP programming language.
First, we install the MySQL Community Server package. The MySQL Community Server package is a metapackage that installs the latest most secure version of MySQL, regardless of the software's version number, as well as its dependencies.
sudo apt install mysql-server
The above install should be fine for us, but note that in some cases you first may want to specifically
confirm which versions are available via the apt
command since some software (e.g., WordPress)
may require specific versions or above.
You can check that with the following:
apt policy mysql-server
After installing, you can confirm the version number with the following command to ensure you know which version you are using:
mysql --version
The install process should start and enable the database server,
but we can check if it's running and enabled using the systemctl
command.
Note that you are looking for the lines beginning with Loaded
and Active
.
systemctl status mysql
Next we need to run a post installation script called mysql_secure_installation
.
The script performs some security checks and creates a secure, baseline configuration of MySQL.
To do that, run the following command:
sudo mysql_secure_installation
When you run the above script, you'll get a series of prompts to respond to. For most responses, you will want to respond with a Y for yes. We will respond with a Y to the first question on validating passwords, but to keep things simple, select LOW when prompted for the password validation policy. This will enforce a weak password policy for testing, but note that in real-world scenarios, we might want to select a more secure policy. In the output below, I show how to respond to each question:
Validate passwords: Y
Password validation policy: 0 (zero) for LOW
Remove anonymous users: Y
Disallow root login remotely: Y
Remove test database and access to it: Y
Reload privilege tables now: Y
We can login to the database now. In order to do so, we use the following command:
sudo mysql -u root
NOTE: we need to distinguish between the regular user prompt of our Linux accounts and the MySQL prompt below. The default promp for our user accounts has the following syntax:
user_name@computer_name:path$
. In the following, I will indicate we are at the MySQL prompt with the following text:mysql>
. Do not type that prompt when you are using MySQL.
Then request a list of the databases with the show databases;
command.
Note that MySQL commands end with a semicolon:
mysql> show databases;
And the following databases should be returned:
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.01 sec)
To exit the MySQL server prompt and return to the Bash shell, we use the following command:
mysql> \q
Create and Set Up a Regular User Account
We need to reserve the root MySQL user for special administrative cases and create a regular MySQL user account for regular use cases.
To create a regular MySQL user, we use the MySQL create
command.
In the command below, I create a new user called opacuser
with a complex password.
The single quotes are quoting the password and are not the password itself.
Thus in the example below, the Xs indicate my password.
First, log back into the MySQL server:
sudo mysql -u root
At the MySQL prompt, create the new user:
create user 'opacuser'@'localhost' identified by 'XXXXXXXXX';
If the prompt returns a Query OK message, then the new user should have been created without any issues.
Create a Practice Database
As the root database user, we create a new database for the user account we just created.
We'll call this database opacdb
and set the character encoding to UTF-8 to support international characters.
Then we run the MySQL show
command to view the new database.
Next we grant all privileges
on the database to the user account opacuser
.
create database opacdb default character set utf8mb4 collate utf8mb4_unicode_ci;
show databases;
grant all privileges on opacdb.* to 'opacuser'@'localhost' with grant option;
Other than granting all privileges, we could limit the user to specific privileges, including:
- CREATE
- DROP
- DELETE
- INSERT
- SELECT
- UPDATE
- GRANT OPTION
Such privileges may be called operations or functions. They allow MySQL users to use and modify the databases, where appropriate. For example, we may want to limit the opacuser user account to only be able to use SELECT commands. These decisions depend on the purpose of the database and our security risks.
Exit out of the MySQL database as the root MySQL user, and then exit out of the root Linux user account. You should be back to your normal Linux user account:
\q
Note: relational database keywords are often written in all capital letters:
CREATE
,DROP
,SELECT
, etc. As far as I know, this is simply a convention to make the code more readable. However, in most cases I'll write the keywords in lower case letters. This is simply because, by convention, I'm lazy.
Logging in as Regular User and Creating Tables
We can now start doing MySQL work.
As a reminder, we've created a new MySQL user named opacuser
and a new database for opacuser
that is called opacdb
.
When we run the show databases
command as the opacuser
user, we should see the opacdb
database.
Note below that I use the -p
option when logging back into MySQL as the opacuser
.
This instructs MySQL to request the password for this user, it is required.
mysql -u opacuser -p
From the MySQL prompt, list the available databases and use the use
command to switch to the new opacdb
database:
show databases;
use opacdb;
A database is not worth much without data.
In the following code, I create and define a new table for our opacdb
database.
The table will be called books
that will contain data describing, er, some books.
We will keep this table very simple and use only four fields: id
, author
, title
, and copyright
.
The id
field will function as a primary key (second to last line in the command below).
This key is used as a unique identifier for a record in the field.
When we create this key as a field called id
, we state that it should be an integer id
(or whole number),
that it should only be a positive number unsigned
,
that it should not be empty not null
, and
that with each record, it should increment by a single integer auto_increment
.
When we create the author
and title
fields,
we say that these fields can have a maximum length of 150 characters and should not be empty.
When we create the copyright
field, we limit it to the year
data type,
which means it has to adhere to a specific syntax YYYY
, and should not be empty.
create table books (
id int unsigned not null auto_increment,
author varchar(150) not null,
title varchar(150) not null,
copyright year(4) not null,
primary key (ID)
);
Note: A relational database contains tables. If you are unfamiliar with this, you can think of a database as an overall Excel spreadsheet file and tables as specific sheets in the Excel file. There is quite a bit that goes into creating proper tables in database because the composition dictates how well data is described and how tables connect and interact with (or relate to) each other. However, we are going to keep things rather simple in this exercise.
You can confirm that the table was created by running the following two commands.
The MySQL show
command lists the tables in a database and the describe
command describes a table's structure.
show tables;
describe books;
Congratulations! Now create some records for that table.
Adding records into the table
We can populate our opacdb database with some data.
(I simply picked the first book listed from the NYTimes best lists of books for the years 2019-2022.)
We'll use the MySQL insert
command to add our records into our books
table.
We need to specify three fields when entering data: author
, title
, and copyright
.
The copyright
field is a date field, and it should conform to the YYYY
syntax.
We do not need to specify data for the id
field because that will be created and will increment automatically.
insert into books (author, title, copyright) values
('Jennifer Egan', 'The Candy House', '2022'),
('Imbolo Mbue', 'How Beautiful We Were', '2021'),
('Lydia Millet', 'A Children\'s Bible', '2020'),
('Julia Phillips', 'Disappearing Earth', '2019');
Now we can view all the records that we just created with the MySQL select
command:
select * from books;
Success! Now let's test our table.
Testing Commands
We will complete the following tasks to refresh our MySQL knowledge:
- retrieve some records or parts of records,
- delete a record,
- alter the table structure so that it will hold more data, and
- add a record
Note: each MySQL command ends with a semi-colon. Some of the following MySQL commands are single-line, but others are multi-line. Regardless if a MySQL command is one-line or multi-line, it doesn't end until it ends with a semi-colon.
select author from books;
select copyright from books;
select author, title from books;
select author from books where author like '%millet%';
select title from books where author like '%mbue%';
select author, title from books where title not like '%e';
select * from books;
alter table books add publisher varchar(75) after title;
describe books;
update books set publisher='Simon \& Schuster' where id='1';
update books set publisher='Penguin Random House' where id='2';
update books set publisher='W. W. Norton \& Company' where id='3';
update books set publisher='Knopf' where id='4';
select * from books;
delete from books where author='Julia Phillips';
insert into books
(author, title, publisher, copyright) values
('Emma Donoghue', 'Room', 'Little, Brown \& Company', '2010'),
('Zadie Smith', 'White Teeth', 'Hamish Hamilton', '2000');
select * from books;
select author, publisher from books where copyright < '2011';
select author from books order by copyright;
\q
Install PHP and MySQL Support
The next goal is to complete the connection between PHP and MySQL so that we can use both for our websites.
First install PHP support for MySQL. We're installing some modules alongside the basic support. These may or may not be needed, but I'm installing them to demonstrate some basics.
sudo apt install php-mysql php-mysqli
And then restart Apache2 and MySQL:
sudo systemctl restart apache2
sudo systemctl restart mysql
Create PHP Scripts
In order for PHP to connect to MySQL, it needs to authenticate itself.
To do that, we will create a login.php
file in in our document root's parent directory: /var/www
.
We also need to change the group ownership of the file and its permissions (see note below).
This will allow the file can be read by the Apache2 web server but not by the world.
This prevents the password information from being accessible to web users.
cd /var/www
sudo touch login.php
sudo chmod 640 login.php
sudo chown :www-data login.php
ls -l login.php
sudo nano login.php
A Short Aside on File Ownership and Permissions
We haven't covered in detail the concept of file ownership and permissions. In short, files and directories are owned by users and by user groups. When we run a command like
ls -l
, the output will show the file owner and the group owner: In the output below, the userroot
is the file owner and the groupwww-data
is the group owner of the filelogin.php
.-rw-r----- 1 root www-data 0 Feb 14 02:44 login.php
We change the file permissions with the
chmod
command. We the file ownership with thechown
command. In the above code snippet, thechmod 640 login.php
command changes the file permissions to:
- user owner: read, write
- group owner: read only
- other/world: no permissions
For an in-depth tutorial, see Linux Systems Administrations, Chapter 3.3 File Permissions and Ownership
When we created the file using the
sudo touch login.php
command, the use ofsudo
creates the file withroot
as the owner. Thechown :www-data login.php
command changes the group ownership towww-data
. Thewww:data
user is the Apache user.Many services, like Apache, have corresponding users on the system. Files placed in our document root
/var/www/html
will be served on the web. Therefore, those files must have read access for other/world. But we don't want thelogin.php
file to be accessible to the world since it contains login information for our MySQL user. Thus, in addition to modifying file ownership and permissions, we also place it in the parent directory/var/www
.
In the file, add the following credentials.
If for some reason you used a different database name than opacdb
and a different username than opacuser
,
then you need to substitute your names below.
You need to use your own password where I have the Xs
:
<?php // login.php
$db_hostname = "localhost";
$db_database = "opacdb";
$db_username = "opacuser";
$db_password = "XXXXXXXXX";
?>
Next we create a new PHP file for our website.
This file will display HTML but will primarily be PHP interacting with our opacdb
database.
Create a file titled opac.php
in /var/www/html
.
cd /var/www/html
sudo nano opac.php
Then copy over the following text. I suggest you transcribe it, especially if you're interested in learning a bit of PHP.
<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<title>MySQL Server Example</title>
</head>
<body>
<h1>A Basic OPAC</h1>
<p>We can retrieve all the data from our database and book table using a couple of different queries.</p>
<?php
// Load MySQL credentials securely
require_once '/var/www/login.php';
// Enable detailed MySQL error reporting
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
// Establish database connection
$conn = new mysqli($db_hostname, $db_username, $db_password, $db_database);
if ($conn->connect_error) {
die("Connection failed: " . $conn->connect_error);
}
echo "<h2>Query 1: Retrieving Publisher and Author Data</h2>";
// Query using prepared statement
$stmt = $conn->prepare("SELECT publisher, author FROM books");
$stmt->execute();
$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {
echo "<p>Publisher " . htmlspecialchars($row["publisher"]) .
" published a book by " . htmlspecialchars($row["author"]) . ".</p>";
}
$stmt->close();
echo "<h2>Query 2: Retrieving Author, Title, and Date Published Data</h2>";
$stmt2 = $conn->prepare("SELECT author, title, copyright FROM books");
$stmt2->execute();
$result2 = $stmt2->get_result();
while ($row = $result2->fetch_assoc()) {
echo "<p>A book by " . htmlspecialchars($row["author"]) .
" titled <em>" . htmlspecialchars($row["title"]) .
"</em> was released in " . htmlspecialchars($row["copyright"]) . ".</p>";
}
$stmt2->close();
$conn->close();
?>
</body>
</html>
Save the file and exit out of nano
.
Test Syntax
After you save the file and exit the text editor, we need to test the PHP syntax. If there are any errors in our PHP, these commands will show the line numbers causing errors or leading up to errors. Nothing will output if all is well with the first command. If all is well with the second command, HTML should be outputted:
sudo php -f /var/www/login.php
sudo php -f /var/www/html/opac.php
Now view the site by opening the public IP address for your server in your browser.
If all goes well, you should see the data in your opacdb
database and books
table rendered in your webpage.
Conclusion
Congratulations! If you've reached this far, you have successfully created a LAMP stack. In the process, you have learned how to install and set up MySQL, how to create MySQL root and regular user accounts, how to create a test database with play data for practicing, and how to connect this with PHP for display on a webpage.
In regular applications of these technologies, there's a lot more involved, but completing the above process is a great start to learning more.