鉴于平时使用MySQL都是5.7版本,且均为yum方式安装。而昨日偶得机会,遂尝试以二进制方式安装MySQL8.0,并编写脚本以方便使用。
环境
CentOS 7.9
MySQL 8.0.28
上传文件到服务器
本实例的文件路径为/home/package,无此路径则自行创建,上传mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz二进制安装包至此目录
mkdir /home/package
编写开机自启脚本
预置工具
# 安装libaio防止报错
yum install -y libaio
# yum install -y libnuma* 可选项
mysql3306.service为自启动服务文件名称,若修改请将文章中所有mysql3306.service名称替换为自己需要的
vim mysql3306.service
输入如下命令
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
Type=simple
# Disable service start and stop timeout logic of systemd for mysqld service.
TimeoutSec=0
# Execute pre and post scripts as root
PermissionsStartOnly=true
# Needed to create system tables
# ExecStartPre=/usr/bin/mysqld_pre_systemd
# Start main service
ExecStart=/usr/local/mysql/bin/mysqld_safe \
--defaults-file=/data/mysql/default/mysql.cnf \
--datadir=/data/mysql/default/data \
--user=mysql
# Use this to switch malloc implementation
# EnvironmentFile=-/etc/sysconfig/mysql
# Sets open_files_limit
LimitNOFILE = 65565
Restart=on-failure
Restart=always
RestartSec=5
RestartPreventExitStatus=1
# Set enviroment variable MYSQLD_PARENT_PID. This is required for restart.
Environment=MYSQLD_PARENT_PID=1
PrivateTmp=false
配置文件脚本
本实例中名称为mysql.cnf,若修改请将文章中所有mysql.cnf名称替换为自己需要的
mysql.cnf
[mysqld]
pid-file = /tmp/mysqld.pid
socket = /tmp/mysql.sock
datadir = /data/mysql/default/data
secure-file-priv= NULL
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0
server-id=1
# 允许最大连接数
max_connections=800
# 允许连接失败的次数。
max_connect_errors=500
#限制server接受的数据包大小
max_allowed_packet = 1G
# 创建新表时将使用的默认存储引擎
default-storage-engine=INNODB
# 默认使用“mysql_native_password”插件认证
#mysql_native_password
default_authentication_plugin=mysql_native_password
lower_case_table_names=1
sql_mode='STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_ENGINE_SUBSTITUTION'
log_timestamps=SYSTEM
# 服务端使用的字符集默认为UTF8
character-set-server=utf8
#------------- log ---------------
log-error =/usr/local/mysql/log/mysql_error.log
#记录慢查询语句
slow_query_log =on
long_query_time =5
slow_query_log_file =/usr/local/mysql/log/slow_query.log
#开启记录所以SQL语句
#general_log =OFF
#general_log_file =$DBHOME/log/general.log
#------------- binlog ---------------
#log-bin-index =/usr/local/log/mysql-bin.index
#log-bin =/usr/local/mysql/binlogs/mysql-bin
binlog-format =ROW
#binlog日志保留时间
expire_logs_days =7
#当每进行n次事务提交之后,将binlog_cache中的数据写入磁盘
sync_binlog =100
binlog_cache_size =2M
#------------- timeout ----------------
connect_timeout = 30
net_read_timeout = 60
interactive_timeout = 1800
wait_timeout = 1800
#------------- innodb -------------
innodb_file_per_table =1
#数据库缓冲池大小,建议服务器内存的50~70%
innodb_buffer_pool_size =1G
#mysql事务日志文件(ib_logfile0)的大小
innodb_log_file_size =512M
[mysql]
# 设置mysql客户端默认字符集
default-character-set=utf8
[client]
# 设置mysql客户端连接服务端时默认使用的端口
port=3306
socket=/tmp/mysql.sock
搭建脚本
在当前package下新建脚本
cd /home/package
vim create.sh
输入如下命令
#!/bin/bash
#端口
port="3306"
#数据库名称
name="test"
#数据目录
data="/data/mysql/"${name}
#配置文件限制65535
cat >> /etc/security/limits.conf << EOF
* soft nofile 655360
* hard nofile 131072
* soft nproc 655350
* hard nproc 655350
* soft memlock unlimited
* hard memlock unlimited
EOF
tar xvf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
# 删除文件
# rm -rf mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz
mv /usr/local/mysql-8.0.28-linux-glibc2.12-x86_64 /usr/local/mysql
mkdir -p /usr/local/mysql/run
mkdir -p /usr/local/mysql/log
cp mysql3306.service /usr/lib/systemd/system/mysql3306.service
systemctl daemon-reload
groupadd mysql
useradd -g mysql mysql
yum remove -y `rpm -aq mariadb*`
sleep 5
mkdir -p /data/mysql/default/data
cp -r mysql.cnf /data/mysql/default/mysql.cnf
chown mysql:mysql /usr/local/mysql -R
chown mysql:mysql /data/mysql* -R
# init初始化
/usr/local/mysql/bin/mysqld --initialize --user=mysql --basedir=/usr/local/mysql --datadir=/data/mysql/default/data --lower-case-table-names=1
systemctl restart mysql3306.service
systemctl enable mysql3306.service
执行下一步操作时务必确保当前目录下存在如下文件
create.sh mysql3306.service mysql-8.0.28-linux-glibc2.12-x86_64.tar.xz mysql.cnf
运行
sh create.sh
密码
脚本执行完成后,倒数第二行的内容即为产生的临时密码
# 如下是我运行后生成的密码
[Note] [MY-010454] [Server] A temporary password is generated for root@localhost: ju?7eQfMD0pY
环境变量
配置mysql默认路径
vim /etc/profile
# 输入以下命令
export PATH=$PATH:/usr/local/mysql/bin
# 刷新
source /etc/profile
启动
systemctl start mysql3306
查看
systemctl status mysql3306
停止
systemctl stop mysql3306
修改root密码
登录后修改root密码,此处root之能作用于local,xxxxx为你自己的密码
ALTER USER 'root'@'localhost' IDENTIFIED BY 'xxxxx';
设置远程登录
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY 'xxxxx' WITH GRANT OPTION;
刷新权限
flush privileges;
删除(谨慎使用)
注:此处未移除创建的mysql用户组及用户
# 停止服务
systemctl stop mysql3306
# 移除服务
systemctl disable mysql3306
# 删除相关文件
rm -rf /data/mysql
rm -rf /usr/local/mysql
rm -rf /data/mysql