How to Manipulate the MySQL Database of your Vagrant from PhpStorm

Untitled drawing

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

config_ssh_vagrant

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 yourdatabase, youruser 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.

You liked this article? You'd probably be a good match for our ever-growing tech team at Theodo.

Join Us

  • Have configured connection and when I click Test connection it says – successful
    But I have an error when trying to refresh schema or tables.

    [08S01] Communications link failure The last packet sent successfully to the server was 0 milliseconds ago. The driver has not received any packets from the server. java.net.ConnectException: Connection refused

  • Quentin Febvre

    Hi Oleg,

    thanks for your reaching out and sorry for the late reply,

    here are a few leads on what might be going on:

    – the config of your connection hasn’t been saved: be sure to click on the apply button
    – the driver isn’t up to date (a notification would be present in the config window)
    – the vagrant needs to be reloaded

    I hope this was helpful, don’t hesitate to tell me if this wasn’t enough I’ll try to figure out what happened!
    Cheers :)

  • Hi

    Thanks for ideas.
    I have found already that reason is PhpStorm itself. After running for some time it lost a connection. But if I restart PhpStorm it able to connect again. Nothing wrong with Vagrant or MySQL.

  • Pingback: Setting up vagrant mysql DB in PHPStorm – David Jarrin()

  • Axtroxality

    This is how all tech articles should be formatted.

  • Quentin Febvre

    Thanks, glad you like it!

  • adamsafr

    Thank you! You saved my day :)

  • thank you you save hour of job, congrats!