Learn how to log into MySQL from the command line to create and drop a database.
- [Instructor] In this movie, we're going to log into MySQL and create our first database. In order to interact with MySQL, we're going to do that from our command line program. If you're on Windows, that program is going to be command prompt, on Mac it's going to be the Terminal. So here I am inside my Terminal application and the first thing we want to do, is make sure that we have MySQL installed. So if you don't have it installed already, you'll want to do that. Let's just check and see here, mysql version. You see I have Version 5.7 installed. It doesn't matter if your version is slightly different from mine, MySQL in general works pretty much the same in all the versions.
Then we want to actually try to log in, to make sure that it's running. We try mysql -you and I have a root user created. Every MySQL installation has a root user by default. I'm going to hit return, and you'll see that it doesn't open up for me, and that's because I didn't tell it that I had a password. By default, a lot of times MySQL doesn't have a root password, but it's a good idea to make sure that you do have one. Secure that root user, because the root user is your most powerful MySQL user. It's the user that has the ability to do anything inside MySQL.
So I'm going to also put the -p option after it, and that's going to allow me to provide a password. So now it's going to ask for my password, and there it is. Now I'm logged into MySQL. So I know I have MySQL installed and it's running. So now we're ready to work inside MySQL. What are the commands that we want to issue? There are four main commands we need to know about creating databases. The first is show databases, and that just simply shows us a list of the databases that are already there.
Notice that it has a semicolon at the end, all commands in MySQL should end in a semicolon. That's how MySQL knows that the command is done. The capitalization doesn't matter, but it's a best practice and it makes your code more readable, so I tend to capitalize things. The second command is to create the database. All we have to do is tell it the name of the database we want. Create database and then the name of the database. Capitalization does matter for the database name and there should be no spaces in it, but you can use underscores. Then, when ready to actually use the database, we can use database name.
If we want to drop the database, or get rid of it, you use drop database and the database name. Use database name is really only if you didn't specify the database when you logged in, or if you want to switch between databases. It's not something we're going to use very often, but if you have multiple databases, it allows you to move around between them, so that you're currently in an active database. So to begin with, let's just do show databases. You can see the databases that are there by default. These are the default MySQL databases. They're not for your use, they're there for MySQL to use, so don't mess with any of these basic ones that are there.
But we want to create our own. So create database and then let's call it globe_bank; That's it, that's all it took to create the database. Now I'm going to hit the up arrow. It'll allow me to go back to show databases again, and now you can see that my database is listed there. As I said, we can use the database, and that will switch us into it, so it's our current active database that we're working with, and we can also drop the database. Drop database globe_bank; and just like that, our database is gone.
Now hopefully we didn't have lots of data in there, because as soon as we say drop database, you can see, everything that was in that database just instantly disappears. It's a very powerful command. So we want to be careful about that. I'm going to hit the up arrow a couple of times, so that I can create my database again, and now show databases, you can see it's going to list it again. So now I have my database there. Now when we logged into MySQL right now, we did it as the root user. That's the most powerful user with lots of privileges to perform actions inside MySQL, from creating tables to dropping them.
We could log in from our web application as the root use as well, but I think that's a really bad habit. It's much better to create a new MySQL user and assign it a password, and then grant access to our database to that user. It limits the scope of access, to just that single database. Now, we don't have access to everything, we only have access to the database we've granted privileges on. It's better security and there's no excuse not to do it, because it's easy to set up. All we have to do is type a command like this, grant all privileges on, and then the name of the database and then a period and then an asterisk.
The asterisk says we're granting privileges to all tables on that database, and that's a best practice. Then there's a space after the asterisk, that you can't see because I dropped to a new line, and then it says to, and then we want to create a username. This is going to be a new user that we're creating. It can be any name you want, inside those single quotes and then @ and then the IP address, or the location that that user would be allowed to log in from. We're going to use localhost. That means that only from this computer can that user log in.
They can't log in from some other foreign country, some other IP address and get into this database. Only our web app, which is locally hosted, can access it. That's another good security practice. Then there's a space at the end of that, and then the next line is, identified by password. If this user already exists in the database, we can leave out that last bit, but if it's a brand new user, this gives us the chance to say what the password is that goes with that username. We can also take a look at what grants have already been given to that user, using show grants for that username.
All right, so let's try adding a new user and granting privileges on our database to them. So again, my command is going to be grant all privileges on, and then the name of the database, globe_bank. and then the asterisk, because it's all tables, to, I'm going to call my user webuser. You can call it anything you want, and then @localhost, and then a space, identified by, and then whatever password you want to use.
I'm going to use secretpassword, just to have a convenient one, but that's not a great password. You should hopefully pick a better password. So pick a good password that you can use for your user, and then let's hit return. Now we have a new user created called webuser, and that user can use their username and password to access our globe_bank database.
- Organizing project files
- Including and requiring files
- Working with URL parameters
- Encoding dynamic content
- Modifying headers and page redirection
- Creating forms and processing form data
- MySQL basics
- Using PHP to access database tables
- Creating, reading, updating, and deleting database records with PHP
- Validating data
- Preventing SQL injection