[MySQL] 安装与配置

1.官方安装文档：
https://dev.mysql.com/doc/mysql-yum-repo-q...
2.镜像下载地址：
http://mirrors.sohu.com/mysql/MySQL-5.7/
3.查询是否已经有mysql：
如果有，必须先删除

rpm -qa | grep -i mysql
yum remove -y mysql*
rpm -qa | grep -i mysql

方式1：通过rpm下载安装

下载以下安装包：
client, common, libs, server

http://mirrors.sohu.com/mysql/MySQL-5.7/my...
http://mirrors.sohu.com/mysql/MySQL-5.7/my...
http://mirrors.sohu.com/mysql/MySQL-5.7/my...
http://mirrors.sohu.com/mysql/MySQL-5.7/my...
要按以下顺序安装，否则出错

wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-common-5.7.26-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-libs-5.7.26-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-client-5.7.26-1.el7.x86_64.rpm
wget http://mirrors.sohu.com/mysql/MySQL-5.7/mysql-community-server-5.7.26-1.el7.x86_64.rpm

rpm -ivh mysql-community-common-5.7.26-1.el7.x86_64.rpm  mysql-community-common-5.7.26-1.el7.x86_64.rpm mysql-community-common-5.7.26-1.el7.x86_64.rpm mysql-community-common-5.7.26-1.el7.x86_64.rpm

ROM Bundle相关包解释

mysql-community-libs-compat-8.0.15-1.el6.x86_64.rpm MySQL之前版本的共享兼容库
mysql-community-test-8.0.15-1.el6.x86_64.rpm MySQL服务端的测试组件
mysql-community-devel-8.0.15-1.el6.x86_64.rpm MySQL数据库客户端应用程序的开发头文件和库
mysql-community-common-8.0.15-1.el6.x86_64.rpm 服务端和客户端的公共文件
mysql-community-libs-8.0.15-1.el6.x86_64.rpm 客户端共享库
mysql-community-client-8.0.15-1.el6.x86_64.rpm 客户端及相关工具
mysql-community-server-8.0.15-1.el6.x86_64.rpm 服务端及相关工具

安装 mysql-community-common-5.7.26-1.el7.x86_64.rpm 过程中报错：

缺少 libaio.so.1:

rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm
warning: mysql-community-server-5.7.26-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-5.7.26-1.el7.x86_64
        libaio.so.1(LIBAIO_0.1)(64bit) is needed by mysql-community-server-5.7.26-1.el7.x86_64
        libaio.so.1(LIBAIO_0.4)(64bit) is needed by mysql-community-server-5.7.26-1.el7.x86_64
        mysql-community-client(x86-64) >= 5.7.9 is needed by mysql-community-server-5.7.26-1.el7.x86_64

解决：
去centos库里边下载缺少的依赖
http://mirror.centos.org/centos/7/os/x86_6...

 wget http://mirror.centos.org/centos/7/os/x86_64/Packages/libaio-0.3.109-13.el7.x86_64.rpm
 rpm -ivh libaio-0.3.109-13.el7.x86_64.rpm
 rpm -ivh mysql-community-server-5.7.26-1.el7.x86_64.rpm

原因：
32位系统现在很少了，yum似乎默认安装32位的

启动mysql

systemctl start mysqld.service

查看mysql 状态

[root@iZbp12ft4a1zgzzaoq9xgrZ ~]#  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-08-02 10:48:20 CST; 3min 19s ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 30783 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 30709 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 30787 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─30787 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Aug 02 10:48:16 iZbp12ft4a1zgzzaoq9xgrZ systemd[1]: Starting MySQL Server...
Aug 02 10:48:20 iZbp12ft4a1zgzzaoq9xgrZ systemd[1]: Started MySQL Server.
[root@iZbp12ft4a1zgzzaoq9xgrZ ~]#

Aug 02 10:48:20 iZbp12ft4a1zgzzaoq9xgrZ systemd[1]: Started MySQL Server.
[root@iZbp12ft4a1zgzzaoq9xgrZ ~]# 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-08-02 10:48:20 CST; 6min ago
     Docs: man:mysqld(8)
           http://dev.mysql.com/doc/refman/en/using-systemd.html
  Process: 30783 ExecStart=/usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid $MYSQLD_OPTS (code=exited, status=0/SUCCESS)
  Process: 30709 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
 Main PID: 30787 (mysqld)
   CGroup: /system.slice/mysqld.service
           └─30787 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

Aug 02 10:48:16 iZbp12ft4a1zgzzaoq9xgrZ systemd[1]: Starting MySQL Server...
Aug 02 10:48:20 iZbp12ft4a1zgzzaoq9xgrZ systemd[1]: Started MySQL Server.
[root@iZbp12ft4a1zgzzaoq9xgrZ ~]#

查看临时密码

[root@iZbp12ft4a1zgzzaoq9xgrZ ~]# grep 'temporary password' /var/log/mysqld.log
2019-08-02T02:48:18.053602Z 1 [Note] A temporary password is generated for root@localhost: DaelA_:Y#6&x
[root@iZbp12ft4a1zgzzaoq9xgrZ ~]#

密码必须包含 符号 + 大小写 + 数字

mysql  -uroot  -p

ALTER USER 'root'@'localhost' IDENTIFIED BY 'MyNewPass4!'

查看当前用户

SELECT CURRENT_USER();

授权

授权所有所有数据库以及所有访问权限

GRANT ALL ON *.* TO 'root'@'%' IDENTIFIED BY '密码'';

无法插入中文 报错:Incorrect string value: '\xE7\x8F' for column 'name' at row 1

分析

show variables like 'char%';

发现 character_set_server 的编码不是utf8mb4

show create table user;

CREATE TABLE `user` (
  `id` varchar(64) NOT NULL COMMENT '主键id',
  `username` varchar(32) DEFAULT NULL COMMENT '姓名',
  `role_no` int(11) DEFAULT NULL COMMENT '角色编号',
  `role_name` varchar(32) DEFAULT NULL COMMENT '角色名称',
  `mobile` bigint(20) DEFAULT NULL COMMENT '手机号码',
  `remark` varchar(200) DEFAULT NULL COMMENT '备注',
  PRIMARY KEY (`id`),
  UNIQUE KEY `user_id_uindex` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1

发现 默认的建表用的是latin1

解决1:针对该表

ALTER TABLE 表名 DEFAULT CHARSET utf8 collate utf8_general_ci ; 

解决2:针对该数据库

创建数据库并指定编码和校验集

ALTER DATABASE 数据库名 DEFAULT CHARACTER SET utf8 collate utf8_general_ci ; 

解决3:统一修改默认配置 一劳永逸

[root@iZbp12ft4a1zgzzaoq9xgrZ ~]# ps axu | grep mysql
root     15144  0.0  0.0 112708   980 pts/0    S+   11:31   0:00 grep --color=auto mysql
mysql    30787  0.0  5.6 1603084 220688 ?      Sl   Aug02   1:58 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid

[root@iZbp12ft4a1zgzzaoq9xgrZ ~]# vim /etc/my.cnf

character-set-server=utf8mb4
character-set-database=utf8mb4
character-set-system=uft8mb4

systemctl restart mysqld

然而，发现只要修改character-set-database=utf8mb4就出现问题无法启动
网上说需要升级成8.0....
参考:
https://www.jianshu.com/p/65c526cd7572

本作品采用《CC 协议》，转载必须注明作者和本文链接