JumpStart PHP Environment - How do I work with databases?

[Bill_Bryan]

I’ve made a good deal of progress, for a noob, I can use Vagrant and VirtualBox to create a VM and work with the files, no problem. But in reading Jumpstart PHP Environment and Vagrant: Up and Running I’m not seeing any good explanation how to work with databases, and of course I need to work with databases. I’ve done some googling and learned that the general workflow is to export (mysqldump) the database to a *.sql file on my hard drive before I halt or destroy the VM, then import it again when I ‘vagrant up’ the VM again.

And I know Vagrant and VirtualBox VMs don’t come with phpMyAdmin, so I installed MySQL Workbench and got that up and running. I can create and see my database (it’s a test database called globe_bank). I can execute SQL queries on it. I can SELECT * and see the data in my two tables. I can use the command line or MySQL Workbench to do all those things. But that’s as far as I can go.

If I try to export data and structure from the database with MySQL Workbench, I get an error because the versions of mysqldump and mySQL don’t match, as shown in the graphic below.

If I ‘continue anyway’ it fails with error code 2. It doesn’t create a file.

If I ‘vagrant ssh’ into the VM, it says mysqldump is ‘version 10.13 Distrib 5.7.23 for Linux’. I have no idea why MySQL Workbench says mysqldump is version 8.0.12. Is it using its own instance of mysqldump?

If I ‘vagrant ssh’ into the VM and give the command ‘mysqldump database_name’ I get:

error: 1045: Access denied for user ‘vagrant’@‘localhost’ (using password: NO) when trying to connect

Well, I understand that. It’s because the login for mysql is

• username: homestead
• password: secret

If I log in to ‘mysql -u homestead -p’ I can successfully log into mysql and I can see the database and SELECT * to see the contents of the tables. Everything in the database is working great, but if I try ‘mysqldump --databases globe_bank > dump.sql’ I get:

ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ‘mysqldump --databases globe_bank > dump.sql’ at line 1

But I looked at the online manual for MySQL version 5.7, and it looks like I’m using the right syntax.

It’s a good thing I lost almost all my hair already, because this is making me crazy.

I need to be able to create a database in my VM, dump it to my hard drive, and then re-import it the next time I ‘vagrant up’ my VM again. What am I doing wrong, besides everything?

[rpkamp]

You need to run the mysqldump program from the command line, not from within MySQL.

So first you vagrant ssh into to the machine, and directly when logged in you run mysqldump --databases globe_bank > dump.sql

As for the MySQL workbench, that does seem to use a local mysqldump in Windows instead of the one in your Vagrant box. I would stick to running it manually in your Vagrant box and don’t try to fix MySQL workbench, as those kind of fixes can take ages (if they work at all).

[Bill_Bryan]

Yes, that was what I thought should work, and what I tried first. Use ‘vagrant ssh’ to access the VM directly, and then:

mysqldump --databases globe_bank > dump.sql

But that’s when I get this error:

Got error: 1045: Access denied for user ‘vagrant’@‘localhost’ (using password: NO) when trying to connect vagrant@homestead:~/Code/Project/public$

Of course, I can’t ‘logout’ of Vagrant to login as ‘homestead’ – that would exit Vagrant and close Git-Bash.

What you wrote sounds very simple and correct. I wonder why it isn’t working for me? How do I get access as vagrant@homestead?

[rpkamp]

The options for username/password you use for mysql work for mysqldump as well. So you could do:

mysqldump -u homestead -p --databases globe_bank > dump.sql

[Bill_Bryan]

OMG, thank you so much. That worked perfectly, and was the answer that eluded me for too long.

Thanks again!