This will be a short one, but last night I realized that it was VVV not Vagrant that was making wonderful backups of mysql databases each time I did a vagrant halt
Of course, I realized this after I did a vagrant provision on a local development site that wasn't set up with VVV and realized the whole Craft CMS project database was gone.
A few hours (yeesh) later, I have a better sense of how VVV uses vagrant-triggers to do a mysqldump of all the databases and tables and I'm halfway towards having that working with the puphpet config.yaml
Particularly helpful references:
- the VVV homebin directory which is pretty much what should be happening: vagrant-triggers on up, halt, and destroy that call a bash script that loops through mysql databases and dumps them out.
In this case, to work with Craft a little more cleanly, I made a bin directory in my webroot and mapped it to /var/vagrant/bin with config.yaml:
vagrantfile:
vm:
synced_folder:
vflwf_iq3ozcalwiv4:
source: ./bin
target: /var/vagrant/bin
sync_type: default
smb:
smb_host: ''
smb_username: ''
smb_password: ''
rsync:
args:
- '--verbose'
- '--archive'
- '-z'
exclude:
- .vagrant/
- .git/
auto: 'true'
owner: vagrant
group: vagrantand a mysql user mysqldumpuser with SELECT privileges on all tables.
user: mysqldumpuser
table: '*.*'
privileges:
- SELECT, LOCK TABLESThe biggest hurdle was misunderstanding a permissions error. Even though I'd added mysqldumpuser to the config.yaml, it wasn't actually added to the database. Once I checked in Sequel Pro and saw that (and added it) this worked much better.
This is my db_backup script:
#!/bin/bash
#
# Create individual SQL files for each database. These files
# are imported automatically during an initial provision if
# the databases exist per the import-sql.sh process.
mysql -e 'show databases' | \
grep -v -F "information_schema" | \
grep -v -F "performance_schema" | \
grep -v -F "mysql" | \
grep -v -F "test" | \
grep -v -F "Database" | \
while read dbname; do mysqldump -u mysqldumpuser -p "$dbname" > /var/www/db_backups/local/"$dbname".sql && echo "Database $dbname backed up..."; done…which is still asking for a password at the prompt, so I have to figure out how to store that as a Vagrant ENV variable or something. Ok.
Edit: This seems to work. .my.cnf file on the Vagrant side with the mysqldumpuser info and then remove the -p part of db_backup
