Keep database synced between computers in Trellis/Bedrock/Sage setup

Once you ssh into the VM (e.g., run vagrant ssh), you’ll find the DB at the path /var/lib/mysql/example_dev.

Synced folders enable Vagrant to sync a folder on the host machine to the guest machine

So, /var/lib/mysql/example_dev is the guest machine path to the DB, inside the VirtualBox VM (not the path your host machine). Let me know if I can clarify.

For comparison, the Vagrantfile already syncs the bedrock-based site directory from the host machine path ~/ to the guest machine path /srv/www/

1 Like

So, when you are working with Trellis (or any virtual machine for that matter), you can share folders with it. But remember, this is another virtualized computer, in essence. So just like you have all your system files on your computer, this virtualized system has all it’s own system files and folders. This is why a typical Ubuntu Trellis box is ~2-3gb.

Not every folder is shared between your computer and the virtual machine. So the database files are not being stored directly on your host system like OS X. You will most likely find a ~/VirtualBox\ VMS/VM_name/box-disk1.vmdk or something similar. That is the virtual hard drive of the virtual machine. It’s not directly accessible.

1 Like

Ok that clears things up, thanks!
So basically I can’t sync the database directly because it’s not accessible on the host machine where dropbox is running, but I could sync the whole VM box? Only disadvantage is that it needs to sync 3 gb every time the VM changes…

:point_up_2: correct. BUT, the Vagrant sync folder feature bridges the gap. Once you set up a sync folder, vagrant keeps the folder on your regular host machine in sync with a folder on the guest machine, so, it’s almost like having dropbox sync the database directly.

No, don’t do it! :scream: :wink:
Only ever pick the folders you want to sync and set them up as vagrant sync folders (see my various links above). For the DB, your dropbox will only be syncing the example_dev db folder, which is only about 4K on a new Trellis VM. Of course, you’ll add to the DB making it bigger, but not as big as 3GB.

If it seems confusing, like we’re saying contradictory things…

  • “you can’t access folders on the VM”
  • “just sync a folder on the VM”

…read up on Vagrant sync folders. They let a folder on your regular (host) machine sync with – or mirror – a folder from inside the VM guest machine, which VM might otherwise be a black box to your regular host machine.


Just FYI - you should never, under any circumstances, sync the raw files of any InnoDB tables using Dropbox, or something similar. MyISAM is a bit safer, but you will eventually corrupt your InnoDB tables - it’s just a matter of time.

1 Like

Thanks fullyint!

I read up on the Synced Folders option in Vagrant and tried adding the following line to my Vagrantfile:

config.vm.synced_folder "../db", "/var/lib/mysql/example_wp", owner: "root", group: "root"

and added the folder db in my main project folder.
When running vagrant up the folder seems to be mounted correctly:

==> default: Mounting NFS shared folders...
==> default: Mounting shared folders...
    default: /vagrant =>~/Dropbox/Websites/example/ansible
    default: /var/lib/mysql/example_wp => ~/Dropbox/Websites/example/db
==> default: Creating bind mounts for selected devices
==> default: Creating bind mount from /vagrant-nfs-example to /srv/www/example/current

But should it also be binded like the site_path?
Because when I go to /wp/wp-admin/ I get the Wordpress installation screen which shouldn’t happen and in the next step I get loads of errors saying:

WordPress database error: [Can't create table `example_wp`.`mydbprefix_users` (errno: 13 "Permission denied")]

So I was wundering what the file permissions should be of the synced folder? I also tried:

config.vm.synced_folder "../db", "/var/lib/mysql/example_wp", u: 'vagrant', g: 'www-data'

with the same result.

Another question; How can I use the database name variable set in group_vars/development in my vagrantfile?

  db_name: example_wp

Would this work:

wp_db = site['env']['db_name']
config.vm.synced_folder "../db", "/var/lib/mysql/#{wp_db}", u: 'vagrant', g: 'www-data'

Thanks for the help so far!

I haven’t tried syncing raw database files and it sounds like those who have tried saw corrupted tables. Maybe someone else will have better ideas, but you may just have to do an sql dump to a dropbox folder when you leave one work location, then import the db when you arrive at the other location. You could dump/import with Sequel Pro, WP CLI, WP Migrate DB Pro, etc. Maybe you could automate it.

Maybe someone familiar with RDS could comment on whether an RDS setup would work, but maybe that’s overkill for syncing two dev DBs.

All that said, yes, you need to manage owership on sync folders using vagrant-bindfs, as is used with site_path. I think the user:group of the raw db is root:root (you could double-check if you haven’t already), but again, it’s probably best to not sync the raw db files. You tried root:root and it failed, but I suspect it would work once you add in the bind folder. For reference on implementation, here is where Trellis began using vagrant-bindfs to handle permissions: roots/trellis#82

As for how to access variables from wordpress_sites in the Vagrantfile, you could use wordpress_sites['']['env']['db_name'], but maybe there is a smarter way. Replace with whatever your site key is. site['env']['db_name'] would work if you were looping over the sites, each site represented by the site object.

1 Like

I know VVV had it set up so that the database files were saved somewhere in the shared folder. I never looked into how they did that though.

I did it with MAMP for 2 years and it did go ok, I sometimes ran into dropbox conflicts but I thought it was easier then exporting and importing everytime you switch workplaces.

Ok thanks for clearing that up! I’m not sure exactly how vagrant-bindfs works yet though…
This seems to work (at least my database files are in the synced db folder now):

  wordpress_sites.each do |(name, site)|
    config.vm.synced_folder local_site_path(site), nfs_path(name), type: 'nfs'
    config.bindfs.bind_folder nfs_path(name), remote_site_path(name), u: 'vagrant', g: 'www-data'
    # Add database sync folder and bind it
    wp_db = site['env']['db_name']
    config.vm.synced_folder "../db", "/var/lib/mysql/#{wp_db}", type: 'nfs'
    config.bindfs.bind_folder "/var/lib/mysql/#{wp_db}", "/var/lib/mysql/#{wp_db}", u: 'root', g: 'root'    

But when accessing results in the following error:

Warning: mysqli_real_connect(): (HY000/2002): Connection refused in /srv/www/example/current/web/wp/wp-includes/wp-db.php on line 1452
Error reconnecting to the database

Does that mean it’s a permission error or the config.bindfs.bind_folder line is incorrect?
I’m not sure what the first value should be, I also tried without succes:

config.bindfs.bind_folder nfs_path(name), "/var/lib/mysql/#{wp_db}", u: 'root', g: 'root' 
config.bindfs.bind_folder "../db", "/var/lib/mysql/#{wp_db}", u: 'root', g: 'root' 

Thanks again!

Hmm I think this is not gonna work…
This binding line seems to work okay now when running vagrant up for the first time:

config.bindfs.bind_folder "/var/lib/mysql/#{wp_db}", "/var/lib/mysql/#{wp_db}", u: 'mysql', g: 'mysql', p: 'og-x:og+rD:u=rwX:g+rw', after: 'provision'

Wordpress is installed correctly, and all the database tables are in my db folder and I can see them changing when changing options in wp. However when running vagrant halt and then running vagrant up --provision again, the database is already corrupted and I get the following error:

 One or more database tables are unavailable. The database probably needs repairing.

Not sure if there’s a fix for that, but for now I’m not gonna bother anymore…
Thanks for all your help!

Good progress. As @kalenjohnson mentioned, you might check out the approach VVV is using. Looks like they’re syncing the entire /var/lib/mysql directory instead of just the db and they use some different options on the sync folder. There are a few other database notes in their README that might have ideas for you.

You might also try to get the vagrant sync part working to your satisfaction and only then make the folder a dropbox folder, to isolate any possible issues dropbox may cause in its handling of the folder.

Tried that too without using the nfs mount option like this:

config.vm.synced_folder "../db", "/var/lib/mysql/#{wp_db}", mount_options: [ "dmode=777", "fmode=777" ]

Which worked the first time, but on the second time after vagrant halt and up same database errors.

Also gave this a try, by moving the db folder outside dropbox and in my home documents folder like this:

config.vm.synced_folder "~/Documents/Sites/example/db", "/var/lib/mysql/#{wp_db}", mount_options: [ "dmode=777", "fmode=777" ]

With exactly the same result I’m afraid…

I didn’t look carefully at VVV, but after a quick glance it looks like they have it working, so you could keep digging in to their approach if you’re set on making it happen. Maybe vagrant-triggers (also used in VVV) could automatically dump/import and sql file on each halt/up. I haven’t used any of the above, however. Good luck!

That’s exactly what I was thinking!
I’ll have a look at that later, last attempt :smile:

Ok, I dug a little deeper in the VVV approach and tried to replicate their database syncing method with the following steps:

  1. Added the following directory structure in my main project directory:

├─ backups/
├─ data/
├─ init-custom.sql.sample (not used)
└─ init.sql (not used)

  1. Changed the sync folder in the wordpress_sites loop in my Vagrantfile into:

    config.vm.synced_folder “…/database/”, "/srv/database"
    if File.exists?(File.join(’…/database/data/mysql_upgrade_info’)) then
    config.vm.synced_folder “database/data/”, “/var/lib/mysql”, :mount_options => [ “dmode=777”, “fmode=777” ]

  2. Added the following directory structure and files in my ansible directory:

├─ homebin/
├── db_backup
├── vagrant_destroy
├── vagrant_halt
└── vagrant_suspend

and added the following to the Vagrantfile:

 config.vm.synced_folder "config/", "/srv/config"
  1. Added a provision directory in the ansible directory with an edited version of the VVV’s file:


    This file is specified in Vagrantfile and is loaded by Vagrant as the primary

    provisioning script whenever the commands vagrant up, vagrant provision,

    or vagrant reload are used. It provides all of the default packages and

    configurations included with Varying Vagrant Vagrants.

    If MySQL is installed, go through the various imports and service tasks.

    exists_mysql="$(service mysql status)"
    if [[ “mysql: unrecognized service” != “${exists_mysql}” ]]; then

     # Setup MySQL by importing an init file that creates necessary
     # users and databases that our vagrant setup relies on.
     # mysql -u root -proot < /srv/database/init.sql
     # echo "Initial MySQL prep..."
     # Process each mysqldump SQL file in database/backups to import
     # an initial data set for MySQL.

    echo -e "\nMySQL is not installed. No databases imported."

and added the following to the Vagrantfile:

if File.exists?(File.join(ANSIBLE_PATH,'provision','')) then
  config.vm.provision :shell, :path => File.join( "provision", "" )
  1. Installed the vagrant-triggers plugin and added the following at the bottom of my Vagrantfile:

    if Vagrant.has_plugin? 'vagrant-triggers’
    config.trigger.before :halt, :stdout => true do
    run "vagrant ssh -c ‘vagrant_halt’"
    config.trigger.before :suspend, :stdout => true do
    run "vagrant ssh -c ‘vagrant_suspend’"
    config.trigger.before :destroy, :stdout => true do
    run "vagrant ssh -c ‘vagrant_destroy’"
    puts 'vagrant-triggers missing, please install the plugin:'
    puts 'vagrant plugin install vagrant-triggers’

Running vagrant up doesn’t give any errors besides stdin: is not a tty

==> default: Running provisioner: shell...
    default: Running: /var/folders/x8/l1j6fgb91s37px9tplwmfdmdn2c64s/T/
==> default: stdin: is not a tty
==> default: 
==> default: Start MySQL Database Import
==> default: No custom databases to import

No databases are imported yet, but that makes sense because one has to be exported first on vagrant halt

That’s where I’m stuck right now, the vagrant_destroy, vagrant_halt & vagrant_suspend commands are called from within the vagrant ssh in the vagrant-triggers but how does Vagrant ‘know’ that these commands are stored inside the config/homebin directory?

When running vagrant halt I get the following error right now:

==> default: Running triggers before halt...
==> default: Executing command "vagrant ssh -c vagrant_halt"...
==> default: bash: vagrant_halt: command not found
==> default: Command execution finished.
The command "vagrant ssh -c 'vagrant_halt'" returned a failed exit code. The
error output is shown below:

bash: vagrant_halt: command not found

How do I ‘load’ these commands into my Vagrantfile?

Thanks again and sorry for the long post!


I got a bit closer!

When I run vagrant ssh and run the following command (where example_dev, username & password are defined in your group_vars/development file):

mysqldump -uusername -ppassword example_dev > /srv/database/backups/example_dev.sql

My database is exported correctly and synced in my local directory database/backups!

But I need to insert this command into the run command of the vagrant trigger instead of using the external command (config/homebin/db_backup) like VVV does within a wordpress_sites loop like this:

 if Vagrant.has_plugin? 'vagrant-triggers'
  config.trigger.before :halt, :stdout => true do
    wordpress_sites.each do |(name, site)|
      db_name  = site['env']['db_name']
      db_user  = site['env']['db_user']
      db_pass  = site['env']['db_password']
      info "Backing up #{db_name}"
      run "vagrant ssh -c echo #{db_name} && mysqldump -u#{db_user} -p#{db_pass} #{db_name} > /srv/database/backups/#{db_name}.sql && echo 'Database #{db_name} backed up';"
  puts 'vagrant-triggers missing, please install the plugin:'
  puts 'vagrant plugin install vagrant-triggers'

But I keep on getting this error:

The command "vagrant ssh mysql -u username -p password
      mysqldump -uusername -ppassword  example_dev > /srv/database/backups/example_dev.sql" returned a failed exit code. The
error output is shown below:

An invalid option was specified. The help for this command
is available below.

Usage: vagrant ssh [options] [name] [-- extra ssh args]


    -c, --command COMMAND            Execute an SSH command directly
    -p, --plain                      Plain mode, leaves authentication up to user
    -h, --help                       Print this help

I already tried it without the -c flag but that doesn’t make a difference.
What exactly is the invalid option?



I’m excited that you’re figuring out cool stuff here. I cheer you on, while also saying that I probably won’t engage on it much more given that it is outside of regular Trellis and not a personal need of my own.

I couldn’t help wondering though, have you already tried vagrant ssh -c 'command in quotes'
When you omit quotes, maybe vagrant is thinking that the parts of the COMMAND are supposed to be options, and they look like invalid options.

Yes, thank you!
I think I got it working now! :grinning:

# Vagrant Triggers
if Vagrant.has_plugin? 'vagrant-triggers'
  # Importing Database
  wordpress_sites.each do |(name, site)|
    db_name  = site['env']['db_name']
    db_user  = site['env']['db_user']
    db_pass  = site['env']['db_password']
      [:up, :resume, :reload] => "vagrant ssh -c 'cd /srv/database/backups/
      sql_count=`ls -1 #{db_name}.sql 2>/dev/null | wc -l`
      if [ $sql_count != 0 ]
        echo Database file exists, so start importing
        mysql -u #{db_user} -p#{db_pass} #{db_name} < /srv/database/backups/#{db_name}.sql
        echo Database file does not exist  
    }.each do |command, trigger|
      config.trigger.after command, :stdout => true do
        info "Importing database #{db_name}"
        run  "#{trigger}" 
  # Exporting Database
  wordpress_sites.each do |(name, site)|
    db_name  = site['env']['db_name']
    db_user  = site['env']['db_user']
    db_pass  = site['env']['db_password']
      [:halt, :suspend, :destroy] => "vagrant ssh -c 'mysqldump -u #{db_user} -p#{db_pass} #{db_name} > /srv/database/backups/#{db_name}.sql'",
    }.each do |command, trigger|
      config.trigger.before command, :stdout => true do
        info "Backing up #{db_name}"
        run  "#{trigger}"
  puts 'vagrant-triggers missing, please install the plugin:'
  puts 'vagrant plugin install vagrant-triggers'

On vagrant up, resume and reload my database file gets imported correctly if it exists and on vagrant halt, suspend and destroy it gets exported correctly!

If anyone sees any potential bugs or problems, please let me know!
Thanks for all your help @fullyint!


Oh by the way, in order to get this up and running;
On all the computers you want to sync, your MacOs user id should be the same (and probably your user name too). Here’s how you can change this:

Make sure to create a second Administrator user first and log in with that new account first before changing the User id and/or name. After changing the home folder name and id and logging back in, my account was all messed up. I had to change the file permissions on my home folder and reinstall Dropbox, Vagrant and Virtualbox in order to get things working again!

But it was worth it, because it’s working now on 2 computers!


Dear Toine ,

I am a newbie in Vagrant and I have made a script which install LAMP but what I missed is the sync of the database can you please share the entire script on how you sync the database?

It would be really much appreciated.