MySQL issues

interest

install on win10

refer https://www.jianshu.com/p/9b79ddf382b4

config my.ini

refer https://www.jianshu.com/p/1c3a4a555b78

cannot start mysql service

see https://stackoverflow.com/questions/36319265/the-mysql-service-could-not-be-started
check if my.ini was not encoded with utf-8

Failed to connect with host localhost

see https://www.bilishare.com/question/mysql/2021/04/12/mysql-error.html
also see https://dev.mysql.com/doc/refman/8.0/en/resetting-permissions.html

Finally failed with [Server] 1396 Operation ALTER USER failed for 'root'@'localhost', try to reinstall mysql …

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
C:\Users\...\Downloads\mysql-8.0.27-winx64\bin>mysqld -remove mysql
Service successfully removed.

C:\Users\...\Downloads\mysql-8.0.27-winx64\bin>mysqld --initialize --user=mysql --console
2022-03-01T09:53:39.820016Z 0 [System] [MY-013169] [Server] C:\Users\eshibij\Downloads\mysql-8.0.27-winx64\bin\mysqld.exe (mysqld 8.0.27) initializing of server in progress as process 26856
2022-03-01T09:53:39.823462Z 0 [ERROR] [MY-010457] [Server] --initialize specified but the data directory has files in it. Aborting.
2022-03-01T09:53:39.823476Z 0 [ERROR] [MY-013236] [Server] The designated data directory C:\Users\eshibij\Downloads\mysql-8.0.27-winx64\data\ is unusable. You can remove all files that the server added to it.
2022-03-01T09:53:39.848518Z 0 [ERROR] [MY-010119] [Server] Aborting
2022-03-01T09:53:39.852182Z 0 [System] [MY-010910] [Server] C:\Users\eshibij\Downloads\mysql-8.0.27-winx64\bin\mysqld.exe: Shutdown complete (mysqld 8.0.27)  MySQL Community Server - GPL.
# here initialize user failed due to the last existing `/data` folder, remind to delete it before you redo the init

C:\Users\...\Downloads\mysql-8.0.27-winx64\bin>mysqld --initialize --user=mysql --console
2022-03-01T09:54:04.045122Z 0 [System] [MY-013169] [Server] C:\Users\eshibij\Downloads\mysql-8.0.27-winx64\bin\mysqld.exe (mysqld 8.0.27) initializing of server in progress as process 11404
2022-03-01T09:54:04.106350Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-03-01T09:54:06.283703Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-03-01T09:54:08.851696Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1 is enabled for channel mysql_main
2022-03-01T09:54:08.856307Z 0 [Warning] [MY-013746] [Server] A deprecated TLS version TLSv1.1 is enabled for channel mysql_main
2022-03-01T09:54:09.029924Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: D(ag!*brW6w5

C:\Users\...\Downloads\mysql-8.0.27-winx64\bin>mysqld -install
Service successfully installed.

C:\Users\...\Downloads\mysql-8.0.27-winx64\bin>net start mysql
The MySQL service is starting.
The MySQL service was started successfully.

C:\Users\...\Downloads\mysql-8.0.27-winx64\bin>mysql -u root -p
Enter password: ************
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.27

Copyright (c) 2000, 2021, Oracle and/or its affiliates.

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> use mysql
No connection. Trying to reconnect...
Enter password: ************
Connection id:    9
Current database: *** NONE ***

ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
# need to change the default password first

mysql> ALTER USER root@localhost IDENTIFIED BY 'root';
Query OK, 0 rows affected (0.01 sec)

mysql> use mysql
Database changed

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
4 rows in set (0.00 sec)

mysql> CREATE USER 'backup'@'localhost' IDENTIFIED BY '';
Query OK, 0 rows affected (0.02 sec)

mysql> select host,user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| localhost | backup           |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
5 rows in set (0.00 sec)

mysql> grant all privileges on *.* to 'backup'@'localhost' with grant option;
Query OK, 0 rows affected (0.01 sec)

mysql> grant all privileges on *.* to 'root'@'localhost' with grant option;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select host,user,password_require_current,Insert_priv from user;
+-----------+------------------+--------------------------+-------------+
| host      | user             | password_require_current | Insert_priv |
+-----------+------------------+--------------------------+-------------+
| localhost | backup           | NULL                     | Y           |
| localhost | mysql.infoschema | NULL                     | N           |
| localhost | mysql.session    | NULL                     | N           |
| localhost | mysql.sys        | NULL                     | N           |
| localhost | root             | NULL                     | Y           |
+-----------+------------------+--------------------------+-------------+
5 rows in set (0.00 sec)