http://iotn.co.kr 연락처: gilho.kr@gmail.com [ The Japanese government should apologize to Japanese Military Sexual Slavery victim. / 日本政府は日本軍の性的奴隷?牲者に謝罪すべき. ]

사용자

ID:
PW:

INDEX
01.게시판
게시판 [69]
02.File Book
File 자료실 [95]
Site Link [18]
개인폴더 [194]
03.Field Book
Altibase [19]
Tibero [30]
MS-SQL [18]
MySQL [40]
DB2 [79]
ORACLE [2888]
PostgreSQL [103]
기타정보 [150]
운영체제 [577]
04.Q/A Book
Q/A [53]
05.방명록
방명록 [54]
레벨업 [37]
구인/구직 [2]

기념일
Search
LINK
+ 가족 Hompy
+ DNSEver
IOTN :: Work Book :: MySQL

 회원가입

Oracle mysql Ver 8.0.20 on Linux 7 [ S/W Install, create database ,create user, alter user, 폴더 변경 ]
최길호 [LIST]   2020-07-02 00:20:13, 조회 : 235

# cat /etc/redhat-release

SQL PROMPT
export MYSQL_PS1="\u@\d> "


CentOS Linux release 7.7.1908 (Core)

cat /etc/yum.repos.d/epel.repo
[epel]
name=Extra Packages for Enterprise Linux 7 - $basearch
#baseurl=http://download.fedoraproject.org/pub/epel/7/$basearch
metalink=https://mirrors.fedoraproject.org/metalink?repo=epel-7&arch=$basearch&infra=$infra&content=$contentdir
failovermethod=priority
enabled=1
gpgcheck=1
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7

[epel-debuginfo]
name=Extra Packages for Enterprise Linux 7 - $basearch - Debug
#baseurl=http://download.fedoraproject.org/pub/epel/7/$basearch/debug
metalink=https://mirrors.fedoraproject.org/metalink?repo=epel-debug-7&arch=$basearch&infra=$infra&content=$contentdir
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
gpgcheck=1

[epel-source]
name=Extra Packages for Enterprise Linux 7 - $basearch - Source
#baseurl=http://download.fedoraproject.org/pub/epel/7/SRPMS
metalink=https://mirrors.fedoraproject.org/metalink?repo=epel-source-7&arch=$basearch&infra=$infra&content=$contentdir
failovermethod=priority
enabled=0
gpgkey=file:///etc/pki/rpm-gpg/RPM-GPG-KEY-EPEL-7
gpgcheck=1


yum repolist


성능 모니터
yum install htop
yum install nmon
yum install tmux [ Ctr+b , %, ", Ctrl + 방향키 크기조절, Ctrl+b , 방향키 이동,  tmux is a terminal multiplexer ]


yum install perl\(JSON\)
or
yum install perl-JSON
yum remove mariadb-server
rpm -Uvh mysql-commercial-*

systemctl enable mysqld
systemctl start mysqld

cat /var/log/mysqld.log | grep password | more
2020-07-01T13:48:47.630410Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Pra>R8oejrLW
mysql -uroot -p
Pra>R8oejrLW
mysql> ALTER user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'root';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
mysql>
mysql>
alter user 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'RootP@ssw0rd';
alter user root identified by 'RootP@ssw0rd';
ERROR 1396 (HY000): Operation ALTER USER failed for 'root'@'local' -- select user,host from mysql.user where user='root'; -- %없고 localhost만 있으면 아래처럼 localhost를 넣야 한다.
alter user 'root'@'localhost' identified by 'RootP@ssw0rd';
Query OK, 0 rows affected (0.01 sec)
mysql>
mysql> status;
--------------
mysql  Ver 8.0.20-commercial for Linux on x86_64 (MySQL Enterprise Server - Commercial)

Connection id:          15
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.0.20-commercial
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:                 16 min 46 sec

Threads: 3  Questions: 13  Slow queries: 0  Opens: 128  Flush tables: 3  Open tables: 46  Queries per second avg: 0.012
--------------


create database webdb default character set utf8 ;
alter database webdb COLLATE 'euckr_korean_ci';
show databases;
use webdb

mysql> show variables like '%passw%';
+----------------------------------------------+-----------------+
| Variable_name                                | Value           |
+----------------------------------------------+-----------------+
| caching_sha2_password_auto_generate_rsa_keys | ON              |
| caching_sha2_password_private_key_path       | private_key.pem |
| caching_sha2_password_public_key_path        | public_key.pem  |
| default_password_lifetime                    | 0               |
| disconnect_on_expired_password               | ON              |
| generated_random_password_length             | 20              |
| mysql_native_password_proxy_users            | OFF             |
| password_history                             | 0               |
| password_require_current                     | OFF             |
| password_reuse_interval                      | 0               |
| report_password                              |                 |
| sha256_password_auto_generate_rsa_keys       | ON              |
| sha256_password_private_key_path             | private_key.pem |
| sha256_password_proxy_users                  | OFF             |
| sha256_password_public_key_path              | public_key.pem  |
| 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               |
+----------------------------------------------+-----------------+
22 rows in set (0.01 sec)

-- 계속 필요하면 /etc/my.cnf 저장
set global validate_password.check_user_name=off;
set global validate_password.policy=LOW;
set global validate_password.length=4;

-- 'user'@'%' => user 사용하면 default host=%로 처리한다.
drop user webadmin;
drop user webdb;
create user webadmin identified by 'admin'; -- Error - Authentication plugin 'caching_sha2_password' 에러 가능
alter user webadmin identified with mysql_native_password by 'admin'; -- select user,plugin from mysql.user;
create user webdb identified by 'webpwd';
grant all privileges on *.* to webadmin;
grant all privileges on webdb.* to 'webdb;
select user,host from mysql.user;
flush privileges;


mysql -h localhost -uwebadmin -padmin webdb

mysql> show variables like '%char%';
+--------------------------------------+--------------------------------+
| Variable_name                        | Value                          |
+--------------------------------------+--------------------------------+
| character_set_client                 | utf8mb4                        |
| character_set_connection             | utf8mb4                        |
| character_set_database               | utf8                           |
| character_set_filesystem             | binary                         |
| character_set_results                | utf8mb4                        |
| character_set_server                 | utf8mb4                        |
| character_set_system                 | utf8                           |
| character_sets_dir                   | /usr/share/mysql-8.0/charsets/ |
| validate_password.special_char_count | 1                              |
+--------------------------------------+--------------------------------+
9 rows in set (0.01 sec)
mysql> create table 한글 ( 한글 text);
Query OK, 0 rows affected (0.06 sec)

mysql> insert into 한글 values ('한글');
Query OK, 1 row affected (0.01 sec)

mysql> select * from 한글;
+--------+
| 한글   |
+--------+
| 한글   |
+--------+
1 row in set (0.00 sec)

다른 터미널 한글 UTF-8 안되고 EUC-KR 된다.

export LANG=ko_KR.EUC-KR

mysql> show variables like '%char%';
+--------------------------------------+--------------------------------+
| Variable_name                        | Value                          |
+--------------------------------------+--------------------------------+
| character_set_client                 | euckr                          |
| character_set_connection             | euckr                          |
| character_set_database               | utf8                           |
| character_set_filesystem             | binary                         |
| character_set_results                | euckr                          |
| character_set_server                 | utf8mb4                        |
| character_set_system                 | utf8                           |
| character_sets_dir                   | /usr/share/mysql-8.0/charsets/ |
| validate_password.special_char_count | 1                              |
+--------------------------------------+--------------------------------+
mysql> select * from 한글;
+------+
| 한글 |
+------+
| 한글 |
+------+
1 row in set (0.00 sec)


mysql>

## 폴더 변경

systemctl stop mysqld

vi /etc/my.cnf
datadir=/data/mysql
socket=/data/mysql/mysql.sock

rsync -av /var/lib/mysql /data/
chmod 755 -R /data/mysql/
chown mysql:mysql -R /data/mysql/
mv /var/lib/mysql /var/lib/mysql.old
mkdir /var/lib/mysql
ln -s /data/mysql/mysql.sock /var/lib/mysql/mysql.sock

semanage fcontext -a -t mysqld_db_t "/data/mysql(/.*)?"
restorecon -R /data/mysql

systemctl start mysqld

mysql> select @@datadir;


## 기본 튜닝

vi /etc/my.cnf
innodb_buffer_pool_size = 2g
join_buffer_size = 800m
sort_buffer_size = 400m
read_rnd_buffer_size = 200m




  LIST

Copyright 1999-2022 Zeroboard / skin by 최길호(gilho.kr@gmail.com)
최근 댓글
2008/12/15
MySQL이 안좋은건가? 장애도 쉽게 나고,...
by 최길호
최근 게시물
06/29
[ORACLE]
DDE: Problem Key 'ORA 600 [133....
by 최길호
06/25
[ORACLE]
Replication may not be valid i....
by 최길호
06/22
[운영체제]
CentOS7 홈페이지 Migration 메모.
by 최길호