Mysql

無法以root身份登錄,也無法更改root密碼

  • June 14, 2017

我在 Windows 2012r2 上有一個 MySQL 5.5 伺服器。數據庫表現不佳,所以我在Percona生成了一個 ini,並用我的舊 ini 替換了它。

伺服器現在執行良好,但現在我無法以 root 身份登錄,儘管我確信我有正確的密碼。然後我想更改root密碼。但這也不起作用。

更改ini對使用者密碼有影響嗎?

我的老這個:

[client]
port=3306

[mysql]
default-character-set=UTF8

[mysqld]
max-allowed_packet = 64M
wait-timeout = 6000
slow_query_log_file = d:/mysql/slow-queries.log
long_query_time=10
port=3306
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
datadir="D:/mysql/"
character-set-server=UTF8
default-storage-engine=INNODB
sql-mode="STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION"
max_connections=150
query_cache_size=0
table_cache=256
tmp_table_size=16M
max_heap_table_size=16M
thread_cache_size=8
myisam_max_sort_file_size=100G
myisam_sort_buffer_size=69M
key_buffer_size=55M
read_buffer_size=64K
read_rnd_buffer_size=256K
sort_buffer_size=256K
innodb_additional_mem_pool_size=3M
innodb_flush_log_at_trx_commit=1
innodb_log_buffer_size=2M
innodb_buffer_pool_size=3076M
innodb_log_file_size=768M
innodb_thread_concurrency=8

我的新內容:

[mysql]
# CLIENT #
port = 3306
socket = D:\mysql\mysql.sock

[mysqld]
# GENERAL #
user = mysql
default-storage-engine = InnoDB
socket = D:\mysql\mysql.sock
pid-file = D:\mysql\mysql.pid

# MyISAM #
key-buffer-size = 32M
myisam-recover = FORCE,BACKUP

# SAFETY #
max-allowed-packet = 16M
max-connect-errors = 1000000
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_AUTO_VALUE_ON_ZERO,NO_ENGINE_SUBSTITUTION,NO_ZERO_DATE,NO_ZERO_IN_DATE,ONLY_FULL_GROUP_BY
sysdate-is-now = 1
innodb = FORCE

# DATA STORAGE #
datadir = D:\mysql\

# BINARY LOGGING #
log-bin = D:\mysql\mysql-bin
expire-logs-days = 14
sync-binlog = 1

# CACHES AND LIMITS #
tmp-table-size = 32M
max-heap-table-size = 32M
query-cache-type = 0
query-cache-size = 0
max-connections = 500
thread-cache-size = 50
open-files-limit = 65535
table-definition-cache = 4096
table-open-cache = 10240

# INNODB #
innodb-log-files-in-group = 2
innodb-log-file-size = 768M
innodb-flush-log-at-trx-commit = 1
innodb-file-per-table = 1
innodb-buffer-pool-size = 3076M

# LOGGING #
log-error = D:\mysql\mysql-error.log
log-queries-not-using-indexes = 1
slow-query-log = 1
slow-query-log-file = D:\mysql\mysql-slow.log

看起來所有 localhost 帳戶都不再起作用了。有一個 %-account 並且仍在工作

我希望有人能解釋為什麼我不能再以 root 身份登錄。或者甚至更好地使 root 登錄再次工作。

在您的 my.ini 中刪除skip-name-resolve,這會阻止解析 localhost,重新啟動 mysql,登錄並將您的 root 帳戶更改為'root'@'127.0.0.1''root'@'::1'然後再次打開skip-name-resolve並重新啟動。見MySQL –skip-name-resolve 也看看這裡如何在 MySQL 中以 root@localhost 權限執行?

引用自:https://dba.stackexchange.com/questions/176290