Connection

connection is a computer science facility that allows the user to connect with the database server software. A user can connect with the database server, whether on the same machine or remote locations. Therefore, if we want to work with the database server to send commands and receive answers in the form of a result set, we need connections. In this article, we are going to learn how we can connect to MySQL Server in various ways.

MySQL Connection Types

MySQL provides various ways to connect with the database server. Once we have installed the MySQL server, we can connect it using any of the client programs that are listed below:

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

MySQL Server Connection Using command-line client

MySQL command-line client program provides interaction with the database server in an interactive and non-interactive mode. We can see this program in the bin directory of the MySQL’s installation folder. We can open the MySQL command prompt by navigating to the bin directory of the MySQL’s installation folder and type:

MySQL 

If we find the MySQL program in the PATH, we can use the below command to connect to the MySQL Server:

mysql -u root -p 

In the syntax, the -u root indicates that we will connect to the MySQL server using the root user account and -p instructs MySQL to ask for a password.

Next, we need to type the password for the root user account and press Enter. If everything is correct, it should give the screen as follows:

This screen indicates that we have successfully connected with the MySQL database server, where we can send commands and receive answers in the form of a result set.

Suppose we want to display all databases available in the current server; we can use the command

mysql> SHOW DATABASES; 

If you want to disconnect the opened MySQL database server, you need to use the exit command.

mysql> EXIT;  

Connect to Database Server Using MySQL Workbench

We can connect to the MySQL database server in workbench by using the following steps:

Step 1: Launch the MySQL Workbench. We should get the following screen:

Step 2: Navigate to the menu bar, click on the ‘Database’ and choose Connect to Database option or press the CTRL+U command. We can also connect with the database server by just clicking the plus (+) button located next to the MySQL Connections. See the below image:

Step 3: After choosing any of the options, we will get the below screen:

Step 4: Fill the box to create a connection, such as connection name and username, whatever you want. By default, the username is the root, but we can also change it with a different username in the Username textbox. After filling all boxes, click the Store in Vault … button to write the password for the given user account.

Step 5: We will get a new window to write the password and click the OK button.

Step 6: After entering all the details, click on the Test Connection to test the database connectivity is successful or not. If the connection is successful, click on the OK button.

Step 7: Again, click on the OK button for saving connection setup. After finishing all the setup, we can see this connection under MySQL Connections for connecting to the MySQL database server. See the below output where we have Localhost3 connection name:

Step 8: Now, we can click this newly created connection that displays the current schemas and a pane for entering queries:

Connect to MySQL Server Using PHP Script

The simplest way to connect with the MySQL database server using the PHP script is to use the mysql_connect() function. This function needs five parameters and returns the MySQL link identifier when the connection becomes successful. If the connection is failed, it returns FALSE.

Syntax

The following is the syntax for MySQL connection using PHP Script:

connection mysql_connect(server, user, passwordd, new_link, client_flag);  

Let us explain the mysql_connect() function parameters:

Server: It is the name of a host that runs the database server. By default, its value will be lcalhost:3306.

User: It is the name of a user who accesses the database. If we will not specify this field, it assumes the default value that will be the name of a user that owns the server process.

Password: It is the password of a user whose database you are going to access. If we will not specify this field, it assumes the default value that will be an empty password.

New_link: If we make a second call with the same arguments in the mysql_connect() function, MySQL does not establish a new connection. Instead, we will get the identifier of the already opened database connection.

Client_flags: This parameter contains a combination of the below constants:

• MYSQL_CLIENT_SSL: It uses SSL encryption.
• MYSQL_CLIENT_COMPRESS: It uses a compression protocol.
• MYSQL_CLIENT_IGNORE_SPACE: It provides space after function names.
• MYSQL_CLIENT_INTERACTIVE: It provides a timeout before closing the connection.

If we want to disconnect from the MySQL database server, we can use another PHP function named mysql_close(). It accepts only a single parameter that will be a connection returned by the mysql_connect() function. Its syntax is given below:

bool mysql_close ( resource $link_identifier );  

If we do not specify any resource, MySQL will close the last opened database. This function returns true when the connection is closed successfully. Otherwise returns a FALSE value.

Example

The following example explain how to connect to a MySQL server using PHP Script:

<html>  

   <head>  

      <title>MySQL Server Connection</title>  

   </head>  

   <body>  

      <?php  

         $servername = 'localhost:3306';  

         $username = 'javatpoint';  

         $dbpass = 'jtp123';  

         $conn = mysql_connect($servername, $username, $password);          

         if(! $conn ) {  

            die('Connection failed: ' . mysql_error());  

         }  

         echo 'Connection is successful';  

         mysql_close($conn);  

      ?>  

   </body>  

</html>