mysql

- install
sudo apt-get install mysql-server
 > mysql account password ---> root:MY_PASS_IS

 edit /etc/mysql/my.cnf file
bind-address            = 192.168.0.5
bind-address            = 127.0.0.1 --> only local access

s ls -al /etc/mysql/my.cnf

 > 우분투에서는 alternatives 로 my.cnf 를 관리한다.
 : lrwxrwxrwx 1 root root 24 12월 24 23:07 /etc/mysql/my.cnf -> /etc/alternatives/my.cnf
 --> alternatives 가 사용됨

USER_ME@gig:/etc/mysql/mysql.conf.d$ sudo update-alternatives --config my.cnf
 : 대체 항목 my.cnf에 대해 (/etc/mysql/my.cnf 제공) 2개 선택이 있습니다.
 :   선택       경로                      우선순� 상태
 : ------------------------------------------------------------
 : * 0            /etc/mysql/mysql.cnf         200       자동 모드
 :   1            /etc/mysql/my.cnf.fallback   100       수동 모드
 :   2            /etc/mysql/mysql.cnf         200       수동 모드
 :
 : Press <enter> to keep the current choice[*], or type selection number:

 --> my.cnf ==> refer to /etc/mysql/mysql.cnf
--
!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/


sudo vi /etc/mysql/conf.d/mysql.cnf
[mysql]

[client]
default-character-set=utf8    # <-- add this line

--
sudo vi /etc/mysql/mysql.conf.d/mysqld.cnf
....
# localhost which is more compatible and is not less secure.
# bind-address    = 127.0.0.1  <-- comment
bind-address    = 0.0.0.0   <-- change

[mysqld]  <---- 이 섹션에 아래  5개 라인 추가
...
init_connect=SET collation=utf8_general_ci
init_connect=SET NAMES utf8
# default-character-set=utf8 # error
character-set-server=utf8 # new DB create with option character-set
collation-server=utf8_general_ci


- restart service
tail -f /var/log/mysql/error.log

sudo service mysql restart
or
sudo /etc/init.d/mysql restart
then check
sudo netstat -tap | grep mysql


: - mysql-client
: sudo apt-get install mysql-client

- connect
mysql -u root -p
or
mysql -h server_ip -P 3306 -u root -p


- create database
create database journaldev;
use journaldev;

create table person (
  id int unsigned auto_increment,
  name varchar(30),
  age int,
  gender char(3),
  grade int,
  primary key(idx)
 ) Engine='InnoDB' default charset='utf8';


- user add
 > 로컬에서 접속 모두 허가
GRANT ALL PRIVILEGES ON journaldev.* TO USER_ME@localhost IDENTIFIED BY 'MY_PASS_IS';

 > 외부에서 접속 모두 허가
GRANT ALL PRIVILEGES ON journaldev.* TO USER_ME@'%' IDENTIFIED BY 'MY_PASS_IS';


 > 특정 권한만 지정
GRANT INSERT,UPDATE,SELECT ON journaldev.* TO USER_ME@'localhost' IDENTIFIED BY 'MY_PASS_IS';

 > commit;
flush privileges;
status;

 > if super priviledge need - with grant option
#+begin_src
GRANT ALL PRIVILEGES ON journaldev.* TO 'USER_ME'@'%' WITH GRANT OPTION;
GRANT ALL PRIVILEGES ON journaldev.* TO 'USER_ME'@localhost WITH GRANT OPTION;
#+end_src

ex.1
#+begin_src
GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP,
    INDEX, ALTER, CREATE TEMPORARY TABLES
    ON *.* TO 'username'@'localhost' IDENTIFIED BY 'password';
#+end_src

ex. 2
#+begin_src
GRANT EXECUTE, PROCESS, SELECT, SHOW DATABASES, SHOW VIEW, ALTER, ALTER ROUTINE,
    CREATE, CREATE ROUTINE, CREATE TEMPORARY TABLES, CREATE VIEW, DELETE, DROP,
    EVENT, INDEX, INSERT, REFERENCES, TRIGGER, UPDATE, CREATE USER, FILE,
    LOCK TABLES, RELOAD, REPLICATION CLIENT, REPLICATION SLAVE, SHUTDOWN,
    SUPER
        ON *.* TO mysql@'%'
    WITH GRANT OPTION;
#+end_src

- mysql-workbench
: USER_ME@gig:~$ sudo apt-get install mysql-workbench

then run

$ mysql-workbench