[SQL] MySQL 기본적인 명령어

1. 새로운 사용자 등록 (GRANT 이용)

(1) MySQL에서 모든 권한을 갖는 관리자용 사용자(root)를 추가할 때

mysql> GRANT ALL PRIVILEGES ON *.* to root@localhost IDENTIFIED BY 'password' \ WITH GRANT OPTION;

(2) 일반 사용자(user)에게 자신의 DB(mydb)의 모든 테이블에 모든 권한을 부여할 때

mysql> GRANT ALL PRIVILEGES on mydb.* to user@localhost IDENTIFIED BY 'password';

2. MySQL의 root 사용자 암호 바꾸기 (mysqladmin 이용)

(1) root 패스워드 변경하기.

mysql> mysqladmin -u root password new-passwd

(2) 잊어버린 root 암호 다시 설정하기

a. MySQL 모든 프로세서 죽이기 # killall mysqld

b. grant-table을 사용하지 않고 MySQL 데몬을 시작 # ./asfe_mysql --skip-grant-tables &

c. 사용자 테이블이 있는 mysql DB로 접속하여 root 패스워드 다시 설정 # mysql -u root mysql mysql> update user set password=password('newpassword') where user='root';

d. 새로운 암호를 적용하기 위해 flush privileges를 실행 mysql> flush privileges;

e. 다시 모든 MySQL 데몬을 죽인다 # killall mysqld

f. grant-table을 사용하기 위해서 정상적인 방법으로 데몬을 시작 # ./safe_mysqld &

3. 데이타베이스&테이블 생성 및 삭제

(1) 데이타베이스 생성 및 삭제하기

mysql> <create | drop> database <DB_name>

(2) 테이블 생성 및 삭제하기

mysql> <create | drop> table <table_name> (......); # mysql -u user_name -p db_name < create_table.sql

4. 데이타 검색

(1) 기본형식

mysql> selcet <column1[, column2, ...]> from <table_name> [<where_definition>];

(2) 정렬해서 검색하기

mysql> select <column1[, column2, ...]> from <table_name> [where <where_definition>] -> order by <column[, column2, ...]> [asc | desc];

(3) 특정 문자열이 포함된 칼럼 검색하기

mysql> select <column1[, column2, ...]> from <table_name> where like 'string';

5. 데이터 백업 및 복구

(1) 백업 # mysqldump -u <user_name> -p <DB_name> > <BackUp File Name>

(2) 복구 # mysql -u -u <user_name> -p <DB_name> < <BackUp File Name>

  일단 본론부터 말씀 드리면 잘못된 명령어를 사용하셨습니다.

mysqldump 라는 mysql 유틸리티는 데이타를 sql 텍스트형식으로 dump 받을때 사용하는 유틸리티 입니다.
dump 된 데이타를 복원하실떄는 mysql 클라이언트 유틸리티를 사용합니다.
* 데이타 백업 방법
DB name : smile
DB user : smile
DB passwd : test
이라고 가정을 했을경우

유닉스 환경 모드 (mysql 설치 디렉토리가 /usr/local/mysql) 이라는 가정하에
]#/usr/local/mysql/bin/mysqldump -u smile -p test smile > smile.sql

윈도우 환경 모드 (mysql 설치 디렉토리가 c:\mysql)
c:\mysql\bin\mysqldump -u smile -p test smile > smile.sql

* 데이타 복원 방법
DB name : smile
DB user : smile
DB passwd : test
이라는 가정을 했을 경우
-------------------------------------------------------------------------------------------------
 SQL 파일 실행문
-------------------------------------------------------------------------------------------------
유닉스 환경 모드 (mysql 설치 디렉토리가 /usr/local/mysql) 이라는 가정하에
]#/usr/local/mysql/bin/mysql -u smile -p test smile < smile.sql

윈도우 환경 모드 (mysql 설치 디렉토리가 c:\mysql)
c:\mysql\bin\mysql -u smile -p test smile < smile.sql

C:\Documents and Settings\정덕채>cd C:\APM_Setup\Server\MySQL\bin

C:\APM_Setup\Server\MySQL\bin>mysql -u mode -p mode < mode_backup_080804.sql

-------------------------------------------------------------------------------------------------
※ Mysql에서는 이와 다른 데이타 복원 방법은 몇가지 더 있습니다.
상황1)유닉스환경 -> 유닉스환경으로 복원
/usr/local/mysql/var/{DB명} 파일을 동일한 폴더에 복사하신뒤에 mysql 데몬을 새로
----------- 구동 하시면 됩니다.
DBMS 저장장소
(주의) 원본mysql 복원mysql이 동일 버전일경우에만 권해드립니다. 100% 장담못함

상황2)
기타 다른 구분자(Tab)에 의해서 필드가 나눠 있을경우
mysql 클라이언트에 접속한뒤에
load data infile '{파일절대경로}{파일명}' into table {테이블명};
기타 다른 구분자 에 의해서 필드가 나눠 있을 경우
load data infile '{파일절대경로}{파일명}' into table {테이블명} fields terminated by '{기타구분자}';

예를 들어서 /home/test/test.txt 파일에 필드구분자가 (,)로 구분되어 있고
Test 테이블에 입력하기를 원할경우에는 mysql 접속모드에서 아래와 같이 하시면 됩니다.
mysql>load data infile '/home/test/test.txt' into table Test fields terminated by ',';
출처 : 직접작성

------------------------------------------------------------------------------------------------------------
 
mysql 관리자 root 패스워드 설정

mysql>update user set password = password(‘password’) where user = ‘root’ ;

버전출력과 현재 날짜출력

mysql>select version(),current_date;

데이터베이스 목록확인

mysql>show databases ;

데이터베이스 생성

myslq>create database databasesname;

생성한 데이터베이스 등록 및 확인

mysql>insert into db values(‘%’,’databasesname’,’root’,’y’,’y’,’y’,’y’,’y’,

’y’,’y’,’y’,’y’,’y’);

mysql>select host, db, user from db;

데이터베이스 소유자 등록

myslq>insert into user(host,user,password)values(‘localhost’,’사용자’,

password(‘패스워드’);

테이블 생성

myslq>create table 데이블명(필드명 데이터타입 );

mysql 데이터 타입

int : 4bytes 정수 (integer)

char(n) : 문자의수가 n개인 문자열과 문자

varchar : 문자의수가 최대 n개인 문자열

text : 문자의수가 최대 65535 개인 문자열

date : 날짜데이터형

time : 시간데이터형

enum : 선택형

테이블 구조 확인

mysql>desc 테이블명

테이블필드 추가

mysql>alter table 데이블명 add [column] 필드명 필드타입

테이블 필드 속성 변경

mysql>alter table 데이블명 change [column] 기존필드명 새필드명 필드타입

테이블 필드 삭제

mysql>alter table 데이블명 drop [column] 필드명

테이블 삭제

mysql>drop table tablename;

데이터 검색하기

mysql>select 필드명(*) form 테이블명

데이터 입력하기

mysql>insert into 테이블명(필드명,필드명) values(‘입력데이터’,’입력데이터’);

데이터 수정하기

mysql>update 테이블명 set 필드명=필드값 검색조건[where]

데이터 삭제하기

mysql>delete form 테이블명 검색조건[where]

버전확인 cat /etc/issue

신규계정추가

mysql> grant all privileges on *.* to ID@localhost

패스워드 변경

mysql>UPDATE user SET Password=OLD_PASSWORD('패스워드') WHERE user='root';

 

mysql>SELECT * FROM `TABLE_NAME` WHERE 1 order by `id` DESC limit 1

데이터비우기

mysql>TRUNCATE `log`;

 

원격접근

~]#mysql -h IP -u root -p

 update user set host='%' where user='root';

php 원격접근실패 유형

특정 테이블 확인

mysql> show tables where Tables_in_DBNAME like 'tablename'

특정 테이블 conut function

mysql >CREATE ALGORITHM=UNDEFINED DEFINER=`root`@`%` SQL SECURITY DEFINER VIEW `COMMON_DATA`.`cnt_para` AS select now() AS `now()`,sum(`tables`.`TABLE_ROWS`) AS `ROW` from `information_schema`.`tables` where (`tables`.`TABLE_NAME` like 'tbl_par%');

중복제거한 갯수

SELECT count(distinct id) FROM `tablename`

테이블복구

* MYISAM 복구

/usr/local/mysql/bin/myisamchk --tmpdir=/home --recover tbl_articles.MYI

마지막에 입력한 id select

  mysql_query(conn, "SELECT LAST_INSERT_ID()");
  result = mysql_store_result(conn);
  while((row=mysql_fetch_row(result))!=NULL){
     printf("%d\n", atoi(row[0]));
  }