WEBVTT 00:03.070 --> 00:07.600 In this lesson, we'll complete our LIM stack by installing Maria DB. 00:08.140 --> 00:12.820 Maria DB is an open source fork of the popular my SQL database. 00:13.490 --> 00:19.130 It uses the same interface and commands as my SQL, so it works as a drop in replacement. 00:19.670 --> 00:23.750 You can learn more about Maria db at Maria db dot org. 00:24.380 --> 00:27.860 I'm connected to our development server as the route user. 00:28.540 --> 00:32.620 And Gen X and PHP have already been installed and configured. 00:33.130 --> 00:35.920 Now let's install Maria DB. 00:36.430 --> 00:41.620 We'll be installing the database server along with the client that were allowed to easily create and 00:41.620 --> 00:43.360 manipulate databases. 00:43.900 --> 00:48.760 We'll do this with APT install Maria DB server and Maria DB client. 00:49.300 --> 00:54.760 Once the installation is complete, we can confirm all is well by checking the status of the server 00:54.760 --> 00:56.410 with system CTL. 00:56.950 --> 01:02.530 One thing to note here is that even though we installed the Maria DB server, it still uses the name 01:02.530 --> 01:03.700 my SQL DD. 01:04.270 --> 01:09.310 It also uses my SQL for the client, which we can check with the DASH version switch. 01:09.850 --> 01:15.520 Now that we have our Maria DB client and server installed, let's secure the installation. 01:16.030 --> 01:21.340 The initial installation for the database server includes some defaults that we need to change to make 01:21.340 --> 01:22.690 things more secure. 01:23.290 --> 01:29.170 For example, we need to set a root password, disable remote connections for root, and remove anonymous 01:29.170 --> 01:29.950 accounts. 01:30.520 --> 01:36.010 These steps may not be necessary for a development environment, but they are definitely required for 01:36.010 --> 01:38.170 databases used in production. 01:38.740 --> 01:43.240 Getting into the habit of securing your database installation is a good thing. 01:43.780 --> 01:47.740 It is also quick, easy and can keep you from getting hacked. 01:48.310 --> 01:54.070 All we have to do is use a tool called my SQL Secure installation that comes with Maria DB. 01:54.610 --> 02:00.460 Running this tool starts a wizard that walks us through the process of making sure everything is secure. 02:01.050 --> 02:06.000 First, we are prompted to enter our current password for root or enter for none. 02:06.510 --> 02:10.560 We didn't set one during install so we can just press enter here. 02:11.120 --> 02:14.330 The next question asks if we want to set a root password. 02:14.330 --> 02:15.770 And of course we do. 02:16.280 --> 02:20.210 So press enter again to select the default value of Yes. 02:20.840 --> 02:25.190 Choose a password that's easy to remember, but difficult for others to guess. 02:25.770 --> 02:29.100 Next up, we need to remove anonymous users. 02:29.610 --> 02:33.810 This forces all users to have an account set up before they can connect. 02:34.350 --> 02:36.510 We'll hit enter to apply that. 02:37.050 --> 02:40.440 Now we remove remote access for the root account. 02:40.950 --> 02:44.250 This means no one can connect to our server as root. 02:44.790 --> 02:49.860 They'll have to be logged on to the server and then they can connect the root account and password. 02:50.370 --> 02:55.170 Next, we'll remove the test database and apply the changes we've made so far. 02:55.710 --> 02:59.970 See how easy it was to make our Maria DB installation secure? 03:00.510 --> 03:06.180 Now we can get to the fun part of setting up the database that we'll use for our limp demonstration. 03:06.720 --> 03:11.520 First, we'll connect to the database as the root user with the my SQL client. 03:12.060 --> 03:18.030 We'll enter my SQL u root p The dash u passes the user we want to connect as. 03:18.060 --> 03:22.050 And the dash p tells the My SQL client to prompt us for the password. 03:22.590 --> 03:27.900 Once we're logged in, we can start creating our demo database with a few SQL commands. 03:28.500 --> 03:33.990 First, we'll create the database, but only if it does not exist, which we know it doesn't. 03:34.560 --> 03:41.670 Then we'll create a user called admin, and then we'll give the admin user all privileges on the appointment 03:41.670 --> 03:44.040 database using the password admin. 03:44.550 --> 03:49.050 And it looks like I have a typo, so I'm just going to correct that real quick. 03:49.630 --> 03:54.100 Now let's disconnect from the route user and log in with the admin user. 03:54.690 --> 03:55.650 To log out. 03:55.650 --> 03:58.410 You can just type exit or press control DD. 03:59.020 --> 04:01.630 And you see that Maria Dbx is so nice. 04:01.630 --> 04:03.790 It even says by when we log out. 04:04.300 --> 04:09.940 Now that we have an account set up, we can use the My SQL command again, this time with admin as the 04:09.940 --> 04:10.630 user. 04:11.230 --> 04:14.710 And then we'll use the password admin to log in. 04:15.190 --> 04:20.680 Once we're logged in, we can check on the database we created with a few SQL commands. 04:21.270 --> 04:26.910 First, we just list the databases with the show databases command, and then we select the appointments 04:26.910 --> 04:28.890 database with the use command. 04:29.460 --> 04:34.680 And finally, we take a look inside the appointments database with the show Tables Command. 04:35.190 --> 04:38.520 We just created this database and haven't entered any data. 04:38.520 --> 04:41.370 So of course the database should be empty. 04:41.940 --> 04:47.790 Now that we have our Maria DB installed and our database is set up, we have all the components of our 04:47.790 --> 04:48.600 Lynch stack. 04:49.690 --> 04:54.040 At this point, we're ready to add some dynamic content to our static site. 04:54.550 --> 05:00.100 We'll be setting up a PHP application that lists the appointments for the patients of this site. 05:00.640 --> 05:06.490 Their application is a PHP script that connects to the appointment database, reads the appointments 05:06.490 --> 05:08.200 and lists them in a table. 05:08.860 --> 05:14.050 It's a simple application, but it's perfect for demonstrating how the components of the limp stack 05:14.050 --> 05:15.040 work together. 05:15.770 --> 05:22.580 I'm connected to our development server where Engine X, Maria, DB and PHP are installed and configured. 05:23.060 --> 05:29.030 Let's change to the route user and do a quick check to make sure all of the systems are running as expected. 05:29.540 --> 05:36.290 We'll use System CTL to check the status of each service engine X, my SQL and PHP FPM. 05:36.770 --> 05:40.220 We could check each one individually, but fortunately we can use one. 05:40.220 --> 05:44.120 Call the system CTL and check them all at the same time. 05:44.630 --> 05:49.580 To get the actual info we need, let's pipe the output to grep and look for loaded and active. 05:50.160 --> 05:53.910 This output shows us that each service is running an active. 05:54.500 --> 05:55.970 We're good to go. 05:56.480 --> 06:01.580 If you're following along and your output differs from this, review the installation for each service 06:01.580 --> 06:03.140 and make any corrections. 06:03.710 --> 06:06.470 Now let's install the PHP script. 06:07.110 --> 06:12.840 I'm using the exercise files so I can find the script in the vagrant directory, which is synchronized 06:12.840 --> 06:15.990 with the directory where I started the VMM with Vagrant up. 06:16.560 --> 06:20.340 The script is in the file named index PHP. 06:20.820 --> 06:26.430 We won't be going into too much detail about the code, but let's take a quick look at a few lines inside 06:26.430 --> 06:30.390 the script and I'm going to go to line 97. 06:30.930 --> 06:36.660 I wanted to point out the lines where two components of the stack come together, the PHP and the MySQL 06:36.660 --> 06:37.590 database. 06:38.190 --> 06:43.710 On this line we see where PHP is using the my SQL, I underscore connect function to connect to the 06:43.710 --> 06:45.450 database on local host. 06:46.020 --> 06:49.950 The user and password are there along with the name of the database. 06:50.630 --> 06:52.850 A few lines down on line 102. 06:52.880 --> 06:55.250 We see where PHP uses the my SQL. 06:55.250 --> 07:01.400 I underscore query function to get all the rows from the data table of the appointment database and 07:01.400 --> 07:06.230 on line 110 PHP prints the data as HTML. 07:06.860 --> 07:08.630 Of course, this is the content. 07:08.630 --> 07:12.890 We'll see when the page is served by Engine X and loaded into our browser. 07:13.460 --> 07:16.910 Now let's put this script where Engine X can access it. 07:17.420 --> 07:21.830 We'll make a directory inside the root of the website and call it appointments. 07:22.400 --> 07:26.690 And we can copy the index PHP file to this directory. 07:27.230 --> 07:32.390 We also need to set the permissions on the file to make sure the Engine X process can read it. 07:33.050 --> 07:33.560 Okay. 07:33.680 --> 07:35.120 Everything looks good. 07:35.600 --> 07:41.120 Now is a good time to point out why the file for our application is named index PHP. 07:41.660 --> 07:45.140 Let's take a look inside the configuration for our site. 07:45.740 --> 07:52.010 Engine X is configured to use index php as one of the files that gets loaded by default when engine 07:52.010 --> 07:53.570 x serves a directory. 07:54.190 --> 07:59.260 By naming our script index PHP and then placing it in a directory named appointments. 07:59.260 --> 08:04.300 Anytime someone requests the page appointments, the app will get loaded automatically. 08:04.300 --> 08:05.620 Pretty slick, right? 08:06.130 --> 08:12.550 We could have used another name for the script appointments dot PHP for example, but using index php 08:12.550 --> 08:17.770 makes things easier to manage if the appointment location needs other directives apply to it. 08:18.310 --> 08:20.710 Now let's take a look at our web app. 08:21.310 --> 08:24.760 And in a browser, I'll go to the IP address for our development server. 08:24.760 --> 08:28.900 192.168.0.3 and go to appointments. 08:29.440 --> 08:32.890 We can see our application, but there is something missing. 08:33.370 --> 08:38.860 And this error tells us right away there's no data in our database. 08:39.420 --> 08:40.830 Let's fix that. 08:41.430 --> 08:43.200 In the slash vagrant directory. 08:43.200 --> 08:46.920 There's a file named appointment underscore database dot SQL. 08:47.490 --> 08:52.230 This contains the data we need and the commands to insert them into the database. 08:52.800 --> 08:56.430 Before we do that, let's take a quick look inside this file. 08:56.970 --> 09:02.640 In the SQL file, we can see on this line the very first line where the table gets created along with 09:02.640 --> 09:03.870 the names of each column. 09:03.870 --> 09:07.350 And on line 13 is where the data insertion begins. 09:07.910 --> 09:12.590 The rest of the lines in the file are the rows of the data that make up the database. 09:13.130 --> 09:15.350 Now let's insert the data. 09:15.890 --> 09:21.830 We'll use a standard in redirect to send the contents of the SQL file into my SQL command. 09:22.370 --> 09:27.890 And we'll use my SQL U and then the admin user P, and then we'll specify the admin user's password 09:27.890 --> 09:30.620 on the command line so that we're not prompted for it. 09:31.230 --> 09:36.450 And then we have to specify the name of the database that we want to inject this data into, which is 09:36.450 --> 09:37.320 appointments. 09:37.860 --> 09:41.730 And now we can redirect our SQL file into this command. 09:42.270 --> 09:45.660 Now let's go back and refresh the appointments page. 09:46.200 --> 09:47.670 There's our data. 09:48.180 --> 09:52.500 Now that the database has some content, the page loads without an error. 09:53.070 --> 09:57.570 Now we can say we've successfully applied each component of the linked stack. 09:58.080 --> 10:04.650 We added some dynamic content to our static site by applying some PHP code and some database content. 10:05.190 --> 10:07.020 But we're still not done. 10:07.500 --> 10:12.540 There are other things we can consider for our site, with security being the top priority.