ubuntu20.4安装mysql8.0

1.查看本机是否安装mysql

service mysqld status
service mysql status

# Unit mysqld.service could not be found.

# Unit mysql.service could not be found.

未找到mysqld &mysql 服务 没有安装

whereis mysql 

# mysql: /etc/mysql

有初始化的mysql文件

2.卸载清除mysql 

TIPS：初始化系统不建议删除mysql文件

                如果已经安装过旧版，需要卸载可：

                1.非apt安装  删除目录和文件 以及配置项

                2.apt安装 

                 执行   $    sudo apt-get --purge remove mysql-server mysql-common mysql-client

sudo find / -name mysql

find 命令找到mysql 文件和目录，如下：(不加sudu 扫描文件时会大量提示Permission denied)

/usr/share/bash-completion/completions/mysql

# 命令自动补全目录 不能删除
find: ‘/run/user/1000/gvfs’: Permission denied
find: ‘/run/user/125/gvfs’: Permission denied
/etc/apparmor.d/abstractions/mysql

#不建议删除
/etc/mysql

#不可删 删除后apt安装会提示错误
/snap/core20/1361/etc/apparmor.d/abstractions/mysql
/snap/core20/1361/usr/share/bash-completion/completions/mysql
/snap/core18/2284/etc/apparmor.d/abstractions/mysql
/snap/core18/2284/usr/share/bash-completion/completions/mysql
/snap/core18/2128/etc/apparmor.d/abstractions/mysql
/snap/core18/2128/usr/share/bash-completion/completions/mysql

# 系统只读文件 不删

3.apt安装mysql服务

 sudo apt-get update 

更新软件包

sudo apt-get install -y mysql-server mysql-client

安装mysql-server mysql-client 服务和客户端

报错：

update-alternatives: using /var/lib/mecab/dic/ipadic-utf8 to provide /var/lib/mecab/dic/debian (mecab-dictionary) in auto mode
Setting up mysql-server-8.0 (8.0.28-0ubuntu0.20.04.3) ...
update-alternatives: warning: alternative /etc/mysql/my.cnf.fallback (part of link group my.cnf) doesn't exist; removing from list of alternatives
update-alternatives: warning: /etc/alternatives/my.cnf is dangling; it will be updated with best choice
update-alternatives: using /etc/mysql/mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in auto mode
Renaming removed key_buffer and myisam-recover options (if present)
ERROR: Unable to start MySQL server:
mysqld: Can't read dir of '/etc/mysql/conf.d/' (OS errno 2 - No such file or directory)
mysqld: [ERROR] Stopped processing the 'includedir' directive in file /etc/mysql/my.cnf at line 20.
mysqld: [ERROR] Fatal error in defaults handling. Program aborted!
Please take a look at https://wiki.debian.org/Teams/MySQL/FAQ for tips on fixing common upgrade issues.
Once the problem is resolved, run apt-get --fix-broken install to retry.
dpkg: error processing package mysql-server-8.0 (--configure):
 installed mysql-server-8.0 package post-installation script subprocess returned error exit status 1
dpkg: dependency problems prevent configuration of mysql-server:
 mysql-server depends on mysql-server-8.0; however:
  Package mysql-server-8.0 is not configured yet.

dpkg: error processing package mysql-server (--configure):
 dependency problems - leaving unconfigured
Processing triggers for systemd (245.4-4ubuntu3.15) ...
No apport report written because the error message indicates its a followup error from a previous failure.
                          Processing triggers for man-db (2.9.1-1) ...
Processing triggers for libc-bin (2.31-0ubuntu9.7) ...
Errors were encountered while processing:
 mysql-server-8.0
 mysql-server
E: Sub-process /usr/bin/dpkg returned an error code (1)

原因： 

因为前一步删除了'/etc/mysql' 目录，安装启动时找不到目录，所以报错

解决办法：

sudo mkdir /etc/mysql/conf.d/

新建目录

sudo apt-get install -y mysql-server mysql-client

重新安装 或者执行 

sudo apt-get --fix-broken install 

修复安装终止错误

查看mysql服务状态 

service mysql status

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:>
     Active: active (running) since Sat 2022-03-05 13:30:14 CST; 16min ago
    Process: 7932 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=e>
   Main PID: 7952 (mysqld)
     Status: "Server is operational"
      Tasks: 37 (limit: 4588)
     Memory: 352.3M
     CGroup: /system.slice/mysql.service
             └─7952 /usr/sbin/mysqld

Mar 05 13:30:14 test systemd[1]: Starting MySQL Community Server...
Mar 05 13:30:14 test systemd[1]: Started MySQL Community Server.

查看mysql端口监听

netstat -tnlp | grep 3306

(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN   

查看mysql配置文件

cat /etc/mysql/my.cnf 

/etc/mysql/mysql.cnf

#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
# 
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

mysql的所有配置文件后缀名都为'.cnf'

配置文件指向2个目录

/etc/mysql/conf.d/ 目录新建的 没有文件 （/etc/mysql/my.cnf &/etc/mysql/mysql.cnf 中这一行可以注释掉？）

/etc/mysql/mysql.conf.d/  目录包含 mysql.cnf  mysqld.cnf

mysql.cnf  无默认配置项

#
# The MySQL database client configuration file
#
# Ref to https://dev.mysql.com/doc/refman/en/mysql-command-options.html

[mysql]

mysqld.cnf 配置项如下：

#
# The MySQL database server configuration file.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

[mysqld]
#
# * Basic Settings
#
user        = mysql
# pid-file    = /var/run/mysqld/mysqld.pid
# socket    = /var/run/mysqld/mysqld.sock
# port        = 3306
# datadir    = /var/lib/mysql

# If MySQL is running as a replication slave, this should be
# changed. Ref https://dev.mysql.com/doc/refman/8.0/en/server-system-variables.html#sysvar_tmpdir
# tmpdir        = /tmp
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
mysqlx-bind-address    = 127.0.0.1
#
# * Fine Tuning
#
key_buffer_size        = 16M
# max_allowed_packet    = 64M
# thread_stack        = 256K

# thread_cache_size       = -1

# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover-options  = BACKUP

# max_connections        = 151

# table_open_cache       = 4000

#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
#
# Log all queries
# Be aware that this log type is a performance killer.
# general_log_file        = /var/log/mysql/query.log
# general_log             = 1
#
# Error log - should be very few entries.
#
log_error = /var/log/mysql/error.log
#
# Here you can see queries with especially long duration
# slow_query_log        = 1
# slow_query_log_file    = /var/log/mysql/mysql-slow.log
# long_query_time = 2
# log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or for replication.
# note: if you are setting up a replication slave, see README.Debian about
#       other settings you may need to change.
# server-id        = 1
# log_bin            = /var/log/mysql/mysql-bin.log
# binlog_expire_logs_seconds    = 2592000
max_binlog_size   = 100M
# binlog_do_db        = include_database_name
# binlog_ignore_db    = include_database_name
 

mysql的所有全局配置项都在   /etc/mysql/mysql.conf.d/mysqld.cnf  中配置

4.修改root用户初始化密码

 4.1 查看系统debian用户配置文件

sudo cat /etc/mysql/debian.cnf

# Automatically generated for Debian scripts. DO NOT TOUCH!
[client]
host     = localhost
user     = debian-sys-maint
password = xxxxxxxxxxxxxxxx   #系统初始化密码
socket   = /var/run/mysqld/mysqld.sock
[mysql_upgrade]
host     = localhost
user     = debian-sys-maint   
password = xxxxxxxxxxxxxxxx    #系统初始化密码
socket   = /var/run/mysqld/mysqld.sock

4.2 debian-sys-maint 用户登录mysql 

mysql -udebian-sys-maint -p

# -u后不能有空格

# 输入密码

# 进入mysql shell 

mysql> show databases;

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.03 sec)
 

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> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | auth_socket           |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)

#查看用户表   用户，主机，密码类型
 

4.3 修改密码

mysql shell 输入：

ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';

mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '123456';
Query OK, 0 rows affected (0.01 sec)
# 密码重置成功

#再次查看用户表   用户，主机，密码类型

mysql> select user,host,plugin from user;
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| debian-sys-maint | localhost | caching_sha2_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
5 rows in set (0.00 sec)
# mysql密码类型已被修改

# exit;

mysql> exit
Bye

4.4 check：使用root用户名和密码123456登录

mysql -uroot -p

Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 12
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.

mysql> 
#进入mysql shell 密码修改成功

备注：

修改密码时候使用以下语法会报错

update mysql.user set authentication_string=PASSWORD('123'), plugin='mysql_native_password' where user='root';

mysql> update mysql.user set authentication_string=PASSWORD('123'), plugin='mysql_native_password' where user='root';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '('123'), plugin='mysql_native_password' where user='root'' at line 1

原因：mysql5.7版本后不支持password函数

其它方法：

先修改root用户的密码类型 

update user set plugin='mysql_native_password' where user='root';

再重新定义新密码

alter user 'root'@'localhost' identified by '123456';

同样的效果

5.创建新用户并授权

5.1登录mysql 进入mysql shell

mysql -uroot -p123456

5.2 创建用户test 密码123456

mysql> CREATE USER 'test'@'%' IDENTIFIED BY '123456';
Query OK, 0 rows affected (0.02 sec)

注意：这里创建用户后密码类型默认为 caching_sha2_password 

           可能会有其它APP连接mysql报错

# 使用语句修改密码

ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY '123456';

# 密码类型为 mysql_native_password

5.3 给test用户授权

grant all on 数据库名.数据库表 to 用户名@'%' identified by "密码";

all 可以替换为 select,delete,update,create,drop

数据库名 所有的 用*

数据库表 所有的 用*

GRANT ALL PRIVILEGES  on *.* to 'test'@'%' identified by '123456';

# 语法错误   

GRANT ALL PRIVILEGES ON *.* TO `test`@`%` WITH GRANT OPTION;

# 语法错误   

GRANT ALL PRIVILEGES ON *.* TO test@'%' WITH GRANT OPTION;

# OK

#用户名不用引号

5.4 查看test用户权限 

select * from user where user = 'test' \G

# \G 表示竖着显示所有字段值

mysql> select * from user where user ='test' \G
*************************** 1. row ***************************
                    Host: %
                    User: test
             Select_priv: Y
             Insert_priv: Y
             Update_priv: Y
             Delete_priv: Y
             Create_priv: Y
               Drop_priv: Y
             Reload_priv: Y
           Shutdown_priv: Y
            Process_priv: Y
               File_priv: Y
              Grant_priv: Y
         References_priv: Y
              Index_priv: Y
              Alter_priv: Y
            Show_db_priv: Y
              Super_priv: Y
   Create_tmp_table_priv: Y
        Lock_tables_priv: Y
            Execute_priv: Y
         Repl_slave_priv: Y
        Repl_client_priv: Y
        Create_view_priv: Y
          Show_view_priv: Y
     Create_routine_priv: Y
      Alter_routine_priv: Y
        Create_user_priv: Y
              Event_priv: Y
            Trigger_priv: Y
  Create_tablespace_priv: Y
                ssl_type: 
              ssl_cipher: 0x
             x509_issuer: 0x
            x509_subject: 0x
           max_questions: 0
             max_updates: 0
         max_connections: 0
    max_user_connections: 0
                  plugin: caching_sha2_password
   authentication_string: $A$005$}d?
]!8}%6XSQshEI3fSKGF9UWmq0qX1SwOWeVZQnBoDguVpPt0qCVo96
        password_expired: N
   password_last_changed: 2022-03-05 15:18:18
       password_lifetime: NULL
          account_locked: N
        Create_role_priv: Y
          Drop_role_priv: Y
  Password_reuse_history: NULL
     Password_reuse_time: NULL
Password_require_current: NULL
         User_attributes: NULL
1 row in set (0.01 sec)
 

OK 权限配置成功

6.配置其它IP访问连接mysql

6.1 查看mysql服务 监听端口

$ netstat -tnlp
(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:3306          0.0.0.0:*               LISTEN      -                   
tcp6       0      0 ::1:631                 :::*                    LISTEN 

只有本地环回监听 ，其它IP无法连接mysql服务

6.2 修改mysql配置文件 /etc/mysql/mysql.conf.d/mysqld.cnf

$ sudo gedit  /etc/mysql/mysql.conf.d/mysqld.cnf

# 找到如下两行 

bind-address        = 127.0.0.1
mysqlx-bind-address    = 127.0.0.1

# 注释掉 bind-address        = 127.0.0.1 

# 或者修改 bind-address  =  0.0.0.0 

保存退出

6.3 重启mysql服务 

重启

sudo service mysql restart

查看mysql服务状态

service mysql status

● mysql.service - MySQL Community Server
     Loaded: loaded (/lib/systemd/system/mysql.service; enabled; vendor preset:>
     Active: active (running) since Sat 2022-03-05 16:32:32 CST; 7s ago
    Process: 9128 ExecStartPre=/usr/share/mysql/mysql-systemd-start pre (code=e>
   Main PID: 9136 (mysqld)
     Status: "Server is operational"
      Tasks: 38 (limit: 4588)
     Memory: 357.9M
     CGroup: /system.slice/mysql.service
             └─9136 /usr/sbin/mysqld
7秒前启动，重启成功

6.4 查看端口状态 

$ netstat -tnlp

(Not all processes could be identified, non-owned process info
 will not be shown, you would have to be root to see it all.)
Active Internet connections (only servers)
Proto Recv-Q Send-Q Local Address           Foreign Address         State       PID/Program name    
tcp        0      0 127.0.0.53:53           0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:631           0.0.0.0:*               LISTEN      -                   
tcp        0      0 127.0.0.1:33060         0.0.0.0:*               LISTEN      -                   
tcp        0      0 0.0.0.0:3306            0.0.0.0:*               LISTEN      -                   
tcp6       0      0 ::1:631                 :::*                    LISTEN 

OK 其它IP可以访问本机mysql服务 

如果3306监听IP为127.0.0.1 其它IP访问会报错

6.5 windows下Navicat访问ubuntu-mysql  

6.5.1 查看本机IP地址 

$ ifconfig 

ens33: flags=4163<UP,BROADCAST,RUNNING,MULTICAST>  mtu 1500
        inet 192.168.1.5  netmask 255.255.255.0  broadcast 192.168.1.255
        inet6 fe80::38a5:3201:f026:f414  prefixlen 64  scopeid 0x20<link>
        inet6 240e:36a:141e:600:df5e:846e:ea64:b1a  prefixlen 64  scopeid 0x0<gl

desktop版，可以直接在settings--network--wared--setting查看ubuntu  IP地址

6.5.2 Navicat连接测试 

 OK~