Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member
As you know a Drupal site comprises two parts, files and a database. Anybody who uses a computer is used to transferring files, but databases are a different breed. They are basically text files that the database program MySQL reads and writes. There are two basic ways of examining, affecting, and moving these files through a graphical program such as phpMyAdmin, which we'll look at in this video, and using the UNIX command-line interface, which we'll look at in the next video. There is actually one other method using the Drupal Module backup in Migrate.
You'll get more information about it in the videos in the section, Keeping Your Site Safe and Secure. But anyway, let's get back to phpMyAdmin. You're already familiar with it if you used the Acquia Dev Desktop program to develop your site. I'll switch to it, so you can see how it works there. You simply click Manage my database, and there you go. Over in the left we have a list of all of the databases that are running on MySQL. In fact, only two of these are Drupal sites, lynda and acquia_drupal But let's go and take a look at what we've on the web host.
I've already set up a Drupal site with a database of tgeller_lynda. The site itself is at lynda.tomgellar.com. To get to it, I'm going to go to my own web host's interface for that. I go to http://my.webfaction.com, click on Databases, and MySQL phpMyAdmin interface, type in the name of the database and the password. One thing that you'll notice it's different between this and the Acquia Dev Desktop screen is over here in the left side you only have two databases: information_schema, which is part of essential to MySQL, and tgellar_lynda.
I found this to be true on many web hosts. They only allow you to have one database per phpMyAdmin interface. Another difference is we don't have to create database thing down here. On my particular web host, I've to do that through a different interface. Another difference that you'll see between the Acquia Dev Desktop and your web host, will be and how big a database you can import. You can see that by going up here and clicking Import. Again this is on my web host, and this is here using Acquia Dev Desktop. Click Import, and we see this maximum of 8 megs versus 20 megs.
If you find yourself running into this limit, there is a program I'd like to recommend to you called BigDump. Just open up a Window go to Google and search for bigdump. What this does is it chunks up your MySQL database into little pieces, so that it is possible to upload and get pass that size restriction. Anyway, let's take a look at exporting and importing a database using phpMyAdmin. As I said, I already have a database in this tgeller_lynda site. So first I'll export it. Click on Export, then I'll click on Custom.
Now you could just do a Quick Export, but I want to show you all the options. We have a list of our databases, and we only need the one database, not information_schema, and scroll down. You can change the name of the file that you're outputting here and the format. Most often you're going to use SQL. Below that there are quite a few options, and to be honest, there are too many to go through now. The most important ones I found are under the Object creation options. I'll show you what happens when I simply use the defaults that it gives me.
I'll scroll down the bottom and say go, and it downloads the file. I'll go back to the desktop and take a look at that and then I can simply open it up in a text editor if I like. And there are all the SQL statements. Most important are the ones up here at the top. You'll see that the way that we exported it, it's saying that when we import it, it's going to Create Database and then use that database. That's going to be a problem for our web host, and I found that it's true for a lot of web hosts, and I'll show you why.
We'll go back in and we'll reimport it, by going up to the top and clicking Import. Choose File, and then scroll down and say go. Ah, it was that Create Database command right there. The problem is that my web host, like many web hosts, doesn't allow you to actually create databases directly. So what can we do about that? Well, we can go back and actually edit that text file again, and remove the Create Database command.
So let's try that and give it another shot. Go back in, click Import, Choose File again, and give it another go. Nope, we still have an error. This is why you have to watch out when you export your database. I'll do another export and show you what the problem was. Click Custom, select the database I want, and scroll down. The problem was that it's trying to create all these tables inside of that database without getting rid of the old ones.
So they clash when you try to do the import. If I add this, Add Drop Table command here, scroll to the bottom and click Go, and now I have another file on the desktop. This one is -1. Now let's try to import that one and see how we do. Go back up to the top, click Import, select that file and go. Oops! We forgot to remove Create Database. These are all typical problems that you'll have when you try to move or databases in and out.
And the reason I'm going through all of this is because you'll probably have similar problems. We go back in, get rid of Create Database, and one more time, fourth time is the charm. Choose the file, scroll down, and go. Now it appears to have worked, and we can be sure of that, by going back to our site, lynda.tomgellar.com, and there it is. If it hadn't worked, it would have come up with a blank screen or an error.
So as you can see, restrictions on your web host might make using phpMyAdmin a little tricky. The easiest way to get around them is to plan ahead and set the right options when you first export the database. I recommend you play around with them, especially with those object creation options, especially the ones within there that say Create and Drop. Then examine the resulting text files, and test, test, test.
Get unlimited access to all courses for just $25/month.Become a member
82 Video lessons · 101136 Viewers
61 Video lessons · 87901 Viewers
71 Video lessons · 71778 Viewers
56 Video lessons · 103648 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.