MySQL community

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.

Percona Server for MySQL

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.

MariaDB Server

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.

MySQL Enterprise

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.

Installing MySQL 8 on Centos 7 using yum

To install MySQL 8 on Centos 7 using yum, complete the following steps:

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

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

3. Start MySQL Service. Next, we start MySQL service:

   shell> systemctl start mysqld

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

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

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

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

Installing MySQL 8 on Centos 8

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.

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

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

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

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

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

shell> sudo systemctl disable mysqld

Installing MySQL 8 on Ubuntu 20.04 LTS (Focal Fossa)

For Ubuntu, the process is slightly different since Ubuntu uses the apt repository:

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

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

Installing Percona Server 8 on Centos 7

Install Percona Server 8 on Centos 7 using the following steps:

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

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

Installing Percona Server 8 on Ubuntu 20.04 LTS (Focal Fossa)

Install Percona Server 8 on Ubuntu 20.04 LTS using the following steps:

1. Install GnuPG, the GNU Privacy Guard:

   shell> sudo apt-get install gnupg2

2. Fetch the repository packages from the Percona web page:

   shell> wget https://repo.percona.com/apt/percona-release_latest.$(lsb_release -sc)_all.deb

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

4. Enable the repository:

   shell> sudo percona-release setup ps80

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

Installing MariaDB 10.5 on Centos 7

Install MariaDB 10.5 on Centos 7 using the following steps:

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

2. Install MariaDB server. The next command will install the latest stable version of MariaDB and its dependencies:

   shell> sudo yum install MariaDB-server

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

Installing MariaDB 10.5 on Ubuntu 20.04 LTS (Focal Fossa)

Install MariaDB 10.5 on Ubuntu 20.04 LTS using the following steps:

1. Update apt package manager index. Before you install MariaDB, update the package index on your server with apt:

   shell> sudo apt update

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

MySQL 5.7 default files

The files are as follows:

The REDO log files

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.

The 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 *.pem files

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

The performance_schema directory

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

The ibtmp1 file

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

The ibdata1 file

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

The mysql.sock file

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

The mysql directory

The 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 default files

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:

The undo tablespace files

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

The 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

The mysql.ibd file (Introduced 8.0 version)

Dictionary tables and system tables store data and meta data needed by the MySQL server.

Installing Docker

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.

shell> sudo yum install 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

shell> docker exec -it mysql-latest mysql -uroot -pstrongpassword

shell> docker stop mysql-latest

shell> docker start mysql-latest

shell> docker logs mysql-latest

shell> docker stop mysql-latest
shell> docker rm mysql-latest

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

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

shell> docker run -d 
--name mariadb.latest 
-e MYSQL_ROOT_PASSWORD='sekret' 
-d mariadb:latest

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

Using Sandboxes

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.

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

shell> dbdeployer --version
dbdeployer version 1.51.0

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

shell> dbdeployer --sandbox-binary=/opt/mysql/ deploy single 8.0.11

shell> cd sandboxes/msb_8_0_11/
shell> ./use

shell> dbdeployer --sandbox-binary=/opt/maria/ deploy replication 10.2.16

# 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

# Create 5 standalone instances
shell> dbdeployer deploy multiple --sandbox-binary=/opt/mysql/ 5.7.29 -n 5

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.