MySQL

사용자 추가시 %와 localhost 함께 추가.

1. insert into user values('%','추가할 사용자 ID',password('사용자 패스워드'),
'N','N','N','N','N','N','N','N','N','N','N','N','N','N');
2. insert into db values('%','디비명','zeus','Y','Y','Y','Y','Y','Y','Y','Y',
'Y','Y');
- % 나 공백은 어디에서나 접속이 가능하도록 하는 옵션이나 이렇게 설정하면
local에서는 접속이 되지 않는 것 같다.
- insert into user values('localhost','추가할 사용자 ID',password
('사용자 패스워드'),'N','N','N','N','N','N','N','N','N','N','N','N','N','N');
이렇게 %를 localhost로 바꾸었다. 이렇게 하면
insert into db values('%','디비명','zeus','Y','Y','Y','Y','Y','Y','Y','Y',
'Y','Y'); 의 %는 바꾸어 주지 않아도 되는 것 같다..

요점은 %로 하니 사용자 추가와 db 추가를 해주어도 local에서 접속하면 db가
나타나지를 않는다는 것이다 user values에 localhost라고 추가하니
사용가능하게 되었다.
===============================================================================

===============================================================================
레드햇 7.3이하에서 설치방벙으로 8.0에서는 에러난다. pgcc가 돌아가지 않고
db파일도 생성되지 않는다. 물론 밑에 방법이 있다. 편법..^^

*. MySQL 설치
===============================================================================

1. 일단 기존의 MySQL을 모두 삭제하자
- 의존성이 걸려있으므로 하나하나 삭제하고
- rpm -e --nodeps mysql 기타등등 기타등등
- pcpp를 시스템에 설치하자..

2. pgcc 설치
- tar xvfz pgcc-2.xxx.xxx.tar.gz -C /
- /usr/local/pgcc에 설치된다.
- bin/의 gcc와 cpp를 pgcc와 pcpp로 이름을 바꾸어 헷갈리지 않게 사용하자.
- ~/.bash_profile 에 path를 걸어주자.
- . .bash_profile 로 설정파일을 읽어들이자

3. 아래와 같이 스크립트 파일을 작성하자.

#!/bin/bash

# 옵션 설정 시작 - 펜티엄 최적화
CC="/usr/local/pgcc/bin/pgcc"
# 컴파일러를 pgcc로 선택
CFLAGS="-O6 -mpentiumpro -mstack-align-double -fomit-frame-pointer -march=pentiumpro"
CXX="/usr/local/pgcc/bin/pgcc"
# c++ 컴파일러 내용
CXXFLAGS="-O6 -mpentiumpro -march=pentiumpro -mstack-align-double -fomit-frame-pointer -felide-constructors -fno-exceptions -fno-rtti"

# 초기화 설정 - 디렉토리지정, 클라이언트,서버 정적 라이브러화"
# - 문자셑지정, 디버깅하지 않음, 트랜잭션사용"
./configure --prefix=/usr/local/mysql --with-client-ldflags=-all-static --with-mysqld-ldflags=-all-static --with-charset=euc_kr --without-debug --enable-assembler
#--with-innodb
# all-static으로 정적으로 설치하자 속도를 위해서, 아파치의 경우도 비슷하지만 아파치는 속도차가
# 거진 안난다. 그러나 mysql의 경우 속도차가 있으므로 정적으로 설치하는 옵션으로 항상설치하자
# mysql은 기본적으로 API가 C 이므로 C와 연동하기 위하여 --enable-assembler옵션을 주자
# 컴파일
make

# 설치
make install

#/usr/local/mysql/bin/mysql_install_db

4. bin/ mysqldump : SQL언어로 backup
mysqlimport : text파일을 db화 시킬때
: 기타 여러 명령어들이 있으므로 공부하여 활용하자. ^^
include/mysql/ : 여러 API(C, php,...)들이 mysql을 사용하기위해 쓰이는 header 파일
이곳의 파일을 수동으로 이용하기도 하므로 기억하자
lib/mysql/ : xxx.a : 정적 라이브러리
: xxx.so : 공유 라이브러리
libexec/mysqld : 실제 작동되는 mysql daemon이다.
share/mysql/ my-huge.cnf, my-large.cnf, my-medium.cnf .... : 기본적인 설정파일이다.

5. 구동
- ./mysql-install-db : 설치후 처음 한번 실행한다.
* var/ 방이 만들어진다.
* db는 디렉토리 형태로 생성된다.
* 처음 var/방의 소유권이 root이므로
chown -R mysql.mysql var로 소유권을 바꿔준다.
- safe-mysqld & : 로 구동한다.

6. 레드햇 8.0에서는 위와 같이 할 경우 에러가 나므로(db가 생성이 안됨)아래와 같은
작업을 따로 해 주자.
- 일단 예전의 db 파일을 다운(mysql-default-db.tgz)받아서 var/ 방에서 압축을 풀자
* var/mysql에 파일이 풀려 있을 것이다.

7. 위 6번의 설정은 편법이므로 아래의 옵션으로 다시 설치해 보자
- 레드햇 8.0에서 에러가 발생하므로 static옵션을 빼고 --disable-shared옵션으로 다시 설치한다.

#!/bin/bash

CC=gcc
CFLAGS="-O3 -mpentiumpro"
CXX=gcc
CXXFLAGS="-O3 -mpentiumpro -felide-constructors -fno-exceptions -fno-rtti"
export CC CFLAGS CXX CXXFLAGS
./configure --prefix=/usr/local/mysql --with-charset=euc_kr --without-debug --enable-assembler --disable-shared
# 컴파일
make
# 설치
make install
#/usr/local/mysql/bin/mysql_install_db

8. 6, 7번의 과정을 거친후 아래와 같이 설정을 한다.
- /usr/local/mysql/share/mysql/my-medium.cnf 를 /etc/my.cnf로 복사하자.
- /usr/local/mysql/share/mysql/mysql.server를 /etc/rc.d/init.d/mysqld로 복사하자
- chmod 755로 퍼미션 변경후
- ln -s /etc/rc.d/init.d/mysqld /etc/rc.d/rc3.d/S99mysqld로 링크 걸어주자.

9. MySQL 설치후 해야 할 일(기본 port;3306)
- MySQL의 기본 암호는 없으므로 반드시 설치후 암호를 넣어준다.
- 5.번의 과정을 마친후
* mysqladmin password xxxxxx
* mysql -p
> 암호 입력하여 접속
* > use mysql;
* > show tables;
* > select * from user;
* > update user set password=password('xxxxxx');
- 기본 사용자의 각 필드도 모두 암호를 걸어주자
- 사용자가 추가되면 조건식으로 암호를 준다.
* exit
* mysqladmin -p shutdown
- 암호가 있으므로 서비스 중지도 -p옵션으로 한다.

10. 암호 잊어버렸을때
- kill psnumber로 강제로 일단 죽인다.
- ./safe_mysql -Sg & : 싱글모드로 작동하는 것과 같은 것이다.
- 위와 같이 데몬 실행시킨후 mysql에 접속한다.
- > use mysql
- > update user set password=password('new-password') where user='root';
- 상기와 같은 방법으로 루트의 패스워드를 바꾸어준다.
- 서비스 죽이고 다시 재가동하여 서비스를 재개하자. 성공

11. 암호 자동입력
- 개인 : ~/.my.cnf 파일을 만들어
[client]
user=root
host=localhost
password=xxxxxx
위와 같이 기록한 후 퍼미션을 600으로 하자

- 전체 : /etc/my.cnf
패스워드부분을 고쳐주면 된다.
당연히 퍼미션을 600으로 바꾸자.

- /etc/my.cnf에서 log-bin을 반드시 주석처리하자.
- set-variable 부분을 자세히 공부하자.


/etc/my.cnf
# Example mysql config file for medium systems.
#
# This is for a system with little memory (32M - 64M) where MySQL plays
# a important part and systems up to 128M very MySQL is used together with
# other programs (like a web server)
#
# You can copy this file to
# /etc/my.cnf to set global options,
# mysql-data-dir/my.cnf to set server-specific options (in this
# installation this directory is /usr/local/mysql/var) or
# ~/.my.cnf to set user-specific options.
#
# One can in this file use all long options that the program supports.
# If you want to know which options a program support, run the program
# with --help option.

# The following options will be passed to all MySQL clients
[client]
#password = skaTldkwjTl

# 패스워드 설정을 이곳에서 해두면 mysql 접속시 암호 입력이 필요없다.
# 이 파일은 퍼미션을 600으로 해 두자.

port = 3306
socket = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server
[mysqld]
port = 3306
socket = /tmp/mysql.sock
skip-locking
set-variable = key_buffer=16M

# > show variables 의 옵션내용들로
# ./safe_mysqld & -- 옵션으로 시작하여도 되면 아래의 옵션을 직접 고쳐서
# 사용해도 된다.
# backlog나 maxconnect 부분이 중요하다.?????

set-variable = max_allowed_packet=1M
set-variable = table_cache=64
set-variable = sort_buffer=512K
set-variable = net_buffer_length=8K
set-variable = myisam_sort_buffer_size=8M
#log-bin

# log-bin은 주석처리 하자 자세한 데이타 흐름을 분석할 때 사용하는 것으로
# 주석처리 하지 않으면 로그파일이 너무 많이 발생하여 서비스에 많은 무리가
# 온다.

server-id = 1

# Point the following paths to different dedicated disks
#tmpdir = /tmp/
#log-update = /path-to-dedicated-directory/hostname

# Uncomment the following if you are using BDB tables
#set-variable = bdb_cache_size=4M
#set-variable = bdb_max_lock=10000

# Uncomment the following if you are using Innobase tables
#innodb_data_file_path = ibdata1:400M
#innodb_data_home_dir = /usr/local/mysql/var/
#innodb_log_group_home_dir = /usr/local/mysql/var/
#innodb_log_arch_dir = /usr/local/mysql/var/
#set-variable = innodb_mirrored_log_groups=1
#set-variable = innodb_log_files_in_group=3
#set-variable = innodb_log_file_size=5M
#set-variable = innodb_log_buffer_size=8M
#innodb_flush_log_at_trx_commit=1
#innodb_log_archive=0
#set-variable = innodb_buffer_pool_size=16M
#set-variable = innodb_additional_mem_pool_size=2M
#set-variable = innodb_file_io_threads=4
#set-variable = innodb_lock_wait_timeout=50

[mysqldump]
quick
set-variable = max_allowed_packet=16M

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

[isamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[myisamchk]
set-variable = key_buffer=20M
set-variable = sort_buffer=20M
set-variable = read_buffer=2M
set-variable = write_buffer=2M

[mysqlhotcopy]
interactive-timeout

===============================================================================
===============================================================================
1.
- /tmp/mysql.sock : socket으로 동작한다.(port나 tcp/ip보다 빠르다)
- mysql의 명령어는 PHP에 함수로 존재한다. 같은 작업을 한다.

2.
- ./safe_mysqld & : 서비스 시작
- mysql : 접속
- mysqladmin : 외부에서 관리

3. mysqladmin
- version : 버전정보.
- variables : 다양한 옵션상태를 보여준다
이러한 옵션은 ./safe_mysql & 구동시 옵션을 주어 변경할수 있다.
- shutdown : service stop
- password xxxxx
- reload
- refresh

4. mysql 명령어 입력방식
- mysqladmin create linux : mysqladmin 명령어 이용
- mysql> create database linux : 접속하여 이용
- mysql -e "use linux;create table xx" : shell 상에서 이용
* mysql linux -e "create table xx : linux로 바로 접속
- mysql < test.batch : 파일을 불러들여 실행

** 마지막 방법을 이용하자. ^^ 물론 명령어 사이에는 세미콜론(;)

5. 여러 간단한 명령어들
- create
- drop table tablename
- use
- explain, desc(ribe), show columns from grade

ex) create database linux, use linux, create table grade, desc grade

6. where 옵션에서
- where Binary id="wateR" : binary 옵션으로 대소분자 구분하여 검색
- where id like '%rt%' : id에 rt가 들어가면 검색
- where id REGEXP '[a-z]+[0-9]+' : REGEXP 정규표현식 사용가능

7. 테이블 수정하기 (TABLE ALTER )
- ALTER TABLE a RENAME bbs : TABLE 이름을 a에서 bbs로 변경
bbs ADD name varchar(10) : name column 추가
bbs DROP name : name column 삭제
bbs ADD name varchar(10) AFTER num : num column 뒤에 추가
bbs ADD name varchar(10) FIRST : 가장 앞에 추가

- ALTER TABLE bbs DROP id, ADD id varchar(10) FIRST
: ,를 이용하여 명령어를 순차적으로 사용가능

- column 자료를 보존하면서 위치 옮기기
( id column을 num column뒤로)
1. ALTER TABLE bbs ADD id_temp varchar(10) AFTER num
2. update bbs set id_temp=id
3. ALTER TABLE bbs DROP id;
4. ALTER TABEL bbs CHANGE id_temp id varchar(10)

8. 간단히 테이블 복사하기
- create table new_table as select * from old_table

9. index 만들기(검색속도 개선)
- table 생성시 index 작성
create table user(id char(8) not null, passwd char(8) not null, name
char(8) not null, ...... , INDEX(id));
: id를 인덱스로 만듬
- ALTER 이용하여 생성 삭제 - ALTER TABLE user DROP INDEX id : id INDEX삭제
- ALTER TABLE user ADD INDEX id_pass_idx(id,passwd) : id,passwd이용하여 인덱스생성
- ALTER TABLE user ADD INDEX id_idx(id) : id 이용하여 인덱스 생성

10. 웹브라우저로 관리
- phpMyAdmin-2xxx를 다운받은후 기본 서비스 디렉토리에 적당하게 압축을 푼다
- config.inc.php에서 mysql root 암호를 적어주자
- 웹브라우저로 압축푼 디렉토리에 접속하여 관리하자

===============================================================================
외부파일로 작성하기, 외부파일 불러오기 (db의 백업과 복구)
===============================================================================
1. mysqldump 이용(mysql 사이의 백업과 복구)
- mysqldump database_name > output_filename : 외부파일로 백업
- mysql database_name < input_filename : 백업파일을 이용한 복구
* mysql 버전이 같으면 var방만 복사해도 가능함

2. SELECT INTO OUTFILE과 LOAD DATA INFILE(ASCII file 이용)

- SELECT * INTO OUTFILE '/var/lib/mysql/db_backup.db'
FIELDS TERMINATED BY ''
FROM table_name;

: field 구분자는 를 이용한다. 없으면 탭으로 표시된다.

- LOAD DATA INFILE '/var/lib/mysql/db_backup.db'
INTO TABLE table_name(num, id, name)
FIELDS TERMINATED BY ''
LINES TERMINATED BY 'rn';

: 외부파일에서 공백은 비어있는 문자로 인식하므로 NULL값을 주기 위해서
외부파일의 공백에 N 을 입력시켜 두면 된다.
: table의 column보다 읽어들이는 파일의 필드수가 작을때는 필드값을 하나하나 적어주자
: 구분자 선택이 가능하며 마지막 라인은 윈도우에서 mysql사용할때 필요하다.
===============================================================================
Mysql Test

1. 계정사용자를 추가(자신의 db만 접속가능하여야 하며, 유동ip를 쓰는 관계로 모든 컴퓨터에서
접속가능하게, zeus:wpdntm)
- insert into user values('%','zeus',password('wpdntm'),'N','N','N','N','N','N',
'N','N','N','N','N','N','N','N');
- insert into db values('%','디비명','zeus','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
: 모든 컴퓨터에서 접속가능하게 해야 하므로 빈칸이나, %을 적어준다.
: 'N'은 14개 ,'Y'는 10개 적어준다.
: 자신의 db만 접속해야 하므로 user에는 'N',을 db에는 'Y'를 적어준다.

2. 기본 설정으로는 show databases;로 모든 database를 볼수있게 되어있다. 자신의 db만 볼수
있게 시작옵션을 조정하라, 그리고 log파일의 경로설정과 pid파일 경로까지
- ./safe_mysqld --safe-show-database --log=/var/log/mysqld.log --pid=/var/run/mysqld.pid &
: log나 pid파일은 허가권이 제대로 되어있지 않으므로 일단 touch 로 파일을 만든후에
소유권을 mysql로 바꾸어 준다. 안되면 디렉토리를 775로 바꾸거나

3. 최대 접속자 수를 늘리자
- ./safe_mysqld -O max_connections=500 &
- my.cnf파일 수정(추천) : set_variable=max_connections=500

4. Replication
- master와 slave의 데이타가 일단 완전히 동일하여야 한다.
: mysqldump를 사용하거나 통채로 디렉토리를 복사한다.
: 이때 slave의 모든 database를 다 지워주고 작업하자.
- my.cnf
: master
log-bin : 이 파일을 이용하여 미러링 하므로 반드시 주석제거
server-id=1 : 중복되지만 않게 적어주자.
: slave
master-host= : master IP or hostname
master-user=root
master-password=xxxxx
master-prot=3306 : 다른포트사용한다면 고쳐주자
server-id=2 : master와 다르게 적자

5. Replication Test
- slave에서 실행 show slave status
: Log_File 과 Pos항목이 master와 동일해야 한다.
- slave에서 실행 show processlist
: State : reading master update - 정상,
: : connecting master update - 에러

===============================================================================
1. 보안관련 다시한번 정리
===============================================================================
1. 설치후 암호 입력
- MySQL의 기본 암호는 없으므로 반드시 설치후 암호를 넣어준다.
- 5.번의 과정을 마친후
* mysqladmin password xxxxxx
* mysql -p
> 암호 입력하여 접속
* > use mysql;
* > show tables;
* > select * from user;
* > update user set password=password('xxxxxx');
- 기본 사용자의 각 필드도 모두 암호를 걸어주자
- 사용자가 추가되면 조건식으로 암호를 준다.
* exit
* mysqladmin -p shutdown
- 암호가 있으므로 서비스 중지도 -p옵션으로 한다.

2. 계정사용자를 추가(자신의 db만 접속가능하여야 하며, 유동ip를 쓰는 관계로 모든 컴퓨터에서
접속가능하게, zeus:wpdntm)
- insert into user values('%','zeus',password('wpdntm'),'N','N','N','N','N','N',
'N','N','N','N','N','N','N','N');
- insert into db values('%','디비명','zeus','Y','Y','Y','Y','Y','Y','Y','Y','Y','Y');
: 모든 컴퓨터에서 접속가능하게 해야 하므로 빈칸이나, %을 적어준다.
: 'N'은 14개 ,'Y'는 10개 적어준다.
: 자신의 db만 접속해야 하므로 user에는 'N',을 db에는 'Y'를 적어준다.

3. 기본 설정으로는 show databases;로 모든 database를 볼수있게 되어있다. 자신의 db만 볼수
있게 시작옵션을 조정하라, 그리고 log파일의 경로설정과 pid파일 경로까지
- ./safe_mysqld --safe-show-database --log=/var/log/mysqld.log --pid=/var/run/mysqld.pid &
: log나 pid파일은 허가권이 제대로 되어있지 않으므로 일단 touch 로 파일을 만든후에
소유권을 mysql로 바꾸어 준다. 안되면 디렉토리를 775로 바꾸거나

4. 파일관리
- /usr/local/mysql/bin 700으로 해두자. mysql 명령어는 755로 해두어야지..
- chattr -R +i /usr/local/mysql/bin 방은 chattr로 변조 불가
- chmod +t /var/lib/mysql : sticky bit로 설정