[CentOS7] 기존 mysql제거 및 mysql8버전 설치, 환경설정, 대소문자 구분없이

1. 기존 mysql제거

# 설치된 mysql 정보 확인  :  yum list installed | grep mysql

yum list installed | grep mysql

mysql-community-client.x86_64          8.0.30-1.el7     @mysql80-community
mysql-community-client-plugins.x86_64  8.0.30-1.el7     @mysql80-community
mysql-community-common.x86_64          8.0.30-1.el7     @mysql80-community
mysql-community-icu-data-files.x86_64  8.0.30-1.el7     @mysql80-community
mysql-community-libs.x86_64            8.0.30-1.el7     @mysql80-community
mysql-community-server.x86_64          8.0.30-1.el7     @mysql80-community
mysql80-community-release.noarch       el7-5           @/mysql80-community-release-el7-5.noarch

 

# 기존 패키지 삭제 : yum remove -y mysql-community-*

yum remove -y mysql-community-*


Loaded Plugins : .... 생략
.
.
.
Removed : 
   mysql-community-client ....... 생략
   
Complete!

 

# 기존 mysql 정보및 데이터 삭제 : rm -rf /var/lib/mysql

rm -rf /var/lib/mysql

 

 # 기존 RPM 확인 : rpm -qa | grep mysql

rpm -qa | grep mysql

 

# 기존 RPM 제거 : rpm -e mysql80-community-release

rpm -e mysql80-community-release

 

# 기존 RPM 파일 확인  : find / -name 'mysql*rpm'

find / -name 'mysql*rpm'

/var/tmp/yum-root-gi2CNt/mysql80-community-release-el7-5.noarch.rpm

 

# 기존 RPM 파일 제거 : rm -rf /var/tmp/yum-root-gi2CNt/mysql80-community-release-el7-5.noarch.rpm

rm -rf /var/tmp/yum-root-gi2CNt/mysql80-community-release-el7-5.noarch.rpm

---

2. mysql8 설치

# yum repository 등록 :  yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm
버젼확인 url : MySQL Community Downloads : https://dev.mysql.com/downloads/repo/yum/

yum install -y https://dev.mysql.com/get/mysql80-community-release-el7-5.noarch.rpm

Loaded Plugins : .... 생략
.
.
.
Installed : 
   mysql80-community-re ....... 생략
   
Complete!

#  yum repository 확인 : yum repolist enabled | grep "mysql.*"

yum repolist enabled | grep "mysql.*"

mysql-connectors .... 생략 ..    199
mysql-tools .... 생략            89
mysql80-comm ....생략            311

 

# mysql8 설치 : yum install -y mysql-server

yum install -y mysql-server

Total .....생략
.
.
.
.
Complete!

# 설치 버젼 확인 : mysqld -V

mysqld -V

/usr/sbin/mysqld  Ver 8.0.30 for Linux on x86_64 (MySQL Community Server - GPL)

---

3. 환경설정 :   

  대소문자 구분없이 : lower_case_table_names = 1
  * "대소문자 구분없음 으로 셋팅" 후 구동  ... 디폴트는 대소문자 구분으로 되어 있어 계정생성 및 디비 이관 후에  설정 정보를 변경 하려면 디비를 재설치 해야 하는 상황이 발생 하므로 꼭 변경 후 서비스 구동 하는게 좋음.

[client]
#password   = your_password
port        = 3306
socket      = /var/lib/mysql/mysql.sock
default-character-set=utf8


# For advice on how to change settings please see
# http://dev.mysql.com/doc/refman/8.0/en/server-configuration-defaults.html

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
# innodb_buffer_pool_size = 128M
#
# Remove the leading "# " to disable binary logging
# Binary logging captures changes between backups and is enabled by
# default. It's default setting is log_bin=binlog
# disable_log_bin
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M
#
# Remove leading # to revert to previous value for default_authentication_plugin,
# this will increase compatibility with older clients. For background, see:
# https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_default_authentication_plugin
# default-authentication-plugin=mysql_native_password

#datadir=/var/lib/mysql
#socket=/var/lib/mysql/mysql.sock

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


port        = 3306
socket      = /var/lib/mysql/mysql.sock
datadir     = /var/lib/mysql
collation_server = utf8_general_ci
character_set_server = utf8
default-storage-engine = MYISAM
max_allowed_packet = 2M
table_open_cache = 10240
sort_buffer_size = 4M
read_buffer_size = 4M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
max_connections = 256
wait_timeout = 60
interactive_timeout = 360
explicit_defaults_for_timestamp = 1
secure-file-priv = ""

validate_password.policy = LOW
default_authentication_plugin = mysql_native_password
log-error=/var/lib/mysql/db01.log


lower_case_table_names = 1
plugin_dir=/usr/lib64/mysql/plugin


# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

sql_mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES

[mysqldump]
quick
max_allowed_packet = 16M
default-character-set=utf8

[mysql]
no-auto-rehash
# Remove the next comment character if you are not familiar with SQL
#safe-updates
default-character-set=utf8

[isamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[myisamchk]
key_buffer = 256M
sort_buffer_size = 256M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

---

4. 서비스 운영

# mysql 서비스 실행 : 

systemctl start mysqld (시작)
systemctl stop mysqld (중지)
systemctl status mysqld (현재상태)
systemctl enable mysqld (재기동 시 자동 시작)

 

 

# 초기 설치시 임시 비번 확인 :  grep "temporary password" /var/log/mysqld.log

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

 생략 ....[Server] A temporary password is generated for root@localhost: %gMAaC%jh1!,

 

# root 계정 비번 변경 : ALTER USER 'root'@'localhost' IDENTIFIED BY '변경비번';

 

  변경

  mysql> SET GLOBAL validate_password.policy=LOW; =>  정책 낮음

  mysql> SET GLOBAL validate_password.mixed_case_count = 0;  => 대소문자 구분없이

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '변경비번';

Your password does not satisfy the current policy requirements
(8자리 이상의 영문대소문자, 특수문자 포함 정책 관련 메세지)

해당 정책에 맞는 비번으로 변경 후

정책 수정

mysql> SHOW VARIABLES LIKE 'validate_password%';

변경
mysql> SET GLOBAL validate_password.policy=LOW; =>  정책 낮음
mysql> SET GLOBAL validate_password.mixed_case_count = 0;  => 대소문자 구분없이

mysql> SHOW VARIABLES LIKE 'validate_password%';
+--------------------------------------+-------+
| Variable_name                        | Value |
+--------------------------------------+-------+
| validate_password.check_user_name    | ON    |
| validate_password.dictionary_file    |       |
| validate_password.length             | 8     |
| validate_password.mixed_case_count   | 0     |
| validate_password.number_count       | 1     |
| validate_password.policy             | LOW   |
| validate_password.special_char_count | 1     |
+--------------------------------------+-------+
7 rows in set (0.00 sec)