MySQL 8 Cookbook

Karthik Appigatla, Kedar Mohaniraj Vaijanapukar

• Publisher: Packt Publishing
• Year: 2018
• ISBN:
  9781788398442
• Edition: 1
• Topic: Ciencia de la computación
• Subtopic: Bases de datos

Design and administer enterprise-grade MySQL 8 solutionsAbout This Book• Store, retrieve, and manipulate your data using the latest MySQL 8 features• Practical recipes on effective administration in MySQL, with a focus on security, performance tuning, troubleshooting, and more• Contains tips, tricks, and best practices for designing, developing, and administering your MySQL 8 database solution without any hassleWho This Book Is ForIf you are a MySQL developer or administrator looking for quick, handy solutions to solve the most common and not-so-common problems in MySQL, this book is for you. MySQL DBAs looking to get up-to-speed with the latest MySQL 8 development and administration features will also find this book very useful. Prior knowledge of Linux and RDBMS is desirable.What You Will Learn• Install and configure your MySQL 8 instance without any hassle• Get to grips with new features of MySQL 8 like CTE, Window functions and many more• Perform backup tasks, recover data and set up various replication topologies for your database• Maximize performance by using new features of MySQL 8 like descending indexes, controlling query optimizer and resource groups• Learn how to use general table space to suit the SaaS or multi-tenant applications• Analyze slow queries using performance schema, sys schema and third party tools• Manage and monitor your MySQL instance and implement efficient performance-tuning tasksIn DetailMySQL is one of the most popular and widely used relational databases in the World today. The recently released MySQL 8 version promises to be better and more efficient than ever before.This book contains everything you need to know to be the go-to person in your organization when it comes to MySQL. Starting with a quick installation and configuration of your MySQL instance, the book quickly jumps into the querying aspects of MySQL. It shows you the newest improvements in MySQL 8 and gives you hands-on experience in managing high-transaction and real-time datasets. If you've already worked with MySQL before and are looking to migrate your application to MySQL 8, this book will also show you how to do that. The book also contains recipes on efficient MySQL administration, with tips on effective user management, data recovery, security, database monitoring, performance tuning, troubleshooting, and more.With quick solutions to common and not-so-common problems you might encounter while working with MySQL 8, the book contains practical tips and tricks to give you the edge over others in designing, developing, and administering your database effectively.Style and approachThis book takes a recipe-based approach to tackling the pain points of SQL developers. It is a comprehensive book full of solutions to common problems faced by SQL administrators and developers alike.

Using MySQL

In this chapter, we will cover the following recipes:

• Connecting to MySQL using the command-line client
• Creating databases
• Creating tables
• Inserting, updating, and deleting rows
• Loading sample data
• Selecting data
• Sorting results
• Grouping results (aggregate functions)
• Creating users
• Granting and revoking access to users
• Selecting data into a file and table
• Loading data into a table
• Joining tables
• Stored procedures
• Functions
• Triggers
• Views
• Events
• Getting information about databases and tables

Introduction

We are going to learn a lot of things in the following recipes. Let's take a look at each one in detail.

Connecting to MySQL using the command-line client

So far, you have learned how to install MySQL 8.0 on various platforms. Along with the installation, you will get the command-line client utility called mysql, which we use to connect to any MySQL server.

Getting ready

First you need to know to which server you need to connect. If you have the MySQL server installed on one host and you are trying to connect to the server from a different host (usually called client), you should specify the hostname or IP address of the server and the mysql-client package should be installed on the client. In the previous chapter, you installed both MySQL server and client packages. If you are already on the server (through SSH), you can specify localhost, 127.0.0.1, or ::1.

Second, since you are connected to the server, the next thing you need to specify is to which port you want to connect on the server. By default, MySQL runs on port 3306. So, you should specify 3306.

Now you know where to connect. The next obvious thing is the username and password to log in to the server. You have not created any users yet, so use the root user to connect. While installing, you would have supplied a password, use that to connect. In case you changed it, use the new password.

How to do it...

Connecting to the MySQL client can be done with any of the following commands:

shell> mysql -h localhost -P 3306 -u <username> -p<password>

shell> mysql --host=localhost --port=3306 --user=root --password=<password>

shell> mysql --host localhost --port 3306 --user root --password=<password>

It is highly recommended not to give the password in the command line, instead you can leave the field blank; you will be prompted for a password:

shell> mysql --host=localhost --port=3306 --user=root --password 
Enter Password:

1. The -P argument (in uppercase) is passed for specifying the port.
2. The -p argument (in lowercase) is passed for specifying the password.
3. There is no space after the -p argument.
4. For the password, there is no space after =.

By default, the host is taken as localhost, the port is taken as 3306, and the user is taken as the current shell user.

1. To know the current user:

shell> whoami

2. To disconnect, press Ctrl + D or type exit:

mysql> ^DBye
shell> 

Or use:

mysql> exit;
Bye
shell>

3. After connecting to the mysql prompt, you can execute statements followed by the delimiter. The default delimiter is a semicolon (;):

mysql> SELECT 1;
+---+
| 1 |
+---+
| 1 |
+---+
1 row in set (0.00 sec)

2. To cancel a command, press Ctrl + C or type \c:

mysql> SELECT ^C
mysql> SELECT \c

Connecting to MySQL using the root user is not recommended. You can create users and restrict the user by granting appropriate privileges, which will be discussed in the Creating Users and Granting and revoking access to users sections. Till then, you can use the root user to connect to MySQL.

See also

After connecting, you might have noticed a warning:

Warning: Using a password on the command line interface can be insecure.

To learn about the secure ways of connecting, refer to Chapter 14, Security.

Once you are connected to the command-line prompt, you can execute the SQL statements, which can be terminated by ;, \g, or \G.
; or \g—output is displayed horizontally, \G—output is displayed vertically.

Creating databases

Well, you have installed MySQL 8.0 and connected to it. Now it is time to store some data in it, that's what the database is meant for, after all. In any relational database management system (RDBMS), data is stored in rows, which is the basic building block of the database. Rows contain columns in which we can store several set of values.

For example, if you want to store information about your customers in a database.

Here is the dataset:

customer id=1, first_name=Mike, last_name=Christensen country=USA
customer id=2, first_name=Andy, last_name=Hollands, country=Australia
customer id=3, first_name=Ravi, last_name=Vedantam, country=India
customer id=4, first_name= Rajiv, last_name=Perera, country=Sri Lanka

You should save them as rows: (1, 'Mike', 'Christensen', 'USA'), (2, 'Andy', 'Hollands', 'Australia'), (3, 'Ravi', 'Vedantam', 'India'), (4, 'Rajiv', 'Perera', 'Sri Lanka'). For this dataset, there are four rows described by three columns (id, first_name, last_name and country), which are stored in a table. The number of columns that a table can hold should be defined at the time of the creation of the table, which is the major limitation of RDBMS. However, we can alter the definition of the table any time, but the full table should be rebuilt while doing so. In some cases, the table will be unavailable while doing an alter. Altering a table will be discussed in detail in Chapter 9, Table Maintenance.

A database is a collection of many tables, and a database server can hold many of these databases. The flow is as follows:

Database Server —> Databases —> Tables (defined by columns) —> Rows

Databases and tables are referred to as database objects. Any operation, such as creating, modifying, or d...