参考 https://dev.mysql.com/doc/refman/8.0/en/binary-installation.html https://dev.mysql.com/doc/refman/8.0/en/starting-server.html https://www.cnblogs.com/kaishirenshi/p/12667004.html


安装MySQL

  1.     ###下载安装包
  2.     wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.25-linux-glibc2.12-x86_64.tar.xz
  5.     ### 对MySQL进行重命名
  6.     [root@localhost local]# mv mysql-8.0.25-linux-glibc2.12-x86_64/ mysql
  8.     [root@localhost local]# ll
  9.     total 0
  10.     drwxr-xr-x. 2 root root   6 Nov  3  2020 bin
  11.     drwxr-xr-x. 2 root root   6 Nov  3  2020 etc
  12.     drwxr-xr-x. 2 root root   6 Nov  3  2020 games
  13.     drwxr-xr-x. 2 root root   6 Nov  3  2020 include
  14.     drwxr-xr-x. 2 root root   6 Nov  3  2020 lib
  15.     drwxr-xr-x. 3 root root  17 Jun 16 02:59 lib64
  16.     drwxr-xr-x. 2 root root   6 Nov  3  2020 libexec
  17.     drwxr-xr-x. 9 root root 129 Jun 16 03:16 mysql
  18.     drwxr-xr-x. 2 root root   6 Nov  3  2020 sbin
  19.     drwxr-xr-x. 5 root root  49 Jun 16 02:59 share
  20.     drwxr-xr-x. 2 root root   6 Nov  3  2020 src
  22.     [root@localhost local]#
  23.     [root@localhost local]#
  24.     ###创建用户组
  25.     [root@localhost local]# groupadd mysql
  26.     [root@localhost local]#
  27.     [root@localhost local]#
  28.     [root@localhost local]#
  29.     ####添加MySQL用户
  30.     [root@localhost local]# useradd -r -g mysql -s /bin/false mysql
  32.     [root@localhost local]# cd mysql/
  33.     [root@localhost mysql]#
  34.     [root@localhost mysql]#
  35.     [root@localhost mysql]#
  37.     [root@localhost mysql]# mkdir mysql-files
  38.     [root@localhost mysql]#
  39.     [root@localhost mysql]#
  40.     [root@localhost mysql]#
  41.     [root@localhost mysql]# ll
  42.     total 284
  43.     drwxr-xr-x.  2 7161 31415   4096 Apr 23 14:56 bin
  44.     drwxr-xr-x.  2 7161 31415     55 Apr 23 14:56 docs
  45.     drwxr-xr-x.  3 7161 31415    282 Apr 23 14:56 include
  46.     drwxr-xr-x.  6 7161 31415    201 Apr 23 14:56 lib
  47.     -rw-r--r--.  1 7161 31415 274942 Apr 23 11:06 LICENSE
  48.     drwxr-xr-x.  4 7161 31415     30 Apr 23 14:56 man
  49.     drwxr-xr-x.  2 root root       6 Jun 16 03:17 mysql-files
  50.     -rw-r--r--.  1 7161 31415    666 Apr 23 11:06 README
  51.     drwxr-xr-x. 28 7161 31415   4096 Apr 23 14:56 share
  52.     drwxr-xr-x.  2 7161 31415     77 Apr 23 14:56 support-files
  53.     [root@localhost mysql]#
  54.     [root@localhost mysql]#
  57.     ####赋予MySQL用户MySQL用户组 对mysql-files的权限
  58.     [root@localhost mysql]# chown mysql:mysql mysql-files
  59.     [root@localhost mysql]#
  60.     [root@localhost mysql]#
  61.     [root@localhost mysql]#
  62.     [root@localhost mysql]# ll
  63.     total 284
  64.     drwxr-xr-x.  2  7161 31415   4096 Apr 23 14:56 bin
  65.     drwxr-xr-x.  2  7161 31415     55 Apr 23 14:56 docs
  66.     drwxr-xr-x.  3  7161 31415    282 Apr 23 14:56 include
  67.     drwxr-xr-x.  6  7161 31415    201 Apr 23 14:56 lib
  68.     -rw-r--r--.  1  7161 31415 274942 Apr 23 11:06 LICENSE
  69.     drwxr-xr-x.  4  7161 31415     30 Apr 23 14:56 man
  70.     drwxr-xr-x.  2 mysql mysql      6 Jun 16 03:17 mysql-files
  71.     -rw-r--r--.  1  7161 31415    666 Apr 23 11:06 README
  72.     drwxr-xr-x. 28  7161 31415   4096 Apr 23 14:56 share
  73.     drwxr-xr-x.  2  7161 31415     77 Apr 23 14:56 support-files
  74.     [root@localhost mysql]#
  75.     [root@localhost mysql]#
  78.     ###赋予MySQL用户的执行权限
  79.     [root@localhost mysql]# chmod 750 mysql-files
  80.     [root@localhost mysql]#
  81.     [root@localhost mysql]#
  82.     [root@localhost mysql]# ll
  83.     total 284
  84.     drwxr-xr-x.  2  7161 31415   4096 Apr 23 14:56 bin
  85.     drwxr-xr-x.  2  7161 31415     55 Apr 23 14:56 docs
  86.     drwxr-xr-x.  3  7161 31415    282 Apr 23 14:56 include
  87.     drwxr-xr-x.  6  7161 31415    201 Apr 23 14:56 lib
  88.     -rw-r--r--.  1  7161 31415 274942 Apr 23 11:06 LICENSE
  89.     drwxr-xr-x.  4  7161 31415     30 Apr 23 14:56 man
  90.     drwxr-x---.  2 mysql mysql      6 Jun 16 03:17 mysql-files
  91.     -rw-r--r--.  1  7161 31415    666 Apr 23 11:06 README
  92.     drwxr-xr-x. 28  7161 31415   4096 Apr 23 14:56 share
  93.     drwxr-xr-x.  2  7161 31415     77 Apr 23 14:56 support-files
  94.     [root@localhost mysql]#
  95.     [root@localhost mysql]#
  98.     ###执行MySQL的初始化  将会生成一个data文件夹 ，最后一行生成一个temporary密码
  99.          ###!!!配置my.cnf应在初始化前，参考 大纲中配置my.cnf，不过按顺序中依然没问题
  100.     [root@localhost mysql]# bin/mysqld --initialize --user=mysql
  101.     2021-06-16T07:18:33.509368Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysqld 8.0.25) initializing of server in progress as process 1682
  102.     2021-06-16T07:18:33.521073Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
  103.     2021-06-16T07:18:34.461153Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
  104.     2021-06-16T07:18:35.564759Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: 2M78VcFDrf+G
  105.     [root@localhost mysql]#
  106.     [root@localhost mysql]#
  107.     [root@localhost mysql]# cd bin
  108.     [root@localhost bin]#
  109.     [root@localhost bin]#
  110.     [root@localhost bin]# ps -ef | grep mysql
  111.     root        1734    1523  0 03:19 pts/0    00:00:00 grep --color=auto mysql
  113.     [root@localhost mysql]# ll
  114.     total 288
  115.     drwxr-xr-x.  2  7161 31415   4096 Apr 24 02:56 bin
  116.     drwxr-x---.  6 mysql mysql   4096 Jun 16 15:18 data
  117.     drwxr-xr-x.  2  7161 31415     55 Apr 24 02:56 docs
  118.     drwxr-xr-x.  3  7161 31415    282 Apr 24 02:56 include
  119.     drwxr-xr-x.  6  7161 31415    201 Apr 24 02:56 lib
  120.     -rw-r--r--.  1  7161 31415 274942 Apr 23 23:06 LICENSE
  121.     drwxr-xr-x.  4  7161 31415     30 Apr 24 02:56 man
  122.     drwxr-x---.  2 mysql mysql      6 Jun 16 15:17 mysql-files
  123.     -rw-r--r--.  1  7161 31415    666 Apr 23 23:06 README
  124.     drwxr-xr-x. 28  7161 31415   4096 Apr 24 02:56 share
  125.     drwxr-xr-x.  2  7161 31415     77 Apr 24 02:56 support-files
  126.     [root@localhost mysql]#
  127.     [root@localhost mysql]#
  128.     [root@localhost mysql]# cd data
  129.     [root@localhost data]# ll
  130.     total 176568
  131.     -rw-r-----. 1 mysql mysql       56 Jun 16 15:18  auto.cnf
  132.     -rw-------. 1 mysql mysql     1676 Jun 16 15:18  ca-key.pem
  133.     -rw-r--r--. 1 mysql mysql     1112 Jun 16 15:18  ca.pem
  134.     -rw-r--r--. 1 mysql mysql     1112 Jun 16 15:18  client-cert.pem
  135.     -rw-------. 1 mysql mysql     1676 Jun 16 15:18  client-key.pem
  136.     -rw-r-----. 1 mysql mysql   196608 Jun 16 15:18 '#ib_16384_0.dblwr'
  137.     -rw-r-----. 1 mysql mysql  8585216 Jun 16 15:18 '#ib_16384_1.dblwr'
  138.     -rw-r-----. 1 mysql mysql     5931 Jun 16 15:18  ib_buffer_pool
  139.     -rw-r-----. 1 mysql mysql 12582912 Jun 16 15:18  ibdata1
  140.     -rw-r-----. 1 mysql mysql 50331648 Jun 16 15:18  ib_logfile0
  141.     -rw-r-----. 1 mysql mysql 50331648 Jun 16 15:18  ib_logfile1
  142.     drwxr-x---. 2 mysql mysql        6 Jun 16 15:18 '#innodb_temp'
  143.     drwxr-x---. 2 mysql mysql      143 Jun 16 15:18  mysql
  144.     -rw-r-----. 1 mysql mysql 25165824 Jun 16 15:18  mysql.ibd
  145.     drwxr-x---. 2 mysql mysql     8192 Jun 16 15:18  performance_schema
  146.     -rw-------. 1 mysql mysql     1680 Jun 16 15:18  private_key.pem
  147.     -rw-r--r--. 1 mysql mysql      452 Jun 16 15:18  public_key.pem
  148.     -rw-r--r--. 1 mysql mysql     1112 Jun 16 15:18  server-cert.pem
  149.     -rw-------. 1 mysql mysql     1676 Jun 16 15:18  server-key.pem
  150.     drwxr-x---. 2 mysql mysql       28 Jun 16 15:18  sys
  151.     -rw-r-----. 1 mysql mysql 16777216 Jun 16 15:18  undo_001
  152.     -rw-r-----. 1 mysql mysql 16777216 Jun 16 15:18  undo_002
  153.     [root@localhost data]# cd ..
  154.     [root@localhost mysql]#
  155.     [root@localhost mysql]#
  156.     [root@localhost mysql]#
  157.     ####无法采用这种方式启动
  158.     [root@localhost mysql]# systemctl start mysqld
  159.     Failed to start mysqld.service: Unit mysqld.service not found.
  162.     [root@localhost mysql]#
  163.     [root@localhost mysql]#
  165.     ###启动MySQL
  166.     [root@localhost mysql]# bin/mysqld_safe --user=mysql &
  167.     [1] 1816
  168.     [root@localhost mysql]# Logging to '/usr/local/mysql/data/localhost.localdomain.err'.
  169.     2021-06-16T07:22:46.224871Z mysqld_safe Starting mysqld daemon with databases from /usr/local/mysql/data
  171.     [root@localhost mysql]#
  172.     [root@localhost mysql]#
  174.     ###查看MySQL是否启动
  175.     [root@localhost mysql]# ps -ef | grep mysql
  176.     root        1816    1523  0 15:22 pts/0    00:00:00 /bin/sh bin/mysqld_safe --user=mysql
  177.     mysql       1898    1816  9 15:22 pts/0    00:00:01 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=localhost.localdomain.pid
  178.     root        1951    1523  0 15:22 pts/0    00:00:00 grep --color=auto mysql
  179.     [root@localhost mysql]#
  180.     [root@localhost mysql]#
  181.     [root@localhost mysql]#
  182.     [root@localhost mysql]#
  183.     [root@localhost mysql]# cd bin
  186.     ###进入MySQL发现错误
  187.     [root@localhost bin]# ./mysql -uroot -p
  188.     ./mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
  189.     [root@localhost bin]#
  190.     [root@localhost bin]#
  193.     ####创建符号链接
  194.     [root@localhost bin]# sudo ln -s /usr/lib64/libtinfo.so.6.1 /usr/lib64/libtinfo.so.5
  195.     [root@localhost bin]#
  196.     [root@localhost bin]#
  197.     [root@localhost bin]# ./mysql -uroot -p
  198.     Enter password:
  199.     ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: YES)
  201.     ###将产生的临时密码已下面方式进行登录
  202.     [root@localhost bin]# ./mysql -uroot -p2M78VcFDrf+G
  203.     mysql: [Warning] Using a password on the command line interface can be insecure.
  204.     Welcome to the MySQL monitor.  Commands end with ; or \g.
  205.     Your MySQL connection id is 9
  206.     Server version: 8.0.25
  208.     Copyright (c) 2000, 2021, Oracle and/or its affiliates.
  210.     Oracle is a registered trademark of Oracle Corporation and/or its
  211.     affiliates. Other names may be trademarks of their respective
  212.     owners.
  214.     Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
  216.     mysql> show databases;
  217.     ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
  218.     mysql>
  219.     mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'root';
  220.     Query OK, 0 rows affected (0.00 sec)
  222.     mysql> show databases;
  223.     +--------------------+
  224.     | Database           |
  225.     +--------------------+
  226.     | information_schema |
  227.     | mysql              |
  228.     | performance_schema |
  229.     | sys                |
  230.     +--------------------+
  231.     4 rows in set (0.00 sec)
  233.     mysql> use mysql
  234.     Reading table information for completion of table and column names
  235.     You can turn off this feature to get a quicker startup with -A
  237.     Database changed
  238.     mysql>
  239.     mysql>
  240.     mysql> select user,password,host from user;
  241.     ERROR 1054 (42S22): Unknown column 'password' in 'field list'
  242.     mysql> select user,host from user;
  243.     +------------------+-----------+
  244.     | user             | host      |
  245.     +------------------+-----------+
  246.     | mysql.infoschema | localhost |
  247.     | mysql.session    | localhost |
  248.     | mysql.sys        | localhost |
  249.     | root             | localhost |
  250.     +------------------+-----------+
  251.     4 rows in set (0.00 sec)
  253.     mysql> update user set host = '%' where user = 'root';
  254.     Query OK, 1 row affected (0.00 sec)
  255.     Rows matched: 1  Changed: 1  Warnings: 0
  257.     mysql> select user,host from user;
  258.     +------------------+-----------+
  259.     | user             | host      |
  260.     +------------------+-----------+
  261.     | root             | %         |
  262.     | mysql.infoschema | localhost |
  263.     | mysql.session    | localhost |
  264.     | mysql.sys        | localhost |
  265.     +------------------+-----------+
  266.     4 rows in set (0.00 sec)
  268.     mysql>
  269.     mysql>
  270.     mysql> select version();
  271.     +-----------+
  272.     | version() |
  273.     +-----------+
  274.     | 8.0.25    |
  275.     +-----------+
  276.     1 row in set (0.00 sec)
  277.     ###刷新权限 就可以远程登陆了
  278.     mysql> flush privileges;
  279.     Query OK, 0 rows affected (0.00 sec)
  281.     mysql>
  282.     mysql>

配置防火墙


    //查看防火墙的状态
    [root@localhost ~]#  firewall-cmd --state
    running
    //关闭防火墙
    [root@localhost ~]# systemctl stop firewalld.service
    [root@localhost ~]#  firewall-cmd --state
    not running


    //查看3306端口是否开启
    firewall-cmd --query-port=3306/tcp

    ###开启防火墙设置端口
    systemctl start firewalld.service

    #### zone --作用域
    #### add-port=80/tcp--添加端口，格式为：端口/通讯协议
    #### permanent--永久生效，没有此参数重启后失效
    firewall-cmd --zone=public --add-port=3306/tcp --permanent
    ####开启3306端口后，workbench或naivcat 就能连接到MySQL数据库了

    ####重启防火墙
    firewall-cmd --reload

    ####永久关闭防火墙
    [root@localhost ~]#  sudo systemctl disable firewalld
    Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
    Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
    [root@localhost ~]#

设置开机自启动


        //配置环境变量和系统自启动
    配置mysql环境变量：将mysql的bin目录加入PATH环境变量，编辑 /etc/profile文件：

    vim /etc/profile

    #最后添加下面一行
    ###PATH与=不能有空格
    export PATH=$PATH:/usr/local/mysql/bin

    #执行命令使其生效：
    source /etc/profile

    ###用 export 命令查看PATH值：

    echo $PATH

    ####设置开机自启动：

    ####复制启动脚本到资源目录：

    [root@localhost mysql]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysqld 

    ####增加 mysqld 服务控制脚本执行权限：

    [root@localhost mysql]# chmod +x /etc/rc.d/init.d/mysqld 

    ####将 mysqld 服务加入到系统服务：

    [root@localhost mysql]# chkconfig --add mysqld

    ####检查mysqld服务是否已经生效：

    [root@localhost mysql]# chkconfig --list mysqld

    ####命令输出类似下面的结果：

    mysqld 0:off 1:off 2:on 3:on 4:on 5:on 6:off

    表明mysqld服务已经生效，在2、3、4、5运行级别随系统启动而自动启动，以后可以使用 service 命令控制 mysql 的启动和停止。

    ####启动指令
    service mysqld stop

远程登录验证

如果远程登录出现以下问题

C:\Users\Zk_Fo>mysql -h192.168.200.133 -uroot -p
Enter password:
ERROR 2059 (HY000): Authentication plugin 'caching_sha2_password' cannot be loaded: ÕÒ²»µ½Ö¸¶¨µÄÄ£¿é¡£

在MySQL服务器（即本环境中得虚拟机中）登录MySQL进行如下修改

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 from user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| root             | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)


mysql> alter user 'root'@'%' identified with mysql_native_password by 'root';
Query OK, 0 rows affected (0.00 sec)


mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

查看MySQL配置文件

#### MySQL配置文件加载顺序
[root@localhost ~]# mysql --help|grep 'my.cnf'
                      order of preference, my.cnf, $MYSQL_TCP_PORT,
/etc/my.cnf /etc/mysql/my.cnf /usr/local/mysql/etc/my.cnf ~/.my.cnf



####查看是否使用了MySQL配置文件 好像没用
[root@localhost ~]# ps aux|grep mysql|grep 'my.cnf'
[root@localhost ~]#

配置My.cnf

https://dev.mysql.com/doc/refman/5.7/en/server-configuration-defaults.html 由于MySQL8 已不在安装包中包含配置文件，建议初始化MySQL之前去建立my.cnf Note: service mysqld stop

[client]
port = 3306
#根据实际情况调整mysql.sock配置
socket = /tmp/mysql.sock

[mysqld]
#Mysql服务的唯一编号 每个mysql服务Id需唯一
server-id = 1

#服务端口号 默认3306
port = 3306

#mysql安装根目录
basedir = /usr/local/mysql

#mysql数据文件所在位置
datadir = /usr/local/mysql/data

#pid
pid-file = /usr/local/mysql/data/mysql.pid

#设置socke文件所在目录
socket = /tmp/mysql.sock

#设置临时目录
tmpdir = /tmp

# 用户
user = mysql

# 允许访问的IP网段
bind-address = 0.0.0.0

# 跳过密码登录
#skip-grant-tables

#主要用于MyISAM存储引擎,如果多台服务器连接一个数据库则建议注释下面内容
skip-external-locking

#只能用IP地址检查客户端的登录，不用主机名
skip_name_resolve = 1

#事务隔离级别，默认为可重复读，mysql默认可重复读级别（此级别下可能参数很多间隙锁，影响性能）
transaction_isolation = REPEATABLE-READ

#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server = utf8mb4

#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_general_ci

#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'

#是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names = 1

#最大连接数
max_connections = 400

#最大错误连接数
max_connect_errors = 1000

#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true

#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 128M


#MySQL连接闲置超过一定时间后(单位：秒)将会被强行关闭
#MySQL默认的wait_timeout  值为8个小时, interactive_timeout参数需要同时配置才能生效
interactive_timeout = 1800
wait_timeout = 1800

#内部内存临时表的最大值 ，设置成128M。
#比如大数据量的group by ,order by时可能用到临时表，
#超过了这个值将写入磁盘，系统IO压力增大
tmp_table_size = 134217728
max_heap_table_size = 134217728



#数据库错误日志文件
log_error = error.log

#慢查询sql日志设置
slow_query_log = 1
slow_query_log_file = slow.log

#检查未使用到索引的sql
log_queries_not_using_indexes = 1

#针对log_queries_not_using_indexes开启后，记录慢sql的频次、每分钟记录的条数
log_throttle_queries_not_using_indexes = 5

#作为从库时生效,从库复制中如何有慢sql也将被记录
log_slow_slave_statements = 1

#慢查询执行的秒数，必须达到此值可被记录
long_query_time = 8

#检索的行数必须达到此值才可被记为慢查询
min_examined_row_limit = 100

#mysql binlog日志文件保存的过期时间，过期后自动删除
expire_logs_days = 5

1,删除data目录

cd /usr/local/mysql
rm -rf data

2,在/etc/ 创建my.cnf

3,初始化MySQL

bin/mysqld --initialize --user=mysql

临时密码会在 usr/local/mysql/data/error.log 中

4,启动MySQL

bin/mysqld_safe --user=mysql &

5,进入MySQL

./mysql -uroot -p

6,验证配置文件

比如 大小写的问题