Vscode remote mysql

The installation itself is easy but you will need to download the code from here and not the usual places for MySQL products.  You will, of course, have to have VS Code installed first, and be warned that some of the more tantalizing links for things like documentation are not connected.

MySQL Shell for VS Code installation screen and yes, you will need VS Code installed first.

Usage

The interface is familiar to that of MySQL Workbench but seems more intuitive.  You will need to set up a connection to your server with all the usual host, user, and password information. From there you can create a session to that server.

The big change is to remember to use Control-ENTER to send commands to the MySQL instance.  The GUI allows easy SQL query editing. But be warned there are problems with the preview.  A familiar query to the world database did not work with the GUI but ran perfectly with the stand-alone version of MySQL Shell.  Other queries worked well.

The first query was not correct. The second query went a little better.

For some reason, the first query did not get sent correctly to the server.

The correct answer to the first query.

Quick Take

This is an impressive product even though it is a preview.  This is far better than the old MySQL CLI and the GUI editing makes the very good MySQL Shell features extremely useful.

MySQL is a relational database management system based on SQL-Structured Query Language used for accessing and managing records in a database. It can be easily connected with programming languages such as Python, Java, and PHP to serve various purposes that require CRUD ( Create,Read,Update,Delete) operations. MySQL is based on a client – server architecture which follows a request – response cycle. Clients make requests through the GUI screen or command prompt by using specific SQL expressions on MySQL. The server application responds with the requested expressions and produces the desired result on the client-side. 

Steps to Connect to MySQL Server using VSCode:

Step 1: Open Visual Studio Code.

Step 2: Go to -> Extensions, to search for MySQL extension, type : MySQL

Step 3: Open the extension called MySQL Management Tool and install it.
 

Step 4: Now click on the Explorer option or press ( Ctrl + Shift + E ), MySQL is added to this section.
Step 5: Click on Add Connection to create a new connection.
 

Step 6:Here we have to provide the server address. The MySQL server is installed on your computer so the host name is, ‘localhost’.

Step 7: Then provide a user name and the default username is ‘root’.

Step 8: Provide the password that you created for this user at the time of MySQL installation and then provide the port number i.e; 3306.

Step 9: Now you’ll see a connection is added to MySQL section from which databases can be accessed. You may see an authentication error. This error means that you are using the version 8 of MySQL server. This version defines and uses a new authentication method instead of old MySQL native password. If the MySQL Client does not use this new authentication method this error would be displayed.

Step 10: The solution is to create a new SQL user that uses old authentication method. Go to the folder where MySQL is installed.

Step 11: Open the bin folder. Select the file path and type ‘cmd‘.

 Step 12: Now to connect to the server, type the following command:

mysql -u root -p

Here -u means that you’ve to provide the username that is ‘root’ and -p means you have give the password.
 

Step 13: Create a new user:

CREATE USER 'sqluser'@'%' IDENTIFIED 
WITH mysql_native_password BY 'password' ; 

 A new user is created by the name ‘sqluser’ with password ‘password’. Provide this user with all the privileges :

GRANT ALL PRIVILEGES ON *.* TO 'sqluser'@'%' ;

 We need to validate these privileges :

  FLUSH PRIVILEGES;

Step 14: Delete the previous connection.

 Step 15: Add a new connection by providing the following details:

• Host: localhost
• User: sqluser
•  Password: password
• Port number : 3306
                                              

Step 16: The connection is established and you can see the existing databases.To create a new database, right click on localhost and create a new query.

Step 17: Type the following commands to create a new database and table inside it.

CREATE DATABASE myrestaurant ;
CREATE TABLE IF NOT EXISTS myrestaurant.users(
    id INT(10) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(200) NOT NULL,
    phone VARCHAR(200),
    address VARCHAR(200),
    password VARCHAR(200) NOT NULL
);
INSERT INTO myrestaurant.users(name,phone,address,password)
VALUES
('Gaurav','123456789','Mumbai,India','pass134'),
('Sakshi','987654321','Chennai,India','pass456');

 Step 18: Run this MySQL query.

 Step 19: Refresh the connection to see the new table named ‘users’ added to this database.Right click on ‘users’ and ‘select top 1000’.

 Step 20: Run this MySQL query and see the results.

In this way databases can be accessed and queries can be executed through VSCode. Since VSCode supports other programming languages also, it is easier to do database management through it. 

How to connect to remote MySQL in VS Code?

Can you use MySQL with VS Code?

How do I connect to a remote database in VS Code?

Can MySQL be accessed remotely?