A 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:
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:
If we find the MySQL program in the PATH, we can use the below command to connect to the MySQL Server:
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 as follows:
It will give the below output:
If you want to disconnect the opened MySQL database server, you need to use the exit command.
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.
The following is the syntax for MySQL connection using PHP Script:
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:
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:
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.
The following example explain how to connect to a MySQL server using PHP Script: