With MySQL becoming popular, the demand for having fast installation process increased. Even with the source code available, which is the recipe for building software, this task is not easy to perform. It takes time to compile, and most of the time, it is necessary to install additional development libraries that expose a risk to production environments. Think in the source code as a chocolate cake recipe: you have the instructions on how to bake it, but sometimes you don’t want all the dirt in your kitchen, and you don’t have the time to cook it, so you go to a bakery shop to buy the chocolate cake. For MySQL, when you want it ready to use without having the effort to compile it, you can use the distribution packages.
Distribution packages for MySQL are available for the most diverse platforms. It covers Linux distributions, Windows, and macOS. These packages provide a flexible and fast way to start using MySQL. Another important thing, going back to the chocolate cake example, is that sometimes you want to customize it, let’s say that you want a white chocolate cake. For MySQL, this is called forks, and there are some different options available that you can choose. This chapter will cover these details.
In software engineering, a fork is when the source code is copied and starts its path with independent development and support from its source. The fork can follow the track close to its original version, as Percona distribution does, or drift away as MariaDB. Because the source code is open and free, new projects can fork the code without prior permission from its original creator.
The MySQL community version, also known as the upstream or vanilla version, is the open-source version distributed by Oracle. The community version drives the development of the InnoDB engine and new features, and it is the first to one to receive updates, new features, and bug fixes.
The Percona distribution for MySQL is a free, open-source, drop-in replacement for MySQL community version, founded by Peter Zaitsev. The software includes additional customized features for security, high availability, and backup, which are generally available only for MySQL enterprise versions. The development follows close to the MySQL community focusing on improving performance and the overall MySQL ecosystem.
Created by Michael Widenius (often called Monty), the MariaDB server is by far the one who most drifted away from the MySQL forks. In the past years, it has been developing its features and engines such as MariaDB ColumStore, and it was the first database to integrate Galera 4 clustering functionality. MariaDB Foundation distributes it.
From those mentioned so far, the MySQL Enterprise version is the only version that has a commercial license. The MySQL enterprise version contains all the community version functionalities plus a set of exclusive features for security, backup, and high availability. Oracle also distributes it.
First, it is necessary to choose the operating system. It is essential to verify if MySQL supports the preferred OS. The same policies are available for Percona Server and MariaDB.
The next step is to decide whether to install a Development or a General Availability (GA) release. Development releases have the newest features, but we do not recommend it for production because they are not stable. GA releases, also called production or stable releases, are meant for production use. We highly recommend using the most recent GA release because they have the latest stable bug fixes and performance improvements.
And the last step is to decide which distribution format to install for your operating system. For most use cases, a binary distribution is a choice that fits most. Binary distributions are available in native format for many platforms, such as rpm
packages for Linux or dmg
packages for macOS. The distributions are also available in generic formats such as zip
archives or compressed tar
files (tarballs). On Windows, you can use the MySQL Installer
to install a binary distribution. There is a critical item to observe, which is if the version is 32-bit or 64-bit. The rule of the thumb is picking the 64-bit version. Unless you are working with an ancient OS, the 32-bit version should not be selected. The critical difference is that 32-bit processors are capable of handling a limited amount of RAM (4GB or less), while 64-bit processors are capable of utilizing much more.
The Linux ecosystem is diverse and has many options like Red Hat(RHEL), Centos, Ubuntu, Debian, and others. This section will focus on the most used ones; otherwise, this book would be entirely about the installation process.
The installation process consists of four major steps. They are essential to install and set the minimum security requirements for the MySQL database. The following topics describe them:
Each distribution has its owner and, by consequence, its source. The following links point where you can download them:
It consists of the elementary steps to make MySQL functional and online, although they do not contemplate some required steps to make MySQL secure. For example, at this point, MySQL root user can connect without a password.
This section is about making sure the MySQL server is working correctly. It is essential to make sure that your server is secure, and the first step for this is executing the mysql_secure_installation
process.
There are those DBA’s which run benchmarks for each deployment to measure if the performance is suitable for their project using, for example, the sysbench
tool.
Now, let’s check the details a few installation processes for the most used platforms.
Both operating systems are supported by MySQL, although they are barely used on production systems. The reason for that is various, but mainly is the lack of tools to monitor, execute backups and administrate when its compared to Linux distributions. We decided to do not include installation steps for these versions since they are relatively simple and requires only to click next and finish buttons.
yum
To install MySQL 8 on Centos 7 using yum
, complete the following steps:
Configure the yum repository. Execute the following command to configure MySQL yum repository:
shell> rpm -Uvh https://repo.mysql.com/mysql80-community-release-el7.rpm
Install MySQL 8 Community Server. Because the MySQL yum repository has multiple repositories configuration for multiple MySQL versions, first you have to disable all repositories:
shell> sed -i 's/enabled=1/enabled=0/' /etc/yum.repos.d/mysql-community.repo
Next, you need to enable MySQL 8 repository and execute the following command to install MySQL 8:
shell> yum --enablerepo=mysql80-community install mysql-community-server
Start MySQL Service. Next, we start MySQL service:
shell> systemctl start mysqld
Discover the default password for the root user. When you install MySQL 8.0, MySQL creates a temporary password for the root user account. To identify the password of the root user account, execute the following command:
shell> grep "A temporary password" /var/log/mysqld.log
The command provides the output:
2020-05-31T15:04:12.256877Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: #z?hhCCyj2aj
MySQL Secure Installation. MySQL provides a shell script called mysql_secure_installation
. The mysql_secure_installation
is a shell script available on Unix systems, and enables you to improve the security of your server installation in the following ways:
You can set a password for root accounts.
You can remove root accounts that are accessible from outside the localhost.
You can remove anonymous-user accounts.
You can remove the test database, which by default can be accessed by anonymous users.
Execute the command mysql_secure_installation
to secure MySQL server:
shell> mysql_secure_installation
It will prompt you for the current password of the root account:
Enter password for user root:
Enter the temporary password provided before and press Enter. The following message will show:
The existing password for the user account root has expired. Please set a new password. New password: Re-enter new password:
You will need to enter the new password for the root‘s account twice. It will prompt some questions about whether to type yes (y) or no(n):
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y Disallow root login remotely? (Press y|Y for Yes, any other key for No) : y Remove test database and access to it? (Press y|Y for Yes, any other key for No) : y Reload privilege tables now? (Press y|Y for Yes, any other key for No) : y
Connect to MySQL. This step is optional, but we use it to verify if all the steps that were executed before went okay. Use this command to connect to MySQL server:
mysql -u root -p
It will prompt for the password of the root user. You type the password and press Enter:
Enter password:
It will show the MySQL command line:
mysql>
The latest MySQL 8.0 version is available to install from the default AppStream repository using the MySQL module that is enabled by default on the CentOS 8 and RHEL 8 systems.
Installing MySQL. Run the following command to install the mysql-server package and a number of its dependencies:
shell> sudo dnf install mysql-server
When prompted, press y and then ENTER to confirm that you want to proceed:
Output ... Transaction Summary ============================================================================ Install 50 Packages Upgrade 8 Packages Total download size: 50 M Is this ok [y/N]: y
Starting MySQL. At this point, MySQL is installed on your server but it isn’t yet operational. The package you just installed configures MySQL to run as a systemd service named mysqld.service. In order to use MySQL, you will need to start it with the systemctl command:
shell> sudo systemctl start mysqld.service
Check if the service is running. To check that the service is running correctly, run the following command:
sudo systemctl status mysqld
If MySQL was successfully started, the output will show that the MySQL service is active:
[root@ip-172-30-222-117 ~]# systemctl status mysqld mysqld.service - MySQL 8.0 database server Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled) Active: active (running) since Sun 2020-06-21 22:57:57 UTC; 6s ago Process: 15966 ExecStartPost=/usr/libexec/mysql-check-upgrade (code=exited, status=0/SUCCESS) Process: 15887 ExecStartPre=/usr/libexec/mysql-prepare-db-dir mysqld.service (code=exited, status=0/SUCCESS) Process: 15862 ExecStartPre=/usr/libexec/mysql-check-socket (code=exited, status=0/SUCCESS) Main PID: 15924 (mysqld) Status: "Server is operational" Tasks: 39 (limit: 23864) Memory: 373.7M CGroup: /system.slice/mysqld.service └─15924 /usr/libexec/mysqld --basedir=/usr Jun 21 22:57:57 ip-172-30-222-117.ec2.internal systemd[1]: Starting MySQL 8.0 database server... Jun 21 22:57:57 ip-172-30-222-117.ec2.internal systemd[1]: Started MySQL 8.0 database server.
Securing MySQL 8. As installing MySQL 8 on Centos 7, you need to execute the mysql_secure_installation
. The main difference is that there is not a temporary password for Centos 8, so when the script requests the root password just leave it blank and press enter.
Start MySQL 8 upon server start (optional). To set MySQL to start whenever the server boots up with the following command:
shell> sudo systemctl enable mysqld
If you ever want to disable this option from MySQL starting up at boot, you can do so by running:
shell> sudo systemctl disable mysqld
For Ubuntu, the process is slightly different since Ubuntu uses the apt
repository:
Configure the apt
repository. On Ubuntu 20.04 (Focal Fossa), you can install MySQL using the APT package repository. To install it, update the package index the server using the following command:
shell> sudo apt update
Then install the mysql-server package:
shell> sudo apt install mysql-server
The apt install
command will install MySQL, but will not prompt you to set a password or make any other configuration changes. Different from the Centos installation, Ubuntu initializes MySQL in the insecure mode, which is addressed next.
Configure MySQL. For fresh installations of MySQL, you’ll want to run the DBMS’s included security script. This script changes some of the less secure default options for things like remote root logins and sample users.
Run the security script with sudo:
shell> sudo mysql_secure_installation
The mysql_secure_installation
will take you through a series of prompts where you can make some changes to your MySQL installation’s security options, which are similar to the Centos version.
The first prompt will ask whether you’d like to set up the Validate Password Plugin, which can be used to test the strength of your MySQL password, and after that, you will be prompted to adjust the user authentication.
Install Percona Server 8 on Centos 7 using the following steps:
Install the Percona repository. You can install Percona yum
repository by running the following command as a root user or with sudo:
shell> yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpm
The installation created a new repository file in /etc/yum.repos.d/percona-original-release.repo
. Now, you need to enable Percona Server 8.0 repository using the next command:
sudo percona-release setup ps80
Install Percona Server 8. To install the server, it is necessary to execute the command:
shell> sudo yum install percona-server-server
And after this point, the steps are similar to the vanilla installation where you need to get the temporary password and execute the mysql_secure_installation
.
Install Percona Server 8 on Ubuntu 20.04 LTS using the following steps:
Install GnuPG, the GNU Privacy Guard:
shell> sudo apt-get install gnupg2
Fetch the repository packages from the Percona web page:
shell> wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb
Install the downloaded package with dpkg. To do that, run the following commands as root or with sudo:
shell> sudo dpkg -i percona-release_latest.$(lsb_release -sc)_all.deb
Once you install this package the Percona repositories should be added. You can check the repository setup in the /etc/apt/sources.list.d/percona-release.list
file.
Enable the repository:
shell> sudo percona-release setup ps80
After that you can install the server package:
shell> sudo apt-get install percona-server-server
And at this point, Percona Server will be running in the insecure mode. The same action items to install the vanilla distribution on Ubuntu are applied here.
Install MariaDB 10.5 on Centos 7 using the following steps:
Install the MariaDB repository. The following set of commands will download MariaDB repo and configure them for the next step.
shell> sudo yum install wget -y shell> wget https://downloads.mariadb.com/MariaDB/mariadb_repo_setup shell> chmod +x mariadb_repo_setup shell> sudo ./mariadb_repo_setup
Install MariaDB server. The next command will install the latest stable version of MariaDB and its dependencies:
shell> sudo yum install MariaDB-server
Starting MariaDB server:
shell> sudo systemctl start mariadb.service
At this point, MariaDB will be running in the insecure mode. The mysql_secure_installation
is necessary to help harden the MariaDB deployement.
Install MariaDB 10.5 on Ubuntu 20.04 LTS using the following steps:
Update apt
package manager index. Before you install MariaDB, update the package index on your server with apt:
shell> sudo apt update
Install MariaDB server. Install the package:
shell> sudo apt install mariadb-server
When installed from the default repositories, MariaDB will start running automatically. To test this, check its status:
shell> sudo systemctl status mariadb mariadb.service - MariaDB 10.5.3 database server Loaded: loaded (/lib/systemd/system/mariadb.service; enabled; vendor preset: enabled) Drop-In: /etc/systemd/system/mariadb.service.d └─migrated-from-my.cnf-settings.conf Active: active (running) since Sun 2020-06-21 21:47:12 UTC; 1min 32s ago Docs: man:mysqld(8) https://mariadb.com/kb/en/library/systemd/ Main PID: 7892 (mysqld) Status: "Taking your SQL requests now..." Tasks: 12 (limit: 2283) Memory: 82.3M CGroup: /system.slice/mariadb.service └─7892 /usr/sbin/mysqld Jun 21 21:47:13 ubuntu /etc/mysql/debian-start[7913]: mysql Jun 21 21:47:13 ubuntu /etc/mysql/debian-start[7913]: performance_schema Jun 21 21:47:13 ubuntu /etc/mysql/debian-start[7913]: Phase 6/7: Checking and upgrading tables Jun 21 21:47:13 ubuntu /etc/mysql/debian-start[7913]: Processing databases Jun 21 21:47:13 ubuntu /etc/mysql/debian-start[7913]: information_schema Jun 21 21:47:13 ubuntu /etc/mysql/debian-start[7913]: performance_schema Jun 21 21:47:13 ubuntu /etc/mysql/debian-start[7913]: Phase 7/7: Running 'FLUSH PRIVILEGES' Jun 21 21:47:13 ubuntu /etc/mysql/debian-start[7913]: OK Jun 21 21:47:13 ubuntu /etc/mysql/debian-start[7982]: Checking for insecure root accounts. Jun 21 21:47:13 ubuntu /etc/mysql/debian-start[7986]: Triggering myisam-recover for all MyISAM tables and aria-recover for all Aria tables
And at this point, it is necessary to run the mysql_secure_install
security script that came installed with MariaDB.
The information managed by MySQL and user data is stored under a directory called data directory. It is also very common that DBAs refers to this directory as datadir. You will see along this book that much of the content stored in this directory can be changed to another location. The reasons for it are various and the two most common ones are performance and disk usage.
The following list describes the structure using a default installation:
The files are as follows:
The redo log files are physically represented as ib_logfile0
and ib_logfile1
in the data directory. MySQL writes to the redo log files in a circular fashion. This means that the files does not grow beyond its configuration size. As any other RDBMS database that is ACID-compliant, the redo files are fundamental to provide data durability and the ability to recover from a crash scenario.
auto.cnf
The auto.cnf is a file that was introduced in the MySQL Server 5.6 version. It has only a single [auto]
section containing a single server_uuid
setting and value. This is a unique signature for the server and the replication layer uses it to communicate with different servers to replicate data.
The auto.cnf file is automatically generated when MySQL is installed and should not be changed.
*.pem
filesIn short, they provide a way of using encrypted connections for the communication between a client and the MySQL server. Encrypted connections are a fundamental part of the network security layer to avoid data being accessed by a element that is not be authorized to read it. Since MySQL 5.7 SSL is enabled by default and the certificates are auto-generated. However, it is possible to use certificates provided by the certificates authorities available in the market.
performance_schema
directoryThe MySQL Performance Schema is a feature for monitoring MySQL Server execution at a low level during runtime. When a metric can be monitored using Performance Schema, this metric is instrumented. For example, the number of users connected is instrumented in Performance Schema:
mysql
>
select
*
from
performance_schema
.
users
;
+----------+---------------------+-------------------+
|
USER
|
CURRENT_CONNECTIONS
|
TOTAL_CONNECTIONS
|
+----------+---------------------+-------------------+
|
NULL
|
1
|
3
|
|
root
|
0
|
1
|
|
rsandbox
|
2
|
3
|
|
msandbox
|
1
|
1
|
+----------+---------------------+-------------------+
4
rows
in
set
(
0
.
01
sec
)
Although it exists since MySQL 5.6, it was on MySQL 5.7 that gained a lot of improvements and became a fundamental part of the DBA tools to investigate and troubleshoot issues at MySQL level.
ibtmp1
fileWhen the application needs to create temporary tables or MySQL needs to use an on-disk internal temporary table they are created in a shared temporary tablespace. The default behavior is to create an auto-extending data file named ibtmp1 that is slightly larger than 12MB.
ibdata1
fileThe ibdata1
is probably the most famous file of MySQL ecosystem. This is because for MySQL 5.7 and older, the ibdata1
file holds data for the InnoDB data dictionary, the doublewrite buffer, the change buffer, and undo logs. It may also contain table and index data if tables are created in the system tablespace rather than file-per-table
or general tablespaces. Note that it is possible to have multiple ìbdata
files in the MySQL data directory.
For MySQL 8 some of these components were removed from ibdata1
and allocated outsite it. Actually, the remaining components are the change buffer table and index data if tables are created in the system tablespace rather than file-per-table
or general tablespaces.
mysql.sock
fileIt is a Unix socket file that the server uses for communication with local clients. This file only exists when MySQL is running and removing it or creating manually may lead to problems.
mysql
directoryThe mysql
directory corresponds to the mysql system schema, which contains information required by the MySQL server as it runs. For example, it contains information about users and its privileges, time zone tables and replication.
MySQL 8 brought a few changes in the core of data directory structure. Some of these changes are related to the implementation of the new data dictionary, others are intended to bring better database management. The following list describes the new files and changes:
undo
tablespace filesUndo files are used to undo the transactions that needs to perform a rollback and to ensure isolated transactions whenever it needs to perform a consistent read.
From MySQL 8.0, the undo log files were separated from the system tablespace (ibdata1
) and placed on the data directory.
dblwr
files (Introduced 8.0.20 version)The doublewrite buffer is responsible writes pages flushed from the buffer pool before writing the pages to the datafiles. The doublewrite file names have the following format: #ib_page_size_file_number.dblwr.
Example:
#ib_16384_0.dblwr #ib_16384_1.dblwr
mysql.ibd
file (Introduced 8.0 version)Dictionary tables and system tables store data and meta data needed by the MySQL server.
The mysql
binary is a simple SQL shell with input line editing capabilities. Your use is very easy and to invoke it is simple as typing:
shell> mysql
We can extend its functionality executing queries in it:
shell> mysql -uroot -pseKret -e "SHOW ENGINE INNODB STATUSG"
Or more advanced pipelined commands like the following one, where we are extracting a dump from one database, sending it across the network and restoring into another MySQL server:
shell> mysql -e "show master statusG" && nice -5 mysqldump --all-databases --single-transaction -R --master-data=2 --flush-logs --log-error=/tmp/donor.log --verbose=TRUE | ssh [email protected] mysql 1> /tmp/receiver.log 2>&1
MySQL 8 brought the MySQL shell, which is a way more powerful tool than its predecessor. MySQL Shell supports Javascript, Python, or SQL languages, providing development and administration for the MySQL Server.
For example, it supports the deployement of sandboxes through the command line:
MySQL
JS
>
shell
.
connect
(
'root@localhost:46009'
);
Creating
a
session
to
'root@localhost:46009'
Fetching
schema
names
for
autocompletion
...
Press
^
C
to
stop
.
Your
MySQL
connection
id
is
15
Server
version
:
8
.
0
.
19
MySQL
Community
Server
-
GPL
No
default
schema
selected
;
type
use
<
schema
>
to
set
one
.
<
ClassicSession
:
root
@
localhost
:
46009
>
MySQL
localhost
:
46009
ssl
JS
>
dba
.
deploySandboxInstance
(
3310
);
A
new
MySQL
sandbox
instance
will
be
created
on
this
host
in
/
home
/
vinicius
.
grippa
/
mysql
-
sandboxes
/
3310
Warning
:
Sandbox
instances
are
only
suitable
for
deploying
and
running
on
your
local
machine
for
testing
purposes
and
are
not
accessible
from
external
networks
.
Please
enter
a
MySQL
root
password
for
the
new
instance
:
******
Deploying
new
MySQL
instance
...
Instance
localhost
:
3310
successfully
deployed
and
started
.
Use
shell
.
connect
(
'root@localhost:3310'
)
to
connect
to
the
instance
.
Do not worry about the examples provided previously. Along the chapters we will explore these functionalities in-depth.
With the advent of virtualization and its popularization with cloud services, many platforms emerged, including Docker. Born in 2013, Docker is a solution that provides a portable and flexible way to deploy software providing resource isolation through the use of Linux features like cgroups and Kernel namespaces.
One orchestrator tool that widely uses Docker containers in Kubernetes. For those who are starting with containerization and Kubernetes, it is a common misunderstanding to think that Kubernetes is the container platform itself or that it is embedded. So for clarification, Kubernetes is only responsible for managing the containers.
One advantage of using Docker is that after the docker service is running, all the commands are the same in all Operating Systems. The commands being the same means that the learning curve for using Docker is faster when we compare learning different Linux versions such as Centos and Ubuntu, for example.
The process for installing Docker service (https://hub.docker.com/search?q=&type=edition&offering=community) is straightforward for most of the platforms. For Windows and macOS operating systems, it is just a binary installation, and after that, the service is up and running. For Linux server-based operating systems without a graphic interface, the process requires a few command lines.
CentOS is a free fork of RHEL distribution. It’s generally praised for stability and long support cycle. The price to that is that the packages used are usually old. At the time of the writing, the version of Docker provided by regular CentOS repositories is 1.13.1, while the upstream stable version is 19.03.8. For this book, there is no difference in which version is used.
To install Docker from standard repo, there’s just one step.
Install Docker
shell> sudo yum install docker
To install Docker from an upstream repo, there’s just one step, the installation is a bit more involved. You may always find fresh installation guide on the docker.com site.
Install yum-utils
to get yum-config-manager
shell> sudo yum install yum-utils
Use yum-config-manager
to add docker-ce
repository
shell> sudo yum-config-manager --add-repo https://download.docker.com/linux/centos/docker-ce.repo
Install necessary packages
shell> sudo yum install docker-ce docker-ce-cli containerd.io
The process for installing Docker is the following:
Use the apt command to install the docker.io package:
shell> sudo apt install docker.io
Start docker and enable it to start after the system reboot
shell> sudo systemctl enable --now docker
Check docker version
shell> docker --version
Check if the service is running
shell> systemctl status docker
Once Docker is intalled, the next step is deploying the MySQL docker container. To install the latest MySQL version with Docker:
shell> docker run --name mysql-latest -p 3306:3306 -p 33060:33060 -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD='strongpassword' -d mysql/mysql-server:latest
It will launch the latest version of MySQL instance, which can be remotely accessible from anywhere with specified root password. Installing with Docker means that you do not get any tools, utilities, or libraries available on your host directly, so you either install these separately, access created instance from a remote host, or use command lines shipped with docker image.
Next, to start the MySQL Command Line Client with Docker Run:
shell> docker exec -it mysql-latest mysql -uroot -pstrongpassword
And below are a few commands to manage your container:
When you want to stop the MySQL Server Docker Container run:
shell> docker stop mysql-latest
If you want to restart a stopped MySQL Docker container, you should not try to use docker run to start it again. Instead, you should use:
shell> docker start mysql-latest
If you need to investigate an issue, for example, if the container is not starting, you can access its logs using this command:
shell> docker logs mysql-latest
If you want to re-create a fresh docker container from scratch you can run:
shell> docker stop mysql-latest shell> docker rm mysql-latest
Followed by the docker run command described previously.
It is possible to customize MySQL parametrization passing Command Line options to MySQL Server in Docker container. If you want to pass some command line options to MySQL Server, you can do it this way:
shell> docker run --name mysql-latest -p 3306:3306 -p 33060:33060 -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD='strongpassword' -d mysql/mysql-server:latest --innodb_buffer_pool_size=256M --innodb_flush_method=O_DIRECT
If you just want to run a specific MySQL version in Docker container, it is easy, you can pick the version you want with Docker Image Tag and change the Name to be different in order to avoid name conflict:
shell> docker run --name mysql-8.0.17 -p 3306:3306 -p 33060:33060 -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD='strongpassword' -d mysql/mysql-server:8.0.17
As demonstrated for deploying MySQL in the previous section, MariaDB and Percona Server follows the same rules. The main difference is that they use different Docker images.
For MariaDB:
shell> docker run -d --name mariadb.latest -e MYSQL_ROOT_PASSWORD='sekret' -d mariadb:latest
For Percona Server:
shell> docker run --name ps-latest -p 3306:3306 -p 33060:33060 -e MYSQL_ROOT_HOST='%' -e MYSQL_ROOT_PASSWORD='sekret' -d percona/percona-server:latest --innodb_buffer_pool_size=256M --innodb_flush_method=O_DIRECT
In software development, a sandbox is a testing environment that isolates code changes and allows experimentation and testing before deploying it to production. In the database world, it is primarily used for testing new software versions, performance tests, and bug analysis.
At the year of 2018, Giuseppe Maxia introduced to the world DBdeployer. DBdeployer is a tool that provides an easy and fast way to deploy MySQL and its forks in the most diverse topologies such as master x slave, master x master, Galera cluster, and group replication.
Installing the tool is relatively simple since it’s developed in Go language, and standalone executables are provided. You can get the latest version here.
shell> wget https://github.com/datacharmer/dbdeployer/releases/download/v1.51.0/dbdeployer-1.51.0.linux.tar.gz shell> tar xzf dbdeployer-1.51.0.linux.tar.gz shell> mv dbdeployer-1.51.0.linux /usr/local/bin/dbdeployer
If you have your /usr/local/bin/
directory in the $PATH
variable, you should now be able to run dbdeployer commands.
shell> dbdeployer --version dbdeployer version 1.51.0
The first step to use dbdeployer is getting the binary you want to run, and then unpacking it into the binaries directory.
shell> wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz shell> dbdeployer --sandbox-binary=/opt/mysql/ unpack mysql-8.0.11-linux-glibc2.12-x86_64.tar.gz
The unpack command will extract and move the files to the appropriate directory. This case is under /opt/mysql/ as overridden with the --sandbox-binary argument so that you can use them with the deploy command.
You can use the following command to create a new standalone MySQL sandbox with the newly extracted binary.
shell> dbdeployer --sandbox-binary=/opt/mysql/ deploy single 8.0.11
Next, to connect to it:
shell> cd sandboxes/msb_8_0_11/ shell> ./use
For setting a replication, it is only necessary to specify on the command line:
shell> dbdeployer --sandbox-binary=/opt/maria/ deploy replication 10.2.16
Another example of topologies that DBdeployer is able to configure:
# Group Replication shell> dbdeployer deploy --topology=group replication --sandbox-binary=/opt/mysql/ 8.0.19 --base-port=49007
# Replication using GTID shell> dbdeployer deploy replication --sandbox-binary=/opt/mysql/ 5.7.29 --gtid
# Percona XtraDB Cluster shell> dbdeployer deploy --topology=pxc replication --sandbox-binary=/opt/percona_xtradb_cluster/ 5.7.26 --base-port=45007 -c log-slave-updates
Finally, it is also possible to deploy multiple standalone versions at the same time:
# Create 5 standalone instances shell> dbdeployer deploy multiple --sandbox-binary=/opt/mysql/ 5.7.29 -n 5
The previous examples are just a small sample of DBdeployer capabilities. The documentation is complete on GitHub. Another option to understand the universe of possibilities is using --help
in the command line:
shell> dbdeployer --help dbdeployer makes MySQL server installation an easy task. Runs single, multiple, and replicated sandboxes. Usage: dbdeployer [command] Available Commands: admin sandbox management tasks cookbook Shows dbdeployer samples defaults tasks related to dbdeployer defaults delete delete an installed sandbox delete-binaries delete an expanded tarball deploy deploy sandboxes downloads Manages remote tarballs export Exports the command structure in JSON format global Runs a given command in every sandbox help Help about any command import imports one or more MySQL servers into a sandbox info Shows information about dbdeployer environment samples sandboxes List installed sandboxes unpack unpack a tarball into the binary directory update Gets dbdeployer newest version usage Shows usage of installed sandboxes versions List available versions Flags: --config string configuration file (default "/home/vinicius.grippa/.dbdeployer/config.json") -h, --help help for dbdeployer --sandbox-binary string Binary repository (default "/home/vinicius.grippa/opt/mysql") --sandbox-home string Sandbox deployment directory (default "/home/vinicius.grippa/sandboxes") --shell-path string Which shell to use for generated scripts (default "/usr/bin/bash") --skip-library-check Skip check for needed libraries (may cause nasty errors) --version version for dbdeployer Use "dbdeployer [command] --help" for more information about a command.
If the first place in the questions ranking is Replication, the second place is for sure for upgrading a MySQL instance. If this procedure is not well tested before doing it in production, the chances of having a problem are high. To start the upgrade planning, first you need to choose between two strategies. These are the recommended according to the documentation:
Involves shutting down MySQL, replacing the old MySQL binaries or packages with the new ones, restarting MySQL on the existing data directory, and running mysql_upgrade
(depending of the version).
Involves exporting the data in SQL format from the old MySQL version using a backup or export utility such as mysqldump or mysqlpump, installing the new MySQL version, and applying the SQL data to the new MySQL version.
It is important to have a rollback strategy established if something goes wrong and they will be different for each case mentioned previously. Moreover, the database size and the topology that is present (if you have slaves for example) will influence this decision.
Now, upgrading straightforward jumping major versions are not recommended. For example, upgrading from MySQL 5.6 to MySQL 8 involve some risks and is not officially supported. Even between two consecutives major versions, there are some notes to highlight to take in consideration(especially the last one):
Upgrade from MySQL 5.7 to 8.0 is supported. However, upgrade is only supported between General Availability (GA) releases. For MySQL 8.0, it is required that you upgrade from a MySQL 5.7 GA release (5.7.9 or higher). Upgrades from non-GA releases of MySQL 5.7 are not supported.
Upgrading to the latest release is recommended before upgrading to the next version. For example, upgrade to the latest MySQL 5.7 release before upgrading to MySQL 8.0.
Upgrade that skips versions is not supported. For example, upgrading directly from MySQL 5.6 to 8.0 is not supported.
Let’s see an example of upgrading from MySQL 5.7 to MySQL 8 using the in-place method.
Stop MySQL service
shell> systemctl stop mysqld
+ . Removing the old binaries
shell> yum erase mysql-community* -y
+ . Installing the new binaries
+
This step will follow the same steps for the installation process (see [Link to Come]). For example, to the MySQL 8 community version on CentOS using yum
:
+
shell> sudo yum-config-manager --enable mysql80-community
+ And installing the binaries:
+
shell> yum install mysql-community-server -y
+
As of MySQL 8.0.16, the mysql_upgrade
step is not required, as the server performs all tasks previously handled by mysql_upgrade.
+ * Start the service
+
shell> systemctl start mysqld
+ We can observe the logs that MySQL upgraded the data dictionary and is running MySQL 8.0.21:
+
shell> tail -200f /var/log/mysqld.log 2020-08-09T21:20:07.922604Z 1 [System] [MY-011012] [Server] Starting upgrade of data directory. 2020-08-09T21:20:07.922706Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started. 2020-08-09T21:20:08.565475Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended. 2020-08-09T21:20:09.087223Z 0 [System] [MY-011323] [Server] X Plugin ready for connections. Bind-address: '::' port: 33060, socket: /var/run/mysqld/mysqlx.sock 2020-08-09T21:20:09.087495Z 0 [Warning] [MY-013129] [Server] A message intended for a client cannot be sent there as no client-session is attached. Therefore, we're sending the information to the error-log instead: MY-001287 - 'validate password plugin' is deprecated and will be removed in a future release. Please use validate_password component instead 2020-08-09T21:20:10.356938Z 2 [System] [MY-011003] [Server] Finished populating Data Dictionary tables with data. 2020-08-09T21:20:11.734091Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80021' started. 2020-08-09T21:20:17.342682Z 5 [System] [MY-013381] [Server] Server upgrade from '50700' to '80021' completed. 2020-08-09T21:20:17.438735Z 0 [Warning] [MY-010068] [Server] CA certificate ca.pem is self signed. 2020-08-09T21:20:17.439047Z 0 [System] [MY-013602] [Server] Channel mysql_main configured to support TLS. Encrypted connections are now supported for this channel. 2020-08-09T21:20:17.463685Z 0 [System] [MY-010931] [Server] /usr/sbin/mysqld: ready for connections. Version: '8.0.21' socket: '/var/lib/mysql/mysql.sock' port: 3306 MySQL Community Server - GPL.
+ And finally, we usually receive the following question:
+
Is it safe to upgrade to the latest major release?
This is a complicated question because any answer that we provide there are pros and cons. As any new product in the industry, early adopters tends to benefit from the new features but they are testers as well and they can potentially hit and discover new bugs. When MySQL 8 was released, my recommendation was to wait three minor releases before considering moving. In this way, you can still enjoy the benefits of a new major version without having to wait too much. The golden rule of this book is to test everything in advance before executing the next step. If you understand this along this book, we will consider the mission accomplished.