一、下载mysql
官网地址：https://dev.mysql.com/downloads/mysql/5.7.html#downloads
注意事项：
- 下载的安装包必须是.rpm-bundle.tar结尾的
- 安装的时候，对应自己的下载版本号，不要直接复制命令
当前安装以版本 5.7.36为例
[zcjk@wms-gy-app1 ~]# wget https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar
二、解压安装包（mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar）
[zcjk@wms-gy-app1 ~]# mkdir mysql-5.7[zcjk@wms-gy-app1 ~]# mv mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar mysql-5.7[zcjk@wms-gy-app1 ~]# cd mysql-5.7[zcjk@wms-gy-app1 mysql-5.7]# tar xvf mysql-5.7.36-1.el7.x86_64.rpm-bundle.tar
三、卸载掉centos7自带的mariadb-lib（阿里云的服务器不带，没有此操作）
查询mariadb信息
[zcjk@wms-gy-app1 ~]# rpm -qa|grep mariadbmariadb-libs-5.5.60-1.el7_5.x86_64
使用rpe -e命令卸载
[zcjk@wms-gy-app1 ~]# sudo rpm -e mariadb-libs-5.5.56-2.el7.x86_64 --nodeps
若卸载不成功，另寻他法
四、安装mysql服务
只需要安装如下4个软件包即可，使用rpm -ivh进行安装（按顺序安装，后面的服务依赖前面的服务）
[zcjk@wms-gy-app1 mysql-5.7]# sudo rpm -ivh mysql-community-common-5.7.36-1.el7.x86_64.rpm警告：mysql-community-common-5.7.36-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中... ################################# [100%]正在升级/安装...1:mysql-community-common-5.7.36-1.e################################# [100%][zcjk@wms-gy-app1 mysql-5.7]# sudo rpm -ivh mysql-community-libs-5.7.36-1.el7.x86_64.rpm警告：mysql-community-libs-5.7.36-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中... ################################# [100%]正在升级/安装...1:mysql-community-libs-5.7.36-1.e################################# [100%][zcjk@wms-gy-app1 mysql-5.7]# sudo rpm -ivh mysql-community-client-5.7.36-1.el7.x86_64.rpm警告：mysql-community-client-5.7.36-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中... ################################# [100%]正在升级/安装...1:mysql-community-client-5.7.36-1.e################################# [100%][zcjk@wms-gy-app1 mysql-5.7]# sudo rpm -ivh mysql-community-server-5.7.36-1.el7.x86_64.rpm警告：mysql-community-server-5.7.36-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY准备中... ################################# [100%]正在升级/安装...1:mysql-community-server-5.7.36-1.e################################# [100%]
安装过程中可能出现的问题
- 缺少libaio ```shell [zcjk@wms-gy-app1 mysql-5.7]# sudo rpm -ivh mysql-community-server-5.7.36-1.el7.x86_64.rpm 警告：mysql-community-server-5.7.36-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY 错误：依赖检测失败： libaio.so.1()(64bit) 被 mysql-community-server-5.7.36-1.el7.x86_64 需要 libaio.so.1(LIBAIO_0.1)(64bit) 被 mysql-community-server-5.7.36-1.el7.x86_64 需要 libaio.so.1(LIBAIO_0.4)(64bit) 被 mysql-community-server-5.7.36-1.el7.x86_64 需要 net-tools 被 mysql-community-server-5.7.17-1.el7.x86_64 需要 解决办法： [zcjk@wms-gy-app1 mysql-5.7]# sudo yum install libaio
2. 缺少net-tools```shell[zcjk@wms-gy-app1 mysql-5.7]# sudo rpm -ivh mysql-community-server-5.7.17-1.el7.x86_64.rpm警告：mysql-community-server-5.7.17-1.el7.x86_64.rpm: 头V3 DSA/SHA1 Signature, 密钥 ID 5072e1f5: NOKEY错误：依赖检测失败：net-tools 被 mysql-community-server-5.7.17-1.el7.x86_64 需要解决办法：[zcjk@wms-gy-app1 mysql-5.7]# sudo yum install net-tools
- 缺少numactl ```shell [zcjk@wms-gy-app1 mysql-5.7]# sudo rpm -ivh mysql-community-server-5.7.36-1.el7.x86_64.rpm
报错：warning: mysql-community-server-5.7.9-1.el6.x86_64.rpm: Header V3 DSA/SHA1 Signature, key ID 5072e1f5: NOKEY error: Failed dependencies: libnuma.so.1()(64bit) is needed by mysql-community-server-5.7.9-1.el6.x86_64 libnuma.so.1(libnuma_1.1)(64bit) is needed by mysql-community-server-5.7.9-1.el6.x86_64 libnuma.so.1(libnuma_1.2)(64bit) is needed by mysql-community-server-5.7.9-1.el6.x86_64
解决办法： [zcjk@wms-gy-app1 mysql-5.7]# sudo yum install numactl
<a name="p2PId"></a>### 五、初始化数据库```shell[zcjk@wms-gy-app1 mysql-5.7]# sudo mysqld --initialize#初始化后会在/var/log/mysqld.log生成随机密码
六、修改mysql数据库目录的所属用户及其所属组，然后启动mysql数据库
[zcjk@wms-gy-app1 mysql-5.7]# sudo chown mysql:mysql /var/lib/mysql -R[zcjk@wms-gy-app1 mysql-5.7]# sudo systemctl start mysqld.service[zcjk@wms-gy-app1 mysql-5.7]# sudo systemctl status mysqld.service● mysqld.service - MySQL ServerLoaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: enabled)Active: active (running) since Tue 2022-04-26 14:07:53 CST; 37min agoDocs: man:mysqld(8)http://dev.mysql.com/doc/refman/en/using-systemd.htmlMain PID: 3457 (mysqld)Tasks: 38 (limit: 47654)Memory: 189.2MCGroup: /system.slice/mysqld.service└─3457 /usr/sbin/mysqld --daemonize --pid-file=/var/run/mysqld/mysqld.pid
七、登录mysql，并修改root用户的密码（系统强制要求，否则不能操作mysql）
初始登录密码可在/var/log/mysqld.log文件中找到
[zcjk@wms-gy-app1 mysql-5.7]# mysql -uroot -p'-4iq<tyjVpLb'mysql: [Warning] Using a password on the command line interface can be insecure.Welcome to the MySQL monitor. Commands end with ; or \g.Your MySQL connection id is 2Server version: 5.7.26Copyright (c) 2000, 2018, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> set password=password('234DFS32#@!!!');Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.01 sec)mysql> show databases;+--------------------+| Database |+--------------------+| information_schema || mysql || performance_schema || sys |+--------------------+4 rows in set (0.00 sec)
八、创建Database和wms操作用户
创建数据库 pro_wms_XXXX；
mysql> create database pro_wms_xxx;
创建操作用户，并给用户赋权限； ```sql mysql> create user ‘pro_wms_xxxx_app’@’localhost’ identified by ‘pro_wms_xxxx_appCS*$‘;
mysql> select host,user from user; —查看当前系统里用户 +—————-+—————————+ | host | user | +—————-+—————————+ | localhost | mysql.session | | localhost | mysql.sys | | localhost | pro_wms_xxxx_app | | localhost | root | +—————-+—————————+ 5 rows in set (0.00 sec)
mysql> grant create, alter, drop, references, index, select, insert, update, delete on pro_wms_xxxx.* to pro_wms_xxxx_app@’localhost’;
mysql> flush privileges;
mysql> show grants for pro_wms_xxxx_app@’localhost’;
+——————————————————————————————————————————————————————————————————+
| Grants for pro_wms_xxxx_app@localhost |
+——————————————————————————————————————————————————————————————————+
| GRANT USAGE ON . TO ‘pro_wms_xxxx_app’@’localhost’ |
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, REFERENCES, INDEX, ALTER ON pro_wms_xxxx.* TO ‘pro_wms_xxxx_app’@’localhost’ |
+——————————————————————————————————————————————————————————————————+
2 rows in set (0.00 sec)
<a name="iVEid"></a>### 九、注意无法使用的问题<a name="yzhvY"></a>#### (1) init_connect设置问题导致 非root用户赋权后无法查看databases;通过mysql的管理员账户进行设置```sqlmysql> set session sql_safe_updates=0;Query OK, 0 rows affected (0.00 sec)mysql> set global init_connect='set session sql_safe_updates=0';Query OK, 0 rows affected (0.00 sec)mysql> show variables like 'init_connect';+---------------+--------------------------------+| Variable_name | Value |+---------------+--------------------------------+| init_connect | set session sql_safe_updates=0 |+---------------+--------------------------------+1 row in set (0.00 sec)mysql> show variables like 'sql_safe_updates';+------------------+-------+| Variable_name | Value |+------------------+-------+| sql_safe_updates | OFF |+------------------+-------+1 row in set (0.01 sec)
也可以通过修改 my.cnf文件设置一些参数，保存后需要重启mysql服务
[zcjk@wms-gy-app1 mysql-5.7]# sudo vim /etc/my.cnf...[mysqld]#字符集设置character-set-server=utf8#大小写不敏感lower_case_table_names=1init_connect='set session sql_safe_updates=0'...[clinet]
(2) 注意 SSL的证书验证权限，需要关闭验证SSL证书，否则无法登陆数据库
方法：
用“SHOW VARIABLES LIKE’%ssl%’;”查看ssl状态；
mysql> SHOW VARIABLES LIKE'%ssl%';+---------------+-----------------+| Variable_name | Value |+---------------+-----------------+| have_openssl | YES || have_ssl | YES || ssl_ca | ca.pem || ssl_capath | || ssl_cert | server-cert.pem || ssl_cipher | || ssl_crl | || ssl_crlpath | || ssl_key | server-key.pem |+---------------+-----------------+9 rows in set (0.00 sec)
修改“my.cnf”文件，在“[mysqld]”中添加“skip_ssl”。切记不要放到别的标签下如“[clinet]” 否则无法关闭验证； ```shell [zcjk@wms-gy-app1 mysql-5.7]# sudo vim /etc/my.cnf
… [mysqld] skip_ssl
字符集设置
character-set-server=utf8
大小写不敏感
lower_case_table_names=1 init_connect=’set session sql_safe_updates=0’ … [clinet]
- 用“service mysqld restart”重启mysql即可。```shell[zcjk@wms-gy-app1 mysql-5.7]# sudo service mysqld restart[sudo] zcjk 的密码：Redirecting to /bin/systemctl restart mysqld.service[zcjk@wms-gy-app1 mysql-5.7]#
mysql> SHOW VARIABLES LIKE'%ssl%';+---------------+----------+| Variable_name | Value |+---------------+----------+| have_openssl | DISABLED || have_ssl | DISABLED || ssl_ca | || ssl_capath | || ssl_cert | || ssl_cipher | || ssl_crl | || ssl_crlpath | || ssl_key | |+---------------+----------+9 rows in set (0.00 sec)
(3) mysql5.7以上默认开启了only_full_group_by 模式
解决办法：
1、查看sql_mode
mysql> SELECT @@sql_mode;
2、重新设置值，去掉ONLY_FULL_GROUP_BY
mysql> SET @@global.sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
3、上面是改变了全局sql_mode，对于新建的数据库有效。对于已存在的数据库，则需要在对应的数据库下执行以下语句
mysql> SET sql_mode ='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION';
4、或是直接修改“my.cnf”文件，在“[mysqld]”中添加 sql_mode 配置，以免重启后失效
...[mysqld]sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION...
若有收获，就点个赞吧
0 人点赞
