Import Database from MySQL Dump on Vagrant Up?

Is there nice a way of setting Trellis to automatically import a mysql dump file on ‘vagrant up’?

I saw the Import Database section of [Database.yml] (https://github.com/roots/trellis/blob/082430a3ad603d58be413cbf9a54b3e2f3d83cdc/roles/wordpress-setup/tasks/database.yml) and thought I’d check if anyone’s modified this to load an existing sql-dump-*.sql.

Currently it seems like to option are:

  1. Manually import the database from the command line.
  2. Host the master database on a server and remote connect, or use WP Migrate DB Pro to handle pull requests.
  3. Using this method.

I’m basically just looking for a simpler way of importing a project database across multiple installations of the same project on different computers. I know this thread offers a good solution, but I’m searching for the a reliable, safe and simple approach. Avoiding potential issues like Mac OS X User ID conflicts is essential.

Thanks!

1 Like

Have a look at my Vagrantfile where I use vagrant-triggers to import and export my database:

2 Likes

The cheap quick and easy method I used with a colleague to make sure we were both working with the same data when collaborating on a project was to use the db_import option in wordpress_sites.

This will import the database on vagrant provision instead of vagrant up however it was enough for us both to have dummy data to work with whilst we developed the site and allowed us both to revert back to a working state if we made a mistake with something. It also means you can keep the dump in version control inside the project which may or may not be what you’re after.

Steps: (last tested on version 0.9.3)

  • vagrant ssh
  • cd /srv/www/example.com/current
  • wp db export # exports example_dev.sql to the site folder
  • Add the line db_import: ../site/example_dev.sql to wordpress_sites.yml

Updating the dump would have to be done manually but it’s as simple as ssh’ing into vagrant and running the export command again.

edit: updated the path for db_import, thanks for checking @ruslankomjakov

8 Likes

Thanks @paul_tibbetts this is exactly what I was looking for!

Thanks @paul_tibbetts. That’s a nice little step to improve development workflow! Just tested the path to the database dump file, and it looks like ../../../ is no longer needed. db_import Path should be relative to Ansible root, so just ../site path is sufficient In case anyone else is looking for this:
db_import: ../site/rb_dev.sql works fine (Trellis 0.9.3).

1 Like

Hi there, does the sql dump needs to be in a “special format”?

I have this on my development/wordpress_sites.yml:

local_path: ../site
db_import: ../site/db.sql

When i run vagrant provision to actually import the database, ansible starts doing the tasks and when it reaches the task:

TASK [wordpress-setup : Import database] *************************************

It takes a little bit (like if it was reading something) and then continues without problems. At the end ansible says which tasks where changed but when i go to my development URL, i just have the Hello World post when my db.sql has like 500 posts.

Any thoughts on this?

My db_name matches the name of the db.sql.

I think you also need to set site_install: false. Your DB import is probably working then the WP install maybe overwriting it.

1 Like

Trying it now, thanks for the super fast response :slightly_smiling:

No luck after site_install: false and vagrant provision.

It runs the Ansible tasks without problem, but still no content from db.sql.

My thoughts are that this is a DB related thing. I’m reviewing the file (almost 40MB) and removing the table prefix i just spotted.

Also noticed that my db.sql doesn’t have a CREATE DATABASE line, so i’m assuming that when Trellis will use the data from my db.sql tables, unless Trellis is using some sorf of prefix? Damn, too much things to try. I’ll update when i fix it.

FIXED

First at all, it is not a Trellis problem.

I’ve inspected my db.sql file and find out that the structure of the tables wasn’t like the official one that can be found in this link. So i made all required changes, then vagrant destroy and vagrant up. The vagrant provision wasn’t working because it loads de Vagrant file but it doesn’t delete any database already in the virtual machine, so i started clean and now it is working.

P.S. I had to truncate the wp_options table. It made the import even faster.

4 Likes

hi! Is this method still working? I’m having troubles getting it to work.
Thanks

In case anyone came across this recently like me, this feature was removed: