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

 회원가입

MariaDB 10.4 Install / Client Tool / Config / Create Database / Create User / Alter User 암호 변경 / 테이블명 대소문자
최길호 [LIST]   2020-05-29 17:28:46, 조회 : 288



추가
oracle linux 7.9
vi /etc/my.conf
[mysqld]
lower_case_table_names=1

systemctl restart mariadb.service


출처: https://wikidocs.net/16274

Admin Tool
https://www.heidisql.com/
ERD Tool
https://dev.mysql.com/downloads/workbench/
Migration Tool
http://www.sqlines.com/download

cat >> /etc/security/limits.conf
* soft nproc 65536
* hard nproc 65536
* soft nofile 1048576
* hard nofile 1048576
* soft stack 10240


mysql 제거
# yum install MariaDB-server
.... 아래와 같이 에러 나면 mysql 제거 필요
Failed:
  MariaDB-server.x86_64 0:10.4.13-1.el6                             mysql-server.x86_64 0:5.1.73-8.el6_8                            
Complete!
# yum remove mysql mysql-server

mariadb 설치
curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
/etc/yum.repos.d/mariadb.repo
yum install MariaDB-server
systemctl enable mariadb
systemctl start mariadb
mysql_secure_installation

## Linux 6.x 필요시 작업
chkconfig mysql on
service mysql start
에러 발생시 아래 조치
chown -R mysql:mysql /var/lib/mysql/*
service mysql start
에러 발생시 삭제후 DB 생성
rm -fr /var/lib/mysql/*
mysql_install_db
chown -R mysql:mysql /var/lib/mysql/*


semanage port -l | grep mysqld_port_t
# default 3306
semanage port -a -t mysqld_port_t -p tcp 3456
# change 3456
vi /etc/my.cnf.d/server.cnf
[mysqld]
port=3456
systemctl restart mariadb
firewall-cmd --permanent --add-port=3456/tcp
firewall-cmd --reload


mysql -u root -p
create database webdb default character set euckr COLLATE 'euckr_korean_ci';
create user test ;                    -- 암호 없이 계정 생성
create user 'user'@'%' identified by 'userpwd';
alter user 'user'@'%' identified by 'newuserpwd';           -- change password
grant all privileges on test.* to 'user'@'%' ;                    -- test db 사용
grant all privileges on *.* to 'user'@'%' ;                       -- 모든 db 사용
grant all privileges on *.* to 'user'@'%' identified by 'userpwd';
FLUSH PRIVILEGES;
select @@datadir;

Tunning
vi /etc/my.cnf
[client-server]
[mysqld]
log_error
log-bin
expire_logs_days=10
lower_case_table_names=1
max_allowed_packet=2G
max_connections=1510
character-set-server=utf8
collation-server=utf8_unicode_ci
init_connect = set collation_connection = utf8_unicode_ci
init_connect = set names utf8
long_query_time = 1
slow_query_log = 1
slow_query_log_file = /tmp/mariadb.slow.log
log_queries_not_using_indexes
# /usr/bin/mysqldumpslow   - Summarize slow query log files


정보 확인
show variables like 'lower_case_table_names'; -- 테이블 대소문자 1=off
show variables like 'max_connections'; -- 최대 로그인
show variables like 'c%';
show variables like 'max_allowed_packet'; -- 튜닝
show variables like 'expire_logs_days'; -- 로그 삭제
show variables like 'log_error'; -- 로그 파일 위치


=========== log ========

[root@node1 ~]# curl -sS https://downloads.mariadb.com/MariaDB/mariadb_repo_setup | sudo bash
[info] Repository file successfully written to /etc/yum.repos.d/mariadb.repo
[info] Adding trusted package signing keys...
[info] Successfully added trusted package signing keys
[root@node1 ~]#
[root@node1 ~]# yum install MariaDB-server
Loaded plugins: fastestmirror, langpacks
Determining fastest mirrors
file:///mnt/repodata/repomd.xml: [Errno 14] curl#37 - "Couldn't open file /mnt/repodata/repomd.xml"
Trying other mirror.
mariadb-main                                                                                                 | 2.9 kB  00:00:00    
mariadb-maxscale                                                                                             | 2.4 kB  00:00:00    
mariadb-tools                                                                                                | 2.9 kB  00:00:00    
(1/3): mariadb-tools/7/x86_64/primary_db                                                                     |  14 kB  00:00:00    
(2/3): mariadb-maxscale/7/x86_64/primary_db                                                                  | 6.9 kB  00:00:01    
(3/3): mariadb-main/7/x86_64/primary_db                                                                      |  57 kB  00:00:02    
Resolving Dependencies
--> Running transaction check
---> Package MariaDB-server.x86_64 0:10.4.13-1.el7.centos will be obsoleting
--> Processing Dependency: galera-4 for package: MariaDB-server-10.4.13-1.el7.centos.x86_64
--> Processing Dependency: MariaDB-client for package: MariaDB-server-10.4.13-1.el7.centos.x86_64
--> Processing Dependency: MariaDB-common for package: MariaDB-server-10.4.13-1.el7.centos.x86_64
---> Package mariadb-server.x86_64 1:5.5.64-1.el7 will be obsoleted
--> Running transaction check
---> Package MariaDB-client.x86_64 0:10.4.13-1.el7.centos will be obsoleting
---> Package MariaDB-common.x86_64 0:10.4.13-1.el7.centos will be installed
--> Processing Dependency: MariaDB-compat for package: MariaDB-common-10.4.13-1.el7.centos.x86_64
---> Package galera-4.x86_64 0:26.4.4-1.rhel7.el7.centos will be installed
---> Package mariadb.x86_64 1:5.5.64-1.el7 will be obsoleted
--> Running transaction check
---> Package MariaDB-compat.x86_64 0:10.4.13-1.el7.centos will be obsoleting
---> Package mariadb-libs.x86_64 1:5.5.64-1.el7 will be obsoleted
--> Processing Dependency: mariadb-libs(x86-64) = 1:5.5.64-1.el7 for package: 1:mariadb-devel-5.5.64-1.el7.x86_64
--> Running transaction check
---> Package MariaDB-devel.x86_64 0:10.4.13-1.el7.centos will be obsoleting
---> Package mariadb-devel.x86_64 1:5.5.64-1.el7 will be obsoleted
--> Finished Dependency Resolution

Dependencies Resolved

====================================================================================================================================
Package                        Arch                   Version                                   Repository                    Size
====================================================================================================================================
Installing:
MariaDB-client                 x86_64                 10.4.13-1.el7.centos                      mariadb-main                  12 M
     replacing  mariadb.x86_64 1:5.5.64-1.el7
MariaDB-compat                 x86_64                 10.4.13-1.el7.centos                      mariadb-main                 2.2 M
     replacing  mariadb-libs.x86_64 1:5.5.64-1.el7
MariaDB-devel                  x86_64                 10.4.13-1.el7.centos                      mariadb-main                 7.6 M
     replacing  mariadb-devel.x86_64 1:5.5.64-1.el7
MariaDB-server                 x86_64                 10.4.13-1.el7.centos                      mariadb-main                  26 M
     replacing  mariadb-server.x86_64 1:5.5.64-1.el7
Installing for dependencies:
MariaDB-common                 x86_64                 10.4.13-1.el7.centos                      mariadb-main                  81 k
galera-4                       x86_64                 26.4.4-1.rhel7.el7.centos                 mariadb-main                 9.5 M

Transaction Summary
====================================================================================================================================
Install  4 Packages (+2 Dependent packages)

Total download size: 57 M
Is this ok [y/d/N]: y
Downloading packages:
(1/6): MariaDB-common-10.4.13-1.el7.centos.x86_64.rpm                                                        |  81 kB  00:00:03    
(2/6): MariaDB-compat-10.4.13-1.el7.centos.x86_64.rpm                                                        | 2.2 MB  00:00:06    
(3/6): MariaDB-client-10.4.13-1.el7.centos.x86_64.rpm                                                        |  12 MB  00:00:13    
(4/6): MariaDB-devel-10.4.13-1.el7.centos.x86_64.rpm                                                         | 7.6 MB  00:00:12    
(5/6): MariaDB-server-10.4.13-1.el7.centos.x86_64.rpm                                                        |  26 MB  00:00:16    
(6/6): galera-4-26.4.4-1.rhel7.el7.centos.x86_64.rpm                                                         | 9.5 MB  00:00:12    
------------------------------------------------------------------------------------------------------------------------------------
Total                                                                                               1.6 MB/s |  57 MB  00:00:35    
Running transaction check
Running transaction test
Transaction test succeeded
Running transaction
  Installing : MariaDB-common-10.4.13-1.el7.centos.x86_64                                                                      1/10
  Installing : MariaDB-compat-10.4.13-1.el7.centos.x86_64                                                                      2/10
  Installing : MariaDB-client-10.4.13-1.el7.centos.x86_64                                                                      3/10
  Installing : galera-4-26.4.4-1.rhel7.el7.centos.x86_64                                                                       4/10
  Installing : MariaDB-server-10.4.13-1.el7.centos.x86_64                                                                      5/10


Two all-privilege accounts were created.
One is root@localhost, it has no password, but you need to
be system 'root' user to connect. Use, for example, sudo mysql
The second is mysql@localhost, it has no password either, but
you need to be the system 'mysql' user to connect.
After connecting you can set the password, if you would need to be
able to connect as any of these users with a password and without sudo

See the MariaDB Knowledgebase at http://mariadb.com/kb or the
MySQL manual for more instructions.

Please report any problems at http://mariadb.org/jira

The latest information about MariaDB is available at http://mariadb.org/.
You can find additional information about the MySQL part at:
http://dev.mysql.com
Consider joining MariaDB's strong and vibrant community:
https://mariadb.org/get-involved/

  Installing : MariaDB-devel-10.4.13-1.el7.centos.x86_64                                                                       6/10
  Erasing    : 1:mariadb-server-5.5.64-1.el7.x86_64                                                                            7/10
  Erasing    : 1:mariadb-devel-5.5.64-1.el7.x86_64                                                                             8/10
  Erasing    : 1:mariadb-5.5.64-1.el7.x86_64                                                                                   9/10
  Erasing    : 1:mariadb-libs-5.5.64-1.el7.x86_64                                                                             10/10
  Verifying  : MariaDB-server-10.4.13-1.el7.centos.x86_64                                                                      1/10
  Verifying  : MariaDB-compat-10.4.13-1.el7.centos.x86_64                                                                      2/10
  Verifying  : MariaDB-devel-10.4.13-1.el7.centos.x86_64                                                                       3/10
  Verifying  : MariaDB-client-10.4.13-1.el7.centos.x86_64                                                                      4/10
  Verifying  : MariaDB-common-10.4.13-1.el7.centos.x86_64                                                                      5/10
  Verifying  : galera-4-26.4.4-1.rhel7.el7.centos.x86_64                                                                       6/10
  Verifying  : 1:mariadb-libs-5.5.64-1.el7.x86_64                                                                              7/10
  Verifying  : 1:mariadb-devel-5.5.64-1.el7.x86_64                                                                             8/10
  Verifying  : 1:mariadb-5.5.64-1.el7.x86_64                                                                                   9/10
  Verifying  : 1:mariadb-server-5.5.64-1.el7.x86_64                                                                           10/10

Installed:
  MariaDB-client.x86_64 0:10.4.13-1.el7.centos                     MariaDB-compat.x86_64 0:10.4.13-1.el7.centos                    
  MariaDB-devel.x86_64 0:10.4.13-1.el7.centos                      MariaDB-server.x86_64 0:10.4.13-1.el7.centos                    

Dependency Installed:
  MariaDB-common.x86_64 0:10.4.13-1.el7.centos                      galera-4.x86_64 0:26.4.4-1.rhel7.el7.centos                    

Replaced:
  mariadb.x86_64 1:5.5.64-1.el7               mariadb-devel.x86_64 1:5.5.64-1.el7        mariadb-libs.x86_64 1:5.5.64-1.el7      
  mariadb-server.x86_64 1:5.5.64-1.el7      

Complete!
[root@node1 ~]#
[root@node1 ~]# mysql_secure_installation

NOTE: RUNNING ALL PARTS OF THIS SCRIPT IS RECOMMENDED FOR ALL MariaDB
      SERVERS IN PRODUCTION USE!  PLEASE READ EACH STEP CAREFULLY!

In order to log into MariaDB to secure it, we'll need the current
password for the root user. If you've just installed MariaDB, and
haven't set the root password yet, you should just press enter here.

Enter current password for root (enter for none):
OK, successfully used password, moving on...

Setting the root password or using the unix_socket ensures that nobody
can log into the MariaDB root user without the proper authorisation.

You already have your root account protected, so you can safely answer 'n'.

Switch to unix_socket authentication [Y/n] n
... skipping.

You already have your root account protected, so you can safely answer 'n'.

Change the root password? [Y/n] y
New password:
Re-enter new password:
Password updated successfully!
Reloading privilege tables..
... Success!


By default, a MariaDB installation has an anonymous user, allowing anyone
to log into MariaDB without having to have a user account created for
them.  This is intended only for testing, and to make the installation
go a bit smoother.  You should remove them before moving into a
production environment.

Remove anonymous users? [Y/n] y
... Success!

Normally, root should only be allowed to connect from 'localhost'.  This
ensures that someone cannot guess at the root password from the network.

Disallow root login remotely? [Y/n] y
... Success!

By default, MariaDB comes with a database named 'test' that anyone can
access.  This is also intended only for testing, and should be removed
before moving into a production environment.

Remove test database and access to it? [Y/n] y
- Dropping test database...
... Success!
- Removing privileges on test database...
... Success!

Reloading the privilege tables will ensure that all changes made so far
will take effect immediately.

Reload privilege tables now? [Y/n] y
... Success!

Cleaning up...

All done!  If you've completed all of the above steps, your MariaDB
installation should now be secure.

Thanks for using MariaDB!
[root@node1 ~]#
[root@node1 ~]# mysql -u root -p
Enter password:
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 19
Server version: 10.4.13-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> create database userdb;
Query OK, 1 row affected (0.001 sec)

MariaDB [(none)]> create user 'user'@'%' identified by 'userpwd';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]>
MariaDB [(none)]> grant all privileges on *.* to 'user'@'%' identified by 'userpwd';
Query OK, 0 rows affected (0.005 sec)

MariaDB [(none)]> flush privileges;
Query OK, 0 rows affected (0.001 sec)

MariaDB [(none)]> exit
Bye
[root@node1 ~]# mysql -uuser -puserpwd
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 20
Server version: 10.4.13-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

MariaDB [(none)]> select @@datadir;
+-----------------+
| @@datadir       |
+-----------------+
| /var/lib/mysql/ |
+-----------------+
1 row in set (0.000 sec)

MariaDB [userdb]>


  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 최길호