IT 인프라 운영

• MySQL Version : 5.1.73
• OS : CentOS 7

1. 기존(MyISAM으로 설정된) DB를 Dump 백업합니다.

# mysqldump -u {로그인아이디} -p {백업할 데이터베이스 이름} > {덤프파일 명}

# mysqldump -u root -p test_db > test_db.sql

엔진이 정상적으로 변경 되었음에도 불구하고 database에 프로시저가 덤프가 안떠져서 소스코드나 , 어플이 정상작동 안할수있다.

아래 옵션을 주고 덤프하면 procedure, function, trigger 를 포함하여 백업이 가능하다.

# mysqldump --routines  --trigger -u유저이름 -p패스워드 DB명 > 백업파일

# mysqldump --routines --trigger -u root -p les > les.sql

2. 복원시 InnoDB로 적용하기 위해 덤프파일의 내용을 변경합니다.

# sed -e 's/ENGINE=MyISAM/Engine=InnoDB/g' {덤프파일 명} > {새로 적용할 덤프파일 명}

# sed -e 's/ENGINE=MyISAM/Engine=InnoDB/g' test_db.sql > test_db2.sql

3. mysql 콘솔에 접속 후 기존의 데이터베이스를 삭제하고 새로 생성합니다.

mysql> drop database {삭제할 데이터베이스 명}

mysql> drop database test_db

mysql> create database {생성할 데이터베이스 명}

mysql> create database test_db

4. 변경된 덤프파일을 이용해 데이터 베이스를 복원합니다.

# mysql -u {로그인 아이디} -p {복원할 데이터베이스 명} < {새로 적용할 덤프파일 명}

# mysql -u root -p test_db < test_db2.sql

출처 : http://www.33gram.com/

[mysql 버전별 컨피그값]

mysql은 버전별로 컨피그 옵션이 다르다.

그렇기 때문에 해당 버전별로 컨피그 옵션을 줘야 설치가 정상적으로 된다.

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[mysql 5.5.29 설치]

1) mysql 사용자 및 그룹 추가

# groupadd mysql
# useradd -g mysql mysql

2) 소스코드파일 압축해제 및 설치(/usr/local/src/ 디렉토리에 mysql-5.5.10.tar.gz 파일을 저장했다.)

# cd /usr/local/src/

# tar xvfz mysql-5.5.10.tar.gz
# cd mysql-5.5.10

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DSYSCONFDIR=/etc -DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1

설명)

mysql 설치 디렉토리는 /usr/local/mysql

mysql DB 디렉토리는 /usr/local/mysql/data

mysql 기본언어셋은 utf8

mysql 사용포트는 3306

# make && make install

# cp support-files/my-huge.cnf /etc/my.cnf            <---mysql 설정파일 복사

# cp support-files/mysql.server /etc/init.d/mysqld    <---mysql 실행데몬 복사     
# vi /etc/init.d/mysqld
--------------------------------------
datadir=/usr/local/mysql/data              <---DB디렉토리 지정 하고 저장
---------------------------------------

# chmod 755 /etc/init.d/mysqld             <---mysql 데몬 실행 권한 부여

# chown -R mysql:mysql /usr/local/mysql              

# /usr/local/mysql/scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data                           <---DB생성

# /etc/init.d/mysqld start                     <---mysql 구동 (시작)  또는 /usr/local/server/mysql/bin/mysqld_safe &

# chkconfig --add mysqld                    

# /usr/local/server/mysql/bin/mysqladmin -u root password 암호           <---mysql root 패스워드 설정

# ln -s /usr/local/server/mysql/bin/mysql /usr/bin/                           <---링크

# ln -s /usr/local/server/mysql/bin/mysqldump /usr/bin/

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[mysql 5.0.96]

./configure --prefix=/usr/local/mysql --localstatedir=/usr/local/mysql/data --with-charset=euckr --with-mysql-user=mysql --with-extra-charsets=all --enable-thread-safe-client        -> 언어셋을 utf8로 바꾸고 싶다면 euc_kr부분을 바꿔주면된다.

#make

#make install

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[mysql 5.6.14]

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DSYSCONFDIR=/etc -DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1            -> 언어셋을 euc_kr로 바꾸고 싶다면 utf8부분을 바꿔주면된다.

# gmake

# gmake install

----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

# Example MySQL config file for very large systems.

#

# This is for a large system with memory of 1G-2G where the system runs mainly

# MySQL.

#

# MySQL programs look for option files in a set of

# locations which depend on the deployment platform.

# You can copy this option file to one of those

# locations. For information about these locations, see:

# http://dev.mysql.com/doc/mysql/en/option-files.html

#

# In this file, you can use all long options that a program supports.

# If you want to know which options a program supports, run the program

# with the "--help" option.

# The following options will be passed to all MySQL clients

[client]

#password       = your_password

port            = 3306

socket          = /tmp/mysql.sock

# Here follows entries for some specific programs

# The MySQL server

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 64M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

slow_query_log = 10

slow_query_log_file = /usr/local/mysql/data/mysql-slow-queries.log

#skip-grant-tables

# Try number of CPU's*2 for thread_concurrency

thread_concurrency = 8

# Don't listen on a TCP/IP port at all. This can be a security enhancement,

# if all processes that need to connect to mysqld run on the same host.

# All interaction with mysqld must be made via Unix sockets or named pipes.

# Note that using this option without enabling named pipes on Windows

# (via the "enable-named-pipe" option) will render mysqld useless!

#skip-networking

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 1

# 소켓에러나 mysql 시작 안될시에 추가해줄것

#datadir=/usr/local/mysql/data

# Replication Slave (comment out master section to use this)

#

# To configure this host as a replication slave, you can choose between

# two methods :

#

# 1) Use the CHANGE MASTER TO command (fully described in our manual) -

#    the syntax is:

#

#    CHANGE MASTER TO MASTER_HOST=<host>, MASTER_PORT=<port>,

#    MASTER_USER=<user>, MASTER_PASSWORD=<password> ;

#

#    where you replace <host>, <user>, <password> by quoted strings and

#    <port> by the master's port number (3306 by default).

#

#    Example:

#

#    CHANGE MASTER TO MASTER_HOST='125.564.12.1', MASTER_PORT=3306,

#    MASTER_USER='joe', MASTER_PASSWORD='secret';

#

# OR

#

# 2) Set the variables below. However, in case you choose this method, then

#    start replication for the first time (even unsuccessfully, for example

#    if you mistyped the password in master-password and the slave fails to

#    connect), the slave will create a master.info file, and any later

#    change in this file to the variables' values below will be ignored and

#    overridden by the content of the master.info file, unless you shutdown

#    the slave server, delete master.info and restart the slaver server.

#    For that reason, you may want to leave the lines below untouched

#    (commented) and instead use CHANGE MASTER TO (see above)

#

# required unique id between 2 and 2^32 - 1

# (and different from the master)

# defaults to 2 if master-host is set

# but will not function as a slave if omitted

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

[mysqlhotcopy]

interactive-timeout

[mysql 리플리케이션 이란?]

MySQL Replication 설정을 해놓으면, 마스터 서버에 갱신되는 모든 자료를 슬레이브 서버에 저장하게 된다.

실시간 백업으로 사용할수 있지만 마스터 서버에서 자료를 삭제했다면 슬레이브 서버에서도 자료가 삭제되므로, 

하드디스크 문제같은 물리적인 문제에 대한 대처방안으로 생각하는것이 좋다.

또한 마스터서버에선 쓰기, 슬레이브 서버에선 읽기만 할경우 DB의 부하 분산에 도움이 된다.

[mysql 리플리케이션 설정방법]

[Master 서버에서 설정]

# vi /etc/my.cnf        -> 해당 파일을 열어 server-id =1 부분과 log-bin=mysql-bin 확인 및 설정완료후 mysql 재시작

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin       ->  (바이너리 로그 기준으로 슬레이브 서버에 리플리케이션하기때문에 반드시 생성이 되도록 설정이 되어있어야 합니다.)

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 1           -> 보통 1이 기본값이며, 슬레이브서버와 다르게 설정하면 된다.

binlog_do_db = db명     ->  리플리케이션 할 DB명을 적어준다. (생략할경우 전체 DB를 리플리케이션함)

# mysql -u root -p         -> mysql 에 접속하여 슬레이브에서 접속할 사용자를 추가하거나 root 로 설정

Mysql > show master status;        -> 해당명령어 입력시 아래 내용이 나오며 mysql-bin 과 Position 번호 확인

+-----------------------+-----------+-------------------+-----------------------+

| File                          | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+-----------------------+-----------+-------------------+-----------------------+

| mysql-bin.000040 |       106  |                          |                                |

+-----------------------+-----------+-------------------+-----------------------+

1 row in set (0.00 sec)

# mysqldump -u root -p -A > mysql.dump        -> 리플리케이션 설정을 위해 마스터 서버의 데이터를 백업받아 슬레이브 서버에서 복구해야합니다.

# rsync -avzPog /usr/local/src/mysql.dump root@115.68.200.69:/usr/local/src/        -> 백업이후 슬레이브서버로 해당 백업파일을 보냅니다.

[Slave 서버에서 설정]

# vi /etc/my.cnf        -> 마스터 서버와 마찬가지로 해당파일을 수정해줍니다.

# Replication Master Server (default)

# binary logging is required for replication

log-bin=mysql-bin       ->  (바이너리 로그 기준으로 슬레이브 서버에 리플리케이션하기때문에 반드시 생성이 되도록 설정이 되어있어야 합니다.)

# required unique id between 1 and 2^32 - 1

# defaults to 1 if master-host is not set

# but will not function as a master if omitted

server-id       = 2        -> 마스터서버와 다르게 설정하여 줍니다.

binlog_do_db = db명        -> 특정 DB만 리플리케이션 할경우 추가, 생략시 전체 DB 리플리케이션

relay-log  = slave-relay-bin   

log-slave-updates

slave-skip-errors = all    -> 모든 에러를 무시하고 싱크를 맞춤 (해당옵션 안줄경우 에러발생시 리플리케이션 정상작동 안함)

read_only    -> 이 옵션을 줘야 슬레이브 서버에서 읽기만 가능. 슬레이브 서버에 데이터가 들어가면 안됨

# mysql -u root -p        -> mysql 에 접속하여 아래처럼 설정을 합니다.

Mysql > CHANGE MASTER TO MASTER_HOST='마스터 서버 IP', MASTER_USER='접속할 계정', MASTER_PASSWORD='패스워드', MASTER_PORT=3306, MASTER_LOG_FILE='mysql-bin.000040', MASTER_LOG_POS=106;

Mysql > start slave;

Mysql > show master status\G        -> 해당명령어로 상태확인

[mysql bin파일로 복구방법]

bin 파일이 있는 디렉토리로 들어가서 빈파일을 확인한다. (보통 /usr/local/mysql/data)

# /usr/local/mysql/bin/mysqlbinlog mysql-bin.0* > /usr/local/src/backup.sql    -> 빈파일0번부터 마지막까지 backup.sql 이라는 파일에 전부 넣는다는 뜻

# cd /usr/local/src        -> backup.sql 파일이 있는 위치로 이동

# mysql -uroot -p         -> mysql에 접속

mysql> source backup.sql        -> mysql에 접속한 상태에서 해당명령어 실행하면 bin파일로 복구가 된다.

[Mysql 5.6.14 버전 설치방법]

[mysql 설치]

# tar zxvf mysql-5.6.14.tar.gz    -> 압축풀기

# cd mysql-5.6.14    -> 해당 디렉토리로 이동

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DMYSQL_DATADIR=/usr/local/mysql/data -DMYSQL_UNIX_ADDR=/tmp/mysql.sock -DSYSCONFDIR=/etc -DMYSQL_TCP_PORT=3306 -DMYSQL_USER=mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DWITH_EXTRA_CHARSETS=all -DENABLED_LOCAL_INFILE=1 -DWITH_INNOBASE_STORAGE_ENGINE=1 -DWITH_ARCHIVE_STORAGE_ENGINE=1 -DWITH_BLACKHOLE_STORAGE_ENGINE=1            -> 언어셋을 euc_kr로 바꾸고 싶다면 utf8부분을 바꿔주면된다.

# gmake

# gmake install

# cd /usr/local/mysql/    -> 설치 된 mysql디렉토리로 이동

# useradd -u400 -g400 -d /usr/local/mysql/    -> mysql 유저생성

# useradd -u400 -g400 -d /usr/local/mysql -s /bin/false mysql   

# ./scripts/mysql_install_db --user=mysql --datadir=/usr/local/mysql/data    -> mysql 데이터베이스 생성하며, mysqldata디렉토리 설정과정

# mv ./my.cnf /etc/my.cnf    -> my.cnf 파일 위치이동

# chown -R mysql:mysql /usr/local/mysql    ->  ★ mysql 은 권한이 mysql로 되어있지 않으면 구동이 안되기때문에 소유권을 바꾸어줌.

[Mysql 주요 기능을 위한 PATH 설정]

# ln -s /usr/local/mysql/bin/mysql /usr/bin/mysql

# ln -s /usr/local/mysql/bin/mysqldump /usr/sbin/mysqldump

# ln -s /usr/local/mysql/bin/mysql_config /usr/sbin/mysql_config

# ln -s /usr/local/mysql/bin/mysqladmin /usr/sbin/mysqladmin

# ln -s /usr/local/mysql/support-files/mysql.server /etc/init.d/mysql    -> /etc/init.d/mysql 로 구동가능

# chkconfig --add mysql

# chkconfig --level 24 mysql off

이후 /etc/init.d/mysql start 로 구동시키면 끝!

[mysql 백업 및 복구형식]

백업형식 : ./mysqldump -u DB계정명 -p 데이터베이스명 테이블명 > 저장할파일명

복구형식 : ./mysql -u DB계정명 -p 데이터베이스명 < 저장한파일명

[mysql 백업방법]

# cd /usr/local/mysql/bin    ->  mysql의 bin 디렉터리로 이동후 아래 명령어 실행

./mysqldump -u root -p -A > alldump.sql    ->  mysql 전부 백업

./mysqldump -u root -p --lock-all-tables -A > test.sql     ->  테이블에 Lock이 걸려서 덤프가 안떠질경우 해당명령어로 백업

./mysqldump -u root -p  DB명 > dump.sql      ->  특정 데이터베이스 백업

./mysqldump -u root -p --databases DB1 DB2 DB3  > dump.sql      ->  다중 데이터베이스 백업 ( 다중디비 백업시에 --databases 명령어 이용 )

./mysqldump -u root -p DB명 TABLE명 > DB1_TABLE1.sql         ->  특정 DB 의 특정TABLE 백업

./mysqldump -u root -p DB명 TABLE1 TABLE2 > DB1_TABLE12.sql         -> 특정DB의 테이블 1,2 백업

[mysql 복구]

mysql  -u root -p < dump.sql        -> 모든DB를 덤프떳을 경우

mysql -u root -p DB명 < dump.sql    -> 특정DB만 덤프떳을 경우 (특정 DB만 복구할때는 같은 이름의 DB를 생성해놓고 해당명령어를 실행해야함)

[위명령어로 안될경우]

dump받은 파일이 있는 위치로 이동한 후 mysql 에 접속하여 아래 해당명령어 실행

mysql > source alldump.sql        -> 해당명령어로 모든 DB 복구 (올덤프 떳을경우)

mysql > use 해당DB명        -> 해당명령어로 복구할 DB선택 이후 아래명령어로 복구진행 (DB가 없다면 생성해야함)

mysql > source dump.sql

[mysql 루트 패스워드 초기화 방법]

# /etc/init.d/mysqld stop    -> 실행중인 디비를 중지시킴

# vi /etc/my.cnf    -> 파일을 열고 아래내용 수정

[mysqld]

port            = 3306

socket          = /tmp/mysql.sock

skip-external-locking

key_buffer_size = 384M

max_allowed_packet = 1M

table_open_cache = 512

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size = 8M

myisam_sort_buffer_size = 64M

thread_cache_size = 8

query_cache_size = 32M

slow_query_log = 10

slow_query_log_file = /usr/local/mysql/data/mysql-slow-queries.log

skip-grant-tables    <-- 이 내용추가후 :wq로 저장하고 나오기

# /etc/init.d/mysqld start    ->  MySQL 실행

# mysql -uroot mysql    ->  MySQL 접속후 아래 명령어 실행

update user set password=password('root패스워드') where user='root';

# vi /etc/my.cnf    ->  파일을 열고 아까 추가해 주었던 skip-grant-tables  지우기

# /etc/init.d/mysql restart 

# mysql -u root -p    ->  이후에 다시 설정한 패스워드로 접속하기!

[mysql 패스워드 변경방법]

mysql> use mysql;

mysql> update user set password=password("password") where user = 'root';

mysql> flush privileges;

mysql> create user 유저아이디@localhost identified by '비밀번호';    -> @뒤에 localhost 부분은 로컬에서만 접근이 가능하다는 의미, 외부에서 접근 불가

Query OK, O rows affected (0.00 sec)

[사용자 추가시 외부에서 접근 가능하도록 설정]

mysql> create user '유저아이디'@'%' identified by '비밀번호';    -> '%' 부분은 localhost 및 외부에서 접근이 가능하다는 의미.

Query OK, O rows affected (0.09 sec)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[localhost에서만 접근가능하도록 변경]

mysql> insert into user(host,user,password) values ('localhost','유저아이디',password('비밀번호'));

Query OK, 1 rows affected, 3 warnings (0.06 sec)

[외부에서도 접근가능하도록 변경]

mysql> insert into user(host,user,password) values ('%','유저아이디',password('비밀번호'));

Query OK, 1 rows affected, 3 warnings (0.00 sec)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[userid 계정에게 test 데이터베이스의 모든 테이블에 select, insert, update 권한부여]

[userid 계정에게 모든 데이터베이스의 모든 테이블에 권한부여]

mysql> grant all privileges on *.* to userid@localhost identified by 'password' with grant option;    

Query OK, 0 rows affected (0.00 sec)

[데이터베이스 추가 및 권한 주기]
mysql> CREATE DATABASE 데이터베이스명;
mysql> GRANT ALL privileges ON 데이터베이스명.* TO 유저명@localhost IDENTIFIED BY '유저패스워드';
mysql> GRANT ALL privileges ON 데이터베이스명.* TO 유저명@127.0.0.1 IDENTIFIED BY '유저패스워드';
mysql> GRANT ALL privileges ON 데이터베이스명.* TO 유저명@'%' IDENTIFIED BY '유저패스워드';    -> 외부에서 해당DB로 접근가능

[userid 계정의 모든 데이터베이스 사용권한 제거]

mysql> revoke all on *.* from userid@localhost;

Query OK, 0 rows affected (0.00 sec)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[특정 IP에서만 접근 가능하도록 설정]

mysql> grant all privileges on *.* to '유저아이디'@'115.68.27.76' identified by '비밀번호';  

Query OK, 0 rows affected (0.00 sec)

[특정 IP대역 에서만 접근 가능하도록 설정]

mysql> grant all privileges on *.* to '유저아이디'@'115.68.%' identified by '비밀번호';  

Query OK, 0 rows affected (0.00 sec)

[모든 IP의 접근 허용]

mysql> grant all privileges on *.* to '유저아이디'@'%' identified by '비밀번호';  

Query OK, 0 rows affected (0.00 sec)

------------------------------------------------------------------------------------------------------------------------------------------------------------------------

[mysql 트러블슈팅]

mysql> insert into user (host, user, password) values ('%' , '유저아이디', password('비밀번호'));

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value        -> 명령어를 잘 입력하였는데도 이처럼 에러가 날경우

[mysql 슬로우 쿼리 설정]

슬로우 쿼리 설정은 쿼리 응답시간이 오랫동안 지연되었을 경우 시간과 해당 쿼리를 로그에 남기도록 하는 기능입니다.

my.cnf 파일의 [mysqld] 항복에 아래 내용을 입력 후 mysql 재시작을 해줍니다.

[MySQL 5.1.34 이상 버전]

slow_query_log = 10

slow_query_log_file = /usr/local/mysql/data/mysql-slow-queries.log

[MySQL 4.x 이상 버전]

long_query_time = 10

log-slow-queries = /usr/local/mysql/data/mysql-slow-queries.log

[MySQL 3.x 이하 버전]

set-variable = long_query_time = 10

log-slow-queries = /usr/local/mysql/data/mysql-slow-queries.log

# mysqladmin -u root -p proc stat -i1 명령어로 어떤 디비에서 쿼리 타임이 높게발생 되는지 확인합니다.

[mysql 테이블 손상 확인 및 복구방법]

mysql> use DB명    ->  작업할 DB 선택

mysql> check table 테이블명    ->  확인할 테이블을 체크

mysql> use DB명    ->  작업할 DB 선택

mysql> repair table 테이블명    ->  복구할 테이블 복구

[mysql DB 손상 확인 명령어]

mysqlcheck -uroot -p --check --database DB명

mysqlcheck -uroot -p DB명