create user 'user-name'@'%' identified by 'password-value';
grant all privileges on *.* to 'user-name'@'%';
flush privileges;mysql> update user set password='' where user='root';
ERROR 1054 (42S22): Unknown column 'password' in 'field list'
- mysql 비밀번호 변경시 5.7에는 password 컬럼이 없어서 위의 쿼리로는 업데이트가 안된다
mysql> describe user;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
| Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
| Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
| Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
| Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
| Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
| Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
| Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
| Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
| Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
| Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
| Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
| Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
| Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
| Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
| Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
| ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
| x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
| max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
| max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
| authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
| password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
| account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
describe user;명령어로 조회보았다password- >authentication_string으로 변경된듯하다 (뇌피셜)
update user set authentication_string=password('자신의_비밀번호') where user='root';
flush privileges; # 즉시 적용- 해당 업데이트 문으로 쿼리 변경이 가능하다
- 여담으로 로컬 데이터베이스 환경은 비밀번호가 없는 것이 편리하고 좋아서 ''으로 설정해서 사용하고 있다.
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT, TABLE_SCHEMA
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME LIKE '%colum%' and
TABLE_CATALOG = 'sample'
;COLUMN_NAME LIKE '%colum%조회 하고자하는 칼럼 이름TABLE_CATALOG = 'sample'조회 대상 데이터베이스
| TABLE_NAME | COLUMN_NAME | COLUMN_COMMENT | TABLE_SCHEMA |
|---|---|---|---|
| orders | member_id | sample |
SELECT TABLE_NAME, COLUMN_NAME, COLUMN_COMMENT
FROM INFORMATION_SCHEMA.COLUMNS
WHERE
COLUMN_NAME LIKE 'business_category%' and
TABLE_CATALOG = 'sample';위 처럼 comment를 조회할 수 있다