Amazon Lightsail Training Series: Course 3, Using MySQL and phpMyAdmin for dynamic sites. | Chase Raz | Skillshare

Amazon Lightsail Training Series: Course 3, Using MySQL and phpMyAdmin for dynamic sites.

Chase Raz, University Instructor, Corporate Trainer

Amazon Lightsail Training Series: Course 3, Using MySQL and phpMyAdmin for dynamic sites.

Chase Raz, University Instructor, Corporate Trainer

Play Speed
  • 0.5x
  • 1x (Normal)
  • 1.25x
  • 1.5x
  • 2x
5 Lessons (32m)
    • 1. Amazon Lightsail Training Series: Course 3 Introduction

    • 2. Connect to phpMyAdmin

    • 3. Create a MySQL User in phpMyAdmin

    • 4. Change the MySQL Password

    • 5. Course 3 Class Project Demonstration

  • --
  • Beginner level
  • Intermediate level
  • Advanced level
  • All levels
  • Beg/Int level
  • Int/Adv level

Community Generated

The level is determined by a majority opinion of students who have reviewed this class. The teacher's recommendation is shown until at least 5 student responses are collected.





About This Class

Note: This is the third course in the Amazon Lightsail Training Series.  All courses are available to you with your paid subscription to Skillshare.   

In this brief course, we'll look at how to access and utilize MySQL via phpMyAdmin in order to begin creating dynamic sites on our Amazon Lightsail VPS Instance.  Up to this point, we've created and hosted static websites, but by having MySQL available to us as a database, we can begin hosting dynamic sites.

About the Instructor

The Hosting Websites with Amazon Lightsail training series is taught by Chase Raz, a University Instructor, Corporate Trainer, and Consultant with nearly a decade of experience teaching adult students, both academically and vocationally,  in the disciplines of business, technology, and marketing.

Meet Your Teacher

Teacher Profile Image

Chase Raz

University Instructor, Corporate Trainer


Chase Raz is an instructor of digital marketing and business technology at Full Sail University and a corporate trainer for Polk State College's Corporate College. Chase has almost a decade of experience teaching business and technology topics to a wide array of adult students; from college students to executives. It is Chase's mission to help others achieve their desired success within business. Chase also owns RCR Business Ventures, a firm specializing in venture development.

A.B. Gamma is the leading corporate training solution from RCR Business Ventures. A.B. Gamma provides business and technical training for business owners, executives, knowledge workers, and almost all other types of professionals. The "Chase Raz" brand of training is the flagship training product from A.B.... See full profile

Class Ratings

Expectations Met?
  • Exceeded!
  • Yes
  • Somewhat
  • Not really
Reviews Archive

In October 2018, we updated our review system to improve the way we collect feedback. Below are the reviews written before that update.

Your creative journey starts here.

  • Unlimited access to every class
  • Supportive online creative community
  • Learn offline with Skillshare’s app

Why Join Skillshare?

Take award-winning Skillshare Original Classes

Each class has short lessons, hands-on projects

Your membership supports Skillshare teachers

Learn From Anywhere

Take classes on the go with the Skillshare app. Stream or download to watch on the plane, the subway, or wherever you learn best.



1. Amazon Lightsail Training Series: Course 3 Introduction: either everyone, I'm chase rats and welcome to course, three of the hosting websites with Amazon lightsail training. Siri's here on skill share. We're gonna be looking at how to connect with my SQL and with PHP my admit. Now I try to make these courses as modular as possible because admittedly, some of you may not be using Amazon lightsail you could be using digital ocean or maybe something else on Microsoft, Azure or Rackspace or who knows what else. But at the same time, this is the first course where we're going to rely on some previous information. In order to complete this third course in the Serie successfully, you need to have an ssh tunnel connected to your VPs instance. Now we talked about how to do that back in course to In fact, there was an entire video training on how to create ssh tunnels on Windows devices using putty. For those of us who are in Amazon lightsail, it's going to be absolutely required for us to have this ssh tunnel in place. Now we can work around that in the long run. The reason we need this is because PHP minded men in the bit NAMI lamp stack is configured toe Onley except Loggins from local host. That means we have to tunnel through the Internet using Ssh! And appears if we're on our virtual private server. Yes, it's completely possible to change PHP Mind Mints behavior and did not require that some other services maybe digital ocean or azure, as I mentioned before. Maybe some of them don't require that, and especially if you've built your own lamp stack. That's probably not something that you've put in by default. But the point is, for those of us on Lightsail and for many others who are using Vietnamese lamp stack, we're going to have to go back to course two and make sure we've at least cleared the level of creating an ssh tunnel. OK, so assuming that that's done, let's move on with this course. The only other thing I want to tell you is that I'm going to give you two different methods for creating a user within my SQL. I'll give you a method within the course proper, and then, as the very last video of this course, I'll give you a preview of the class project for this course inside of that preview that demonstration you'll find I use a completely different method that I've never talked about before. This is okay, and that's intentional. Knowing both of these helps you understand what PHP my admin is doing and furthermore, how my SQL is managing authorizations behind the scenes user names, passwords, all of that. They're actually stored as a my SQL table inside of my SQL. So I'll be demonstrating that. Don't let it throw you off. The instructions for the class project are pretty clear that you can use either method the one described within the course proper or the one in the demonstration video. All right, so sit back, relax, enjoy this section and I'll see you in the class project if you need any assistance or are ready to gloat and show off that you've completed this portion of the hosting websites with Amazon Lightsail training, Siri's on skill share 2. Connect to phpMyAdmin: we've been able to host one website and we're now able to host multiple websites using multiple domains and we still aren't able to create a dynamic website. And the reason is we have my SQL, but we don't have access to it. And PHP my admin is the tool that we often use in order to manage my SQL databases that leads to not have to do so from the command line. So we want to gain access. We know that on our server it's located at a directory called PHP Mayan Men. But if you remember back toe conversation to, we've already had this method I'm using here of using my static I p address slash PHP. Mind men is never going to work. This is never going to work because we have to use ssh tunneling to get through and connected PHP mind men. At least that's how PHP my admin is set up on the server. You can at your own risk and at your own Ah, leisure. Go in and change that setting within PHP my admin that is beyond the scope of our course. We want to use the officially sanctioned way of connecting So let's go ahead and just get rid of this tab right now. We're not even going to attempt at this point to get through and connect using our static I p What we're gonna do instead is used local host and remember, local host is 1 27.0 dot 0.1. I'm just substituting this. Browsers understand local host as that I p address. So we want to connect using local host Port 8888 ford slash PHP mind men Now remember, for 8888 is what we sent up earlier as our ssh tunnel over to port 80 of our server. Now what I have type here is actually never going to work. PHP My ad men, at least for the purposes of accessing it through the directory here is all lower case and you can see not resolving either. I don't have the ssh tunnel connected And so that's what we're going to do now and that we are unable to connect. No big deal. So we've talked about this before. We've skirted around this issue and we know the two components that are required. First we need a launch party and establish our ssh tunnel. So I'll select my buddy configuration for my A W s light cell demo. Remember what you called yours? Probably something different. Open that connection and I'm gonna log in. I needed a log in for two reasons. Here first is because this log in generates the ssh tunnel between my computer and the VPs , and I can prove that here, I still actually could connect the PHP mind men. And we've done that before. I just can't log in yet. But that brings me to the second point. We need to come back to our command line so that we can get our application password. Now, we've seen where this is before. We've just never utilized it right here on the home directory. When we log in, we have a file called Bit NAMI application password. And before I've used Nano, the the lightweight command line text editor to view the contents of this file. I don't want to go through all that work right now, so I'm just going to use a Lennox command called Cat, which is concoct nation essentially. And then I'm gonna pass the file name. That's quite a bits type and there we go. So what can't does is basically it's used for contaminating multiple files together rather than text contents of multiple files together. But since I only passed one file, it's just gonna return that password that stored in here. So this what you're seeing on screen right now is for this particular instance that I'm deleting after I finish these recordings. This is the password, the application password four PHP, my admin and my SQL someone I'm actually gonna copy and paste this that will work from putty. Here I get my control, C command, but it will work now. This is the trick. So far, we have used But NAMI as our user name, it is not going to work for us here. If I put my password in and the user name but NAMI and say go, it's just there is no user but NAMI as a part of PHP ID men. Now the reason that IHS PHP and Men is using route This is gonna be a problem for a lot of us. I'll show you in upcoming videos how to change this, how to create user accounts. But I'm gonna use the user name route and my application password and say Go not going to save that information. And here we are. We are now for the first time in PHP, mind meant you can set up my SQL databases. You can do a maintenance on your databases that you've created. But once you've done that first step and you can create new accounts now doing all of that . The Web development side is all beyond our scope within these videos. But I do want to show you inside of this user interface for just a moment somewhere. We're going to be looking in a video or two from now, and that is right here at the top center under user accounts. And this is what I'm verifying for you. That route is our only user name. That's all we have right now. And you can imagine in the future we're gonna come right down to this new section and add a user account. So let's go ahead and rob this video up and move straight into that next 3. Create a MySQL User in phpMyAdmin: All right. So if we have ssh tunneling turned on through putty or some other type of ssh software that supports tunneling and we've longed into PHP my admin using local host Port 8888 ford slash PHP my neman. Remember, you have to use route as the user name and your bit NAMI application password as the password. But once we're here, we can click on user accounts, create a new user account by selecting add user account, and we'll simply fill out this form. The user name I'm going to create is called test user, which is not a very could user name to use. Please don't use that make up your own and next up, we're going to be asked what the host name is now. I like using these default options. We can believe it or not for someone to only be able to log in using our local account. But PHP my Adminis already set their by default. So I'm gonna leave this to any host for now. We can then set a password. I'm gonna do something really horrible here so horrible I won't even tell you what it is like. How Look how horrible that. You know what? I can't even do that. I can't do that to myself. They were, you know, a little bit better. I hope I remember what that is to and then ah, re type it great. No, If I didn't have a particular password I wanted to set, I could have my obscure create one for me. Using either the native my sq authentication plug in or an S h A to 56 plug in And what I can do here. Select either one come down below to generate, password to select, and a password will be created for me. And by the way, it's automatically put right up here in password and retyped field as well. So I want to copy that for a moment because actually, I like this. I like a strong password. I like this better than what I put in. The problem is, I'm not gonna remember it so temporarily. I'm a place that on my note pad. Don't tell anyone I'm doing this. Said one secure, but I'm temporarily gonna put that password on my note pad is the next time we're going to be asked about the database for the user account. Do we want to create a database with the same name and grant all privileges? So essentially, this would be if you're gonna have multiple users and each user has their own database. This is something you may want here. Or we can grant all privileges on wild card name. I'm gonna skip all of this right now, because again, this isn't a my SQL or PHP. My ad men tutorial. I'm simply trying to create the user account at this moment. So I'm going to skip that. And since I'm honestly just creating this count for me and it's gonna have the same settings as the root user account, I'm going to say check all here now I'm kind of being a little bit bad. I want to be clear why I'm creating this account for myself rather than just using route. I know that in the Lenox world, war taught, never used route don't use root level access. But in all honesty over here in PHP, my admin, it's simply that the user name is root. Somebody needs toe, have all privileges in PHP. My admit and it doesn't matter what you call that person, whether it's route or whether you create the name like I did of calling this person test user, that doesn't matter. So again, this isn't quite the same. Is using root level access in Lenox four Lenox operating system procedures. This is creating a top level connection from PHP Meid men to my SQL. So if you're perfectly comfortable continuing to use the name route, feel free to use that instead, there's virtually no difference. Why you'd really want to use something like this is if you're like me and one a particular user name, or if you just want to be able to access and have all of the same controls that the root user name has but again want that different user name one a different password, that type of thing. This isn't gonna be for security reasons, because again, route in this instance is just in name only. So finally, at the very bottom we consent. Whether someone needs to connect over SSL or not, we do not need to do this, Believe it or not, because we're not accessing through the Web, we're not accessing through my domain, a b gamma dot com or anything like that. In fact, that won't work. Watch took me, Want a type of there, and I try from here to go to PHP my Edmon. It's not going to work and think Why? For me, this is because I have this domain pointed to a folder called a b gamma dot com. But even if I didn't even if I had this domain forwarded simply to the H T docks voter of Apache, it still wouldn't work because I'm required to connect through local host on the VPs. And that means I have to ssh tunnel through from my local host. So all in all, that's just a lot of wording to say. We don't need to worry about this SSL here unless we do plan to allow people to log in over the Internet. All right, so I've got my test user. My password is generated. I'm simply going to say Go perfect. I've added a new user, so I'll go to log out, which is I always find that to be a funny little place there in PHP. Mind meant test user. I have my password still on my clipboard and we'll say, Go there. We are whatever user name, password combination you want whatever level of privileges you want to give that user. Here I am test user. You can do right here from user accounts as soon as you sign in to PHP my men. 4. Change the MySQL Password: whether you're still using the root user name or if you've created a new PHP meid men and my SQL user name is kind of irrelevant for what we're going to do next. What we want to talk about now is how do we reset these long and complex passwords that we have and go to something a little bit easier to remember Now there are a couple of different ways to change passwords, and I'm gonna walk through a few of them for the first example. I'm gonna use my test user. Use your name. I'm not actually going along in here to PHP might, man, but I'm going to use a test user user name and next all backtrack. And I'll change the password for route as well. The first method I'll use for test user is on pullup putty and I will connect to my server . What's word I couldn't type yet once again. All right, So once I'm connected to the server, let me clear this. So we have a little bit more real estate. I'm gonna use a my SQL command right here from the command line. And then command is my SQL at men pretty aptly name, isn't it? Now, with my sq odd men, we're gonna have to do a few things here before we can hit Enter. First of all, we're gonna have to flag with the letter P. So, hyphen P, this is going to indicate that we need to provide a password to log in to the my SQL server in order to do whatever it is we're about to type in. So next up after the P, we're gonna have to do a U flag to indicate that we are using a different user name. Then our Lennox instances. Right now we're logged into Lennox as user name bit na me. That is not our my SQL user name. So we'll do flag you and I'll provide that user name here. It's going to be in this case test user. Remember, this is a test user that we gave full privileges to just like the root account so you could do root here instead. Now, after all of that, I'm gonna indicate that we're going to use a feature called Password. And what password does is changes the password for the user were logging in, as at this point We also want to pass in our new preferred password. I'm gonna do something horribly generic. Please never do this in production. Don't even do this on your development servers. But I'm gonna do something simple. Like pass 1234 How generic is that? Right now when I hit enter, I'm gonna be prompted for my existing my SQL password for test user and bear with me here. I'm gonna have to type this out. De que h m g n seven h k h a n t. While this is awful, I'm leaving a few things out as I not for any security reason, but wow, that was a lot to type C. And this is why we change your passwords. We don't wanna have to do that. We can get secure passwords without having to go through all of that. So I am given a warning saying that using a password on the command line interface can be insecure. It can be We are connected through Ssh. I'm not overly worried about that right now, but, um, Lennox is giving us that warning. So at this point, I'm taken back to the command line and take a look at what can happen here if I come back to PHP. Mind men. User name, test user. Now I can use pass 1234 and had go, and I'm gonna be logged in. Isn't that wonderful? Well, okay, the passwords not wonderful. But the fact that we can change the password so easily I'm gonna log back out. And just to demonstrate, I'm gonna take a moment to type that horrible, horrible password that I was fighting with and verify that I can no longer use it. Toe log in and I can't. There we go. And I am accurate that I I'm 100% confident I typed it correctly, you know, just in case. Let's do this. Let's copy and paste it. Absolutely. Password has been changed. Now, what's the other method we could use? Right. So now we've changed Test user to pass. 1234 I think maybe we should do something really horrible and changed the root password to pass. I don't know. 7890 again. Please, never do this, But we're gonna go back to the command line and I'm gonna clear just for some space here Now what we want to do on the command line is actually log in to my SQL as a program and how we do that as we type my SQL. We Of course, you're gonna have toe have my school prompted for a password. So flag it with P hyphen peak. And we want to log in with a different user name than our Lennox user name. We're not logging on Ben Ami were logging in with in this case route, so we're gonna have to enter a root password. And remember, this is what we got by calling that file. But NAMI application password from our home directory. All right, so I'm gonna type that in very quickly. Mine was a really complex string. All of them are as set by lightsail. All right, so now once I've typed that password in and hit enter, we have my SQL running here on the command line, and this is the second way we can change the password from here within my SQL. We're actually going to get to use sequel in order to do that. So we're going to simply do a sequel command like set password. Which password is therefore going to be a field within one of our databases. Four. And we're gonna type in in this case route and follow this nomenclature. Set password four. We're gonna put the user name in single quotes at that. I'm gonna type of local host because that's how we're accessing. No, If I were doing this with my test user account, remember that I said the test user account, not toe local host, but to the wild card. So in that case, if I were doing, I test use your account. I would use wildcard symbol there, but I'm gonna change route at local host right again. Local host is within single quotes here. So would the wild card if we were using that. So what do we have on this set? Password for the user will say equals password. And we're gonna actually type the password within this area. So password is simply encoding the password or encrypting the password rather. And what type of clear text password right here in the middle. And remember that bad password I want to dissent was passed 7890 for route in the single quote and the parentheses. And since we are in sequel. We do have to end the line with a semi colon. There we go. And don't be do freaked out right here that it says query. Okay, but zero roads affected. That freaks me out almost every single time. But look, what happens if we come back over to PHP. Mind men and change your user name to root. We can now log in with past 7890 So say go. Don't say the password. And here we are. We're in. If I were to log back out and try my old password, someone is going to copy and paste it here for my own simplicity. It no longer works. So even though my excuse telling a zero rose affected, that's perfectly fine. We are still updating the password. So either way, you can update your password directly from the Lennox command line using my SQL and men, or you can log in to my SQL and use SAT password. All things being equal them. I'm not necessarily sure that we need to change the default application password. What I'd suggest doing more than that is going into PHP. Mayan men using the user accounts a section and creating the user accounts that you would like to utilize in order to connect to your mind SQL databases from PHP or whatever other mechanism you're using and set those with the appropriate privileges. That's my suggestion. Keep route, keep the default application password and then established other user accounts. Let's say you establish additional user accounts and then you need to change the password. What I'm about to show you actually work on your route accounts as well. But again, the idea is you create an account. You want to change the password you can within PHP. My ad men simply come to that user account. Select edit privileges. A change password option will be near the top of the screen, and you could enter a new password. I'll just enter a password of password here and you can see the strength is extremely weak . Or we could have a new password generated for us using the my SQL authentication that we talked about before or the S H A to 56. So this is probably not not to save the best until last here, but this is probably what you're looking at doing go to user accounts, added the privileges for the user. You want to modify and set a new password, But just in case you ever do need to change your route password or your password for any other My SQL user. This video should help you in doing that. 5. Course 3 Class Project Demonstration: All right. So for the class project here in the third class of the hosting websites with Amazon lightsail Siris of trainings, I want to give you a little bit of behind the scenes example here rather than just going, you know, with the typical screenshots. So if we are in PHP my admin and we want to add a user to my SQL, you can see that my SQL is a database on the left hand side as soon as we log in. If I expand that database to get all of the tables inside of it, the one at the very bottom because this is alphabetically ordered is going to be called user. When I click on user, look what we get here. We get a listing of all of our user names that are able to access my SQL the host. So if there's a host limitation on how they have to access, we see for root, of course, a local host of limitation put in several different ways, quite frankly. And then 14 um, for our ssh client to be able to connect our Amazon ssh client. But anyway, we've got our user name. We've got our passwords that are encrypted, and we have all of our user permissions. So if I want to add a new user to be able to access my SQL and I want to be able to, I don't know, maybe you some type of dynamic PHP script, to to use that user name and password to log in and pull information from my Web application. Then all I actually have to do is add a new entry into this table, and I'll do so by clicking. Insert. Now, I should say, if WordPress or any of the other application modules or even other images like the WordPress image or the Red Mind image If those are your primary use of lightsail, you're probably never going to find yourself here doing this. This is for people who are writing custom scripts or for, you know, putting on custom applications that are not provided as modules to the bit NAMI lamp stack . This is for the rest of us who need the custom control if you are just using WordPress or something similar. But NAMI will create these users and passwords for you, and you never even have to touch them you just use your regular route or you use another account you've created on your lamp system, and you never have to touch this. So you'll see things that are in fact, let's take a look and see if I have any. Yes, he's a WordPress right here. Gives me bit. NAMI bian. Underscore WordPress. This is my Ben Ami WordPress user name. If I were to create another wordpress instance had come in with two more user name. Right. We're back to the insert tab and all we really, truly have to do. I'm going to encourage you to do more in just a moment. I'll tell you why, but I'm not covering. That is we need to create a new user. So I'm gonna say my new user is John Doe, and the password is had something horribly simple. I am John Doe. Don't ever do that in production police. I'm deleting this user as soon as I create him. So then we can set all of our user privileges. I'll leave this to you, because again, this is not a course on my SQL. It's not, of course, on PHP. Mind minutes just on using a lamp stack through Amazon lightsail to send all of these things up. Now I will. The thing I want to caution you and I want to tell you that you may want to look into doing is setting some of the SSL an x 509 Information X 509 is a part of managing your private keys for SSL here in terms off server connection. So the reason I'm bringing this up is if I send this record to the table right now John Doe and his password, I am John Doe vice in that I'm gonna get a couple of warnings or errors. I'm not sure which way they actually come out, but it's That's because these fields right here these three blobs don't have default values . Of course, they don't have devote to fall values right. There's nothing to default to. We're only using this if we are going to ensure over SSL now. I'm not doing that right now. Just to show you how to and create a basic user, get the screenshot. This user will still work. They just won't be required to connect over SSL and won't be authenticated in that way. So a little less secure here, but for, you know, for some of us, that's that's gonna be fine. You may be doing that. I'm gonna leave that to you and your development team if you have one or the suggestion of the software you trying to install. So again, that's a lot of long one to talk to, say, user name, password go. They're those warnings as the cell cipher. Exe 509 Issuer x 509 Subject. They don't have default values, and that's okay, right? So if I go back to browse, we see John Doe here at the bottom of our list. And the password him, John Doe. It's not even encrypted, right? This is something that you're gonna want to pass through. You're gonna want to encrypt this password, and there are a number of ways to do that. Sometimes you'll encrypted before, but other than that, when you insert you can call some of the my SQL functions here, maybe for which whichever type of encryption you like you used with my SQL I'm not a security expert, so I'm not going to suggest that I typically rely on the application documentation the application I'm trying to install all rely on their documentation to know what type of what type of security they re require or request. So where we're going with this activity now that have John Doe and is horribly unsecured because it's not even encrypted password is I want to be able to show that I've created this user. I want to show that I can get in here and manually start the process of adding a new user and then securing it down the road. But I don't necessarily want to give you my password, because what if its user that I'm gonna leave around for a day or two? What if What if it is encrypted here? And this isn't a trash and burned server like I, as the instructor have? Well, here's what I'll suggest you dio go to the sequel tab and we're gonna use this little queer here. I'm going to select the field users our excuse me, just user field user. It doesn't matter if I capitalize it or not, but I'm tryingto make everything consistent here now with what I see in the columns. So select user from user, which is the table name will say, Go in. Here we go. We get a list of just the user names. So take a screenshot of this. If you're on Windows, something like, uh, the snipping tool. Just grab a little screenshot of it, and you should be good to go for the assignment. All right? Take care. You know, actually, just one more thing, because I'd feel really remiss if I didn't do this talking about security in the patch word . Most of the time you are going to create a user name from within an application, right? So maybe you install a an application that's not available as a bit NAMI module. Great. Fine it a lot of times. Once you give it access to a database right, giving it the other user name and password, it can create stuff for you. But a lot of times the applications we right? Or for just using some maybe an open source software, and we don't want to utilize our root level access initially to even do that or whatever the case is. I do want to give you information that we can very easily encrypt this password without ever having to leave PHP mind men. So I don't like this being here the way it is. I would never operate with this at all. So, um, I'm just gonna delete John Doe, and I'm gonna recreate him. Just nagging me that much. John Dome. And then I am John Doe. Awful name, but over to the left. This is why I'd feel remiss, because it's right here, and I just wasn't bringing it up, because, again, this isn't a my SQL course. This isn't a PHP might men course, but, um, some of our options are in here now. I'm just going to choose the standard password option. These are some functions that we can assigned to this password. The password field. Let's see. Where's Pat? There it is. Now, I'm not going to go into the merits of whether you should be using MD five or S h a one or right. I'm gonna leave that for you. What we all know don't use MD five, but, uh, I'm gonna leave that for you and your security team. Or maybe you are your security team and just the best advice of the people around you. But I'm just using the straight up password tool here when I say go. Still going to get my warnings because I'm not setting any SSL. And then when eyebrows. Here we go. John Doe now has a hash in place of the password field. So again, I'd feel remiss if I didn't only show you that. So good luck with this project. And now I'll see you in the discussion in the conversation for our class project.