Master MySQL 8.0 Installation: YUM, Binary, Multi‑Instance Setup & Common Pitfalls

Mysql8.0 Installation (YUM method)

Remove any existing MySQL packages:

# for i in $(rpm -qa|grep mysql); do rpm -e $i --nodeps; done
# rm -rf /var/lib/mysql && rm -rf /etc/my.cnf

Install the MySQL 8.0 YUM repository:

# yum localinstall https://repo.mysql.com//mysql80-community-release-el7-1.noarch.rpm

Install MySQL server:

# yum install mysql-community-server
# systemctl start mysqld
# systemctl enable mysqld

Reset the temporary root password after first login:

# grep 'temporary password' /var/log/mysqld.log
# mysql -p<temporary_password>
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
# ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';

Mysql8.0 Installation (Binary method)

Remove any existing MySQL packages (same as YUM method).

Install required packages:

# yum -y install libaio
# yum -y install net-tools

Download and extract MySQL binary tarball, create mysql user/group, and set PATH:

# groupadd mysql
# useradd -g mysql mysql
# cd /usr/local/src/
# tar -zvxf mysql8.0.12_bin_centos7.tar.gz
# mv mysql /usr/local/
# chown -R mysql.mysql /usr/local/mysql
# echo "export PATH=/usr/local/mysql/bin:/usr/local/mysql/lib:$PATH" >> /etc/profile
# source /etc/profile

Create data directories and set ownership:

# mkdir -p /data/mysql/{data,log,binlog,conf,tmp}
# chown -R mysql.mysql /data/mysql

Configure my.cnf (example excerpt):

[mysqld]
lower_case_table_names=1
user=mysql
port=3306
default_authentication_plugin=mysql_native_password
... (innodb, log, buffer settings) ...
log_error=/data/mysql/log/mysqld.err
log_bin=/data/mysql/binlog/binlog
slow_query_log=1
slow_query_log_file=/data/mysql/log/slow.log
long_query_time=10

Initialize the data directory (insecure):

# mysqld --defaults-file=/data/mysql/conf/my.cnf --initialize-insecure --user=mysql

Start MySQL safely:

# mysqld_safe --defaults-file=/data/mysql/conf/my.cnf &

Login and reset root password as shown in the YUM method.

MySQL Single‑Machine Multi‑Instance Installation

Create separate data directories for each instance (ports 3307 and 3308):

# mkdir -p /data/mysql3307/{data,log,binlog,conf,tmp}
# mkdir -p /data/mysql3308/{data,log,binlog,conf,tmp}
# chown -R mysql.mysql /data/mysql3307
# chown -R mysql.mysql /data/mysql3308

Copy and adjust the configuration file for each instance:

# cp -r /data/mysql/conf/my.cnf /data/mysql3307/conf/
# cp -r /data/mysql/conf/my.cnf /data/mysql3308/conf/
# sed -i 's#/data/mysql/#/data/mysql3307/#g' /data/mysql3307/conf/my.cnf
# sed -i 's#/data/mysql/#/data/mysql3308/#g' /data/mysql3308/conf/my.cnf
# sed -i 's/3306/3307/g' /data/mysql3307/conf/my.cnf
# sed -i 's/3306/3308/g' /data/mysql3308/conf/my.cnf

Initialize each instance:

# mysqld --defaults-file=/data/mysql3307/conf/my.cnf --initialize-insecure --user=mysql
# mysqld --defaults-file=/data/mysql3308/conf/my.cnf --initialize-insecure --user=mysql

Start the instances:

# mysqld_safe --defaults-file=/data/mysql3307/conf/my.cnf &
# mysqld_safe --defaults-file=/data/mysql3308/conf/my.cnf &

Login to each instance via its socket and reset the root password:

# mysql -S /data/mysql3307/tmp/mysqld.sock
mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '123456';
mysql> FLUSH PRIVILEGES;
# mysql -S /data/mysql3308/tmp/mysqld.sock
... (same steps) ...

Common MySQL 8.0 Pitfalls

Creating users and granting privileges is stricter in MySQL 8.0; you must create the user with a host specification before granting.

CREATE USER 'kevin'@'%' IDENTIFIED BY '123456';
GRANT ALL PRIVILEGES ON *.* TO 'kevin'@'%' WITH GRANT OPTION;

Root cannot log in remotely by default. Update the host field to allow remote access:

UPDATE mysql.user SET host='%' WHERE user='root';
FLUSH PRIVILEGES;

Authentication plugin change: MySQL 8.0 uses caching_sha2_password by default, which may cause client errors (e.g., Navicat, SQLyog). Switch back to mysql_native_password:

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY '123456';
FLUSH PRIVILEGES;

Default character set is utf8mb4. To change or verify:

SHOW VARIABLES LIKE 'character_set_%';
# To enforce utf8mb4 in my.cnf:
[client]
default-character-set=utf8mb4
[mysql]
default-character-set=utf8mb4
[mysqld]
character-set-server=utf8mb4
collation-server=utf8mb4_unicode_ci
init_connect='SET NAMES utf8mb4'

Useful configuration queries (max connections, slow query log, thread status, etc.)

SHOW GLOBAL VARIABLES LIKE 'max_conn%';
SHOW GLOBAL STATUS LIKE 'Threads%';
SHOW VARIABLES LIKE 'slow_query%';
SHOW PROCESSLIST;