Databases, Migrations & SQL: Oh My! Introduction to WordPress Database & Migrations

Presented at WordPress St. Petersburg Meetup, 2/1/2018

You've created your new website design on your own host or on your local laptop and now you're ready to launch for the client. Do you suddenly _cringe_ at the concept of having to migrate the site? Have you done it before and it's always a mess and a headache and you wish you had a method that always worked? This meetup might be for you!

We'll be talking about most things database, but will cover some of the basics of site migration:

- Tour of the WordPress Database
- What those tables mean and what they control
- Where the files are stored that are specific to your WordPress configuration
- What things specifically have to change when you migrate
- Some easy plugins for painless migrations

If you have a special plugin that you love and you want to sing its praises, let me know ahead of time and we'll include it in the talk. Be prepared to come with your questions!

Transcript

1. Databases, Migrations & SQL: Oh My!
   Intro to WordPress Databases & Migrations
   WordPress St. Petersburg
   

   View Slide

2. Jim True
   Support Lead & Community Manager

   Pods Framework
   pods.io | @podsframework
   jimtrue.com | @jimtrue
   

   View Slide

3. How many times has
   this happened to you?
   

   View Slide

4. You’ve worked on a Site for Days
   

   View Slide

5. You’re Happy with the Site
   

   View Slide

6. Client is Happy with the Site
   

   View Slide

7. Even better, they’ve PAID you
   

   View Slide

8. Now you have to move it to their server
   

   View Slide

9. And you’re suddenly lost
   ?
   

   View Slide

10. Where do you Start?
    FILES
    DATABASE
    

    View Slide

11. Where do you Start?
    WHICH FILES?
    WHICH DATABASE?
    

    View Slide

12. Let’s Start with WordPress
    WordPress wp-config.php
    WP-CONFIG.PHP contains the database connection
    parameters and any special configurations for your
    particular install.
    It will be different for every WP Install, but you
    need to at least have a copy of the file for reference
    when you migrate.
    

    View Slide

13. Grab the Specific Content
    WordPress
    wp-content/
    wp-config.php
    The only folders that are specific to this install are
    contained under wp-content
    

    View Slide

14. The Themes, Plugins & Images
    WordPress
    wp-content/
    wp-config.php
    themes/ ALL files & directories
    plugins/ ALL files & directories
    uploads/ ALL files & directories
    

    View Slide

15. Now for the Database
    DATABASE Users wp_users & wp_usermeta
    Posts wp_posts & wp_postmeta
    Comments wp_comments & 

    wp_commentmeta
    Taxonomies
    wp_terms & 

    wp_term_taxonomy &

    wp_term_relationships
    Plugin &
    Theme Settings
    wp_options
    

    View Slide

16. So basically just copy those
    to the new server and I’m
    done, right?
    

    View Slide

17. Not enough to just copy files & DB
    • wp-config.php needs new DB credentials
    • Site URL needs to be changed in
    wp_options
    • URL & Paths need to be changed in ALL
    places where they appear in the
    database: Media Paths, internal links, etc.
    

    View Slide

18. Step by Step Copy & Import
    1. SQL Dump of the Database
    2. Copy Files from wp-content & DB Dump to new Server
    3. Create new database, create a user & import data from SQL Dump
    4. Update wp_config.php with DB details (#3)
    5. Update wp_options with Site URL
    6. Login to wp-admin and go to Settings, General (Site URL) & Save
    7. Search & Replace paths (post_content) in wp_posts for Image &
    Media Links
    http://www.wpbeginner.com/wp-tutorials/how-to-move-wordpress-from-local-server-to-live-site/
    

    View Slide

19. There’s gotta be an easier way,
    right?
    

    View Slide

20. Yep, we’ve got tools for that!
    Updraft
    Backup

    Buddy
    WP DB 

    Migrate Pro*
    

    View Slide

21. Determine your Needs
    Backup Files & Database Yes Yes DB*
    Offsite Backup Storage Yes Yes Yes*
    Scheduled Backups Yes Yes Yes*
    Multisite Support Yes Yes Yes
    Easy Migration Setup Yes Yes Yes
    

    View Slide

22. Backup Buddy by iThemes
    • Database & File Backups (including other tables on site)
    • Scheduled Backups & Offsite Backups (great if your server
    crashes or the client destroys it!)
    • SIMPLE Migration Tool: One file created by BB, copied to new
    site, handles full file & database creation & restore with a step by
    step wizard.
    • NO Free Version
    https://ithemes.com/purchase/backupbuddy/
    

    View Slide

23. UpdraftPlus (& Other plugins)
    • Database & File Backups (including other tables)
    • Scheduled Backups & Offsite Backups (great if your server
    crashes or the client destroys it!)
    • Cloning & Migration Tool, also UpdraftVault, UpdraftMigrator
    and UpdraftCentral (great for people supporting tons of sites)
    • Free Version does Backup & Restore, some Offsite Storage,
    Scheduled Backups
    https://updraftplus.com/
    

    View Slide

24. WP DB Migrate (& Pro)
    • Database Backups & Media (with Add-on to Pro version). NO
    files.
    • Push & Pull Technology from WP Site to WP Site. Great for
    Development/Staging/Live Environments.
    • Very Powerful Pro Version: Determine Data Flow, Exclude/
    Include Custom Post Types, Multisite Support (including pulling
    sites in and out of Network) and WP-CLI support.
    • Free Version only does Database Backup & Search/Replace
    https://deliciousbrains.com/wp-migrate-db-pro/
    

    View Slide

25. DEMO TIME
    

    View Slide

26. Resources & Links
    • Tour of the WordPress Database:

    https://deliciousbrains.com/tour-wordpress-database/
    • How to Move WordPress From Local Server to Live Site:

    http://www.wpbeginner.com/wp-tutorials/how-to-move-wordpress-from-local-server-to-live-site/
    • How to Move WordPress with Duplicator:

    http://www.wpbeginner.com/wp-tutorials/how-to-move-wordpress-to-a-new-host-or-server-with-no-downtime/
    • Seven Best Backup Plugins (Pros & Cons):

    http://www.wpbeginner.com/plugins/7-best-wordpress-backup-plugins-compared-pros-and-cons/
    

    View Slide

27. Jim True
    Support Lead & Community Manager

    Pods Framework
    pods.io | @podsframework
    jimtrue.com | @jimtrue
    

    View Slide

28. WordPress St. Petersburg
    Downtown St. Pete @ TEC Garage

    1st & 3rd Thursdays / Monthly
    Meetup: https://meetup.com/WordPress-St-Petersburg

    Web: https://tampabaywp.org

    Slack Chat | Facebook Group | Meetups

    A Member of Tampa Bay WordPress Network
    

    View Slide

29. View Slide

30. Upcoming Events Sponsored by TEC Garage

    & Tampa Bay Innovates
    Tech Talk

    Next Event: February 13th
    “Leading with Purpose: How Our Mission Drives Revenue

    at the Penny Hoarder
    @ Microsoft

    5426 Bay Center Dr Ste 700, Tampa, FL 33609 

    FREE Registration Required: 

    http://www.tbinnovates.com/TechTalk.cfm
    

    View Slide