一、MySQL安装包下载
- 官网地址:https://dev.mysql.com/downloads/mysql/
- 下载步骤:
- 下载后,上传并md5校验安装包是否与上图官方提供的值一致,确保传输过程安装包无损害
[root@MyDB1 ~]# cd /usr/local/software/
[root@MyDB1 software]# md5sum mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar
22b8dbf57a6bb0dc31fc1eb65c4a08be mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar
- 解压安装包
[root@MyDB1 software]# tar -xvf mysql-8.0.22-1.el7.x86_64.rpm-bundle.tar
二、MySQL卸载
注:在卸载旧的MySQL之前,注意备份数据
[root@MyDB1 ~]# rpm -qa|grep mysql #查看是否已安装mysql数据库
[root@MyDB1 ~]# rpm -qa|grep mysql|xargs rpm -e --nodeps #卸载mysql
[root@MyDB1 software]# rpm -qa|grep mariadb-libs|xargs rpm -e --nodeps #卸载mariadb
三、MySQL的rpm安装
注:安装顺序如下,否则会报“依赖”错误
[root@MyDB1 software]# rpm -ivh mysql-community-common-8.0.22-1.el7.x86_64.rpm
[root@MyDB1 software]# rpm -ivh mysql-community-client-plugins-8.0.22-1.el7.x86_64.rpm
[root@MyDB1 software]# rpm -ivh mysql-community-libs-8.0.22-1.el7.x86_64.rpm
[root@MyDB1 software]# rpm -ivh mysql-community-client-8.0.22-1.el7.x86_64.rpm
[root@MyDB1 software]# yum -y install openssl-devel #解决devel所需的依赖
[root@MyDB1 software]# rpm -ivh mysql-community-devel-8.0.22-1.el7.x86_64.rpm
[root@MyDB1 software]# rpm -ivh mysql-community-server-8.0.22-1.el7.x86_64.rpm
四、MySQL启动服务
[root@MyDB1 log]# systemctl start mysqld.service
[root@MyDB1 log]# systemctl status mysqld.service
注:mysql默认端口是3306,注意可能会因端口冲突,导致启动失败!
五、MySQL相关文件路径
路径 | 功能 |
---|---|
/var/lib/mysql | 数据文件位置(包含binlog日志) |
/etc/my.cnf | 配置文件位置 |
/var/lib/mysql/mysql.sock | sock文件位置(用于client连接) |
/var/log/mysqld.log | 日志文件位置 |
/var/run/mysqld/mysqld.pid | 进程文件位置 |
/usr/sbin/mysqld | 服务端启动二进制文件位置 |
/usr/bin/mysql | 客户端访问二进制文件位置 |
六、修改root初始密码
- 安装MySQL成功,在启动服务时,会生成root的初始密码(临时),可以从日志文件中找到
- 使用临时密码登录
[root@MyDB1 ~]# mysql -uroot -p
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 8.0.22
Copyright (c) 2000, 2020, 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>
- 修改root密码
mysql> alter user root@'localhost' identified by 'MyDB12@com';
Query OK, 0 rows affected (0.00 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.00 sec)
注:MySQL8密码必须符合一定复杂度,否则无法修改;退出当前会话后,重启登录生效!
七、配置mysql远程登录
- 关闭防火墙或开放MySQL端口
- 查看允许访问MySQL的用户和地址
mysql> select user,host from mysql.user;
+------------------+-----------+
| user | host |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session | localhost |
| mysql.sys | localhost |
| root | localhost |
+------------------+-----------+
4 rows in set (0.00 sec)
- 遇到的问题
mysql> grant all privileges on *.* to root@'%' identified by 'MyDB12@com';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use
near 'identified by 'MyDB12@com'' at line 1
注:该错误并不是语法错误,是因为mysql该版本不支持直接创建用户和赋权,而需要分别实现
- 创建远程登录用户
mysql> create user 'root'@'%' identified by 'MyDB12@com';
Query OK, 0 rows affected (0.01 sec)
- 赋权
mysql> grant all privileges on *.* to 'root'@'%';
Query OK, 0 rows affected (0.01 sec)
mysql> flush privileges;
Query OK, 0 rows affected (0.01 sec)
八、修改mysql配置文件
- 修改并重启
[root@MyDB1 ~]# vi /etc/my.cnf
[root@MyDB1 ~]# systemctl restart mysqld.service
- 追加如下内容:
[mysqld]
character-set-server=utf8 #服务端字符集
lower_case_table_names=0 #是否区分表名大小写;1:不区分 0区分
max_connections=1000 #最大连接数
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION' #解决group by语句的校验问题
[mysql]
default-character-set=utf8 #客户端字符集
[client]
default-character-set=utf8
- 遇到的问题:无法修改“lower_case_table_names=1”,否则启动报错!
- 原因:mysql8的rpm安装默认是“0”,所以对应数据文件也是“0”。
- 解决方案:rpm方式无法修改,只有编译安装方式可以在初始化时设置
- 启动错误日志如下:
[ERROR] [MY-011087] [Server] Different lower_case_table_names settings for server ('1') and data dictionary ('0').
- 注释:mysql的参数有很多,上面参数仅仅涉及到开发中可能遇到的问题,其他的参数需根据实际项目调整!