MySQL

• MySQL DATE_Format

%a Abbreviated weekday name

%b Abbreviated month name

%c Month, numeric

%D Day of month with English suffix

%d Day of month, numeric (00-31)

%e Day of month, numeric (0-31)

%f Microseconds

%H Hour (00-23)

%h Hour (01-12)

%I Hour (01-12)

%i Minutes, numeric (00-59)

%j Day of year (001-366)

%k Hour (0-23)

%l Hour (1-12)

%M Month name

%m Month, numeric (00-12)

%p AM or PM

%r Time, 12-hour (hh:mm:ss AM or PM)

%S Seconds (00-59)

%s Seconds (00-59)

%T Time, 24-hour (hh:mm:ss)

%U Week (00-53) where Sunday is the first day of week

%u Week (00-53) where Monday is the first day of week

%V Week (01-53) where Sunday is the first day of week, used with %X

%v Week (01-53) where Monday is the first day of week, used with %x

%W Weekday name

%w Day of the week (0=Sunday, 6=Saturday)

%X Year of the week where Sunday is the first day of week, four digits, used with %V

%x Year of the week where Monday is the first day of week, four digits, used with %v

%Y Year, four digits

%y Year, two digits

※ 참조 : http://www.w3schools.com/sql/func_date_format.asp

• backup

mysqldump -u[userID] -p[password] --all_databases > dump.sql : 서버에 있는 모든 것의 논리 백업 생성
mysqldump -u[userID] -p[password] --databases[DB_name] > dump.sql : 서버에 있는 특정 DB의 논리 백업 생성

• restore

mysql -u[userID] -p[password] --databases[DB_name] < restore.sql : restore.sql 파일을 특정 DB에 insert

• 원격 접속 허용

1. MySQL 접속 :

mysql -uroot -p mysql

2. DB 생성 :

if> create database {DataBase Name};

else> insert into db (host, db, user, select_priv, insert_priv, update_priv, delete_priv, create_priv, drop_priv, grant_priv, references_priv, index_priv, alter_priv) values ('%','{DataBase Name}','{User ID}', 'Y','Y','Y','Y','Y','Y','Y','Y','Y','Y' );

3. 접속 계정 생성 : 

insert into user (host, user, password) values('%', '{User ID}', password('{User Password}'));

flush privileges;

4. 접근권한 설정 :

if>

localhost

grant all privileges on {DataBase Name}.* to {User Name}@localhost identified by '{User Password}' with grant option;

flush privileges;

Remote

grant all privileges on *.* to '사용자명'@'IP 주소' identified by '비밀번호';

flush privileges;

else if(접근 권한 해제)>

revoke ...

5. 등록확인 :

select host, user, password from user;

6. my.cnf 파일 수정 :

# Instead of skip-networking the default is now to listen only on

# localhost which is more compatible and is not less secure.

#bind-address           = 127.0.0.1