How to Manipulate the MySQL Database of your Vagrant from PhpStorm

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!

Step 1:

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.
• cd ~/path/to/your/project
• Display the configuration of your ssh connection with the command vagrant ssh-config
• On the configuration window, click on the SSH/SSL tab
• 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 parameters.yml file.
• Click on Apply
• Finally, click on Test Connection

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: vagrant ssh
• Change to root user sudo su
• Log as root user to your MySQL DB: mysql -uroot
• Run the following queries (don't forget to replace your_database, your_user and your_password):
• GRANT ALL PRIVILEGES ON your_database.* TO 'your_user'@'127.0.0.1' identified by 'your_password';
• FLUSH PRIVILEGES;
  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:

1. Explore the schema of your tables on the sidebar
2. 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,
3. Update or add data to your database from the graphical interface.