Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
In the last video you saw how to use the graphical program phpMyAdmin to move databases onto and off of your web host. That's how I usually do it, but there are times when you need to use UNIX's command-line interface. For example, if your web host doesn't offer phpMyAdmin or if the database is too big for its upload feature, I'll show you the basics of the command-line interface, but that should be enough to get you started. We'll start by exporting a database we already have installed. This one is on my own site tomgeller.com.
The database itself is named tgeller_lynda. First, let's take a look at it with the interactive MySQL command. Before I can do that, I have to switch over to the Terminal and log in. As before I do that with the ssh command. and then enter my password. Once there, let's take a look and see what we have. This is my home directory. I'm going to switch into that lynda directory, where we'll play around a little bit.
As you can see, we have nothing there already. To use the interactive MySQL command you type mysql-u, for user, then your username, and then -p, which means it will ask you for the password. I enter the database password, not the password for my user itself, and you can tell that you're in it because you get a prompt similar to this one, and then you see mysql down here. Every command that you entered in the interactive MySQL program requires a semicolon at the end. I'll show you that.
If I were to simply say show databases, I don't get anything, instead I get this prompt saying okay, I'm waiting for more information, but when I then type the semicolon (;) it does what I want. Usually of course you just do that all in one line, like this. We have two databases and the one that I actually want to use is the tgeller_lynda. The command is quite simple; it's use, use tgeller_lynda, followed by the semicolon (;), and it confirms our choice.
We can see what's in that with show tables, and there we have a list of everything that's in our Drupal installation. This is a very fresh installation by the way. The only thing that I changed was this page up here that says Database test, and then I put that on the front page of the site. By the way, the MySQL command also works in noninteractive mode; you'll see that in a few minutes. But to export the database, you actually use a different program called mysqldump. So I'm going to exit out from the MySQL program right now with exit.
And here's the command that you use for mysqldump. So far it's similar to MySQL, mysqldump -u, and then you put in your username, -p, for the password, and then the name of the database. In my case, both the username and the database itself are tgeller_lynda; that's something that my web host requires. Then to put it into another file, you use this greater than (>) sign and the name of the file. I'm just going to call it export.sql. It asked for the password, again this is for the database, and we're done.
We can prove that by doing ls -al, and there it is. Let's take a look inside of that file. I'll use the more command, which just prints out what's inside a text file. This should look familiar to you if you've looked at any of these MySQL files on your desktop computer. One thing that's important to notice is that before every table it has this Drop Table If Exists that's actually quite useful, because there's no easy way to drop all tables through the command line, as we did by clicking around in phpMyAdmin.
So if you do an export from phpMyAdmin and you plan to later import using the command line, make sure the option to Add the Drop Table's directive is checked. From here of course you could download the SQL file as a backup or something. Alternately, you could have uploaded one to your server from somewhere else. So I'm going to get out of here by pressing Q, that been back to the command prompt, then go back into my site and just mess around a little bit. I'll add some content to the front page. Say Basic page, go down, put it on the front page, save, and go back up, and there it is.
Now let's say that we didn't like what we did there to add that second note; we want to import the file that we just exported. The fastest way is to use MySQL command in noninteractive mode with a very similar syntax to the mysqldump command we just did. So I'll switch back to my Terminal here, and here's the command. Now you'll notice the only difference between this and the previous command is this little greater than and less than symbol, instead of saying greater than, we said less there.
It's sort of like a funnel; the stuff from the big end goes into the little end. Now if we hit Return, we enter the password again, and hopefully we were successful. We can find out by going back to our site and reloading, and if we were successful, the second page will disappear because of course we're taking an export from before we created that, and it worked. There is no way around it. Databases are a kind of tricky until you get used to them, and using MySQL from the command line is a world unto itself.
Just take a look at all the options it has. I'm going to go back into the Terminal, clear the screen; we usually do that with a Command+L or Ctrl+L by the way, and take a look at the man page, that is the manual page for MySQL. Scroll down a little bit, and just look at all of these options. The good news is you probably won't need to know more than a few options to run your Drupal site. As always, drupal.org is a good place to look for solutions to any problems you run into, and your web host can provide guidance on the restrictions that they've put in place.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 101787 Viewers
61 Video lessons · 88547 Viewers
71 Video lessons · 72357 Viewers
56 Video lessons · 104066 Viewers
Access exercise files from a button right under the course name.
Search within course videos and transcripts, and jump right to the results.
Remove icons showing you already watched videos if you want to start over.
Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.
Click on text in the transcript to jump to that spot in the video. As the video plays, the relevant spot in the transcript will be highlighted.
Your file was successfully uploaded.