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

Hi there,

I’m trying out the new Trellis/Bedrock/Sage setup for the first time and I’m running into some questions;

My previous workflow for development websites was with MAMP Pro where I synced my database and htdocs folder with dropbox so I could work from both my computer at work and at home on the same project. Managing my databases was done with phpMyAdmin so I could export my database, find and replace path and domain names and import to my production environment.

Now with Trellis I would like to have a similar setup where I can continuing working on a project on a different computer, so my first question is:

  1. Where are the databases stored created with MariaDB with the vagrant up command? Can’t seem to find them anywhere? I read it is embedded in the virtual machine file, but can’t seem to locate that either? And if it’s outside the main project folder, could I somehow either:
  • Move them inside the main project folder and keep that synced through dropbox?
  • Symlink them to a separate database dropbox folder?
  1. How can I manage the database through an graphic interface, so I can export etc.?
    It doesn’t have to be phpMyAdmin, Sequel Pro is fine too. When I try to enter my database credentials as entered in group_vars/development in the Sequel Pro quick connect I get an error:

    Unable to connect to host (null) because access was denied.
    Double-check your username and password and ensure that access from your current location is permitted.

The host should be localhost right?

Since I don’t have SSH access on most of my production servers, I can’t use the last Deploying to remote servers step in Trellis. So my last questions would be;

  1. Should I only FTP the contents of the site folder to my production server or is the ansible folder also being used on the production environment?

  2. Which configuration files should I edit on the production server in order to have it running just like it would with deploying it with Trellis? Just the .env file or are there any other files that need editing?

Sorry if any of these questions are too nooby or already being answered somewhere else, but it seems everyone over here somehow has SSH access to all of it’s production servers…

Thanks a lot!

2 Likes

Sequel Pro settings:

  1. Just the site directory.

  2. Off the top of my head only .env should be needed.

  1. Databases aren’t familiar for me, but my first attempt would be to add an empty directory to the project, its name matching db_name in group_vars/development:
example.com/
├── ansible/
├── site/
└── example_dev/

The new example_dev directory could be synced via dropbox.

My assumption and hope is that you could sync this directory with the DB on the VM at /var/lib/mysql/example_dev using some variation on how the Vagrantfile syncs the bedrock site directory (see sync folder declarations and helper functions).

The directory is empty till the VM creates your DB, etc.

Ok thanks, that’s what I hoped!

Thanks, gonna try this next week!
So when you say:

Does that mean the computer where VirtualBox is running? Would that mean:

/var/lib/mysql/example_dev

or:

~/.vagrant.d/boxes/box_name/var/lib/mysql/example_dev

or:

~/VirtualBox\ VMS/VM_name/var/lib/mysql/example_dev

because I can’t seem to recall seeing any database folders in there?
Thanks

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 ~/example.com/site to the guest machine path /srv/www/example.com/current

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.

4 Likes

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?

env:
  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['example.com']['env']['db_name'], but maybe there is a smarter way. Replace example.com 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'    
  end

But when accessing http://example.dev/wp/wp-admin/ 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: