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

MySQL Linux RPM package CPU identifiers

MySQL Debian and Ubuntu 7 and 8 installation packages CPU identifiers

MySQL Debian 6 Installation package CPU identifiers

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)

Let's look at steps for installing MySQL 8 in the following ways:

1. Find the Red Hat or CentOS version:

shell> cat /etc/redhat-release
CentOS Linux release 7.3.1611 (Core)

2. 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.rpm
Loaded plugins: fastestmirror
Examining mysql57-community-release-el7-11.noarch.rpm: mysql57-community-release-el7-11.noarch
Marking mysql57-community-release-el7-11.noarch.rpm to be installed
Resolving 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-11
Complete!

3. 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.rpm
Preparing...                          ################################# [100%]
Updating / installing...
   1:mysql57-community-release-el7-11 ################################# [100%]

4. Verify the installation:

shell> yum repolist enabled | grep 'mysql.*-community.*'
mysql-connectors-community/x86_64 MySQL Connectors Community                  42
mysql-tools-community/x86_64      MySQL Tools Community                       53
mysql57-community/x86_64          MySQL 5.7 Community Server                 227

5. 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 mysql
mysql-cluster-7.5-community/x86_64   MySQL Cluster 7.5 Community disabled
mysql-cluster-7.5-community-source   MySQL Cluster 7.5 Community disabled
mysql-cluster-7.6-community/x86_64   MySQL Cluster 7.6 Community disabled
mysql-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 disabled
mysql-tools-preview/x86_64           MySQL Tools Preview         disabled
mysql-tools-preview-source           MySQL Tools Preview - Sourc disabled
mysql55-community/x86_64             MySQL 5.5 Community Server  disabled
mysql55-community-source             MySQL 5.5 Community Server  disabled
mysql56-community/x86_64             MySQL 5.6 Community Server  disabled
mysql56-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

6. Disable mysql57-community and enable mysql80-community:

shell> sudo yum install yum-utils.noarch -y
shell> sudo yum-config-manager --disable mysql57-community
shell> sudo yum-config-manager --enable mysql80-community

7. 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

8. Install MySQL 8:

shell> sudo yum install -y mysql-community-server
Loaded plugins: fastestmirror
mysql-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.com
Resolving Dependencies
~
Transaction test succeeded
Running 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.el7
Dependency 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!

9. You can check the installed packages using the following:

shell> rpm -qa | grep -i 'mysql.*8.*'
perl-DBD-MySQL-4.023-5.el7.x86_64
mysql-community-libs-8.0.3-0.1.rc.el7.x86_64
mysql-community-common-8.0.3-0.1.rc.el7.x86_64
mysql-community-client-8.0.3-0.1.rc.el7.x86_64
mysql-community-server-8.0.3-0.1.rc.el7.x86_64

1. 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"

2. 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

3. 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

4. Update package information from the MySQL APT repository with the following command (this step is mandatory):

shell> sudo apt-get update

5. 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 mode
Setting up mysql-community-client-core (8.0.3-rc-1ubuntu14.04) ...
Setting up mysql-community-server-core (8.0.3-rc-1ubuntu14.04) ...
~

6. Verify packages. ii indicates that the package is installed:

shell> dpkg -l | grep -i mysql
ii  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 version
ii  mysql-common                8.0.3-rc-1ubuntu14.04 amd64 MySQL Common
ii  mysql-community-client      8.0.3-rc-1ubuntu14.04 amd64 MySQL Client
ii  mysql-community-client-core 8.0.3-rc-1ubuntu14.04 amd64 MySQL Client Core Binaries
ii  mysql-community-server      8.0.3-rc-1ubuntu14.04 amd64 MySQL Server
ii  mysql-community-server-core 8.0.3-rc-1ubuntu14.04 amd64 MySQL Server Core Binaires

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.

Let's look at how to do it using the following types of bundles:

1. 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’
~

2. Untar the package:

shell> tar xfv mysql-8.0.3-0.1.rc.el7.x86_64.rpm-bundle.tar

3. Install MySQL:

shell> sudo rpm -i mysql-community-{server-8,client,common,libs}*

4. 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

5. Verify the installation:

shell> rpm -qa | grep -i mysql-community
mysql-community-common-8.0.3-0.1.rc.el7.x86_64
mysql-community-libs-compat-8.0.3-0.1.rc.el7.x86_64
mysql-community-libs-8.0.3-0.1.rc.el7.x86_64
mysql-community-server-8.0.3-0.1.rc.el7.x86_64
mysql-community-client-8.0.3-0.1.rc.el7.x86_64

1. 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’
~

2. Untar the packages:

shell> tar -xvf mysql-server_8.0.3-rc-1ubuntu16.04_amd64.deb-bundle.tar 

3. Install the dependencies. You may need to install the libaio1 package if it is not already installed:

shell> sudo apt-get install -y libaio1

4. Upgrade libstdc++6 to the latest:

shell> sudo add-apt-repository ppa:ubuntu-toolchain-r/test
shell> sudo apt-get update
shell> sudo apt-get upgrade -y libstdc++6

5. 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.list
deb http://us.archive.ubuntu.com/ubuntu zesty main universe

shell> sudo apt-get update
shell> sudo apt-get install libmecab2

6. 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

7. 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

8. Install the shared libraries:

shell> sudo dpkg -i libmysqlclient21_8.0.1-dmr-1ubuntu16.10_amd64.deb

9. Verify the installation:

shell> dpkg -l | grep -i mysql
ii  mysql-client                8.0.3-rc-1ubuntu14.04 amd64 MySQL Client meta package depending on latest version
ii  mysql-common                8.0.3-rc-1ubuntu14.04 amd64 MySQL Common
ii  mysql-community-client      8.0.3-rc-1ubuntu14.04 amd64 MySQL Client
ii  mysql-community-client-core 8.0.3-rc-1ubuntu14.04 amd64 MySQL Client Core Binaries
ii  mysql-community-server      8.0.3-rc-1ubuntu14.04 amd64 MySQL Server
ii  mysql-community-server-core 8.0.3-rc-1ubuntu14.04 amd64 MySQL Server Core Binaires
ii  mysql-server                8.0.3-rc-1ubuntu16.04 amd64 MySQL Server meta package depending on latest version

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.

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 /opt
shell> 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:

1. Add the mysql group and the mysql user. All the files and directories should be under the mysql user:

shell> sudo groupadd mysql
shell> sudo useradd -r -g mysql -s /bin/false mysql

2. This is the installation location (you can change it to another location):

shell> cd /usr/local

3. 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.gz
mysql-8.0.3-rc-linux-glibc2.12-x86_64/bin/myisam_ftdump
mysql-8.0.3-rc-linux-glibc2.12-x86_64/bin/myisamchk

mysql-8.0.3-rc-linux-glibc2.12-x86_64/bin/myisamlog
mysql-8.0.3-rc-linux-glibc2.12-x86_64/bin/myisampack
mysql-8.0.3-rc-linux-glibc2.12-x86_64/bin/mysql
~

4. Make the symlink:

shell> sudo ln -s mysql-8.0.3-rc-linux-glibc2.12-x86_64 mysql

5. Create the necessary directories and change the ownership to mysql:

shell> cd mysql
shell> sudo mkdir mysql-files
shell> sudo chmod 750 mysql-files
shell> sudo chown -R mysql .
shell> sudo chgrp -R mysql .

6. 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
~

7. 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_setup
Generating 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'
-----

8. 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

9. Copy the startup script to init.d:

shell> sudo cp support-files/mysql.server /etc/init.d/mysql

10. Export the binary of mysql to the PATH environment variable:

shell> export PATH=$PATH:/usr/local/mysql/bin

11. Refer to Starting or Stopping MySQL 8 section to start MySQL.

After installation, you will get the following directories inside /usr/local/mysql:

There are other installation methods, such as:

1. 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.
2. 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.

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.

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.

You can start the server with the following commands:

1. Using service:

shell> sudo service mysql start

2. Using init.d:

shell> sudo /etc/init.d/mysql start

3. 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

4. If your installation includes systemd support:

shell> sudo systemctl start mysqld

5. 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,

1. The server is initialized.
2. The SSL certificate and key files are generated in the data directory.
3. The validate_password plugin is installed and enabled.
4. 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:h6jD
mysql: [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 7
Server version: 8.0.3-rc-log

Copyright (c) 2000, 2017, Oracle and/or its affiliates. All rights reserved.

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> 

5. 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 step

mysql> 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 MySQL and checking the status are similar to starting it, except for the change of one word:

1. Using service:

shell> sudo service mysqld stop
Redirecting to /bin/systemctl stop  mysqld.service

2. Using init.d:

shell> sudo /etc/init.d/mysql stop
[ ok ] Stopping mysql (via systemctl): mysql.service.

3. If your installation includes the systemd support (refer to the Managing MySQL Server with systemd section):

shell> sudo systemctl stop mysqld

4. Using mysqladmin:

shell> mysqladmin -u root -p shutdown

1. 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=1

Dec 02 07:33:51 centos7 systemd[1]: Starting MySQL Server...
Dec 02 07:33:53 centos7 systemd[1]: Started MySQL Server.

2. 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 MySQL
Dec 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 exists
Dec 02 06:01:55 ubuntu systemd[1]: Stopping LSB: start and stop MySQL...
Dec 02 06:01:55 ubuntu mysql[20445]: Shutting down MySQL
Dec 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.

3. If your installation includes the systemd support (refer to the Managing MySQL Server with systemd section):

shell> sudo systemctl status mysqld

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.

Uninstalling will be dealt in a different way on different systems. Let's look at how.

1. Check whether there are any existing packages:

shell> rpm -qa | grep -i mysql-community
mysql-community-libs-8.0.3-0.1.rc.el7.x86_64
mysql-community-common-8.0.3-0.1.rc.el7.x86_64
mysql-community-client-8.0.3-0.1.rc.el7.x86_64
mysql-community-libs-compat-8.0.3-0.1.rc.el7.x86_64
mysql-community-server-8.0.3-0.1.rc.el7.x86_64

2. 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

3. To remove all packages:

shell> sudo rpm -qa | grep -i mysql-community | xargs sudo rpm -e --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

1. Check whether there are any existing packages:

shell> dpkg -l | grep -i mysql

2. 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 -y
Reading package lists... Done
Building dependency tree       
Reading state information... Done
The following packages will be REMOVED:
  mysql-client mysql-common mysql-community-client mysql-community-client-core mysql-community-server mysql-community-server-core mysql-server
0 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 mode
Removing 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-\* -y
shell> sudo apt-get autoremove -y

3. Verify that the packages are uninstalled:

shell> dpkg -l | grep -i mysql
ii  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 Common
rc  mysql-community-client  8.0.3-rc-1ubuntu16.04 amd64        MySQL Client
rc  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.

It is very simple to uninstall a binary installation. All you need to do is to remove the symlink:

1. Change the directory to the installation path:

shell> cd /usr/local

2. Check where mysql is pointing to, which will show the path it is referencing to:

shell> sudo ls -lh mysql

3. Remove mysql:

shell> sudo rm mysql

4. Remove the binaries (optional):

shell> sudo rm -f /opt/mysql-8.0.3-rc-linux-glibc2.12-x86_64.tar.gz

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.

1. Create a localized systemd configuration file:

shell> sudo mkdir -pv /etc/systemd/system/mysqld.service.d

2. Create/open the conf file:

shell> sudo vi /etc/systemd/system/mysqld.service.d/override.conf

3. 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"

4. Reload systemd:

shell> sudo systemctl daemon-reload

5. For temporary changes, you can reload without editing the conf file:

shell> sudo systemctl set-environment MYSQLD_OPTS="--general_log=1"
or unset using
shell> sudo systemctl unset-environment MYSQLD_OPTS

6. 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

7. Restart mysql:
   On RPM platforms:

shell> sudo systemctl restart mysqld

On Debian platforms:

shell> sudo systemctl restart mysql

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

In the following subsections, you will be learning how to handle the installation/uninstallation/upgrade/downgrade using various repositories, bundles, and so on.

For downgrades between the GA status releases within MySQL 8.0 (note that you cannot downgrade to MySQL 5.7 using this method):

1. Shut down the old MySQL version
2. Replace the MySQL 8.0 binaries or older binaries
3. Restart MySQL on the existing data directory
4. Run the mysql_upgrade utility

1. Prepare MySQL for a slow shutdown, which ensures that the undo logs are empty and data files are fully prepared in case of file format differences between releases:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

2. Shut down the mysql server as described in the Stopping MySQL 8.0 Server section:

shell> sudo systemctl stop mysqld

3. Remove the InnoDB redo log files (the ib_logfile* files) from the data directory to avoid downgrade issues related to redo log file format changes that may have occurred between releases:

shell> sudo rm -rf /var/lib/mysql/ib_logfile*

4. Downgrade MySQL. To downgrade the server, you need to uninstall MySQL 8.0, as described in the Uninstalling MySQL 8 section. The configuration files are automatically stored as backup.
   
   List the available versions:

shell> sudo yum list mysql-community-server

Downgrades are tricky; it is better to remove the existing packages before downgrading:

shell> sudo rpm -qa | grep -i mysql-community | xargs sudo rpm -e --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

Install the older version:

shell> sudo yum install -y mysql-community-server-<version>

1. Reconfigure MySQL and choose the older version:

shell> sudo dpkg-reconfigure mysql-apt-config

2. Run apt-get update:

shell> sudo apt-get update

3. Remove the current version:

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 -y

shell> sudo apt-get autoremove

4. Install the older version (autoselected since you have reconfigured):

shell> sudo apt-get install -y mysql-server

Uninstall the existing packages (refer to the Uninstalling MySQL 8 section) and install the new packages, which can be downloaded from the MySQL Downloads (refer to the Installing MySQL 8.0 using RPMs or DEB files section).

If you have installed MySQL through binaries, you have to remove the symlink to the old version (refer to the Uninstalling MySQL 8 section) and do a fresh installation (refer to the Installing MySQL on Linux Using Generic Binaries section):

1. Start the server as described in the Starting or Stopping MySQL 8 section. Please note that the start procedure is the same for all the versions.
2. Run the mysql_upgrade utility:

shell> sudo mysql_upgrade -u root -p

3. Restart the MySQL server to ensure that any changes made to the system tables take effect:

shell> sudo systemctl restart mysqld

Here is an outline of the steps:

1. Export existing data from the MySQL 8.0 version using logical backup (refer to Chapter 7, Backups for logical backup methods)
2. Install MySQL 5.7
3. Load the dump file into the MySQL 5.7 version (refer to Chapter 8, Restoring Data for restoring methods)
4. Run the mysql_upgrade utility

Here are the detailed steps:

1. You need to take logical backup of the database. (refer to Chapter 7, Backups for a quicker backup called mydumper):

shell> mysqldump -u root -p --add-drop-table --routines --events --all-databases --force > mysql80.sql

2. Shut down the MySQL server as described in the Starting or Stopping MySQL 8 section.
3. Move the data directory. Instead of restoring the SQL backup (in step 1), we can move back the data directory if you want to keep MySQL 8:

shell> sudo mv /var/lib/mysql /var/lib/mysql80

4. Downgrade MySQL. To downgrade the server, we need to uninstall MySQL 8. The configuration files are automatically backed up.

After the uninstallation, install the older version:

1. Switch the repositories:

shell> sudo yum-config-manager --disable mysql80-community
shell> sudo yum-config-manager --enable mysql57-community

2. Verify that mysql57-community is enabled:

shell> yum repolist enabled | grep "mysql.*-community.*"
!mysql-connectors-community/x86_64 MySQL Connectors Community                 42
!mysql-tools-community/x86_64      MySQL Tools Community                      53
!mysql57-community/x86_64          MySQL 5.7 Community Server                227

3. Downgrades are tricky; it is better to remove the existing packages before downgrading:

shell> sudo rpm -qa | grep -i mysql-community | xargs sudo rpm -e --nodeps
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave

4. List the available versions:

shell> sudo yum list mysql-community-server
Loaded plugins: fastestmirror
Loading mirror speeds from cached hostfile
 * base: mirror.rackspace.com
 * epel: mirrors.develooper.com
 * extras: centos.s.uw.edu
 * updates: mirrors.syringanetworks.net
Available Packages
mysql-community-server.x86_64   5.7.20-1.el7                         mysql57-community

5. Install MySQL 5.7:

shell> sudo yum install -y mysql-community-server

1. Reconfigure apt to switch to MySQL 5.7:

shell> sudo dpkg-reconfigure mysql-apt-config

2. Run apt-get update:

shell> sudo apt-get update

3. Remove the current version:

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 -y
shell> sudo apt-get autoremove

4. Install MySQL 5.7:

shell> sudo apt-get install -y mysql-server

Uninstall the existing packages (refer to the Uninstalling MySQL 8 section) and install the new packages, which can be downloaded from MySQL Downloads (refer to the Installing MySQL 8 using RPM or DEB files section).

If you have installed MySQL through binaries, you have to remove the symlink to the old version (refer to the Uninstalling MySQL 8 section) and do a fresh installation (refer to the Installing MySQL on Linux using Generic Binaries section).

Once you have downgraded MySQL, you have to restore the backup and run the mysql_upgrade utility:

1. Start MySQL (refer to the Starting or Stopping MySQL 8 section). You need to reset the password again.
2. Restore the backup (this may take a long time, depending up on the size of backup). Refer to Chapter 8, Restoring Data, for a quick restoration method called myloader:

shell> mysql -u root -p < mysql80.sql

3. Run mysql_upgrade:

shell> mysql_upgrade -u root -p

4. Restart the MySQL server to ensure that any changes made to the system tables take effect. Refer to the Starting or Stopping MySQL 8 section:

shell> sudo /etc/init.d/mysql restart

MySQL 8 uses a global data dictionary containing information about database objects in transactional tables. In previous versions, the dictionary data was stored in metadata files and non-transactional system tables. You need to upgrade your data directory  from the file-based structure to the data-dictionary structure.

Just like a downgrade, you can upgrade using two methods:

• In-place upgrade
• Logical upgrade

You also should check a few prerequisites before the upgrade.

1. Check for obsolete datatypes or triggers that have a missing or empty definer or an invalid creation context:

shell> sudo mysqlcheck -u root -p --all-databases --check-upgrade

2. There must be no partitioned tables that use a storage engine that does not have native partitioning support. To identify these tables, execute this query:

shell> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE ENGINE NOT IN ('innodb', 'ndbcluster') AND CREATE_OPTIONS LIKE '%partitioned%';

If there are any of these tables, change them to InnoDB:

mysql> ALTER TABLE table_name ENGINE = INNODB;

Or remove the partitioning:

mysql> ALTER TABLE table_name REMOVE PARTITIONING;

3. There must be no tables in the MySQL 5.7 mysql system database that have the same name as a table used by the MySQL 8.0 data dictionary. To identify tables with those names, execute this query:

mysql> SELECT TABLE_SCHEMA, TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE LOWER(TABLE_SCHEMA) = 'mysql' and LOWER(TABLE_NAME) IN ('catalogs', 'character_sets', 'collations', 'column_type_elements', 'columns', 'events', 'foreign_key_column_usage', 'foreign_keys', 'index_column_usage', 'index_partitions', 'index_stats', 'indexes', 'parameter_type_elements', 'parameters', 'routines', 'schemata', 'st_spatial_reference_systems', 'table_partition_values', 'table_partitions', 'table_stats', 'tables', 'tablespace_files', 'tablespaces', 'triggers', 'version', 'view_routine_usage', 'view_table_usage');

4. There must be no tables that have foreign key constraint names longer than 64 characters. To identify tables with constraint names that are too long, execute this query:

mysql> SELECT CONSTRAINT_SCHEMA, TABLE_NAME, CONSTRAINT_NAME FROM INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS WHERE LENGTH(CONSTRAINT_NAME) > 64;

5. Tables not supported by MySQL 8.0, such as ndb, should be moved to InnoDB:

mysql> ALTER TABLE tablename ENGINE=InnoDB;

Just like the previous recipe, the following subsections will take you through the details with various systems, bundles, and so on. 

Here is an outline of the steps:

1. Shut down the old MySQL version.
2. Replace the old MySQL binaries or packages with the new ones (detailed steps for different types of installation methods are covered).
3. Restart MySQL on the existing data directory.
4. Run the mysql_upgrade utility.
5. In the MySQL 5.7 server, if there are encrypted InnoDB tablespaces, rotate the keyring master key by executing this statement:

mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;

Here are the detailed steps:

1. Configure your MySQL 5.7 server to perform a slow shutdown. With a slow shutdown, InnoDB performs a full purge and change buffer merge before shutting down, which ensures that the undo logs are empty and the data files are fully prepared in case of file format differences between releases.
   This step is the most important because, without it, you will end up with the following error:

[ERROR] InnoDB: Upgrade after a crash is not supported. 

This redo log was created with MySQL 5.7.18. Please follow the instructions at http://dev.mysql.com/doc/refman/8.0/en/upgrading.html:

mysql> SET GLOBAL innodb_fast_shutdown = 0;

2. Shut down the MySQL server as described in the Starting or Stopping MySQL 8 section.

Upgrade the MySQL binaries or packages.

1. Switch the repositories:

shell> sudo yum-config-manager --disable mysql57-community
shell> sudo yum-config-manager --enable mysql80-community

2. 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

3. Run the yum update:

shell> sudo yum update mysql-server

1. Reconfigure the apt to switch to MySQl 8.0:

shell> sudo dpkg-reconfigure mysql-apt-config

2. Run apt-get update:

shell> sudo apt-get update

3. Remove the current version:

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 -y
shell> sudo apt-get autoremove

4. Install MySQL 8:

shell> sudo apt-get update
shell> sudo apt-get install mysql-server
shell> sudo apt-get install libmysqlclient21

Uninstall the existing packages (refer to the Uninstalling MySQL 8 section) and install the new packages, which can be downloaded from MySQL Downloads (refer to the Installing MySQL 8.0 using RPM or DEB files section).

If you have installed MySQL through binaries, you have to remove the symlink to the old version (refer to the Uninstalling MySQL 8 section) and do a fresh installation (refer to the Installing MySQL on Linux using generic binaries section).

Start the MySQL 8.0 server (refer to the Starting or Stopping MySQL 8 to start MySQL section). If there are encrypted InnoDB tablespaces, use the --early-plugin-load option to load the keyring plugin.

The server automatically detects whether data dictionary tables are present. If not, the server creates them in the data directory , populates them with metadata, and then proceeds with its normal startup sequence. During this process, the server upgrades metadata for all database objects, including databases, tablespaces, system and user tables, views, and stored programs (stored procedures and functions, triggers, event scheduler events). The server also removes files that previously were used for metadata storage. For example, after upgrading, you will notice that your tables no longer have .frm files.

The server creates a directory named backup_metadata_57 and moves the files used by MySQL 5.7 into it. The server renames the event and proc tables to event_backup_57 and proc_backup_57. If this upgrade fails, the server reverts all changes to the data directory. In this case, you should remove all redo log files, start your MySQL 5.7 server on the same data directory, and fix the cause of any errors. Then, perform another slow shutdown of the 5.7 server and start the MySQL 8.0 server to try again.

Run the mysql_upgrade utility:

shell> sudo mysql_upgrade -u root -p

mysql_upgrade examines all tables in all databases for incompatibilities with the current version of MySQL. It makes any remaining changes required in the mysql system database between MySQL 5.7 and MySQL 8.0, so that you can take advantage of new privileges or capabilities. mysql_upgrade also brings the performance schema, INFORMATION_SCHEMA, and sys schema objects up to date for MySQL 8.0.

Restart the MySQL server (refer to the Starting or Stopping MySQL 8 to start MySQL section).

Here is an outline of the steps:

1. Export existing data from the old MySQL version using mysqldump
2. Install the new MySQL version
3. Load the dump file into the new MySQL version
4. Run the mysql_upgrade utility

Here are the detailed steps:

1. You need to take a logical backup of the database (refer to Chapter 7, Backups for a quicker backup called mydumper):

shell> mysqldump -u root -p --add-drop-table --routines --events --all-databases --ignore-table=mysql.innodb_table_stats --ignore-table=mysql.innodb_index_stats --force > data-for-upgrade.sql

2. Shut down the MySQL server (refer to the Starting or Stopping MySQL 8 section).
3. Install the new MySQL version (refer to the methods mentioned in the In-place upgrades section).
4. Start the MySQL server (refer to the Starting or Stopping MySQL 8 section).
5. Reset the temporary root password:

shell> mysql -u root -p
Enter password: **** (enter temporary root password from error log)

mysql> ALTER USER USER() IDENTIFIED BY 'your new password';

6. Restore the backup (this may take a long time depending up on the size of the backup). Refer to Chapter 8, Restoring Data for a quick restoration method called myloader:

shell> mysql -u root -p --force < data-for-upgrade.sql

7. Run the mysql_upgrade utility:

shell> sudo mysql_upgrade -u root -p

8. Restart the MySQL server (refer to the Starting or Stopping MySQL 8 section).

MySQL utilities gives you very handy tools to smoothly carry out day-to-day operations without much manual effort.

It can be installed on YUM-based and APT-based systems in the following manner. Let's take a look.

Download the files from the MySQL downloads page, https://dev.mysql.com/downloads/utilities/, by selecting Red Hat Enterprise Linux/Oracle Linux, or directly from this link, using wget:

shell> wget https://cdn.mysql.com//Downloads/MySQLGUITools/mysql-utilities-1.6.5-1.el7.noarch.rpm

shell> sudo yum localinstall -y mysql-utilities-1.6.5-1.el7.noarch.rpm

Download the files from the MySQL Downloads page, https://dev.mysql.com/downloads/utilities/, by selecting Ubuntu Linux,  or directly from this link, using wget:

shell> wget "https://cdn.mysql.com//Downloads/MySQLGUITools/mysql-utilities_1.6.5-1ubuntu16.10_all.deb"
shell> sudo dpkg -i mysql-utilities_1.6.5-1ubuntu16.10_all.deb
shell> sudo apt-get install -f