MySQL安装

MySQL 8.0安装

一、在Ubuntu上安装MySQL 8.x

先更新一下apt

sudo apt update

通过apt安装：

sudo apt install mysql-server

验证 MySQL 服务器是否正在运行：

sudo systemctl status mysql

若显示以下内容，则应该是已经启动并正在运行了：

mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset: enabled)
     Active: active (running) since Tue 2020-04-28 20:59:52 UTC; 10min ago
   Main PID: 8617 (mysqld)
     Status: "Server is operational"
     ...

或者这个命令：（我总感觉上面那个命令是 centos 的）

service mysql status

结果：

root@9244a36ad986:~# service mysql status
 * MySQL is stopped.

若开启的话，去第二部分，没有的话，走一下下面流程：

1. su root且输入密码切换成超级用户
2. 输入service mysql start开启mysql服务
   • 如果出现警告的话（可以先不管），多半是 /nonexistent 这文件或目录没找着，去https://www.yuque.com/duegin/note/fqp6c6#iUVIs看看
3. netstat -na 查看3306端口是否开启，没有则开启一下

二、保护加固MySQL

MySQL 安装文件附带了一个名为mysql_secure_installation的脚本，它允许你很容易地提高数据库服务器的安全性。

不带参数运行这个脚本：

sudo mysql_secure_installation

你将会被要求配置VALIDATE PASSWORD PLUGIN，它被用来测试 MySQL 用户密码的强度，并且提高安全性：

Securing the MySQL server deployment.

Connecting to MySQL using a blank password.

VALIDATE PASSWORD COMPONENT 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 component?

Press y|Y for Yes, any other key for No: y

有三个级别的密码验证策略，低级，中级，高级。如果你想设置验证密码插件，按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: 2

下一步被提示时，你将被要求为 MySQL root 用户设置一个密码：

Please set the password for root here.


New password: 

Re-enter new password:

如果你设置了验证密码插件，这个脚本将会显示你的新密码强度。输入y确认密码：

Estimated strength of the password: 50 
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

下一步，是否禁止 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) : y

下一步，是否移除测试数据库

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) : y

下一步，是否现在重新加载特权表

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) : y

三、以 root 用户身份登录MySQL

在MySQL 8.0上，root 用户默认通过auth_socket插件授权。

auth_socket插件通过 Unix socket 文件来验证所有连接到localhost的用户。这意味着你不能通过提供密码，验证为 root。

以 root 用户身份登录 MySQL服务器

sudo mysql -u root -p

输入密码后，看到以下信息即登陆成功

Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 16
Server version: 8.0.28-0ubuntu0.20.04.3 (Ubuntu)

Copyright (c) 2000, 2022, Oracle and/or its affiliates.

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.

(root@localhost)[(none)]>

执行这句：

select host,user,plugin,authentication_string from mysql.user;

修改权限：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码'

刷新配置：

FLUSH PRIVILEGES;

OK

至于查看mysql的状态还是显示如下：

root@b6beb613ab1f:/var# sudo systemctl status mysql
System has not been booted with systemd as init system (PID 1). Can't operate.
Failed to connect to bus: Host is down

这个我也不知道，总之就是MySQL能用

打开远程连接

一、打开 MySQL 的远程连接

mysql> grant all privileges on *.* to root@'%';
mysql> flush privileges; # 将用户权限从内存写入到磁盘

二、开启服务器端口，防火墙端口

sudo ufw enable #开启ufw
sudo ufw allow 3306 #允许3306端口传入传出流量
sudo ufw status #查看防火墙开启了哪些端口，ipv4和ipv6分别会有一个3306端口

ufw（Ubuntu自带） 是uncomplicated firewall 的缩写，意为简单的防火墙管理，相比于 iptables 工具更容易管理系统防火墙。

三、修改配置文件

找到mysql配置文件，在配置文件中的[mysqld]作用域下找到变量bind-address，修改为0.0.0.0，代表任意来源

[mysqld]
 
bind-address=0.0.0.0

重启 MySQL 服务器

sudo service mysql restart

问题

这种错误一般是 mysql 服务器异常关机导致的

Ubuntu

sudo service mysql stop
sudo usermod -d /var/lib/mysql/ mysql
sudo service mysql start

Centos

sudo systemctl stop mysql.service
sudo usermod -d /var/lib/mysql/ mysql
sudo systemctl start mysql.service