MySql : 阿里云CentOS7 安装MYSQL8.0

最新推荐文章于 2024-08-15 01:26:58 发布

weixin_33690367

最新推荐文章于 2024-08-15 01:26:58 发布

阅读量122

点赞数

文章标签：数据库运维

原文链接：https://my.oschina.net/gently/blog/3034323

版权

为什么80%的码农都做不了架构师？>>>

首先安装MySQL依赖libaio

[root@master ~]# yum list  installed | grep libaio 
[root@master ~]# yum install libaio 
......
Installed: libaio.x86_64 0:0.3.109-13.el7   
Complete!
[root@master ~]# 
[root@master ~]# yum list  installed | grep libaio 
libaio.x86_64                           0.3.109-13.el7                 @base

添加MySQL

# 检查MYSQL是否已安装：
[root@master ~]# yum list installed | grep mysql 

# 下载MYSQL Yum Repository 地址 
[root@master ~]#  wget http://dev.mysql.com/get/mysql80-community-release-el7-2.noarch.rpm 
......
Saving to: ‘mysql80-community-release-el7-2.noarch.rpm’

[root@master ~]# yum localinstall mysql80-community-release-el7-2.noarch.rpm
......
Installed: mysql80-community-release.noarch 0:el7-2 
Complete!

# 验证MYSQL是否添加成功
[root@master ~]# yum repolist enabled | grep 'mysql.*-community*'
mysql-connectors-community/x86_64 MySQL Connectors Community                  95
mysql-tools-community/x86_64      MySQL Tools Community                       84
mysql80-community/x86_64          MySQL 8.0 Community Server                  82

# 查看所有MYSQL版本
[root@master ~]# yum repolist all | grep mysql 
mysql-cluster-7.5-community/x86_64 MySQL Cluster 7.5 Community   disabled
mysql-cluster-7.5-community-source MySQL Cluster 7.5 Community - disabled
mysql-cluster-7.6-community/x86_64 MySQL Cluster 7.6 Community   disabled
mysql-cluster-7.6-community-source MySQL Cluster 7.6 Community - disabled
mysql-connectors-community/x86_64  MySQL Connectors Community    enabled:     95
mysql-connectors-community-source  MySQL Connectors Community -  disabled
mysql-tools-community/x86_64       MySQL Tools Community         enabled:     84
mysql-tools-community-source       MySQL Tools Community - Sourc disabled
mysql-tools-preview/x86_64         MySQL Tools Preview           disabled
mysql-tools-preview-source         MySQL Tools Preview - Source  disabled
mysql55-community/x86_64           MySQL 5.5 Community Server    disabled
mysql55-community-source           MySQL 5.5 Community Server -  disabled
mysql56-community/x86_64           MySQL 5.6 Community Server    disabled
mysql56-community-source           MySQL 5.6 Community Server -  disabled
mysql57-community/x86_64           MySQL 5.7 Community Server    disabled
mysql57-community-source           MySQL 5.7 Community Server -  disabled
mysql80-community/x86_64           MySQL 8.0 Community Server    enabled:     82
mysql80-community-source           MySQL 8.0 Community Server -  disabled

# 查看当前已启用的MYSQL版本：
[root@master ~]# yum repolist enabled | grep mysql 
mysql-connectors-community/x86_64 MySQL Connectors Community                  95
mysql-tools-community/x86_64      MySQL Tools Community                       84
mysql80-community/x86_64          MySQL 8.0 Community Server                  82

安装MySQL服务

#安装MYSQL服务：
[root@master ~]# yum install mysql-community-server 
......
Installed:
  mysql-community-libs.x86_64 0:8.0.15-1.el7
  mysql-community-libs-compat.x86_64 0:8.0.15-1.el7
  mysql-community-server.x86_64 0:8.0.15-1.el7  
......
Complete!
[root@master ~]# whereis mysql 
mysql: /usr/bin/mysql /usr/lib64/mysql /usr/share/man/man1/mysql.1.gz

#查看详细信息： 
[root@master ~]# rpm -qi mysql-community-server.x86_64 0:8.0.15-1.el7 

#启动MYSQL服务
[root@master ~]# systemctl start mysqld 

#查看MYSQL服务状态 
[root@master ~]# systemctl status mysqld 
● mysqld.service - MySQL Server
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2019-04-09 18:02:48 CST; 14s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 27410 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 27479 (mysqld)
   Status: "SERVER_OPERATING"
   CGroup: /system.slice/mysqld.service
           └─27479 /usr/sbin/mysqld
Apr 09 18:02:39 master systemd[1]: Starting MySQL Server...
Apr 09 18:02:48 master systemd[1]: Started MySQL Server.

-- 查看MYSQL版本：
[root@master ~]# mysql -V 
mysql  Ver 8.0.15 for Linux on x86_64 (MySQL Community Server - GPL)

MySQL使用


#查看MYSQL服务的初始化密码：
[root@master ~]# cat /var/log/mysqld.log | grep 'temp'
[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: aCkEg5wiy=/o

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

#更新root用户密码：
mysql> alter user 'root'@'localhost' identified by 'root用户密码';
Query OK, 0 rows affected (0.01 sec)

#创建普通用户berg并授权：
mysql> create user 'berg'@'%' identified by 'berg用户密码';

mysql> grant select,insert,update,delete on *.* to 'berg'@'%';

mysql> ALTER USER 'berg'@'%' IDENTIFIED WITH mysql_native_password BY 'berg用户密码';


#创建管理员用户admin并授权：
mysql> create user 'admin'@'%' identified by 'admin用户密码';

mysql> grant all privileges on *.* to 'admin'@'%';

mysql> ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'admin用户密码';


#使授权立刻生效
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)

mysql> select host, user from user;
+-----------+------------------+
| host      | user             |
+-----------+------------------+
| %         | admin            |
| %         | berg             |
| localhost | mysql.infoschema |
| localhost | mysql.session    |
| localhost | mysql.sys        |
| localhost | root             |
+-----------+------------------+
6 rows in set (0.00 sec)

#查看字符集
mysql> show variables like 'character%';
+--------------------------+--------------------------------+
| Variable_name            | Value                          |
+--------------------------+--------------------------------+
| character_set_client     | utf8mb4                        |
| character_set_connection | utf8mb4                        |
| character_set_database   | utf8mb4                        |
| character_set_filesystem | binary                         |
| character_set_results    | utf8mb4                        |
| character_set_server     | utf8mb4                        |
| character_set_system     | utf8                           |
| character_sets_dir       | /usr/share/mysql-8.0/charsets/ |
+--------------------------+--------------------------------+
8 rows in set (0.00 sec)

mysql> exit;
Bye

问题1：关于mysql8.0版本是否需要更改字符集为utf-8,默认使用的是utf8mb4

Mysql 中保存 4 字节长度字符，需要使用 utf8mb4 字符集，而utf8字符集最大保存3字节长度字符。
为了获取更好的兼容性,应该总是使用 utf8mb4 而非 utf8。
而对于 CHAR 类型数据,utf8mb4 会多消耗一些空间.
根据 Mysql 官方建议,使用 VARCHAR  替代 CHAR。
所以我们也就不需要修改 /etc/my.cnf 文件,也不需要像之前低版本那样添加字符集的设置。
[mysqld] character_set_server = utf8
[mysql] default-character-set = utf8

问题2：mysql8.0版本密码加密方式的更改：

之前默认是mysql_native_password，现在改为caching_sha2_password。
很多连接工具,像sqlyog仍然使用默认的mysql_native_password，所以在连接的时候回报错：

2058, Plugin caching——sha2_passward could not be loaded:******** ”(最后一段信息是乱码)。 


此时需要将mysql.user表中的plugin字段修改下：

ALTER USER user IDENTIFIED WITH mysql_native_password BY 'password';

ALTER USER 'admin'@'%' IDENTIFIED WITH mysql_native_password BY 'admin用户密码';


问题3：mysql连接工具连接阿里云服务器上数据库后, 还需要给服务器的安全组设置端口,默认端口为3306.


---------------------