2

I have a MySQL 5.5 server on Windows 2012r2. The database was not performing well, so I generated an ini at Percona and replaced that wit my old ini.

The server is performing good now, but now I cannot login as root, although I am sure I have the right password. Then I wanted to change the root password. But that did not work either.

Does changing the ini has influence on user passwords?

My old 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

My new ini:

[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

It looks like all localhost-account's don't work anymore. There is one %-account and that is still working

I hope someone has an explanation why I cannot login as root anymore. Or even better make the root login work again.

  • hi MDCCL, I guess you are trying to help me, and i am very gratefull about that, but I don't understand why you edited my ini, it looks the same to me? Or should I leave out the client part in my.ini? – Jan Jun 14 '17 at 17:29
  • Have a closer look at top of MDCCL's edit, where he discibes his changes (Added the generic tag of the DBMS of relevance; modified capitalization and formatting). Plz provide logs (mysql startup) and error msg next time. Also you don't tell us how you tried to connect (console, workbench?).. – BitAccesser Jun 15 '17 at 0:13
1

Remove skip-name-resolve in your my.ini, which prevents localhost to be resolved, restart mysql, login and change your root account to 'root'@'127.0.0.1' or 'root'@'::1' then turn on skip-name-resolve again and restart. See MySQL --skip-name-resolve Also have a look here How can I run with root@localhost privileges in MySQL?

  • Thanks MDDSL and @BitAccesser. Your last answer was the solution.As soon as i took skip-name-resolve out of the ini all local-accounts worked again. I had no idea skip-name-resolve has influence on localhost too. – Jan Jun 15 '17 at 5:51

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.