注意： 本文操作環境為win10系統wsl下的Ubuntu18.04，對于原生的Ubuntu18.04同樣適用，MySQL默認版本為5.7，其他版本不適用，
安裝步驟
1.更新源：
sudo apt update
2.安裝mysql：
sudo apt install mysql-server
wsl下使用上述命令安裝就直接安裝上去了，沒有設定密碼的地方，這時候無論怎么登陸，都無法登錄上去，
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:~$
設定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)
發現無法設定，看報錯資訊，說的是不能連接到mysqld.sock套接字，猜想是mysql服務沒有開啟，
開啟mysql服務：
chenyc@DESKTOP-Q5J25HR:~$ sudo service mysql start
* Starting MySQL database server mysqld
No directory, logging in with HOME=/ [ OK ]
重新設定密碼：
使用sudo mysql_secure_installation命令，有幾個地方需要用戶確認，
Press y|Y for Yes, any other key for No:y
選y，前面提示大致的意思是：默認使用空的密碼連接，該種連接方式可以作為測驗使用，但是不安全，問是否要重新設定密碼，
Please enter 0 = LOW, 1 = MEDIUM and 2 = STRONG: 0
這里是讓選擇密碼強度，從前面提示可以知道，有LOW，MEDIUM，STRONG三種強度可選，我們選擇0即可，
New password:
Re-enter new password:
這個沒什么好說的，讓設定密碼，并確認新密碼，
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用戶遠程登錄，在沒有設定其他用戶之前，只能通過root用戶登錄，所以不能禁止，選擇n，
Remove test database and access to it? (Press y|Y for Yes, any other key for No) : n
是否移除測驗資料庫，選擇n，
Reload privilege tables now? (Press y|Y for Yes, any other key for No) : n
是否重新載入特權表，最好不動它，選否，
下面是設定密碼的全程序，
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!
以上設定完成后，使用剛剛設定的root用戶密碼，再次連接資料庫，成功連接上，
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默認有很多是latin1的編碼格式，對漢字的支持不好，因此需要改成utf8編碼格式，
修改mysql資料庫編碼的步驟：
1.停止mysql服務：
chenyc@DESKTOP-Q5J25HR:~$ /etc/init.d/mysql stop
* Stopping MySQL database server mysqld
cat: /var/run/mysqld/mysqld.pid: Permission denied
[fail]
發現沒有權限，加上sudo再去執行：
chenyc@DESKTOP-Q5J25HR:~$ sudo /etc/init.d/mysql stop
* Stopping MySQL database server mysqld [ OK ]
服務停止成功，
2.修改組態檔:
進入組態檔目錄：
cd /etc/mysql/mysql.conf.d
修改之前先備份：
sudo cp mysqld.cnf mysqld.cnf.2
接下來修改組態檔，執行如下命令：
sudo vim mysqld.cnf
修改兩個地方：
# 修改處1：添加以下2行
[client]
default-character-set=utf8
[mysqld]
# 修改處2：添加以下3行
default-storage-engine=INNODB
character-set-server=utf8
collation-server=utf8_general_ci
修改完成后，重啟資料庫：
chenyc@DESKTOP-Q5J25HR:~$ sudo service mysql start
* Starting MySQL database server mysqld [ OK ]
再次登錄，發現登不上了：
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密碼，發現密碼是下面這個玩意：
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，成功登錄，
修改密碼，執行如下陳述句：
UPDATE mysql.user SET authentication_string=PASSWORD('你的密碼'), PLUGIN='mysql_native_password'
WHERE USER='root';
執行時發現報錯：
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
說不符合安全規范，估計是之前設定安全級別的那地方的問題，需要重新設定一下：
mysql> set global validate_password_policy=0;
Query OK, 0 rows affected (0.00 sec)
validate_password_length(密碼長度)引數默認為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
授權遠程登錄：
mysql> grant all privileges on *.* to 'root'@'%' identified by 'cyc2010' with grant option;
Query OK, 0 rows affected, 1 warning (0.01 sec)
設定完成后，再次重啟服務：
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)
發現都變成了utf8，說明設定成功了，
系統重啟后失效問題
重啟wsl-Linux子系統：
//WSL-Ubuntu18.04 LTS 重啟方法
//以管理員權限運行cmd
>net stop LxssManager //停止
>net start LxssManager //啟動
重啟系統后，再次登錄，發現又登不上了:
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)
看報錯資訊，應該是mysql服務沒有啟動，啟動服務：
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 ]
再次登錄，發現登陸成功，
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>
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/62714.html
標籤：MySQL
上一篇：索引下（5）
下一篇：行鎖（7）