MySQL, MariaDB 비밀번호 변경 및 유저 생성

MySQL 5.7 미만 버전

mysql> use mysql;

mysql> update mysql.user set password=password('비밀번호') where user='root';

mysql> flush privileges;

mysql> select user,host,password from mysql.user;

mysql> exit

 

MySQL 5.7 

mysql> use mysql;

mysql> update user set authentication_string=password('비밀번호') where user='root';

 

우분투 한정 mysql> update mysql.user set plugin='mysql_native_password', authentication_string=password('비밀번호') where user='root';
mysql> flush privileges;

mysql> select user,host,authentication_string from mysql.user;

mysql> exit

 

MySQL 8.0

mysql> use mysql;

mysql> alter user 'root'@'localhost' identified by '비밀번호';

mysql> alter user 'root'@'localhost' identified with mysql_native_password by '비밀번호';

mysql> select user,host,authentication_string from mysql.user;

mysql> flush privileges;

 

MariaDB 변경 10.0 이상

MariaDB []> use mysql;

MariaDB [mysql]> set password=password('qwer1234');

MariaDB [mysql]> alter user 'root'@'localhost' identified by 'qwer1234';

MariaDB [mysql]> update user set authentication_string=password('qwer1234') where user='root';

MariaDB [mysql]> update user set password=password('qwer1234') where user='root';

MariaDB [mysql]> flush privileges;

 

MariaDB 10.4 root 비밀번호 변경

MariaDB [mysql]> alter user root@localhost identified via mysql_native_password using password("비밀번호");

 

MariaDB [mysql]> drop user ''@'localhost';

MariaDB [mysql]> select user,host,password from mysql.user;

MariaDB [mysql]> flush privileges;

MariaDB [mysql]> quit

mysql -u root -p

 

# 일반계정 만들어주기

mysql> use mysql;

mysql> create user '일반유저ID'@'localhost' identified by '비밀번호';

mysql> create user '일반유저ID'@'%' identified by '비밀번호';

 

# 일반계정 권한주기

mysql> grant all privileges on 데이터베이스이름.테이블이름 to '일반유저ID'@'localhost' identified by 'qwer1234비밀번호';

mysql> grant all privileges on *.* to '일반유저ID'@'localhost' identified by 'qwer1234비밀번호';
mysql> grant all privileges on 데이터베이스이름.테이블이름 to '일반유저ID'@'%' identified by 'qwer1234비밀번호';

mysql> grant all privileges on *.* to '일반유저ID'@'%' identified by 'qwer1234비밀번호';

mysql> grant all privileges on *.* to '일반유저ID'@'IP' identified by 'qwer1234비밀번호';
mysql> flush privileges;

mysql> select user,host from mysql.user;

 

권한을 all privileges 가 아닌 다른것으로 할 수 있다. select, insert, update, delete, all privileges 5개중 고르세요 중복으로 기입가능

 

# 일반계정 삭제

mysql> use mysql;

mysql> delete from user where user='계정이름';

 

# 권한 부여 

mysql> grant [부여할 권한들] on [DB명].[테이블명] to '[사용자명]'@'[접속호스트] 

mysql> grant select, insert on shopDB.* to 'cat'@'localhost' 

 # 권한 제거 

mysql> revoke [제거할 권한들] on [DB명].[테이블명] from '[사용자명]'@'[접속호스트]  

mysql> revoke insert  on shopDB.* from 'cat'@'localhost' 

 

# 비밀번호 변경 

mysql> update user set password=password('변경할비밀번호') where user='사용자명'; 

mysql> update user set password=password('1111') where user='lion'; 

 

# 사용자 제거 

mysql> drop user '사용자명'@'접속계정' 

mysql> drop user 'cat'@'localhost' 

 

# 계정 권한 보기

mysql> use mysql;mysql> show grants for '계정이름'@'호스트';