MYSQL 명령어

#MYSQL 리눅스 설치

http://blog.beany.co.kr/archives/2648#i-2

1. mysql 설치 확인 : #rpm -qa | grep mysql
2. mysql 설치 위치 확인 : #find / -name mysql
3. mysql 시작 : #/etc/rc.d/init.d/mysqld start

4. rpm으로 설치 했을때 mysql 의 위치
/usr/bin ; mysql 실행 모듈,명령어
/usr/lib ; mysql rpm 설치 디렉토리
/usr/lib/mysql ; 데이타베이스 생성되는 디렉토리 ( DB create 할때 디렉토리로 추가됨)
/etc/my.cnf ; mysql 설정 파일
( ** rpm으로 php 와 mysql 을 설치한 경우와 소스를 컴파일한 경우 있는 위치가 틀립니다. **)

5. mysql root 의 패스워드 설정 초기에는 root 사용자에 패스워드가 설정되어 있지 않으므로(null 값) Enter password: 에 그냥 엔터를 입력하면 됩니다.

#cd /usr/bin // mysql 명령어가 있는 디렉토리로 이동 합니다.
#mysql -u root -p mysql // mysql 접속
Enter password: //비밀번호 입력

mysql> //mysql 프롬프트 입니다.

1) root 사용자의 패스워드를 myroot로 설정 해 보겠습니다.
(** mysql 명령어는 모두 끝에 ; (세미콜론)을 붙여 주어야 합니다. **)

mysql> update user set password=password('myroot') where user='root';
Querk OK, 2 rows affected (0.00 sec)
Rows matched : 2 Changed : 2 Warnings : 0

mysql>quit // mysql 을 끝내는 명령어 입니다. 쉘프롬프트로 돌아 가겠죠.
(** 위 방법말고 #mysqladmin -u root -p password 'myroot' 해도 됩니다. )

변경된 root 사용자의 패스워드가 적용되기 위해서는 데이터베이스를 다시 불러와야 합니다.
# mysqladmin -u root reload

변경된 root 사용자 패스워드로 다시 mysql에 접속해 보겠습니다. Enter password: 에 변경된 패스워드인 myroot 를 입력합니다.

$ mysql -u root -p mysql
Enter password:
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Welcome to the MySQL monitor. Commands end with ; or g.
Your MySQL connection id is 1 to server version: 3.23.49

Type 'help;' or 'h' for help. Type 'c' to clear the buffer.

mysql>

루트 비번 변경
mysql> use  mysql;
mysql> update  user  set  password=password('123456') where user='root';
mysql> flush  privileges;

2) 새로운 데이터베이스 생성하기
새로운 데이터베이스를 생성하기 위해 셸 프롬프트 상에서 다음과 같이 입력합니다.생성할 데이터베이스 이름은 zboard 입니다.

mysql>create database zboard; // zboard 를 만듭니다.
Query OK, 0 rows affected (0.00 sec)

3) zboard 에 계정 사용자(ID:zman)를 주고 암호(Password:zpass)까지 설정해서 한방에 끝내겠습니다.
( ** insert문을 이용하는 것보다 grant 를쓰는것이 편합니다. **)

mysql>GRANT ALL PRIVILEGES ON zboard.* TO zman@localhost IDENTIFIED BY 'zpass' WITH GRANT OPTION;
Query OK, 0 rows affected (0.00 sec)

#어디서나 접속 가능 '%'

mysql>GRANT ALL PRIVILEGES ON zboard.* TO zman@'%'IDENTIFIED BY 'zpass' WITH GRANT OPTION;

4) 계정 등록을 확인해 보겠습니다.
mysql>use mysql; // 참고로 mysql 은 사용자가 등록되는 DB 입니다.root 도 여기등록 됩니다.
mysql>select * from user; // 위에서 만든 zman 이 사용자에 보여야 합니다.

데이터 베이스 시작,종료,재시작

 ./safe_mysqld &

 ./mysqladmin -u root -p shutdown

 ./mysqladmin -u root -p reload

 

데이터베이스 접속

$ mysql -u 사용자명 -p dbname

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

$ mysql -u root mysql

 

데이터베이스 생성/보기

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

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

 특정 데이타베이스를 사용하겠다고 선언한다.
 

 mysql> select database();

 현재 사용중인 데이터 베이스 알아보기

 mysql> USE dbname;

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

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

사용자 추가/삭제

 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';

#모든권한부여
 GRANT ALL PRIVILEGES ON dbname.* TO username@'%' IDENTIFIED BY 'password' with grant option;

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

비밀번호 변경
 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('새비밀번호');

 
테이블 생성/보기

 

테이블을 생성

 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> alter table php add email varchar(50) not null default '0';   
Query OK, 1 row affected (0.04 sec)   
Records: 1 Duplicates: 0 Warnings: 0  

컬럼 삭제는   
mysql> alter table php drop email;   
Query OK, 1 row affected (0.01 sec)   
Records: 1 Duplicates: 0 Warnings: 0  

컬럼명,타입 변경은   
mysql> alter table php change name testname varchar(12);   
Query OK, 1 row affected (0.01 sec)   
Records: 1 Duplicates: 0 Warnings: 0  

컬럼 타입 수정은   
mysql> alter table php modify testname varchar(14);   
Query OK, 1 row affected (0.01 sec)   
Records: 1 Duplicates: 0 Warnings: 0  

마지막으로 테이블명 수정은   
mysql> alter table php rename testphp;   
Query OK, 0 rows affected (0.00 sec)  

현재 상태 보기

 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;

 

SYSDATE 사용법

ORACLE = UPDATE TEST SET INPUT_DT = SYSDATE

MYSQL = UPDATE TEST SET INPUT_DT = SYSDATE()

 

접속 권한 주기

mysql> grant all privileges on *.* to userid@"127.0.0.1" identified by 'password1';

 

Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;

 

Query OK, 0 rows affected (0.00 sec)

 

MYSQL 문자셋 확인

 - 전체

mysql>\s

 - 해당 데이터 베이스

show variables like 'c%';

 - 설치제품 전체

show global variables like 'char%';

데이터베이스 문자셋 변경

use ascadb;
set character_set_client=utf8;
set character_set_results=utf8;
set character_set_connection=utf8;
set character_set_server=utf8;

ALTER DATABASE ascadb DEFAULT CHARACTER SET utf8;

테이블 및 컬럼 문자셋 변경

 

alter table C_USERS CHARSET=utf8;

alter table C_USERS modify USERNAME varchar(10) CHARACTER SET utf8 NOT NULL;

 

MYSQL 시퀀스 처리

 

auto_increment 값 1000부터 테이블 생성

create table 테이블 (
num int(11) unsigned not null auto_increment primary key
) type=myisam auto_increment=1000;

auto_increment 값 변경

alter table 테이블명 auto_increment=1000;

현재 auto_increment 값 알기

$SQL = "SHOW TABLE STATUS LIKE 'TableName'";

 

MySQL function사용

 

delemiter를 일시적으로 '$$'로 변경해 주고, 기존에 존재하는 function을 삭제하고(선택), 마지막으로 function을 생성하는 문장을 넣어줍니다.

마지막에 delimiter를 ';'로 다시 돌려준것도 봐주시구요. ^^

 

DELIMITER $$

 

DROP FUNCTION IF EXISTS 디비명.펑션이름$$

 

CREATE FUNCTION 디비명.펑션이름($변수1 VARCHAR(255), $변수2 DATE) RETURNS INT

BEGIN

DECLARE result INT DEFAULT -1;

SELECT COUNT(*) INTO result FROM 테이블;

 

RETURN result;

END $$

 

DELIMITER ;

사용법

 

select 디비명.펑션이름(변수값, 변수값);

 

혹시 function을 생성하는 중 아래와 같은 에러를 만난다면 다음 쿼리를 날려줍니다.

 

Error Code : 1418
This function has none of DETERMINISTIC, NO SQL, or READS SQL DATA in its declaration and binary logging is enabled (you *might* want to use the less safe log_bin_trust_function_creators variable)

SET Globallog_bin_trust_function_creators='ON';

 

Linux Mysql 설치 시 테이블명의 대소문자를 구분 설정

Linux에서 Mysql 설치 시 테이블명의 대소문자를 구분하는 것이 default 이다.

이것을 강제적으로 대소문자 구분하지 않게 하기 위해서는

아래와 같이 작업하면 된다.

- OS ; Centos 5.x

- DBMS : MySQL 5.0.xx

- Centos 설치 시 자동으로 설치되는 MySql에서 작업을 하였음

/etc/ 밑에 있는 mysql 설정파일인 my.cnf 를 오픈하여

[mysqld] 테크 아래에

lower_case_table_names=1

를 삽입한다.

$>cat /etc/my.cnf

~~~~

default-character-set=euckr
init_connect="set names euckr"
character-set-client-handshake = FALSE
lower_case_table_names=1

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

[client]
default-character-set=euckr

~~~~

설치한 후 환경변수를 확인하면 1로 변경되어 있는 것을 확인할 수 있다.

mysql> show variables like 'lower_case_table_names';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| lower_case_table_names | 1 |
+------------------------+-------+

 

백업 및 복구

/usr/local/mysql/bin 디렉토리로 이동하여 명령을 실행한다.

[ 백업 ] : mysqldump -u 사용자아이디 -p 백업받을DB > 백업파일명
./mysqldump -u root -p intranet > /home/eight256/intranet.sql
./mysqldump -u root -p realweb > /home/eight256/realweb.sql 

//특정 테이블만 백엄

mysqldump -u root -p intranet  testtable  > intranet_testtable.sql

[ 복구 ] : mysql -u 사용자아이디 -p 복구할DB < 백업파일명
./mysql -u root -p intranet < /home/eight256/intranet.sql
./mysql -u root -p realweb < /home/eight256/realweb.sql