MySQL 8.3.0发布,最佳实战部署

作者：IT邦德
中国DBA联盟(ACDU)成员，10余年DBA工作经验
擅长主流数据Oracle、MySQL、PG、openGauss运维
备份恢复，安装迁移，性能优化、故障应急处理等

可提供技术业务：
1.DB故障处理/疑难杂症远程支援
2.Mysql/PG/Oracle/openGauss
数据库部署及数仓搭建
•••
微信:jem_db
QQ交流群:587159446
公众号：IT邦德

文章目录

• 1.环境准备
• 2.配置yum依赖
• 3.用户及目录
• 4.创建用户及组
• 5.解压缩包
• 6.环境变量配置
• 7.创建参数文件
• 8.数据库初始化
• 9.启动Mysql
• 10.登陆MySQL
• 11.systemctl自启动MySQL

1.环境准备

1.操作系统
[root@jeames ~]# cat /etc/redhat-release
Red Hat Enterprise Linux release 8.1 (Ootpa)
2.关闭防火墙#
查看防火墙是状态
[root@jeames ~]# systemctl status firewalld
关闭防火墙
[root@jeames ~]# systemctl stop firewalld
取消开机自启动
[root@jeames ~]# systemctl disable firewalld
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.service.
3.selinux关闭
修改参数文件/etc/sysconfig/selinux中SELINUX的值为disabled
[root@jeames ~]# sed -i 's/SELINUX=enforcing/SELINUX=disabled/g' /etc/selinux/config

2.配置yum依赖

1.创建挂载路径
mkdir -p /mnt/cdrom

2.挂载系统镜像光盘到指定目录
#因为光盘的格式通常是iso9660，意思是/dev/sr0挂载在/mnt/cdrom目录上
mount -t iso9660 /dev/sr0 /mnt/cdrom
mount: /mnt/cdrom: WARNING: device write-protected, mounted read-only.

3.修改yum源配置文件
##编辑rhel8-local.repo文件，加入以下内容
[root@jeames ~]# cd /etc/yum.repos.d
[root@jeames yum.repos.d]# rm -rf *
[root@jeames yum.repos.d]# vi rhel8-local.repo

[localREPO]
name=localhost8
baseurl=file:///mnt/cdrom/BaseOS
enable=1
gpgcheck=0

[localREPO_APP]
name=localhost8_app
baseurl=file:///mnt/cdrom/AppStream
enable=1
gpgcheck=0

4.配置好后重建本地缓存
yum clean all 
yum makecache 
yum repolist

5.安装MySQL 8.1版本二进制所需的依赖包
yum -y install libncurses*
yum -y install libaio
yum -y install perl perl-devel
yum -y install autoconf
yum -y install numactl.x86_64
yum -y install ncurses-compat-libs

3.用户及目录

mkdir -p /mysql/data/mysql3306
mkdir -p /mysql/app/
mkdir -p /mysql/conf/
mkdir -p /mysql/data/mysql3306/pid/
mkdir -p /mysql/data/mysql3306/socket/
mkdir -p /mysql/data/mysql3306/log/
mkdir -p /mysql/data/mysql3306/binlog/
mkdir -p /mysql/data/mysql3306/errlog
mkdir -p /mysql/data/mysql3306/relaylog/
mkdir -p /mysql/data/mysql3306/slowlog/
mkdir -p /mysql/data/mysql3306/tmp/

4.创建用户及组

[root@jeames ~]# groupadd mysql
[root@jeames ~]# useradd -g mysql mysql
[root@jeames ~]# chown -R mysql:mysql /mysql
[root@jeames ~]# passwd mysql

5.解压缩包

[root@jeames ~]# cd /opt
[root@jeames opt]# cp mysql-8.3.0-linux-glibc2.28-x86_64.tar.xz /mysql/app
[root@jeames opt]# chown -R mysql:mysql /mysql

mysql用户操作
[root@jeames ~]# su - mysql
md5 值验证，保证下载到的软件包无破损无木马
[mysql@jeames ~]$ cd /mysql/app
[mysql@jeames app]$ ll
total 474444
-rw-r--r--. 1 mysql mysql 485826804 Jan 21 03:55 mysql-8.3.0-linux-glibc2.28-x86_64.tar.xz
[mysql@jeames app]$ md5sum mysql-8.3.0-linux-glibc2.28-x86_64.tar.xz
f4f824cf51013fad5694614cda223e64  mysql-8.3.0-linux-glibc2.28-x86_64.tar.xz

解压软件包并重命名
[mysql@jeames app]$ tar xvf mysql-8.3.0-linux-glibc2.28-x86_64.tar.xz
[mysql@jeames app]$ mv mysql-8.3.0-linux-glibc2.28-x86_64 mysql8

[mysql@jeames app]$ ll
total 474448
drwxrwxr-x. 9 mysql mysql      4096 Jan 21 03:57 mysql8
-rw-r--r--. 1 mysql mysql 485826804 Jan 21 03:55 mysql-8.3.0-linux-glibc2.28-x86_64.tar.xz

6.环境变量配置

##mysql用户下操作
cat >> /home/mysql/.bash_profile << "EOF"
MYSQL_HOME=/mysql/app/mysql8
PATH=$PATH:$HOME/.local/bin:$HOME/bin:$MYSQL_HOME/bin
EOF
环境变量生效
[mysql@jeames app]$ source ~/.bash_profile
[mysql@jeames app]$ which mysql
/mysql/app/mysql8/bin/mysql

7.创建参数文件

由于是二进制文件安装，数据库参数文件需要自己配置
cat >> /mysql/conf/my3306.cnf << "EOF"
[mysqld]
server_id = 8303306
default-storage-engine= InnoDB
basedir=/mysql/app/mysql8
datadir=/mysql/data/mysql3306/data/
socket=/mysql/data/mysql3306/socket/mysql.sock
log-error=/mysql/data/mysql3306/log/mysqld.log
pid-file=/mysql/data/mysql3306/pid/mysqld.pid
port=3306
default_authentication_plugin=mysql_native_password
max_connections=1500
back_log=500
wait_timeout=1800
max_user_connections=800
innodb_buffer_pool_size=1024M
innodb_log_file_size=512M
innodb_log_buffer_size=40M
slow_query_log=ON
long_query_time=5
# log settings #
slow_query_log = ON
slow_query_log_file = /mysql/data/mysql3306/slowlog/slow3306.log
log_error = /mysql/data/mysql3306/errlog/err3306.log
log_error_verbosity = 3
log_bin = /mysql/data/mysql3306/binlog/mysql_bin
log_bin_index = /mysql/data/mysql3306/binlog/mysql_binlog.index
general_log_file = /data/mysql/mysql3306/generallog/general.log
log_queries_not_using_indexes = 1
log_slow_admin_statements = 1
#expire_logs_days = 90
binlog_expire_logs_seconds = 2592000
long_query_time = 2
min_examined_row_limit = 100
log_throttle_queries_not_using_indexes = 1000
innodb_flush_log_at_trx_commit=1
EOF

8.数据库初始化

mysql用户操作：
mysqld --defaults-file=/mysql/conf/my3306.cnf --initialize --user=mysql --basedir=/mysql/app/mysql8 --datadir=/mysql/data/mysql3306/data/
注意：以上同一行执行

9.启动Mysql

[mysql@jeames app]$ mysqld_safe --defaults-file=/mysql/conf/my3306.cnf --user=mysql &

10.登陆MySQL

第一次登录 MySQL 时，需要到错误日志下找初始化密码，使用 socket 加密码登录进去后，无法查询任何东西，提示先要修改 root 密码.
[mysql@jeames ~]$ cat /mysql/data/mysql3306/errlog/err3306.log | grep password
[mysql@jeames ~]$ mysql -uroot -p
Enter password: 
ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

[mysql@jeames ~]$ mysql -uroot -p  -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock
mysql> status
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.

##修改密码
mysql> alter user root@'localhost' identified by 'root';

mysql> status
--------------
mysql  Ver 8.1.0 for Linux on x86_64 (MySQL Community Server - GPL)

Connection id:          8
Current database:
Current user:           root@localhost
SSL:                    Not in use
Current pager:          stdout
Using outfile:          ''
Using delimiter:        ;
Server version:         8.3.0
Protocol version:       10
Connection:             Localhost via UNIX socket
Server characterset:    utf8mb4
Db     characterset:    utf8mb4
Client characterset:    utf8mb4
Conn.  characterset:    utf8mb4
UNIX socket:            /mysql/data/mysql3306/socket/mysql.sock
Binary data as:         Hexadecimal
Uptime:                 3 min 46 sec

Threads: 2  Questions: 8  Slow queries: 0  Opens: 398  Flush tables: 3  Open tables: 36  Queries per second avg: 0.035

##远程登陆设置
mysql> create user root@'%' identified with mysql_native_password by 'root';
Query OK, 0 rows affected (0.01 sec)

mysql> grant all on *.* to root@'%' with grant option;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select user,host,plugin from mysql.user;  
+------------------+-----------+-----------------------+
| user             | host      | plugin                |
+------------------+-----------+-----------------------+
| root             | %         | mysql_native_password |
| mysql.infoschema | localhost | caching_sha2_password |
| mysql.session    | localhost | caching_sha2_password |
| mysql.sys        | localhost | caching_sha2_password |
| root             | localhost | mysql_native_password |
+------------------+-----------+-----------------------+
5 rows in set (0.01 sec)

11.systemctl自启动MySQL

1.替换server里面的mysqld_safe为mysqld
[root@jeames ~]# netstat -lnp|grep 3306
tcp6       0      0 :::33060                :::*                    LISTEN      42929/mysqld
tcp6       0      0 :::3306                 :::*                    LISTEN      42929/mysqld
unix  2      [ ACC ]     STREAM     LISTENING     92128    42929/mysqld         /mysql/data/mysql3306/socket/mysql.sock


[root@jeames ~]# find / -name mysql.server
/mysql/app/mysql8/support-files/mysql.server

cd /mysql/app/mysql8/support-files/
sed -i 's#mysqld_safe#mysqld#g' mysql.server

2.关闭数据库
[mysql@jeames ~]$ mysql -uroot -p  -P 3306 -S /mysql/data/mysql3306/socket/mysql.sock
mysql> shutdown;

cat >> /etc/systemd/system/mysqld.service  <<EOF
[Unit]
Description=MySQL Server
Documentation=http://dev.mysql.com/doc/refman/en/using-systemd.html
After=network.target
After=syslog.target
[Install]
WantedBy=multi-user.target
[Service]
User=mysql
Group=mysql
ExecStart=/mysql/app/mysql8/bin/mysqld --defaults-file=/mysql/conf/my3306.cnf
LimitNOFILE = 5000
EOF


3.环境变量设置
tail -1 /etc/profile
echo "export PATH=/mysql/app/mysql8/bin:$PATH" >> /etc/profile
source /etc/profile

[root@jeames ~]# chmod +x /etc/systemd/system/mysqld.service

systemctl daemon-reload
systemctl stop mysqld
systemctl start mysqld
systemctl status mysqld
systemctl enable mysqld

Starting MySQL ERROR! Couldn't find MySQL server (/usr/local/mysql/bin/mysqld)
mysql.server里面这两行没有添加路径，添加路径后即可解决问题

basedir=/mysql/app/mysql8
datadir=/mysql/data/mysql3306/data

SELinux is preventing /usr/bin/bash from execute access on the file mysqld. For complete SELinux message>
记得重启即可


--方法2
cp /mysql/app/mysql8/support-files/mysql.server /etc/rc.d/init.d/mysqld
chmod +x /etc/init.d/mysqld  #授予执行的权限
chkconfig --add mysqld #添加服务
chkconfig --del mysqld #删除服务
chkconfig --list   #查看服务表

[root@jeames ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld          0:off   1:off   2:on    3:on    4:on    5:on    6:off

这样就代表成功了
注释：后面每次启动就只要systemctl status mysqld 就行了
也可以直接systemctl enable mysqld 设置开机自启