39,59 €
Mehr erfahren.
- Herausgeber: Packt Publishing
- Kategorie: Fachliteratur
- Sprache: Englisch
MySQL 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.
Das E-Book können Sie in Legimi-Apps oder einer beliebigen App lesen, die das folgende Format unterstützen:
Seitenzahl: 436
Veröffentlichungsjahr: 2018
Ähnliche
MySQL 8 Cookbook
Copyright © 2018 Packt Publishing
All rights reserved. No part of this book may be reproduced, stored in a retrieval system, or transmitted in any form or by any means, without the prior written permission of the publisher, except in the case of brief quotations embedded in critical articles or reviews.
Every effort has been made in the preparation of this book to ensure the accuracy of the information presented. However, the information contained in this book is sold without warranty, either express or implied. Neither the author, nor Packt Publishing or its dealers and distributors, will be held liable for any damages caused or alleged to have been caused directly or indirectly by this book.
Packt Publishing has endeavored to provide trademark information about all of the companies and products mentioned in this book by the appropriate use of capitals. However, Packt Publishing cannot guarantee the accuracy of this information.
Commissioning Editor: Amey VarangaokarAcquisition Editor: Viraj MadhavContent Development Editors: Aishwarya PandereTechnical Editor: Prasad RameshCopy Editor: Vikrant PhadkayProject Coordinator: Nidhi JoshiProofreader: Safis EditingIndexer: Pratik ShirodkarGraphics: Tania DuttaProduction Coordinator: Shraddha Falebhai
First published: January 2018 Production reference: 1230118
Published by Packt Publishing Ltd. Livery Place 35 Livery Street Birmingham B3 2PB, UK.
ISBN 978-1-78839-580-9
www.packtpub.com
Mapt is an online digital library that gives you full access to over 5,000 books and videos, as well as industry leading tools to help you plan your personal development and advance your career. For more information, please visit our website.
Why subscribe?
Spend less time learning and more time coding with practical eBooks and Videos from over 4,000 industry professionals
Improve your learning with Skill Plans built especially for you
Get a free eBook or video every month
Mapt is fully searchable
Copy and paste, print, and bookmark content
PacktPub.com
Did you know that Packt offers eBook versions of every book published, with PDF and ePub files available? You can upgrade to the eBook version at www.PacktPub.com and as a print book customer, you are entitled to a discount on the eBook copy. Get in touch with us at [email protected] for more details.
At www.PacktPub.com, you can also read a collection of free technical articles, sign up for a range of free newsletters, and receive exclusive discounts and offers on Packt books and eBooks.
Contributors
About the author
Karthik Appigatla is a highly reputed database architect and is very famous for performance tuning. He has been consulted by many companies all over the world for designing, performance tuning, building database infrastructure, and training. In his decade of experience, he has worked for companies such as Yahoo, Pythian, and Percona. Currently, he is working for LinkedIn, where he has innovated a new way of analyzing queries. He gave a talk about this at SRECon, Dublin in 2017.
About the reviewers
Marco Ippolito is an Italian software engineer working as Director of Software Development for Imagining IT. Marco completed his postgraduate in Software Engineering in Oxford and has worked for large corporations such as Intel, HP, Google, Dell, and Oracle (in the acquired MySQL team), as well as for start-ups such as @platformsh and for large production users of MySQL such as @bookingcom (Twitter). He can be reached at [email protected] and has experience working in teams speaking Italian, English, Spanish, Brazilian Portuguese, German, and French, remotely or onsite.
Kedar Mohaniraj Vaijanapurkar is a MySQL database consultant with over a decade of experience, ranging from programming to database administration. He aims to spread happiness with the MySQL database systems he works on. Apart from working with MySQL and related open source technologies, he also explores cloud, automation, and NoSQL. He is a 5-year Pythian laureate living with his awesome family in the cultured city of Vadodara, India. You can reach him at [email protected].
Packt is searching for authors like you
If you're interested in becoming an author for Packt, please visit authors.packtpub.com and apply today. We have worked with thousands of developers and tech professionals, just like you, to help them share their insight with the global tech community. You can make a general application, apply for a specific hot topic that we are recruiting an author for, or submit your own idea.
Table of Contents
Preface
Who this book is for
What this book covers
To get the most out of this book
Conventions used
Sections
Getting ready
How to do it…
How it works…
There's more…
See also
Get in touch
Reviews
MySQL 8 - Installing and Upgrading
Introduction
Installing MySQL using YUM/APT
How to do it...
Using YUM repositories
Using APT repositories
Installing MySQL 8.0 using RPM or DEB files
How to do it...
Using the RPM bundle
Using the APT bundle
Installing MySQL on Linux using Generic Binaries
How to do it...
There's more...
Starting or Stopping MySQL 8
How to do it...
Starting the MySQL 8.0 server
Stopping the MySQL 8.0 server
Checking the status of the MySQL 8.0 server
Uninstalling MySQL 8
How to do it...
On YUM-based systems
On APT-based systems
Uninstalling Binaries
Managing the MySQL Server with systemd
How to do it...
Downgrading from MySQL 8.0
How to do it...
In-place Downgrades
Using YUM repositories
Using APT Repositories
Using the RPM or APT bundle
Using Generic Binaries
Logical Downgrades
Using YUM Repositories
Using APT Repositories
Using RPM or APT bundles
Using Generic Binaries
Upgrading to MySQL 8.0
Getting ready
How to do it...
In-place upgrades
YUM-based systems
APT-based systems
Using RPM or APT bundles
Using Generic Binaries
Logical Upgrades
Installing MySQL utilities
How to do it...
On YUM-based systems
On APT-based systems
Using MySQL
Introduction
Connecting to MySQL using the command-line client
Getting ready
How to do it...
See also
Creating databases
How to do it...
See also
Creating tables
How to do it...
Cloning table structure
See also
Inserting, updating, and deleting rows
How to do it...
Inserting
Updating
Deleting
REPLACE, INSERT, ON DUPLICATE KEY UPDATE
Truncating tables
Loading sample data
How to do it...
Selecting data
How to do it...
Selecting columns
Count
Filter based on condition
Operators
Simple pattern matching
Regular expressions
Limiting results
Using the table alias
Sorting results
How to do it...
Grouping results (aggregate functions)
How to do it...
COUNT
SUM
AVERAGE
DISTINCT
Filtering using HAVING
See also
Creating users
How to do it...
See also
Granting and revoking access to users
How to do it...
Granting privileges
Checking grants
Revoking grants
Modifying the mysql.user table
Setting password expiry for users
Locking users
Creating roles for users
Selecting data into a file and table
How to do it...
Saving as a file
Saving as a table
Loading data into a table
How to do it...
Joining tables
How to do it...
Identifying Duplicates using SELF JOIN
Using SUB queries
Finding mismatched rows between tables
Stored procedures
How to do it...
There's more...
See also
Functions
How to do it...
Inbuilt functions
See also
Triggers
How to do it...
See also
Views
How to do it...
Events
How to do it...
Access control
See also
Getting information about databases and tables
How to do it...
TABLES
COLUMNS
FILES
INNODB_SYS_TABLESPACES
INNODB_TABLESTATS
PROCESSLIST
See also
Using MySQL (Advanced)
Introduction
Using JSON
How to do it...
Insert JSON
Retrieve JSON
JSON functions
Pretty view
Searching
Modifying
Removing
Other functions
See also
Common table expressions (CTE)
How to do it...
Non-recursive CTE
Recursive CTE
Generated columns
How to do it...
Window functions
How to do it...
Row number
Partition results
Named windows
First, last, and nth values
Configuring MySQL
Introduction
Using config file
How to do it...
Using global and session variables
How to do it...
Using parameters with startup script
How to do it...
Configuring the parameters
How to do it...
data directory
innodb_buffer_pool_size
innodb_buffer_pool_instances
innodb_log_file_size
Changing the data directory
How to do it...
Transactions
Introduction
Performing transactions
How to do it...
Autocommit
Using savepoints
How to do it...
Isolation levels
How to do it...
Read uncommitted
Read committed
Repeatable read
Serializable
Locking
How to do it...
Locking queue
Binary Logging
Introduction
Using binary logging
How to do it...
Enabling binary logs
Disabling binary logs for a session
Move to the next log
Expire binary logs
Binary log format
How to do it...
See also
Extracting statements from a binary log
Getting ready
How to do it...
Observations
Extracting based on time and position
Extracting based on the database
Extracting a row event display
Rewriting a database name
Disabling a binary log for recovery
Displaying events in a binary log file
Ignoring databases to write to a binary log
How to do it...
Example 1
Example 2
Relocating binary logs
How to do it...
Backups
Introduction
Taking backups using mysqldump
How to do it...
Full backup of all databases
Point-in-time recovery
Dumping master binary coordinates
Specific databases and tables
Ignore tables
Specific rows
Backup from a remote server
Backup to rebuild another server with a different schema
Only schema and no data
Only data and no schema
Backup for merging data with other server
REPLACE with new data
IGNORE data
Taking backups using mysqlpump
How to do it...
Parallel processing
Exclude/include database objects using regex
Backup users
Compressed backups
Faster reload
Taking backups using mydumper
How to do it...
Installation
Full backup
Consistent backup
Backup of a single table
Backup of specific databases using regex
Taking backup of a big table using mydumper
Non-blocking backup
Compressed backups
Backing up only data
Taking backups using flat files
How to do it...
Taking backups using XtraBackup
How to do it...
Installation
On CentOS/Red Hat/Fedora
On Debian/Ubuntu
Locking instances for backup
How to do it...
Binary log backup
How to do it...
Restoring Data
Introduction
Recovering from mysqldump and mysqlpump
How to do it...
There's more...
Recovering from mydumper using myloader
How to do it...
Recovering full database
Recover a single database
Recovering a single table
Recovering from flat file backup
How to do it...
Performing point-in-time recovery
How to do it...
mysqldump or mysqlpump
mydumper
Replication
Introduction
Setting up replication
How to do it...
Setting up master-master replication
How to do it...
Setting up multi-source replication
How to do it...
Setting up replication filters
How to do it...
Replicate a database only
Replicate specific tables
Ignore a database
Ignore specific tables
See also
Switching slave from master-slave to chain replication
How to do it...
Switching the slave from chain replication to master-slave
How to do it...
Setting up delayed replication
How to do it...
Setting up GTID replication
How to do it...
Setting up semi-synchronous replication
How to do it...
Table Maintenance
Introduction
Installing Percona Toolkit
How to do it...
On Debian/Ubuntu
On CentOS/Red Hat/Fedora
Altering tables
How to do it...
Moving tables across databases
How to do it...
Altering tables using an online schema change tool
How it works...
How to do it...
Archiving tables
How to do it...
Purging data
Archiving data
Copying data
See also
Cloning tables
How to do it...
Partitioning tables
How to do it...
RANGE partitioning
Removing partitioning
RANGE COLUMNS partitioning
LIST and LIST COLUMNS partitioning
HASH and LINEAR HASH partitioning
KEY and LINEAR KEY partitioning
Subpartitioning
Partition pruning and selection
How to do it...
Partition pruning
Partition selection
Partition management
How to do it...
ADD partitions
Reorganizing partitions
DROP partitions
TRUNCATE partitions
Managing HASH and KEY partitions
Other operations
Partition information
How to do it...
Using SHOW CREATE TABLE
Using SHOW TABLE STATUS
Using EXPLAIN
Querying the INFORMATION_SCHEMA.PARTITIONS table
Efficiently managing time to live and soft delete rows
How it works...
How to do it...
Managing Tablespace
Introduction
Changing the number or size of InnoDB redo log files
How to do it...
Resizing the InnoDB system tablespace
How to do it...
Increasing the InnoDB system tablespace
Shrinking the InnoDB system tablespace
Creating file-per-table tablespaces outside the data directory
How to do it...
Copying file-per-table tablespaces to another instance
How to do it...
Copy full table
Copying individual partitions of a table
See also
Managing UNDO tablespace
How to do it...
Managing general tablespace
How to do it...
Create a general tablespace
Adding tables to a general tablespace
Moving non-partitioned tables between tablespaces
Managing partitioned tables in a general tablespace
Dropping general tablespace
Compressing InnoDB tables
How to do it...
Enabling Compression for file_per_table Tables
Disabling Compression for file_per_table Tables
Enabling Compression for General Tablespace
Managing Logs
Introduction
Managing the error log
How to do it...
Configuring the error log
Rotating the error log
Using the system log for logging
Error logging in JSON format
Managing the general query log and slow query log
How to do it...
General query log
Slow query log
Selecting query log output destinations
Managing the binary logs
How to do it...
Performance Tuning
Introduction
The explain plan
How to do it...
Using EXPLAIN
Using EXPLAIN JSON
Using EXPLAIN for connection
Benchmarking queries and the server
How to do it...
Adding indexes
Primary key (clustered index) and secondary indexes
How to do it...
Add index
UNIQUE index
Prefix index
Drop index
Index on generated columns
Invisible index
How to do it...
Descending index
How to do it...
Analyzing slow queries using pt-query-digest
How to do it...
Slow query log
General query log
Process list
Binary log
TCP dump
See also
Optimizing datatypes
How to do it...
Removing duplicate and redundant indexes
How to do it...
pt-duplicate-key-checker
mysqlindexcheck
Checking index usage
How to do it...
Controlling the query optimizer
How to do it...
optimizer_search_depth
How to know that the query is spending time in evaluating plans?
optimizer_switch
Optimizer hints
Adjusting the optimizer cost model
Using index hints
How to do it...
Indexing for JSON using generated columns
How to do it...
Using resource groups
How to do it...
Alter and drop resource group
Using performance_schema
How to do it...
Enable/disable performance_schema
Enable/disable consumers and instruments
performance_schema tables
Using the sys schema
How to do it...
Statement by type (INSERT and SELECT) from each host
Statement by type from each user
Redundant indexes
Unused indexes
Statements executed from each host
Table statistics
Table statistics with buffer
Statement analysis
Security
Introduction
Securing installation
How to do it...
The FILE privilege
Restricting networks and users
How to do it...
Password-less authentication using mysql_config_editor
How to do it...
Resetting the root password
How to do it...
Using init-file
Using --skip-grant-tables
Setting up encrypted connections using X509
How to do it...
Setting up SSL replication
How to do it...
Preface
MySQL is one of the most popular and widely used relational databases in the world today. With the recently released MySQL 8, it promises to be better and more efficient than ever, giving you high-performance querying results and easy configuration as an administrator.
Who this book is for
This book is for a wide range of readers. MySQL database administrators and developers who have worked on earlier versions of MySQL will learn about the features of MySQL 8 and how they can leverage them. For readers who worked on other RDBMSes such as Oracle, MSSQL, PostgreSQL, and Db2 this book will be a quick start guide to MySQL 8. For beginners, this book serves as a handbook; they can refer to the recipes and find quick solutions to their problems.
Most importantly, this book makes you production-ready. After reading this book, you will be confident in handling busy database servers with large datasets.
In my 10 years of experience with MySQL, I have witnessed small mistakes leading to major outages. In this book, many scenarios where a mistake can be made are covered and put under a warning label.
The topics are introduced in such a way that a beginner need not go back and forth to understand the concepts. A reference link to the MySQL documentation or any other source is provided for each topic, and the reader can refer to the link for more details.
Since this book is written to suit beginners as well, there may be a few recipes on topics you already know; feel free to skip them.
What this book covers
Practice makes a man perfect. But to practice, you need some knowledge and training. This book helps you with that. Most day-to-day and practical scenarios are covered in this book.
Chapter 1, MySQL 8 - Installing and Upgrading, describes how to install MySQL 8 on different flavors of Linux, upgrade to MySQL 8 from previous stable releases, and also downgrade from MySQL 8.
Chapter 2, Using MySQL, takes you through the basic uses of MySQL, such as creating databases and tables; inserting, updating, deleting, and selecting data in various ways; saving to different destinations; sorting and grouping results; joining tables; managing users; other database elements such as triggers, stored procedures, functions, and events; and getting metadata information.
Chapter 3, Using MySQL (Advanced), covers the latest additions to MySQL 8, such as the JSON datatype, common table expressions, and window functions.
Chapter 4, Configuring MySQL, shows you how to configure MySQL and basic configuration parameters.
Chapter 5, Transactions, explains the four isolation levels of RDBMS and how to use MySQL for transactions.
Chapter 6, Binary Logging, demonstrates how to enable binary logging, various formats of binary logs, and how to retrieve data from binary logs.
Chapter 7, Backups, covers various types of backups, the pros and cons of each method, and which one to choose based on your requirements.
Chapter 8, Restoring Data, covers how to recover data from varies backups.
Chapter 9, Replication, explains how to set up various replication topologies. The recipes on switching a slave from master-slave to chain replication and switching a slave from chain replication to master-slave is something that will interest the readers.
Chapter 10, Table Maintenance, covers cloning tables. Managing big tables is something that this chapter will make you a maestro of. Installation and usage of third-party tools is also covered in this chapter.
Chapter 11, Managing Tablespace, deals with recipes that will teach the readers how to resize, create, copy, and manage tablespaces.
Chapter 12, Managing Logs, takes readers through error, general query, slow query, and binary logs.
Chapter 13, Performance Tuning, explains query and schema tuning in detail. There are ample recipes in the chapter that will cover this.
Chapter 14, Security, focuses on the aspects of security. Recipes on securing installation, restricting networks and users, setting and resetting of passwords, and much more in covered are detail.
To get the most out of this book
Basic knowledge of any Linux system makes it easy for you to understand this book.
Conventions used
There are a number of text conventions used throughout this book.
CodeInText: Indicates code words in text, database table names, folder names, filenames, file extensions, pathnames, dummy URLs, user input, and Twitter handles. Here is an example: "MySQL has a dependency on the libaio library."
When we wish to draw your attention to a particular part of a command line statement, the relevant lines or items are set in bold:
shell> sudo yum repolist all | grep mysql8
mysql80-community/x86_64 MySQL 8.0 Community Server enabled: 16
mysql80-community-source MySQL 8.0 Community Server disabled
Any command-line input or output is written as follows:
mysql> ALTER TABLE table_name REMOVE PARTITIONING;
Bold: Indicates a new term, an important word, or words that you see onscreen. For example, words in menus or dialog boxes appear in the text like this. Here is an example: "Select the Development Releases tab for getting MySQL 8.0 and the choose the OS and version."
Sections
In this book, you will find several headings that appear frequently (Getting ready, How to do it..., How it works..., There's more..., and See also).
To give clear instructions on how to complete a recipe, use these sections as follows:
Getting ready
This section tells you what to expect in the recipe and describes how to set up any software or any preliminary settings required for the recipe.
How to do it…
This section contains the steps required to follow the recipe.
How it works…
This section usually consists of a detailed explanation of what happened in the previous section.
There's more…
This section consists of additional information about the recipe in order to make you more knowledgeable about the recipe.
See also
This section provides helpful links to other useful information for the recipe.
Get in touch
Feedback from our readers is always welcome.
General feedback: Email [email protected] and mention the book title in the subject of your message. If you have questions about any aspect of this book, please email us at [email protected].
Errata: Although we have taken every care to ensure the accuracy of our content, mistakes do happen. If you have found a mistake in this book, we would be grateful if you would report this to us. Please visit www.packtpub.com/submit-errata, selecting your book, clicking on the Errata Submission Form link, and entering the details.
Piracy: If you come across any illegal copies of our works in any form on the internet, we would be grateful if you would provide us with the location address or website name. Please contact us at [email protected] with a link to the material.
If you are interested in becoming an author: If there is a topic that you have expertise in and you are interested in either writing or contributing to a book, please visit authors.packtpub.com.
Reviews
Please leave a review. Once you have read and used this book, why not leave a review on the site that you purchased it from? Potential readers can then see and use your unbiased opinion to make purchase decisions, we at Packt can understand what you think about our products, and our authors can see your feedback on their book. Thank you!
For more information about Packt, please visit packtpub.com.
MySQL 8 - Installing and Upgrading
In this chapter, we will cover the following recipes:
Installing MySQL using YUM/APT
Installing MySQL 8.0 using RPM or DEB files
Installing MySQL on Linux using Generic Binaries
Starting or Stopping MySQL 8
Uninstalling MySQL 8
Managing MySQL Server with systemd
Downgrading from MySQL 8.0
Upgrading to MySQL 8.0
Installing MySQL utilities
Introduction
In this chapter, you will learn about the installing, upgrading, and downgrading steps of MySQL 8. There are five different ways to install or upgrade; the three most widely-used installation methods are covered in this chapter:
Software repositories (YUM or APT)
RPM or DEB files
Generic Binaries
Docker (not covered)
Source code compilation (not covered)
If you have already installed MySQL and want to upgrade, go through the upgrade steps in the Upgrade to MySQL 8 section. If your installation is corrupt, go through the uninstallation steps also in the Upgrade to MySQL 8 section.
Before installation, make a note of OS and CPU architecture. The convention followed is as follows:
MySQL Linux RPM package distribution identifiers
Distribution value
Intended use
el6, el7
Red Hat Enterprise Linux, Oracle Linux, CentOS 6 or 7
fc23, fc24, fc25
Fedora 23, 24, or 25
sles12
SUSE Linux Enterprise Server 12
MySQL Linux RPM package CPU identifiers
CPU value
Intended processor type or family
i386, i586, i686
Pentium processor or better, 32-bit
x86_64
64-bit x86 processor
ia64
Itanium (IA-64) processor
MySQL Debian and Ubuntu 7 and 8 installation packages CPU identifiers
CPU value
Intended processor type or family
i386
Pentium processor or better, 32-bit
amd64
64-bit x86 processor
MySQL Debian 6 Installation package CPU identifiers
CPU value
Intended processor type or family
i686
Pentium processor or better, 32-bit
x86_64
64-bit x86 processor
Installing MySQL using YUM/APT
The most common and easiest way of installation is through software repositories where you add official Oracle MySQL repositories to your list and install MySQL through package management software.
There are mainly two types of repository software:
YUM (Centos, Red Hat, Fedora and Oracle Linux)
APT (Debian, Ubuntu)
How to do it...
Let's look at steps for installing MySQL 8 in the following ways:
Using YUM repositories
Find the Red Hat or CentOS version:
shell> cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)
Add the MySQL Yum repository to your system's repository list. This is a one-time operation that can be performed by installing an RPM provided by MySQL. You can download the MySQL YUM Repository from
http://dev.mysql.com/downloads/repo/yum/
and choose the file depending on your OS. Install the downloaded release package with the following command, replacing the name with the platform- and version-specific package name of the downloaded RPM package:
shell> sudo yum localinstall -y mysql57-community-release-el7-11.noarch.rpmLoaded plugins: fastestmirrorExamining mysql57-community-release-el7-11.noarch.rpm: mysql57-community-release-el7-11.noarchMarking mysql57-community-release-el7-11.noarch.rpm to be installedResolving Dependencies--> Running transaction check---> Package mysql57-community-release.noarch 0:el7-11 will be installed--> Finished Dependency Resolution~ Verifying : mysql57-community-release-el7-11.noarch 1/1 Installed: mysql57-community-release.noarch 0:el7-11Complete!
Or you can copy the link location and install directly using RPM (you can skip the next step after installing):
shell> sudo rpm -Uvh "https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm"Retrieving https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpmPreparing... ################################# [100%]Updating / installing... 1:mysql57-community-release-el7-11 ################################# [100%]
Verify the installation:
shell> yum repolist enabled | grep 'mysql.*-community.*'mysql-connectors-community/x86_64 MySQL Connectors Community 42mysql-tools-community/x86_64 MySQL Tools Community 53mysql57-community/x86_64 MySQL 5.7 Community Server 227
Set the release series. At the time of writing this book, MySQL 8 is not a
general availability
(
GA
) release. So MySQL 5.7 will be selected as the default release series. To install MySQL 8, you have to set the release series to 8:
shell> sudo yum repolist all | grep mysqlmysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community disabledmysql-cluster-7.5-community-source MySQL Cluster 7.5 Community disabledmysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community disabledmysql-cluster-7.6-community-source MySQL Cluster 7.6 Community disabled
mysql-connectors-community/x86_64 MySQL Connectors Community enabled: 42
mysql-connectors-community-source MySQL Connectors Community disabled
mysql-tools-community/x86_64 MySQL Tools Community enabled: 53
mysql-tools-community-source MySQL Tools Community - Sou disabledmysql-tools-preview/x86_64 MySQL Tools Preview disabledmysql-tools-preview-source MySQL Tools Preview - Sourc disabledmysql55-community/x86_64 MySQL 5.5 Community Server disabledmysql55-community-source MySQL 5.5 Community Server disabledmysql56-community/x86_64 MySQL 5.6 Community Server disabledmysql56-community-source MySQL 5.6 Community Server disabled
mysql57-community/x86_64 MySQL 5.7 Community Server enabled: 227
mysql57-community-source MySQL 5.7 Community Server disabled
mysql80-community/x86_64 MySQL 8.0 Community Server disabled
mysql80-community-source MySQL 8.0 Community Server disabled
Disable
mysql57-community
and enable
mysql80-community
:
shell> sudo yum install yum-utils.noarch -yshell> sudo yum-config-manager --disable mysql57-communityshell> sudo yum-config-manager --enable mysql80-community
Verify that
mysql80-community
is enabled:
shell> sudo yum repolist all | grep mysql8
mysql80-community/x86_64 MySQL 8.0 Community Server enabled: 16
mysql80-community-source MySQL 8.0 Community Server disabled
Install MySQL 8:
shell> sudo yum install -y mysql-community-serverLoaded plugins: fastestmirrormysql-connectors-community | 2.5 kB 00:00:00 mysql-tools-community | 2.5 kB 00:00:00 mysql80-community | 2.5 kB 00:00:00 Loading mirror speeds from cached hostfile * base: mirror.web-ster.com * epel: mirrors.cat.pdx.edu * extras: mirrors.oit.uci.edu * updates: repos.lax.quadranet.comResolving Dependencies~Transaction test succeededRunning transaction Installing : mysql-community-common-8.0.3-0.1.rc.el7.x86_64 1/4 Installing : mysql-community-libs-8.0.3-0.1.rc.el7.x86_64 2/4 Installing : mysql-community-client-8.0.3-0.1.rc.el7.x86_64 3/4 Installing : mysql-community-server-8.0.3-0.1.rc.el7.x86_64 4/4 Verifying : mysql-community-libs-8.0.3-0.1.rc.el7.x86_64 1/4 Verifying : mysql-community-common-8.0.3-0.1.rc.el7.x86_64 2/4 Verifying : mysql-community-client-8.0.3-0.1.rc.el7.x86_64 3/4 Verifying : mysql-community-server-8.0.3-0.1.rc.el7.x86_64 4/4 Installed: mysql-community-server.x86_64 0:8.0.3-0.1.rc.el7Dependency Installed: mysql-community-client.x86_64 0:8.0.3-0.1.rc.el7 mysql-community-common.x86_64 0:8.0.3-0.1.rc.el7 mysql-community-libs.x86_64 0:8.0.3-0.1.rc.el7 Complete!
You can check the installed packages using the following:
shell> rpm -qa | grep -i 'mysql.*8.*'perl-DBD-MySQL-4.023-5.el7.x86_64mysql-community-libs-8.0.3-0.1.rc.el7.x86_64mysql-community-common-8.0.3-0.1.rc.el7.x86_64mysql-community-client-8.0.3-0.1.rc.el7.x86_64mysql-community-server-8.0.3-0.1.rc.el7.x86_64
Using APT repositories
Add the MySQL APT repository to your system's repository list. This is a one-time operation that can be performed by installing a
.deb
file provided by MySQL You can download the MySQL APT repository from
http://dev.mysql.com/downloads/repo/apt/
.
Or you can copy the link location and use
wget
to download directly on to the server. You might need to install
wget
(
sudo apt-get install wget
):
shell> wget "https://repo.mysql.com//mysql-apt-config_0.8.9-1_all.deb"
Install the downloaded release package with the following command, replacing the name with platform- and version-specific package name of the downloaded APT package:
shell> sudo dpkg -i mysql-apt-config_0.8.9-1_all.deb (Reading database ... 131133 files and directories currently installed.)Preparing to unpack mysql-apt-config_0.8.9-1_all.deb ...Unpacking mysql-apt-config (0.8.9-1) over (0.8.9-1) ...Setting up mysql-apt-config (0.8.9-1) ...Warning: apt-key should not be used in scripts (called from postinst maintainerscript of the package mysql-apt-config)OK
During the installation of the package, you will be asked to choose the versions of the MySQL server and other components. Press
Enter
for selecting and the Up and Down keys for navigating. Select
MySQL Server and Cluster
(Currently selected: mysql-5.7). Select
mysql-8.0
preview (At the time of writing, MySQL 8.0 is not GA). You might get a warning such as
MySQL 8.0-RC Note that MySQL 8.0 is currently an RC
. It should only be installed to preview upcoming features of MySQL, and is not recommended for use in production environments
.
(
RC
is short for
release candidate
).
If you want to change the release version, execute the following:
shell> sudo dpkg-reconfigure mysql-apt-config
Update package information from the MySQL APT repository with the following command (this step is mandatory):
shell> sudo apt-get update
Install MySQL. During installation, you'll need to provide a password for the root user for your MySQL installation. Remember the password; if you forget it, you'll have to reset the root password (refer to the
Resetting root password
section). This installs the package for the MySQL server, as well as the packages for the client and for the database common files:
shell> sudo apt-get install -y mysql-community-server~Processing triggers for ureadahead (0.100.0-19) ...Setting up mysql-common (8.0.3-rc-1ubuntu14.04) ...update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto modeSetting up mysql-community-client-core (8.0.3-rc-1ubuntu14.04) ...Setting up mysql-community-server-core (8.0.3-rc-1ubuntu14.04) ...~
Verify packages.
ii
indicates that the package is installed:
shell> dpkg -l | grep -i mysqlii mysql-apt-config 0.8.9-1 all Auto configuration for MySQL APT Repo.ii mysql-client 8.0.3-rc-1ubuntu14.04 amd64 MySQL Client meta package depending on latest versionii mysql-common 8.0.3-rc-1ubuntu14.04 amd64 MySQL Commonii mysql-community-client 8.0.3-rc-1ubuntu14.04 amd64 MySQL Clientii mysql-community-client-core 8.0.3-rc-1ubuntu14.04 amd64 MySQL Client Core Binariesii mysql-community-server 8.0.3-rc-1ubuntu14.04 amd64 MySQL Serverii mysql-community-server-core 8.0.3-rc-1ubuntu14.04 amd64 MySQL Server Core Binaires
Installing MySQL 8.0 using RPM or DEB files
Installing MySQL using repositories requires access to public internet. As a security measure, most of the production machines are not connected to the internet. In that case, you can download the RPM or DEB files on the system administration and copy them to the production machine.
There are mainly two types of installation files:
RPM (CentOS, Red Hat, Fedora, and Oracle Linux)
DEB (Debian, Ubuntu)
There are multiple packages that you need to install. Here is a list and short description of each one:
mysql-community-server
: Database server and related tools.
mysql-community-client
: MySQL client applications and tools.
mysql-community-common
: Common files for server and client libraries.
mysql-community-devel
: Development header files and libraries for MySQL database client applications, such as the Perl MySQL module.
mysql-community-libs
: The shared libraries (
libmysqlclient.so*
) that certain languages and applications need to dynamically load and use MySQL.
mysql-community-libs-compat
: The shared libraries for older releases. Install this package if you have applications installed that are dynamically linked against older versions of MySQL but you want to upgrade to the current version without breaking the library dependencies.
How to do it...
Let's look at how to do it using the following types of bundles:
Using the RPM bundle
Download the MySQL RPM tar bundle from the
MySQL Downloads
page,
http://dev.mysql.com/downloads/mysql/
, choosing your OS and CPU architecture. At the time of writing, MySQL 8.0 is not GA. If it is still in the development series, select the
Development Releases
tab for getting MySQL 8.0 and the choose the OS and version:
shell> wget 'https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.3-0.1.rc.el7.x86_64.rpm-bundle.tar'~Saving to: ‘mysql-8.0.3-0.1.rc.el7.x86_64.rpm-bundle.tar’~
Untar the package:
shell> tar xfv mysql-8.0.3-0.1.rc.el7.x86_64.rpm-bundle.tar
Install MySQL:
shell> sudo rpm -i mysql-community-{server-8,client,common,libs}*
RPM cannot solve the dependency issues and the installation process might run issues. If you are facing such issues, use the
yum
command listed here (you should have access to dependent packages):
shell> sudo yum install mysql-community-{server-8,client,common,libs}* -y
Verify the installation:
shell> rpm -qa | grep -i mysql-communitymysql-community-common-8.0.3-0.1.rc.el7.x86_64mysql-community-libs-compat-8.0.3-0.1.rc.el7.x86_64mysql-community-libs-8.0.3-0.1.rc.el7.x86_64mysql-community-server-8.0.3-0.1.rc.el7.x86_64mysql-community-client-8.0.3-0.1.rc.el7.x86_64
Using the APT bundle
Download the MySQL APT TAR from the
MySQL Downloads
page,
http://dev.mysql.com/downloads/mysql/
:
shell> wget "https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-server_8.0.3-rc-1ubuntu16.04_amd64.deb-bundle.tar"~Saving to: ‘mysql-server_8.0.3-rc-1ubuntu16.04_amd64.deb-bundle.tar’~
Untar the packages:
shell> tar -xvf mysql-server_8.0.3-rc-1ubuntu16.04_amd64.deb-bundle.tar
Install the dependencies. You may need to install the
libaio1
package if it is not already installed:
shell> sudo apt-get install -y libaio1
Upgrade
libstdc++6
to the latest:
shell> sudo add-apt-repository ppa:ubuntu-toolchain-r/testshell> sudo apt-get updateshell> sudo apt-get upgrade -y libstdc++6
Upgrade
libmecab2
to the latest. If
universe
is not included, then add the following line to the end of the file (for example,
zesty
):
shell> sudo vi /etc/apt/sources.listdeb http://us.archive.ubuntu.com/ubuntu zesty main universeshell> sudo apt-get updateshell> sudo apt-get install libmecab2
Preconfigure the MySQL server package with the following command. It asks you to set the root password:
shell> sudo dpkg-preconfigure mysql-community-server_*.deb
Install the database common files package, the client package, the client metapackage, the server package, and the server metapackage (in that order); you can do that with a single command:
shell> sudo dpkg -i mysql-{common,community-client-core,community-client,client,community-server-core,community-server,server}_*.deb
Install the shared libraries:
shell> sudo dpkg -i libmysqlclient21_8.0.1-dmr-1ubuntu16.10_amd64.deb
Verify the installation:
shell> dpkg -l | grep -i mysqlii mysql-client 8.0.3-rc-1ubuntu14.04 amd64 MySQL Client meta package depending on latest versionii mysql-common 8.0.3-rc-1ubuntu14.04 amd64 MySQL Commonii mysql-community-client 8.0.3-rc-1ubuntu14.04 amd64 MySQL Clientii mysql-community-client-core 8.0.3-rc-1ubuntu14.04 amd64 MySQL Client Core Binariesii mysql-community-server 8.0.3-rc-1ubuntu14.04 amd64 MySQL Serverii mysql-community-server-core 8.0.3-rc-1ubuntu14.04 amd64 MySQL Server Core Binairesii mysql-server 8.0.3-rc-1ubuntu16.04 amd64 MySQL Server meta package depending on latest version
Installing MySQL on Linux using Generic Binaries
Installing using the software packages requires some dependencies to be installed first and can conflict with other packages. In that case, you can install MySQL using the generic binaries available on the downloads page. Binaries are precompiled using advanced compilers and are built with the best possible options for optimal performance.
How to do it...
MySQL has a dependency on the libaio library. The data directory initialization, and subsequent server startup steps, will fail if this library is not installed locally.
On YUM-based systems:
shell> sudo yum install -y libaio
On APT-based systems:
shell> sudo apt-get install -y libaio1
Download the TAR binary from the MySQL Downloads page, at https://dev.mysql.com/downloads/mysql/, then choose Linux - Generic as the OS and select the version. You can download directly onto your server directly using the wget command:
shell> cd /optshell> wget "https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.3-rc-linux-glibc2.12-x86_64.tar.gz"
Install MySQL using the following steps:
Add the
mysql
group and the
mysql
user. All the files and directories should be under the
mysql
user:
shell> sudo groupadd mysqlshell> sudo useradd -r -g mysql -s /bin/false mysql
This is the installation location (you can change it to another location):
shell> cd /usr/local
Untar the binary file. Keep the untarred binary file at the same location and symlink it to the installation location. In this way, you can keep multiple versions and it is very easy to upgrade. For example, you can download another version and untar it to a different location; while upgrading, all you need to do is to change the symlink:
shell> sudo tar zxvf /opt/mysql-8.0.3-rc-linux-glibc2.12-x86_64.tar.gzmysql-8.0.3-rc-linux-glibc2.12-x86_64/bin/myisam_ftdumpmysql-8.0.3-rc-linux-glibc2.12-x86_64/bin/myisamchk
mysql-8.0.3-rc-linux-glibc2.12-x86_64/bin/myisamlogmysql-8.0.3-rc-linux-glibc2.12-x86_64/bin/myisampackmysql-8.0.3-rc-linux-glibc2.12-x86_64/bin/mysql~
Make the symlink:
shell> sudo ln -s mysql-8.0.3-rc-linux-glibc2.12-x86_64 mysql
Create the necessary directories and change the ownership to
mysql
:
shell> cd mysqlshell> sudo mkdir mysql-filesshell> sudo chmod 750 mysql-filesshell> sudo chown -R mysql .shell> sudo chgrp -R mysql .
Initialize
mysql
, which generates a temporary password:
shell> sudo bin/mysqld --initialize --user=mysql~2017-12-02T05:55:10.822139Z 5 [Note] A temporary password is generated for root@localhost:
Aw=ee.rf(6Ua
~
Set up the RSA for SSL. Refer to
Chapter 14
,
Setting up Encrypted Connections using X509 Section
, for more details on SSL. Note that a temporary password is generated for
root@localhost: eJQdj8C*qVMq
:
shell> sudo bin/mysql_ssl_rsa_setupGenerating a 2048 bit RSA private key...........+++....................................+++writing new private key to 'ca-key.pem'-----Generating a 2048 bit RSA private key...........................................................+++...........................................+++writing new private key to 'server-key.pem'-----Generating a 2048 bit RSA private key.....+++..........................+++writing new private key to 'client-key.pem'-----
Change the ownership of binaries to
root
and
data
files to
mysql
:
shell> sudo chown -R root .shell> sudo chown -R mysql data mysql-files
Copy the startup script to
init.d
:
shell> sudo cp support-files/mysql.server /etc/init.d/mysql
Export the binary of
mysql
to the
PATH
environment variable:
shell> export PATH=$PATH:/usr/local/mysql/bin
Refer to
Starting or Stopping MySQL 8
section to start MySQL.
After installation, you will get the following directories inside /usr/local/mysql:
Directory
Contents of directory
bin
mysqld server, client, and utility programs
data
Log files, databases
docs
MySQL manual in info format
man
Unix manual pages
include
Include (header) files
lib
Libraries
share
Miscellaneous support files, including error messages, sample configuration files, SQL for database installation
There's more...
There are other installation methods, such as:
Compiling from the source code. You can compile and build MySQL from the source code provided by Oracle where you have the flexibility to customize build parameters, compiler optimizations, and the installation location. It is highly recommended to use precompiled binaries provided by Oracle, unless you want specific compiler options or you are debugging MySQL. This method is not covered as it is used very rarely and it requires several development tools, which is beyond the scope of this book. For installation through source code, you can refer to the reference manual, at
https://dev.mysql.com/doc/refman/8.0/en/source-installation.html
.
Using Docker. The MySQL server can also be installed and managed using Docker image. Refer to
https://hub.docker.com/r/mysql/mysql-server/
for installation, configuration, and also how to use MySQL under Docker.
Starting or Stopping MySQL 8
After the installation is completed, you can start/stop MySQL using the following commands, which vary from different platforms and installation methods. mysqld is the mysql server process. All the startup methods invoke the mysqld script.
How to do it...
Let's look at it in detail. Along with the starting and stopping, we will also learn something about checking the status of the server. Let's see how.
Starting the MySQL 8.0 server
You can start the server with the following commands:
Using
service
:
shell> sudo service mysql start
Using
init.d
:
shell> sudo /etc/init.d/mysql start
If you do not find the startup script (when you are doing binary installation), you can copy from the location where you untarred.
shell> sudo cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql
If your installation includes
systemd
support:
shell> sudo systemctl start mysqld
If the
systemd
support is not there, MySQL can be started using
mysqld_safe
.
mysqld_safe
is the launcher script for
mysqld
that safeguards the
mysqld
process. If
mysqld
is killed,
mysqld_safe
attempts to start the process again:
shell> sudo mysqld_safe --user=mysql &
After start,
The server is initialized.
The SSL certificate and key files are generated in the
data directory
.
The
validate_password
plugin is installed and enabled.
A superuser account,
root'@'localhost
, is created. A password for the superuser is set and stored in the error log file (not for binary installation). To reveal it, use the following command:
shell> sudo grep "temporary password" /var/log/mysqld.log 2017-12-02T07:23:20.915827Z 5 [Note] A temporary password is generated for root@localhost: bkvotsG:h6jD
You can connect to MySQL using that temporary password.
shell> mysql -u root -pbkvotsG:h6jDmysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 7Server version: 8.0.3-rc-logCopyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql>
Change the root password as soon as possible by logging in with the generated temporary password and setting a custom password for the superuser account:
# You will be prompted for a password, enter the one you got from the previous stepmysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'NewPass4!';Query OK, 0 rows affected (0.01 sec)# password should contain at least one Upper case letter, one lowercase letter, one digit, and one special character, and that the total password length is at least 8 characters
Stopping the MySQL 8.0 server
Stopping MySQL and checking the status are similar to starting it, except for the change of one word:
Using
service
:
shell> sudo service mysqld stopRedirecting to /bin/systemctl stop mysqld.service
Using
init.d
:
shell> sudo /etc/init.d/mysql stop[ ok ] Stopping mysql (via systemctl): mysql.service.
If your installation includes the
systemd
support (refer to the
Managing MySQL Server
with systemd
section
):
shell> sudo systemctl stop mysqld
Using
mysqladmin
:
shell> mysqladmin -u root -p shutdown
Checking the status of the MySQL 8.0 server
Using
service
:
shell> sudo systemctl status mysqld● mysqld.service - MySQL Server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled) Drop-In: /etc/systemd/system/mysqld.service.d └─override.conf
Active: active (running) since Sat 2017-12-02 07:33:53 UTC; 14s ago
Docs: man:mysqld(8) http://dev.mysql.com/doc/refman/en/using-systemd.html Process: 10472 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS) Process: 10451 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS) Main PID: 10477 (mysqld) CGroup: /system.slice/mysqld.service └─10477 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid --general_log=1Dec 02 07:33:51 centos7 systemd[1]: Starting MySQL Server...Dec 02 07:33:53 centos7 systemd[1]: Started MySQL Server.
Using
init.d
:
shell> sudo /etc/init.d/mysql status● mysql.service - LSB: start and stop MySQL Loaded: loaded (/etc/init.d/mysql; bad; vendor preset: enabled) Active: inactive (dead) Docs: man:systemd-sysv-generator(8)Dec 02 06:01:00 ubuntu systemd[1]: Starting LSB: start and stop MySQL...Dec 02 06:01:00 ubuntu mysql[20334]: Starting MySQLDec 02 06:01:00 ubuntu mysql[20334]: *Dec 02 06:01:00 ubuntu systemd[1]: Started LSB: start and stop MySQL.Dec 02 06:01:00 ubuntu mysql[20334]: 2017-12-02T06:01:00.969284Z mysqld_safe A mysqld process already existsDec 02 06:01:55 ubuntu systemd[1]: Stopping LSB: start and stop MySQL...Dec 02 06:01:55 ubuntu mysql[20445]: Shutting down MySQLDec 02 06:01:57 ubuntu mysql[20445]: .. *Dec 02 06:01:57 ubuntu systemd[1]: Stopped LSB: start and stop MySQL.Dec 02 07:26:33 ubuntu systemd[1]: Stopped LSB: start and stop MySQL.
If your installation includes the
systemd
support (refer to the
Managing MySQL Server with systemd
section):
shell> sudo systemctl status mysqld
Uninstalling MySQL 8
If you have messed up with installation or you do not want MySQL 8 version, you can uninstall using the following steps. Before uninstalling, make sure to make backup files (refer to Chapter 7, Backups), if required, and stop MySQL.
How to do it...
Uninstalling will be dealt in a different way on different systems. Let's look at how.
On YUM-based systems
Check whether there are any existing packages:
shell> rpm -qa | grep -i mysql-communitymysql-community-libs-8.0.3-0.1.rc.el7.x86_64mysql-community-common-8.0.3-0.1.rc.el7.x86_64mysql-community-client-8.0.3-0.1.rc.el7.x86_64mysql-community-libs-compat-8.0.3-0.1.rc.el7.x86_64mysql-community-server-8.0.3-0.1.rc.el7.x86_64
Remove the packages. You may be notified that there are other packages dependent on MySQL. If you plan on installing MySQL again, you can ignore the warning by passing the
--nodeps
option:
shell> rpm -e <package-name>
For example:
shell> sudo rpm -e mysql-community-server
To remove all packages:
shell> sudo rpm -qa | grep -i mysql-community | xargs sudo rpm -e --nodepswarning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
On APT-based systems
Check whether there are any existing packages:
shell> dpkg -l | grep -i mysql
Remove the packages using the following:
shell> sudo apt-get remove mysql-community-server mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server mysql-community-server-core -yReading package lists... DoneBuilding dependency tree Reading state information... DoneThe following packages will be REMOVED: mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server mysql-community-server-core mysql-server0 upgraded, 0 newly installed, 7 to remove and 341 not upgraded.After this operation, 357 MB disk space will be freed.(Reading database ... 134358 files and directories currently installed.)Removing mysql-server (8.0.3-rc-1ubuntu16.04) ...Removing mysql-community-server (8.0.3-rc-1ubuntu16.04) ...update-alternatives: using /etc/mysql/my.cnf.fallback to provide /etc/mysql/my.cnf (my.cnf) in auto modeRemoving mysql-client (8.0.3-rc-1ubuntu16.04) ...Removing mysql-community-client (8.0.3-rc-1ubuntu16.04) ...Removing mysql-common (8.0.3-rc-1ubuntu16.04) ...Removing mysql-community-client-core (8.0.3-rc-1ubuntu16.04) ...Removing mysql-community-server-core (8.0.3-rc-1ubuntu16.04) ...Processing triggers for man-db (2.7.5-1) ...
Or remove them using:
shell> sudo apt-get remove --purge mysql-\* -yshell> sudo apt-get autoremove -y
Verify that the packages are uninstalled:
shell> dpkg -l | grep -i mysqlii mysql-apt-config 0.8.9-1 all Auto configuration for MySQL APT Repo.rc mysql-common 8.0.3-rc-1ubuntu16.04 amd64 MySQL Commonrc mysql-community-client 8.0.3-rc-1ubuntu16.04 amd64 MySQL Clientrc mysql-community-server 8.0.3-rc-1ubuntu16.04 amd64 MySQL Server
rc indicates that the packages have been removed (r), and only config files (c) have been kept.
Uninstalling Binaries
It is very simple to uninstall a binary installation. All you need to do is to remove the symlink:
Change the directory to the installation path:
shell> cd /usr/local
Check where
mysql
is pointing to, which will show the path it is referencing to:
shell> sudo ls -lh mysql
Remove
mysql
:
shell> sudo rm mysql
Remove the binaries (optional):
shell> sudo rm -f /opt/mysql-8.0.3-rc-linux-glibc2.12-x86_64.tar.gz
Managing the MySQL Server with systemd
If you install MySQL using an RPM or Debian package server, startup and shutdown is managed by systemd. On platforms for which the systemd support for MySQL is installed, mysqld_safe, mysqld_multi, and mysqld_multi.server are not installed. MySQL server startup and shutdown is managed by systemd using the systemctl command. You need to configure systemd as follows.
How to do it...
Create a localized
systemd
configuration file:
shell> sudo mkdir -pv /etc/systemd/system/mysqld.service.d
Create/open the
conf
file:
shell> sudo vi /etc/systemd/system/mysqld.service.d/override.conf
Enter the following:
[Service]LimitNOFILE=max_open_files (ex: 102400)PIDFile=/path/to/pid/file (ex: /var/lib/mysql/mysql.pid)Nice=nice_level (ex: -10)Environment="LD_PRELOAD=/path/to/malloc/library" Environment="TZ=time_zone_setting"
Reload
systemd
:
shell> sudo systemctl daemon-reload
For temporary changes, you can reload without editing the
conf
file:
shell> sudo systemctl set-environment MYSQLD_OPTS="--general_log=1"or unset usingshell> sudo systemctl unset-environment MYSQLD_OPTS
After modifying the
systemd
environment, restart the server to make the changes effective. Enable
mysql.serviceshell> sudo systemctl
, and enable
mysql.service
:
shell> sudo systemctl unmask mysql.service
Restart
mysql
: On RPM platforms:
shell> sudo systemctl restart mysqld
On Debian platforms:
shell> sudo systemctl restart mysql
Downgrading from MySQL 8.0
If your application is not performing as expected, you can always downgrade to a previous GA release (MySQL 5.7). Before downgrading, it is recommended to take a logical backup (refer to Chapter 7, Backups). Note that you can downgrade by only one previous release. Suppose that you want to downgrade from MySQL 8.0 to MySQL 5.6, you have to downgrade to MySQL 5.7, and then from MySQL 5.7 to MySQL 5.6.
You can do it in two ways:
In-place downgrade (downgrades within MySQL 8)
Logical downgrade
How to do it...
In the following subsections, you will be learning how to handle the installation/uninstallation/upgrade/downgrade using various repositories, bundles, and so on.
In-place Downgrades
For downgrades between the GA status releases within MySQL 8.0 (note that you cannot downgrade to MySQL 5.7 using this method):
Shut down the old MySQL version
Replace the MySQL 8.0 binaries or older binaries
Restart MySQL on the existing
data directory
Run the
mysql_upgrade
utility
Using APT Repositories
Reconfigure MySQL and choose the older version:
shell> sudo dpkg-reconfigure mysql-apt-config
Run
apt-get update
:
shell> sudo apt-get update
Remove the current version:
