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
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 |
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:
- 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.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!
- 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%]
- 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
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
- Disable
mysql57-community
and enablemysql80-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
- 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-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!
- 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
- 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 usewget
to download directly on to the server. You might need to installwget
(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). Selectmysql-8.0
preview (At the time of writing, MySQL 8.0 is not GA). You might get a warning such asMySQL 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 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) ... ~
- 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 administrationand 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:
- 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 theDevelopment 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-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
- 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/test
shell> sudo apt-get update
shell> sudo apt-get upgrade -y libstdc++6
- Upgrade
libmecab2
to the latest. Ifuniverse
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
- 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
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 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:
- Add the
mysql
group and themysql
user. All the files and directories should be under themysql
user:
shell> sudo groupadd mysql shell> 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.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 ~
- 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 mysql shell> sudo mkdir mysql-files shell> sudo chmod 750 mysql-files shell> 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_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' -----
- Change the ownership of binaries to
root
anddata
files tomysql
:
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 thePATH
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 |
|
|
| Log files, databases |
| MySQL manual in info format |
| Unix manual pages |
| Include (header) files |
| Libraries |
| Miscellaneous support files, including error messages, sample configuration files, SQL for database installation |
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.
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:
- 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 usingmysqld_safe
.mysqld_safe
is the launcher script formysqld
that safeguards themysqld
process. Ifmysqld
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: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>
- 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:
- Using
service
:
shell> sudo service mysqld stop Redirecting 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 Serverwith systemd section):
shell> sudo systemctl stop mysqld
- Using
mysqladmin
:
shell> mysqladmin -u root -p shutdown
- 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.
- 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.
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.
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
- 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 --nodeps warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
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 -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
- 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:
- 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
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.
Note
RPM-based systems use the mysqld.service
files, and APT-based systems use the mysql.server
files.
- 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 using shell> sudo systemctl unset-environment MYSQLD_OPTS
- After modifying the
systemd
environment, restart the server to make the changes effective. Enablemysql.serviceshell> sudo systemctl
, and enablemysql.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
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):
- 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
mysql> SET GLOBAL innodb_fast_shutdown = 0;
- Shut down the
mysql
server as described in the Stopping MySQL 8.0 Server section:
shell> sudo systemctl stop mysqld
- Remove the
InnoDB
redo log files (theib_logfile*
files) from thedata 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*
- 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>
shell> sudo dpkg-reconfigure mysql-apt-config
- Run
apt-get update
:
shell> sudo apt-get update
- 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
- 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):
- 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.
- Run the
mysql_upgrade
utility:
shell> sudo mysql_upgrade -u root -p
- 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:
- Export existing data from the MySQL 8.0 version using logical backup (refer to Chapter 7, Backups for logical backup methods)
- Install MySQL 5.7
- Load the dump file into the MySQL 5.7 version (refer to Chapter 8, Restoring Data for restoring methods)
- Run the
mysql_upgrade
utility
Here are the detailed steps:
- 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
- Shut down the MySQL server as described in the Starting or Stopping MySQL 8 section.
- Move the
data directory
. Instead of restoring the SQL backup (in step 1), we can move back thedata directory
if you want to keep MySQL 8:
shell> sudo mv /var/lib/mysql /var/lib/mysql80
- 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:
- Switch the repositories:
shell> sudo yum-config-manager --disable mysql80-community shell> sudo yum-config-manager --enable mysql57-community
- 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
- 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
- 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
- Install MySQL 5.7:
shell> sudo yum install -y mysql-community-server
shell> sudo dpkg-reconfigure mysql-apt-config
- Run
apt-get update
:
shell> sudo apt-get update
- 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
- 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 fromMySQL 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:
- Start MySQL (refer to the Starting or Stopping MySQL 8 section). You need to reset the password again.
- 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
- Run
mysql_upgrade
:
shell> mysql_upgrade -u root -p
- 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.
- 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
- 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;
- 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.0data 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');
- 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;
- Tables not supported by MySQL 8.0, such as
ndb
, should be moved toInnoDB
:
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:
- Shut down the old MySQL version.
- Replace the old MySQL binaries or packages with the new ones (detailed steps for different types of installation methods are covered).
- Restart MySQL on the existing
data directory
. - Run the
mysql_upgrade
utility. - In the MySQL 5.7 server, if there are encrypted
InnoDB
tablespaces, rotate thekeyring
master key by executing this statement:
mysql> ALTER INSTANCE ROTATE INNODB MASTER KEY;
Here are the detailed steps:
- 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;
- Shut down the MySQL server as described in the Starting or Stopping MySQL 8 section.
Upgrade the MySQL binaries or packages.
shell> sudo yum-config-manager --disable mysql57-community shell> 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
- Run the yum update:
shell> sudo yum update mysql-server
shell> sudo dpkg-reconfigure mysql-apt-config
- Run
apt-get update
:
shell> sudo apt-get update
- 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
- 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 fromMySQL 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:
- Export existing data from the old MySQL version using
mysqldump
- Install the new MySQL version
- Load the dump file into the new MySQL version
- Run the
mysql_upgrade
utility
Here are the detailed steps:
- 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
- Shut down the MySQL server (refer to the Starting or Stopping MySQL 8 section).
- Install the new MySQL version (refer to the methods mentioned in the In-place upgrades section).
- Start the MySQL server (refer to the Starting or Stopping MySQL 8 section).
- 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';
- 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
- Run the
mysql_upgrade
utility:
shell> sudo mysql_upgrade -u root -p
- 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