基于二进制包安装 MySQL 9.1

青年数据库学习互助会 2024-10-22

205

林森，网名“Forest”，拥有Oracle 12c OCM，MySQL 8.0 OCP，RHCE、OBCA、信息系统项目管理师等认证，利用闲暇时光记录、分享学习和工作中遇到的问题和心得。

想学会更多实用技巧，请联系小助手，欢迎加入青学会MOP技术社区（实名社区）。同时欢迎大家在评论区留言互动交流！社区会不定期举行相关的抽奖、公开分享活动。如果你有想了解的知识点希望我们发文可以后台私信。

正文开始

官方文档

https://dev.mysql.com/doc/refman/9.1/en/binary-installation.html

官方文档的操作步骤

$> groupadd mysql
$> useradd -r -g mysql -s bin/false mysql
$> cd usr/local
$> tar xvf path/to/mysql-VERSION-OS.tar.xz
$> ln -s full-path-to-mysql-VERSION-OS mysql
$> cd mysql
$> mkdir mysql-files
$> chown mysql:mysql mysql-files
$> chmod 750 mysql-files
$> bin/mysqld --initialize --user=mysql
$> bin/mysqld_safe --user=mysql &
# Next command is optional
$> cp support-files/mysql.server etc/init.d/mysql.server

环境准备

1、卸载mariadb
[root@forest ~]# yum remove mariadb
2、安装libaio
[root@forest ~]# yum install libaio-devel -y
3、删除配置文件
[root@forest ~]# rm -rf etc/my.cnf

创建用户组和用户

1、创建组
[root@forest ~]# groupadd -g 1000 mysql
2、创建用户
[root@forest ~]# useradd -r -g mysql -s bin/false -u 1000 mysql
3、查看用户mysql
[root@forest ~]# id mysql
uid=1000(mysql) gid=1000(mysql) groups=1000(mysql)

下载二进制软件包、解压缩、创建软链接

1、wget下载mysql
[root@forest ~]# wget https://dev.mysql.com/get/Downloads/MySQL-9.1/mysql-9.1.0-linux-glibc2.28-x86_64.tar.xz
[root@forest ~]# ll -h mysql-9.1.0-linux-glibc2.28-x86_64.tar.xz
-rw-r--r-- 1 root root 909M Oct 21 13:37 mysql-9.1.0-linux-glibc2.28-x86_64.tar.xz
 
2、解压缩
[root@forest ~]# tar -xf mysql-9.1.0-linux-glibc2.28-x86_64.tar.xz -C usr/local/
 
3、创建软链接
[root@forest ~]# ln -s usr/local/mysql-9.1.0-linux-glibc2.28-x86_64/ usr/local/mysql
 
[root@forest ~]# ll -h usr/local/mysql
lrwxrwxrwx 1 root root 46 Oct 21 13:41 usr/local/mysql -> /usr/local/mysql-9.1.0-linux-glibc2.28-x86_64/
 
[root@forest ~]# ll -h usr/local/mysql/
total 272K
drwxr-xr-x 2 7161 31415 4.0K Sep 24 21:45 bin
drwxr-xr-x 2 7161 31415  38 Sep 24 21:45 docs
drwxr-xr-x 3 7161 31415 4.0K Sep 24 21:45 include
drwxr-xr-x 6 7161 31415 4.0K Sep 24 21:45 lib
-rw-r--r-- 1 7161 31415 252K Sep 24 19:37 LICENSE
drwxr-xr-x 4 7161 31415  30 Sep 24 21:45 man
-rw-r--r-- 1 7161 31415 666 Sep 24 19:37 README
drwxr-xr-x 28 7161 31415 4.0K Sep 24 21:45 share
drwxr-xr-x 2 7161 31415  77 Sep 24 21:45 support-files

创建数据目录

[root@forest ~]# mkdir -p data/mysql/3306/data
[root@forest ~]# chown -R mysql:mysql data/mysql/3306/data

创建日志目录

[root@forest ~]# mkdir -p data/mysql/3306/log
[root@forest ~]# chown -R mysql.mysql data/mysql/3306/log

参数配置

[root@forest ~]# vim etc/my.cnf
[client]
 
socket=/data/mysql/3306/data/mysql.sock
 
[mysql]
prompt="\u@\h [\d]> "
 
[mysqld]
basedir=/usr/local/mysql
datadir=/data/mysql/3306/data
lower_case_table_names=1
log-bin=/data/mysql/3306/log/binlog
user=mysql
port=3306
pid-file=/data/mysql/3306/data/mysql.pid
socket=/data/mysql/3306/data/mysql.sock
log-error=/data/mysql/3306/data/mysql.err
log-timestamps=system

初始化数据库

默认使用/etc/my.cnf参数文件
[root@forest ~]# usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --initialize
 
看到日志输出显示临时密码说明初始化成功
[root@forest ~]# more /data/mysql/3306/data/mysql.err | grep temporary
2024-10-21T13:47:43.602963+08:00 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: Vl?j(fJ9MdhW

启动数据库

[root@forest ~]# /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf &
[1] 3310689
[root@forest ~]# 2024-10-21T05:48:12.059460Z mysqld_safe Logging to '/data/mysql/3306/data/mysql.err'.
2024-10-21T05:48:12.115621Z mysqld_safe Starting mysqld daemon with databases from /data/mysql/3306/data
 
[root@forest ~]# ps -ef |grep mysql | grep -v grep
root   3310689 554620 0 13:48 pts/0  00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
mysql  3310883 3310689 6 13:48 pts/0  00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf --basedir=/usr/local/mysql --datadir=/data/mysql/3306/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/data/mysql/3306/data/mysql.err --pid-file=/data/mysql/3306/data/mysql.pid --socket=/data/mysql/3306/data/mysql.sock --port=3306

配置环境变量

[root@forest ~]# vim .bash_profile
 
export MYSQL_HOME=/usr/local/mysql
export PATH=$PATH:$MYSQL_HOME/bin
 
[root@forest ~]# source .bash_profile

第一次登录提示修改密码

[root@forest ~]# mysql -uroot -p -S /data/mysql/3306/data/mysql.sock
Enter password:
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 8
Server version: 9.1.0
 
Copyright (c) 2000, 2024, Oracle and/or its affiliates.
 
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.
 
root@localhost [(none)]>
root@localhost [(none)]> select * from mysql.user\G
ERROR 1820 (HY000): You must reset your password using ALTER USER statement before executing this statement.
root@localhost [(none)]> alter user 'root'@'localhost' identified by 'oracle';
Query OK, 0 rows affected (0.01 sec)

密码复杂度插件

1、查看密码复杂度插件，基于二进制安装默认不安装密码复杂度插件
root@localhost [(none)]> select *from mysql.component;
Empty set (0.04 sec)
 
2、安装组件
root@localhost [(none)]> install component 'file://component_validate_password';
Query OK, 0 rows affected (0.00 sec)
 
3、查看组件
root@localhost [(none)]> select *from mysql.component;
+--------------+--------------------+------------------------------------+
| component_id | component_group_id | component_urn           |
+--------------+--------------------+------------------------------------+
|      1 |          1 | file://component_validate_password |
+--------------+--------------------+------------------------------------+
1 row in set (0.00 sec)
 
 
root@localhost [(none)]> show variables like 'validate_password%';
+-------------------------------------------------+--------+
| Variable_name                  | Value |
+-------------------------------------------------+--------+
| validate_password.changed_characters_percentage | 0   |
| validate_password.check_user_name        | ON   |
| validate_password.dictionary_file        |    |
| validate_password.length            | 8   |
| validate_password.mixed_case_count       | 1   |
| validate_password.number_count         | 1   |
| validate_password.policy            | MEDIUM |
| validate_password.special_char_count      | 1   |
+-------------------------------------------------+--------+
8 rows in set (0.00 sec)
 
 
4、再次修改密码失败，不符合密码度要求
root@localhost [(none)]> alter user user() identified by 'oracle';
ERROR 1819 (HY000): Your password does not satisfy the current policy requirements
 
5、密码复杂度要求，至少一个数字，一个特殊字符，一个大写字母，一个小写字母，且长度不小于8位
root@localhost [(none)]> alter user user() identified by 'MySQL@91';
Query OK, 0 rows affected (0.01 sec)
 
6、卸载插件
root@localhost [(none)]> uninstall component 'file://component_validate_password';
Query OK, 0 rows affected (0.29 sec)
 
root@localhost [(none)]> select *from mysql.component;
Empty set (0.00 sec)
 
root@localhost [(none)]> show variables like 'validate_password%';
Empty set (0.00 sec)

MySQL服务管理

通常有两种服务管理方式

1、服务脚本管理（/etc/init.d/mysqld）

2、systemd

使用/etc/init.d/mysqld管理MySQL服务

1、MySQL安装包脚本
[root@forest ~]# ls /usr/local/mysql/support-files/
mysqld_multi.server mysql-log-rotate mysql.server
 
[root@forest ~]# /usr/local/mysql/support-files/mysql.server status
 SUCCESS! MySQL running (3310883)
 
2、使用/etc/init.d/mysqldr服务管理MySQL实例，只需要复制mysql.server到/etc/init.d/目录下即可，并修改名字为mysqld
[root@forest ~]# cp /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@forest ~]# /etc/init.d/mysqld status
 SUCCESS! MySQL running (3310883)
 
[root@forest ~]# service mysqld status
 SUCCESS! MySQL running (2052)

使用systemd管理MySQL服务

1、创建systemd服务配置文件
[root@forest ~]# vim /usr/lib/systemd/system/mysqld.service
 
[Unit]
Description=MySQL Server
Documentation=man:mysqld(8)
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
 
Type=notify
TimeoutSec=0
 
# Execute pre and post scripts as root
PermissionsStartOnly=true
 
# Start main service
ExecStart=/usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf $MYSQLD_OPTS
 
# Use this to switch malloc implementation
#EnvironmentFile=-/etc/sysconfig/mysql
 
# Sets open_files_limit
@LimitNOFILE = 5000
 
Restart=on-failure
 
Environment=MYSQLD_PARENT_PID=1
RestartPreventExitStatus=1
 
PrivateTmp=false
 
2、使配置文件生效
[root@forest ~]# systemctl daemon-reload
 
[root@forest ~]# service mysqld status
 SUCCESS! MySQL running (1301)
  
3、使用systemd管理MySQL服务
[root@forest ~]# systemctl status mysqld
● mysqld.service - MySQL Server
  Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
  Active: inactive (dead)
   Docs: man:mysqld(8)
      http://dev.mysql.com/doc/refman/en/using-systemd.html
 
Oct 21 14:10:04 forest.oracle.com systemd[1]: /usr/lib/systemd/system/mysqld.service:28: Unknown lvalue '@LimitNOFILE' in section 'Service'
 
使用/etc/init.d/mysqld关闭MySQL
[root@forest ~]# /etc/init.d/mysqld stop
Shutting down MySQL..2024-10-21T06:12:43.982636Z mysqld_safe mysqld from pid file /data/mysql/3306/data/mysql.pid ended
 SUCCESS!
[1]+ Done          /usr/local/mysql/bin/mysqld_safe --defaults-file=/etc/my.cnf
 
[root@forest ~]# systemctl start mysqld
[root@forest ~]# systemctl status mysqld
● mysqld.service - MySQL Server
  Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
  Active: active (running) since Mon 2024-10-21 14:23:12 CST; 4s ago
   Docs: man:mysqld(8)
      http://dev.mysql.com/doc/refman/en/using-systemd.html
 Main PID: 3500691 (mysqld)
  Status: "Server is operational"
  Tasks: 39 (limit: 411235)
  Memory: 430.2M
  CGroup: /system.slice/mysqld.service
      └─3500691 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
 
Oct 21 14:23:11 forest.oracle.com systemd[1]: Starting MySQL Server...
Oct 21 14:23:12 forest.oracle.com systemd[1]: Started MySQL Server.
 
[root@forest ~]# ps -ef |grep mysql
mysql  3500691    1 1 14:23 ?    00:00:01 /usr/local/mysql/bin/mysqld --defaults-file=/etc/my.cnf
root   3507807 554620 0 14:24 pts/0  00:00:00 grep --color=auto mysql
4、设置开机自启动
[root@forest ~]# systemctl enable mysqld
Synchronizing state of mysqld.service with SysV service script with /usr/lib/systemd/systemd-sysv-install.
Executing: /usr/lib/systemd/systemd-sysv-install enable mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.