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 (link: https://github.com/Varying-Vagrant-Vagrants/VVV/ text: 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 (link: https://github.com/emyl/vagrant-triggers text: vagrant-triggers) to do a mysqldump of all the databases and tables and I'm halfway towards having that working with the (link: http://puphpet.com text: puphpet) `config.yaml`
Particularly helpful references:
- the VVV (link: https://github.com/Varying-Vagrant-Vagrants/VVV/tree/master/config/homebin text: 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: vagrant
```
and a mysql user `mysqldumpuser` with `SELECT` privileges on all tables.
```
user: mysqldumpuser
table: '*.*'
privileges:
- SELECT, LOCK TABLES
```
The 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*: (link: http://stackoverflow.com/questions/9293042/mysqldump-without-the-password-prompt text: This seems to work.) `.my.cnf` file on the Vagrant side with the `mysqldumpuser` info and then remove the `-p` part of `db_backup`