How to Login Mysql (10 Examples)

MySQL provides different ways to connect or login. We can login MySQL via command line interface, programatically or GUI. Here are 10 examples related to logging in to a MySQL database using different methods and contexts:

1. Basic Login via Command Line

The most popular way to login MySQL is using the mysql command via Linux shell. We can provide the user name with -u and password interactively with the -p option. The password will be asked and do not displayed on the terminal for the security reasons.

mysql -u root -p

• Explanation: Prompts for the root user’s password to log in to MySQL.

2. Login with Hostname

We can specify the hostname directly to the mysql login using the -h option. We can also provide the IP address using the -h option.

mysql -h localhost -u username -p

• Explanation: Specifies the hostname (localhost) and the username (username). It prompts for the password.

3. Login with a Specific Port

MySQL use the TCP/3306 port by default. We can specify the port number using the -P option. Sometimes MySQL may be configured to use different port than the default one.

mysql -h localhost -P 3306 -u username -p

• Explanation: Logs in to MySQL using a specific port (3306).

4. Login without Prompting for Password

By default the -p option is used to enter login password interactely. But sometimes we may need to put password in the command line. We can put the password after the -p option. Keep in mind that this usage is not secure.

mysql -u username -ppassword

• Explanation: Directly provides the password (password) in the command. This is not secure as the password is exposed.

5. Login with a Configuration File

We can use MySQL configuration file in order to connect. The configuration file is name as my.cnf . The configuration file can be specified by --defaults-file option like below.

mysql --defaults-file=/path/to/my.cnf

• Explanation: Uses a configuration file (my.cnf) that contains the login credentials.

6. Login to a Specific Database

We can specify the database name we want to work with during MySQL login. The database name is specified as the last argument of the mysql command.

mysql -u username -p database_name

• Explanation: Logs in directly to a specific database (database_name).

7. Login as a Different MySQL User

We can specify the login user name other than root by using the -u option like below.

mysql -u new_user -p

• Explanation: Logs in as new_user instead of the default user.

8. Login with SSL/TLS Encryption

The mysql connection use SSL/TLS encryption for secure connection. We can specify custom SSL/TLS certificate with the --ssl-ca option with the certificate path.

mysql -u username -p --ssl-ca=/path/to/ca-cert.pem

• Explanation: Uses SSL/TLS to encrypt the connection.

9. Login Using MySQL Workbench

We can use the MySQL Workbench GUI tool in order to login mysql. Take the following steps in order to use MySQL Workbench for GUI login to mysql.

• Instructions:
  1. Open MySQL Workbench.
  2. Click on the + icon to create a new connection.
  3. Enter the connection name, hostname, port, username, and password.
  4. Click Test Connection to verify, then OK to save.

10. Login Using a PHP Script

MySQL can be used with lots of programming languages. We can use the PHP script or application in order to login MySQL. we can use the mysql function by providing the servername , username , password and dbname in order to login mysql programatically.

<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

$conn = new mysqli($servername, $username, $password, $dbname);

if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
echo "Connected successfully";
?>

• Explanation: This PHP script connects to a MySQL database and checks if the connection is successful.

These examples cover a variety of login scenarios, from basic command-line usage to connecting via a PHP script or MySQL Workbench.