环境准备
这边已经准备好一个空闲的虚拟机，信息如下：
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用户权限下，然后执行下面的命令安装MySQL
sudo apt update
sudo apt install mysql-server
配置MySQL
1. 安装配置
sudo mysql_secure_installation
配置说明
#1是否安装密码校验插件
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设置密码
Please set the password for root here.
New password:
Re-enter new password:
#3删除匿名用户（生产环境有必要删除）
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用户远程登录
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是否删除“测试”库
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服务的运行状态
root@ubuntuMysql:~# systemctl status mysql.service
显示如下为正常状态
● 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. 远程登录MySQL
sudo mysql -uroot -p
执行如下命令配置mysql的root用户的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'
跳过密码验证，进入MySQL，然后重置root用户密码即可。
root@ubuntuMysql:~# vi /etc/mysql/mysql.conf.d/mysqld.cnf
# 在[mysqld]段落内增加skip-grant-tables跳过密码验证
skip-grant-tables
# 重启MySQL服务
root@ubuntuMysql:~# sudo /etc/init.d/mysql restart
[ ok ] Restarting mysql (via systemctl): mysql.service.
# 重启完成之后，直接使用mysql命令进入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用户密码
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
上面的配置完成后，还需要把增加的跳过密码验证的配置关闭，则需要进入配置文件中，然后在skip-grant-tables前增加#，然后重启MySQL服务
重启完成后，可以再使用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
进行完成上述操作后，即可使用Navicat访问MySQL了。
