In this lesson, we'll complete our LIM stack by installing Maria DB.
Maria DB is an open source fork of the popular my SQL database.
It uses the same interface and commands as my SQL, so it works as a drop in replacement.
You can learn more about Maria db at Maria db dot org.
I'm connected to our development server as the route user.
And Gen X and PHP have already been installed and configured.
Now let's install Maria DB.
We'll be installing the database server along with the client that were allowed to easily create and
manipulate databases.
We'll do this with APT install Maria DB server and Maria DB client.
Once the installation is complete, we can confirm all is well by checking the status of the server
with system CTL.
One thing to note here is that even though we installed the Maria DB server, it still uses the name
my SQL DD.
It also uses my SQL for the client, which we can check with the DASH version switch.
Now that we have our Maria DB client and server installed, let's secure the installation.
The initial installation for the database server includes some defaults that we need to change to make
things more secure.
For example, we need to set a root password, disable remote connections for root, and remove anonymous
accounts.
These steps may not be necessary for a development environment, but they are definitely required for
databases used in production.
Getting into the habit of securing your database installation is a good thing.
It is also quick, easy and can keep you from getting hacked.
All we have to do is use a tool called my SQL Secure installation that comes with Maria DB.
Running this tool starts a wizard that walks us through the process of making sure everything is secure.
First, we are prompted to enter our current password for root or enter for none.
We didn't set one during install so we can just press enter here.
The next question asks if we want to set a root password.
And of course we do.
So press enter again to select the default value of Yes.
Choose a password that's easy to remember, but difficult for others to guess.
Next up, we need to remove anonymous users.
This forces all users to have an account set up before they can connect.
We'll hit enter to apply that.
Now we remove remote access for the root account.
This means no one can connect to our server as root.
They'll have to be logged on to the server and then they can connect the root account and password.
Next, we'll remove the test database and apply the changes we've made so far.
See how easy it was to make our Maria DB installation secure?
Now we can get to the fun part of setting up the database that we'll use for our limp demonstration.
First, we'll connect to the database as the root user with the my SQL client.
We'll enter my SQL u root p The dash u passes the user we want to connect as.
And the dash p tells the My SQL client to prompt us for the password.
Once we're logged in, we can start creating our demo database with a few SQL commands.
First, we'll create the database, but only if it does not exist, which we know it doesn't.
Then we'll create a user called admin, and then we'll give the admin user all privileges on the appointment
database using the password admin.
And it looks like I have a typo, so I'm just going to correct that real quick.
Now let's disconnect from the route user and log in with the admin user.
To log out.
You can just type exit or press control DD.
And you see that Maria Dbx is so nice.
It even says by when we log out.
Now that we have an account set up, we can use the My SQL command again, this time with admin as the
user.
And then we'll use the password admin to log in.
Once we're logged in, we can check on the database we created with a few SQL commands.
First, we just list the databases with the show databases command, and then we select the appointments
database with the use command.
And finally, we take a look inside the appointments database with the show Tables Command.
We just created this database and haven't entered any data.
So of course the database should be empty.
Now that we have our Maria DB installed and our database is set up, we have all the components of our
Lynch stack.
At this point, we're ready to add some dynamic content to our static site.
We'll be setting up a PHP application that lists the appointments for the patients of this site.
Their application is a PHP script that connects to the appointment database, reads the appointments
and lists them in a table.
It's a simple application, but it's perfect for demonstrating how the components of the limp stack
work together.
I'm connected to our development server where Engine X, Maria, DB and PHP are installed and configured.
Let's change to the route user and do a quick check to make sure all of the systems are running as expected.
We'll use System CTL to check the status of each service engine X, my SQL and PHP FPM.
We could check each one individually, but fortunately we can use one.
Call the system CTL and check them all at the same time.
To get the actual info we need, let's pipe the output to grep and look for loaded and active.
This output shows us that each service is running an active.
We're good to go.
If you're following along and your output differs from this, review the installation for each service
and make any corrections.
Now let's install the PHP script.
I'm using the exercise files so I can find the script in the vagrant directory, which is synchronized
with the directory where I started the VMM with Vagrant up.
The script is in the file named index PHP.
We won't be going into too much detail about the code, but let's take a quick look at a few lines inside
the script and I'm going to go to line 97.
I wanted to point out the lines where two components of the stack come together, the PHP and the MySQL
database.
On this line we see where PHP is using the my SQL, I underscore connect function to connect to the
database on local host.
The user and password are there along with the name of the database.
A few lines down on line 102.
We see where PHP uses the my SQL.
I underscore query function to get all the rows from the data table of the appointment database and
on line 110 PHP prints the data as HTML.
Of course, this is the content.
We'll see when the page is served by Engine X and loaded into our browser.
Now let's put this script where Engine X can access it.
We'll make a directory inside the root of the website and call it appointments.
And we can copy the index PHP file to this directory.
We also need to set the permissions on the file to make sure the Engine X process can read it.
Okay.
Everything looks good.
Now is a good time to point out why the file for our application is named index PHP.
Let's take a look inside the configuration for our site.
Engine X is configured to use index php as one of the files that gets loaded by default when engine
x serves a directory.
By naming our script index PHP and then placing it in a directory named appointments.
Anytime someone requests the page appointments, the app will get loaded automatically.
Pretty slick, right?
We could have used another name for the script appointments dot PHP for example, but using index php
makes things easier to manage if the appointment location needs other directives apply to it.
Now let's take a look at our web app.
And in a browser, I'll go to the IP address for our development server.
192.168.0.3 and go to appointments.
We can see our application, but there is something missing.
And this error tells us right away there's no data in our database.
Let's fix that.
In the slash vagrant directory.
There's a file named appointment underscore database dot SQL.
This contains the data we need and the commands to insert them into the database.
Before we do that, let's take a quick look inside this file.
In the SQL file, we can see on this line the very first line where the table gets created along with
the names of each column.
And on line 13 is where the data insertion begins.
The rest of the lines in the file are the rows of the data that make up the database.
Now let's insert the data.
We'll use a standard in redirect to send the contents of the SQL file into my SQL command.
And we'll use my SQL U and then the admin user P, and then we'll specify the admin user's password
on the command line so that we're not prompted for it.
And then we have to specify the name of the database that we want to inject this data into, which is
appointments.
And now we can redirect our SQL file into this command.
Now let's go back and refresh the appointments page.
There's our data.
Now that the database has some content, the page loads without an error.
Now we can say we've successfully applied each component of the linked stack.
We added some dynamic content to our static site by applying some PHP code and some database content.
But we're still not done.
There are other things we can consider for our site, with security being the top priority.