Data represents stored information in various forms such as images, text, spreadsheets, attachments, videos, application packages, user account records, and order details. A Database Management System (DBMS) organizes, stores, and retrieves this data efficiently. The major categories include:

• RDBMS (Relational)
• NoSQL (Non-relational)
• NewSQL (Distributed SQL)

Commonly used products in each category:

• RDBMS: Oracle Database, MySQL, Microsoft SQL Server, PostgreSQL
• NoSQL: Redis, MongoDB, Elasticsearch
• NewSQL: TiDB (PingCAP), Google Spanner, OceanBase (Alibaba), PolarDB (Alibaba Cloud)

MySQL Distributions and Versions

Several MySQL-based distributions exist:

• Oracle MySQL
• MariaDB
• Percona Server for MySQL
• Cloud-managed services: Amazon RDS for MySQL, TencentDB, etc.

To production deployments, always choose a General Availability (GA) release. Avoid Release Candidate or beta versions. Recommended stable GA versions include:

• 5.6 series: 5.6.38, 5.6.40 (GA dates: 5.6.38 – September 2017; 5.6.40 – February 2018)
• 5.7 series: 5.7.20, 5.7.24 (5.7.24 GA date: October 2018)

When introducing a new environment, prefer a 5.7 GA version that has been in production for 6–12 months (typically an even-numbered release).

Deploying MySQL 5.7.26 (Binary Distribution)

Environment Setup

Installation Steps

1. Prepare directories

   mkdir -p /opt/mysql
   mkdir -p /data/mysql_data
   

2. Create the MySQL system user

   useradd -s /sbin/nologin -M mysql
   id mysql
   

3. Extract the binary package

   cd /opt
   tar xf mysql-5.7.26-linux-glibc2.12-x86_64.tar.gz
   mv mysql-5.7.26-linux-glibc2.12-x86_64 mysql57
   

4. Remove conflicting MariaDB libraries and set environment variables

   rpm -qa | grep mariadb
   yum remove -y mariadb-libs
   echo 'export PATH=/opt/mysql57/bin:$PATH' >> /etc/profile
   source /etc/profile
   mysql -V
   

5. Initialize the data directory

   yum install -y libaio-devel
   mysqld --initialize-insecure --user=mysql --basedir=/opt/mysql57 --datadir=/data/mysql_data
   

   The --initialize-insecure flag creates a root user without a password. (If --initialize is used, a temporary passsword is generated and must be retrieved from the error log.)

6. Create the configuration file /etc/my.cnf

   [mysqld]
   user=mysql
   basedir=/opt/mysql57
   datadir=/data/mysql_data
   socket=/var/run/mysql/mysql.sock
   
   [mysql]
   socket=/var/run/mysql/mysql.sock
   prompt=mysql [\d]>
   

7. Set ownership and install the init script

   chown -R mysql:mysql /data/mysql_data /opt/mysql57
   cp /opt/mysql57/support-files/mysql.server /etc/init.d/mysqld
   /etc/init.d/mysqld start
   

8. Adopt systemd for service management Create /etc/systemd/system/mysqld.service:

   [Unit]
   Description=MySQL Server
   Documentation=man:mysqld(8)
   After=network.target syslog.target
   
   [Install]
   WantedBy=multi-user.target
   
   [Service]
   User=mysql
   Group=mysql
   ExecStart=/opt/mysql57/bin/mysqld --defaults-file=/etc/my.cnf
   LimitNOFILE=5000
   

   Then stop the SysV-init script and start with systemd:

   /etc/init.d/mysqld stop
   systemctl start mysqld.service
   

9. Access the MySQL shell

   mysql -u root
   

   Inside the client:

   SELECT user, host FROM mysql.user;
   

Basic Post-Installation Tasks

Set the root password (first time):

mysqladmin -u root password 'YourNewPass'

Log in and insppect authentication details:

USE mysql;
SELECT user, host, authentication_string FROM mysql.user;

In-Place Upgrade from MySQL 5.7 to 8.0

1. Snapshot or backup the current installation (skip on test systems)
2. Install the new MySQL 8.0 binaries to a separate location

   cd /opt
   tar xf mysql-8.0.16-linux-glibc2.12-x86_64.tar.xz
   mv mysql-8.0.16-linux-glibc2.12-x86_64 mysql80
   

3. Stop the running 5.7 instance

   systemctl stop mysqld.service
   

4. Update environment variables and configuration Edit /etc/profile:

   export PATH=/opt/mysql80/bin:$PATH
   

   Then source the file. Update /etc/my.cnf:

   [mysqld]
   user=mysql
   basedir=/opt/mysql80
   datadir=/data/mysql_data
   socket=/var/run/mysql/mysql.sock
   

   Also adjust the systemd unit file (ExecStart points to the new binary):

   ExecStart=/opt/mysql80/bin/mysqld --defaults-file=/etc/my.cnf
   

5. Start MySQL 8.0

   systemctl daemon-reload
   systemctl start mysqld.service
   

6. Run the upgrade utility (for pre-8.0 data)

   mysql_upgrade -u root -p
   

7. Verify

   mysql -V
   mysql -u root -p
   

   Once the upgrade is successful, the mysql_upgrade_info file inside the data directory will contain the new version number.