28,99 €
Mehr erfahren.
- Herausgeber: Payload Media, Inc.
- Kategorie: Fachliteratur
- Sprache: Englisch
MySQL 9 Essentials is a concise guide to MySQL 9 database management, covering fundamental concepts, techniques, and best practices.
The book begins by installing and configuring MySQL on Windows, macOS, and Linux before outlining the fundamentals of relational database management systems.
Beyond the fundamentals, this book covers advanced MySQL features such as views, indexing for performance optimization, automation with triggers and events, and database modeling with MySQL Workbench.
In addition to covering the command-line tools provided with MySQL, several chapters introduce the phpMyAdmin and MySQL Workbench tools, which offer user-friendly graphical interfaces for database management.
The book explains each topic in detail and includes practical examples that provide hands-on experience. Each chapter also contains a quick-reference summary highlighting key points for easy review and access to an online knowledge test quiz to assess and reinforce your understanding.
In addition, this book features in-depth chapters, 28 online quizzes, and access to downloadable project code, ensuring you gain both theoretical knowledge and hands-on experience.
By the end of this book, you will have the confidence to build, manage, and optimize MySQL databases effectively, equipping you with the skills necessary to handle real-world database challenges.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 303
Veröffentlichungsjahr: 2025
Ähnliche
MySQL 9 Essentials
MySQL 9 Essentials
ISBN-13: 978-1-965764-10-7
© 2025 Neil Smyth / Payload Media, Inc. All Rights Reserved.
This book is provided for personal use only. Unauthorized use, reproduction and/or distribution strictly prohibited. All rights reserved.
The content of this book is provided for informational purposes only. Neither the publisher nor the author offers any warranties or representation, express or implied, with regard to the accuracy of information contained in this book, nor do they accept any liability for any loss or damage arising from any errors or omissions.
This book contains trademarked terms that are used solely for editorial purposes and to the benefit of the respective trademark owner. The terms used within this book are not intended as infringement of any trademarks.
Rev: 1.0
https://www.payloadbooks.com
Table of Contents
1. Start Here
1.1 About MySQL
1.2 Downloading the database snapshots
1.3 Importing the database snapshots
1.4 Feedback
1.5 Errata
1.6 Knowledge tests
2. The Basics of Databases
2.1 Database vs. DBMS
2.2 Client-server databases
2.3 What is a database server?
2.4 Understanding database tables
2.5 Introducing database schema
2.6 Columns and data types
2.7 Database rows
2.8 Primary keys
2.9 What is SQL?
2.10 Knowledge test
2.11 Reference points
3. Installing and Launching MySQL on Windows
3.1 Downloading MySQL for Windows
3.2 Running the software installer
3.3 Configuring and starting MySQL Server
3.3.1 Data directory
3.3.2 Type and network settings
3.3.3 Accounts and roles
3.3.4 Windows service
3.3.5 Server file permissions
3.3.6 Sample databases
3.3.7 Applying the configuration
3.4 Setting the PATH environment variable
3.5 Securing MySQL Server on Windows
3.6 Launching the mysql client
3.7 Knowledge test
3.8 Reference points
4. Installing and Launching MySQL on macOS
4.1 Downloading MySQL for macOS
4.2 Running the package installer
4.3 Using the Preferences Pane
4.3.1 The Instances pane
4.3.2 The Configuration pane
4.4 Adding MySQL to your PATH
4.5 Starting MySQL Server using launch control
4.6 Starting MySQL Server using mysql.server
4.7 Securing MySQL Server on macOS
4.8 Launching the mysql client
4.9 Knowledge test
4.10 Reference points
5. Installing and Launching MySQL on Linux
5.1 Downloading MySQL for Linux
5.2 Installing MySQL on RPM-based distributions
5.3 Securing MySQL Server on RPM-based systems
5.4 Installing MySQL on Debian and Ubuntu
5.5 Securing MySQL Server on Debian and Ubuntu
5.6 Launching the mysql client
5.7 Knowledge test
5.8 Reference points
6. The MySQL Client
6.1 The mysql command-line tool
6.2 Using the mysql client
6.3 Typing and editing SQL statements
6.4 Exiting from the mysql client
6.5 Knowledge test
6.6 Reference points
7. MySQL Users, Privileges, and Security
7.1 MySQL security
7.2 Getting information about users
7.3 Adding a MySQL user account
7.4 Deleting a MySQL user
7.5 Renaming a MySQL user
7.6 Changing the password for a MySQL user
7.7 Granting privileges
7.8 Privileges and grantable levels
7.9 Privilege examples
7.10 Removing remote root access
7.11 Managing password verification
7.12 Knowledge test
7.13 Reference points
8. Creating Databases and Tables
8.1 Creating a new MySQL database
8.2 Enabling table encryption
8.3 Creating tables with SQL
8.4 Understanding NULL and NOT NULL values
8.5 Primary keys
8.6 Using AUTO_INCREMENT
8.7 Defining default values during table creation
8.8 Displaying table schema
8.9 Deleting databases and tables
8.10 MySQL storage engine types
8.11 Knowledge test
8.12 Reference points
9. Inserting Data into Database Tables
9.1 Creating the sample table
9.2 The basics of data insertion
9.3 Inserting a row
9.4 Adding multiple rows to a table
9.5 Inserting results from a SELECT statement
9.6 Reducing the INSERT performance load
9.7 Knowledge test
9.8 Reference points
10. Updating and Managing MySQL Tables
10.1 Opening the sample database
10.2 Altering a database table
10.3 Adding table columns
10.4 Moving table columns
10.5 Renaming tables
10.6 Renaming table columns
10.7 Deleting table columns
10.8 Changing the data type of a column
10.9 Deleting tables
10.10 Duplicating tables
10.11 Knowledge test
10.12 Reference points
11. Updating and Deleting Table Data
11.1 Opening the sample database
11.2 Updating database data
11.3 Ignoring update errors
11.4 Understanding auto-commit
11.5 Transactions and rollbacks
11.6 Deleting table rows
11.7 Knowledge test
11.8 Reference points
12. Retrieving Data from MySQL Databases
12.1 Opening the sample database
12.2 Retrieving a single column
12.3 Using SELECT to retrieve multiple columns
12.4 Restricting the number of results
12.5 Eliminating duplicate values
12.6 Sorting result sets
12.7 Sorting on multiple columns
12.8 Sorting data in descending order
12.9 Expressions and aggregate functions
12.10 Knowledge test
12.11 Reference points
13. Filtering Result Sets with the WHERE Clause
13.1 Opening the sample database
13.2 The basics of the WHERE clause
13.3 Comparison Operators
13.4 Checking for NULL values
13.5 Searching within value ranges
13.6 Knowledge test
13.7 Reference points
14. Filtering Results with Logical Operators
14.1 Opening the sample database
14.2 Filtering results with the OR operator
14.3 Filtering results with the AND operator
14.4 Combining AND and OR operators
14.5 Understanding operator precedence
14.6 Specifying condition ranges using the IN operator
14.7 Using the NOT Operator
14.8 Using subqueries with WHERE IN clauses
14.9 Knowledge test
14.10 Reference points
15. Wildcard and Regular Expression Matching
15.1 What are wildcards?
15.2 Opening the sample database
15.3 Single character wildcards
15.4 Multiple character wildcards
15.5 What are regular expressions?
15.6 Regular expression character matching
15.7 Matching with a group of characters
15.8 Matching character ranges
15.9 Handling special characters
15.10 Whitespace metacharacters
15.11 Matching by character type
15.12 Repetition metacharacters
15.13 Matching by text position
15.14 Replacing text with regular expressions
15.15 Knowledge test
15.16 Reference points
16. Understanding Joins and Unions
16.1 How joins work
16.2 Opening the sample database
16.3 Creating the supplier table
16.4 Adding the foreign key to the product table
16.5 Adding key values to supplier_id
16.6 Performing a cross join
16.7 Performing an inner join
16.8 Performing left joins
16.9 Performing right joins
16.10 Understanding WHERE in join statements
16.11 Working with unions
16.12 Full outer joins in MySQL
16.13 Knowledge test
16.14 Reference points
17. An Introduction to MySQL Views
17.1 An introduction to MySQL views
17.2 Opening the sample database
17.3 Creating a basic view
17.4 Joins and views
17.5 Updating view-based tables
17.6 Updating join-based views
17.7 Altering existing views
17.8 Deleting and replacing views
17.9 Knowledge test
17.10 Reference points
18. Understanding MySQL Indexes
18.1 Understanding MySQL indexes
18.2 Primary index
18.3 Unique index
18.4 Regular index
18.5 Full-text indexes
18.6 Composite index
18.7 Spatial index
18.8 Getting index details
18.9 Deleting indexes
18.10 Limitations of indexes
18.11 Knowledge test
18.12 Reference points
19. MySQL Stored Routines
19.1 An overview of stored routines
19.2 The benefits of stored routines
19.3 Creating stored procedures
19.4 Opening the sample database
19.5 Working with MySQL variables
19.6 Stored procedure examples
19.7 Creating stored functions
19.7.1 RETURNS
19.7.2 RETURN
19.7.3 DETERMINISTIC
19.7.4 NON DETERMINISTIC
19.7.5 NO SQL
19.7.6 READS SQL DATA
19.7.7 MODIFIES SQL DATA
19.7.8 CONTAINS SQL
19.8 Stored function examples
19.9 Using stored functions as subqueries
19.10 Introducing Common Table Expressions
19.11 Viewing procedures and functions
19.12 Deleting stored procedures and functions
19.13 Knowledge test
19.14 Reference points
20. Working with Control Flow
20.1 Opening the sample database
20.2 Looping in MySQL
20.2.1 The WHILE loop
20.2.2 The REPEAT loop
20.3 The LOOP construct
20.4 Conditional Statements in MySQL
20.4.1 The IF statement
20.4.2 The CASE statement
20.5 Knowledge test
20.6 Reference points
21. Error Handling in MySQL
21.1 Overview of MySQL error handling
21.2 Deciphering SQLSTATE codes
21.3 Opening the sample database
21.4 Declaring a handler
21.5 Error handling with SQLEXCEPTION
21.6 Error handling with SQLWARNING and NOT FOUND
21.7 Handling specific errors with SQLSTATE
21.8 Custom error handling with SIGNAL
21.9 Knowledge test
21.10 Reference points
22. Automation with MySQL Triggers
22.1 An overview of MySQL triggers
22.2 The syntax for creating triggers
22.3 Accessing trigger event data
22.4 Triggers in action
22.4.1 An AFTER INSERT example
22.4.2 A BEFORE INSERT example
22.4.3 A BEFORE DELETE example
22.5 Displaying trigger information
22.6 Deleting triggers
22.7 Limitations of triggers
22.8 Knowledge test
22.9 Reference points
23. Scheduling Tasks with MySQL Events
23.1 An introduction to MySQL events
23.2 Enabling the MySQL event scheduler
23.3 Creating Events
23.4 Opening the sample database
23.5 Adding the event_tests table
23.6 Creating a one-time event
23.7 Creating recurring events
23.8 Viewing event details
23.9 Preserving events
23.10 Altering event properties
23.11 Deleting events
23.12 Knowledge test
23.13 Reference points
24. An Introduction to mysqladmin
24.1 Setting up mysqladmin
24.2 Basic mysqladmin syntax
24.3 Checking server status
24.4 Shutting down the server
24.5 Resetting user passwords
24.6 Flushing privileges and logs
24.7 Creating and dropping databases
24.8 Viewing server variables
24.9 Connecting to remote servers
24.10 Knowledge test
24.11 Reference points
25. Configuring MySQL using the my.cnf File
25.1 Introducing the my.cnf File
25.2 The my.cnf File Structure
25.3 Key my.cnf parameters
25.3.1 General server settings
25.3.2 Performance tuning
25.3.3 Storage engine configuration
25.3.4 Logging and debugging
25.3.5 Security settings
25.3.6 Applying my.cnf file changes
25.4 Knowledge test
25.5 Reference points
26. Installing MySQL Workbench
26.1 Downloading MySQL Workbench
26.2 Installation on Windows
26.3 Installation on macOS
26.4 Installation on RPM-based Linux distributions
26.5 Installation on Ubuntu
26.6 Knowledge test
26.7 Reference points
27. MySQL Workbench Administration
27.1 Creating a server connection
27.2 The MySQL Workbench user interface.
27.3 Managing servers with MySQL Workbench
27.3.1 Server status
27.3.2 Client connections
27.3.3 Users and privileges
27.3.4 Status and system variables
27.4 Importing and exporting data
27.5 Performance monitoring
27.6 Instance management
27.7 Knowledge test
27.8 Reference points
28. MySQL Workbench Queries and Models
28.1 Introducing the SQL Editor
28.2 Creating a database model
28.3 Adding a user to the model
28.4 Generating the database creation script
28.5 Generating EER diagrams
28.6 Knowledge test
28.7 Reference points
29. Database Administration with phpMyAdmin
29.1 Introducing phpMyAdmin
29.2 Setting up a web server on Red Hat-based Linux distributions
29.3 Testing the web server
29.4 Installing phpMyAdmin
29.5 Securing phpMyAdmin access
29.6 Opening phpMyAdmin
29.7 Knowledge test
29.8 Reference points
30. A Guided Tour of phpMyAdmin
30.1 The phpMyAdmin home screen
30.2 Using the console
30.3 Managing databases
30.4 Running SQL queries
30.5 Monitoring server status
30.6 User accounts
30.7 Importing and exporting data
30.8 Working with database diagrams
30.9 Knowledge test
30.10 Reference points
Index
1. Start Here
Databases are the foundation of applications like websites, mobile apps, and large-scale enterprise systems. MySQL is one of the most widely deployed and highly regarded database management systems in use today. MySQL 9 Essentials provides a concise guide to MySQL, covering fundamental concepts, techniques, and best practices.
The book begins by installing and configuring MySQL on Windows, macOS, and Linux before outlining the fundamentals of relational database management systems.
MySQL 9 Essentials also covers key MySQL features such as views, stored routines, indexing for performance optimization, automation with triggers and events, and database modeling with MySQL Workbench.
In addition to covering the command-line tools provided with MySQL, several chapters introduce the phpMyAdmin and MySQL Workbench tools, which offer user-friendly graphical interfaces for database management.
By following this book, you will learn how to:
•Install and configure MySQL on Windows, macOS, and Linux.
•Use MySQL client tools and graphical interfaces like MySQL Workbench and phpMyAdmin.
•Design efficient database schemas and relationships.
•Manage databases using the Structured Query Language (SQL).
•Optimize database performance using indexing and query optimization techniques.
•Automate repetitive database tasks using triggers and scheduled events.
•Secure MySQL databases by managing user privileges and access controls.
This book explains each topic in detail and includes practical examples that provide hands-on experience. The chapters also contain quick-reference summaries highlighting key points for easy review and access to online knowledge quizzes to assess and reinforce your understanding.
By the end of this book, you will have the confidence to build and manage MySQL databases.
1.1 About MySQL
Before the arrival of MySQL, implementing a database was typically a complex and expensive task involving the purchase, installation, and maintenance of a proprietary database management system from a vendor such as Oracle or IBM. In contrast, MySQL provides a free, open-source database management system that is easy to install, implement, and maintain. In addition, MySQL is fast, extremely reliable, and widely deployed by many companies and organizations worldwide.
It is not an exaggeration to say that MySQL has brought the power of a fully featured, scalable relational database management system into the reach of anyone with a computer and the desire to build a data-driven application or website. MySQL was originally developed by MySQL AB, a company founded in Sweden in 1995, and remained independent until it was acquired by Sun Microsystems in 2008. Oracle purchased Sun Microsystems in 2008 and found itself owning MySQL. Oracle provides a free community and a subscription-based enterprise edition of MySQL. Though the core elements of the two editions are identical, the enterprise edition includes additional scaling, performance, security, backup features, and 24x7 support. To address the broadest possible audience, this book is based on MySQL 9 Community Edition.
The name MySQL comes from a combination of “My” and “SQL.” In this context, “My” is a proper noun rather than a possessive adjective and is named after My, the daughter of Monty Widenius, one of the original developers of MySQL. SQL stands for Structured Query Language, the language used for managing databases.
1.2 Downloading the database snapshots
Many chapters in this book assume that you have completed the steps from previous chapters. If you would rather not read the chapters in sequence, you can import database snapshots at the beginning of each chapter using the snapshot files available for download at the following link:
https://www.payloadbooks.com/product/mysql9/
1.3 Importing the database snapshots
To import a snapshot, open a terminal or command prompt, navigate to the directory that contains the sample files, and run the following commands:
mysql -u root -p -e "CREATE DATABASE sampledb;"
mysql -u root -p sampledb < snapshot_file_name.sql
1.4 Feedback
We want you to be satisfied with your purchase of this book. Therefore, if you find any errors in the book or have any comments, questions, or concerns, please contact us at [email protected].
1.5 Errata
While we make every effort to ensure the accuracy of the content of this book, inevitably, a book covering a subject area of this size and complexity may include some errors and oversights. Any known issues with the book will be outlined, together with solutions, at the following URL:
https://www.payloadbooks.com/mysql9_errata
If you find an error not listed in the errata, email our technical support team at [email protected].
1.6 Knowledge tests
Look for this section at the end of most chapters and use the link or scan the QR code to take a knowledge quiz to test and reinforce your understanding of the covered topic. Use the following link to review the full list of tests available for this book:
https://www.answertopia.com/ce9o
2. The Basics of Databases
The chances are that if you have ever logged into a website or purchased an item on the internet you have interacted with a database in some way. Anything that involves the retrieval or storage of information on a computer system is most likely to involve a database. In fact, databases are the core of almost every application that relies on data of some form to complete a task. In this chapter, we will introduce the basic concepts of databases.
2.1 Database vs. DBMS
The first step in learning MySQL is understanding the difference between a database and a database management system (DBMS). The term database refers to the entity that stores the actual data (such as ID numbers, names, and addresses, for example) in a structured way. A database management system (DBMS), on the other hand, refers to the software used to store, access, and manipulate the data stored in the database. All interactions with the database are performed via the DBMS.
Modern databases and database management systems are not restricted to storing just text. Today, databases store such items as images, videos, and software objects.
2.2 Client-server databases
MySQL is classified as a client-server database management system (DBMS). This type of DBMS consists of two main components. The server, which usually resides on the same physical computer as the database files, is responsible for all interactions with the database. The second component is the client, which sends database requests to the server. The server processes these requests and returns the results to the client.
There are several key advantages to using a client-server architecture for a database management system (DBMS). First, the client does not need to run on the same computer as the server. Instead, clients can send requests over a network or internet connection to a server located on a remote host. This setup makes the database accessible to a large number of clients. In large-scale enterprise environments, it also allows for fault tolerance, high performance, and load balancing to be implemented effectively.
Second, separating the client from the server allows a wider range of client types to be used to access the database. Typical clients include MySQL tools, desktop and mobile apps, web-based applications, web servers, and even other database servers:
Figure 2-1
2.3 What is a database server?
The term “database server” can be somewhat misleading as it can refer to different concepts. One definition relates to the computer system hosting a Database Management System (DBMS) and other applications and services. However, in this book, we will specifically refer to the software component of a DBMS responsible for executing database operations on behalf of clients and returning the results. In the context of MySQL, this role is fulfilled by MySQL Server.
A database server can contain multiple databases, each containing one or more tables.
2.4 Understanding database tables
Database tables provide the most basic level of data structure in a database. Each database can contain multiple tables, each designed to hold information of a specific type. For example, a database may contain a customer table containing the name, address, and telephone number for all the customers of a particular business. The same database may also include a product table that stores the product descriptions with associated product codes and pricing for the items the business sells.
Each table in a database is assigned a name that must be unique to that particular database. A table name, once assigned to a table in one database, may only be re-used within the context of a different database:
Figure 2-2
2.5 Introducing database schema
Database schemas define the characteristics of the data stored in a database table. For example, the schema for a customer database table might define that the customer name is a string of no more than 20 characters in length and that the customer phone number is a numerical data field of a specific format.
Schemas are also used to define the structure of entire databases and the relationship between the various tables in each database.
2.6 Columns and data types
At this stage, it is helpful to begin viewing a database table similar to a spreadsheet, where data is stored in rows and columns.
Each column represents a data field in the corresponding table. For example, a table’s name, address, and telephone data fields are all columns.
Each column, in turn, is defined to contain a specific data type, which dictates the type of data it can store. Therefore, a column designed to store numbers would be defined as a numerical data type.
2.7 Database rows
Each new record saved to a table is stored in a row, which consists of the columns of data associated with the saved record.
Once again, consider the spreadsheet analogy described earlier. Each entry in a customer table is equivalent to a row in a spreadsheet, and each column contains the data for each customer (name, address, telephone number, etc.). The individual columns within a specific row are referred to as fields.
When a new customer is added to the table, a new row is created, and the data for that customer is stored in the corresponding columns of the new row.
Rows are also sometimes referred to as records, and these terms can generally be used interchangeably:
Figure 2-3
2.8 Primary keys
Each database table must contain one or more columns that uniquely identify each row. This is known in database terminology as the primary key. For example, a table may use a bank account number column as the primary key. Alternatively, a customer table may assign unique identifiers to each customer as the primary key.
Primary keys allow the database management system to uniquely identify a specific row in a table. Without a primary key, retrieving or deleting a specific row in a table would be impossible because there is no certainty that the correct row has been selected. For example, suppose a table existed where the customer’s last name had been defined as the primary key. Imagine the problem if more than one customer called “Smith” was recorded in the database. Without some guaranteed way to uniquely identify a specific row, it would be impossible to ensure the correct data was being accessed at any given time.
Primary keys can comprise a single column or multiple columns in a table. To qualify as a single column primary key, no two rows can contain matching primary key values. When using multiple columns to construct a primary key, individual column values do not need to be unique, but all the columns combined must be unique.
Finally, while primary keys are not mandatory in database tables, their use is strongly recommended.
2.9 What is SQL?
As discussed previously, a database management system (DBMS) provides the means to access the data stored in a database. One key method for achieving this is via a language called Structured Query Language (SQL), which is abbreviated to SQL and pronounced “sequel”.
SQL is a straightforward and easy-to-use language developed at IBM in the 1970s specifically to enable the reading and writing of database data. Because SQL contains a small set of keywords, it can be learned quickly. In addition, SQL syntax is identical in most DBMS implementations, so having learned SQL for one system, your skills will likely transfer to other database management systems.
Throughout this book, particular attention will be paid to explaining the key SQL commands so that you will be proficient in using SQL to read, write, and manage database data.
2.10 Knowledge test
Click the link below or scan the QR code to test your knowledge and understanding of database architecture:
https://www.answertopia.com/vwh2
2.11 Reference points
The main points covered in this chapter are as follows:
•Databases vs. DBMS
■A database stores structured data (e.g., names, addresses).
■A DBMS (Database Management System) is software to access, store, and manipulate the database (e.g., MySQL).
•Database Client-Server Architecture
■Databases operate on a server, and clients (apps, web servers) access them locally or remotely via a network.
■A database server may host multiple databases, each with one or more tables.
•Database Tables
■Tables organize data into rows (records) and columns (fields).
■Each column has a specific data type (e.g., numerical, text).
•Database Schema
■Defines the structure of a database and its tables, including data types and relationships.
•Primary Keys
■Unique identifier for rows in a table (e.g., customer ID).
■Can be a single column or a combination of columns.
■Strongly recommended to ensure reliable row identification.
■Proper table structure and primary keys ensure efficient data management and retrieval.
•SQL (Structured Query Language)
■The primary language for interacting with databases.
■Used for querying, updating, and managing database data.
■SQL is simple, widely adopted, and transferable across DBMS platforms.
3. Installing and Launching MySQL on Windows
This chapter will explain how to install and run MySQL on Microsoft Windows. This will include instructions for starting and stopping the MySQL server. Additionally, we will cover how to use the MySQL client and explore various configuration options available for MySQL on Windows.
3.1 Downloading MySQL for Windows
MySQL for Windows is available as a Microsoft Software Installer (.MSI) file. To download the latest Community Edition of MySQL 9, open a web browser and visit the following page:
https://dev.mysql.com/downloads/mysql/
On the download page, use the menu at the top to select the latest version of MySQL 9 (marked as A in Figure 3-1). Additionally, set the operating system menu (B) to Microsoft Windows.
Figure 3-1
To download the MSI Installer, click the corresponding Download button (C). When prompted, you can either sign in with your Oracle web account or choose “No thanks, just start my download” to proceed without signing in.
3.2 Running the software installer
Using Windows Explorer, navigate to where you downloaded the MySQL MSI file and double-click it to begin the installation. Once the installer has loaded, the screen shown in Figure 3-2 will appear:
Figure 3-2
Click the Next button to review and accept the licensing terms before moving on to the setup type screen shown below:
Figure 3-3
For the purposes of this book, the “typical” option will provide everything you need to learn MySQL, so select this option to start the installation process.
After completing the MySQL installation, the screen displayed in Figure 3-4 will appear. The next step is to configure and start the MySQL Server using the MySQL Configurator tool. Ensure the “Run MySQL Configurator” option is checked, then click the Finish button:
Figure 3-4
3.3 Configuring and starting MySQL Server
The MySQL Configurator tool configures the installation, initializes the database, and starts the server. It can be launched from the Windows start menu anytime to modify configuration settings. When you open the MySQL Configurator for the first time, it will display a welcome screen, as shown in Figure 3-5:
Figure 3-5
3.3.1 Data directory
Click Next to proceed to the Data Directory screen (Figure 3-6). The default location for the database files is as follows, where <version> is replaced by the version of MySQL that was installed:
C:\ProgramData\MySQL Server <version>\
For example, if you installed MySQL 9.2, the data directory will default to the following path:
C:\ProgramData\MySQL Server 9.2\
If you prefer to use a different directory for the database data, click the button labeled ‘...’ to select another location:
Figure 3-6
3.3.2 Type and network settings
The next screen contains settings related to the configuration type and network connectivity:
Figure 3-7
The Config Type menu allows you to configure the memory allocated to the MySQL Server during operation. The available types are as follows:
•Development Computer - This setting is intended for situations where MySQL is run on a personal computer during development, and the system is also used for other tasks not related to MySQL. To prevent any negative impact on the performance of other applications on the system, this option configures MySQL to use a minimal amount of memory.
•Server Computer - Choose this option for production servers that will be running additional services alongside MySQL Server, such as web and application servers. This option allocates more memory to MySQL compared to the Development type while maintaining the performance of the other services.
•Dedicated Computer - This configuration is intended for systems that run only MySQL Server, allowing the server to utilize all available system memory.
•Manual - This option prevents the MySQL Configurator from altering the memory allocation. As a result, MySQL will revert to the settings specified in the my.ini file located in the MySQL data directory, which was configured on the previous screen of the configurator.
The network settings manage access to MySQL Server from external sources. For most development installations, these settings typically do not require changes.
3.3.3 Accounts and roles
Use the Accounts and Roles screen to set a password for the MySQL root user account. The root account functions as a “superuser,” providing complete access and control over the databases managed by MySQL. For this reason, it is crucial to select a sufficiently complex password. You can also add additional user accounts in this section, though we will discuss this topic further later in the book:
Figure 3-8
3.3.4 Windows service
The Windows Services screen determines how MySQL Server operates on the system. MySQL can run as a managed Windows service or be launched manually by executing the mysqld.exe file. However, launching the server manually involves additional steps and requires passing several command-line configuration options to mysqld.exe. Using the Windows service option is recommended unless you have a specific requirement that necessitates manual execution.
When the Windows service option is enabled, you can configure MySQL Server to start automatically when the system boots up. By default, the MySQL Windows service runs under the system’s NetworkService user account. However, you can choose to use a different user account. Keep in mind that this account must have the appropriate permissions and access rights on the Windows system. Setting this up requires advanced knowledge of Windows system administration:
Figure 3-9
3.3.5 Server file permissions
The Server File Permissions screen lets you configure access controls for files in the data directory. Generally, it is advisable to grant access to both the current user (likely yourself) and system administrators:
Figure 3-10
3.3.6 Sample databases
MySQL for Windows includes a collection of sample databases that can be installed for educational purposes. Although these databases will not be utilized in this book, you are welcome to install them on your system.
3.3.7 Applying the configuration
Before using MySQL, the final step is to apply the configuration settings and start the server. The screen in Figure 3-11 outlines the steps to be performed and will update as each step is completed. To begin the configuration, click the “Execute” button and wait for the process to finish:
Figure 3-11
If the configuration fails, select the Log tab and review the log information for error details.
3.4 Setting the PATH environment variable
In addition to the Configurator, MySQL includes several command-line tools for managing and interacting with the MySQL server. To avoid typing a long pathname each time you want to execute a command, it helps to add the path to these tools to your PATH environment variable. Assuming that you installed MySQL into the default location, the following path will need to be added to your PATH environment variable (where <version> is replaced by the version of MySQL that was installed):
C:\Program Files\MySQL\MySQL Server <version>\bin
To ensure this is included in the path whenever a Command Prompt or PowerShell is opened, right-click on the Windows Start menu, select Settings from the resulting menu, and enter “Edit the system environment variables” into the “Find a setting” text field. In the System Properties dialog (Figure 3-12), click the Environment Variables... button:
Figure 3-12
In the Environment Variables dialog, locate the Path variable in the User variables list, select it, and click the Edit… button:
Figure 3-13
Using the New button in the edit dialog (Figure 3-14), add a new entry to the path. For example, assuming MySQL was installed into C:\Program Files\MySQL\MySQL Server 9.1\bin\, the following entry would need to be added:
C:\Program Files\MySQL\MySQL Server 9.2\bin\
Figure 3-14
Once the new path entry has been added, click OK in each dialog box and close the system properties control panel.
Open a Command Prompt window by pressing Windows + R on the keyboard and entering cmd into the Run dialog. Within the Command Prompt window, enter:
echo %Path%
The returned path variable value should include the MySQL path. Verify that thepath is correct by attempting to run the mysql client as follows:
mysql --version
If the Path is correctly configured, you should see output similar to the following:
mysql Ver 9.2.0 for Win64 on x86_64 (MySQL Community Server - GPL)
3.5 Securing MySQL Server on Windows
Once the installation is complete, an additional step is recommended to increase MySQL’s security. MySQL provides a tool called mysql_secure_installation, which allows us to eliminate potential vulnerabilities. When executed, this command will prompt for the current root password:
C:\Demo> mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
The next option is to install the password validation component. This component ensures that all user passwords are strong and secure. It is recommended to install this component if you are working in a production environment; however, it is not essential if you are simply learning MySQL on a local system.
VALIDATE PASSWORD COMPONENT can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD component?
Press y|Y for Yes, any other key for No:
The tool will then ask if you wish to change the current root password:
Using existing password for root.
Change the password for root ? ((Press y|Y for Yes, any other key for No) :
The mysql_secure_installation command will prompt you to remove the anonymous user, disallow remote root access, and delete the test database. Generally, it is recommended to agree to these suggestions. However, if you are accessing your Linux server remotely, such as through a secure shell (SSH) connection, you will need to keep remote root access until you have created at least one additional user account:
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y
- Dropping test database...
Success.
- Removing privileges on test database...
Success.
Finally, mysql_secure_installation will request permission to reload the privilege tables to apply the changes. Enter ‘y’ to complete the process of securing the MySQL installation:
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Success.
All done!
3.6 Launching the mysql client
Now that the MySQL server is running, we need a way to interact with it. The simplest option is to use the MySQL client (mysql). This tool connects to the server and provides a command-line environment for executing SQL statements on the stored data.
When starting the MySQL client, you must provide a username and password to access the server. Since this is a new installation, the only configured user (unless you added more in the MySQL Configurator) is the root user, and you will use the password that you specified during the configuration process. To start the MySQL client, run the following command in a Command Prompt window and enter the root password when prompted:
mysql -u root -p
Once mysql has connected, the following output will appear:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 9.2.0 MySQL Community Server - GPL
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql>
At the mysql> prompt, enter the following SQL statement:
mysql> SHOW DATABASES;
+--------------------+
| Database |
+--------------------+
| information_schema |
| mysql |
| performance_schema |
| sys |
+--------------------+
4 rows in set (0.02 sec)
To exit from mysql, enter exit or quit at the prompt:
mysql> quit
Bye
3.7 Knowledge test
Click the link below or scan the QR code to test that you know how to install MySQL on Windows:
https://www.answertopia.com/hr7u
3.8 Reference points
The main points covered in this chapter are as follows:
•Downloading MySQL for Windows
■Available as a Microsoft Software Installer (.MSI) file.
■Download from https://dev.mysql.com/downloads/mysql/.
■Choose the latest version and Windows OS, then download the MSI Installer.
•Running the Software Installer
■Navigate to the downloaded MSI file and double-click to start installation.
■Accept licensing terms and choose the “Typical” setup for basic functionality.
■At the end of the installation, enable “Run MySQL Configurator” and finish.
•Configuring and Starting MySQL Server
■MySQL Configurator sets up the database, initializes it, and starts the server.
■Data Directory: Default location is C:\ProgramData\MySQL Server <version>\, but can be changed.
•Configuration Types
■
