mysql 5.7 패스워드 초기화및 my.cnf > Mysql

• 목록
• 답변
• 글쓰기

1. mysql 구동 중지

 

#/etc/init.d/mysqld stop

 

2. mysql safe mode로 구동

 

#/usr/local/mysql/bin/mysqld_safe --skip-grant-tables  &

 

3. safe_mode로 구동된후 mysql접속후 패스워드 변경

 

#mysql

 

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed

 

mysql> UPDATE user SET authentication_string=PASSWORD('변경할암호') WHERE User = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 1

 

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

mysql> exit
Bye
 

4. mysql 재구동

 

[root@localhost ~]# /etc/init.d/mysqld stop
Shutting down MySQL..2016-08-29T02:48:32.960623Z mysqld_safe mysqld from pid file /usr/local/mysql/data/localhost.localdomain.pid ended
                                                          [  OK  ]
[1]+  Done                    /usr/local/mysql/bin/mysqld_safe --skip-grant-tables
[root@localhost ~]# /etc/init.d/mysqld start
Starting MySQL.                                            [  OK  ]

 

5. 재구동후 접속확인.

 

[root@localhost ~]# mysql -u root -p
Enter password:
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.14

Copyright (c) 2000, 2016, Oracle and/or its affiliates. All rights reserved.

Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

 

mysql> show databases;
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

 

6. 위에러에대한 조치

 

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY '변경된 암호';
Query OK, 0 rows affected (0.00 sec)

 

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

 

7. 다시 재접속하면 정상적으로 출력되는걸 확인할수 있다.

 

mysql> show databases;
+--------------------+
| Database          |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

 

8. my.cnf 파일 설정부분

 

아래내용은 이용하실때 조금씩 수정해가면서 이용하셔야합니다.

그대로 긁어갔을시에 구동에러가 나는경우가 발생되실수 있습니다.

 

[root@localhost ~]# vi /etc/my.cnf


[client]
default-character-set = utf8
port = 3306
socket = /var/lib/mysql/mysql.sock

# default character set settings
#default-character-set = utf8


[mysqld]
socket=/var/lib/mysql/mysql.sock
datadir=/var/lib/mysql

#
skip-external-locking
key_buffer_size = 384M
max_allowed_packet = 1M
table_open_cache = 512
sort_buffer_size = 2M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
myisam_sort_buffer_size = 64M
thread_cache_size = 8
query_cache_size = 32M

#dns query
skip-name-resolve

#connection
max_connections = 1000
max_connect_errors = 1000
wait_timeout= 60

#slow-queries
slow_query_log = /var/lib/mysql/slow-queries.log
long_query_time = 3
#log-slow-queries = /var/lib/mysql/mysql-slow-queries.log


symbolic-links=0

### log
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

###chracter
character-set-client-handshake=FALSE
init_connect = SET collation_connection = utf8_general_ci
init_connect = SET NAMES utf8
character-set-server = utf8
collation-server = utf8_general_ci


symbolic-links=0


##Password Policy
validate_password_policy=LOW
#validate_password_policy=MEDIUM


### MyISAM Spectific options
#default-storage-engine = myisam
key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1


### INNODB Spectific options
default-storage-engine = InnoDB
#skip-innodb
#innodb_additional_mem_pool_size = 16M
#innodb_buffer_pool_size = 1024MB
#innodb_data_file_path = ibdata1:10M:autoextend
innodb_write_io_threads = 8
innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 128M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
 
여기서 default-storagne-engin을 myisam 을 사용할것인지 innodb를 사용할것인지 선택 후 사용하면 된다
여기 my.cnf 파일은 현재 innodb를 기본엔진으로 사용
myisam으로 변경하려면 주석 해제 후 사용 / innodb 부분은 주석처리