RHEL Install MySQL 5.6, 5.7, 8.0

由 datahunter 在四, 18/08/2016 - 17:28 發表

最後更新: 2023-11-14

介紹

安裝 mysql5.6

rpm -i mysql57-community-release-el7-9.noarch.rpm

yum -y install yum-utils

yum-config-manager --disable mysql57-community

yum-config-manager --enable mysql56-community

yum repolist enabled | grep ^mysql

mysql-connectors-community/x86_64 MySQL Connectors Community                 108
mysql-tools-community/x86_64      MySQL Tools Community                       90
mysql56-community/x86_64          MySQL 5.6 Community Server                 463

yum install mysql-community-server

yum install mysql-community-client # 提供 mysql

yum install mysql-utilities

systemctl start mysqld

systemctl enable mysqld

安裝 mysql5.7

# 安裝 repo 包

http://dev.mysql.com/downloads/repo/yum/

# 看看有無安 mariadb

rpm -qa | grep ^mariadb

systemctl stop mariadb

# Backup Data & Config

mv /var/lib/mysql /var/lib/mysql.bak

cd /etc/

mkdir _bak

mv my.cnf* _bak/

# 安裝

yum install mysql-community-client mysql-community-server

改 Password

# 找出臨時 password

* 要 service mysqld start 才有 temporary password

grep 'temporary password' /var/log/mysqld.log

# 更改 password

Run mysql_secure_installation

Upgrade from Mariadb to Mysql5.7

Case: mariadb 5.5(Centos 7 Default) to mysql 5.7

# Backup

service mariadb stop

mkdir /var/lib/_bak

cp -a /var/lib/mysql /var/lib/_bak

# Prepare

mkdir /var/log/mysqld

chown mysql. /var/log/mysqld

# Install

yum install mysql-community-server

# Config

/etc/my.cnf

Remove [mysqld_safe] section

在 [mysqld] 加入/修改

[mysqld]
...
log-error=/var/log/mysqld/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

/etc/my.cnf.d/server.cnf

#key_buffer=128M
#table_cache=1024
key_buffer_size=256M
table_open_cache=1024

# for debug
slow_query_log = 1
long_query_time = 2
slow_query_log_file = /var/log/mysqld/slow-queries.log

# Upgrade tables

service mysqld start

mysql_upgrade -p

# Test restart & auto-start

service mysqld restart

systemctl enable mysqld

新功能

5.7

ARCHIVE Engine

http://dev.mysql.com/doc/refman/5.7/en/archive-storage-engine.html

When you create an ARCHIVE table, the server creates a table format file in the database directory.

The file begins with the table name and has an .frm extension.

The storage engine creates other files, all having names beginning with the table name.

The data file has an extension of .ARZ.

An .ARN file may appear during optimization operations.

The ARCHIVE engine supports INSERT, REPLACE, and SELECT, but not DELETE or UPDATE.

It does support ORDER BY operations, BLOB columns, and basically all but spatial data types

MySQL 8

php7 connect mysql8

mysqli_real_connect(): The server requested authentication method unknown to the client [caching_sha2_password]

Fix

[Step 1] In mysql shell

ALTER USER 'mysqlUsername'@'localhost' IDENTIFIED WITH

mysql_native_password BY 'mysqlUsernamePassword';

[Step 2] my.cnf

default-authentication-plugin=mysql_native_password

Rocky8 Install MySQL5.7

安裝

到 http://repo.mysql.com/yum/mysql-5.7-community/el/7/x86_64/ 下載

mysql-community-client
mysql-community-common
mysql-community-libs
mysql-community-server
mysql-community-libs-compat # 非必要, 安它要有 (libcrypto.so.10, libssl.so.10)

dnf install perl ncurses-compat-libs numactl libaio net-tools

rpm -Uvh mysql-*.rpm

安裝時出現

[/usr/lib/tmpfiles.d/mysql.conf:23] Line references path below legacy directory /var/run/,
  updating /var/run/mysqld → /run/mysqld; please update the tmpfiles.d/ drop-in file accordingly.

* Due to the systemd change, any package using /var/run will need to be updated to attempt to remove the logs.

To determine which package provides the /var/run/<FILE>/ location

dnf whatprovides /var/run/<FILE>/

修改 /etc/my.cnf

#pid-file=/var/run/mysqld/mysqld.pid
pid-file=/run/mysqld/mysqld.pid

systemctl enable mysqld --now

Fix: tmpfiles

Diagnostic Steps

systemctl restart systemd-tmpfiles-clean.service

grep "Line references path below legacy directory /var/run/" /var/log/messages

... vm systemd-tmpfiles[52620]: [/usr/lib/tmpfiles.d/mysql.conf:23]
Line references path below legacy directory /var/run/, updating /var/run/mysqld → /run/mysqld;
please update the tmpfiles.d/ drop-in file accordingly.

P.S.

要安 rsyslog 才有 /var/log/messages 的 log

dnf install rsyslog; systemctl enable rsyslog --now

舊的 Default Settings:

cat /usr/lib/tmpfiles.d/mysql.conf

d /var/run/mysqld 0755 mysql mysql  -

Fix: 建立新的 /etc/tmpfiles.d/mysql.conf

sed "s@/var/run/@/run/@g" /usr/lib/tmpfiles.d/mysql.conf > /etc/tmpfiles.d/mysql.conf