Table of contents
1.
Introduction
2.
Types Of Connection Of MySQL
3.
PHP Script
3.1.
Example 
4.
Workbench of MySQL
5.
Command-line client
6.
FAQs
7.
Key Takeaways
Last Updated: Mar 27, 2024

MySQL Connection

Author Divyansh Jain
0 upvote
Career growth poll
Do you think IIT Guwahati certified course can help you in your career?

Introduction

A connection is a feature of computer science that allows a user to communicate with database server software. A user on the same workstation or from a remote place can access the database server. As a result, we'll need connections to send commands to the database server and get results as a result set. In this lesson, we'll learn how to connect to MySQL Server in a variety of ways.

Listen To Your Heart Love GIF by The Bachelor

Source: The Bachelor

Types Of Connection Of MySQL

  1. PHP Script
  2. Workbench of MySQL
  3. Command-line-client

 

Let’s deep dive into them one by one: 

PHP Script

We can connect to MySQL database server using PHP script by employing the mysql_connect() function. It is the easiest and fastest way to connect to the MySQL database server.

  • Three parameters are required to make the connection successful. 
  • When failed to connect false is returned.
  • When the connection is successful it returns the MySQL link identifier.

 

SYNTAX

mysql_connect(server,user,password,new_link,client_flag);
You can also try this code with Online PHP Compiler
Run Code

where, 

server: is Optional and it specifies the server to which you want to connect (you can also include a port number). "hostname:port" or a path to a local socket for the localhost, for example). "localhost:3306" is the default value.

user: is optional which specifies the username with which to log in. The name of the user who owns the server process is the default value.

password: is Optional which specifies the password to use when logging in. The default value is " ". 

new_link: is optional in which MySQL does not establish a new connection when we call the mysql_connect() function again with the same arguments. Instead, we'll get the identifier of a database connection that has already been opened.

client_flag: is optional. 

This parameter is made up of the following constants:

  • MYSQL CLIENT SSL: It encrypts data with SSL.
  • MYSQL CLIENT COMPRESS is a compression protocol used by MySQL.
  • MYSQL CLIENT IGNORE SPACE: It adds extra space between function names.
  • MYSQL CLIENT INTERACTIVE: Before ending the connection, it offers a timeout.

 

Source: Salesprogress

Another PHP function called mysql_close() can be used to disconnect from the MySQL database server. It only takes one parameter, which is the connection returned by the mysql_connect() function. Its syntax is as follows:

mysql_close($conn);  
You can also try this code with Online PHP Compiler
Run Code

If no resource is specified, MySQL will close the most recently opened database. When the connection is successfully ended, this function returns TRUE. Otherwise, FALSE is returned.

Example 

<?php  
         $servername = 'hostatcodingninjas';  
         $username = 'codingninjas';  
         $dbpass = 'pass';  
         $conn = mysql_connect($servername, $username, $password);          
         if(! $conn ) {  
            die('Failed to connect: ' . mysql_error());  
         }  
         echo 'Successfully connected';  
         mysql_close($conn);  
      ?>  
You can also try this code with Online PHP Compiler
Run Code

Let us now move to the second part: 

Workbench of MySQL

Mysql databases can be connected to the workbench using the following steps:

Step 1: Open the MySQL workbench and you will see the following screen.

Step 2: Click on the [+] button to connect to the database server.

Step 3: After clicking on the plus icon, you will get redirected to the below screen. You can fill in the connection name here and even change other details too. But it’s better not to change anything other than the connection name, at least if you are doing it the first time. And simultaneously you can put the password for the given user in the “Store in vault…” section.

Step 4: We'll be sent to a new window to type in the password and then click the OK button.

Step 5: After you've entered all of the information, click Test Connection to see if the database connection is working. Click the OK button if the connection is successful.

Step 6: To save the connection settings, click the OK button once again. We may see this connection within MySQL Connections to connect to the MySQL database server when we've completed all of the setups. See the result below for the name of the CodingNinjas connection:

Step 7: We can now click this freshly formed connection, which displays the existing schemas as well as a query pane:

Yeah, that’s it. You are good to go.

Leaving See Ya GIF by 20th Century Fox Home Entertainment

Source: 20th Century Fox Home Entertainment

Command-line client

In both interactive and non-interactive modes, the MySQL command-line client software allows you to interface with the database server. This software can be found in the bin directory of the MySQL installation folder. To access the MySQL command prompt, go to the bin directory in the MySQL installation folder and type:

MySQL - -version

Now if you get the following result then you have successfully installed MySQL and you can also see the version which you have downloaded by using the above command.

 

If MySQL program is found in the path, use the following command to connect the MySQL server. 

mysql -u root -p

 

Here -u root indicates a connection to the MySQL server using the root user account and also using -p MySQL will ask for the password that is needed to be entered.

Now we need to enter the password and then 

  • If the password is found to be incorrect then the command line client will close and you will have to restart the process.
  • And if the password is found to be correct then the following screen will appear

Source: sheepdogguides

This indicates that the connection with the MySQL database server was successful. Now we can enter commands and get answers.

To display all the available databases we can use the following command line:

mysql> show databases;

 

The output will be as follows 

Now to disconnect the opened MySQL database server use the exit command.

mysql> EXIT;  

the above code represents that you have successfully closed MySQL.

FAQs

  1. What happens when we do not specify the hostname in the PHP script connection type?
    When we do not provide the hostname in the PHP  script connection type it assumes the default value that is the name of the user that owns the server process.
     
  2. What happens when the password entered in the command-line client is wrong?
    When the wrong password is entered in the command line client then the command line client will shut down and you will have to restart it again.
     
  3. Why is -p used in the command line prompt?
    In command-line prompt -p is used so that the command line can take password as input from us.
     
  4. How can I figure out how many MySQL connections there are?
    The threads connected variable can be used to determine whether a connection is active or not. The variable indicates how many connections are currently open. Here is the output of mysql> show status were 'variable name' = 'Threads connected'.
     
  5. What is the maximum duration of a MySQL connection?
    Database connections will be timed out by myself based on two server options: By default, both are 28,800 seconds (8 hours). If your connections are persistent (initiated via MySQL connect), you can reduce these amounts to 600 (10 minutes) or even 60 minutes (1 minute).

Key Takeaways

So now that we have learned how can we build a connection with the database server in various ways such as using PHP, workbench, and command-line clients.

Recommended Readings:

Hope you learned something. But the knowledge never stops, so to better understand the Database management system, you can go through many articles on our platform. Don't stop here, Ninja; check out the Top 100 SQL Problems to get hands-on experience with frequently asked interview questions and land your dream job.

Happy Learning Ninja :) 

Live masterclass