Versions are introduced in any product to upgrade by adding extra features and removing unnecessary ones, fixing the bugs etc. The process of versioning is actually important to make the product more efficient with growing technology.

A product is generally released after performing phases of testing: alpha testing, beta testing, gamma testing, and then it is produced once all these tests are passed.

Whenever MySQL is installed, we must choose the version to install along with its distribution format. The latest version of MySQL is 8.0 with its minor version being 8.0.34. To install the MySQL server, there are two methods: Development release and General Availability.

The Development Release contains all the latest features but it is not recommended to be used in production. The General Availability Release is more of a production release which can be stably used in production.

MySQL Versions

MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses. It is developed, marketed and supported by MySQL AB, which is a Swedish company. MySQL was first internally released on 23^rd May, 1995, and until Oracle acquired Sun Microsystems, version 3.19 to version 5.1 were released.

Version 5.1

The version 5.1 of MySQL had its production release on 27th November, 2008 by adding extra features like event scheduler, partitioning, plugin API, row-based replication, server log tables.

But, version 5.1 contained 20 known bugs that gave out wrong results, along with the 35 bugs from version 5.0. However, almost all of them are fixed as of release 5.1.51. Also, MySQL 5.1 and 6.0 (in alpha test phase) showed poor performance in data warehousing, which could partially be due to its inability to utilize multiple CPU cores for processing a single query.

Version 5.5

MySQL Server 5.5 was a General Availability Release as of December 2010. The improved features in this version include −

• The default storage engine is InnoDB with improved I/O subsystem, which supports transactions and referential integrity constraints.

• Improved SMP support

• Semi-synchronous replication.

• SIGNAL and RESIGNAL statement was added in compliance with the SQL standard.

• Support for supplementary Unicode character sets utf16, utf32, and utf8mb4.

• New options for user-defined partitioning.

Version 5.6

The General Availability of MySQL 5.6 was released in February 2013. New features of this version included:

• Query optimizer has more efficient performance.

• Higher transactional throughput in InnoDB.

• New NoSQL-style memcached APIs.

• Improvements to partitioning for querying large tables.

• Better management of very large tables.

• TIMESTAMP column type that correctly stores milliseconds.

• Improvements to replication.

• Better performance monitoring by expanding the data available through the PERFORMANCE_SCHEMA.

• The InnoDB storage engine also provides support for full-text search and improved group commit performance.

Version 5.7

MySQL 5.7 was made generally available in October 2015. For the minor versions of MySQL 5.7, MySQL 5.7.8 and later, a support for a native JSON data type defined by RFC 7159 by August 2015.

Version 8.0

MySQL Server 8.0 was announced in April 2018, with new improved features. Currently, the minor versions in MySQL 8.0 start from 8.0.0 to 8.0.34. Previous MySQL Server 8.0.0-dmr (as a Development Milestone Release) was announced on 12th September, 2016.

Features Added in MySQL 8.0

The latest version of MySQL is 8.0. The following features are some of the newly added features to it:

• Data dictionary − In previous MySQL releases, dictionary data was stored in metadata files and non-transactional tables. MySQL now incorporates a transactional data dictionary that stores information about database objects.

• Atomic Data Definition Language(Atomic DDL) statements − An Atomic DDL statement combines the data dictionary updates, storage engine operations, and binary log writes associated with a DDL operation into a single, atomic transaction.

• Upgrade procedure − Previously, after installation of a new version of MySQL, the MySQL server automatically upgrades the data dictionary tables at the next startup, after which the DBA is expected to invoke mysql_upgrade manually to upgrade the system tables in the mysql schema, as well as objects in other schemas such as the ”sys” schema and user schemas.

  As of MySQL 8.0.16, the server also performs the tasks previously handled by mysql_upgrade. In addition, the server updates the contents of the help tables as well. A new –upgrade server option provides control over how the server performs automatic data dictionary and server upgrade operations.

• Session Reuse − MySQL Server now supports SSL session reuse by default with a timeout setting to control how long the server maintains a session cache that establishes the period during which a client is permitted to request session reuse for new connections. All MySQL client programs support session reuse. In addition, C applications now can use the C API capabilities to enable session reuse for encrypted connections.

• Security and account management − The security is improved greatly and greater DBA flexibility in account management is also enabled.

• Resource management − MySQL now supports creation and management of resource groups, and permits assigning threads running within the server to particular groups so that threads execute according to the resources available to the group.

• Table encryption management − Table encryption can now be managed globally by defining and enforcing encryption defaults.

• InnoDB enhancements − Several InnoDB enhancements were added, like, auto-increment counter value, index tree corruption, mem-cached plug-in, InnoDB_deadlock_detect, tablespace encryption feature, storage engine, InnoDB_dedicated_server, creating temporary tables in temporary tablespace, zlib library etc.

• Character set support − The default character set has changed from latin1 to utf8mb4. The utf8mb4 character set has several new collations, including utf8mb4_ja_0900_as_cs, the first Japanese language-specific collation available for Unicode in MySQL.

• JSON enhancements − Several enhancements and additions were made to MySQL”s JSON functionality.

• Data type support − MySQL now supports use of expressions as default values in data type specifications. This includes the use of expressions as default values for the BLOB, TEXT, GEOMETRY, and JSON data types, which previously could not be assigned default values at all.

• Optimizer − Optimizer is enhanced in various ways as well.

• Improved hash join performance − MySQL 8.0.23 reimplements the hash table used for hash joins, resulting in several improvements in hash join performance.

• Common table expressions − MySQL now supports common table expressions, both non-recursive and recursive.

• Window functions − MySQL now supports window functions that, for each row from a query, perform a calculation using rows related to that row.

• Lateral derived tables − A derived table now may be preceded by the LATERAL keyword to specify that it is permitted to refer to (depend on) columns of preceding tables in the same FROM clause.

• Aliases in single-table DELETE statements − In MySQL 8.0.16 and later, single-table DELETE statements support the use of table aliases.

• Regular expression support − Previously, MySQL used the Henry Spencer regular expression library to support regular expression operators.

• Internal temporary tables − The TempTable storage engine replaces the MEMORY storage engine as the default engine for in-memory internal temporary tables.

• Logging − Logging process is also improved in the newer versions.

In addition to all these, there are many other features added to the new versions of MySQL as well.

Features Deprecated in MySQL 8.0

The following are some of the many features that are deprecated in MySQL 8.0 and may be removed in the future series. Several alternatives mentioned can be used by applications.

• The utf8mb3 character set is deprecated. Please use utf8mb4 instead.

• ucs2, macroman and macce, dec, hp8 are also deprecated. You should use utf8mb4 instead.

• User-defined collations are deprecated.

• sha256_password is deprecated.

• The plugin form of validate_password is still available, but deprecated.

• The ENGINE clause for the ALTER TABLESPACE and DROP TABLESPACE statements is deprecated.

• The PAD_CHAR_TO_FULL_LENGTH SQL mode is deprecated.

• AUTO_INCREMENT support is deprecated for columns of type FLOAT and DOUBLE (and any synonyms).

• The UNSIGNED attribute is deprecated for columns of type FLOAT, DOUBLE, and DECIMAL (and any synonyms)

• FLOAT(M,D) and DOUBLE(M,D) syntax is deprecated.

• The ZEROFILL attribute for numeric data types and the display width attribute for integer data types are deprecated.

• The BINARY attribute is deprecated. However, the use of BINARY to specify a data type or character set remains unchanged.

• ASCII and UNICODE are deprecated (MySQL 8.0.28 and later). Use CHARACTER SET instead, in both cases.

In general, variables are the containers that store some information in a program. The value of a variable can be changed as many times as required. Each variable has a datatype specifying the type of data we can store in it such as integer, string, float etc.

• In some programming languages such as Java, C, C++ etc., we need to declare the datatype of a variable before assigning values to it.

• In languages like python the datatype of a variable is presumed based on the values assigned to it. There is no need of declaring the datatype separately.

• In MySQL there is no need to declare the datatype we can simply define a variable with a value using the SET statement.

Variables in MySQL

The main purpose of a variable is to label a memory location(s) and store data in it so that it can be used throughout the program.

The characters we use to declare and define a variables are called literals and a literal can be anything other than special characters, numbers and, reserved keywords.

In MySQL, there are three types of variables. The same is described below −

• User-Defined Variable

• Local Variable

• System Variable

User-Defined Variables

The User-Defined variable allows us to store a value in one statement and subsequently refer to it in another. To do so, MySQL provides SET and SELECT commands to declare a variable. These variable names will have the symbol “@” as a prefix. We can use either = or := symbols depending on the situation. The user-defined data type can be any of the following: integer, decimal, Boolean, etc.

Syntax

Following is the syntax to declare a user-defined variable in MySQL using the SET statement −

SELECT @variable_name = value

Example

In the following query, we are assigning a value to a variable using the SET statement as follows −

SET @Name = ''Michael

Using the SELECT statement, we can display the value of @name variable −

SELECT @Name;

Output

The output for the query above is produced as given below −

@Name
Michael

Example

Here, we are assigning a value to a variable using the SELECT statement −

SELECT @test := 10;

Output

On executing the given query, the output is displayed as follows −

@test := 10
10

Example

Let us create table with the name CUSTOMERS using the following query −

CREATE TABLE CUSTOMERS(
   ID INT AUTO_INCREMENT PRIMARY KEY,
   NAME VARCHAR(20) NOT NULL,
   AGE INT NOT NULL,
   ADDRESS CHAR (25),
   SALARY DECIMAL (18, 2)
);

Now, let us insert values into the above-created table using the INSERT INTO statement −

INSERT INTO CUSTOMERS (NAME, AGE, ADDRESS, SALARY) VALUES
(''Ramesh'', 32, ''Ahmedabad'', 2000.00),
(''Khilan'', 25, ''Delhi'', 1500.00),
(''Kaushik'', 23, ''Kota'', 2000.00),
(''Chaitali'', 25, ''Mumbai'', 6500.00),
(''Hardik'', 27, ''Bhopal'', 8500.00),
(''Komal'', 22, ''Hyderabad'', 4500.00),
(''Muffy'', 24, ''Indore'', 10000.00);

The CUSTOMERS table is created as follows −

ID	NAME	AGE	ADDRESS	SALARY
1	Ramesh	32	Ahmedabad	2000.00
2	Khilan	25	Delhi	1500.00
3	Kaushik	23	Kota	2000.00
4	Chaitali	25	Mumbai	6500.00
5	Hardik	27	Bhopal	8500.00
6	Komal	22	Hyderabad	4500.00
7	Muffy	24	Indore	10000.00

Now, let us declare a variable with the name @max_salary using the SELECT statement to display the maximum salary value from the CUSTOMERS table −

SELECT @max_salary := MAX(salary) FROM CUSTOMERS;

Then, we will select records from the table where the salary is equal to @max_salary variable −

SELECT * FROM CUSTOMERS WHERE SALARY = @max_salary;

Output

The output for the query above is produced as given below −

ID	NAME	AGE	ADDRESS	SALARY
7	Muffy	24	Indore	10000.00

Local Variables

The MySQL local variable can be declared using the DECLARE keyword. When we declare the local variable, the @ symbol is not used as prefix. This variable is a strongly typed variable, which means that we definitely need to declare a data type.

The MySQL DEFAULT keyword can be used while declaring a variable to set the default value of the variable. This is an optional parameter, if we do not define this, the initial value will be NULL.

Syntax

Following is the syntax to declare a local variable in MySQL −

DECLARE variable_name1, variabale_name2, ...

data_type [DEFAULT default_value];

Example

In the following example, we are using the DECLARE statement in a stored procedure.

DELIMITER //
CREATE PROCEDURE salaries()
BEGIN
   DECLARE Ramesh INT;
   DECLARE Khilan INT DEFAULT 30000;
   DECLARE Kaushik INT;
   DECLARE Chaitali INT;
   DECLARE Total INT;
   SET Ramesh = 20000;
   SET Kaushik = 25000;
   SET Chaitali = 29000;
   SET Total = Ramesh+Khilan+Kaushik+Chaitali;
   SELECT Total,Ramesh,Khilan,Kaushik,Chaitali;
END //

Now, let us call the stored procedure using the following query −

CALL salaries() //;

Output

Following is the output −

Total	Ramesh	Khilan	Kaushik	Chaitali
104000	20000	30000	25000	29000

System Variables

The system variables are predefined by the MySQL. These contains the data we need, to work with the database. Each MySQL system variable has a default value.

The SET command in MySQL can be used at the runtime to dynamically change the values of the system variables.

There are two variable scope modifiers available for the SHOW VARIABLES command. They are GLOBAL and SESSION.

• The GLOBAL variables are active throughout the lifecycle.

• The SESSION variables can be available only in the current session.

Following is the command to display all the system variables in MySQL −

SHOW [GLOBAL | SESSION] VARIABLES;

Example

In the following example, let us display the existing global system variables using the SHOW VARIABLES query −

SHOW VARIABLES LIKE ''%table%

The variables are displayed in the table format as follows −

Variable_name	Value
big_tables	OFF
default_table_encryption	OFF
innodb_file_per_table	ON
innodb_ft_aux_table
innodb_ft_server_stopword_table
innodb_ft_user_stopword_table
innodb_table_locks	ON
innodb_temp_tablespaces_dir	.#innodb_temp
innodb_undo_tablespaces	2
innodb_validate_tablespace_paths	ON
lower_case_table_names	1
max_heap_table_size	16777216
old_alter_table	OFF
performance_schema_max_table_handles	-1
performance_schema_max_table_instances	-1
performance_schema_max_table_lock_stat	-1
show_create_table_skip_secondary_engine	OFF
show_create_table_verbosity	OFF
table_definition_cache	2000
table_encryption_privilege_check	OFF
table_open_cache	4000
table_open_cache_instances	16
tablespace_definition_cache	256
temptable_max_mmap	1073741824
temptable_max_ram	1073741824
temptable_use_mmap	ON
tmp_table_size	99614720
updatable_views_with_limit	YES

Now, using the query below, we will fetch the current value of the MySQL “key_buffer_size” variable −

SELECT @@key_buffer_size;

Output

Following is the output of the above query −

@@key_buffer_size
8388608

Node.js is a JavaScript runtime environment that allows developers to run JavaScript code outside of a web browser, enabling server-side scripting.

When we talk about a Node.js MySQL connector, we are referring to a specific library that facilitates communication between a Node.js application and a MySQL database. This connector enables developers to interact with a MySQL database by providing methods and functionalities that simplify tasks like querying, updating, and managing data within the database using JavaScript code. Essentially, it acts as a bridge, allowing Node.js applications to seamlessly connect with and manipulate data stored in a MySQL database.

Installation “mysql” package

To use MySQL with Node.js, you can use the “mysql” package, which is a popular MySQL driver for Node.js. Here are the steps to install Node.js and the MySQL package −

Step 1: Install Node.js

Visit the official Node.js website (https://nodejs.org/) and download the latest version of Node.js for your operating system. Follow the installation instructions provided on the website.

Step 2: Create a Node.js Project

Create a new directory for your Node.js project and navigate to it using your terminal or command prompt.

mkdir mynodeproject
cd mynodeproject

Step 3: Initialize a Node.js Project

Run the following command to initialize a new Node.js project. This will create a ”package.json” file.

npm init -y

Step 4: Install the MySQL Package

Install the “mysql” package using the following command:

npm install mysql

Step 5: Create a JavaScript File

Create a JavaScript file (e.g., app.js) in your project directory.

Step 6: Run the Node.js Script

Run your Node.js script using the following command:

node app.js

Now, you have successfully installed the MySQL Node.js connector (mysql package) for your Node.js project.

NodeJS Functions to Access MySQL

In Node.js, the “mysql” package provides a set of functions to interact with MySQL databases. Here are some of the major functions you can use−

S.No	Function & Description
1	createConnection(config) Creates a new MySQL connection.
2	connect(callback) Establishes a connection to the MySQL server.
3	query(sql, values, callback) Executes a SQL query on the connected MySQL database. You can provide placeholders in the SQL query and pass values as an array to replace the placeholders.
4	execute(sql, values, callback) Similar to the query function, but specifically designed for executing non-select queries (e.g., INSERT, UPDATE, DELETE).
5	beginTransaction(callback) Starts a new transaction.
6	commit(callback) Commits the current transaction.
7	rollback(callback) Rolls back the current transaction.
8	end() Closes the MySQL connection.

Basic Example

Following are the steps to connect and communicate with a MySQL database using Node.js −

• Download and install Node.js
• Create a new directory, navigate to it, and run ”npm init -y”.
• Run ”npm install mysql”.
• Create a JavaScript file (e.g., app.js) and use the “mysql” package to connect to the MySQL database.
• Use the query or execute functions to perform SQL queries on the database.
• Implement error handling for database operations. Close the database connection when finished.
• Execute your Node.js script with node app.js.

The following example shows a generic syntax of NodeJS to call any MySQL query.

const mysql = require("mysql2");

// Create a connection to the MySQL database
const connection = mysql.createConnection({
  host: ''your-mysql-hostname'',
  user: ''your-mysql-username'',
  password: ''your-mysql-password'',
  database: ''your-mysql-database'',
});

// Connect to the database
connection.connect((err) => {
  if (err) {
    console.error(''Error connecting to MySQL:'', err);
    return;
  }
  console.log(''Connected to MySQL database'');

  // Perform MySQL operations here
  connection.query("SELECT * FROM your_table", (err, results) => {
    if (err) throw err;
    console.log(''Query result:'', results);
  });

  // Close the connection when done
  connection.end((err) => {
    if (err) console.error(''Error closing MySQL connection:'', err);
    else console.log(''Connection closed'');
  });
});

All downloads for MySQL are located at . Pick the version number of MySQL Community Server which is required along with the platform you will be running it on.

Installing MySQL on Windows

In this tutorial, we are installing the latest version of MySQL (8.0.34) on Windows 11. Follow the given steps to do so −

Step 1: Firstly, choose the MySQL version and operating system. Then, we download the desired MSI installer on your system by clicking the ”Download” button shown in the image below. This installer is suitable for both 32-bit and 64-bit systems.

Step 2: Then, we will be redirected to another file download page. Here, ignore the prompts asking to log in or sign up and directly start downloading by clicking on the link as shown in the image.

Step 3: Once the installer is downloaded, run it to start the MySQL installation.

Step 4: Now, we can see the installer community window, asking to choose a Setup type for our MySQL products. Choose Custom and click Next to decide what products we want to actually install.

Step 5: In the next step, select MySQL Server, MySQL Workbench, MySQL Shell (all latest versions) to be installed. We can also choose more products available as per necessity. Click Next.

Step 6: The installation process will now begin. However, path conflicts might arise if there exists a path directory with the same name. After the installation is done, click Next.

Step 7: In this step, we will be asked to set Type and Networking of MySQL. Unless there is any particular change we want to make, it is recommended to keep the settings as they are, and click Next.

Step 8: Then, we need to set the Authentication method to access MySQL root user. So, choose the strong password encryption method (as it is recommended) and click Next.

Step 9: Set a password for the root account. This password must always be used to log into the root account in every session. After setting password, click Next.

Step 10: In this step, MySQL Server Instance will be configured as a Windows Service. The default name will be set as “MySQL80”, which can be changed if needed. Click Next.

Step 11: Now, set the file permissions as required and click Next.

Step 12: As shown in the image below, the specified configuration steps will be applied on clicking Execute.

Once it is completed, click Finish. The next window will display the products on which the configuration is applied. Click Next to finish the installation.

Step 12: The installation is now complete. Uncheck the options asking to start MySQL Workbench and Shell after setup, so that they will not run after setup. Click Finish.

The MySQL Server is now installed in the Windows Operating System. We can now access it via the Command Prompt or the UI products we installed with it (Shell and Workbench).

Installing MySQL on Linux/UNIX

The recommended way to install MySQL on a Linux system is via RPM. MySQL AB makes the following RPMs available for download on its website −

• MySQL − The MySQL database server manages the databases and tables, controls user access and processes the SQL queries.

• MySQL-client − MySQL client programs, which make it possible to connect to and interact with the server.

• MySQL-devel − Libraries and header files that come in handy when compiling other programs that use MySQL.

• MySQL-shared − Shared libraries for the MySQL client.

• MySQL-bench − Benchmark and performance testing tools for the MySQL database server.

The MySQL RPMs listed here are all built on a SuSE Linux system, but they will usually work on other Linux variants with no difficulty.

Now, you will need to adhere to the steps given below, to proceed with the installation −

• Login to the system using the root user.

• Switch to the directory containing the RPMs.

• Install the MySQL database server by executing the following command. Remember to replace the filename in italics with the file name of your RPM.

[root@host]# rpm -i MySQL-5.0.9-0.i386.rpm

The above command takes care of installing the MySQL server, creating a user of MySQL, creating necessary configuration and starting the MySQL server automatically.

You can find all the MySQL related binaries in /usr/bin and /usr/sbin. All the tables and databases will be created in the /var/lib/mysql directory.

The following code box has an optional but recommended step to install the remaining RPMs in the same manner −

[root@host]# rpm -i MySQL-client-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-devel-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-shared-5.0.9-0.i386.rpm
[root@host]# rpm -i MySQL-bench-5.0.9-0.i386.rpm

To communicate with databases Java provides a library known as JDBC (Java Database Connectivity). JDBC provides a set of classes and methods specifically designed for database connectivity, enabling Java developers to perform tasks such as establishing connections, executing queries, and managing data in MySQL databases.

JDBC Installation

To use MySQL with Java, you need to use a JDBC (Java Database Connectivity) driver to connect your Java application to a MySQL database. Below are the general steps for installing and using the MySQL Connector/J, which is the official MySQL JDBC driver for Java −

Step 1: Download MySQL Connector/J

Visit the official MySQL Connector/J download page: MySQL Connector/J Downloads.

Step 2: Select the Appropriate Version

Choose the appropriate version based on your MySQL server version and Java version. Download the ZIP or TAR archive containing the JDBC driver.

Step 3: Extract the Archive

Extract the contents of the downloaded archive to a location on your computer.

Step 4: Add Connector/J to Your Java Project

In your Java project, add the Connector/J JAR file to your classpath. You can do this in your IDE or by manually copying the JAR file into your project.

Step 5: Connect to MySQL Database in Java

In your Java code, use the JDBC API to connect to the MySQL database.

Java Functions to Access MySQL

Following are the major functions involved in accessing MySQL from Java −

S.No	Function & Description
1	DriverManager.getConnection(String url, String user, String password) Establishes a connection to the database using the specified URL, username, and password.
2	createStatement() Creates a Statement object for executing SQL queries.
3	executeQuery(String sql) Executes a SQL SELECT query and returns a ResultSet object containing the result set.
4	executeUpdate(String sql) Executes a SQL INSERT, UPDATE, DELETE, or other non-query statement.
5	next() Moves the cursor to the next row in the result set. Returns true if there is a next row, false otherwise.
6	getInt(String columnLabel) Retrieves the value of the specified column in the current row of the result set.
7	prepareStatement(String sql) Creates a PreparedStatement object for executing parameterized SQL queries.
8	setXXX(int parameterIndex, XXX value) Sets the value of a specified parameter in the prepared statement.
9	executeQuery(), executeUpdate() Execute the prepared statement as a query or update.
10	setAutoCommit(boolean autoCommit) Enables or disables auto-commit mode.
11	commit() Commits the current transaction.
12	rollback() Rolls back the current transaction.

Basic Example

To connect and communicate with a MySQL database using Java, you can follow these steps −

• Load the JDBC driver specific to your database.
• Create a connection to the database using “DriverManager.getConnection()”.
• Create a “Statement” or “PreparedStatement” for executing SQL queries.
• Use “executeQuery()” for SELECT queries, or “executeUpdate()” for other statements.
• Iterate through the “ResultSet” to process the retrieved data.
• Close “ResultSet”, “Statement”, and “Connection” to release resources.
• Wrap database code in try-catch blocks to handle exceptions.
• Use transactions if performing multiple operations as a single unit.

The following example shows a generic syntax of a Java program to call any MySQL query −

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;

public class DatabaseInteractionExample {

    public static void main(String[] args) {
        try {
            // Load JDBC Driver
            Class.forName("com.mysql.cj.jdbc.Driver");

            // Connect to Database
            Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/your_database", "your_username", "your_password");

            // Execute Query
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery("Your SQL Query");

            // Process Results
            while (resultSet.next()) {
                // Process data
            }

            // Close Resources
            resultSet.close();
            statement.close();
            connection.close();

            // Handle Exceptions
        } catch (ClassNotFoundException | SQLException e) {
            e.printStackTrace();
        }
    }
}

MySQL Server is the program that mainly executes the SQL queries in the database system. Hence it becomes necessary to optimize the working of this server. The general MySQL administration usually includes concepts like:

• Starting and Stopping the Server

• User Security

• Database Maintenance

• Backup & Restore

Start MySQL Server

We need to first start the MySQL server on the device in order to use it. One way to do so, is by executing the following command on the command prompt (run as an administrator) −

mysqld

We can also start the server by going through the services provided by the Windows and follow the steps below −

• Open the ”Run” Window using the ”Windows+R” shortcut and run ”services.msc” through it.



• Then, select the “MySQL80” service click “start” to start the server.

Stop, Pause, Restart MySQL Server

Now, if you want to pause, stop or restart an already running MySQL server, then you can do it by opening the Windows Services and selecting the desired action −

To stop the MySQL Server, select the ”stop” option as shown in the image below −

To pause the MySQL Server, select the ”pause” option as shown in the image below −

We can also restart the MySQL server as needed, by selecting the ”restart” option as shown below −

Setting Up a MySQL User Account

For adding a new user to MySQL, you just need to add a new entry to the user table in the database mysql.

In the following example, we are creating a new user guest with the password guest123 on the ”localhost”. We are also granting all privileges required to executing SQL queries −

CREATE USER ''guest''@''localhost'' IDENTIFIED BY ''guest123

Now, execute the FLUSH PRIVILEGES statement. This tells the server to reload the grant tables. If you don”t use it, then you won”t be able to connect to MySQL using the new user account at least until the server is rebooted.

FLUSH PRIVILEGES;

Finally, you need to grant all privileges to this new user to execute SQL queries.

GRANT ALL PRIVILEGES ON * . * TO ''sample''@''localhost

You can also specify other privileges to a new user by setting the values of following columns in ”user” table to ”Y” using the UPDATE query.

• Select_priv
• Insert_priv
• Update_priv
• Delete_priv
• Create_priv
• Drop_priv
• Reload_priv
• Shutdown_priv
• Process_priv
• File_priv
• Grant_priv
• References_priv
• Index_priv
• Alter_priv

NOTE − MySQL does not terminate a command until you give a semi colon (;) at the end of the SQL command.

The /etc/my.cnf File Configuration

In most of the cases, you should not touch this file. By default, it will have the following entries −

[mysqld]
datadir = /var/lib/mysql
socket = /var/lib/mysql/mysql.sock

[mysql.server]
user = mysql
basedir = /var/lib

[safe_mysqld]
err-log = /var/log/mysqld.log
pid-file = /var/run/mysqld/mysqld.pid

Here, you can specify a different directory for the error log, otherwise you should not change any entry in this table.

Administrative MySQL Commands

Here is the list of the important MySQL commands, which you will use time to time to work with MySQL database −

• USE database_name − This will be used to select a database in the MySQL.

• SHOW DATABASES − Lists out the databases that are accessible by the MySQL DBMS.

• SHOW TABLES − Displays the list of the tables in the current database.

• SHOW COLUMNS FROM table_name: Shows the attributes, types of attributes, key information, whether NULL is permitted, defaults, and other information for a table.

• SHOW INDEX FROM table_name − Presents the details of all indexes on the table, including the PRIMARY KEY.

• SHOW TABLE STATUS LIKE table_nameG − Reports details of the MySQL DBMS performance and statistics.