Chapter 1 : Introduction to MySQL

Overview of MySQL

What is MySQL?

MySQL stands as the leading open-source relational database management system (RDBMS), fostered, distributed, and supported by Oracle Corporation. It's pivotal for managing structured data across various applications and systems. The MySQL website (http://www.mysql.com/) provides the latest information about MySQL software.

Relational Database System:

MySQL employs a relational structure, organizing data into distinct tables for efficient storage. It optimizes database structures into physical files for enhanced speed, offering a logical model comprising databases, tables, views, and more, providing a flexible programming environment.

Structured Query Language (SQL):

MySQL integrates SQL, the standard language for interacting with databases. It allows direct SQL inputs, embedding SQL within code, or using language-specific APIs to access and manipulate data stored in the database.

Open-Source Nature:

MySQL embodies an open-source ethos, allowing unrestricted usage and modifications. Anyone can freely download, study, and modify the MySQL software due to its GNU General Public License (GPL). A commercially licensed version is also available for embedding MySQL code in commercial applications.

Performance and Reliability:

Recognized for its speed, reliability, scalability, and ease of use, MySQL operates proficiently across diverse environments. From single desktop installations to clustered configurations, it adapts to available hardware resources and sustains high-performance levels.

Client/Server Architecture:

MySQL operates as a client/server system, comprising a multithreaded SQL server, client programs, libraries, administrative tools, and a range of application programming interfaces (APIs). Additionally, it offers an embedded library suitable for integration into applications.

MySQL HeatWave:

MySQL HeatWave introduces a fully managed database service, leveraging the HeatWave in-memory query accelerator. It integrates transactions, real-time analytics across data warehouses and lakes, and machine learning within a unified MySQL Database, minimizing complexities and costs associated with data duplication.

MySQL HeatWave is available across Oracle Cloud Infrastructure (OCI), AWS, and Azure. For more information, visit Oracle's MySQL page.

Main Features of MySQL

Internals and Portability:

Written in C and C++: MySQL is primarily developed using C and C++ languages, ensuring efficiency and performance.

Example: Understanding MySQL's core programming languages can help in optimizing queries and performance tuning.

Multi-Platform Support: MySQL is designed to operate on various platforms, ensuring compatibility across different systems.

Example: Installing and running MySQL on Windows, Linux, or macOS to showcase its cross-platform capabilities.

Scalability and Limits:

Support for Large Databases: MySQL accommodates extensive datasets, showcasing its capability to handle substantial records and tables.

Example: Creating and populating a database with millions of records to demonstrate MySQL's scalability.

Indexing: MySQL supports multiple indexes per table, aiding in faster data retrieval.

Example: Creating different indexes on a table and comparing query performance with and without indexes.

Connectivity:

Diverse Client Connections: MySQL allows clients to connect via various protocols such as TCP/IP, named pipes, and Unix domain sockets.

Example: Connecting MySQL clients using TCP/IP sockets and Unix domain sockets to demonstrate diverse connectivity options.

APIs and Interfaces: MySQL offers APIs and interfaces for languages like Java, Python, PHP, and others for seamless integration.

Example: Developing a simple application using MySQL Connector/J in Java to perform database operations.

Localization:

Language Support: MySQL supports multiple languages for error messages and different character sets.

Example: Configuring MySQL to display error messages in different languages and using various character sets.

Collation and Sorting: MySQL allows changing collation for sorting based on different character sets.

Example: Sorting and comparing data using different collations for specific character sets.

Clients and Tools:

Command-Line Utilities: MySQL provides various command-line tools for database administration and maintenance.

Example: Using utilities like mysqladmin or mysqldump to perform tasks such as backup, restore, and table maintenance.

Graphical Tools: MySQL Workbench offers a graphical interface for database design, modeling, and administration.

Example: Creating ER diagrams or performing database modeling using MySQL Workbench.

In a MySQL Lab Manual, these detailed features can be accompanied by step-by-step exercises, hands-on tasks, and practical examples to familiarize learners with MySQL's capabilities. Practical demonstrations and exercises provide a hands-on approach, reinforcing theoretical concepts and aiding in practical application and understanding.

Installation and Setup

MySQL is one of the most popular relational database management software that is widely used in today's industry. It provides multi-user access support with various storage engines. It is backed by Oracle Company. In this section, we are going to learn how we can download and install MySQL for beginners.

Prerequisites

The following requirements should be available in your system to work with MySQL:

MySQL Setup Software

Microsoft .NET Framework 4.5.2

Microsoft Visual C++ Redistributable for Visual Studio 2019

RAM 4 GB (6 GB recommended)

Download MySQL

Follow these steps:

Step 1: Go to the official website (https://dev.mysql.com/downloads/installer/) of MySQL and download the community server edition software. Here, you will see the option to choose the Operating System, such as Windows.

Step 2: Next, there are two options available to download the setup. Choose the version number for the MySQL community server, which you want. If you have good internet connectivity, then choose the mysql-installer-web-community. Otherwise, choose the other one.

Installing MySQL on Windows

Step 1: After downloading the setup, unzip it anywhere and double click the MSI installer .exe file. It will give the following screen:

Step 2: In the next wizard, choose the Setup Type. There are several types available, and you need to choose the appropriate option to install MySQL product and features. Here, we are going to select the Full option and click on the Next button.

This option will install the following things: MySQL Server, MySQL Shell, MySQL Router, MySQL Workbench, MySQL Connectors, documentation, samples and examples, and many more.

Step 3: Once we click on the Next button, it may give information about some features that may fail to install on your system due to a lack of requirements. We can resolve them by clicking on the Execute button that will install all requirements automatically or can skip them. Now, click on the Next button.

Step 4: In the next wizard, we will see a dialog box that asks for our confirmation of a few products not getting installed. Here, we have to click on the Yes button.

After clicking on the Yes button, we will see the list of the products which are going to be installed. So, if we need all products, click on the Execute button.

Step 5: Once we click on the Execute button, it will download and install all the products. After completing the installation, click on the Next button.

Step 6: In the next wizard, we need to configure the MySQL Server and Router. Here, I am not going to configure the Router because there is no need to use it with MySQL. We are going to show you how to configure the server only. Now, click on the Next button.

Step 7: As soon as you will click on the Next button, you can see the screen below. Here, we have to configure the MySQL Server. Now, choose the Standalone MySQL Server/Classic MySQL Replication option and click on Next. Here, you can also choose the InnoDB Cluster based on your needs.

Step 8: In the next screen, the system will ask you to choose the Config Type and other connectivity options. Here, we are going to select the Config Type as 'Development Machine' and Connectivity as TCP/IP, and Port Number is 3306, then click on Next.

Step 9: Now, select the Authentication Method and click on Next. Here, I am going to select the first option.

AD

Step 10: The next screen will ask you to mention the MySQL Root Password. After filling the password details, click on the Next button.

Step 11: The next screen will ask you to configure the Windows Service to start the server. Keep the default setup and click on the Next button.

Step 12: In the next wizard, the system will ask you to apply the Server Configuration. If you agree with this configuration, click on the Execute button.

Step 13: Once the...