Webstorm Mysql

Posted on  by



It can be a bit painful to get the database connection started from your PHPStorm IDE to your MySQL database.

  1. Webstorm Mysql Data
  2. Webstorm Mysql Plugin
  3. Webstorm Mysql Software

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. MySQL Workbench enables a DBA, developer, or data architect to visually design, model, generate, and manage databases. It includes everything a data modeler needs for creating complex ER models, forward and reverse engineering, and also delivers key features for performing difficult change management and documentation tasks that normally require much time and effort. MySQL Workbench enables a DBA, developer, or data architect to visually design, model, generate, and manage databases. It includes everything a data modeler needs for creating complex ER models, forward and reverse engineering, and also delivers key features for performing difficult change management and documentation tasks that normally require much time and effort. Open a terminal (command prompt in Microsoft Windows) and open a MySQL client as a user who can create new users. For example, on a Linux system, use the following command; $ sudo mysql -password This connects to MySQL as root and allows access to the user from all hosts.

Mysql

As a quick hint, this is where it went wrong with mine:

Intellij

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.

Webstorm Mysql
  • MySQL wasn’t listening for incoming connections on the network (change directive bind-address to 0.0.0.0
     in /etc/mysql/my.cnf and restart MySQL)
  • My database user was only allowed to connect from localhost

Edit the file /etc/mysql/my.cnf to configure MySQL to listen for incoming database connections from the network:

Then I added a new user (dbuser) which is allowed to connect from my workstation (workstation.i2s.nl):

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!

Webstorm Mysql Data

Step 1:

Webstorm Mysql Plugin

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
Tutorial

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

Mysql

Webstorm Mysql Software

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 yourpassword_):
  • 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.