How to Connect MySQL in Linux (2024)

MySQL & mysqldump employ parameters from the command line to tailor their connection to the MySQL server. Please refer to Section 4.2.5, “Connecting to the Server Using URI-Like Strings or Key-Value Pairs,” for details about how to connect clients like MySQL Shell using URI-like connection string or key-value pairs. If you’re having trouble establishing a connection to MySQL, check out Section 6.2.22 for further details.

Introduction

Connection parameters, like the hostname where a server runs & your MySQL account’s password and username, are required for a client software to establish a connection with the MySQL server. If you need to change the preset value for a connection parameter, you can do so by specifying a new value for that parameter via the command line or a setting file.

If you run the client program with the –password or -p option but do not supply a password, it will display a prompt asking for the password. (In these situations, mydb is not considered a password as it follows a space and is not followed by another password choice.)

MySQL’s password prompting library routine may impose an eight-character cap on passwords on certain platforms. This restriction does not exist in MySQL but rather in the system library. MySQL’s internals do not impose a maximum length on passwords. On impacted systems, you can avoid the restriction by setting the password in a possibility file (for more information, see Section 4.2.2.2, “Using Option Files”). Alternatively, you might try changing your MySQL password to something with fewer than eight characters, but keep in mind that such passwords are typically less secure.

Set Up a MySQL Database on Linux

Installing a MySQL server as well as ODBC driver, and then setting up Media Server to utilize the driver for connecting to the database, are prerequisites for using MySQL with Media Server.

For candidates who want’s to advance their Career in Operating System & Administration, Linux  Training Course is the best option.

So, here are detailed steps necessary to install MySQL on CentOS 6.

Installing MySQL for Media Server under Linux:

1. Set up a server running MySQL. (Do check that the mysql command-line program is part of the package.) Please see the MySQL manual at www.mysql.com for further information.

2. Set up the database server so that Media Server can access it.

• Launch the MySQL server’s configuration file (often called my.ini)
• Enter the configuration parameter max_allowed_packet=1073741824 to enable Media Server to transmit huge amounts of binary data (images) to the databases. 
• Remove the configuration file after saving changes.

3. To include the MySQL bin directory in your PATH variable, type in the following command:

(export PATH=$PATH:binDirectoryPath)

Please take note that this action prepares the terminal for using the mysql command to launch the mysql command-line tool. 

4. To launch mysql, you have to enter the full path to the mysql.exe file in the terminal unless you have already set the PATH environment variable to include the directory.

5. Launch the mysql steps command prompt. Type the following into the terminal:

• To make a new database, you can use the CREATE DATABASE statement. Please use the following parameters for your database.

Database name	Any names
Character set	Should be Unicode–either UCS2/UTF8
Collation	Choose any which is consistent with proper encoding.

• To use the Media Server database, use the my.sql script located in the server’s installation folder. This script creates the database structure necessary for Media Server to function.
• Put an end to the mysql command prompt session. Type the following into the terminal:

(-u userName -p -v -D “source path/my.sql” -e “databaseName” -e “where,”)

userName	is MySQL username.
databaseName	is the database name you made in the 3rd step.
path	is this script file path.

Non-interactive script execution via the terminal prevents script from continuing execution after an error.

6. Provide access to the MySQL server for users that Media Server will use in order to connect.  Essential rights include:

Database	Create Temporary Table
All tables	Insert, Select, Update, Delete
All functions & stored procedures	Execution

If security doesn’t pose an issue, give full access.

mysql

Execute the GRANT procedures:

* TO username 

GRANT SELECT, 

INSERT, 

UPDATE, 

DELETE ON databasename

*TO username

• EXECUTE RIGHTS GRANTED ON databaseName.username * TO; where,

databaseName	is the name of the particular database you developed in the 2nd step.
userName	is the username which Media Server can not connect.

Close the mysql command-line tool:
quit

7. Upgrade to unixODBC driver management 2.2.14. In the terminal, do sudo yum install unixODBC when you have access to the necessary Yum repository

8. Mount the MySQL device driver. In the terminal, type sudo yum install mysql-connector-odbc if you are connected to the appropriate Yum repository.

9. It’s time to set up the data source:

• It is best to use an editor for text to open the odbc.ini file. The /etc directory is the typical location for this file.
• Fill in a square parentheses with the name of the data source. This name may be whatever string you like. Case in point:

[MySQL_1]

• Please modify the following settings under “data source name:“

Parameter	Description
Driver	The driver to utilise.
Server	The hostname or IP address of the particular server that database server has been installed on.
Port	The port to utilise to convey with this database server.
User	This username is attached to the database server.
Password	The particular password for an user account which connects to those database servers.
Database	The database name which you developed in the 3rd step.

For example:
[MySQL_1]

Driver=MySQL

Server=localhost

Port=5432

User=mysql

Password=password

Database=myDatabase

• NOTE:
  In addition to the parameters checked with Media Server, you may configure extra options in this file.
• Don’t forget to save your work and exit the file.

10. Configure this ODBC driver

• Open the odbcinst.ini file with a text editor. This file is usually stored in the /etc directory.

• If not already present, add the database server name in square brackets. For example: [MySQL]

• Set the following parameters.

Parameter	Description
Description	A description of the driver instance.
Driver64	The location of the MySQL driver library file.
Setup64	The location of the driver installer file.
FileUsage	Set this parameter to 1.

For example:
[MySQL]

Description=ODBC for MySQL

Driver64=/usr/lib64/libmyodbc5.so

Setup64=/usr/lib64/libodbcinST.so

FileUsage=1
NOTE: You can change other settings in this file, but Media Server has not been used to test them.

Save the file and close it.

11. Now, you can set up Media Server so that it can join to the database.

Conclusion

Using JDBC (Java Database Connectivity), we may establish a connection from Java code to our MySQL database. To simply execute our query, assertion, and get data from the database, we can use JDBC, one of the standard APIs for database connectivity. You must first manually populate a list in your MySQL database with some data before running the aforementioned code.