'Mysql'에 해당되는 글 21건

  1. 2015/03/12 visualp mysql bin-log 대처 방안,mysql binlog
  2. 2015/01/28 visualp mysql 트리거
  3. 2012/12/28 visualp order by cast(id as unsigned) desc
  4. 2012/08/31 visualp mysql, if 문
  5. 2012/05/14 visualp MySQL 에서 IF문 사용하기
  6. 2012/03/04 visualp MySQL INSERT의 사용법
  7. 2012/02/20 visualp 재퀴쿼리, 현제 부터 부모노드 까지 한방에 호출 하기
  8. 2012/02/17 visualp mysql TIMESTAMP
  9. 2011/05/27 visualp MYSQL LOG 관리 (mysql log 관리)
  10. 2011/02/25 visualp mysql - reqpire , analyze

METHOD
CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE

HOWTO

ALTER TABLE tb_cycle_data CHECK   PARTITION tb_cycle_data2013;

ALTER TABLE {TABLE-NAME} {METHOD}   PARTITION {PARTITION-TABLE-NAME};


22.3.4 Maintenance of Partitions

A number of table and partition maintenance tasks can be carried out using SQL statements intended for such purposes on partitioned tables in MySQL 5.7.

Table maintenance of partitioned tables can be accomplished using the statements CHECK TABLE, OPTIMIZE TABLE, ANALYZE TABLE, and REPAIR TABLE, which are supported for partitioned tables.

You can use a number of extensions to ALTER TABLE for performing operations of this type on one or more partitions directly, as described in the following list:

  • Rebuilding partitions.  Rebuilds the partition; this has the same effect as dropping all records stored in the partition, then reinserting them. This can be useful for purposes of defragmentation.

    Example:

    ALTER TABLE t1 REBUILD PARTITION p0, p1;
  • Optimizing partitions.  If you have deleted a large number of rows from a partition or if you have made many changes to a partitioned table with variable-length rows (that is, having VARCHAR, BLOB, or TEXT columns), you can use ALTER TABLE ... OPTIMIZE PARTITION to reclaim any unused space and to defragment the partition data file.

    Example:

    ALTER TABLE t1 OPTIMIZE PARTITION p0, p1;

    Using OPTIMIZE PARTITION on a given partition is equivalent to running CHECK PARTITION, ANALYZE PARTITION, and REPAIR PARTITION on that partition.

    Some MySQL storage engines, including InnoDB, do not support per-partition optimization; in these cases, ALTER TABLE ... OPTIMIZE PARTITION analyzes and rebuilds the entire table, and causes an appropriate warning to be issued. (Bug #11751825, Bug #42822) Use ALTER TABLE ... REBUILD PARTITION and ALTER TABLE ... ANALYZE PARTITION instead, to avoid this issue.

  • Analyzing partitions.  This reads and stores the key distributions for partitions.

    Example:

    ALTER TABLE t1 ANALYZE PARTITION p3;
  • Repairing partitions.  This repairs corrupted partitions.

    Example:

    Press CTRL+C to copy
     
    ALTER TABLE t1 REPAIR PARTITION p0,p1;

    Normally, REPAIR PARTITION fails when the partition contains duplicate key errors. In MySQL 5.7.2 and later, you can use ALTER IGNORE TABLE with this option, in which case all rows that cannot be moved due to the presence of duplicate keys are removed from the partition (Bug #16900947).

  • Checking partitions.  You can check partitions for errors in much the same way that you can use CHECK TABLE with nonpartitioned tables.

    Example:

    Press CTRL+C to copy
     
    ALTER TABLE trb3 CHECK PARTITION p1;

    This command will tell you if the data or indexes in partition p1 of table t1 are corrupted. If this is the case, use ALTER TABLE ... REPAIR PARTITION to repair the partition.

    Normally, CHECK PARTITION fails when the partition contains duplicate key errors. In MySQL 5.7.2 and later, you can use ALTER IGNORE TABLE with this option, in which case the statement returns the contents of each row in the partition where a duplicate key violation is found. Only the values for the columns in the partitioning expression for the table are reported. (Bug #16900947)

Each of the statements in the list just shown also supports the keyword ALL in place of the list of partition names. Using ALL causes the statement to act on all partitions in the table.

The use of mysqlcheck and myisamchk is not supported with partitioned tables.

In MySQL 5.7, you can also truncate partitions using ALTER TABLE ... TRUNCATE PARTITION. This statement can be used to delete all rows from one or more partitions in much the same way that TRUNCATE TABLE deletes all rows from a table.

ALTER TABLE ... TRUNCATE PARTITION ALL truncates all partitions in the table.

Prior to MySQL 5.7.2, ANALYZE, CHECK, OPTIMIZE, REBUILD, REPAIR, and TRUNCATE operations were not permitted on subpartitions (Bug #14028340, Bug #65184).

[원문]
https://dev.mysql.com/doc/refman/5.7/en/partitioning-maintenance.html

2018/01/09 18:23 2018/01/09 18:23
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/731

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/731

종 데이터를 횡 데이트로 만들기 (세로 - 가로)

횡으로 구성되어있는 데이터를 종으로 만드는 방법에 대해 설명합니다. 먼저 전, 후의 데이터를 비교해보세요.

처리 전 데이터

1
2
3
4
5
6
7
8
9
10
+-----+------+-------+-------+
| idx | name | class | score |
+-----+------+-------+-------+
|   7 | choi | kor   |    90 |
|   8 | choi | eng   |    80 |
|   9 | choi | math  |    70 |
|  10 | kim  | kor   |    60 |
|  11 | kim  | eng   |    85 |
|  12 | kim  | math  |   100 |
+-----+------+-------+-------+

처리 후 데이터

1
2
3
4
5
6
+------+------+------+------+
| name | kor  | eng  | math |
+------+------+------+------+
| choi |   90 |   80 |   70 |
| kim  |   60 |   85 |  100 |
+------+------+------+------+

처리 후 데이터를 보면 name 기준으로 kor, eng, math항목이 횡으로 나열된 것을 볼 수 있습니다.

종 데이터 샘플 생성

종 데이터로 사용할 사용할 가상의 샘플 데이터를 생성해 보겠습니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
CREATE TABLE `score` (
  `idx` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(32) NOT NULL,
  `class` varchar(32) NOT NULL,
  `score` int(11) NOT NULL,
  PRIMARY KEY (`idx`)
);
 
INSERT INTO score (name, class, score) VALUES ('choi', 'kor', 90);
INSERT INTO score (name, class, score) VALUES ('choi', 'eng', 80);
INSERT INTO score (name, class, score) VALUES ('choi', 'math', 70);
 
INSERT INTO score (name, class, score) VALUES ('kim', 'kor', 60);
INSERT INTO score (name, class, score) VALUES ('kim', 'eng', 85);
INSERT INTO score (name, class, score) VALUES ('kim', 'math', 100);

종 데이터 생성이 완료 되었습니다.

1
2
3
4
5
6
7
8
9
10
11
mysql> SELECT * FROM score;
+-----+------+-------+-------+
| idx | name | class | score |
+-----+------+-------+-------+
|   7 | choi | kor   |    90 |
|   8 | choi | eng   |    80 |
|   9 | choi | math  |    70 |
|  10 | kim  | kor   |    60 |
|  11 | kim  | eng   |    85 |
|  12 | kim  | math  |   100 |
+-----+------+-------+-------+

alias를 이용한 컬럼 생성

종 데이터를 kor, eng, math로 구성된 횡 데이터로 만들기 위해 alias를 이용 K, E, M 컬럼을 생성하고 kor데이터는 K에 eng데이터는 E에 math데이터는 M에 넣습니다.

값이 저장되지 않은 컬럼은 NULL이 저장됩니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
SELECT
      name,
      CASE WHEN class = 'kor' THEN score END AS K,
      CASE WHEN class = 'eng' THEN score END AS E,
      CASE WHEN class = 'math' THEN score END AS M
FROM score;
 
+------+------+------+------+
| name | K    | E    | M    |
+------+------+------+------+
| choi |   90 | NULL | NULL |
| choi | NULL |   80 | NULL |
| choi | NULL | NULL |   70 |
| kim  |   60 | NULL | NULL |
| kim  | NULL |   85 | NULL |
| kim  | NULL | NULL |  100 |
+------+------+------+------+

아직까지는 종 데이터의 형식이지만 이제 횡데이터로 만들기 위한 준비는 되었습니다.

횡 데이터 생성

위에서 만들었던 쿼리를 이용해서 임의의 테이블을 만듭니다.
임의의 테이블을 group by하고 alias를 이용해서 생성한 K, E, M 컬럼을 이용 횡데이터로 만듭니다.
K, E, M컬럼은 SUM 함수를 이용해서 더해주고 alias를 이용 kor, eng, math로 컬럼명을 지정합니다.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SELECT name, SUM(K) as kor, SUM(E) as eng, SUM(M) as math FROM
(
  SELECT
        name,
        CASE WHEN class = 'kor' THEN score END AS K,
        CASE WHEN class = 'eng' THEN score END AS E,
        CASE WHEN class = 'math' THEN score END AS M
  FROM score
)AS T GROUP BY name;
 
+------+------+------+------+
| name | kor  | eng  | math |
+------+------+------+------+
| choi |   90 |   80 |   70 |
| kim  |   60 |   85 |  100 |
+------+------+------+------+


[원문]http://blog.devez.net/307

참고 URL

http://stackoverflow.com/questions/1241178/mysql-rows-to-columns

2018/01/09 10:57 2018/01/09 10:57
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/730

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/730

[참고] : https://dev.mysql.com/doc/refman/5.7/en/alter-table-partition-operations.html

========================================================================================================
1. 파티션 지원 확인

SHOW VARIABLES LIKE '%partition%';

SHOW PLUGINS;

 

2. 파티션 추가

CREATE TABLE `테이블` (

  ....

)

PARTITION BY RANGE(함수(`필드명`)) (

  PARTITION `파티션명1` VALUES LESS THAN (값),

  PARTITION `파티션명2` VALUES LESS THAN (값),

  PARTITION `파티션명3` VALUES LESS THAN (MAXVALUE)

);

 

CREATE TABLE 이후 추가

ALTER TABLE `테이블` PARTITION BY RANGE(함수(`필드명`)) (

  PARTITION `파티션명1` VALUES LESS THAN (값),

  PARTITION `파티션명2` VALUES LESS THAN (값),

  PARTITION `파티션명3` VALUES LESS THAN (MAXVALUE)

);

 

# THAN (표현식) 에는 THAN (함수(값)) 형태로도 가능

#표현식에 사용할 수 있는 함수 종류 및 파티션의 종류는 아래 링크에서 참고

http://wyseburn.tistory.com/entry/MySQL-%ED%8C%8C%ED%8B%B0%EC%85%94%EB%8B%9D

 

3. 파티션 분할

ALTER TABLE `테이블` REORGANIZE PARTITION `파티션명3` INTO (

  PARTITION `파티션명3` VALUES LESS THAN (값),

  PARTITION `파티션명4` VALUES LESS THAN (MAXVALUE)

);

 

4. 파티션 삭제

ALTER TABLE `테이블` DROP PARTITION `파티션명1`;

 

5. 파티션 관리

ALTER TABLE REBUILD PARTITION;
ALTER TABLE OPTIMIZE PARTITION;
ALTER TABLE ANALYZE PARTITION;
ALTER TABLE REPAIR PARTITION;
ALTER TABLE CHECK PARTITION;

 

6. 파티션 완전삭제

ALTER TABLE `테이블` REMOVE PARTITIONING;



출처: http://wyseburn.tistory.com/entry/MySql-파티셔닝-정리 [메모장입니다.]
2018/01/08 15:31 2018/01/08 15:31
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/729

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/729

innodb_flush_log_at_trx_commit

Mysql RSS Icon ATOM Icon 2017/09/27 09:15 visualp


실 운영시 =2로 사용(안정성)
디비작업시(대량)인서트  = 잠시 0 설정 해줌

mariadb 콜솔에서 실행
set global innodb_flush_log_at_trx_commit = 0;


innodb_flush_log_at_trx_commit = 0
innodb_flush_log_at_trx_commit = 1
innodb_flush_log_at_trx_commit = 2

0 인 경우, MySQL 이나 OS가 갑자기 crash 된다면 최대 1초동안의 트랜잭션을 잃을 수 있다.

  • 1 인 경우, 안전하다.
  • 2 인 경우, OS가 갑자기 crash 된다면 최대 1초동안의 트랜잭션을 잃을 수 있다. 하지만 MySQL 장애시에는 이미 OS 영역으로 데이터는 넘어갔기 때문에 안전할 수 있다.
  • 각 값에 따라, 엄청난 성능을 보일 수 있다.
    • 지난번에 엄청난 양의 log를 위하여 MySQL을 사용하는 팀이 있었는데, 해당값을 1에서 0으로 수정함에 따라 성능이 7배 빨라지기도 했다.
    • 단순 select용의 slave나, 최대 1초정도의 트랜잭션은 무시할 수 있는 서비스 혹은 log를 저장할 서버라면 해당값을 1에서 0으로 변경할 수 있다.
    • MySQL을 가장 빠르고 쉽게 튜닝할 수 있는 parameter 중의 하나이다.
2017/09/27 09:15 2017/09/27 09:15
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/723

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/723

wait_timeout 설정

Mysql RSS Icon ATOM Icon 2017/09/20 14:21 visualp

[원문]https://sites.google.com/site/dist777study/home/db/mysql/wait_timeout
SET @@GLOBAL.wait_timeout=300
my.cnf 에
wait_timeout=300
추가

2017/09/20 14:21 2017/09/20 14:21
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/722

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/722

날짜 중복범위 검색

Mysql RSS Icon ATOM Icon 2017/07/18 13:38 visualp

날짜 중복확인:

(('사용자입력시작일' <= DB시작일 and DB시작일 <= '사용자입력종료일') or ('사용자입력시작일' <= DB종료일 and DB종료일 <= '사용자입력종료일') or (DB시작일 <= '사용자입력시작일' and '사용자입력종료일' <= DB종료일)) 

2017/07/18 13:38 2017/07/18 13:38
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/718

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/718


-- Maria DB max connection 수 확인

SHOW VARIABLES LIKE '%max_connection%'

-- Maria DB에 접속되어 있는 client 정보 리스트

SHOW PROCESSLIST;

-- Maria DB max connection 수 조절

SET GLOBAL max_connections = 10000;

//status 관련 값
show status like '%CONNECT%';

Max_used_connections <--현재 연결된 접속수

Threads_connected  <-- 연결되었던 최대 접속수

2016/10/13 09:45 2016/10/13 09:45
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/713

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/713

mysql max connection

Mysql RSS Icon ATOM Icon 2015/05/09 17:07 visualp
mysql> set global max_connections=300;
(mysql 을 재시작 안해도 max_connections 값이 바로 적용된다.)
또 다른 방법으로
mysql 설정파일(my.ini 또는 my.cnf )을 다음과 같이 설정해주면 된다.
[mysqld]
  max_connections = 300
설정파일 변경 후에는 mysql 재시작이 필요하다.

■ max_connections에 대한 설명
MySQL은 [ 최대 접속수 + 1 ]의 접속을 허용한다. "1"은 관리자 권한 접속을 나타낸다.
문제가 발생했을 경우 관리자가 접속할 수 있게 하기 위해서이다.

시스템에 접속수가 폭주해서 접속이 안되는 경우가 발생한다. (ERROR 1040 (08004): Too many connections)
이런 상황에서 일시적으로 접속수를 증가시킴으로써 에러를 해결하자~.

■ MySQL 접속수 관련 상태를 확인하는 방법
mysql> show variables like '%max_connect%';    
+--------------------+-------+
| Variable_name      | Value |
+--------------------+-------+
| max_connect_errors | 10000 |
| max_connections    | 100   |
+--------------------+-------+

mysql> show status like '%CONNECT%';
+--------------------------+-------+
| Variable_name            | Value |
+--------------------------+-------+
| Aborted_connects         | 200   |
| Connections              | 300   |
| Max_used_connections     | 101   |   ==> 현재 연결된 접속수
| Ssl_client_connects      | 0     |
| Ssl_connect_renegotiates | 0     |
| Ssl_finished_connects    | 0     |
| Threads_connected        | 101   |   ==> 연결되었던 최대 접속수
+--------------------------+-------+
7 rows in set (0.00 sec)

mysqladmin 실행해서 확인하는 방법
./mysqladmin -u root -p processlist
./mysqladmin -u  root -p variables | grep max_connections
2015/05/09 17:07 2015/05/09 17:07
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/684

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/684

1. MySQL Replication 환경에서 지우기

  - MySQL Replication MASTER 서버
    shell> mysql -u root -p
    mysql> RESET MASTER;

  - MySQL Replication SLAVE 서버
    shell> mysql -u root -p
    mysql> RESET MASTER;

2. MySQL Binary Log sequence number 또는 특정 일자로 지우기

  shell> mysql -u root -p
  mysql> PURGE BINARY LOGS TO 'mysql-bin.000015';
  shell> mysql -u root -p
  mysql> PURGE BINARY LOGS BEFORE '2009-05-01 00:00:00';

3. mysqladmin flush-logs 명령어를 통해서 MySQL Binary Log 지우기

   shell> mysqladmin -u root -p flush-logs

4. MySQL Binary Log 생성을 방지하는 방법

  /etc/my.cnf 파일에서 아래 라인을 주석 처리
  log-bin

5. MySQL Binary Log 를 특정 1주일까지만 생성 및 보관하기
  /etc/my.cnf 파일에서 아래 라인을 추가
  expire_logs_days = 7

[원문] http://faq.hostway.co.kr/Linux_DB/1307

 

2015/03/12 09:05 2015/03/12 09:05
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/672

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/672

mysql 트리거

Mysql RSS Icon ATOM Icon 2015/01/28 23:33 visualp
  트리거는 테이블에 INSERT, UPDATE, DELETE 가 발생 할 때 그 과정의 전후로 무언가를 하도록 지정 할 수 있다.

 

DELIMITER $$

CREATE TRIGGER 트리거이름
BEFORE UPDATE ON 테이블이름
FOR EACH ROW 
BEGIN 
 IF (NEW.AA <> OLD.AA) THEN
    SET new.BB = new.AA;
 END IF ;
END$$
;
 

 

위의 쿼리는 트리거의 한 예제이다.

 

이 쿼리는 특정 테이블에 UPDATE가 발생 할 때 AA 의 값이 이전값과 이후값이 다를 경우에 

 

BB 컬럼에 값을 새로운 AA의 값으로 치환하라는 트리거이다.

 

녹색 : AFTER, BEFORE 

붉은색 : INSERT, UPDATE, DELETE

푸른색 : 쿼리(INSERT, UPDATE, DELETE 등)

 

NEW : 새로운 값

OLD : 이전 값

AFTER 를 사용할 때 같은 테이블에 INSERT, UPDATE, DELETE를 하게된다면 문제가 발생할 수 있다.

[원문] http://wofmaker.blog.me/220116117593

2015/01/28 23:33 2015/01/28 23:33
받은 트랙백이 없고, 댓글이 없습니다.

댓글+트랙백 RSS :: http://blog.visualp.com/rss/response/665

댓글+트랙백 ATOM :: http://blog.visualp.com/atom/response/665