2016년 12월 26일 월요일

my.cnf sample

MySQL의 Sample my.cnf file


[mysqld]

back_log = 1024
bind_address = 0.0.0.0
binlog_cache_size = 64k
binlog_format = row
binlog_stmt_cache_size = 64k
################################################################
character_set_server = utf8
collation_server =  utf8_general_ci

connect_timeout = 60

datadir = /MYSQL/data
date_format = %Y-%m-%d
datetime_format = %Y-%m-%d %H:%i:%s

default_authentication_plugin = mysql_native_password
default_password_lifetime = 365

default_storage_engine = InnoDB
default_tmp_storage_engine = InnoDB

disabled_storage_engines="MyISAM,FEDERATED"
div_precision_increment = 6

event_scheduler = OFF

general_log = OFF
general_log_file = /var/log/mysql/mysql-general-log.log

init_connect = 'SET NAMES utf8'

################################################################
# InnoDB
###############################################################
innodb_adaptive_flushing = ON
innodb_adaptive_flushing_lwm = 20
innodb_adaptive_hash_index = ON
innodb_adaptive_hash_index_parts = 16

innodb_api_trx_level = 2

innodb_autoextend_increment = 128
innodb_autoinc_lock_mode = 1

innodb_buffer_pool_chunk_size = 256M
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_dump_pct = 100
innodb_buffer_pool_instances = 8
innodb_buffer_pool_load_at_startup = ON

innodb_buffer_pool_size = 2048
innodb_concurrency_tickets = 8192

innodb_data_file_path = ibdata1:2048M;ibdata2:2048M:autoextend
innodb_data_home_dir = /MYSQL/system

innodb_deadlock_detect = OFF
innodb_doublewrite = OFF
innodb_fast_shutdown = 0

innodb_file_per_table = ON

innodb_flush_log_at_trx_commit = 2
innodb_flush_method = O_DIRECT # 4 for 8.0
innodb_flush_neighbors = 0

innodb_flush_sync = OFF
innodb_io_capacity = 200
innodb_io_capacity_max = 400
innodb_lock_wait_timeout = 60

innodb_log_buffer_size = 32k
innodb_log_file_size = 1G
innodb_log_files_in_group = 4 
innodb_log_group_home_dir = /MYSQL/system

innodb_log_write_ahead_size = 8k
innodb_max_dirty_pages_pct = 80
innodb_max_dirty_pages_pct_lwm = 20

innodb_max_undo_log_size = 4G
innodb_monitor_enable='latch'
innodb_open_files = 8192
innodb_page_size = 8 * 1024

innodb_random_read_ahead = ON
innodb_read_io_threads = 4

innodb_sort_buffer_size = 2M

innodb_stats_persistent_sample_pages = 80
innodb_stats_transient_sample_pages = 20
innodb_sync_array_size = 768

innodb_temp_data_file_path = ibtmp1:256M:autoextend
innodb_tmpdir = /MYSQL/system
innodb_thread_concurrency = 8

innodb_undo_tablespaces = 2
innodb_write_io_threads = 4
####

interactive_timeout = 600
join_buffer_size = 1K
lock_wait_timeout = 600
#
log_bin = /MYSQL/binlog/mysql-binlog
log_error = /var/log/mysql/mysql-error.log
log_output = file
log_timestamps = system

long_query_time = 10
max_allowed_packet = 1G
max_connect_errors = 8192
max_connections = 8192

net_buffer_length = 1M
open_files_limit = 8192

performance_schema_max_sql_text_length = 8192
port = 3306

query_cache_limit = 32M
query_cache_size = 16M
query_cache_type = 2
#
schema_definition_cache = 1024
server_id = 1
skip_external_locking = ON
skip_name_resolve = ON
#
slow_query_log = ON
slow_query_log_file = /var/log/mysql/mysql-slow.log

socket = /tmp/mysql.sock

sort_buffer_size = 1M

sql_mode = ORACLE

#################################################################
# ssl
################################################################
ssl-ca=/etc/my.cnf.d/ssl/ad-admindb-m-01-cert.pem
ssl-cert=/etc/my.cnf.d/ssl/ad-admindb-m-01-cert.pem
ssl-key=/etc/my.cnf.d/ssl/ad-admindb-m-01-key.pem


stored_program_cache = 512
stored_program_definition_cache = 512

sync_binlog = 0

table_definition_cache = 2048
table_open_cache = 4096
thread_stack = 8192

max_heap_table_size = 32M
max_tmp_table_size = 32M

tmpdir = /MYSQL/db_tmp

 # set tx_isolation = 'READ-COMMITTED'
transaction-isolation           = READ-COMMITTED

wait_timeout = 600

[mysql]
socket                                          = /tmp/mysql.sock
port                                            = 3306
no-auto-rehash
show-warnings
default-character-set                          = utf8
prompt                                          =\u@\h:\d\_\R:\m:\\s>
pager                                           ="less -n -i -F -X -E"

[client]
port                                           = 3306
socket                                         = /tmp/mysql.sock

#################################################################
# ssl
################################################################
ssl-ca=/etc/my.cnf.d/ssl/ad-admindb-m-01-cert.pem
ssl-cert=/etc/my.cnf.d/ssl/ad-admindb-m-01-cert.pem
ssl-key=/etc/my.cnf.d/ssl/ad-admindb-m-01-key.pem

[mysqladmin]
port = 3306
socket = /tmp/mysql.sock
user = mysql
passwor = 123

[mysqld_safe]
socket                                          = /tmp/mysql.sock
port                                            = 3306
log_error                                       = /var/log/mysql/ad-admindb-m-01-error.log
basedir                                         = /usr
datadir                                         = /MYSQL/data
open-files-limit                                = 65535
malloc-lib                                      = /usr/lib64/libjemalloc.so.1

2016년 12월 21일 수요일

MySQL Connection SSL Setting

MySQL Server와 Client간의 SSL을 설정 하는 방법


--
-- 01.Key 생성
--
openssl genrsa -out server-key.pem 4096
openssl req -x509 -new -nodes -days 0 -key server-key.pem -out server-cert.pem
cp server-cert.pem ca.pem
 
--
-- 02.설정
-- my.cnf
ㅡ
[mysqld]
 
ssl-ca=ca.pem
ssl-key=server-key.pem
ssl-cert=server-cert.pem
 
[mysql]
 
ssl-ca=ca.pem
ssl-key=server-key.pem
ssl-cert=server-cert.pem
 
--
-- 03. SSL 전용 유저 
--
 
grant all on test.* to `ssluser`@`%` require SSL;
 
--
-- 04. 확인
--
 
show variables like 'have_ssl'
show status like 'Ssl%'
status

생각을 정리 하며


올해 초에 쓴 글이다.

그 당시에는 이런 느낌이었나 보다.



생각을 정리 한다.
지금 나는 대한민국의 노동법이 아무런 의미가 없다는 걸 경험 했다.
2016년 04월 20일 나는 그런 중요한 문제를 깨달았다.
노동법을 믿고 있었던 나의 무지 이다.
유젠스에스앤씨라는 회사 내가 5년 넘게 다닌 회사이다. 그러나 난 그 5년이라는 시간을 그냥 버버린 것이. 2016년 04월 20일에 있었던 딱 20분이라는 시간 때문에

2016년 12월 16일 금요일

Oracle 12cR1 TDE Setting

Microsoft 의 Azure 에서 지원하는 Oracle 12cR1(12.1.0.2) Enterprise 에 PDB의 USERS Tablespace 만들다 DBCA 에서 wallet key not open 으로 에러가 나서 찾아 봤다. 12cR1의 마지막 Release 에서는 PDB 의 일반 Tablespace 는 기본적으로 Force tablespace USERS to be encrypted with AES128 되나 보다.


--
-- 00. Wallet Key location
-- 

sqlnet.ora

ENCRYPTION_WALLET_LOCATION =
  (SOURCE =(METHOD = FILE)(METHOD_DATA =
    (DIRECTORY = /u01/app/oracle/admin/$ORACLE_SID/encryption_keystore/)
    
    

--
-- 01. Create TDE Wallet Key
--

Root Container (CDB)
sqlplus / as sysdba

ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/admin/KOREA/encryption_keystore/' identified by audtlr2;

/* 
      Key Open and Close

    if container = ALL is ommit then
        current container
    end if
 */
 
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY audtlr2 container=ALL;
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY audtlr2 container=ALL;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY audtlr2 WITH BACKUP CONTAINER=ALL;

SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;

   CON_ID KEY_ID
---------------- ------------------------------------------------------------------------------
        0 AZVTr7stFE8Gvyn8Yxt7dLMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA
        0 AfszURVAMk/5v1Eq3znzMMkAAAAAAAAAAAAAAAAAAAAAAAAAAAAA


SET LINESIZE 200
COLUMN wrl_parameter FORMAT A50
SELECT * FROM v$encryption_wallet;
   
     

WRL_TYPE      WRL_PARAMETER     STATUS          WALLET_TYPE     WALLET_OR FULLY_BAC    CON_ID
-------------------- -------------------------------------------------- ------------------------------ -------------------- --------- --------- ----------------
FILE       /u01/app/admin/KOREA/encryption_keystore/  OPEN          PASSWORD      SINGLE    NO         0


-- if not using CONTAONER=ALL then make pdb key

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY audtlr2 ;
ADMINISTER KEY MANAGEMENT SET KEYSTORE CLOSE IDENTIFIED BY audtlr2;
ADMINISTER KEY MANAGEMENT SET KEY IDENTIFIED BY audtlr2 WITH BACKUP ;


SET LINESIZE 100
SELECT con_id, key_id FROM v$encryption_keys;

   CON_ID KEY_ID
---------------- ------------------------------------------------------------------------------
        0 AZVTr7stFE8Gvyn8Yxt7dLMAAAAAAAAAAAAAAAAAAAAAAAAAAAAA

 --
 -- 02. unplug and plug pdb with tde
 --

/* unplug */

CONN sys@pdb1 AS SYSDBA

ADMINISTER KEY MANAGEMENT EXPORT ENCRYPTION KEYS WITH SECRET "mySecret" TO '/tmp/export.p12' IDENTIFIED BY myPassword;

CONN / AS SYSDBA

ALTER PLUGGABLE DATABASE pdb1 CLOSE;
ALTER PLUGGABLE DATABASE pdb1 UNPLUG INTO '/tmp/pdb1.xml';



/* plug */

CONN / AS SYSDBA

CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/pdb1.xml';

-- If you are not using OMF, you will have to convert the paths manually.
--CREATE PLUGGABLE DATABASE pdb2 USING '/tmp/pdb1.xml'
--  FILE_NAME_CONVERT=('/u01/app/oracle/oradata/cdb1/pdb1/','/u01/app/oracle/oradata/cdb2/pdb2/');

ALTER PLUGGABLE DATABASE pdb2 OPEN READ WRITE;

if cdb2 is not key then
    ADMINISTER KEY MANAGEMENT CREATE KEYSTORE '/u01/app/oracle/admin/cdb2/encryption_keystore/' IDENTIFIED BY myPassword;
    ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY myPassword;
end

ONN / AS SYSDBA
ALTER SESSION SET CONTAINER=pdb2;

ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "myPassword";
ADMINISTER KEY MANAGEMENT IMPORT ENCRYPTION KEYS WITH SECRET "mySecret" FROM '/tmp/export.p12' IDENTIFIED BY "myPassword" WITH BACKUP;

-- Restart the PDB and open the keystore.
SHUTDOWN;
STARTUP;
ADMINISTER KEY MANAGEMENT SET KEYSTORE OPEN IDENTIFIED BY "myPassword";


-- auto login open setting

CONN / AS SYSDBA
ADMINISTER KEY MANAGEMENT CREATE LOCAL AUTO_LOGIN KEYSTORE FROM KEYSTORE '/u01/app/oracle/admin/cdb1/encryption_keystore/' IDENTIFIED BY myPassword;

SHUTDOWN IMMEDIATE;
STARTUP

2016년 12월 15일 목요일

MariaDB Dynamic Column Test

MariaDB 책을 보다 Dynamic Column 이란는게 있어 테스트 한번 해봤음


/**
    MariaDB Dynamic Column Test 
    
    func:   지원하는 함수 
            column_create
            column_add
            column_get
            column_delete
            column_list
            column_join
            column_exists
            column_check
            
   Type:         
            지원되는 타입
             Type  Description
       BINARY[(N)] A variable-length binary string
         CHAR[(N)] A variable-length string
              DATE A 3-byte date
     DATETIME[(D)] A 9-byte date and time. Microseconds are supported
  DECIMAL[(M[,D])] A variable-length binary decimal
           INTEGER A variable-length signed integer, up to 64 bits in length
  SIGNED [INTEGER] A variable-length signed integer, up to 64 bits in length
         TIME[(D)] A 6-byte time. Microseconds are supported and it may be  negative
UNSIGNED [INTEGER] A variable-length unsigned integer, up to 64 bits in length

*/

--
-- 01. Test Table Create
--
drop database if exists test;
create database test;

drop table if exists tbl_dyn_col_test;
CREATE TABLE `test`.`tbl_dyn_col_test`
(
 `id`  int(10) unsigned NOT NULL AUTO_INCREMENT PRIMARY KEY,
 `dyn_cols` BLOB comment '동적 칼럼'
) engine = innodb default charset=utf8 comment='동적칼럼 테스트 테이블'
;

--
-- 02. Test Data Insert
--

INSERT INTO tbl_dyn_col_test (dyn_cols)
VALUES
  (
    COLUMN_CREATE (
      'name',
      't-shirt',
      'color',
      'blue' AS CHAR,
      'size',
      'XL' AS CHAR
    )
  ),
  (
    COLUMN_CREATE (
      'name',
      't-shirt',
      'color',
      'blue' AS CHAR,
      'size',
      'L' AS CHAR
    )
  ),
  (
    COLUMN_CREATE (
      'name',
      't-shirt',
      'color',
      'black' AS CHAR,
      'size',
      'M' AS CHAR
    )
  ),
  (
    COLUMN_CREATE (
      'name',
      'flashlight',
      'color',
      'black' AS CHAR,
      'size',
      'AAA' AS CHAR,
      'num',
      2 AS INT
    )
  ),
  (
    COLUMN_CREATE ('name', 'shovel', 'length', '5')
  );

--
-- 02.1 결과 검증
--

SELECT
  id,
  column_list (dyn_cols)
FROM
  tbl_dyn_col_test;


    id  column_list(dyn_cols)        
------  -----------------------------
     1  `name`,`size`,`color`        
     2  `name`,`size`,`color`        
     3  `name`,`size`,`color`        
     4  `num`,`name`,`size`,`color`  
     5  `name`,`length`            ]
     
--
-- 03.칼럼 추가 
--  

UPDATE
  tbl_dyn_col_test
SET
  dyn_cols = COLUMN_ADD (dyn_cols, 'name', 'torch')
WHERE COLUMN_GET (dyn_cols, 'name' AS CHAR) = 'flashlight';

UPDATE
  tbl_dyn_col_test
SET
  dyn_cols = COLUMN_ADD (dyn_cols, 'length', 6)
WHERE COLUMN_GET (dyn_cols, 'name' AS CHAR) = 'torch';


    id  column_list (dyn_cols)                
------  --------------------------------------
     1  `name`,`size`,`color`                 
     2  `name`,`size`,`color`                 
     3  `name`,`size`,`color`                 
     4  `num`,`name`,`size`,`color`,`length`  
     5  `name`,`length`      
     
--
-- 04.칼럼 삭제
--

UPDATE
  tbl_dyn_col_test
SET
  dyn_cols = COLUMN_DELETE (dyn_cols, 'length')
WHERE COLUMN_GET (dyn_cols, 'name' AS CHAR) = 'shovel';


    id  column_list (dyn_cols)                
------  --------------------------------------
     1  `name`,`size`,`color`                 
     2  `name`,`size`,`color`                 
     3  `name`,`size`,`color`                 
     4  `num`,`name`,`size`,`color`,`length`  
     5  `name`                                                 

--
-- 05.칼럼 조회 
--


SELECT
  id,
  COLUMN_GET (dyn_cols, 'name' AS CHAR) AS 'name',
  COLUMN_GET (dyn_cols, 'color' AS CHAR) AS 'color',
  COLUMN_GET (dyn_cols, 'size' AS CHAR) AS 'size',
  COLUMN_GET (dyn_cols, 'num' AS INT) AS 'num'
FROM
  tbl_dyn_col_test;
  
  
    id  name     color   size       num  
------  -------  ------  ------  --------
     1  t-shirt  blue    XL        (NULL)
     2  t-shirt  blue    L         (NULL)
     3  t-shirt  black   M         (NULL)
     4  torch    black   AAA            2
     5  shovel   (NULL)  (NULL)    (NULL)


--
-- 05.2 Json 형식
--
     
SELECT
  id,
  COLUMN_JSON (dyn_cols)
FROM
  tbl_dyn_col_test;


    id  COLUMN_JSON (dyn_cols)                                            
------  ------------------------------------------------------------------
     1  {"name":"t-shirt","size":"XL","color":"blue"}                     
     2  {"name":"t-shirt","size":"L","color":"blue"}                      
     3  {"name":"t-shirt","size":"M","color":"black"}                     
     4  {"num":2,"name":"torch","size":"AAA","color":"black","length":6}  
     5  {"name":"shovel"}                                                 
     
--
-- 05.3 칼럼 존재 여부 확인
--

SELECT
  id,
  COLUMN_EXISTS (dyn_cols, 'num')
FROM
  tbl_dyn_col_test;

    id  COLUMN_EXISTS (dyn_cols, 'num')  
------  ---------------------------------
     1                                  0
     2                                  0
     3                                  0
     4                                  1
     5                                  0
     

SELECT
  id,
  COLUMN_CHECK (dyn_cols)
FROM
  tbl_dyn_col_test;


    id  COLUMN_CHECK (dyn_cols)  
------  -------------------------
     1                          1
     2                          1
     3                          1
     4                          1
     5                          1
     
--
-- 06. 내부 동적 칼럼
--

INSERT INTO tbl_dyn_col_test (dyn_cols)
VALUES
  (
    COLUMN_CREATE (
      'type',
      'parent',
      'name',
      'Mary',
      'child1',
      COLUMN_CREATE ('name', 'Sue', 'eyes', 'brown'),
      'child2',
      COLUMN_CREATE (
        'name',
        'Bob',
        'grandchild',
        COLUMN_CREATE ('name', 'baby')
      )
    )
  );

select
    id,
    column_list(dyn_cols)
from
    tbl_dyn_col_test
;

    id  COLUMN_list(dyn_cols)                 
------  --------------------------------------
     1  `name`,`size`,`color`                 
     2  `name`,`size`,`color`                 
     3  `name`,`size`,`color`                 
     4  `num`,`name`,`size`,`color`,`length`  
     5  `name`                                
     6  `name`,`type`,`child1`,`child2`  


--
-- 06.2 조회 방법
--     

SELECT
  COLUMN_GET (dyn_cols, 'child1' AS CHAR) AS 'child1',
  COLUMN_GET (dyn_cols, 'child2' AS CHAR) AS 'child2'
FROM
  tbl_dyn_col_test
WHERE COLUMN_GET (dyn_cols, 'type' AS CHAR) = 'parent';


child1                           child2                                             
-------------------------------  ---------------------------------------------------
                                                                                    

SELECT
  COLUMN_JSON (dyn_cols)
FROM
  tbl_dyn_col_test
WHERE COLUMN_GET (dyn_cols, 'type' AS CHAR) = 'parent';

COLUMN_JSON (dyn_cols)                                                                                                       
-----------------------------------------------------------------------------------------------------------------------------
{"name":"Mary","type":"parent","child1":{"eyes":"brown","name":"Sue"},"child2":{"name":"Bob","grandchild":{"name":"baby"}}}  
  

2016년 12월 14일 수요일

MariaDB 와 MySQL Cluster 간의 복제

한번 테스트를 해봤다. 뜬금없이 MariaDB 와 MySQL Cluster 는 기본 Storge Engine이 틀리고 Base Code 버전이 틀려서 될까 했는데 해보니까 된다.


--
-- 01. 구성
-- binlog_format= ROW    

정상 적으로 복제가 이루어짐 


MariaDB (10.1.19-MariaDB) : Replication Master
  |
  +--> NDB-01(5.7.16-ndb-7.5.4-cluster-gpl-log) : Replication Slave 
       NDB-02(5.7.16-ndb-7.5.4-cluster-gpl-log) 
       

--
-- 02. Check
--

-. Master 에서 create table 구문에서 engine 절을 빼야 함 
(Default storge engine 이 틀림)

MariaDB(InnoDB:XtraDB)
Mysql-Cluster(NDB)

-. MySQL Cluster에 Slave 를 구성시 SQL NODE 한대에서만 구성해야 함


2016년 12월 8일 목요일

MySQL MHA 구성 정리

작업을 하면서 정리한 스크립트 입니다.

너무 오래 걸렸습니다.


1. MySQL MHA 구성 (Oracle Linux 7.3)

--
-- 01.DB Package, MHA Package Install 
-- and DB Config

Percona Database:
    www.percona.com
    
yum install http://www.percona.com/downloads/percona-release/redhat/0.1-3/percona-release-0.1-3.noarch.rpm
yum install Percona-Server-server-57 Percona-Server-devel-57 Percona-Server-tokudb-57 percona-toolkit percona-xtrabackup-24 jemalloc jemalloc hdparm

/* HDD Write Cache Off */
hdparm -W0 /dev/sdb
echo never > /sys/kernel/mm/transparent_hugepage/enabled
echo never > /sys/kernel/mm/transparent_hugepage/defrag

systemctl stop mysqld
systemctl disable mysqld

/* DB 설정 구성 (복제 구성) */

mha-01 <--> mha-02
 |
 +-> mha-03
 +-> mha-04
 
vi /etc/percona-server.conf.d/mysqld.cnf
delete auto.cnf after db copy

[mysqld]
#
# Remove leading # and set to the amount of RAM for the most important data
# cache in MySQL. Start at 70% of total RAM for dedicated server, else 10%.
innodb_buffer_pool_size = 1024M
#
# Remove leading # to turn on a very important data integrity option: logging
# changes to the binary log between backups.
log_bin=mha-bin-log
server_id = 51
binlog_cache_size                              = 8M
binlog_format                                  = ROW
max_binlog_size                                = 1024M
expire_logs_days                               = 3

relay-log                                       = mha-relay-log
relay_log_purge                                 = 0

replicate_ignore_db = mysql,sys,performance_schema,information_schema
log_slave_updates = 1
# read_only = 1
thread_handling=pool-of-threads
explicit_defaults_for_timestamp
log_timestamps=SYSTEM
#
# Remove leading # to set options mainly useful for reporting servers.
# The server defaults are faster for transactions and fast SELECTs.
# Adjust sizes as needed, experiment to find the optimal values.
join_buffer_size = 128M
sort_buffer_size = 2M
read_rnd_buffer_size = 2M
#
user=mysql
basedir=/usr
datadir=/data/MYSQL
tmpdir=/tmp
socket=/tmp/mysql.sock
port=3306
bind_address=0.0.0.0

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid
#
tokudb_cache_size=512m


mysql.cnf
[MYSQL]
socket                                          = /tmp/mysql.sock
no-auto-rehash
show-warnings
default-character-set                          = utf8
prompt                                          =\u@\h:\d\_\R:\m:\\s>
pager                                           ="less -n -i -F -X -E"

/* systemctl config */
systemctl edit mysql

[Service]
LimitNOFILE=65535
PIDFile=/var/run/mysqld/mysqld.pid
Nice=-2
Environment="LD_PRELOAD=/usr/lib64/libjemalloc.so.1"


show engines;
show plugins;


--
-- Config Replication
--

/* replication user create */

CREATE USER 'replication'@'192.168.189.%' IDENTIFIED BY 'audtlr2';
GRANT REPLICATION SLAVE, REPLICATION CLIENT  ON *.* TO 'replication'@'192.168.189.%' ;

CHANGE MASTER TO
 MASTER_HOST='192.168.189.52',
MASTER_USER='replication',
 MASTER_PASSWORD='audtlr2',
 MASTER_LOG_FILE='mha-bin-log.000003',
 MASTER_LOG_POS=154;


start slave;
stop slave;
reset slave;


--
-- 02. MHA Config
-- (Service VIP 는 HAProxy 를 써서 구성 함)   


Project Site : https://code.google.com/p/mysql-master-ha/

/* Install Package */

yum install perl-DBD-MySQL  perl-Config-Tiny perl-Log-Dispatch perl-Parallel-ForkManager
if os = oracle-7.3 then
    perl-Config-Tiny download
end

yum install mha4mysql-manager-0.56-0.el6.noarch mha4mysql-node-0.56-0.el6.noarch

mkdir /etc/mha
mkdir /data/MHA

touch app1.cnf mha_master.cnf

--
-- ssh 구성
-- (전 서버 SSH 구성)

ssh-keygen -t rsa
cat id_rsa.pub > authorized_keys

--
-- 
--
/etc/mha/app1.cnf

[server default]
# mysql user and password
user=root
password=audtlr2

ssh_user=root
ping_interval=3

# working directory on the manager
manager_workdir=/data/MHA

# manager log file
manager_log=/data/MHA/app1.log

# working directory on MySQL servers
remote_workdir=/data/MHA

master_binlog_dir=/data/MYSQL

  
[server1]
hostname=192.168.189.51
candidate_master=1
 
[server2]
hostname=192.168.189.52
candidate_master=1
  
[server3]
hostname=192.168.189.53
no_master=1

[server4]
hostname=192.168.189.54
no_master=1

[root@mha-03 mha]# more masterha_default.cnf 
[server default]

# 아래 부분은 HA Proxy 에서 처리
#secondary_check_script= masterha_secondary_check -s remote_host1 -s remote_host2
#master_ip_failover_script=/script/masterha/master_ip_failover
#shutdown_script= /script/masterha/power_manager
#report_script= /script/masterha/send_master_failover_mail

--
--
--

/* rc.local 에 등록 */
nohup masterha_manager --conf=/etc/mha/app1.cnf --global-conf=/etc/mha/masterha_default.cnf < /dev/null >> /data/MHA/mha_manager.log 2>&1 &

masterha_stop --conf=/etc/mha/app1.cnf --global-conf=/etc/mha/masterha_default.cnf

/* 사전 점검 */
masterha_check_status --conf=/etc/mha/app1.cnf --global-conf=/etc/mha/masterha_default.cnf
masterha_check_ssh --conf=/etc/mha/app1.cnf --global-conf=/etc/mha/masterha_default.cnf
masterha_check_repl --conf=/etc/mha/app1.cnf --global-conf=/etc/mha/masterha_default.cnf



-- relay log 삭제 설정
-- /etc/cron.d
--

#
0 5 * * * app /usr/bin/purge_relay_logs --user=root --password=audtlr2 --workdir=/data/MYSQL --disable_relay_log_purge >> /data/MHA/purge_relay_logs.log 2>&1

2016년 12월 1일 목요일

MariaDB Primary Key Plan

-. PK 를 사용 하여 조회 조건에 = 조회를 하면 select 는 당연히 상수 처리 해서 한건만 access 하는데 delete, update 는 상수 처리를 못해서 range scan 을 합니다. 옵티마이저가 같은 방식으로 처리를 한다면 delete, update 도 상수 처리를 해서 딱 한건만 access 하는 것이 맞을것 같은데요.. 혹시 이런 이유를 아시는 분 있을까요? 참고로 mariadb 10.0 대 버전인데 제가 볼때는 mysql도 같을거라 판단이 듭니다.

update or delete
 
+------+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+
| id   | select_type | table        | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+------+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+
| 1    | SIMPLE      | ox_campaigns | range | PRIMARY       | PRIMARY | 3       | NULL | 1    | Using where |
+------+-------------+--------------+-------+---------------+---------+---------+------+------+-------------+
 
select
 
+------+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
| id   | select_type | table        | type  | possible_keys | key     | key_len | ref   | rows | Extra |
+------+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+
| 1    | SIMPLE      | ox_campaigns | const | PRIMARY       | PRIMARY | 3       | const |    1 |       |
+------+-------------+--------------+-------+---------------+---------+---------+-------+------+-------+


참고 문서: http://bugs.mysql.com/bug.php?id=69887

MariaDB 에서 정렬후 limit 하는 쿼리에 대한 이상한 경우

아래와 같은 이상한 경우를 확인 했다. 이러면 항상 결과값을 확인을 해야 한다는 이야긴데… 그러나 MySQL 5.7.15 에서는 발생 안한다. 이럼 MySQL 아니면 Percona 인가 보다.
/**
 Test
 
 Ubuntu
 dbadmin@localhost:test 16:04:46>select version();
+--------------------------+
| version()                |
+--------------------------+
| 10.0.25-MariaDB-1~trusty |
+--------------------------+
 
+-----------------+
| @@version       |
+-----------------+
| 10.1.16-MariaDB |
+-----------------+

*/
 
--
-- 01. Test Data Table Create
--

create table test_tbl ( today datetime default current_timestamp);
 
insert into test_tbl(sysdate());
insert into test_tbl(sysdate());
insert into test_tbl(sysdate());
insert into test_tbl(sysdate());
 
dbadmin@localhost:test 16:02:06>select * from test_tbl;

+---------------------+
| today               |
+---------------------+
| 2016-10-11 16:02:00 |
| 2016-10-11 16:02:03 |
| 2016-10-11 16:02:05 |
| 2016-10-11 16:02:06 |
+---------------------+
4 rows in set (0.00 sec)
 
--
-- 02. Check SQL
--
 
dbadmin@localhost:test 16:02:39>select * from test_tbl order by today desc limit 1;
+---------------------+
| today               |
+---------------------+
| 2016-10-11 16:02:06 |
+---------------------+
1 row in set (0.00 sec)
 
+------+-------------+----------+------+---------------+------+---------+------+------+----------+----------------+
| id   | select_type | table    | type | possible_keys | key  | key_len |  ref | rows | filtered | Extra          |
+------+-------------+----------+------+---------------+------+---------+------+------+----------+----------------+
| 1    | SIMPLE      | test_tbl | ALL  | NULL          | NULL | NULL    | NULL |    4 | 100.00   | Using filesort |
+------+-------------+----------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)
 
Note (Code 1003): select `test`.`test_tbl`.`today` AS `today` from `test`.`test_tbl` order by `test`.`test_tbl`.`today` desc limit 1
 
--
-- 03. Check View SQL
--
 
dbadmin@localhost:test 16:02:51>select * from (select * from test_tbl order by today) aa limit 1;
 
+---------------------+
| today               |
+---------------------+
| 2016-10-11 16:02:00 |
+---------------------+
1 row in set (0.00 sec)
 
+------+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
| id   | select_type | table    | type | possible_keys | key  | key_len |  ref | rows | filtered | Extra |
+------+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
| 1    | SIMPLE      | test_tbl | ALL  | NULL          | NULL | NULL    | NULL | 4    | 100.00   |       |
+------+-------------+----------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)
 
Note (Code 1003): select `test`.`test_tbl`.`today` AS `today` from `test`.`test_tbl` limit 1
-- 이렇게 뷰로 감싸면 안에 있는 order by 가 쿼리 옵티마이징 이후 사라져 버린다.