環(huán)境準(zhǔn)備
這邊已經(jīng)準(zhǔn)備好一個(gè)空閑的虛擬機(jī)，信息如下：
ubuntu@ubuntuMysql:~$ ifconfig
ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST> mtu 1500
inet 192.168.202.133 netmask 255.255.255.0 broadcast 192.168.202.255
inet6 fe80::20c:29ff:fedf:47ab prefixlen 64 scopeid 0x20<link>
ether 00:0c:29:df:47:ab txqueuelen 1000 (Ethernet)
RX packets 65240 bytes 96272315 (96.2 MB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 15666 bytes 1021191 (1.0 MB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
lo: flags=73<UP,LOOPBACK,RUNNING> mtu 65536
inet 127.0.0.1 netmask 255.0.0.0
inet6 ::1 prefixlen 128 scopeid 0x10<host>
loop txqueuelen 1000 (Local Loopback)
RX packets 223 bytes 17672 (17.6 KB)
RX errors 0 dropped 0 overruns 0 frame 0
TX packets 223 bytes 17672 (17.6 KB)
TX errors 0 dropped 0 overruns 0 carrier 0 collisions 0
安裝MySQL
首先切換到root用戶(hù)權(quán)限下，然后執(zhí)行下面的命令安裝MySQL
sudo apt update
sudo apt install mysql-server
配置MySQL
1. 安裝配置
sudo mysql_secure_installation
配置說(shuō)明
#1是否安裝密碼校驗(yàn)插件
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:
#2設(shè)置密碼
Please set the password for root here.
New password:
Re-enter new password:
#3刪除匿名用戶(hù)（生產(chǎn)環(huán)境有必要?jiǎng)h除）
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) :
#4是否允許root用戶(hù)遠(yuǎn)程登錄
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) :
#5是否刪除“測(cè)試”庫(kù)
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) :
#6是否立即生效
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) :
2. 檢查MySQL服務(wù)的運(yùn)行狀態(tài)
root@ubuntuMysql:~# systemctl status mysql.service
顯示如下為正常狀態(tài)
● mysql.service - MySQL Community Server
Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
Active: active (running) since Sun 2019-09-22 15:26:47 UTC; 3min 27s ago
Main PID: 23814 (mysqld)
Tasks: 29 (limit: 4633)
CGroup: /system.slice/mysql.service
└─23814 /usr/sbin/mysqld --daemonize --pid-file=/run/mysqld/mysqld.pid
Sep 22 15:26:47 ubuntuMysql systemd[1]: Starting MySQL Community Server...
Sep 22 15:26:47 ubuntuMysql systemd[1]: Started MySQL Community Server.
3. 遠(yuǎn)程登錄MySQL
sudo mysql -uroot -p
執(zhí)行如下命令配置mysql的root用戶(hù)的host
use mysql
update user set host='%' where user='root';
flush privileges;
exit;
2003 - Can't connect to MySQL server on '192.168.202.133' (10061 "Unknow error")
root@ubuntuMysql:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
# 注釋掉bind-address = 127.0.0.1
root@ubuntuMysql:~# sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
1698 - Access denied for user 'root'@'192.168.202.1'
跳過(guò)密碼驗(yàn)證，進(jìn)入MySQL，然后重置root用戶(hù)密碼即可。
root@ubuntuMysql:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
# 在[mysqld]段落內(nèi)增加skip-grant-tables跳過(guò)密碼驗(yàn)證
skip-grant-tables
# 重啟MySQL服務(wù)
root@ubuntuMysql:~# sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
# 重啟完成之后，直接使用mysql命令進(jìn)入mysql
root@ubuntuMysql:~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2019, 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>
# 然后重置root用戶(hù)密碼
mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set authentication_string=password("12345678"),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> flush privileges;
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
上面的配置完成后，還需要把增加的跳過(guò)密碼驗(yàn)證的配置關(guān)閉，則需要進(jìn)入配置文件中，然后在skip-grant-tables前增加#，然后重啟MySQL服務(wù)
重啟完成后，可以再使用mysql -uroot -p輸入密碼登錄mysql
root@ubuntuMysql:~# sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
root@ubuntuMysql:~# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.27-0ubuntu0.18.04.1 (Ubuntu)
Copyright (c) 2000, 2019, 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> exit
Bye
Navicat連接MySQL
進(jìn)行完成上述操作后，即可使用Navicat訪問(wèn)MySQL了。
