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