opid's document

Mysql 설정.pdf

MySQL 테이블내 데이터 모두 삭제하기

truncate [table name];
ex) truncate students;

MySQL 워크벤치 Read Only 테이블 변경하기

The table has no unique row identifier (primary key or a NOT NULL unique index)

현재 테이블에 PK(primary key)나 NOT NULL, unique index 속성이 없으므로 Read Only가 된다.

## 컬럼 추가
ALTER TABLE 테이블명 ADD 컬럼명 타입, 속성;

## 컬럼 속성 수정
mysql > ALTER TABLE 테이블명 MODIFY 컬럼명 타입,속성;
# 속성 : NOT NULL / AUTO_INCREMENT / PRIMARY KEY / FIRST / AFTER 컬럼명);
# ex) ALTER TABLE 테이블명 MODIFY 컬럼명 INT(20) NOT NULL AUTO_INCREMENT PRIMARY KEY FIRST;

## 컬럼 삭제
mysql > ALTER TABLE 테이블명 DROP 컬럼명;

## primary key 속성 삭제
mysql > ALTER TABLE 테이블명 DROP PRIMARY KEY;

## 테이블 내용 전체 삭제
mysql > TRUNCATE TABLE 테이블명;

참고사이트 [1]

MySQL Reserved Words

MySQL doc 링크

 Reserved Words in MySQL 5.6.22

캐릭터셋 확인하기

mysql> show variables like 'char%';

캐릭터셋 변경하기

MySQL이 설치된 곳에서 my.ini 파일에 아래 코드를 입력 후 mysql을 재가동시킨다.

없다면 my-default.ini 파일을 복사하여 이름을 바꾸어 준다.

# 2014-07-21 utf setting
character-set-client-handshake = FALSE
init_connect="SET collation_connection = utf8_general_ci"
init_connect="SET NAMES utf8"
character-set-server = utf8

[client]
default-character-set = utf8

[mysql]
default-character-set = utf8

[mysqldump]
default-character-set = utf8

오토커밋(AUTOCOMMIT) 상태 확인, 변경하기

상태 확인하기
SQL> select @@autocommit;

변경하기
# 설정
SQL> set autocommit = 1;
SQL> set autocommit = TRUE;
# 해제
SQL> set autocommit = 0;
SQL> set autocommit = FALSE;

함수 만들기

DELIMITER $$
DROP FUNCTION IF EXISTS `db_name.function_name`$$
CREATE  FUNCTION `db_name.function_name`(stID int, endID int, line int) RETURNS int
    DETERMINISTIC
BEGIN

Declare stOrd int;
declare endOrd int;
declare result int;

select ord into stOrd 
from busstoplinelist 
where BusLineID = line and BusStopID = stID;

select ord into endOrd 
from busstoplinelist 
where BusLineID = line and BusStopID = endID;

RETURN endOrd - stOrd;
    END$$
DELIMITER ;

select function_name(421, 423, 1525);

예외 처리

delimiter $$

DROP PROCEDURE IF EXISTS db_name.procedure_name$$

CREATE PROCEDURE db_name.procedure_name(IN num INT, IN ch VARCHAR(2))

BEGIN

     DECLARE err INT DEFAULT '0'; 
     DECLARE CONTINUE HANDLER FOR SQLEXCEPTION  SET err = -1; 

     START TRANSACTION;

     INSERT INTO table_name VALUES (num, ch);     // 성공
     INSERT INTO table_name VALUES (ch, num);     // 실패

     IF err < 0 THEN 
          ROLLBACK; 
     ELSE 
          COMMIT; 

     END IF; 

END $$

delimiter ;

참고사이트 Link

문제점

컴퓨터 강제종료시 발생하는 것 같다. 

2014-07-09 11:01:56 2280 [Note] Plugin 'FEDERATED' is disabled.
2014-07-09 11:01:56 1248 InnoDB: Warning: Using innodb_additional_mem_pool_size is DEPRECATED. This option may be removed in future releases, together with the option innodb_use_sys_malloc and with the InnoDB's internal memory allocator.
2014-07-09 11:01:56 2280 [Note] InnoDB: Using atomics to ref count buffer pool pages
2014-07-09 11:01:56 2280 [Note] InnoDB: The InnoDB memory heap is disabled
2014-07-09 11:01:56 2280 [Note] InnoDB: Mutexes and rw_locks use Windows interlocked functions
2014-07-09 11:01:56 2280 [Note] InnoDB: Compressed tables use zlib 1.2.3
2014-07-09 11:01:56 2280 [Note] InnoDB: Not using CPU crc32 instructions
2014-07-09 11:01:56 2280 [Note] InnoDB: Initializing buffer pool, size = 165.0M
2014-07-09 11:01:56 2280 [Note] InnoDB: Completed initialization of buffer pool
2014-07-09 11:01:56 2280 [Note] InnoDB: Highest supported file format is Barracuda.
2014-07-09 11:01:56 2280 [Note] InnoDB: The log sequence numbers 1810266 and 1810266 in ibdata files do not match the log sequence number 1810276 in the ib_logfiles!
2014-07-09 11:01:56 2280 [Note] InnoDB: Database was not shutdown normally!
2014-07-09 11:01:56 2280 [Note] InnoDB: Starting crash recovery.
2014-07-09 11:01:56 2280 [Note] InnoDB: Reading tablespace information from the .ibd files...
2014-07-09 11:01:56 2280 [ERROR] InnoDB: Attempted to open a previously opened tablespace. Previous tablespace bis/busstop uses space ID: 2 at filepath: .\bis\busstop.ibd. Cannot open tablespace mysql/innodb_index_stats which uses space ID: 2 at filepath: .\mysql\innodb_index_stats.ibd
InnoDB: Error: could not open single-table tablespace file .\mysql\innodb_index_stats.ibd
InnoDB: We do not continue the crash recovery, because the table may become
InnoDB: corrupt if we cannot apply the log records in the InnoDB log to it.
InnoDB: To fix the problem and start mysqld:
InnoDB: 1) If there is a permission problem in the file and mysqld cannot
InnoDB: open the file, you should modify the permissions.
InnoDB: 2) If the table is not needed, or you can restore it from a backup,
InnoDB: then you can remove the .ibd file, and InnoDB will do a normal
InnoDB: crash recovery and ignore that table.
InnoDB: 3) If the file system or the disk is broken, and you cannot remove
InnoDB: the .ibd file, you can set innodb_force_recovery > 0 in my.cnf
InnoDB: and force InnoDB to continue crash recovery here.

innodb_index_stats.frm 
innodb_index_stats.ibd 
innodb_table_stats.frm 
innodb_table_stats.ibd 
slave_master_info.ibd 
slave_relay_log_info.frm 
slave_relay_log_info.ibd 
slave_worker_info.frm 
slave_worker_info.ibd

참고사이트 링크1, 링크2, 링크3

테이블 이름 변경

ALTER TABLE [테이블명] RENAME [변경할 테이블명];

컬럼 이름 변경

ALTER TABLE [테이블명] CHANGE colname [변경할 컬럼명] [변경할 컬럼 타입];

컬럼 속성 수정

ALTER TABLE [테이블명] MODIFY [컬럼명] [변경할 컬럼 타입];

컬럼 추가

ALTER TABLE [테이블명] ADD [추가할 컬럼명] [추가할 컬럼 데이터형];

컬럼 삭제

ALTER TABLE [테이블명] DROP [삭제할 컬럼명];

다른 컬럼 다음으로 이동

ALTER TABLE [테이블명] MODIFY COLUMN [컬럼명 데이터형] AFTER [다른컬럼];

첫번째로 컬럼 이동

ALTER TABLE [테이블명] MODIFY COLUMN [컬럼명 데이터형] FIRST;

기존 컬럼을 지우고 맨 앞에 컬럼 추가

ALTER TABLE [테이블명] DROP [기존 컬럼], ADD [추가할 컬럼] [추카할 컬럼 데이터형] FIRST;

Primary key 속성 삭제

ALTER TABLE [테이블명] DROP PRIMARY KEY;

참고사이트 [1] [2] [3]

문제점

ERROR 1364 (HY000): Field 'ssl_cipher' doesn't have a default value

해결방안

로컬접근 허용
mysql> insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject)
     -> values ('localhost','사용자명',password('비밀번호'),'','','');

외부접근 허용
mysql> insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject)
     -> values ('%','사용자명',password('비밀번호'),'','','');

문제점

해결방안

mysqld --skip-grant-tables

mysql -u root  mysql
mysql> show databases;

문제점

SQLException: Access denied for user 'root'@'localhost' (using password: YES)

MySQL data 디렉토리 변경하기

확실한 방법인지는 모르겠다. my.ini을 찾아보려해도 없어 WorkBench 를 이용해 바꾸어 보았다.

(이에 대한 문제는 책임지지 않는다.)

1. 먼저 MySQL 을 Stop한 뒤에 WorkBench를 실행하고 왼쪽에 Options File를 클릭하고 datadir의 경로를 바꾸어 주었다.

2. 기존 data 폴더에 있는 두개의 디렉토리를 복사하여 변경한 디렉토리에 넣어준다.

3. MySQL 을 Start 한다.

환경변수 설정하기

MySQL 가 설치된 경로를 시스템변수 path 추가한다. 

즉, 편집을 누른 뒤 변수 값 제일 뒤에 ;C:\Program Files\MySQL\MySQL Server 5.6\bin 을 추가한다.

(주의, 변수값을 잘못 건들여 모두 지울 경우 포맷을 해야 할지도 모른다.)

LOAD DATA LOCAL INFILE 'D:/project/file.csv' 
INTO TABLE dbName.tableName
FIELDS TERMINATED BY ',' 
ENCLOSED BY '"'
IGNORE 1 ROWS;

참조 Link

MySQL DB export, import

Export(내보내기)
mysqldump -h 호스트명 -u 사용자명 -p DB명 > 경로\저장할파일명.sql
Import(가져오기)
mysql  -h 호스트명 -u 사용자명 -p DB명 < 경로\가져올파일명.sql

* C 드라이브에서 오류 발생할 수 있으니, 다른 드라이브에 저장 후 실행할 것.

MySQL data export, import

Export(내보내기)
select * into outfile "/temp/file.dump" fields terminated by '\t' optionally enclosed by '"' lines terminated by '\n' from [dababase.tablename];
Import(가져오기)
LOAD DATA INFILE 'file.dump' INTO TABLE [dababase.tablename] FIELDS TERMINATED BY '\t' OPTIONALLY ENCLOSED BY '"' LINES TERMINATED BY '\n';

참고사이트

1. MySQL ODBC 설치하기

2.1. MySQL Connector/ODBC Error 1918

2.2. 해결방안

C:\Windows\System32 안에 msvcr100_clr0400.dll 을 msvcr100.dll 로 바꾸어준다.

(구글에서 찾은 해결방법이므로 기존 파일을 백업하길 바라고, 다른 문제는 책임지지 않습니다.)

+ 추가 문제점

파일명을 변경하고 나면 MySQL Installer 가 실행되지 않았는다. 그래서 복사본이었던 파일을 msvcr_clr0400.dll 로 넣어두었다.

참고사이트

1. MySQL 설치파일 다운받기

먼저 링크를 통해 MySQL 홈페이지에서 설치파일을 받는다.

2. MySQL 설치하기

Connector/ODBC 이 설치가 되지않는다면 추후에 받도록 한다.

root의 비밀번호를 지정해준다.

데이터베이스 생성하기

데이터베이스 제거하기

! 변경사항 적용하기 위해 아래 명령어 사용.

MySQL root Password 확인하고 변경하기

mysql> show databases;
mysql> use mysql;

mysql> select host, user, password from user where user = 'root';

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

mysql >flush privileges;

MySQL 버전 확인하기

command

mysql --version

윈도우, 리눅스에서도 확인할 수 있다. 

단, 윈도우에서는 환경변수 설정이 되어있지 않다면 경로이동 후 명령어 실행해야한다.

query

SELECT version()

사용자 확인, 사용자 추가

mysql> use mysql;
mysql> select user, host from user;

로컬접근 허용
mysql> insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject)
     -> values ('localhost','사용자명',password('비밀번호'),'','','');

외부접근 허용
mysql> insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject)
     -> values ('%','사용자명',password('비밀번호'),'','','');

mysql> drop user 사용자명@호스트;

mysql> flush privileges;

mysql> grant all privileges on *.* to 사용자명@호스트
     -> identified by '비밀번호' with grant option;

mysql> grant all privileges on DB명.* to 사용자명@호스트 identified by '비밀번호';

mysql> grant select, insert on DB명.* to 사용자명@호스트 
     -> identified by '비밀번호';

mysql> grant update(컬럼1,컬럼2,컬럼3) on DB명.테이블명 to 사용자명@호스트
     -> identified by '비밀번호';

mysql> grant select on DB명.* to 사용자명@% identified by '비밀번호';

mysql> grant all privileges on *.* to 사용자명@'192.168.0.%' identified by '비밀번호';

mysql> REVOKE ALL PRIVILEGES ON *.* FROM 사용자명@호스트;

usage 권한 부여
mysql> GRANT USAGE ON *.* TO USER_NAME@HOST IDENTIFIED BY 'USER_NAME';
usage 권한 삭제
mysql> REVOKE USAGE ON *.* FROM USER_NAME@HOST;

mysql> SHOW GRANTS FOR 사용자명;

특정 아이디에 등록된 호스트 확인

select host from mysql.user where user='사용자명';

접속 ip 추가하기

▶ 모든 IP와 DB 허용하기

mysql> insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject)
     -> values('%','사용자명',password('비밀번호'),'','','');
mysql> grant all privileges on *.* to '사용자명'@'%';
mysql> flush privileges;

▶ 특정 IP에 특정 DB 허용하기

mysql> insert into user(host,user,password,ssl_cipher,x509_issuer,x509_subject)
     -> values('111.222.333.44','사용자명',password('비밀번호'),'','','');
mysql> grant all privileges on 특정DB.* to '사용자명'@'111.222.333.44';
mysql> flush privileges;

원격 접속하기

mysql -h 호스트명 -u 사용자명 -p

실행확인 : mysqladmin -u root -p ping

접속 : mysql -u root -p

DB이동 : use DB명

DB생성 : create database DB명;

테이블 생성 : create table 테이블명 (컬럼명 타입(크기), …);

테이블 삭제 : drop table 테이블명;

테이블 구조확인 : desc 테이블명;

데이터 입력 : insert info 테이블명 (컬럼명…) values (값…);

데이터 확인 : select * from 테이블명;  모두보기

   select 컬럼이름 from 테이블명;  특정컬럼

   select * from 테이블명 where 조건;  조건에 맞는 데이터

ex) select * from numdb where colnum>2;

데이터 변경 : update 테이블명 set 변경할 내용 where 조건;

ex) update numdb set colnum:=3, rownum:=4 where code='1001';

데이터 삭제 : delete from 테이블명 where 조건;

버전확인 : select version();