꼬반 Blog

MySQL Enterprise root user default password

회사 서버에 오랜만에 MySQL Enterprise 5.7.20 버전을 설치하게 되었다. 

요즘은 MariaDB나 NoSQL 같은 오픈소스 위주로 사용하다 보니.

일단 Oracle 에서 Enterprise 버전을 받는것 부터 좀 버벅 대다가 다운받고

설치는 zip 파일을 풀면 rpm 파일들이 있는데

yum localinstall 명령어로 common -> lib -> devel -> client -> server 순으로 설치해준다.

rhel 7 기준으로 systemctl 명령어를 사용할텐데 systemd 데몬이 바로 생성되어 있지 않다.

이를 위해서는 mysqld_pre_systemd 명령어를 실행시켜주면 mysqld 로 systemd 데몬이 생성된다.

이후 systemctl start mysqld 로 데몬을 실행시켜주고

mysql -u root -p 로 기본으로 접속해서 확인하려고 하면?

패스워드가 필요하다!??

보통 community 버전 혹은 오픈소스 MariaDB는 그간 설치후 최초에는 Root 패스워드가

설정되어 있지 않았는데 갑작스러운 상황에 당황.

mysql_secure_installation (기본 보안 설정을 해준다.) 명령어를 사용해도 root 패스워드를 요구하고..

구글신에 검색해본 결과!

https://www.percona.com/blog/2016/05/18/where-is-the-mysql-5-7-root-password/

위 내용을 찾았다. 즉 /var/log/mysqld.log 의 [NOTE] 부분에 최초 실행시 설정되는 임시 패스워드가 

포함되어 있다.

해당 로그에서 임시패스워드를 사용하면 mysql 에 접속이 가능하다.

다만 임시패스워드 상태에서는 접속만 가능하고 다른 작업은 할 수 없기 때문에 

mysql_secure_installation 을 사용하여 기본 보안 설정을 진행하거나 (root 패스워드 변경이 포함되어 있음, 권장)

임시 패스워드를 변경하고 사용하면 된다.

단 패스워드 정책에도 보안등급이 적용되어 너무 단순한 패스워드는 사용할 수 없으니 참고할 것

일반적으로 mysql, mariadb 등을 모니터링 할때는 기본 cli 명령어인

mysqladmin -u 사용자 -p stat proc

명령어로 확인할 수 있다. mysql cli 안에서는

mysql> show full processlist;

명령어를 활용한다.

이 명령어는 기본적으로 root 사용자는 모든 사용자의 process 가 보이지만

특정 DB에 특정 유저만 권한 설정을 하는 경우, 기본적으로 자신의 process 만 보인다.

각 DB에 여러 유저가 작업을 하고 해당 유저간 모니터링을 위해 process를 볼 수 있어야 한다면

mysql> grant select, process on DB.table to '사용자'@'host' identified by '패스워드';

로 권한을 부여한다.

권한 변경 후에는 꼭

mysql> flush privileges;

를 해주어야 적용이 된다.

[MySQL] MySQL 기본 명령어 정리 데이터베이스
by 권남 2004/10/06 13:34 kwon37xi.egloos.com/1634694 덧글수 : 22
http://wiki.kwonnam.pe.kr/database/mysql/basic

MySQL의 기본 명령어와 SQL문 정리. 내가 RDBMS에 완전 문외한이기 때문에 기본 SQL쿼리까지...
Redhat Linux 7.3, MySQL 3.23.58 에서 테스트함.
설치는 Redhat 기본 제공 RPM으로 했다.

데이터베이스 접속
$ mysql -u 사용자명 -p dbname

설치 직후에는 root 사용자에 비밀번호가 없으므로 다음과 같이 접속하여 MySQL을 관리할 수 있다.

$ mysql -u root mysql


비밀번호 변경
MySQL을 설치한 직후에는 root 계정에 암호가 지정되어 있지 않다.
다음 세가지 방법으로 비밀번호를 변경 할 수 있다.

mysqladmin이용
$ mysqladmin -u root password 새비밀번호

update문 이용
$ mysql -u root mysql

mysql> UPDATE user SET password=password('새비밀번호') WHERE user='root';
mysql> FLUSH PRIVILEGES;


Set Password 이용
SET PASSWORD FOR root=password('새비밀번호');


일단 root 비밀번호가 설정된 상태에서는 mysql이나 mysqladmin 명령을 실행할 때 -p 옵션을 붙여주고 기존 비밀번호를 입력해야만 한다.

사용자 추가/삭제
mysql> GRANT ALL PRIVILEGES ON dbname.* TO username@localhost IDENTIFIED BY 'password';

username 이라는 사용자를 password라는 비밀번호를 갖도록 하여 추가한다. username은 dbname이라는
데이타베이스에 대해 모든 권한을 가지고 있다.
username 사용자는 로칼 호스트에서만 접속할 수 있다. 다른 호스트에서 접속하려면
GRANT ALL PRIVILEGES ON dbname.* TO username@'%' IDENTIFIED BY 'password';

위를 또한 번 실행한다. '%'에서 홑따옴표를 주의한다.
추가 : '%'를 호스트네임으로 지정해도 모든 호스트에서 접속할 수 없었다. 각 호스트별로 다 지정해야 했다.
아래 댓글에서 rukikuki님이 알려주셨습니다.
GRANT ALL PRIVILEGES ON testdb.* TO 'username'@'%' IDENTIFIED BY 'password' ;
처럼 'username' 이런식으로 홑따옴표를 username 에도 붙여야 하더군요..

http://tech.zhenhua.info/2009/01/mysql-error-104528000-access-denied-for.html 에 따르면 '%'란 localhost 를 제외한 모든 호스트를 뜻한다고 한다. 즉, localhost에 대해서는 명시적으로 따로 권한을 지정해야 한다.

불필요한 사용자 삭제는
mysql> DLETE FROM user WHERE user='username';
mysql> FLUSH PRIVILEGES;


데이터베이스 생성/보기

데이터베이스를 생성하고,
mysql> CREATE DATABASE dbname;

현재 존재하는 데이터베이스 목록을 보여준다.
mysql> SHOW DATABASES;

특정 데이타베이스를 사용하겠다고 선언한다.
mysql> USE dbname;

쓸모 없으면 과감히 삭제한다.
mysql> DROP DATABASE [IF EXISTS] dbname;

IF EXISTS 옵션은 비록 데이타베이스가 없더라도 오류를 발생시키지 말라는 의미이다.


테이블 생성/보기

테이블을 생성하고,
mysql> CREATE TABLE tablename (
column_name1 INT,
column_name2 VARCHAR(15),
column_name3 INT );

현재 데이타베이스의 테이블 목록을 보고
mysql> SHOW TABLES;

테이블 구조를 살펴본다.
mysql> EXPLAIN tablesname;
혹은
mysql> DESCRIBE tablename;

이름을 잘못 지정했으면 이름을 변경할 수도 있다.
mysql> RENAME TABLE tablename1 TO tablename2[, tablename3 TO tablename4];

필요 없으면 삭제한다.
mysql> DROP TABLE [IF EXISTS] tablename;



현재 상태 보기

mysql> status

--------------
mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686)

Connection id: 26
Current database: study
Current user: study@localhost
Current pager: stdout
Using outfile: ''
Server version: 3.23.58
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: latin1
Server characterset: euc_kr
UNIX socket: /var/lib/mysql/mysql.sock
Uptime: 2 hours 9 min 59 sec

Threads: 1 Questions: 160 Slow queries: 0 Opens: 28 Flush tables: 1
Open tables: 1 Queries per second avg: 0.021
--------------



INSERT
mysql> INSERT INTO tablename VALUES(값1, 값2, ...);

혹은

mysql> INSERT INTO tablename (col1, col2, ...) VALUES(값1, 값2, ...);


SELECT
mysql> SELECT col1, col2, ... FROM tablename;

컬럼명을 *로 하면 모든 컬럼 의미.

mysql> SELECT col1 AS '성명', col2 AS '국어점수' FROM grade;

컬럼의 이름을 바꿔서 출력.

mysql> SELECT * FROM tablename ORDER BY col1 DESC;
mysql> SELECT col1, korean + math english AS '총점' FROM tablename ORDER BY '총점' ASC;

DESC는 내림차순 ASC는 오름차순.

mysql> SELECT * FROM grade WHERE korean < 90;

조건줘서 SELECT.

mysql> SELECT * FROM grade LIMIT 10;

결과중 처음부터 10개만 가져오기

mysql> SELECT * FROM grade LIMIT 100, 10;

결과중 100번째부터 10개만 가져오기. 첫번째 레코드는 0번 부터 시작한다.

UPDATE
mysql> UPDATE tablename SET col1=새값 WEHER 조건


DELETE
mysql> DELETE FROM tablename WEHRE 조건


mysql에서 쿼리 결과 세로로 보기
-E 옵션을 줘서 실행한다.
$ mysql -E -u root -p mysql


mysql에서 발생한 오류나 경고 다시 보기
mysql> show errors;
mysql> show warnings;

**출처 : http://intomysql.blogspot.kr/2010/12/mysqldump.html
IntoMysql 블로그**

**mysqldump 의 사용법**
백업뿐만 아니라, 데이터의 이관이나 복제등과 같이 어떤 MySQL 서버에서 다른 서버로 데이터를 옮기는 경우
MySQL 패키지에 기본적으로 포함되어 있는 mysqldump라는 도구를 자주 사용하게 된다.

**mysqldump 명령과 옵션의 기본 구성**
mysqldump [DB 접속 정보] [dump 옵션] [dump 대상 옵션] > /tmp/dump_data.sql
mysqldump는 기본적으로 모든 출력물을 화면(stdout)으로 보내게 된다.
그래서 이 결과를 백업용이나 데이터 이관용으로 사용하기 위해서는 표준 출력을 파일로 리다이렉션해서 저장해야 한다.

**DB 접속 정보**
<code>
--host : 데이터베이스 호스트 정보
--user : 접속 데이터베이스 계정
--password : 접속 데이터베이스 계정의 비밀번호
--port : 접속 포트 번호
--socket : 접속시 사용할 소켓 파일의 경로
</code>

**dump 옵션**
<code>
--single-transaction : dump를 하나의 트랜잭션을 이용해서 실행함
(InnoDB 스토리지 엔진을 사용하는 테이블에 대해서는 Lock없이 일관된 덤프를 받을 수 있음).
자세한 내용은 아래 "일관된 데이터 덤프 받기" 참조

--flush-logs : dump를 시작하는 시점에 binary log를 rotation 시키도록 한다.
Binary log를 증분 백업용으로 사용하는 경우, 풀백업과 증분 백업의 구분을 명확히 할 수 있다.

--master-data : 이 옵션이 명시되면, dump 파일의 헤더 부분에 CHANGE MASTER TO 구문을 포함시키며,
이 구문에는 덤프 시작 시점의 Binary log 파일명과 위치 정보 및 호스트 정보를 포함하고 있다.
이 값을 1로 설정하면 CHANGE MASTER TO 구문이 실제 실행 가능한 형태로 포함되며, 2로 설정되면
SQL 코멘트 형태로 참조만 할 수 있도록 포함된다. 가끔 Binary log가 활성화되지 않은 서버에서 실행 시
에러를 유발하기도 하므로 반드시 먼저 테스트를 해볼 것을 권장한다.

--opt | --skip-opt : opt 옵션은 여러 개의 옵션들을 묶어 놓은 별명과 같은 옵션이며
(--add-drop-table, --add-locks, --create-options, --disable-keys, --extended-insert,

--lock-tables, --quick, --set-charset) 옵션들을 포함한다.
때때로(create db, table등을 포함할지 말지 제어시) mysqldump의 옵션을 상세하게 제어하고자
할 경우에는 skip-opt 옵션으로 비활성화시킨 후, 필요한 것들만 직접 명시해주는 것이 좋다.

--quick : 일반적으로 mysqldump는 테이블의 데이터들을 모두 Client의 메모리에 모두 로딩한 후,
파일에 쓰기를 시작하게 되는데, 이 옵션이 활성화되면 Client의 메모리에 버퍼링하지 않고
바로 파일로 쓰거나 화면으로 출력하게 된다. 이 옵션은 opt 옵션에 포함되어서 자동으로 활성화되기 때문에,
(큰 테이블이 있는 경우) skip-opt를 사용하는 경우에는 quick 옵션을 별도로 명시해줘야 한다.

--extended-insert : mysqldump는 테이블의 데이터를 INSERT 문장 형태로 출력하게 되는데,
이 옵션을 사용하면 확장 형태의 INSERT문장으로 데이터를 덤프하게 된다. 이런 형태의 덤프는
다시 로드하는 작업을 빠르게 해줄 수 있다. (확장 형태의 INSERT 문장이라는 것은
"INSERT INTO tab VALUES ('1','2'),('2','3'),('3','4');"와 같은 형태의 INSERT 문장을 의미한다.)

--add-drop-database : 덤프 파일의 내용에 "DROP DATABASE ..." 명령을 포함하지 않도록 한다.

--add-drop-table : 덤프 파일의 내용에 "DROP TABLE ..." 명령을 포함하지 않도록 한다.

--no-create-db : mysqldump 의 옵션에 --databases 또는 --all-databases 옵션이 명시되지 않으면,
기본적으로 "CREATE DATABASE ..." 명령이 포함되는데, 종종 이 명령이 필요치 않은 경우가 있을 수 있으며
이 때에는 이 옵션을 활성화 해주면 된다.

--no-create-info : 덤프 파일의 내용에 "CREATE TABLE ..." 명령을 포함하지 않도록 한다.

--create-options : 덤프 파일에 "CREATE TABLE ..." 명령이 출력되는 경우, 초기 테이블 생성시
사용했던 테이블 생성 옵션들을 모두 포함하도록 한다.

--no-data : 데이터는 덤프하지 않고 테이블의 구조만 덤프하도록 한다.

--hex-blob : 덤프 대상 테이블중에서 이진 값을 가지는 컬럼의 값들을 16진수 문자열로 출력한다.

--routines : 덤프시에 스토어드 프로시져와 함수를 출력하도록 한다.

--triggers : 덤프시에 트리거를 출력하도록 한다.
</code>

**dump 대상 옵션**
<code>
--all-databases : 이 옵션을 명시하면 현재 서버의 모든 데이터베이스를 덤프하게 된다.
이 옵션으로 덤프를 받게 되면 기본적으로 "USE <데이터베이스명>;" 명령이 덤프 내용에 포함된다.

--databases [database1] [database2] ... : 이 옵션을 명시하면 현재 서버에서 명시된
데이터베이스들을 덤프하게 된다. 이 옵션으로 덤프를 받게 되면 기본적으로 "USE <데이터베이스명>;"
명령이 덤프 내용에 포함된다.

[database1] : 별도의 옵션없이 mysqldump 명령의 마지막에 특정 데이터베이스명만을 명시하면
해당 데이터베이스의 내용만을 덤프하게 된다. 이 경우에는 "USE <데이터베이스명>;" 명령이
덤프 파일에 포함되지 않는다. 그래서 데이터베이스를 덤프 받아서 동일 데이터베이스에 다시
적재하고자 하는 경우 (덤프받은 파일을 동일 서버의 다른 데이터베이스명으로 적재하고자 하는 경우)
"USE <데이터베이스명>;" 때문에 작업이 쉽지 않은데 이런 경우에는 이 옵션으로 덤프를 받으면 쉽게
동일 MySQL 서버에 다른 데이터베이스명으로 적재할 수 있다.

[database1] [table1] [table2] : 별도의 옵션없이 mysqldump 명령의 마지막에 여러개의
오브젝트명을 명시하면, mysqldump는 첫번째 오브젝트는 데이터베이스명, 그 다음부터는
테이블명으로 인식하게 된다. 그래서 해당 데이터베이스에 있는 각 테이블들만을 덤프하게 된다.
</code>

**일관된 데이터 덤프 받기**
mysqldump를 이용하여 일관된 데이터를 덤프받고자 한다면,
lock-tables 옵션 또는 single-transaction 옵션을 사용해야만 한다.
간단히 이 옵션들의 사용에 따른 데이터 잠금 여부를 확인해 본 결과이다.
일반적으로 InnoDB의 경우에는 single-transaction을 사용하며,
MyISAM 의 경우에는 lock-tables 옵션을 사용해야 한다. (물론 MyISAM의 경우 일관된 백업의 의미는 없다)
InnoDB에서 single-transaction 옵션이 사용되면, table에 대한 lock을 걸지 않고 MVCC의 Read view를 이용하기 때문에
mysqldump 도중에도 trasaction (insert, update, delete)이 허용된다.

mysqldump --skip-add-locks --single-transaction --skip-lock-tables db1 db2 > test.dump.sql
==> 덤프 도중 다른 세션에서 insert, update, delete 가능
mysqldump --single-transaction  db1 db2 > test.dump.sql
==> 덤프 도중 다른 세션에서 insert, update, delete 가능
mysqldump --lock-tables  db1 db2 > test.dump.sql
==> 덤프 도중 다른 세션에서 insert, update, delete 불가능


위의 옵션들을 이용한 여러가지 목적 또는 용도로 mysqldump명령

**MySQL 테이블 & 루틴 전부 덤프**
  mysqldump --user=root --password --opt --single-transaction --hex-blob --master-data=2 --routines --triggers
               --default-character-set=utf8 --databases [데이터베이스명1] [데이터베이스명2] > `hostname`_`date '+%y%m%d'`.dump.sql

**MySQL의 루틴만 덤프**
  mysqldump --user=root --password --routines --no-create-info --no-data
                   --no-create-db --skip-opt [데이터베이스명] > `hostname`_`date '+%y%m%d'`.dump.sql

**View의 덤프**
View 는 기본적으로 Algorithm (Merge 또는 Temptable)에 관계없이
독립적인 데이터를 가지지 않기 때문에 mysqldump로 받아도 실질적인 데이터를 가지지 않는다.
그래서 mysqldump로 --no-create-info 가 명시되지 않는 이상은 테이블과 동일하게 CREATE VIEW
문장은 백업이 가능하다

====== MySql Replication 설정하기 ======


회사에서 한 팀에게 할당해주었던 팀 DB 서버가 디스크가 깨져 날라갔다!!!
replication 설정 된 서버가 있어서 다행이! 라고 생각했었는데
이럴수가.. slave 서버는 1주일 전 Data만이 남아있었고 그 이후 Data가 없는 것 아닌가!?!

로그와 설정을 확인해본 결과 mysql 기본 셋팅인 max_allow_packet이 1M 였고
이 때문에 1주일전쯤 대량의 Data가 replication 도중 error가 났던 것.. 때문에 그 후 Data는 replication 이
되지 않고 있었다...

아...

일단은 DB는 해당 담당자가 되는대로 복구 하기로 하였고 디스크가 나간 master 서버는 새 disk 교체 후 os, mysql 최신버전으로
재설치, slave 서버 역시 새 디스크로 교체 후 os, mysql 재설치 하였다. (서버에 사용되는 disk가 오래되면 재앙이다 ㅠㅠ)

이 후 replication 설정한 기록을 남긴다.

===== Master Server Setting =====

mysql 공식 메뉴얼 (사용한 mysql 버전이 5.1.67 버전이었으므로 5.1 메뉴얼에 따라 설정)
참조 : http://dev.mysql.com/doc/refman/5.1/en/replication-howto-masterbaseconfig.html

<code>
$ vi /etc/my.cnf

[mysqld]
#log-bin=mysql-bin // 주석 해제
log-bin=mysql-bin
server-id=1 // 입력
max_allowed_packet=128M // 이전 replication 설정에서 error 가 됬던 부분으로 최대치 설정.
</code>

===== Slave Server Setting =====

<code>
$ vi /etc/my.cnf

[mysqld]
#log-bin=mysql-bin // 주석 해제
log-bin=mysql-bin
server-id=2 // master 와 다른 id 입력
max_allowed_packet=128M // 이전 replication 설정에서 error 가 됬던 부분으로 최대치 설정.
</code>

master나 slave의 몇몇 설정옵션들이 더 있지만 현재는 전체 DB를 대상으로 하는 replication 이므로 이정도 설정이면 된다.

===== Creating a User for Replication =====

master 서버에 slave 서버에서 replication을 위해 접속할 user를 추가해 주어야 한다.

<code>
mysql> CREATE USER 'repl'@'%.mydomain.com' IDENTIFIED BY 'slavepass';
mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%.mydomain.com';
mysql> FLUSH PRIVILEGES;
</code>

===== Creating a Data Snapshot Using mysqldump =====

master 서버의 db를 slave로 복사해 주어야 하는데 여러 가지 방법이 있다.
이번에 진행하면서 mysqld 를 stop 하고 rsync 로 slave에 복사하였는데 mysql manual dump를 사용하는 방법은 아래와 같다.

<code>
mysql> FLUSH TABLES WITH READ LOCK;
shell> mysqldump --all-databases --lock-all-tables >dbdump.db
shell> mysqldump --all-databases --master-data >dbdump.db
mysql> UNLOCK TABLES;
</code>

===== Creating a Data Snapshot Using Raw Data Files =====

dump 가 아닌 다른 방법으로 db를 slave로 옮기는 방법.
<code>
shell> mysqladmin shutdown
// 먼저 mysql을 정지
shell> tar cf /tmp/db.tar ./data
// mysql data폴더를 tar로 묶거나 zip으로 압축
shell> zip -r /tmp/db.zip ./data
// 아니면 rsync 를 사용하여 slave로 바로 복사한다.
shell> rsync --recursive ./data /tmp/dbdata
// 이번에 복사할때 아래와 같이 복사하였다.
shell> rsync -avz -e 'ssh -p 2022' root@ip:/var/lib/mysql /home
// -e 옵션은 기본 포트가 아닌 다른 포트를 사용하기때문에 포트를 지정하는 옵션.
// slave 서버는 sdb1에 따로 /home 을 마운트 하고 그곳에 mysql data 폴더를 옮겨주었다.
</code>

===== Setting Up Replication with New Master and Slaves =====

master 서버 확인

<code>
mysql> show master status;
+------------------+----------+--------------+------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB |
+------------------+----------+--------------+------------------+
| mysql-bin.000003 |    61290 |              |                  |
+------------------+----------+--------------+------------------+
1 row in set (0.00 sec)
</code>

위의 file과 position을 slave에 셋팅하여 준다.

<code>
mysql> CHANGE MASTER TO
    ->     MASTER_HOST='master_host_name',
    ->     MASTER_USER='replication_user_name',
    ->     MASTER_PASSWORD='replication_password',
    ->     MASTER_LOG_FILE='recorded_log_file_name',
    ->     MASTER_LOG_POS=recorded_log_position;

mysql> start slave;
// 정지할땐 mysql> stop slave;
// slave의 경우는 셋팅이 된 이후 mysqld 가 시작될때 자동으로 같이 동작 됨.
// 자동으로 동작되는 slave를 동작하지 않게 하기 위해서는 option을 주면 됨.

mysql> show slave status;
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Slave_IO_State                   | Master_Host    | Master_User | Master_Port | Connect_Retry | Master_Log_File  | Read_Master_Log_Pos | Relay_Log_File          | Relay_Log_Pos | Relay_Master_Log_File | Slave_IO_Running | Slave_SQL_Running | Replicate_Do_DB | Replicate_Ignore_DB | Replicate_Do_Table | Replicate_Ignore_Table | Replicate_Wild_Do_Table | Replicate_Wild_Ignore_Table | Last_Errno | Last_Error | Skip_Counter | Exec_Master_Log_Pos | Relay_Log_Space | Until_Condition | Until_Log_File | Until_Log_Pos | Master_SSL_Allowed | Master_SSL_CA_File | Master_SSL_CA_Path | Master_SSL_Cert | Master_SSL_Cipher | Master_SSL_Key | Seconds_Behind_Master | Master_SSL_Verify_Server_Cert | Last_IO_Errno | Last_IO_Error | Last_SQL_Errno | Last_SQL_Error |
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
| Waiting for master to send event | ip | repl     |        3306 |            60 | mysql-bin.000003 |               61290 | mysqld-relay-bin.000003 |         59606 | mysql-bin.000003      | Yes              | Yes               |                 |                     |                    |                        |                         |                             |          0 |            |            0 |               61290 |           59762 | None            |                |             0 | No                 |                    |                    |                 |                   |                |                     0 | No                            |             0 |               |              0 |                |
+----------------------------------+----------------+-------------+-------------+---------------+------------------+---------------------+-------------------------+---------------+-----------------------+------------------+-------------------+-----------------+---------------------+--------------------+------------------------+-------------------------+-----------------------------+------------+------------+--------------+---------------------+-----------------+-----------------+----------------+---------------+--------------------+--------------------+--------------------+-----------------+-------------------+----------------+-----------------------+-------------------------------+---------------+---------------+----------------+----------------+
1 row in set (0.00 sec)
</code>

===== Check =====

이제 정상적으로 Replication 이 동작하는지 확인하여 보면 된다.
master에 임시로 db와 table을 생성하여 보고 slave에 잘 반영이 된다면 기본적인 동작은 잘 동작한다고 할 수 있다.

다만 이제 운영중에 나오는 트러블이나 에러가 나올 수 있지만 이건 앞으로 문제로 남겨두면 된다.

첨부.

이번 작업때 master server의 설정을 slave server의 설정으로 그대로 복구 (해당 db 담당자가..) 한 바람에 ..

master server에도 slave 설정이 되어 있어서 replication 이 정상적으로 동작되지 아니하였다 ㅠㅠ

때문에 구글링에 다음과 같은 해결방법을 찾았다.

master server 에서 아래와 같이 조치 한다.

<code>
mysql> stop slave;
mysql> CHANGE MASTER TO MASTER_HOST='';
mysql> RESET SLAVE;
</code>

출처 : http://wyseburn.tistory.com/222
출처2: http://www.phpschool.com/gnuboard4/bbs/board.php?bo_table=tipntech&wr_id=54465&sca=DBMS&page=6

**MySql DB 별 백업 쉘스크립트(메모)**

매일 디렉토리 생성 후 데이타베이스별로 백업 후 압축
15일이 지난 백업분 제거

<code>

#!/bin/sh

DBHOST="호스트"
DBUSER="계정"
DBPWD="비번"

BACKUPDIR="/root/backup/"
OPTIONS="--skip-comments --default-character-set=utf8 --routines --single-transaction --quick"

DATE=`date +%Y%m%d`
DELDATE=`date --date "15 day ago" +%Y%m%d`

rm -rf ${BACKUPDIR}${DELDATE}
mkdir ${BACKUPDIR}${DATE}

RESULT=`/usr/local/mysql/bin/mysql -u$DBUSER -p$DBPWD -h$DBHOST -e "show databases" | grep -v Database`


for DB in $RESULT; do

  FNAME=${BACKUPDIR}${DATE}/${DB}${DATE}.sql
  /usr/local/mysql/bin/mysqldump -u$DBUSER -p$DBPWD -h$DBHOST $OPTIONS $DB >> $FNAME
  echo "set foreign_key_checks=1;" >> $FNAME
  gzip --rsyncable $FNAME

done

</code>

**[DBMS] mysql 백업(Backup) 및 복구(Recover)**

Mysql 백업
우선 Mysql 이 /usr/local/mysql/에 설치되어있으며 Mysql configure시
--localstatedir=/usr/local/mysql/data 옵션을 주고 설치했다고 가정하겠다.
백업 방법으로는 크게 2가지가 있다.
Mysql DB 데이터 화일을 직접 백업하는 경우와 mysqldump 문을 이용하여 sql문을 백업받는방법이 있다

1. 데이터 화일 직접백업
보통의 경우 Mysql 을 사용하는 Type 이 InnoDB와 Myisam 이 있다.
두 Type 의 차이는 여러가지가 있지만 여기서 언급하는것은 데이터 화일 백업에 관련된것이므로
데이터 화일의 위치를 언급하겠다.

nnodb_data_home_dir = /usr/local/mysql/data/
innodb_data_file_path = ibdata1:2000M;ibdata2:10M:autoextend

InnoDB 의 경우 일반적으로 위의 /etc/my.cnf 에서 설정된 것과 같이
ibdata1, ibdata2 과 같은 화일에 index 및 파일데이터가 저장된다. 그리고 DB 및 테이블 정보는
/usr/local/mysql/data/DB명/테이블명.frm 과 같은 구조로 이루어져 있다.
그러므로 ibdata1 와 같은 화일과 /usr/local/mysql/data/DB명/테이블명.frm 화일들을 복사하면 백업하면된다.

Myisam 인 경우는 좀더 직관적이다.
/usr/local/mysql/data/DB명 에 있는 화일을 통째로 백업받으면 된다.
특정 DB의 특정 테이블만 백업받을 경우에는..

/usr/local/mysql/data/DB명/테이블명.frm # 테이블 구조
/usr/local/mysql/data/DB명/테이블명.MYD # Myisam Type 테이블의 DATA
/usr/local/mysql/data/DB명/테이블명.MYI # Myisam Type 테이블의 index

위의 3개 화일만 다운받으면 된다.
물론 data 디렉토리를 통째로 백업받을수도 있다.

다만 이런 직접적으로 파일을 백업받는 방법은...
Mysql 버젼이 달라졌을때 문제가 될수도 있으니...
가급적이면 mysqldump 를 이용하는것이 좋다.


2. mysqldump를 이용한 Backup
가장 널리 이용하는 방법이고 가장 확실한 방법이다.
다만 DB 데이터가 큰 경우 Backup 시간이 많이 걸릴수 있기 때문에
보통 크론등을 이용하여 주기적으로 새벽시간을 이용하여 백업받는다.

사용방법은 다음과 같다.
/usr/local/mysql/bin/mysqldump -uroot -pPassword [백업옵션] [환경옵션] > /BackUp/Mysql/Backup.sql
/BackUp/Mysql/ <== 임의로 정한 백업화일 위치이다.

[백업옵션] 의 내용은 아래의 4가지 형태만 알아도 될듯하다.
옵션들에 주의 해주면 다양한 형태로 백업을 받을수 있다.
--all-databases # mysql DB 전체를 백업다는것을 의미한다.
--databases DB1 DB2 DB3 # mysql 에서 특정 DB만 백업받는 것을 의미한다.
DB1 # DB1 이라는 DB 만 백업받을때 사용한다.
DB1 table1 # DB1 이라는 DB의 table1 이라는 테이블만 백업받을때 사용한다.

[환경옵션]은 백업시에 어떤환경으로 백업을 할것인가에 대한 옵션이다.
--default-character-set=utf8 # 지정된 캐릭터셋을 기본으로함
--set-charset # 기본지정된 캐릭터셋(default-character-set)을 SET NAMES default-character-set로 설정
--opt # 메모리에 로드하지 않고 바로 화일로 덤프
--create-options # create문 백업시에 테이블 설정을 포함함.
--compatible=DB # 백업sql이 특정 db에 호환되도록 함 예) mysql40, mysql41, oracle, mssql
--extended-insert=FALSE # insert 문을 한줄씩 만든다
--result-file=file # 지정된 file 로 바로 넣음.. "> /BackUp/Mysql/Backup.sql"  과 같은 의미
--triggers # 트리거 덤프
--no-create-db # DB 생성정보를 뺌
--no-create-info # 테이블 생성정보를 뺌
--no-data # 테이블의 데이터를 뺌
이외에도 많은 옵션이 있지만 거의 사용할일이 없어서 나도 잘모른다.
공부하고 싶으신분들은 mysqlkorea.com 이나 mysql.com 에서 찾아보시길..


복구(Recover)
복구는 상당히 간단하다.
/usr/local/mysql/bin/mysql -uroot -pPassword < /BackUp/Mysql/Backup.sql
위의 방법은 전체복구이며 단위 DB가 생성되어 있는상태에서 db별 백업은 다음과 같이 한다.
/usr/local/mysql/bin/mysql -uroot -pPassword DB명< /BackUp/Mysql/Backup.sql


대용량DB 복구를 위한 백업
팁으로 대용량의 Table 을 덤프 받아서 입력할때...
입력속도 때문에 문제가 생기는 경우가 있다.
아마도 대부분이 InnoDB라서 그런 문제가 생기지 싶다.
그럴때는 Table Type 을 InnoDB에서 Myisam 으로 바꾸고 Insert 그리고 다 입력된 다음..
Table Type을 다시 InnoDB로 바꾸는게 빠르다.
그래도 속도가 느리다면 Mysql 에서 LOAD DATA INFILE 을 실행하여.. CSV 같은 데이터를 입력받는게
가장 빠르다.

다음은 하나의 DB(DB에 포함된 모든 테이블 정보)에 관한 DDL문 하나와 각 테이블별 CSV 데이터를 만드는
방법이다. 아래의 명령을 실행시키기 이전에 우선 /백업디렉토리 설정을 먼저해야한다.
갑자기 왠 백업디렉토리 설정이냐라고 물을수 있는데.. CSV 로 만들경우 mysql 이라는 유저권한으로
파일들이 생성되기 때문에 mysql 이 쓰기권한이 있어야한다.

/usr/local/mysql/bin/mysqldump -u root -pPASSWORD DB --no-data > /백업디렉토리/DB.sql
위의 dump 명령은 db 구조만 백업받는것이다.
/usr/local/mysql/bin/mysqldump -u root -pPASSWORD DB --no-create-info --tab=/백업디렉토리 --fields-terminated-by=',' --lines-terminated-by='\r\n' --fields-enclosed-by='"'

여기서 주의해야 할것은 각 테이블별로 "--tab=/백업디렉토리" 정의된 곳에 table명.txt 화일로 CSV 화일이
생성된다는 것이다. 만약 --no-create-info 옵션을 주지 않는경우에는 "--tab=/백업디렉토리"에 정의된 대로
해당디렉토리에 테이블 DDL 문장이 테이블명.sql 로 각각 생성이된다.

각테이블별로 DDL문과 CSV 화일을 백업받고 싶다면..
/usr/local/mysql/bin/mysqldump -u root -pPASSWORD DB --tab=/백업디렉토리 --fields-terminated-by=',' --lines-terminated-by='\r\n' --fields-enclosed-by='"'
이와 같이 하면된다.

만들어진 테이블DDL문과 CSV 데이터로 복구할때는 아래와 같이..
/usr/local/mysql/bin/mysql -uroot -pPassword < /백업디렉토리/DB.sql
/usr/local/mysql/bin/mysql -uroot -pPassword DB명
mysql> load data infile '/백업디렉토리/테이블.txt' into table 테이블명 fields terminated by ',' enclosed by '"' lines terminated by '\r\n'; 

참고1
--tab 옵션을 사용해서 CSV 형태로 백업할때는 --all-database 와 같은 옵션과 같이 사용할수없다.
--tab 옵션은 하나의 DB 이하에서만 사용이 가능하다.
참고2
--fields-terminated-by=',' # 필드구분자
--fields-enclosed-by='"' # 필드를 특정기호로 감싸는것
--lines-terminated-by='\r\n' # 라인구분자(테이블 데이터의 Row 구분자)

====== Mysql ======
===== 보안 설정 =====
http://qiita.com/TatsuNet/items/f284d3ba0ac71def2a63 << 추천
http://www.iblue.kr/xe/index.php?document_srl=35923&mid=board_kiyj73

===== Root Password 설정 =====

mysql을 설치 후 처음 접속할때는 root에 password 가 설정 되어 있지 않다.

  $ /usr/bin/mysqladmin -u root -p password 'new-password'

'new-password' 에 사용할 패스워드를 입력하면 된다.

===== Root 원격 접속 설정 =====
 
  mysql> grant all privileges on *.* to 'root'@'%' identified by 'root의 패스워드';
  mysql> flush privileges;

mysql에서 명령어들은 대소문자 구문없이 사용이 가능하나 테이블의 값들은 대소문자를 구별하니 잘 사용하자.

all privileges 는 모든 권한을 의미한다.
grant select, update, insert on ... 과 같이 구분하여 권한 설정이 가능 하다.

*.* : 모든 DB에 대해 모든 테이블을 의미
db명.* : 특정 db에 대한 모든 테이블
db명.권한 : 특정 db에 대한 특정 테이블

'root'@'%' : root계정에 대해 모든 host 접속 허용
'root'@'localhost' : root계정에 대해 localhost 에서의 접속만 허용
'root'@'ip' : root계정에 대해 특정 ip 접속만 허용
'root'@'xxx.xxx.xxx.xxx' : root계정에 대해 특정 ip 대역 허용
  root 계정을 user id로 사용하면 해당 user에 대해 접속 권한을 설정할 수 있다.

===== mysql 접속자수 확인 =====

출처 : http://www.ssongacademy.com/index.php?document_srl=563&mid=linux_mysql

too many connections 를 확인한다면
myslq 접속자수 또한 확인하고 설정을 해야한다.

<code>
[root@localhost logrotate.d]# mysqladmin -u root -p proc stat
Enter password:
+------+------+-----------+----+---------+------+-------+------------------+
| Id   | User | Host      | db | Command | Time | State | Info             |
+------+------+-----------+----+---------+------+-------+------------------+
| 7996 | root | localhost |    | Query   | 0    |       | show processlist |
+------+------+-----------+----+---------+------+-------+------------------+
Uptime: 432759  Threads: 1  Questions: 315099  Slow queries: 0  Opens: 4783  Flush tables: 1  Open tables: 64  Queries per second avg: 0.728
 
Uptime : the MySQL server 시작된 후 현재까지 시간 (초)
Threads : 현재 디비서버에 연결된 유저수
Questions : 서버시작후 지금까지 요청된 쿼리수
Slow queries : --log-slow-queries[=file_name] option로 시작된 서버가 variables에 지정된
long_query_time seconds시간보다 큰 쿼리시간을 가진 요청수
Opens : 서버가 시작된 후 현재까지 열렸던 테이블 수
Flush tables : flush ..., refresh, and reload commands된 수
Open tables : 현재 열려 있는 테이블 수
Queries per second avg : 평균 초당 쿼리수
 
Memory in use :the mysqld code에 의해 직접 할당된 메모리 (only available when MySQL is compiled with --with-debug=full).
Max memory used : the mysqld code에 의해 직접 할당된 최대메모리 (only available when MySQL is compiled with --with-debug=full).
</code>

===== [mysql] mysql 비밀번호 초기화 =====

테스트를 진행하다보면 mysql 비밀번호를 초기화 할 때가 있습니다.

비밀번호를 업데이트 할 경우,
간혹 다음과 같은 에러메시지를 보기도 합니다.

  ERROR 1142 (42000): UPDATE command denied to user ''@'localhost' for table 'user'

  ERROR 1044 (42000): Access denied for user ''@'localhost' to database 'mysql'
 
이럴 경우 비밀번호를 초기화하는 방법은...

1. mysqld_safe --skip-grant-tables &

2. mysql -u root -p

[엔터]

3. update mysql.user set password=password('변경 비밀번호') where user='root';

4. flush privileges;

입니다...

비밀번호 변경 후,

mysql -u root -p 명령을 내리면

변경된 비밀번호를 넣어야 접근이 가능하게 됩니다.

참고자료 :  http://dev.mysql.com/doc/refman/5.0/en/resetting-permissions.html#resetting-permissions-unix

===== 한글 셋 변경 =====

http://tedwon.com/pages/viewpage.action?pageId=30244996

===== mysql.socket error =====

설치 후 설정파일을 변경하는 경우 해당 문제가 발생하는 경우가 많다.

보통 이런경우 실제 sock 파일을 에러에 나오는 경로에 링크를 생성하여 주는데 이보다는

설정파일에

<code>
[client]
socket=소켓위치
</code>

로 설정하여 주면 된다.

====== MariaDB yum 으로 CentOS 로 설치하기. ======

http://gubok.tistory.com/m/post/view/id/358

====== MariaDB 5.5 이상 버전에서 문자셋 변경하기. ======

http://threadbuilder.wordpress.com/2013/05/11/mysql-5-5%EC%9D%B4%EC%83%81-%EB%B2%84%EC%A0%84%EC%97%90%EC%84%9C-%EC%96%B8%EC%96%B4%EC%85%8B-%EC%84%A4%EC%A0%95-%EB%B3%80%EA%B2%BD%ED%95%98%EA%B8%B0/

====== 한 서버에 MariaDB와 MySQL 설치하기 ======
회사에서 분석 및 초기 개발 테스트 환경을 위하여
한 서버에 MariaDB 10.0.17 과 MySQL Community 5.6.23 을 같아 설치 하였다.

둘다 소스 설치로 진행하였으며
서버 환경은 CentOS 7 / Xeon E5-2637 v3 / 128GB / 5TB / HP 이다.

참고 사이트 리스트
> 스크립트 관련
http://forum.falinux.com/zbxe/index.php?document_srl=549896&mid=lecture_tip
http://www.linuxlab.co.kr/docs/10-4.htm
> 소스 설치 관련
http://www.iblue.kr/xe/index.php?document_srl=35923&mid=board_kiyj73
http://sir.co.kr/bbs/board.php?bo_table=pg_tip&wr_id=14304
http://qiita.com/TatsuNet/items/f284d3ba0ac71def2a63
http://www.linuxfromscratch.org/blfs/view/svn/server/mariadb.html
http://nanoomi.org/bbs/board.php?bo_table=03_1&wr_id=29
http://linux.systemv.pe.kr/mariadb-10-source-install/
http://diveis.tistory.com/100
> 설치 중 에러 해결
http://kzy52.com/entry/2014/07/10/064352