Using MySQL from Command Line

Video loading...

  • 0:02
    Using MySQL from Command Line with Addison Berry
  • 0:08
    Hello there, in this command line video,
  • 0:10
    we're going to be looking at working with a MySQL database.
  • 0:14
    And so, we're going to look at how to get in,
  • 0:16
    take a look around, and do some queries,
  • 0:19
    all from the command line, rather than using a GUI
  • 0:22
    interface with something like phpMyAdmin.
  • 0:24
    So to start off, the first thing we need to do,
  • 0:27
    is actually get in there.
  • 0:27
    So mysql is the command and if I did just that,
  • 0:31
    I would go into MySQL and be able to do some things,
  • 0:35
    but I want to work with specific databases,
  • 0:36
    which means I need to have permissions,
  • 0:38
    which means I need to be a particular user.
  • 0:40
    So -u will let me put in the username
  • 0:43
    that I would like to connect to MySQL with,
  • 0:46
    and so I'm going to go in as the root user here.
  • 0:48
    And this user does have a password, and so I do -p,
  • 0:53
    and I can type the password in, but normally,
  • 0:56
    it's best practice to just use -p,
  • 0:59
    instead of typing the password in.
  • 1:01
    In plain text, it will then prompt me and I can type it
  • 1:04
    in at that point, so someone looking over
  • 1:06
    my shoulder won't see the password in plain text.
  • 1:09
    You'll notice I now have a MySQL prompt which looks
  • 1:13
    quite a bit different than my regular shell prompt here.
  • 1:15
    So I know that I'm actually in the MySQL world at this point.
  • 1:21
    I've also logged in as a privileged user who can do
  • 1:24
    everything in MySQL.
  • 1:28
    So I can create databases.
  • 1:30
    You need to be in this user with proper permissions
  • 1:32
    to do the things you need to.
  • 1:34
    Now, to create a database, create database,
  • 1:37
    simply what we need to do and then I type a database name,
  • 1:40
    and then I put a semicolon at the end here.
  • 1:45
    And that creates the database.
  • 1:47
    It's that simple when it's very straightforward.
  • 1:50
    So we can take a look and see what I did
  • 1:53
    and show databases will list all the databases
  • 1:55
    and you can see that foo is listed here now.
  • 1:58
    It was that simple to create it.
  • 2:02
    Now, once I have the database created,
  • 2:04
    the next thing you typically want to do,
  • 2:06
    especially when dealing with web applications and such is,
  • 2:08
    create a particular user for just that database so that
  • 2:13
    I don't need to put connection information from my root user.
  • 2:15
    So I'm going to grant all permissions on and then
  • 2:20
    I say which database, foo, and then the . *
  • 2:22
    means all tables within it.
  • 2:25
    I'm going to grant those permissions on the foo database
  • 2:29
    to a particular user.
  • 2:31
    So, I'll put a username in here, whatever username I want,
  • 2:35
    I'll call them bardev. And you'll notice I'm putting
  • 2:38
    that inside those little quote marks, and then @,
  • 2:43
    and then I need to give the location where this user is
  • 2:45
    going to be connecting from, which is localhost most
  • 2:49
    common place, unless there's a separate database server with a
  • 2:52
    different IP and other things, but localhost is typical.
  • 2:56
    And then the last thing I want to do is give this user
  • 3:00
    a password, so I'm going to say, identified by,
  • 3:04
    which will signify the password and then I can type
  • 3:07
    the password in as a string.
  • 3:09
    So I'll use the word secret, and again,
  • 3:12
    do my little semicolon to end that.
  • 3:14
    So, I'm granting all permission on the foo database, all tables,
  • 3:18
    to the new user, bardev, from localhost, is the server.
  • 3:26
    And then I'm setting up the password as a secret,
  • 3:31
    and you can look these kinds of things up,
  • 3:34
    but I just want to show you what this one string creates
  • 3:37
    the new user, sets the password, and gives
  • 3:39
    them all permissions on the database.
  • 3:41
    And so that's now down, and if I exit out of MySQL here,
  • 3:46
    and I'm going back to my regular shell,
  • 3:47
    I'm going to log in, back into MySQL as this new user,
  • 3:51
    just to show you that it works.
  • 3:52
    So the user is bardev, I'll type in my password.
  • 3:56
    And now, if I do show databases;
  • 4:01
    now, instead of getting that big list,
  • 4:03
    I'm just getting the list that I have access to as bardev.
  • 4:08
    So you see foo and some basic MySQL databases in there,
  • 4:12
    and that's it.
  • 4:13
    That's all I have access to.
  • 4:14
    So, I'm going to go back in as the root user here.
  • 4:18
    So I have all the permissions.
  • 4:21
    And another thing, a major thing we want to do with
  • 4:24
    databases is to get rid of them.
  • 4:26
    So instead of creating the database,
  • 4:28
    I want to drop the database, foo.
  • 4:31
    And now when we go to look, do a show databases;
  • 4:35
    listing them all, you'll see that foo is now gone.
  • 4:37
    So that easily created, that easily get rid of it.
  • 4:40
    Just be careful, make sure you know what you're doing.
  • 4:43
    It's very quick and easy to do.
  • 4:45
    OK, so, that's sort of major database and creating a user.
  • 4:51
    I now want to actually work in this particular
  • 4:53
    database that already exists here,
  • 4:54
    this 6drupal database.
  • 4:55
    So to work in a particular one, I want to use.
  • 4:59
    So the command is use and then the database name,
  • 5:01
    so I'm going to use the 6drupal Database.
  • 5:05
    You can see I have now changed into that particular database
  • 5:08
    and now let's, again, let's take a look and see what we have.
  • 5:13
    So I'm going to show the tables, so I can see what all the
  • 5:15
    different tables in this database are.
  • 5:17
    So you can see I have 73 and I can scroll up.
  • 5:21
    In particular, I want to look at this cache table here.
  • 5:24
    So if I want to get more information about a particular
  • 5:28
    table, instead of using show from here, I can say,
  • 5:31
    describe. I actually want to get some more information about
  • 5:35
    this particular table.
  • 5:36
    So I'm going to describe cache; the table name,
  • 5:39
    and here it lists the fields.
  • 5:42
    So like I have a cid, the expire,
  • 5:45
    those are the different fields.
  • 5:46
    You can see I can see what type of field they are,
  • 5:49
    and so I have a sense of the kind of data
  • 5:52
    that I'm working with.
  • 5:54
    Now, if I want to get in there and actually see what
  • 5:56
    the contents are, I would do a regular SQL query.
  • 5:59
    So I'm going to select the cid's from the cache table
  • 6:03
    and that gives me a list.
  • 6:06
    You can see I have returned 4 rows.
  • 6:08
    So there are 4 rows in there and then this
  • 6:11
    is the actual contents.
  • 6:12
    So I can see I have stuff in there,
  • 6:16
    and what I want to do is actually I want to clear this out.
  • 6:18
    I don't want to delete the table,
  • 6:21
    but I want to empty all of the rows so that it's
  • 6:24
    a clean table again.
  • 6:25
    And the way that we do that is with the truncate command.
  • 6:29
    So, drop would delete the whole thing,
  • 6:31
    truncate just sort of empties it.
  • 6:34
    So truncate the cache table.
  • 6:35
    I'll go ahead and run that and if I redo that select statement,
  • 6:43
    you'll see that now it's empty.
  • 6:46
    There are no contents in there now.
  • 6:48
    So I just went ahead and kind of cleaned it out.
  • 6:50
    I didn't mean to do, show databases; I was going to do,
  • 6:54
    show table. Right?
  • 6:56
    So, just to show, so the cache table is still there.
  • 7:00
    I didn't actually delete the entire table.
  • 7:03
    The empty table is still there, it's just no actual
  • 7:06
    content for right now.
  • 7:08
    So those are both handy things to know,
  • 7:10
    dropping and truncating.
  • 7:14
    And so, I mean from here,
  • 7:15
    you can, you know, I can pretty much also just
  • 7:17
    do SQL queries and find out data.
  • 7:20
    So, I could do say, I'll just do a select nid
  • 7:24
    and title from the node table and here's
  • 7:28
    a list of all the nodes that are on my site, by title.
  • 7:30
    You can also do updates.
  • 7:33
    So I could change things, very common thing in the Drupal, well,
  • 7:35
    not common, but a common, "Oh my God, what do I do?"
  • 7:38
    thing is when you've lost the user 1 password.
  • 7:44
    So I can actually do that, directly in the database
  • 7:46
    because I can't do it through the site.
  • 7:48
    So I update the users table. I want to set the password
  • 7:53
    equal to a new password, but Drupal does MD5 hashing
  • 7:58
    on the passwords.
  • 7:59
    So I can use the MD5 function here,
  • 8:01
    pass the password into that and definitely want to
  • 8:05
    say where uid equal 1. I don't want to change
  • 8:07
    the password for all of my users,
  • 8:10
    but, so that simple, that line right there will
  • 8:13
    reset the password for my Drupal site,
  • 8:18
    and that's pretty much, go nuts!
  • 8:21
    You now have access to everything that you need,
  • 8:24
    using SQL from the command line and it can be a lot faster
  • 8:28
    than trying to find the right place to point and click.
  • 8:31
    And again, if you're on a server that doesn't have
  • 8:34
    something like phpMyAdmin to work with,
  • 8:36
    you can still get done the tasks that you need to do.
Loading ...

Using MySQL from Command Line

Loading...

This video looks at the basics of working with MySQL from the command line. We get into the mysql environment and look at databases, tables and fields. We cover creating and deleting databases, creating a user, and querying within a particular database.

Note: In some places the command line prompt is cut-off. The YouTube version of this video doesn't have the cut-off problem. We are working on getting this fixed, but in the meantime, check out the YouTube version instead.

Command Line Basics 13: Using MySQL from Command Line (youtube.com)

Additional resources:
There are no resources for this video. If you believe there should be, please contact us.