Easy-to-follow video tutorials help you learn software, creative, and business skills.Become a member

Moving databases using phpMyAdmin

From: Drupal 7 Advanced Training

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.

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.

Show transcript

This video is part of

Image for Drupal 7 Advanced Training
Drupal 7 Advanced Training

72 video lessons · 10887 viewers

Tom Geller
Author

 
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 10s
    1. Learning from case studies
      3m 13s
    2. Planning your site
      4m 18s
    3. Populating your site with Devel Generate
      3m 42s
    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 53s
    1. Understanding and installing Drush
      5m 23s
    2. Installing Drupal using Drush
      7m 24s
    3. Building a site using Drush
      9m 6s
  6. 31m 59s
    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 27s
  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 35s
    1. Understanding the Rules module
      6m 48s
    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 learning today

Get unlimited access to all courses for just $25/month.

Become a member
Sometimes @lynda teaches me how to use a program and sometimes Lynda.com changes my life forever. @JosefShutter
@lynda lynda.com is an absolute life saver when it comes to learning todays software. Definitely recommend it! #higherlearning @Michael_Caraway
@lynda The best thing online! Your database of courses is great! To the mark and very helpful. Thanks! @ru22more
Got to create something yesterday I never thought I could do. #thanks @lynda @Ngventurella
I really do love @lynda as a learning platform. Never stop learning and developing, it’s probably our greatest gift as a species! @soundslikedavid
@lynda just subscribed to lynda.com all I can say its brilliant join now trust me @ButchSamurai
@lynda is an awesome resource. The membership is priceless if you take advantage of it. @diabetic_techie
One of the best decision I made this year. Buy a 1yr subscription to @lynda @cybercaptive
guys lynda.com (@lynda) is the best. So far I’ve learned Java, principles of OO programming, and now learning about MS project @lucasmitchell
Signed back up to @lynda dot com. I’ve missed it!! Proper geeking out right now! #timetolearn #geek @JayGodbold
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.

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?

Become a member to like this course.

Join today and get unlimited access to the entire library of video courses.

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 preferencesfrom 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.

Are you sure you want to delete this note?

No

Your file was successfully uploaded.

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.