注意： 本文操作環(huán)境為win10系統(tǒng)wsl下的Ubuntu18.04，對(duì)于原生的Ubuntu18.04同樣適用。MySQL默認(rèn)版本為5.7，其他版本不適用。
1.更新源：
sudo apt update
2.安裝mysql：
sudo apt install mysql-server
wsl下使用上述命令安裝就直接安裝上去了，沒有設(shè)置密碼的地方，這時(shí)候無論怎么登陸，都無法登錄上去。
chenyc@DESKTOP-Q5J25HR:~$ mysql
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
chenyc@DESKTOP-Q5J25HR:~$ mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
chenyc@DESKTOP-Q5J25HR:~$
設(shè)置root用戶密碼：
chenyc@DESKTOP-Q5J25HR:~$ sudo mysql_secure_installation
Securing the MySQL server deployment.
Enter password for user root:
Error: Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
發(fā)現(xiàn)無法設(shè)置，看報(bào)錯(cuò)信息，說的是不能連接到mysqld.sock套接字，猜想是mysql服務(wù)沒有開啟。
開啟mysql服務(wù)：
chenyc@DESKTOP-Q5J25HR:~$ sudo service mysql start
* Starting MySQL database server mysqld
No directory, logging in with HOME=/ [ OK ]
重新設(shè)置密碼：
使用sudo mysql_secure_installation命令，有幾個(gè)地方需要用戶確認(rèn)。
Press y|Y for Yes, any other key for No:y
選y，前面提示大致的意思是：默認(rèn)使用空的密碼連接，該種連接方式可以作為測試使用，但是不安全，問是否要重新設(shè)置密碼。
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
這里是讓選擇密碼強(qiáng)度，從前面提示可以知道，有LOW，MEDIUM，STRONG三種強(qiáng)度可選，我們選擇0即可。
New password:
Re-enter new password:
這個(gè)沒什么好說的，讓設(shè)置密碼，并確認(rèn)新密碼。
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
問是否移除匿名用戶，匿名用戶留著也沒什么用，可以移除掉，選擇y。
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
是否禁止root用戶遠(yuǎn)程登錄，在沒有設(shè)置其他用戶之前，只能通過root用戶登錄，所以不能禁止，選擇n。
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n
是否移除測試數(shù)據(jù)庫，選擇n。
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : n
是否重新載入特權(quán)表，最好不動(dòng)它，選否。
下面是設(shè)置密碼的全過程。
chenyc@DESKTOP-Q5J25HR:~$ sudo mysql_secure_installation
Securing the MySQL server deployment.
Connecting to MySQL using a blank password.
VALIDATE PASSWORD PLUGIN can be used to test passwords
and improve security. It checks the strength of password
and allows the users to set only those passwords which are
secure enough. Would you like to setup VALIDATE PASSWORD plugin?
Press y|Y for Yes, any other key for No: y
There are three levels of password validation policy:
LOW Length >= 8
MEDIUM Length >= 8, numeric, mixed case, and special characters
STRONG Length >= 8, numeric, mixed case, special characters and dictionary file
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
Please set the password for root here.
New password:
Re-enter new password:
Estimated strength of the password: 25
Do you wish to continue with the password provided?(Press y|Y for Yes, any other key for No) : y
By default, a MySQL installation has an anonymous user,
allowing anyone to log into MySQL without having to have
a user account created for them. This is intended only for
testing, and to make the installation go a bit smoother.
You should remove them before moving into a production
environment.
Remove anonymous users? (Press y|Y for Yes, any other key for No) : y
Success.
Normally, root should only be allowed to connect from
'localhost'. This ensures that someone cannot guess at
the root password from the network.
Disallow root login remotely? (Press y|Y for Yes, any other key for No) : n
... skipping.
By default, MySQL comes with a database named 'test' that
anyone can access. This is also intended only for testing,
and should be removed before moving into a production
environment.
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n
... skipping.
Reloading the privilege tables will ensure that all changes
made so far will take effect immediately.
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : n
... skipping.
All done!
以上設(shè)置完成后，使用剛剛設(shè)置的root用戶密碼，再次連接數(shù)據(jù)庫，成功連接上。
chenyc@DESKTOP-Q5J25HR:~$ sudo mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 6
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2020, 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>
查看編碼：
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | latin1 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | latin1 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
可以看到，剛剛安裝好的mysql默認(rèn)有很多是latin1的編碼格式，對(duì)漢字的支持不好，因此需要改成utf8編碼格式。
修改mysql數(shù)據(jù)庫編碼的步驟：
1.停止mysql服務(wù)：
chenyc@DESKTOP-Q5J25HR:~$ /etc/init.d/mysql stop
* Stopping MySQL database server mysqld
cat: /var/run/mysqld/mysqld.pid: Permission denied
[fail]
發(fā)現(xiàn)沒有權(quán)限，加上sudo再去執(zhí)行：
chenyc@DESKTOP-Q5J25HR:~$ sudo /etc/init.d/mysql stop
* Stopping MySQL database server mysqld [ OK ]
服務(wù)停止成功。
2.修改配置文件:
進(jìn)入配置文件目錄：
cd /etc/mysql/mysql.conf.d
修改之前先備份：
sudo cp mysqld.cnf mysqld.cnf.2
接下來修改配置文件，執(zhí)行如下命令：
sudo vim mysqld.cnf
修改兩個(gè)地方：
# 修改處1：添加以下2行
[client]
default-character-set=utf8
[mysqld]
# 修改處2：添加以下3行
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
修改完成后，重啟數(shù)據(jù)庫：
chenyc@DESKTOP-Q5J25HR:~$ sudo service mysql start
* Starting MySQL database server mysqld [ OK ]
再次登錄，發(fā)現(xiàn)登不上了：
chenyc@DESKTOP-Q5J25HR:~$ mysql -u root -p
Enter password:
ERROR 1698 (28000): Access denied for user 'root'@'localhost'
使用sudo cat /etc/mysql/debian.cnf查看debian-sys-maint密碼，發(fā)現(xiàn)密碼是下面這個(gè)玩意：
chenyc@DESKTOP-Q5J25HR:~$ sudo cat /etc/mysql/debian.cnf
# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host = localhost
user = debian-sys-maint
password = LMCuPijw9kX5cRsS
socket = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host = localhost
user = debian-sys-maint
password = LMCuPijw9kX5cRsS
socket = /var/run/mysqld/mysqld.sock
因此，可以使用debian-sys-maint用戶先登錄上去，修改密碼：
chenyc@DESKTOP-Q5J25HR:~$ mysql -udebian-sys-maint -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 18
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2020, 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>
使用上面的password，成功登錄。
修改密碼，執(zhí)行如下語句：
UPDATE mysql.user SET authentication_string=PASSWORD('你的密碼'), PLUGIN='mysql_native_password'
WHERE USER='root';
執(zhí)行時(shí)發(fā)現(xiàn)報(bào)錯(cuò)：
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('cyc2010'), PLUGIN='mysql_native_password' WHERE USER='root';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
說不符合安全規(guī)范，估計(jì)是之前設(shè)置安全級(jí)別的那地方的問題，需要重新設(shè)置一下：
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
validate_password_length(密碼長度)參數(shù)默認(rèn)為8，修改為1:
mysql> set global validate_password_length=1;
Query OK, 0 rows affected (0.00 sec)
再次修改密碼成功。
mysql> UPDATE mysql.user SET authentication_string=PASSWORD('cyc2010'), PLUGIN='mysql_native_password' WHERE USER='root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
授權(quán)遠(yuǎn)程登錄：
mysql> grant all privileges on *.* to 'root'@'%' identified by 'cyc2010' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
設(shè)置完成后，再次重啟服務(wù)：
chenyc@DESKTOP-Q5J25HR:~$ sudo /etc/init.d/mysql restart
* Stopping MySQL database server mysqld [ OK ] * Starting MySQL database server mysqld No directory, logging in with HOME=/ [ OK ]
使用root用戶登錄：
chenyc@DESKTOP-Q5J25HR:~$ mysql -u root -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2020, 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>
成功登錄，查看編碼格式：
mysql> show variables like 'character%';
+--------------------------+----------------------------+
| Variable_name | Value |
+--------------------------+----------------------------+
| character_set_client | utf8 |
| character_set_connection | utf8 |
| character_set_database | utf8 |
| character_set_filesystem | binary |
| character_set_results | utf8 |
| character_set_server | utf8 |
| character_set_system | utf8 |
| character_sets_dir | /usr/share/mysql/charsets/ |
+--------------------------+----------------------------+
8 rows in set (0.00 sec)
發(fā)現(xiàn)都變成了utf8，說明設(shè)置成功了。
重啟wsl-Linux子系統(tǒng)：
//WSL-Ubuntu18.04 LTS 重啟方法
//以管理員權(quán)限運(yùn)行cmd
>net stop LxssManager //停止
>net start LxssManager //啟動(dòng)
重啟系統(tǒng)后，再次登錄，發(fā)現(xiàn)又登不上了:
chenyc@DESKTOP-Q5J25HR:/mnt/c/Users/cheny$ mysql -u root -p
Enter password:
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/var/run/mysqld/mysqld.sock' (2)
看報(bào)錯(cuò)信息，應(yīng)該是mysql服務(wù)沒有啟動(dòng)，啟動(dòng)服務(wù)：
chenyc@DESKTOP-Q5J25HR:/mnt/c/Users/cheny$ sudo service mysql start
[sudo] password for chenyc:
* Starting MySQL database server mysqld No directory, logging in with HOME=/
[ OK ]
再次登錄，發(fā)現(xiàn)登陸成功。
chenyc@DESKTOP-Q5J25HR:/mnt/c/Users/cheny$ mysql -u root -p Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 4
Server version: 5.7.29-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2020, 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>
聯(lián)系客服
