mariadb with galera setting

MySQL 2017. 11. 24. 16:41
반응형
## /etc/mysql/my.cnf 설정 파일

[mysqld]
# base directory
datadir=/var/lib/mysql
tmpdir=/tmp

# Charset
character_set_server=utf8
collation_server=utf8_unicode_ci
skip-character-set-client-handshake

# Logging
slow_query_log=on
slow_query_log_file=/var/log/mysql/mariadb-slow.log
log_warnings=2

# General logging has huge performance penalty therefore is disabled by default
general_log=off
general_log_file=/var/log/mysql/mariadb-error.log

long_query_time=3
log_queries_not_using_indexes=on

# Networking
bind_address=0.0.0.0
port=3306

# When a client connects, the server will perform hostname resolution,
# and when DNS is slow, establishing the connection will become slow as well.
# It is therefore recommended to start the server with skip-name-resolve to
# disable all DNS lookups. The only limitation is that the GRANT statements
# must then use IP addresses only.
skip_name_resolve

# Tuning
user=mysql
max_allowed_packet=256M
open_files_limit=10240
max_connections=8192
max-connect-errors=1000000
join_buffer_size = 192K
tmp_table_size = 16M
max_heap_table_size = 16M

## Generally, it is unwise to set the query cache to be larger than 64-128M
## as the costs associated with maintaining the cache outweigh the performance
## gains.
## The query cache is a well known bottleneck that can be seen even when
## concurrency is moderate. The best option is to disable it from day 1
## by setting query_cache_size=0 (now the default on MySQL 5.6)
## and to use other ways to speed up read queries: good indexing, adding
## replicas to spread the read load or using an external cache.
query_cache_size=0
query_cache_type=0

sync_binlog=0
thread_cache_size=16
table_open_cache=2048
table_definition_cache=1024

#
# InnoDB
#
# The buffer pool is where data and indexes are cached: having it as large as possible
# will ensure you use memory and not disks for most read operations.
# Typical values are 50..75% of available RAM.
# TODO(tomasz.paszkowski): This needs to by dynamic based on available RAM.
innodb_buffer_pool_size=10G
innodb_log_file_size=128M
innodb_file_per_table=1
innodb_doublewrite=0
innodb_file_format=Barracuda
innodb_flush_method=O_DIRECT
innodb_io_capacity=500
innodb_old_blocks_time=1000
innodb_read_io_threads=8
innodb_write_io_threads=8

# Clustering
binlog_format=ROW
default-storage-engine=InnoDB
innodb_autoinc_lock_mode=2
innodb_flush_log_at_trx_commit=2
innodb_locks_unsafe_for_binlog=1
wsrep_provider=/usr/lib/galera/libgalera_smm.so
wsrep_cluster_name=mariadb
wsrep_node_name=mariadb-0.mariadb-discovery
wsrep_node_address=172.16.10.252
wsrep_cluster_address="gcomm://mariadb-1.mariadb-discovery:4567,mariadb-2.mariadb-discovery:4567"
wsrep_sst_method=xtrabackup-v2
wsrep_sst_auth=root:password
wsrep_provider_options="gcache.size=512M; gcache.name=/tmp/galera.cache; gcache.page_size=128M; gmcast.listen_addr=tcp://0.0.0.0:4567"
wsrep_slave_threads=16
wsrep_on=1

[mysqldump]
max-allowed-packet=16M

[client]
default_character_set=utf8
protocol=tcp
port=3306
connect_timeout=10




## sync 상태 보기 (각 mariadb 서버에서)
MariaDB [(none)]> show status like 'wsrep_%';
+------------------------------+-------------------------------------------------------------+
| Variable_name                | Value                                                       |
+------------------------------+-------------------------------------------------------------+
| wsrep_apply_oooe             | 0.007694                                                    |
| wsrep_apply_oool             | 0.000000                                                    |
| wsrep_apply_window           | 1.014445                                                    |
| wsrep_causal_reads           | 0                                                           |
| wsrep_cert_deps_distance     | 25.653383                                                   |
| wsrep_cert_index_size        | 67                                                          |
| wsrep_cert_interval          | 0.007814                                                    |
| wsrep_cluster_conf_id        | 3                                                           |
| wsrep_cluster_size           | 3                                                           |
| wsrep_cluster_state_uuid     | 86cf6166-d00b-11e7-92f6-bf254b8a19b3                        |
| wsrep_cluster_status         | Primary                                                     |
| wsrep_commit_oooe            | 0.000000                                                    |
| wsrep_commit_oool            | 0.000000                                                    |
| wsrep_commit_window          | 1.006594                                                    |
| wsrep_connected              | ON                                                          |
| wsrep_desync_count           | 0                                                           |
| wsrep_evs_delayed            |                                                             |
| wsrep_evs_evict_list         |                                                             |
| wsrep_evs_repl_latency       | 0.000495221/0.000788639/0.00164732/0.00034555/19            |
| wsrep_evs_state              | OPERATIONAL                                                 |
| wsrep_flow_control_paused    | 0.000000                                                    |
| wsrep_flow_control_paused_ns | 0                                                           |
| wsrep_flow_control_recv      | 0                                                           |
| wsrep_flow_control_sent      | 0                                                           |
| wsrep_gcomm_uuid             | f14761b1-d026-11e7-a9c6-43e9a12bd841                        |
| wsrep_incoming_addresses     | 192.168.236.97:3306,192.168.28.197:3306,192.168.105.20:3306 |
| wsrep_last_committed         | 12525                                                       |
| wsrep_local_bf_aborts        | 0                                                           |
| wsrep_local_cached_downto    | 6127                                                        |
| wsrep_local_cert_failures    | 0                                                           |
| wsrep_local_commits          | 970                                                         |
| wsrep_local_index            | 2                                                           |
| wsrep_local_recv_queue       | 0                                                           |
| wsrep_local_recv_queue_avg   | 0.143574                                                    |
| wsrep_local_recv_queue_max   | 40                                                          |
| wsrep_local_recv_queue_min   | 0                                                           |
| wsrep_local_replays          | 0                                                           |
| wsrep_local_send_queue       | 0                                                           |
| wsrep_local_send_queue_avg   | 0.000978                                                    |
| wsrep_local_send_queue_max   | 2                                                           |
| wsrep_local_send_queue_min   | 0                                                           |
| wsrep_local_state            | 4                                                           |
| wsrep_local_state_comment    | Synced                                                      |
| wsrep_local_state_uuid       | 86cf6166-d00b-11e7-92f6-bf254b8a19b3                        |
| wsrep_protocol_version       | 7                                                           |
| wsrep_provider_name          | Galera                                                      |
| wsrep_provider_vendor        | Codership Oy <info@codership.com>                           |
| wsrep_provider_version       | 25.3.20(r3703)                                              |
| wsrep_ready                  | ON                                                          |
| wsrep_received               | 5579                                                        |
| wsrep_received_bytes         | 4241688                                                     |
| wsrep_repl_data_bytes        | 441822                                                      |
| wsrep_repl_keys              | 4283                                                        |
| wsrep_repl_keys_bytes        | 56574                                                       |
| wsrep_repl_other_bytes       | 0                                                           |
| wsrep_replicated             | 970                                                         |
| wsrep_replicated_bytes       | 560476                                                      |
| wsrep_thread_count           | 13                                                          |

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


wsrep_cluster_conf_id : 모든 db 에 똑같은 id 로 설정되어야 함

wsrep_cluster_size : 클러스터의 노드 갯수

wsrep_cluster_state_uuid : 고유 클러스터 ID 로 클러스터의 노드는 같은 값을 가져야 함

wsrep_cluster_status : 노드의 역할

wsrep_incoming_addresses : 클러스터 멤버의 ip 

wsrep_local_state_comment : 해당 노드가 클러스터의 멤버인지를 확인

wsrep_ready : 노드가 준비된 상태



## docker 내에서 클러스터 시작 (첫번째 클러스터)

$ sed -i 's/^safe_to_bootstrap:.*/safe_to_bootstrap: 1/' /var/lib/mysql/grastate.dat || :

$ docker-entrypoint.sh mysqld --wsrep-new-cluster


## docker 내에서 클러스터 시작 (두번째, 세번째 클러스터)

$ sed -i 's/^safe_to_bootstrap:.*/safe_to_bootstrap: 1/' /var/lib/mysql/grastate.dat || :

$ docker-entrypoint.sh mysqld







반응형
Posted by seungkyua@gmail.com
,
반응형
I. sql 보안
    1. mysqli->real_escape_string()
        - characters encoded are NUL (ASCII 0), \n, \r, ', ", Contrl-Z

II. php 애플리케이션 보안
    1. htmlspecialchars()
        - &, <, > 을 html entity로 변환

    2. htmlspecailchars()
        - entity 로 표현될 수 있는 모든 것을 교체
        - 두번째 파라미터
            . ENT_COMPAT : 큰따옴표를 &quot;로 변환하되 작은 따옴표는 그대로 둔다.
            . ENT_QUOTES  : 작은따옴표와 큰따옴표 모두를 각각 &#39; 와 &quot; 로 변환
            . ENT_NOQUOTES(기본값) : 어떠한 따옴표도 변환하지 않는다.
        - 두번째 파라미터
            . UTF-8 : 문자열 인코딩 타입

        예) $str = htmlspecailchars($input_str, ENT_QUOTES, "UTF-8");
              echo nl2br($str);

    3. PHP 단방향 해쉬 암호화 알고리즘
        - crypt() < md5() < sha1()
            .sha1() 함수는 40개의 문자열로 리턴된다.

    4. MySQL 단방향 암호화 알고리즘
        - sha1()

III. php 서버설정 보안
    1. php.ini 파일에서 allow_url_fopen을 막는다.
    2. 웹서버에서 .php 와 .html 파일에 대한 요청만을 수용한다.
    3. 사용자가 파일을 읽는 경로를 보내면 상대경로와 절대경로를 잘 파악하여 중요한 파일을 못읽게 한다.

IV. Javascript 보안
    1. XSS 공격 막는 방법
        - 동적으로 생성되는 데이터에 다음의 코드를 추가한다.
            .  content = content.replace(/</g, "&lt;").replace(/>/g, "&gt;");

반응형
Posted by seungkyua@gmail.com
,
반응형
I. 데이터베이스 백업하기
    1. 테이블 업데이트를 막고 백업하기
        sql>flush tables;
        sql>lock tables <table명> [read | write]
        #> cp 데이터 파일 복사 하기
 
    2. mysql_dump를 사용하기
        #>mysqladmin shutdown
        #>mysqladmin start --log-bin[=logfile]
        #>mysqldump --opt --all-databases > all.sql

    3. mysqlhotcopy 스크립트 사용
        #>mysqladmin shutdown
        #>mysqlhotcopy database </path/for/backup>
        #>mysqladmin start
         
II. 데이터베이스 복구
    1. 테이블의 문제
        #>myisamchk -r 

    2. 두번째 방법으로 백업했을 경우
        - 백업파일의 sql 실행 : 백업 시점의 데이터베이스를 새로 만듬
        - 그 이후의 저장된 갱신자료가 로그 파일에 저장되므로 그 파일로 데이터베이스를 갱신
           #>mysqlbinlog hostname-bin.[0-9]* | mysql

 
반응형
Posted by seungkyua@gmail.com
,

MySQL EXPLAIN 사용

MySQL 2011. 5. 19. 15:09
반응형
1. 문장 끝에 ; 대신 \G 를 사용하면 결과를 세로로 볼 수 있다.
2. select  문장 앞에 explain  키워드를 사용하면  플랜을 볼 수 있다.
explain
select * from User \G 

I. plan 결과 설명
    ※ http://dev.mysql.com/doc/refman/5.5/en/using-explain.html

    1. select_type 값
        - SIMPLE
           . 평범한 SELECT 문장
        - PRIMARY
           . 하위 쿼리나  UNION을 사용할 때 외부(첫 번째) 쿼리
        - UNION
           . UNION 두 번째나 다음 번 쿼리
        - DEPENDENT UNION
           . UNION에서 첫 번째 쿼리에 따른 두 번째나 다음 번 쿼리
        - UNION RESULT
           . UNION 겨로가
        - SUBQUERY
           . 내부 하위 쿼리
        - DEPENDENT SUBQUERY
           . 첫 번째 쿼리에 따른 내부 하위 쿼리(즉, 상관 하위 쿼리)
        - DERIVED
           . FROM 절에서 사용한 하위 쿼리
        - UNCACHEABLE SUBQUERY
           . 그 결과가 캐쉬될 수 없으며 각 행마다 다시 계산되어야 하는 하위 쿼리
        - UNCACHEABLE UNION
           . UNCACHEABLE SUBQUERY에 속한 UNION에서 두 번째나 그 이후의 SELECT

    2. table
        - 쿼리에 응답하는 테이블 명

    3. type
        - 쿼리에서 테이블이 어떻게 조인되는지 설명
        - const 또는 system
           . 테이블이 한 번만 읽어온다. 테이블에 row가 단 하나만 있을 때 일어날 수 있다.
        - eq_ref
           . 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 한 row씩 읽는다.
             테이블의 인덱스를 사용할 때 조인이 사용되며 인덱스는 UNIQUE하거나 기본 키이다.
        - fulltext
           . fulltext 인덱스를 사용하여 조인한다.
        - ref
           . 조인하는 테이블에서 row 집합을 읽어올 때마다 이 테이블에서 대응되는 row 집합을 읽어온다.
             조인 조건에 따라서는 한 행만 선택할 수 없을 때, 조인에서 키의 일부분만이 사용되었을 때,
             혹은 키가 UNIQUE 하지 않거나 기본 키가 아닐 때
        - ref_or_null
           . ref 쿼리와 비슷하지만 NULL 인 row 도 찾아본다. (하위 쿼리에서 가장 많이 사용된다)
        - index_merge
           . Index Merge 라는 특별한 최적화 기법이 사용되었다.
        - unique_subquery
           . 유일한 한 줄이 리턴될 때 IN 하위 쿼리에서 ref를 대신하여 사용할 수 있다.
        - index_subquery
           . unique_subquery 와 비숫하지만 인덱스된 유일하지 않은 하위 쿼리에서 사용한다.
        - range
           . 조인하는 테이블에서 row 집합마다 이 테이블 중 특정 범위에 들어가는 row들을 읽어온다.
        - index
           . 모든 인덱스를 읽는다.
        - ALL
           . 이 테이블의 모든 행을 읽는다.

    4. rows
        - 조인을 실행하기 위해 테이블마다 읽어야 하는 row의 수를 대충 계산
          쿼리가 사용하는 총 row의 수를 얻기 위해서는 값들을 곱해야 한다.

    5. possible_keys
        - 테이블을 조인하기 위해 사용할 수 있는 키
        - PRIMARY 는 테이블마다 일반적으로 있기 때문에 대부분 나온다. 

    6. key
        - row 을 조인할 때 실제로 사용하고 있는 키
        - NULL 은 키를 사용하고 있지 않다는 뜻이다.

    7. key_len
        - 사용된 키의 길이를 나타낸다.

    8. ref
        - 테이블에서 row를 선택할 때 사용한 키를 나타낸다.

    9. Extra
        - 어떻게 조인을 실행하는가에 대한 정보
        - Distinct
           . 처음으로 일치하는 줄이 발견되면 끝난다.
        - Not exists
           . LEFT JOIN 을 사용하여 쿼리가 최적화되어 있다.
        - Range checked for each record
           . 조인하는 테이블에서 row 집합을 읽어올 때마다 사용할 수 있는 인덱스를 찾는다.
        - Using filesort
           . 테이터를 정렬하려면 두 번 읽어야 한다. (따라서 시간도 두 배 걸린다)
        - Using index
           . 테이블에 있는 모든 정보를 인덱스에서 얻어왔다. 따라서 실제 row는 읽지 않았다.
        - Using join buffer
           . 조인 버퍼를 사용하여 테이블을 부분적으로 읽어 들인다. 그 후 쿼리를 수행할 때 버퍼에서 각 행을 추출한다.
        - Using temporary
           . 이 쿼리를 실행하는 동안 임시 테이블을 사용하였다.
        - Using where
           . 행을 선택하는 데 where 절을 사용하였다.

II. sql 쿼리 튜닝
    1. 조인 최적화를 위해 키 분배를 검사
        - myisamchk 유틸리티 사용
            #>myisamchk --analyze <pathtomysqldatabase/table명>
            #>myisamchk --analyze <pathtomysqldatabase/*.MYI>
            #>myisamchk --analyze <pathtomysqldatadirectory/*/*.MYI>
        - Analyze Table 문을 사용
            sql>analyze table customers, orders, order_items, books;

    2. 테이블 최적화
        - 테이블의 단편화를 없앤다.
            sql>optimize table <table명>;
            #>myisamchk -r table
        - 테이블의 인덱스를 정렬하고 인덱스에 맞게 데이터를 재배치 한다.
            #>myisamchk --sort-index --sort-records=1 <pathtomysqldatadirectory/*/*.MYI>





반응형
Posted by seungkyua@gmail.com
,

MySQL 데이터타입

MySQL 2011. 5. 19. 10:59
반응형
I. 문자열
    1. char(60)
    2. varchar(8192)
    3. longtext

II. boolean
    1. enum('N', Y')  혹은  enum('FUNCTION', 'PROCEDURE')
    2. set('Select', 'Insert', 'Update')

III. 숫자
    1. bigint(21) unsigned   혹은 bitint(10)

IV. 시간
    1. datetime
        - 값 : 2011-05-19 10:06:34
    2. timestamp(14)




반응형
Posted by seungkyua@gmail.com
,