Drupal 7 Advanced Training
Illustration by John Hersey

Moving databases using phpMyAdmin


From:

Drupal 7 Advanced Training

with Tom Geller

Video: Moving databases using phpMyAdmin

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.
Expand all | Collapse all
  1. 4m 51s
    1. Welcome
      1m 7s
    2. Planning your Drupal career
      3m 44s
  2. 25m 46s
    1. Comparing development and production environments
      6m 22s
    2. Configuring your settings.php file
      7m 10s
    3. Running multiple sites from one Drupal installation
      7m 32s
    4. Troubleshooting common issues
      4m 42s
  3. 51m 20s
    1. Understanding your Drupal site's place on the web
      2m 44s
    2. Selecting a web host
      7m 20s
    3. Using Unix's command-line interface
      8m 23s
    4. Working with your web host's access restrictions
      4m 51s
    5. Using cPanel and other graphical web host interfaces
      3m 40s
    6. Transferring files to and from your web host
      5m 49s
    7. Moving databases using phpMyAdmin
      6m 34s
    8. Moving databases using Unix commands
      6m 8s
    9. Modifying server configuration files
      5m 51s
  4. 27m 11s
    1. Learning from case studies
      3m 13s
    2. Planning your site
      4m 18s
    3. Populating your site with Devel Generate
      3m 43s
    4. Managing URL paths
      3m 20s
    5. Restricting access to downloadable files
      5m 55s
    6. Reusing site components with the Features module
      6m 42s
  5. 21m 54s
    1. Understanding and installing Drush
      5m 23s
    2. Installing Drupal using Drush
      7m 24s
    3. Building a site using Drush
      9m 7s
  6. 32m 0s
    1. Backing up with the Backup and Migrate module
      8m 17s
    2. Moderating comment spam
      6m 47s
    3. Migrating from Drupal 6
      4m 28s
    4. Migrating to Drupal 7
      12m 28s
  7. 22m 24s
    1. Letting users log in through OpenID
      5m 10s
    2. Letting users log in through Facebook, Twitter, and other services
      9m 43s
    3. Republishing posts on Facebook and Twitter
      7m 31s
  8. 1h 5m
    1. Understanding Drupal's base themes
      5m 55s
    2. Introducing base themes: Zen and subtheming
      11m 35s
    3. Introducing base themes: Fusion and extensibility
      10m 44s
    4. Introducing base themes: AdaptiveTheme and responsive design
      7m 25s
    5. Introducing base themes: Omega and mobile devices
      7m 9s
    6. Using Firebug and other theming tools
      7m 20s
    7. Modifying themes with Sweaver
      6m 59s
    8. Modifying themes with Livethemer
      8m 45s
  9. 49m 36s
    1. Understanding the Rules module
      6m 49s
    2. Demonstrating how Rules works in Drupal Commerce
      3m 53s
    3. Creating practical rules
      6m 37s
    4. Improving rules
      9m 10s
    5. Defining conditions with the Context module
      7m 51s
    6. Varying layout with the Context and Delta modules
      5m 0s
    7. Varying layout with the Panels module
      10m 16s
  10. 52m 46s
    1. Enabling social features
      9m 46s
    2. Implementing a voting system
      9m 15s
    3. Rewarding good behavior with the Userpoints module
      5m 57s
    4. Setting up Organic Groups
      6m 28s
    5. Adding content to groups
      2m 21s
    6. Seeing group activity better with views and panels
      10m 13s
    7. Making groups private
      4m 54s
    8. Letting each group have its own permissions
      3m 52s
  11. 25m 32s
    1. Understanding packaged Drupal distributions
      2m 8s
    2. Getting a head start with Acquia Drupal
      7m 28s
    3. Creating online storefronts with Drupal Commerce
      6m 53s
    4. Creating government web sites with OpenPublic
      9m 3s
  12. 1h 30m
    1. Taking the assignment
      3m 26s
    2. Planning the project
      3m 43s
    3. Preparing the infrastructure
      8m 42s
    4. Starting the design
      7m 35s
    5. Filling in the content
      7m 29s
    6. Developing the layout
      10m 27s
    7. Finishing the layout
      7m 8s
    8. Refining the CSS
      6m 32s
    9. Setting up sections for regional offices
      4m 53s
    10. Configuring regional offices
      7m 18s
    11. Connecting to social media
      5m 9s
    12. Cleaning up
      12m 32s
    13. Securing the site
      5m 56s
  13. 37s
    1. Next steps
      37s

Start your free trial now, and begin learning software, business and creative skills—anytime, anywhere—with video instruction from recognized industry experts.

Start Your Free Trial Now
please wait ...
Watch the Online Video Course Drupal 7 Advanced Training
7h 50m Intermediate May 17, 2012

Viewers: in countries Watching now:

This course teaches web site designers how to take their sites to the next level with a few advanced techniques and the free and open-source Drupal software. Author Tom Geller shows how to configure the most popular add-on modules; use *nix commands and an FTP program to manage a Drupal site on a web server; change its visual appearance using the latest graphical tools; automate and speed through common tasks with Drush; integrate with social media sites; and see how "supermodules" like Panels, Context, Rules, and Features open up new worlds of code-free development.

Drupal 7 Advanced Training was designed as a follow-up to Drupal 7 Essential Training and it also dovetails nicely with our other Drupal courses, such as Drupal 7 Reporting and Visualizing Data and Create Your First Online Store with Drupal Commerce.

Topics include:
  • Moving a site from the development environment to production
  • Hosting a Drupal site
  • Moving databases with phpMyAdmin and Unix commands
  • Making site administration more efficient with Drush
  • Backing up site data
  • Moderating comments
  • Migrating from previous versions of Drupal
  • Working with themes
  • Creating variable layouts
  • Enabling social features
  • Creating an online store with Drupal Commerce
Subjects:
Developer Web
Software:
Drupal
Author:
Tom Geller

Moving databases using phpMyAdmin

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.

There are currently no FAQs about Drupal 7 Advanced Training.

 
Share a link to this course

What are exercise files?

Exercise files are the same files the author uses in the course. Save time by downloading the author's files instead of setting up your own files, and learn by following along with the instructor.

Can I take this course without the exercise files?

Yes! If you decide you would like the exercise files later, you can upgrade to a premium account any time.

Become a member Download sample files See plans and pricing

Please wait... please wait ...
Upgrade to get access to exercise files.

Exercise files video

How to use exercise files.

Learn by watching, listening, and doing, Exercise files are the same files the author uses in the course, so you can download them and follow along Premium memberships include access to all exercise files in the library.


Exercise files

Exercise files video

How to use exercise files.

For additional information on downloading and using exercise files, watch our instructional video or read the instructions in the FAQ .

This course includes free exercise files, so you can practice while you watch the course. To access all the exercise files in our library, become a Premium Member.

* Estimated file size

Are you sure you want to mark all the videos in this course as unwatched?

This will not affect your course history, your reports, or your certificates of completion for this course.


Mark all as unwatched Cancel

Congratulations

You have completed Drupal 7 Advanced Training.

Return to your organization's learning portal to continue training, or close this page.


OK
Become a member to add this course to a playlist

Join today and get unlimited access to the entire library of video courses—and create as many playlists as you like.

Get started

Already a member ?

Exercise files

Learn by watching, listening, and doing! Exercise files are the same files the author uses in the course, so you can download them and follow along. Exercise files are available with all Premium memberships. Learn more

Get started

Already a Premium member?

Exercise files video

How to use exercise files.

Ask a question

Thanks for contacting us.
You’ll hear from our Customer Service team within 24 hours.

Please enter the text shown below:

The classic layout automatically defaults to the latest Flash Player.

To choose a different player, hold the cursor over your name at the top right of any lynda.com page and choose Site preferences from the dropdown menu.

Continue to classic layout Stay on new layout
Exercise files

Access exercise files from a button right under the course name.

Mark videos as unwatched

Remove icons showing you already watched videos if you want to start over.

Control your viewing experience

Make the video wide, narrow, full-screen, or pop the player out of the page into its own window.

Interactive transcripts

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.

Learn more, save more. Upgrade today!

Get our Annual Premium Membership at our best savings yet.

Upgrade to our Annual Premium Membership today and get even more value from your lynda.com subscription:

“In a way, I feel like you are rooting for me. Like you are really invested in my experience, and want me to get as much out of these courses as possible this is the best place to start on your journey to learning new material.”— Nadine H.

Thanks for signing up.

We’ll send you a confirmation email shortly.


Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

Keep up with news, tips, and latest courses with emails from lynda.com.

Sign up and receive emails about lynda.com and our online training library:

Here’s our privacy policy with more details about how we handle your information.

   
submit Lightbox submit clicked
Terms and conditions of use

We've updated our terms and conditions (now called terms of service).Go
Review and accept our updated terms of service.