How to Manipulate the MySQL Database of your Vagrant from PhpStorm
March 03, 2017Quentin Febvre2 min read
Why should I care?
How many hours have you spent logged on your Vagrant trying to type your query, copying in a text editor, pasting, then raging because the console goes crazy and you have to start all over again?
Well, spend five minutes to follow the next steps and see the pain of manipulating your data disappear!
Open your project in PhpStorm and open the DataSource window:
- Click on View -> Tool Windows -> Database
- Click on the Database sidebar, click on new -> Datasource -> MySQL
The configuration window will appear
Step 2: configure the ssh tunnel
- Open your terminal.
- Display the configuration of your ssh connection with the command
- On the configuration window, click on the
- Report the host, user and port
- Choose the Auth Type "Key pair (OpenSSH)" and report the path of the IdentityFile
Click on the
apply and then click on
Test Connection, you should see an error message saying you've got the wrong user/password combination
Step 3: Set up the database configuration
- In PhpStorm :
- Click on the
General tab of the configuration of your datasource
- Fill the credentials, host and port. If you're using Symfony, you can find it in the
- Click on
- Finally, click on
Case 1 -> It works! congratulations you can now manipulate your DB from within your IDE
Case 2 -> You get a wrong user/password combination error. Don't panic! just do the following:
- SSH into your Vagrant:
- Change to root user
- Log as root user to your MySQL DB:
- Run the following queries (don't forget to replace yourdatabase, _youruser_ and your\password_):
GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'127.0.0.1' identified by 'your_password';
You now have granted your user to login to using the host "127.0.0.1"
You can now go to PhpStorm and test your connection again and it should work!
A few use examples:
- Explore the schema of your tables on the sidebar
Open the console file and enjoy:
- the autocomplete
- the syntactic correction and coloration
- write multiple queries and execute the one you want (Ctrl + Enter)
- paginated scrollable results
- execute multiple queries at the same time,
- Update or add data to your database from the graphical interface.
Web Developer at Theodo