具体参考:
https://blog.csdn.net/qq_25340149/article/details/84307547#_3
安装
安装环境:CentOS 7+,mysql 8.0.13
官网下载网址:https://dev.mysql.com/downloads/repo/yum/
下载:wget https://dev.mysql.com/get/mysql80-community-release-el7-1.noarch.rpm
安装步骤:
rpm -qa | grep mysql //查询是否存在自带的mysql版本
如果存在,则进行下面的代码操作,如果不存在,可省略...
rpm -e mysql //普通删除模式
rpm -e --nodeps mysql //强力删除模式,如果使用上面命令删除时,提示有依赖的其它文件,则用该命令可以对其进行强力删除
解压并安装mysql服务:
rpm -ivh mysql-community-release-el7-5.noarch.rpm
yum update
yum install mysql-server
初始化mysql. mysqld --initialize
启动mysql. systemctl start mysqld
在启动时,发生错误:
[root@roothome mysql]# systemctl start mysqld
Job for mysqld.service failed because the control process exited with error code. See "systemctl status mysqld.service" and "journalctl -xe" for details.
//查看mysql 的启动日志,查看到底问题出在哪
[root@roothome usr]# cat /var/log/mysqld.log
2018-11-19T09:12:26.121019Z 0 [System] [MY-010116] [Server] /usr/sbin/mysqld (mysqld 8.0.13) starting as process 22972
2018-11-19T09:12:26.132212Z 1 [ERROR] [MY-012271] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2018-11-19T09:12:26.132251Z 1 [ERROR] [MY-012278] [InnoDB] The innodb_system data file 'ibdata1' must be writable
2018-11-19T09:12:26.132275Z 1 [ERROR] [MY-010334] [Server] Failed to initialize DD Storage Engine
2018-11-19T09:12:26.132469Z 0 [ERROR] [MY-010020] [Server] Data Dictionary initialization failed.
2018-11-19T09:12:26.132499Z 0 [ERROR] [MY-010119] [Server] Aborting
2018-11-19T09:12:26.133307Z 0 [System] [MY-010910] [Server] /usr/sbin/mysqld: Shutdown complete (mysqld 8.0.13) MySQL Community Server - GPL.
由上面的异常可以看到data file ‘ibdata1’ must be writable说明当前是不可写的数据文件,看来是因为权限不足,通过以下命令授权。
[root@roothome ~]# chown -R mysql:mysql /var/lib/mysql
然后再次尝试启动:
[root@roothome ~]# systemctl start mysqld
这次在没有出现错误的问题。证明启动成功了!
接下来查看一下状态:
[root@roothome ~]# systemctl status mysqld
● mysqld.service - MySQL Server
Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
Active: active (running) since 二 2018-11-20 09:56:55 CST; 4min 15s ago
Docs: man:mysqld(8)
http://dev.mysql.com/doc/refman/en/using-systemd.html
Process: 30903 ExecStartPre=/usr/bin/mysqld_pre_systemd (code=exited, status=0/SUCCESS)
Main PID: 30924 (mysqld)
Status: "SERVER_OPERATING"
Tasks: 37
Memory: 381.4M
CGroup: /system.slice/mysqld.service
└─30924 /usr/sbin/mysqld
11月 20 09:56:51 roothome systemd[1]: Starting MySQL Server...
11月 20 09:56:55 roothome systemd[1]: Started MySQL Server.
可以看出,mysql启动成功了。
然后开始登陆mysql,由于第一次登陆,root密码为空,所以要去修改更新一次密码:
mysqladmin -u root password "root"
这里会报错:
mysqladmin: connect to server at 'localhost' failed
error: 'Access denied for user 'root'@'localhost' (using password: NO)'
配置
这里的问题是地址访问异常,这个地址不能进入mysql。
具体的解决办法:
[root@roothome bin]# vim /etc/my.cnf
....
[mysqld]
skip-grant-tables //在[mysqld]下面添加此参数
bind-address = 0.0.0.0 //允许远程访问
default-authentication-plugin=mysql_native_password //把前面的#号去掉,设置加密格式
....
在忽略密码模式下进入mysql:
[root@roothome bin]# mysql -u root -p
接着回车,进入:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.13 MySQL Community Server - GPL
Copyright (c) 2000, 2018, 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> FLUSH PRIVILEGES;//刷新权限 先执行一次 可以防止下一步出现异常
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY '你的密码';//修改你的root密码
mysql> FLUSH PRIVILEGES;//修改成功后,刷新权限
mysql> exit;
退出然后进入到my.cnf文件中再把skip-grant-tables注释掉;然后重启;再次使用
[root@roothome bin]# mysql -u root -p
Enter password: //在此输入你更新的密码
//注意,由于当前版本不支持root用户被远程连接,所以在此需要创建一个新用户来进行远程的连接。
//创建新用户,如果在配置文件中没有开启default-authentication-plugin=mysql_native_password,则要遵守mysql8.0以上的密码策略,限制必须要大小写加数字特殊符号。
//创建新用户,其中%表示所有的地址都可以访问。如果要指定专门的地址则可以是@‘自定义ip’。
mysql>CREATE USER 'test'@'%' IDENTIFIED BY 'test.123';
//给当前用户授权远程访问
mysql>GRANT ALL PRIVILEGES ON *.* TO 'test'@'%' WITH GRANT OPTION;
//刷新权限
mysql>FLUSH PRIVILEGES;
//如果在远程时出现字符集不匹配的情况,如下:
//Unable to load authentication plugin 'caching_sha2_password'.
mysql>ALTER USER 'test'@'%' IDENTIFIED WITH mysql_native_password BY 'test.123';
###高版本mysql 分组查询报ONLY_FULL_GROUP_BY错误
vi /etc/my.cnf
添加如下代码
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION