mysql 5.7 패스워드 초기화및 my.cnf
페이지 정보

본문
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 부분은 주석처리
#/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 부분은 주석처리
- 이전글mysql 정보확인 명령어 20.11.24
- 다음글mysql binlog 관리 20.11.24
댓글목록
등록된 댓글이 없습니다.