OS : Centos 7.x
MYSQL VERSION : 8.0.26
INSTALL : yum

1. mysql 8.0.21 설치를 진행하는데 간단하게 yum 으로 진행해보자 repo rpm file down 및 설치
   
   Shell

   [root@localhost ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm [root@localhost ~]# rpm -Uvh mysql80-community-release-el7-3.noarch.rpm [root@localhost ~]# ll /etc/yum.repos.d/ | grep mysql -rw-r--r-- 1 root root 2108 4월 25 2019 mysql-community-source.repo -rw-r--r-- 1 root root 2076 4월 25 2019 mysql-community.repo

   1 2 3 4 5 6 7

   [root@localhost ~]# wget https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm   [root@localhost ~]# rpm -Uvh mysql80-community-release-el7-3.noarch.rpm   [root@localhost ~]# ll /etc/yum.repos.d/ | grep mysql -rw-r--r--  1 root root 2108  4월 25  2019 mysql-community-source.repo -rw-r--r--  1 root root 2076  4월 25  2019 mysql-community.repo

2. yum install
   
   Shell

   [root@localhost ~]# yum -y install mysql-community-server.x86_64 mysql-community-libs.x86_64 mysql-community-client.x86_64 mysql-community-devel.x86_64

   1	[root@localhost ~]# yum -y install mysql-community-server.x86_64 mysql-community-libs.x86_64 mysql-community-client.x86_64 mysql-community-devel.x86_64

3. /etc/my.cnf 파일 설정 (기본 innodb)  —> 만약 기본 engine을 myisam으로 설정하고 싶다면 젤 하단 myisam my.cnf파일 내용 참고
   
   Shell

   [root@localhost ~]# vi /etc/my.cnf mysqld] # innodb_buffer_pool_size = 128M # disable_log_bin # # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ### log log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-external-locking key_buffer_size = 384M max_allowed_packet = 16M table_open_cache = 2048 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 #dns query skip-name-resolve #connection max_connections = 1000 max_connect_errors = 1000 wait_timeout= 60 #slow-queries #slow_query_log = /var/log//slow-queries.log #long_query_time = 3 #log-slow-queries = /var/log/mysql-slow-queries.log ##timestamp explicit_defaults_for_timestamp #symbolic-links=0 ##Password Policy #validate_password.policy=0 #validate_password.policy=1 #validate_password.policy=2 ### MyISAM Spectific options #default-storage-engine = myisam key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 ### INNODB Spectific options default-storage-engine = InnoDB #innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 1024MB innodb_data_file_path = ibdata1:10M:autoextend innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 128M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120 [mysqldump] max_allowed_packet = 512M [mysql] #no-auto-rehash [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85

   [root@localhost ~]# vi /etc/my.cnf     mysqld] # innodb_buffer_pool_size = 128M # disable_log_bin # # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M   datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock   ### log log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid   skip-external-locking key_buffer_size = 384M max_allowed_packet = 16M table_open_cache = 2048 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8   #dns query skip-name-resolve   #connection max_connections = 1000 max_connect_errors = 1000 wait_timeout= 60   #slow-queries #slow_query_log = /var/log//slow-queries.log #long_query_time = 3 #log-slow-queries = /var/log/mysql-slow-queries.log   ##timestamp explicit_defaults_for_timestamp #symbolic-links=0     ##Password Policy #validate_password.policy=0 #validate_password.policy=1 #validate_password.policy=2   ### MyISAM Spectific options #default-storage-engine = myisam key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1   ### INNODB Spectific options default-storage-engine = InnoDB #innodb_additional_mem_pool_size = 16M innodb_buffer_pool_size = 1024MB innodb_data_file_path = ibdata1:10M:autoextend innodb_write_io_threads = 8 innodb_read_io_threads = 8 innodb_thread_concurrency = 16 innodb_flush_log_at_trx_commit = 1 innodb_log_buffer_size = 8M innodb_log_file_size = 128M innodb_log_files_in_group = 3 innodb_max_dirty_pages_pct = 90 innodb_lock_wait_timeout = 120   [mysqldump] max_allowed_packet = 512M   [mysql] #no-auto-rehash   [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M

   
   4. 서비스 시작 및 서비스 확인
   
   Shell

   [root@localhost ~]# systemctl start mysqld.service [root@localhost ~]# ps -ef | grep mysql mysql 2517 1 6 15:07 ? 00:00:01 /usr/sbin/mysqld

   1 2 3 4

   [root@localhost ~]# systemctl start mysqld.service   [root@localhost ~]# ps -ef | grep mysql mysql     2517     1  6 15:07 ?        00:00:01 /usr/sbin/mysqld

   
   5. 기본 설치를 하게 되면 패스워드 정보가 log 파일에 기록이 된다 검색을 통해서 패스워드를 확인하자
   
   Shell

   [root@localhost ~]# grep 'temporary password' /var/log/mysqld.log 2021-09-29T05:23:53.233658Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: q5Qn7Kalx,G6

   1 2	[root@localhost ~]# grep 'temporary password' /var/log/mysqld.log 2021-09-29T05:23:53.233658Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: q5Qn7Kalx,G6

   
   6. 서버 접속
   
   Shell

   [root@localhost ~]# mysql -u root -p mysql Enter password: q5Qn7Kalx,G6

   1 2	[root@localhost ~]# mysql -u root -p mysql Enter password: q5Qn7Kalx,G6

   
   7.기본 환경값 확인해야 하는데 에러가 발생한다 즉 root user가 없다는 것이다 alter로 추가
   
   MySQL

   mysql> \s ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement. ### root alter 추가 mysql> alter user 'root'@localhost identified by 'qwer1234'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

   1 2 3 4 5 6

   mysql> \s ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.   ### root alter 추가 mysql> alter user 'root'@localhost identified by 'qwer1234'; ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

   
   8. 사용자를 추가해도 에러가 발생한다 이 부분은 패스워드 정책에 어긋나기 때문에 그러는데 해당 부분은 좀더 자세하게 다를것이다
   우선 강력한 패스워드 8자리를 설정한다 (8자리 이상, 숫자,소문자,대문자,특수문자 를 모두 포함)
   
   MySQL

   mysql> alter user 'root'@localhost identified by 'Qwer1234!@#$'; Query OK, 0 rows affected (0.03 sec) mysql> flush privileges; Query OK, 0 rows affected (0.00 sec) mysql> \s -------------- mysql Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL) Connection id: 8 Current database: mysql Current user: root@localhost SSL: Not in use Current pager: stdout Using outfile: '' Using delimiter: ; Server version: 8.0.26 Protocol version: 10 Connection: Localhost via UNIX socket Server characterset: utf8mb4 Db characterset: utf8mb4 Client characterset: utf8mb4 Conn. characterset: utf8mb4 UNIX socket: /var/lib/mysql/mysql.sock Binary data as: Hexadecimal Uptime: 2 min 27 sec

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

   mysql> alter user 'root'@localhost identified by 'Qwer1234!@#$'; Query OK, 0 rows affected (0.03 sec)   mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)   mysql> \s -------------- mysql  Ver 8.0.26 for Linux on x86_64 (MySQL Community Server - GPL)   Connection id:          8 Current database:       mysql Current user:           root@localhost SSL:                    Not in use Current pager:          stdout Using outfile:          '' Using delimiter:        ; Server version:         8.0.26 Protocol version:       10 Connection:             Localhost via UNIX socket Server characterset:    utf8mb4 Db     characterset:    utf8mb4 Client characterset:    utf8mb4 Conn.  characterset:    utf8mb4 UNIX socket:            /var/lib/mysql/mysql.sock Binary data as:         Hexadecimal Uptime:                 2 min 27 sec

   
   9.기본 mysql engine을 확인해보자
   
   MySQL

   mysql> SELECT engine, support FROM information_schema.engines; +--------------------+---------+ | engine | support | +--------------------+---------+ | FEDERATED | NO | | MEMORY | YES | | InnoDB | DEFAULT | | PERFORMANCE_SCHEMA | YES | | MyISAM | YES | | MRG_MYISAM | YES | | BLACKHOLE | YES | | CSV | YES | | ARCHIVE | YES | +--------------------+---------+ 9 rows in set (0.00 sec)

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

   mysql> SELECT engine, support FROM information_schema.engines; +--------------------+---------+ | engine             | support | +--------------------+---------+ | FEDERATED          | NO      | | MEMORY             | YES     | | InnoDB             | DEFAULT | | PERFORMANCE_SCHEMA | YES     | | MyISAM             | YES     | | MRG_MYISAM         | YES     | | BLACKHOLE          | YES     | | CSV                | YES     | | ARCHIVE            | YES     | +--------------------+---------+ 9 rows in set (0.00 sec)

   
   10. 여기서 체크해야 할 항목이 있는데 우선 innodb_file_per_table 값이 기본 on으로 설정되어 있다
   innodb_file_per_table=OFF : ibdata1 파일 하나로 데이터 저장
   innodb_file_per_table=ON : 데이터베이스 내 테이블당 ibd 파일로 데이터 저장
   요즘 추세는 해당 옵션을 사용한다 즉 ibd파일로 데이터를 저장하는 방식

   mysql에서 확인해보자

   MySQL

   mysql> show global variables like '%innodb_file_per_table%' ; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | ON | +-----------------------+-------+ 1 row in set (0.00 sec)

   1 2 3 4 5 6 7

   mysql> show global variables like '%innodb_file_per_table%' ; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | innodb_file_per_table | ON    | +-----------------------+-------+ 1 row in set (0.00 sec)

   실제 해당값을 on / off 설정을 변경하면서 데이터베이스 생성 후 테이블을 만들어 보면
   xinet 데이터베이스 생성 후 innodb_file_per_table = on 으로 설정 했을 경우 아래처럼 ibd 파일이 생성된다

   MySQL

   mysql> create database xinet; Query OK, 1 row affected (0.01 sec) mysql> use xinet; Database changed mysql> create table idol ( groupname varchar(50), membername varchar(50) ); Query OK, 0 rows affected (0.05 sec) mysql> create table Users (id int(3) primary key, name varchar(20), email varchar(20), country varchar(20), password varchar(20)); Query OK, 0 rows affected, 1 warning (0.06 sec) mysql> INSERT INTO Users (id, name, email, country, password) VALUES (1, 'Pankaj', 'pankaj@apple.com', 'India', 'pankaj123'); Query OK, 1 row affected (0.03 sec) mysql> INSERT INTO Users (id, name, email, country, password) VALUES (4, 'David', 'david@gmail.com', 'USA', 'david123'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO Users (id, name, email, country, password) VALUES(5, 'Raman', 'raman@google.com', 'UK', 'raman123'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO idol (groupname, membername) VALUES('PCY', 'jsh'); Query OK, 1 row affected (0.03 sec) mysql> flush privileges; Query OK, 0 rows affected (0.03 sec)

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25

   mysql> create database xinet; Query OK, 1 row affected (0.01 sec)   mysql> use xinet; Database changed mysql> create table idol ( groupname varchar(50), membername varchar(50) ); Query OK, 0 rows affected (0.05 sec)   mysql> create table Users (id int(3) primary key, name varchar(20), email varchar(20), country varchar(20), password varchar(20)); Query OK, 0 rows affected, 1 warning (0.06 sec)   mysql> INSERT INTO Users (id, name, email, country, password) VALUES (1, 'Pankaj', 'pankaj@apple.com', 'India', 'pankaj123'); Query OK, 1 row affected (0.03 sec)   mysql> INSERT INTO Users (id, name, email, country, password) VALUES (4, 'David', 'david@gmail.com', 'USA', 'david123'); Query OK, 1 row affected (0.01 sec)   mysql> INSERT INTO Users (id, name, email, country, password) VALUES(5, 'Raman', 'raman@google.com', 'UK', 'raman123'); Query OK, 1 row affected (0.01 sec)   mysql> INSERT INTO idol (groupname, membername) VALUES('PCY', 'jsh');       Query OK, 1 row affected (0.03 sec)   mysql> flush privileges; Query OK, 0 rows affected (0.03 sec)

   파일을 확인해 보면 ibd 파일이 생성 된것을 확인 할 수 있다

   Shell

   [root@localhost ~]# ls -l /var/lib/mysql/xinet/ -rw-r----- 1 mysql mysql 114688 9월 29 14:29 Users.ibd -rw-r----- 1 mysql mysql 114688 9월 29 14:28 idol.ibd

   1 2 3 4

   [root@localhost ~]# ls -l /var/lib/mysql/xinet/   -rw-r----- 1 mysql mysql 114688  9월 29 14:29 Users.ibd -rw-r----- 1 mysql mysql 114688  9월 29 14:28 idol.ibd

   innodb_file_per_table = off 설정 후 데이터베이스 xinet3를 생성 후 테이블을 만들면  데이터베이스내에 아무런 파일이 존재하지 않는다

   Shell

   [root@localhost mysql]# vi /etc/my.cnf ###innodb_file_per_table 0 = OFF / 1 = ON innodb_file_per_table = 0

   1 2 3 4

   [root@localhost mysql]# vi /etc/my.cnf   ###innodb_file_per_table 0 = OFF / 1 = ON innodb_file_per_table = 0

    

   MySQL

   mysql> show global variables like '%innodb_file_per_table%' ; +-----------------------+-------+ | Variable_name | Value | +-----------------------+-------+ | innodb_file_per_table | OFF | +-----------------------+-------+ mysql> create database xinet3; Query OK, 1 row affected (0.02 sec) mysql> create table idol ( groupname varchar(50), membername varchar(50) ); Query OK, 0 rows affected (0.07 sec) mysql> create table Users (id int(3) primary key, name varchar(20), email varchar(20), country varchar(20), password varchar(20)); Query OK, 0 rows affected, 1 warning (0.05 sec) mysql> INSERT INTO Users (id, name, email, country, password) VALUES(5, 'Raman', 'raman@google.com', 'UK', 'raman123'); Query OK, 1 row affected (0.01 sec) mysql> INSERT INTO idol (groupname, membername) VALUES('PCY', 'jsh'); Query OK, 1 row affected (0.03 sec) mysql> flush privileges; Query OK, 0 rows affected (0.02 sec)

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24

   mysql> show global variables like '%innodb_file_per_table%' ; +-----------------------+-------+ | Variable_name         | Value | +-----------------------+-------+ | innodb_file_per_table | OFF   | +-----------------------+-------+   mysql> create database xinet3; Query OK, 1 row affected (0.02 sec)   mysql> create table idol ( groupname varchar(50), membername varchar(50) ); Query OK, 0 rows affected (0.07 sec)   mysql> create table Users (id int(3) primary key, name varchar(20), email varchar(20), country varchar(20), password varchar(20)); Query OK, 0 rows affected, 1 warning (0.05 sec)   mysql> INSERT INTO Users (id, name, email, country, password) VALUES(5, 'Raman', 'raman@google.com', 'UK', 'raman123'); Query OK, 1 row affected (0.01 sec)   mysql> INSERT INTO idol (groupname, membername) VALUES('PCY', 'jsh');       Query OK, 1 row affected (0.03 sec)   mysql> flush privileges; Query OK, 0 rows affected (0.02 sec)

   파일 확인

   Shell

   [root@localhost mysql]# ls -l /var/lib/mysql/xinet3/ 합계 0

   1 2	[root@localhost mysql]# ls -l /var/lib/mysql/xinet3/ 합계 0

   이제 패스워드를 좀더 쉽게 변경 해보자
   기존 mysql 5.7 버전에서는 패스워드 정책이

   #validate_password_policy=LOW
   #validate_password_policy=MEDIUM
   이런식으로 설정을 했었는데  mysql 8.0  버전에서는 _가 아닌 .으로 설정을 해야 한다우선 기본값이 어떤값으로 되어 있는지 확인 해 보자
   

   MySQL

   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 | 1 | | validate_password.number_count | 1 | | validate_password.policy | MEDIUM | | validate_password.special_char_count | 1 | +--------------------------------------+--------+ 7 rows in set (0.00 sec)

   1 2 3 4 5 6 7 8 9 10 11 12 13

   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   | 1      | | validate_password.number_count       | 1      | | validate_password.policy             | MEDIUM | | validate_password.special_char_count | 1      | +--------------------------------------+--------+ 7 rows in set (0.00 sec)

   
   해당 값은 MEDIUM 으로 되어 있지만 validate_password.policy 값이 5.7하고 다르게 구성되어 있다. 위에서 패스워드를 강력하게 구성을 했던것이
   이것 때문에 그런다  그럼 이제 패스워들 쉽게 구성하기 위해서 값을 변경해보자
   여기서 기존에는 LOW , MEDIUM 을 사용하였지만 my.cnf에서는 0,1,2로 설정해서 사용한다

   Shell

   [root@localhost mysql]# vi /etc/my.cnf [mysqld] ##Password Policy validate_password.policy=0 #validate_password.policy=1 #validate_password.policy=2

   1 2 3 4 5 6 7 8

   [root@localhost mysql]# vi /etc/my.cnf   [mysqld]   ##Password Policy validate_password.policy=0 #validate_password.policy=1 #validate_password.policy=2

   mysql 재시작

   Shell

   [root@localhost mysql]# systemctl restart mysqld

   1	[root@localhost mysql]# systemctl restart mysqld

   mysql 접속해서 이제 상태값을 보고 패스워드를 변경해보자  값이 LOW으로 변경 된 것을 확인 할 수 있다

   MySQL

   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 | 1 | | validate_password.number_count | 1 | | validate_password.policy | LOW | | validate_password.special_char_count | 1 | +--------------------------------------+-------+ 7 rows in set (0.00 sec)

   1 2 3 4 5 6 7 8 9 10 11 12 13

   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   | 1     | | validate_password.number_count       | 1     | | validate_password.policy             | LOW   | | validate_password.special_char_count | 1     | +--------------------------------------+-------+ 7 rows in set (0.00 sec)

   이제 패스워드를 좀더 쉬운것으로 변경 해 보자 근데 에러가 발생한다. 뭐지…

   MySQL

   mysql> update user set password=password('qwer1234') where user='root'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('qwer1234') where user='root'' at line 1

   1 2	mysql> update user set password=password('qwer1234') where user='root'; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('qwer1234') where user='root'' at line 1

   mysql 5.7 버전부터 password 필드가 없어지고 그 대신 authentication_string 필드가 password 필드를 대체

   MySQL

   mysql> select host, user, authentication_string, password_last_changed from user; +-----------+------------------+------------------------------------------------------------------------+-----------------------+ | host | user | authentication_string | password_last_changed | +-----------+------------------+------------------------------------------------------------------------+-----------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2021-09-29 14:23:58 | | localhost | mysql.session | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2021-09-29 14:23:58 | | localhost | mysql.sys | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2021-09-29 14:23:58 | | localhost | root | $A$005$a/?OhSe*l>]]9#-M|t47H/HTsiZhToB6ejWbHOhs6fDpjgLKGmdTpwfNqrz2 | 2021-09-29 14:26:24 | +-----------+------------------+------------------------------------------------------------------------+-----------------------+ 4 rows in set (0.00 sec)

   1 2 3 4 5 6 7 8 9 10

   mysql> select host, user, authentication_string, password_last_changed from user;     +-----------+------------------+------------------------------------------------------------------------+-----------------------+ | host      | user             | authentication_string                                                  | password_last_changed | +-----------+------------------+------------------------------------------------------------------------+-----------------------+ | localhost | mysql.infoschema | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2021-09-29 14:23:58   | | localhost | mysql.session    | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2021-09-29 14:23:58   | | localhost | mysql.sys        | $A$005$THISISACOMBINATIONOFINVALIDSALTANDPASSWORDTHATMUSTNEVERBRBEUSED | 2021-09-29 14:23:58   | | localhost | root             | $A$005$a/?OhSe*l>]]9#-M|t47H/HTsiZhToB6ejWbHOhs6fDpjgLKGmdTpwfNqrz2 | 2021-09-29 14:26:24   | +-----------+------------------+------------------------------------------------------------------------+-----------------------+ 4 rows in set (0.00 sec)

   이제 패스워드를 변경하는데 기존 update가 아닌 alter 명령어로 패스워드를 변경해 주면 된다

   MySQL

   mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'qwer1234'; Query OK, 0 rows affected (0.02 sec) mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)

   1 2 3 4 5

   mysql> alter user 'root'@'localhost' identified with mysql_native_password by 'qwer1234'; Query OK, 0 rows affected (0.02 sec)   mysql> flush privileges; Query OK, 0 rows affected (0.01 sec)

   변경된 패스워드로 정상 접속되는지 테스트 해보면 된다

   추가적으로 mysql engine을 myisam으로 사용하고 싶다면 아래 my.cnf 파일 복사 후 사용

   Shell

   [root@localhost mysql]# vi /etc/my.cnf [client] port = 3306 [mysqld] # innodb_buffer_pool_size = 128M # disable_log_bin # # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock ### log log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid skip-external-locking key_buffer_size = 384M max_allowed_packet = 16M table_open_cache = 2048 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8 #dns query skip-name-resolve #connection max_connections = 1000 max_connect_errors = 1000 wait_timeout= 60 #slow-queries #slow_query_log = /var/log//slow-queries.log #long_query_time = 3 #log-slow-queries = /var/log/mysql-slow-queries.log ##timestamp explicit_defaults_for_timestamp #symbolic-links=0 ##Password Policy validate_password.policy=0 #validate_password.policy=1 #validate_password.policy=2 ### MyISAM Spectific options default-storage-engine = myisam key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1 ### INNODB Spectific options #default-storage-engine = InnoDB #skip--innodb=OFF #innodb_additional_mem_pool_size = 16M #innodb_buffer_pool_size = 1024MB #innodb_data_file_path = ibdata1:10M:autoextend #innodb_write_io_threads = 8 #innodb_read_io_threads = 8 #innodb_thread_concurrency = 16 #innodb_flush_log_at_trx_commit = 1 #innodb_log_buffer_size = 8M #innodb_log_file_size = 128M #innodb_log_files_in_group = 3 #innodb_max_dirty_pages_pct = 90 #innodb_lock_wait_timeout = 120 [mysqldump] max_allowed_packet = 512M [mysql] #no-auto-rehash [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M

   1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90

   [root@localhost mysql]# vi /etc/my.cnf     [client] port = 3306   [mysqld] # innodb_buffer_pool_size = 128M # disable_log_bin # # join_buffer_size = 128M # sort_buffer_size = 2M # read_rnd_buffer_size = 2M   datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock   ### log log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid   skip-external-locking key_buffer_size = 384M max_allowed_packet = 16M table_open_cache = 2048 sort_buffer_size = 2M read_buffer_size = 2M read_rnd_buffer_size = 8M myisam_sort_buffer_size = 64M thread_cache_size = 8   #dns query skip-name-resolve   #connection max_connections = 1000 max_connect_errors = 1000 wait_timeout= 60   #slow-queries #slow_query_log = /var/log//slow-queries.log #long_query_time = 3 #log-slow-queries = /var/log/mysql-slow-queries.log     ##timestamp explicit_defaults_for_timestamp   #symbolic-links=0   ##Password Policy validate_password.policy=0 #validate_password.policy=1 #validate_password.policy=2   ### MyISAM Spectific options default-storage-engine = myisam key_buffer_size = 32M bulk_insert_buffer_size = 64M myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G myisam_repair_threads = 1   ### INNODB Spectific options #default-storage-engine = InnoDB #skip--innodb=OFF #innodb_additional_mem_pool_size = 16M #innodb_buffer_pool_size = 1024MB #innodb_data_file_path = ibdata1:10M:autoextend #innodb_write_io_threads = 8 #innodb_read_io_threads = 8 #innodb_thread_concurrency = 16 #innodb_flush_log_at_trx_commit = 1 #innodb_log_buffer_size = 8M #innodb_log_file_size = 128M #innodb_log_files_in_group = 3 #innodb_max_dirty_pages_pct = 90 #innodb_lock_wait_timeout = 120   [mysqldump] max_allowed_packet = 512M   [mysql] #no-auto-rehash   [myisamchk] key_buffer_size = 512M sort_buffer_size = 512M read_buffer = 8M write_buffer = 8M

    

2. 사용자 추가 및 권한 생성