This guide outlines the process of installing MySQL 8 by compiling its source code on a CentOS 7 system. Source compilation provides maximum control over the installation and optimization.

1. System Environment Overview

Before beginning, verify the operating system details. The instructions are tailored for CentOS 7.

[root@host ~]# cat /etc/redhat-release
CentOS Linux release 7.6.1810 (Core)
[root@host ~]# uname -r
5.2.14-1.el7.elrepo.x86_64

2. Initial Setup and Dependencies

2.1 Install Required Packages

Several development tools and libraries are necessary for compiling MySQL. Install them using yum:

yum -y install wget cmake gcc gcc-c++ ncurses ncurses-devel libaio-devel openssl openssl-devel

2.2 Download MySQL Source

Navigate to a suitable directory, such as /usr/local/src, and download the MySQL 8 source code package, which includes Boost libraries.

cd /usr/local/src/
wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-boost-8.0.17.tar.gz

2.3 Extract Source and Create System User/Directories

Extract the downloaded archive. Then, create a dedicated system user and group for MySQL, and establish the necessary installation and data directories.

tar -xf mysql-boost-8.0.17.tar.gz
cd mysql-8.0.17/

groupadd mysql
useradd -r -g mysql -s /sbin/nologin mysql

mkdir -p /usr/local/mysql       # MySQL installation directory
mkdir -p /data/mysql           # MySQL data directory

chown -R mysql:mysql /usr/local/mysql/
chown -R mysql:mysql /data/mysql/
chmod -R 750 /data/mysql/
chmod -R 755 /usr/local/mysql/

3. Tooclhain Upgrade

MySQL 8 compilation requires newer versions of CMake and GCC than typically available in default CentOS 7 repositories.

3.1 Upgrade CMake to Version 3.5+

First, remove any older CMake versions, then install a newer binary release.

yum -y remove cmake # If cmake is already installed

cd /usr/local/src/
wget https://github.com/Kitware/CMake/releases/download/v3.15.3/cmake-3.15.3-Linux-x86_64.tar.gz
tar -xf cmake-3.15.3-Linux-x86_64.tar.gz

mv cmake-3.15.3-Linux-x86_64 /usr/local/cmake

# Set environment variables for CMake
echo 'export CMAKE_PATH=/usr/local/cmake' >> /etc/profile
echo 'export PATH=$PATH:$CMAKE_PATH/bin' >> /etc/profile
source /etc/profile

cmake --version # Verify CMake version

3.2 Upgrade GCC

CentOS 7 typically ships with GCC 4.8.5, which is insufficient for compiling MySQL 8. Utilize Software Collections (SCL) to install a newer GCC version, such as GCC 9.

yum -y install centos-release-scl
yum -y install devtoolset-9-gcc devtoolset-9-gcc-c++ devtoolset-9-binutils

# Enable GCC 9 for the current session (and subsequent build commands)
scl enable devtoolset-9 bash

gcc --version # Verify GCC version (should be 9.x.x)

Note: The scl enable devtoolset-9 bash command provides a temporary environment. For persistent use, consider adding it to your .bashrc or a similar profile, or running all build commands within the scl enabled shell.

4. Compile and Install MySQL

Navigate back to the MySQL source directory and initiate the build process using CMake and make.

cd /usr/local/src/mysql-8.0.17/

cmake \
  -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \             # Installation path
  -DMYSQL_DATADIR=/data/mysql \                         # Data directory path
  -DSYSCONFDIR=/etc \                                   # Configuration file directory
  -DMYSQL_TCP_PORT=3306 \                               # Default server port
  -DWITH_BOOST=/usr/local/src/mysql-8.0.17/boost \      # Path to Boost libraries within source
  -DDEFAULT_CHARSET=utf8mb4 \                           # Default character set
  -DDEFAULT_COLLATION=utf8mb4_0900_ai_ci \              # Default collation
  -DENABLED_LOCAL_INFILE=ON \                           # Enable LOCAL INFILE capability
  -DWITH_INNODB_MEMCACHED=ON \                          # Compile InnoDB Memcached plugin
  -DWITH_INNOBASE_STORAGE_ENGINE=1 \                    # Enable InnoDB storage engine
  -DWITH_FEDERATED_STORAGE_ENGINE=1 \                   # Enable Federated storage engine
  -DWITH_BLACKHOLE_STORAGE_ENGINE=1 \                   # Enable Blackhole storage engine
  -DWITH_ARCHIVE_STORAGE_ENGINE=1 \                     # Enable Archive storage engine
  -DWITH_PERFSCHEMA_STORAGE_ENGINE=1                    # Enable Performance Schema storage engine

make -j $(nproc) # Compile using all available CPU cores
make install     # Install compiled binaries

5. MySQL Post-Installation Configuration

5.1 Initialize Data Directory

After compilation, initialize the MySQL data directory and set appropriate permissions.

cd /usr/local/mysql/bin/
./mysqld --initialize-insecure --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql

# Add MySQL binaries to system PATH for convenience
echo 'export PATH=/usr/local/mysql/bin:$PATH' >> /etc/profile
source /etc/profile

5.2 Configure Service Startup Script

Copy the provided MySQL server startup script and modify it to reflect the custom installation paths.

cp /usr/local/src/mysql-8.0.17/support-files/mysql.server /etc/init.d/mysqld
chmod +x /etc/init.d/mysqld

vim /etc/init.d/mysqld
# Edit basedir and datadir within the script:
# basedir=/usr/local/mysql
# datadir=/data/mysql

5.3 Create my.cnf Configuration File

Create or edit the primary MySQL configuration file /etc/my.cnf with essential settings. Ensure the datadir matches the path specified during initialization.

vim /etc/my.cnf

Add the following content to /etc/my.cnf:

[client]
port=3306
socket=/tmp/mysql.sock
default-character-set=utf8mb4

[mysqld]
server-id=1
port=3306
user=mysql
max_connections=200
socket=/tmp/mysql.sock
basedir=/usr/local/mysql
datadir=/data/mysql
pid-file=/data/mysql/mysql.pid
init-connect='SET NAMES utf8mb4'
character-set-server=utf8mb4
default-storage-engine=INNODB
log_error=/data/mysql/mysql-error.log
slow_query_log_file=/data/mysql/mysql-slow.log
long_query_time=1

[mysqldump]
quick
max_allowed_packet=16M

5.4 Start MySQL Service

Start the MySQL service using the init.d script.

/etc/init.d/mysqld start

5.5 Initial Login

Since --initialize-insecure was used, the root user has no password. Log in directly:

mysql -u root

6. Managing MySQL Security and Access

6.1 Resetting Root Password

To set or reset the MySQL root user password:

1. Stop MySQL:

   /etc/init.d/mysqld stop
   

2. Edit my.cnf to enable passwordless login:

   vim /etc/my.cnf
   

   Under the [mysqld] section, add:

   skip-grant-tables
   

3. Start MySQL:

   /etc/init.d/mysqld start
   

4. Log in to MySQL and update the password:

mysql -u root

USE mysql; UPDATE user SET authentication_string = '' WHERE user = 'root'; FLUSH PRIVILEGES; ALTER USER 'root'@'localhost' IDENTIFIED BY 'YourNewSecurePassword123!'; FLUSH PRIVILEGES; EXIT; ``` Replace 'YourNewSecurePassword123!' with a strong password.

5. Stop MySQL, remove skip-grant-tables from my.cnf, and restart:

   /etc/init.d/mysqld stop
   vim /etc/my.cnf # Remove skip-grant-tables line
   /etc/init.d/mysqld start
   

6. Verify login with the new password:

mysql -u root -p ```

6.2 Enabling Remote Access

By default, the root user might only be accessible from localhost. To allow remote connections, create a new user or modify the existing root user to connect from any host (%).

mysql -u root -p

CREATE USER 'remote_admin'@'%' IDENTIFIED BY 'AnotherSecurePassword!';
GRANT ALL PRIVILEGES ON *.* TO 'remote_admin'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;
EXIT;

Alternatively, you could modify the existing 'root'@'localhost' user to 'root'@'%' if prefered, but creating a dedicated remote user is often beetter practice.