安装mysql8.0

下载安装包

mysql官方下载地址： https://dev.mysql.com/downloads/

选择： MySQL Community Server

如果是centos，官方没有centos选择，选择redhat即可；需要查看内核版本，这里是el7,64位；命令如下：

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# uname -a
Linux iZ2ze8diszrt8lvjh9yr86Z 3.10.0-957.21.3.el7.x86_64 #1 SMP Tue Jun 18 16:35:19 UTC 2019 x86_64 x86_64 x86_64 GNU/Linux
[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# more /proc/version 
Linux version 3.10.0-957.21.3.el7.x86_64 (mockbuild@kbuilder.bsys.centos.org) (gcc version 4.8.5 20150623 (Red Hat 4.8.5-36) (G
CC) ) #1 SMP Tue Jun 18 16:35:19 UTC 2019

下载安装包(mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar)；bundle是包含所有按照包的；通过tar -xvf命令解压

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# tar -xvf mysql-8.0.18-1.el7.aarch64.rpm-bundle.tar 
mysql-community-embedded-compat-8.0.18-1.el7.aarch64.rpm
mysql-community-libs-compat-8.0.18-1.el7.aarch64.rpm
mysql-community-devel-8.0.18-1.el7.aarch64.rpm
mysql-community-test-8.0.18-1.el7.aarch64.rpm
mysql-community-libs-8.0.18-1.el7.aarch64.rpm
mysql-community-common-8.0.18-1.el7.aarch64.rpm
mysql-community-server-8.0.18-1.el7.aarch64.rpm
mysql-community-client-8.0.18-1.el7.aarch64.rpm

也可以通过 yum install mysql-community-server 安装，但是包比较大；

还可以通过wget获取，这种方式比较方便；但是需要先去官网登录，找到对应的下载url

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar
--2019-10-25 14:34:51--  https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar
Resolving dev.mysql.com (dev.mysql.com)... 137.254.60.11
Connecting to dev.mysql.com (dev.mysql.com)|137.254.60.11|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar [following]
--2019-10-25 14:34:53--  https://cdn.mysql.com//Downloads/MySQL-8.0/mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar
Resolving cdn.mysql.com (cdn.mysql.com)... 23.56.181.34
Connecting to cdn.mysql.com (cdn.mysql.com)|23.56.181.34|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 684851200 (653M) [application/x-tar]
Saving to: ‘mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar’

100%[=====================================================================================>] 684,851,200 15.3MB/s   in 56s    

2019-10-25 14:35:50 (11.6 MB/s) - ‘mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar’ saved [684851200/684851200]

mysql安装

centos7默认安装了mariadb，需要先进行卸载

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# rpm -qa | grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# rpm -qa | grep mariadb-server
[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# yum remove mariadb-libs.x86_64

安装顺序rpm包有依赖关系，如下：

rpm -ivh …common.rpm
rpm -ivh …libs.rpm
rpm -ivh …client.rpm
rpm -ivh …server.rpm

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# ll total 1999492 -rw-r--r-- 1 root root 684851200 Sep 23 15:36 mysql-8.0.18-1.el7.x86_64.rpm-bundle.tar -rw-r--r-- 1 7155 31415 40104640 Sep 23 15:19 mysql-community-client-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 611436 Sep 23 15:20 mysql-community-common-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 6915400 Sep 23 15:20 mysql-community-devel-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 23683600 Sep 23 15:20 mysql-community-embedded-compat-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 3877664 Sep 23 15:20 mysql-community-libs-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 1363968 Sep 23 15:20 mysql-community-libs-compat-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 450282440 Sep 23 15:21 mysql-community-server-8.0.18-1.el7.x86_64.rpm -rw-r--r-- 1 7155 31415 158001648 Sep 23 15:22 mysql-community-test-8.0.18-1.el7.x86_64.rpm [root@iZ2ze8diszrt8lvjh9yr86Z mysql]# rpm -ivh mysql-community-common-8.0.18-1.el7.x86_64.rpm warning: mysql-community-common-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-common-8.0.18-1.e################################# [100%] [root@iZ2ze8diszrt8lvjh9yr86Z mysql]# rpm -ivh mysql-community-libs-8.0.18-1.el7.x86_64.rpm warning: mysql-community-libs-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-libs-8.0.18-1.el7################################# [100%] [root@iZ2ze8diszrt8lvjh9yr86Z mysql]# rpm -ivh mysql-community-client-8.0.18-1.el7.x86_64.rpm warning: mysql-community-client-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-client-8.0.18-1.e################################# [100%] [root@iZ2ze8diszrt8lvjh9yr86Z mysql]# rpm -ivh mysql-community-server-8.0.18-1.el7.x86_64.rpm warning: mysql-community-server-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY Preparing... ################################# [100%] Updating / installing... 1:mysql-community-server-8.0.18-1.e################################# [100%] [root@iZ2ze8diszrt8lvjh9yr86Z mysql]#

mysql8默认安装到了/var/lib/mysql

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# ll /var/lib/mysql
total 155716
-rw-r----- 1 mysql mysql       56 Oct 25 14:52 auto.cnf
-rw-r----- 1 mysql mysql      178 Oct 25 14:53 binlog.000001
-rw-r----- 1 mysql mysql       16 Oct 25 14:52 binlog.index
-rw------- 1 mysql mysql     1680 Oct 25 14:52 ca-key.pem
-rw-r--r-- 1 mysql mysql     1112 Oct 25 14:52 ca.pem
-rw-r--r-- 1 mysql mysql     1112 Oct 25 14:52 client-cert.pem
-rw------- 1 mysql mysql     1680 Oct 25 14:52 client-key.pem
-rw-r----- 1 mysql mysql     3449 Oct 25 14:53 ib_buffer_pool
-rw-r----- 1 mysql mysql 12582912 Oct 25 14:53 ibdata1
-rw-r----- 1 mysql mysql 50331648 Oct 25 14:53 ib_logfile0
-rw-r----- 1 mysql mysql 50331648 Oct 25 14:52 ib_logfile1
drwxr-x--- 2 mysql mysql     4096 Oct 25 14:53 #innodb_temp
drwxr-x--- 2 mysql mysql     4096 Oct 25 14:52 mysql
-rw-r----- 1 mysql mysql 25165824 Oct 25 14:52 mysql.ibd
drwxr-x--- 2 mysql mysql     4096 Oct 25 14:52 performance_schema
-rw------- 1 mysql mysql     1680 Oct 25 14:52 private_key.pem
-rw-r--r-- 1 mysql mysql      452 Oct 25 14:52 public_key.pem
-rw-r--r-- 1 mysql mysql     1112 Oct 25 14:52 server-cert.pem
-rw------- 1 mysql mysql     1680 Oct 25 14:52 server-key.pem
drwxr-x--- 2 mysql mysql     4096 Oct 25 14:52 sys
-rw-r----- 1 mysql mysql 10485760 Oct 25 14:53 undo_001
-rw-r----- 1 mysql mysql 10485760 Oct 25 14:53 undo_002

默认配置文件在/etc/my.cnf

mysql启动停止

通过service mysqld服务就可以启动停止mysql了

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# service mysqld start
Redirecting to /bin/systemctl start mysqld.service

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# 
[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# service mysqld status
Redirecting to /bin/systemctl status mysqld.service
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Fri 2019-10-25 14:52:50 CST; 2s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 12015 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 12094 (mysqld)
   Status: "Server is operational"
   CGroup: /system.slice/mysqld.service
           └─12094 /usr/sbin/mysqld

Oct 25 14:52:42 iZ2ze8diszrt8lvjh9yr86Z systemd[1]: Starting MySQL Server...
Oct 25 14:52:50 iZ2ze8diszrt8lvjh9yr86Z systemd[1]: Started MySQL Server.
[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# service mysqld stop
Redirecting to /bin/systemctl stop mysqld.service

修改密码

　1. 刚刚启动成功之后, 用命令查看默认密码并且登录
默认密码查看：cat /var/log/mysqld.log | grep password 　　　登陆：mysql -u root -p 然后输入密码
　2. 修改密码　　　set global validate_password.policy=0; 　　　　set global validate_password.length=1; 　　　　ALTER USER "root"@"localhost" IDENTIFIED BY "1234"; // 新密码为1234 　3. exit 退出 mysql -u root -p 然后输入密码即可登录

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# cat /var/log/mysqld.log | grep password
2019-10-25T06:52:47.021899Z 5 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: lV.66%i>m,7F

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# mysql -u root -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.18

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> set global validate_password.policy=0;
Query OK, 0 rows affected (0.00 sec)
# 修改密码长度校验
mysql> set global validate_password.length=1;
Query OK, 0 rows affected (0.00 sec)
# 修改root密码
mysql> ALTER USER "root"@"localhost" IDENTIFIED  BY "123456";
Query OK, 0 rows affected (0.01 sec)

启动远程访问

首先关闭防火墙

//临时关闭
systemctl stop firewalld
//禁止开机启动
systemctl disable firewalld

mysql8.0修改了默认认证规则；之前的版本中加密规则是mysql_native_password,而在mysql8之后,加密规则是caching_sha2_password, 解决此问题方法有两种,一种是升级客户端驱动,一种是把mysql用户登录密码加密规则还原成mysql_native_password。

但网上教程大多数都是修改root为远程连接，并且修改认证模式；不建议这么操作，如果操作稍有问题会导致mysql无法链接，解决比较麻烦；

这里创建新用户admin，付给所有权限，可进行远程维护；操作如下

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
# 创建admin用户
mysql> create user  'admin'@'%' identified by 'Admin123';
Query OK, 0 rows affected (0.00 sec)
# 付给所有权限
mysql> grant all on *.* to 'admin'@'%' ;
Query OK, 0 rows affected (0.01 sec)

mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
# 修改admin认证模式及密码
mysql> ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'Admin123';
Query OK, 0 rows affected (0.00 sec)
# 刷新权限
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)

# 退出使用新用户验证登录
[root@iZ2ze8diszrt8lvjh9yr86Z ~]# mysql -uadmin -p
Enter password: 
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 22
Server version: 8.0.18 MySQL Community Server - GPL

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> 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 host,user,plugin from user;
+-----------+------------------+-----------------------+
| host      | user             | plugin                |
+-----------+------------------+-----------------------+
| %         | admin            | mysql_native_password |
| localhost | mysql.infoschema | caching_sha2_password |
| localhost | mysql.session    | caching_sha2_password |
| localhost | mysql.sys        | caching_sha2_password |
| localhost | root             | caching_sha2_password |
+-----------+------------------+-----------------------+

之后使用远程客户端Navicat验证，链接成功；

至此mysql全部安装完成！

mysql卸载

这里没有进行操作，仅作为记录

首先，停止mysql service mysqld stop

总共分两步：

1.卸载mysql组件

2.删除相关文件夹

# 第一步卸载mysql组件
# 查看MySQL组件
[root@localhost ~]# rpm -qa | grep -i mysql
mysql-community-libs-8.0.12-1.el7.x86_64
mysql-community-common-8.0.12-1.el7.x86_64
mysql-community-client-8.0.12-1.el7.x86_64
mysql-community-server-8.0.12-1.el7.x86_64
# 删除
[root@localhost ~]# rpm -ev mysql-community-server-8.0.12-1.el7.x86_64
Preparing packages...
mysql-community-server-8.0.12-1.el7.x86_64
warning: /etc/my.cnf saved as /etc/my.cnf.rpmsave
# 删除
[root@localhost ~]# rpm -ev mysql-community-client-8.0.12-1.el7.x86_64
Preparing packages...
mysql-community-client-8.0.12-1.el7.x86_64
# 删除失败
[root@localhost ~]# rpm -ev mysql-community-common-8.0.12-1.el7.x86_64
error: Failed dependencies:
    mysql-community-common(x86-64) >= 8.0.0 is needed by (installed) mysql-community-libs-8.0.12-1.el7.x86_64
# 继续查看
[root@localhost ~]# rpm -qa | grep -i mysql
mysql-community-libs-8.0.12-1.el7.x86_64
mysql-community-common-8.0.12-1.el7.x86_64
# 删除
[root@localhost ~]# rpm -e --noscripts mysql-community-libs-8.0.12-1.el7.x86_64
# 删除
[root@localhost ~]# rpm -e --noscripts mysql-community-common-8.0.12-1.el7.x86_64
# 查看
[root@localhost ~]# rpm -qa | grep -i mysql
# 第二步：删除相关目录
# 查看
[root@localhost ~]# find / -name mysql
/etc/mysql
/etc/selinux/targeted/active/modules/100/mysql
/var/lib/mysql
/var/lib/mysql/mysql
# 删除
[root@localhost ~]# rm -rf /etc/mysql
[root@localhost ~]# rm -rf /etc/selinux/targeted/active/modules/100/mysql
[root@localhost ~]# rm -rf /var/lib/mysql
[root@localhost ~]# find / -name mysql
# 再次检查，什么都没有说明卸载成功了
[root@localhost ~]# rpm -qa | grep -i mysql

mysql安装遇到的问题

问题一

CentOS安装rpm安装MySQL时爆出警告：

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# rpm -ivh mysql-community-server-8.0.18-1.el7.x86_64.rpm 
warning: mysql-community-server-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY

原因：这是由于yum安装了旧版本的GPG keys造成的解决办法：后面加上 --force --nodeps 如：rpm -ivh mysql-community-server-8.0.18-1.el7.x86_64.rpm --force --nodeps 好了，完美解决

只是一个警告，不处理也是可以的

问题二

提示libaio.so.1()(64bit) is needed by mysql-community-server-8.0.18-1.el7.x86_64

[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# rpm -ivh mysql-community-server-8.0.18-1.el7.x86_64.rpm 
warning: mysql-community-server-8.0.18-1.el7.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY
error: Failed dependencies:
    libaio.so.1()(64bit) is needed by mysql-community-server-8.0.18-1.el7.x86_64
    libaio.so.1(LIBAIO_0.1)(64bit) is needed by mysql-community-server-8.0.18-1.el7.x86_64
    libaio.so.1(LIBAIO_0.4)(64bit) is needed by mysql-community-server-8.0.18-1.el7.x86_64
[root@iZ2ze8diszrt8lvjh9yr86Z mysql]# yum install libaio

yum install libaio 这样就ok了，就可以继续安装mysql了。

Mysql常用配置查询

# 查询mysql最大连接数设置
show global variables like 'max_conn%';
# 查看当前用户链接数
show global status like 'Max_used_connections';
# 查看慢查询日志是否开启以及日志位置
show variables like 'slow_query%';
# 查看慢查询日志超时记录时间
show variables like 'long_query_time';
# 查看链接创建以及现在正在链接数
show status like 'Threads%';
# 查看数据库当前链接
show processlist;
# 查看数据库配置
show variables like '%quer%';

mysql配置文件my.cnf

参数请根据实际情况调整，不要无脑复制

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

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

#mysql安装根目录
basedir = /var/share/mysql-8.0

#mysql数据文件所在位置
datadir = /var/lib/mysql

#pid
pid-file = /var/lib/mysql/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 = READ-COMMITTED

#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用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

#禁用mysql的缓存查询结果集功能
#后期根据业务情况测试决定是否开启
#大部分情况下关闭下面两项
query_cache_size = 0
query_cache_type = 0

#数据库错误日志文件
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

慢查询

slow_query_log 慢查询开启状态 slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录） long_query_time 查询超过多少秒才记录

mysql> show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

方法一：修改全局变量

# 将slow_query_log 全局变量设置为“ON”状态
mysql> set global slow_query_log='ON'; 
# 设置慢查询日志存放的位置
mysql> set global slow_query_log_file='/usr/local/mysql/data/slow.log';
# 查询超过3秒就记录
mysql> set global long_query_time=3;

方法二：配置文件设置

修改配置文件my.cnf，在[mysqld]下的下方加入

[mysqld]
slow_query_log = ON
slow_query_log_file = /usr/local/mysql/data/slow.log
long_query_time = 3

3.重启MySQL服务

service mysqld restart

4.查看设置后的参数

mysql> show variables like 'slow_query%';
+---------------------+--------------------------------+
| Variable_name       | Value                          |
+---------------------+--------------------------------+
| slow_query_log      | ON                             |
| slow_query_log_file | /usr/local/mysql/data/slow.log |
+---------------------+--------------------------------+

mysql> show variables like 'long_query_time';
+-----------------+----------+
| Variable_name   | Value    |
+-----------------+----------+
| long_query_time | 1.000000 |
+-----------------+----------+