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

Moving databases using Unix commands

From: Drupal 7 Advanced Training

Video: Moving databases using Unix commands

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.

Moving databases using Unix commands

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.

Show transcript

This video is part of

Image for Drupal 7 Advanced Training
Drupal 7 Advanced Training

72 video lessons · 10923 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.