Mysql-8.0.34 tar包单机安装

Mysql-8.0.34 tar包单机安装

---

1-卸载旧版本的MySql

查看旧版本MySql

#-i 忽略大小写
[root@mysql-m ~]# rpm -qai | grep mysql
libmysqlclient18-10.0.40.1-2.9.1.x86_64
perl-DBD-mysql-4.021-12.5.2.x86_64

逐个删除掉旧的组件

#使用命令rpm -e --nodeps *进行移除操作
#移除的时候可能会有依赖，要注意一定的顺序
[root@mysql-m ~]# rpm -e --nodeps libmysqlclient18-10.0.40.1-2.9.1.x86_64
[root@mysql-m ~]# rpm -e --nodeps perl-DBD-mysql-4.021-12.5.2.x86_64
[root@mysql-m ~]# rpm -qa | grep mysql

卸载postfix和mariadb

[root@mysql-m ~]# rpm -qa | grep postfix
postfix-2.10.1-7.el7.x86_64
[root@mysql8 ~]# rpm -e postfix-2.10.1-7.el7.x86_64

[root@mysql-m ~]# rpm -qa | grep mariadb
mariadb-libs-5.5.60-1.el7_5.x86_64
[root@mysql-m ~]# rpm -e --nodeps mariadb-libs-5.5.44-2.el7.centos.x86_64 						#查询出的版本

删除相关目录及文件

#查询所有Mysql对应的文件夹
[root@mysql-m ~]# whereis mysql
mysql: /usr/bin/mysql /usr/include/mysql
[root@mysql-m ~]# find / -name mysql
/data/mysql
/data/mysql/mysql

#删除相关目录或文件
[root@mysql-m ~]#  rm -rf /usr/bin/mysql /usr/include/mysql

#验证是否删除完毕
[root@mysql-m ~]# whereis mysql
mysql:
[root@mysql-m ~]# find / -name mysql
[root@mysql-m ~]# 

#删除my.cnf
[root@mysql-m ~]# rm -rf /etc/my.cnf

2-安装mysql

下载安装包

[root@mysql ~]# wget https://dev.mysql.com/get/Downloads/MySQL-8.0/mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz
[root@mysql ~]# ll
-rw-r--r-- 1 root root 617598500 Nov 14 15:43 mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz

解压并改名

#解压xz格式文件使用-Jxf
[root@mysql ~]# tar Jxvf mysql-8.0.34-linux-glibc2.12-x86_64.tar.xz -C /opt/apps
[root@mysql ~]# cd /opt/apps/
[root@mysql apps]# mv mysql-8.0.34-linux-glibc2.12-x86_64/ mysql-8.0.34
[root@mysql apps]# ln -s mysql-8.0.34/ mysql

创建mysql组和用户

[root@mysql apps]# groupadd mysql
[root@mysql apps]# useradd -r -g mysql mysql

创建存储数据的 data 目录和日志 logs 目录

[root@mysql apps]# mkdir -p /data/mysql
[root@mysql apps]# mkdir -p /data/mysql/data
[root@mysql apps]# mkdir -p /data/mysql/logs

生成日志文件

[root@mysql apps]# echo "" > /data/mysql/logs/mysql_error.log

创建配置文件

[root@mysql apps]# touch /etc/my.cnf

数据目录赋予权限

[root@mysql apps]# chown -R mysql:mysql /data/mysql
[root@mysql apps]# chown -R mysql:mysql /opt/apps/mysql-8.0.34
[root@mysql apps]# chown -R mysql:mysql /etc/my.cnf
[root@mysql apps]# chmod 644 /etc/my.cnf

编辑mysql服务的配置文件

[root@mysql apps]# vim /etc/my.cnf
#添加：
[client]
port = 3306
 
#根据实际情况调整mysql.sock配置
socket = /data/mysql/data/mysql.sock
 
[mysqld]
#Mysql服务的唯一编号 每个mysql服务Id需唯一
server-id = 1
 
#服务端口号 默认3306
port = 3306
 
#mysql安装根目录
basedir = /opt/apps/mysql
 
#mysql数据文件所在位置
datadir = /data/mysql/data
 
#pid
pid-file = /data/mysql/data/mysql.pid
 
#设置socke文件所在目录
socket = /data/mysql/data/mysql.sock
 
#错误日志
log_error=/data/mysql/logs/mysql_error.log
 
#设置认证插件
default_authentication_plugin=mysql_native_password
 
#设置sqlmode(根据需求自定义)
sql_mode=STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION
 
#数据库默认字符集,主流字符集支持一些特殊表情符号（特殊表情符占用4个字节）
character-set-server = utf8mb4
 
#数据库字符集对应一些排序等规则，注意要和character-set-server对应
collation-server = utf8mb4_general_ci
 
#设置client连接mysql时的字符集,防止乱码
init_connect='SET NAMES utf8mb4'
 
#是否对sql语句大小写敏感，1表示不敏感
lower_case_table_names = 1
 
#最大连接数
max_connections = 16000
 
#最大错误连接数
max_connect_errors = 1000
 
#TIMESTAMP如果没有显示声明NOT NULL，允许NULL值
explicit_defaults_for_timestamp = true
 
#SQL数据包发送的大小，如果有BLOB对象建议修改成1G
max_allowed_packet = 1G
 
#内部内存临时表的最大值 ，设置成128M。
#比如大数据量的group by ,order by时可能用到临时表，
#超过了这个值将写入磁盘，系统IO压力增大
tmp_table_size = 134217728
max_heap_table_size = 134217728
 
#mysql binlog日志文件保存的过期时间，过期后自动删除
expire_logs_days = 5

# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB

初始化数据库，并会自动生成随机密码

[root@mysql apps]# cd mysql/
[root@mysql mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/opt/apps/mysql --datadir=/data/mysql/data

[Server] --character-set-server: 'utf8' is currently an alias for the character 
set UTF8MB3, but will be an alias for UTF8MB4 in a future release. Please consider using UTF8MB4 in order to be unambiguous.
[Server] A temporary password is generated for root@localhost: 7<Sear.UP*Qx	     # 密码

如没有弹出日志，手动查看密码

[root@hhht-7 mysql]# tail -200 /data/mysql/logs/mysql_error.log | grep password
[Server] A temporary password is generated for root@localhost: 7<Sear.UP*Qx		# 密码
[Warning] [MY-013360] [Server] Plugin mysql_native_password reported: 
''mysql_native_password' is deprecated and will be removed in a future release. 
Please use caching_sha2_password instead'

初始化数据库时候报错

#错误为：
bin/mysqld: error while loading shared libraries: libaio.so.1: cannot open shared object file: No such file or directory

#解决方法为：
yum install libaio-devel.x86_64                         #原因是缺少了libaio

初始化数据库，使之忽略大小写 参考

[root@mysql8 mysql]# ./bin/mysqld --initialize --user=mysql --basedir=/opt/apps/mysql --datadir=/data/mysql/data --lower-case-table-names=1

3-系统服务启停及开机自启

添加msyql到系统服务

[root@mysql8 mysql]# vim ./support-files/mysql.server
#修改：
basedir=/opt/apps/mysql
datadir=/data/mysql/data

复制mysql.server脚本到资源目录，并赋予执行权限

[root@mysql8 mysql]# cp ./support-files/mysql.server /etc/rc.d/init.d/mysql
[root@mysql8 mysql]# chmod +x /etc/rc.d/init.d/mysql

注册服务开机自启

[root@mysql8 mysql]# chkconfig --add mysql

#查看开机自启动是否成功
[root@mysql8 mysql]# chkconfig --list mysql
mysql          	0:关	1:关	2:开	3:开	4:开	5:开	6:关

将mysql命令添加到服务

[root@mysql8 mysql]# ln -s /opt/apps/mysql/bin/mysql /usr/bin

4-启动服务

服务启动

[root@hhht-8 mysql]# systemctl start mysql

#查看服务状态
[root@hhht-8 mysql]# systemctl status mysql
● mysql.service - LSB: start and stop MySQL
   Loaded: loaded (/etc/rc.d/init.d/mysql; bad; vendor preset: disabled)
   Active: active (running) since Tue 2023-11-14 16:15:03 CST; 1s ago		# running

路径启动 参考

[root@mysql8 mysql]# cd /opt/apps/mysql/bin/
./mysqld --defaults-file=/etc/my.cnf --user=mysql
或
./mysqld_safe --defaults-file=/etc/my.cnf --user=mysql &

5-启动可能的几种报错信息

1）Starting MySQL. ERROR! The server quit without updating PID file (/data/mysql/data/VM-0-16-centos.pid).

#编辑mysqld文件
[root@mysql8 mysql]# vim /etc/rc.d/init.d/mysqld
#找到start模块，添加--user=root到mysqld_safe 后面即可

#或者授权目录权限
[root@mysql8 mysql]# chown -R mysql:mysql /data/mysql/

2）在 Red Hat Enterprise Linux Server release 7.9 (Maipo) 中提示如下信息：
/etc/init.d/mysqld: line 239: my_print_defaults: command not found
/etc/init.d/mysqld: line 259: cd: /usr/local/mysql: No such file or directory
Starting MySQLCouldn’t find MySQL server (/usr/local/mysql/[FAILED]ld_safe)

#1-辑mysqld文件，修改 basedir 和 datadir 的路径
[root@mysql8 mysql]# vim /etc/rc.d/init.d/mysqld
basedir=/opt/apps/mysql
bindir=/opt/apps/mysql/bin
datadir=/data/mysql/data
sbindir=/opt/apps/mysql/bin
libexecdir=/opt/apps/mysql/bin

#2-找到start模块，添加--user=root到mysqld_safe 后面即可

6-配置环境变量

修改变量文件

[root@mysql8 mysql]# vim /etc/profile
#末行添加：
MYSQL_HOME=/opt/apps/mysql
export PATH=$PATH:$MYSQL_HOME/bin

使得生效

[root@mysql8 mysql]# source /etc/profile

7-登录mysql并修改密码

登录MySQL

[root@mysql support-files]# mysql -uroot -p
Enter password:                                         #随机密码

修改密码

mysql> ALTER USER 'root'@'localhost' IDENTIFIED BY 'Jd2019';
或者
mysql> ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Jd2019';

mysql> flush privileges;

8-创建mysql用户

#创建用户
mysql> CREATE USER 'mysql'@'%' IDENTIFIED BY 'admin@123';

#授权远程登录
mysql>  ALTER USER 'mysql'@'%' IDENTIFIED BY 'admin@123' PASSWORD EXPIRE NEVER;

#授权新建用户可以远程登陆，并解决密码问题
mysql> ALTER USER 'mysql'@'%' IDENTIFIED WITH mysql_native_password BY 'admin@123';

#授权普户mysql可以创建数据库(授权MySQL有所有权限)
mysql>  grant all privileges on *.* to 'mysql'@'%';

#立即生效
mysql> flush privileges; 

#查看用户
mysql> select user,host from mysql.user;
+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql            | %         |
| slave            | %         |
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

9-授权

10-root远程登录

查看要修改用户的权限

[root@mysql ~]# mysql -uroot -p
mysql> select user,host from mysql.user;

+------------------+-----------+
| user             | host      |
+------------------+-----------+
| mysql.infoschema | localhost |
| mysql.session    | localhost |
| mysql.sys        | localhost |
| root             | localhost |
+------------------+-----------+

创建root用户，host为%(全部)

mysql> CREATE USER 'root'@'%' IDENTIFIED BY 'Jd2019';

授权远程登录

mysql> ALTER USER 'root'@'%' IDENTIFIED BY 'Jd2019' PASSWORD EXPIRE NEVER;
Query OK, 0 rows affected (0.00 sec)

mysql> ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'Jd2019';
Query OK, 0 rows affected (0.00 sec)

mysql> flush privileges; 
Query OK, 0 rows affected (0.00 sec)

#这样就可以使用root远程登录了

11-创建数据库

mysql> create database east_nrt_info;                         #数据库名east_nrt_info

mysql> flush privileges;
mysql> exit

12-查看数据库是否忽略大小写

登录数据库查看

mysql>  show variables like '%case%';
+------------------------+-------+
| Variable_name          | Value |
+------------------------+-------+
| lower_case_file_system | OFF   |
| lower_case_table_names | 1     |
+------------------------+-------+

#显示1就是已经忽略大小写

13-my.cnf配置参考

[root@mysql ~]# vim /etc/my.cnf
#清空添加如下：
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8mb4
[client]
port       = 3306
socket     = /tmp/mysql.sock
 
[mysqld]
port       = 3306
server-id  = 3306
user       = mysql
socket     = /tmp/mysql.sock
# 设置mysql的安装目录
basedir    = /opt/apps/mysql-8.0.19
# 设置mysql数据库的数据的存放目录
datadir    = /data/mysql/
log-bin    = /data/mysql/logs
innodb_data_home_dir      =/data/mysql/
innodb_log_group_home_dir =/data/mysql/
#设置mysql数据库的日志及进程数据的存放目录
log-error =/data/mysql/logs/
pid-file  =/data/mysql/mysql.pid
# 服务端使用的字符集默认为8比特编码
character-set-server=utf8mb4
lower_case_table_names=1
autocommit =1

skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 1M
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 8K
read_buffer_size = 4M
read_rnd_buffer_size = 512K
myisam_sort_buffer_size = 64M
thread_cache_size = 128
#query_cache_size = 128M
tmp_table_size = 128M
explicit_defaults_for_timestamp = true
max_connections = 500
max_connect_errors = 100
open_files_limit = 65535
 
binlog_format=mixed
  
binlog_expire_logs_seconds =864000
# 创建新表时将使用的默认存储引擎
default_storage_engine = InnoDB
innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 256M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
transaction-isolation=READ-COMMITTED
 
[mysqldump]
quick
max_allowed_packet = 16M
 
[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M
 
[mysqlhotcopy]
interactive-timeout