释放双眼，带上耳机，听听看~！
【更改主机名】：hostnamectl set-hostname onefine
【安装ifconfig命令】：yum install net-tools.x86_64
安装MySQL
CentOS 8默认安装mariadb数据库：
- 搜索是否有mariadb：yum search mariadb
- 移除：yum remove mariadb-libs.x86_64
下载MySQL源：
- wget https://dev.mysql.com/get/mysql57-community-release-el7-11.noarch.rpm
- 【安装wget命令】：yum -y install wget
安装源：
- yum -y localinstall mysql57-community-release-el7-11.noarch.rpm
- 【安装命令自动补全】：yum -y install bash-completion
安装MySQL：
- 【查询MySQL】：yum search mysql
- yum install mysql-community-server.x86_64
- 【查看服务是否启动】：ps -ef | grep mysql
- 【重启服务】：service mysqld restart
- 【停止服务】：service mysqld stop
- 【启动服务】：service mysqld start
修改默认密码
- cat /var/log/mysqld.log | grep password
栗子：
2
3
4
5
6
7
8
9
10
11
22020-01-01T08:52:20.771065Z 1 [Note] A temporary password is generated for root@localhost: ;rMMa;rG=9eA
32020-01-01T08:52:30.793354Z 0 [Note] Shutting down plugin 'validate_password'
42020-01-01T08:52:32.409836Z 0 [Note] Shutting down plugin 'sha256_password'
52020-01-01T08:52:32.409838Z 0 [Note] Shutting down plugin 'mysql_native_password'
62020-01-01T08:53:00.227874Z 0 [Note] Shutting down plugin 'validate_password'
72020-01-01T08:53:01.668570Z 0 [Note] Shutting down plugin 'sha256_password'
82020-01-01T08:53:01.668573Z 0 [Note] Shutting down plugin 'mysql_native_password'
9[root@onefine ~]#
10
11
记住上面第一行的密码：rMMa;rG=9eA
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
2Enter password: # 输入查询到的密码：;rMMa;rG=9eA
3Welcome to the MySQL monitor. Commands end with ; or \g.
4Your MySQL connection id is 4
5Server version: 5.7.28
6
7Copyright (c) 2000, 2019, Oracle and/or its affiliates. All rights reserved.
8
9Oracle is a registered trademark of Oracle Corporation and/or its
10affiliates. Other names may be trademarks of their respective
11owners.
12
13Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
14
15mysql>
16
17
设置新密码：
2
3
4
5
6
7
8
9
10
11
12
13
14
2ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
3mysql> set global validate_password_policy=0;
4Query OK, 0 rows affected (0.00 sec)
5
6mysql> set global validate_password_length=1;
7Query OK, 0 rows affected (0.00 sec)
8
9mysql> SET PASSWORD = PASSWORD("123456");
10Query OK, 0 rows affected, 1 warning (0.00 sec)
11
12mysql>
13
14
注意：
-p是密码；-u是用户名；-h是主机名（默认为：-h127.0.0.1）
远程连接
直接使用会报错：
开启远程连接：
-
update user set host = '%' where Host = "localhost" and User = "root"; # 允许任何主机进行连接
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
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
2+--------------------+
3| Database |
4+--------------------+
5| information_schema |
6| mysql |
7| performance_schema |
8| sys |
9+--------------------+
104 rows in set (0.00 sec)
11
12mysql> use mysql;
13Reading table information for completion of table and column names
14You can turn off this feature to get a quicker startup with -A
15
16Database changed
17mysql> show tables;
18+---------------------------+
19| Tables_in_mysql |
20+---------------------------+
21| columns_priv |
22| db |
23| engine_cost |
24| event |
25| func |
26| general_log |
27| gtid_executed |
28| help_category |
29| help_keyword |
30| help_relation |
31| help_topic |
32| innodb_index_stats |
33| innodb_table_stats |
34| ndb_binlog_index |
35| plugin |
36| proc |
37| procs_priv |
38| proxies_priv |
39| server_cost |
40| servers |
41| slave_master_info |
42| slave_relay_log_info |
43| slave_worker_info |
44| slow_log |
45| tables_priv |
46| time_zone |
47| time_zone_leap_second |
48| time_zone_name |
49| time_zone_transition |
50| time_zone_transition_type |
51| user |
52+---------------------------+
5331 rows in set (0.00 sec)
54
55mysql> select * from user \G;
56*************************** 1. row ***************************
57 Host: localhost
58 User: root
59 Select_priv: Y
60 Insert_priv: Y
61 Update_priv: Y
62 Delete_priv: Y
63 Create_priv: Y
64 Drop_priv: Y
65 Reload_priv: Y
66 Shutdown_priv: Y
67 Process_priv: Y
68 File_priv: Y
69 Grant_priv: Y
70 References_priv: Y
71 Index_priv: Y
72 Alter_priv: Y
73 Show_db_priv: Y
74 Super_priv: Y
75 Create_tmp_table_priv: Y
76 Lock_tables_priv: Y
77 Execute_priv: Y
78 Repl_slave_priv: Y
79 Repl_client_priv: Y
80 Create_view_priv: Y
81 Show_view_priv: Y
82 Create_routine_priv: Y
83 Alter_routine_priv: Y
84 Create_user_priv: Y
85 Event_priv: Y
86 Trigger_priv: Y
87Create_tablespace_priv: Y
88 ssl_type:
89 ssl_cipher:
90 x509_issuer:
91 x509_subject:
92 max_questions: 0
93 max_updates: 0
94 max_connections: 0
95 max_user_connections: 0
96 plugin: mysql_native_password
97 authentication_string: *39F64A7C8ABD00D30E94AE234BFBE58532C25420
98 password_expired: N
99 password_last_changed: 2020-01-01 17:10:31
100 password_lifetime: NULL
101 account_locked: N
102*************************** 2. row ***************************
103 Host: localhost
104 User: mysql.session
105 Select_priv: N
106 Insert_priv: N
107 Update_priv: N
108 Delete_priv: N
109 Create_priv: N
110 Drop_priv: N
111 Reload_priv: N
112 Shutdown_priv: N
113 Process_priv: N
114 File_priv: N
115 Grant_priv: N
116 References_priv: N
117 Index_priv: N
118 Alter_priv: N
119 Show_db_priv: N
120 Super_priv: Y
121 Create_tmp_table_priv: N
122 Lock_tables_priv: N
123 Execute_priv: N
124 Repl_slave_priv: N
125 Repl_client_priv: N
126 Create_view_priv: N
127 Show_view_priv: N
128 Create_routine_priv: N
129 Alter_routine_priv: N
130 Create_user_priv: N
131 Event_priv: N
132 Trigger_priv: N
133Create_tablespace_priv: N
134 ssl_type:
135 ssl_cipher:
136 x509_issuer:
137 x509_subject:
138 max_questions: 0
139 max_updates: 0
140 max_connections: 0
141 max_user_connections: 0
142 plugin: mysql_native_password
143 authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
144 password_expired: N
145 password_last_changed: 2020-01-01 16:52:21
146 password_lifetime: NULL
147 account_locked: Y
148*************************** 3. row ***************************
149 Host: localhost
150 User: mysql.sys
151 Select_priv: N
152 Insert_priv: N
153 Update_priv: N
154 Delete_priv: N
155 Create_priv: N
156 Drop_priv: N
157 Reload_priv: N
158 Shutdown_priv: N
159 Process_priv: N
160 File_priv: N
161 Grant_priv: N
162 References_priv: N
163 Index_priv: N
164 Alter_priv: N
165 Show_db_priv: N
166 Super_priv: N
167 Create_tmp_table_priv: N
168 Lock_tables_priv: N
169 Execute_priv: N
170 Repl_slave_priv: N
171 Repl_client_priv: N
172 Create_view_priv: N
173 Show_view_priv: N
174 Create_routine_priv: N
175 Alter_routine_priv: N
176 Create_user_priv: N
177 Event_priv: N
178 Trigger_priv: N
179Create_tablespace_priv: N
180 ssl_type:
181 ssl_cipher:
182 x509_issuer:
183 x509_subject:
184 max_questions: 0
185 max_updates: 0
186 max_connections: 0
187 max_user_connections: 0
188 plugin: mysql_native_password
189 authentication_string: *THISISNOTAVALIDPASSWORDTHATCANBEUSEDHERE
190 password_expired: N
191 password_last_changed: 2020-01-01 16:52:21
192 password_lifetime: NULL
193 account_locked: Y
1943 rows in set (0.00 sec)
195
196ERROR:
197No query specified
198
199mysql> select Host, User from user \G;
200*************************** 1. row ***************************
201Host: localhost
202User: mysql.session
203*************************** 2. row ***************************
204Host: localhost
205User: mysql.sys
206*************************** 3. row ***************************
207Host: localhost
208User: root
2093 rows in set (0.00 sec)
210
211ERROR:
212No query specified
213
214mysql> update user set host = '%' where Host = "localhost" and User = "root";
215Query OK, 1 row affected (0.00 sec)
216Rows matched: 1 Changed: 1 Warnings: 0
217
218mysql> select Host, User from user \G;
219*************************** 1. row ***************************
220Host: %
221User: root
222*************************** 2. row ***************************
223Host: localhost
224User: mysql.session
225*************************** 3. row ***************************
226Host: localhost
227User: mysql.sys
2283 rows in set (0.00 sec)
229
230ERROR:
231No query specified
232
233mysql> flush privileges;
234Query OK, 0 rows affected (0.01 sec)
235
236mysql>
237
238
再次尝试：
关闭防火墙
2
3
4
5
6
7
8
9
10
11
2Bye
3[root@onefine ~]#
4[root@onefine ~]# ps -ef | grep firewalld
5root 6360 1 0 16:40 ? 00:00:01 /usr/bin/python -Es /usr/sbin/firewalld --nofork --nopid
6root 7786 7279 0 17:31 pts/0 00:00:00 grep --color=auto firewalld
7[root@onefine ~]# service firewalld stop
8Redirecting to /bin/systemctl stop firewalld.service
9[root@onefine ~]#
10
11
再次尝试：
开启Genelog
-
Genelog记录了所有操作数据库的SQL语句，有时候可以查看ORM自动生成的SQL语句，进行排错。
2
3
4
5
6
7
8
9
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> set global general_log = on; # 设置全局模式-打开Genelog
5Query OK, 0 rows affected (0.00 sec)
6
7mysql>
8
9
注：关闭Genelog：set global general_log = off;
栗子：
2
3
4
5
6
7
2/usr/sbin/mysqld, Version: 5.7.28 (MySQL Community Server (GPL)). started with:
3Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
4Time Id Command Argument
5[root@onefine ~]#
6
7
2
3
4
5
6
7
8
9
10
11
12
13
14
2+--------------------+
3| Database |
4+--------------------+
5| information_schema |
6| mysql |
7| performance_schema |
8| sys |
9+--------------------+
104 rows in set (0.00 sec)
11
12mysql>
13
14
2
3
4
5
6
7
8
2/usr/sbin/mysqld, Version: 5.7.28 (MySQL Community Server (GPL)). started with:
3Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
4Time Id Command Argument
52020-01-01T09:46:22.479044Z 12 Query show databases
6[root@onefine ~]#
7
8
MySQL新建用户和权限操作
2
3
4
5
6
7
8
9
10
11
12
2Query OK, 0 rows affected (0.00 sec)
3
4mysql> set global validate_password_length=1;
5Query OK, 0 rows affected (0.00 sec)
6
7mysql> create user 'ionefine'@'%' identified by '123456';
8Query OK, 0 rows affected (0.00 sec)
9
10mysql>
11
12
自学：mysql赋予权限和权限收回。
忘记root密码怎么办
这里以ionfine账号为例（可以换成root）:
2
3
4
5
6
7
8
9
10
11
2Enter password:
3ERROR 1045 (28000): Access denied for user 'ionefine'@'localhost' (using password: NO)
4[root@onefine ~]# sudo vim /etc/my.cnf
5sudo: vim: command not found
6[root@onefine ~]# yum -y install vim*
7... # 省略
8[root@onefine ~]#
9[root@onefine ~]# sudo vim /etc/my.cnf
10
11
最后一行新添：skip-grant-tables # 跳出授权表的验证
保存后重启MySQL服务：[root@onefine ~]# service mysql restart
后面之后回车可以直接进入，修改密码：update user set authentication_string = password("13579") where user = ’ionefine';
将/etc/my.cnf文件新加的语句注释或删除：# skip-grant-tables
保存后重启MySQL服务：[root@onefine ~]# service mysql restart
修改成功！
