티스토리 뷰

데이터베이스

MYSQL 쿼리 정리 (MariaDB)

초보의 CHOMAN 2015.06.11 13:35

flush privileges;



[SHEEL COMMAND]



create user 'newrun'@'192.168.10.222'  identified by 'newrun';



INSERT INTO mysql.user (User,Host, Password)

   VALUES ('newrun','192.168.10.222', password('newrun'));



mysql -u root

: root 계정으로 mysql 접속


mysql -u root -p 패스워드

: root 계정으로 mysql 접속 (패스워드 지정)



mysql_config --socket
- mysql.sock 화일 위치 경로 보기

/usr/local/mysql/bin/mysqladmin -u root password 비밀번호
- Mysql 설치 직후 root 패스워드가 적용안되있을때 mysqladmin 을 이용하여 설정하기

/usr/local/mysql/bin/mysqladmin -u root -p password 비밀번호
- Mysql 운영중 mysqladmin 을 이용하여 root 패스워드 변경

/usr/local/mysql/bin/mysqladmin -u 일반계정 -p password 12341234@

- Mysqladmin을 이용하여 일반계정 에대한 패스워드 설정



[명령어 & tool]



myisam_ftdump

MyISAM에서 사용하는 FULLTEXT 인덱스에 대한 정보 출력해주는 TOOL


mysqlaccess

DB에 대한 계정에 접근권한을 체크해주는 TOOL



mysqldumpslow queries.log 

slow query log 를 정리해주는 tools


mysqlslap

mysql 서버에 쿼리를 실행하여 부하를 유발할수 있는 Tool





[DB 생성 및 삭제]

drop database DB명;
- show database 해보면 빈 DB가 그대로 있는데 이거 까지 지워줄려면 data 디렉토리안에 해당 DB 디렉토리 삭제...


check table wp_users;

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

| Table              | Op    | Msg_type | Msg_text |

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

| wordpress.wp_users | check | status   | OK       |

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



[DB 접속]

※ mysql 이라는 데이터 베이스는 시스템 관리를 위해 설정됨 (절대 지우지 말것)

show databases;
- 데이터베이스 보기

create database homepage;
- homepage 라는 데이터베이스 생성

uset mysql;
- mysql 이라는 DB 선택 및 사용


[테이블]

show tables;
- 현제 선택한 데이터베이스의 테이블 목록 보기

show tables from 디비명;
- 디비명 이라는 DB의 table을 출력

select * from 테이블명;
- 테이블 레코드들 다 나열하기

select * from 테이블명 where xx="xx"
- 테이블 레코드들 중 xx의 값이 "xx"인것들만 출력하기

select * from 테이블명 limit 100;
- 100개 레코드만 출력

delete from 테이블명 where xx="xx";
- 테이블중에 xx의 값이 "xx"인것인 레코드 삭제

desc 테이블명;
테이블과 그의 구조등을 나열하는듯?

테이블 이름 바꾸기

alter table oldtable rename newtable;

 

테이블 복사하기
INSERT INTO `oldtable` SELECT * FROM `newtable`;

 

테이블 복사하기 (KEY 값 무시하기?)

INSERT ignore INTO `oldtable` SELECT * FROM `newtable`;

 

테이블 삭제하기

drop table 테이블명;


select * from member_info where ip like '222.xxx.142.%';
222.xxx.142 로 시작하는 모든 단어 테이블에서 검색

 

insert into window_use_port (service_name,port,protocol) values('MS-Terminal','3389','tcp');

 

select * from test where ip like '192.168.1.%' and order by inet_aton(ip) asc

 - inet_aton(ip) 라는 정의를 mysql에서 정의해서 나열해주는듯함 출력할 결과가 IP주소 일때 사용



테이블2에 있는 조건을 검색해서 테이블1에 INSERT 하기 : (INSERT 할때 컬럼수 맞추어줘야함)

INSERT INTO 테이블1 SELECT 테이블컬럼럼1, 테이블컬럼2... FROM 테이블2 WHERE {조건} ORDER BY num ASC;




[Mysql root 비번 변경]
/etc/rc.d/init.d/mysqld stop or killall mysqld
/usr/local/mysql/bin/mysqld_safe --skip-grant &
/usr/local/mysql/bin/mysql -u root mysql
mysql> update user set password=password('new password') where user='root';
mysql> flush privileges;
mysql> quit

/usr/local/mysql/bin/mysqld_safe &


[계정]



계정 생성


create user 'newrun'@'192.168.10.0'  identified by 'newrun';




mysql 계정 목록 보기


use mysql;
select * from user;


일반 계정 삭제하기 (root로 mysql 접속해야함)

drop user





계정 패스워드 변경

update user set password=password('비밀번호') where user='유저명';




사용자 계정 추가

사용자 계정 및 권한은 delete 와 insert into 명령으로 하지말것



grant all privileges on 디비명.* to 유저명@localhost identified by '유저 패스워드';
grant all privileges on elcap.* to elcap@localhost identified by 'password';
grant all privileges on wordpress.* to 'newrun'@'192.168.10.175' with grant option;


: 디비명.테이블명

grant all privileges on 디비명.* to 유저명@localhost identified by '유저 패스워드' WITH GRANT OPTION;


WITH GRANT OPTION 옵션은 다른 유저에게 권한을 줄수 있는 옵션인듯 하다.



특정 권한만 선택적으로 적용할 수 있다.

GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, INDEX, ALTER ON [DB명].[테이블명(*)] TO [유저명]@[서버명];


권한 삭제는 GRANT 명령어 반대로 하면 된다.

REVOKE ALL ON [DB명].[테이블명(*)] TO [유저명]@[서버명];


특정 권한만 삭제할 수 있다.

REVOKE SELECT ON [DB명].[테이블명(*)] TO [유저명]@[서버명];


권한만 삭제한다고 유저가 삭제되진 않는다. 아래와 같이 해줘야 완벽하게 유저가 제거된다.

DROP USER [유저명]@[서버명];


사용자에게 할당된 권한 확인 방법

show grants for root@localhost;


[백업 및 복구]

디비별로 백업
mysqldump -u root -p DB명 > 파일명.sql
mysqldump -u root -p elcap > elcap.sql

전체백업
mysqldump -u root -p --all-databases > 파일명.sql
mysqldump -u root -p --all-databases > namoelcap.sql

케릭터셋 옵션을 이용한 백업 (euckr, utf8)
mysqldump -u root -p --default-character-set=euckr DB명 > 파일명.sql

테이블만 덤프
mysqldump -u root -p -B DB명 --tables 테이블명1 테이블명2 테이블명3 > 파일명.sql

디비별로 복구
mysql -u root -p DB명 < 파일명.sql

전체복구
mysql -u root -p < 파일명.sql

게릭터셋 옵션을 이용한 복구 (euckr, utf8)
mysqldump -u root -p --default-character-set=euckr DB명 < 파일명.sql

 

아래와 같은 식으로도 복구 가능

/usr/local/mysql/bin/mysql -f -uuser_firewall -puser_firewall -Duser_firewall -h192.168.1.40 < /root/dump.sql

[ ] 

use mysql;

select * from user where user='root';

update user set Host='%' where user='root' and host='localhost';

flush privileges;


DB 전체 백업후 bzip2로 압축하기
mysqldump -u root -pspeed99 --all-database | /usr/bin/bzip2 > /home/DBBACKUP_`date +%Y-%m-%d`.dump.bz2


[etc]

mysql> status ; 정보 보여주는듯함
--------------
/usr/local/mysql/bin/mysql Ver 11.18 Distrib 3.23.58, for pc-linux (i686)

Connection id: 369
Current database:
Current user: root@localhost
Current pager: stdout
Using outfile: ''
Server version: 3.23.58-log
Protocol version: 10
Connection: Localhost via UNIX socket
Client characterset: euc_kr
Server characterset: euc_kr
UNIX socket: /tmp/mysql.sock
Uptime: 5 hours 33 min 4 sec

Threads: 1 Questions: 2478 Slow queries: 0 Opens: 14 Flush tables: 1 Open tables: 8 Queries per second avg: 0.124


[패스워드 복구]

/usr/local/mysql/bin/mysqld_safe --skip-grant-table & ; 권한없이 바로 DB에 접근 가능하다
mysql -u root ; 바로 mysql 접근 됨
use mysql;
update user set password=password('zzimsudaasam') where user='root';
flush privileges;



[테이블 복구]

mysql> analyze table 테이블명;
mysql> repair table 테이블명;

mysql> analyze table 테이블명; < 확인 >



[Mysql 점검]

Mysql status 점검
mysqladmin -uroot -p -i1 processlist


언어셋 보기
show variables like 'c%';



show engine innodb status;

InnoDB 상태 보기 (데드락 확인 가능)



SHOW STATUS LIKE 'innodb%';





[권한]



계정에 대한 권한 보기


show grants for rmtestbed@'%';

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

| Grants for rmtestbed@%                                                                                   |

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

| GRANT USAGE ON *.* TO 'rmtestbed'@'%' IDENTIFIED BY PASSWORD 

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



권한주기

GRANT ALL PRIVILEGES ON `masterdb`.* TO 'rmtestbed'@'%' WITH GRANT OPTION;




[LOCK]


global read locak


FLUSH TABLES WITH READ LOCK;

UNLOCK TABLES;


실행한 세션이 종료되면 Lock 자동 해제됨

select 조회만 가능하며 update insert 같은 쓰기 쿼리는 대기열 상태이며 Lock 이 해제되면 일괄 처리됨

'데이터베이스' 카테고리의 다른 글

트랜잭션 (Transaction)  (0) 2018.08.21
storage engine  (0) 2018.08.21
데이터베이스 엔진  (0) 2018.08.21
MariaDB Replication  (0) 2018.08.20
MYSQL 쿼리 정리 (MariaDB)  (0) 2015.06.11
mysql replication 리플리케이션  (0) 2015.06.10
댓글
댓글쓰기 폼