mysql服务

二进制安装

到mysql官网下载mysql sever

通过XFTP传至虚拟机

[root@node2 ~]# ls
anaconda-ks.cfg  mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz

创建用户

[root@node2 ~]# useradd -r -M -s /sbin/nologin -u 306 mysql
[root@node2 ~]# id mysql
uid=306(mysql) gid=306(mysql) groups=306(mysql)
[root@node2 ~]# 

解压

[root@node2 src]# tar xf mysql-5.7.39-linux-glibc2.12-x86_64.tar.gz -C /usr/local/

配置

创建软连接

[root@node2 local]# ln -s /usr/local/mysql-5.7.39-linux-glibc2.12-x86_64 /usr/local/mysql
[root@node2 local]# ll /usr/local/
total 0
drwxr-xr-x. 2 root root   6 Jun 22  2021 bin
drwxr-xr-x. 2 root root   6 Jun 22  2021 etc
drwxr-xr-x. 2 root root   6 Jun 22  2021 games
drwxr-xr-x. 2 root root   6 Jun 22  2021 include
drwxr-xr-x. 2 root root   6 Jun 22  2021 lib
drwxr-xr-x. 3 root root  17 Nov 28 17:37 lib64
drwxr-xr-x. 2 root root   6 Jun 22  2021 libexec
lrwxrwxrwx. 1 root root  46 Dec 29 15:26 mysql -> /usr/local/mysql-5.7.39-linux-glibc2.12-x86_64
drwxr-xr-x. 9 root root 129 Dec 29 15:24 mysql-5.7.39-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 Jun 22  2021 sbin
drwxr-xr-x. 5 root root  49 Nov 28 17:37 share
drwxr-xr-x. 2 root root   6 Jun 22  2021 src
[root@node2 local]# 

修改属主

[root@node2 local]# chown -R mysql.mysql /usr/local/mysql*

添加环境变量

[root@node2 local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@node2 local]# source /etc/profile.d/mysql.sh 
[root@node2 local]# which mysql
/usr/local/mysql/bin/mysql
[root@node2 local]# ln -s /usr/local/mysql/include /usr/include/mysqld
[root@node2 local]# vi /etc/ld.so.conf.d/mysqld.conf
[root@node2 local]# cat /etc/ld.so.conf.d/mysqld.conf 
/usr/local/mysql/lib
[root@node2 local]# ldconfig
[root@node2 local]# vi /etc/man_db.conf 
MANDATORY_MANPATH                       /usr/man
MANDATORY_MANPATH                       /usr/share/man
MANDATORY_MANPATH                       /usr/local/share/man
MANDATORY_MANPATH                       /usr/local/mysql/man

建立数据缓存目录

[root@node2 ~]# /usr/local/mysql/bin/mysql --initialize --user mysql --datadir /opt/data
2022-12-29T07:36:20.413857Z 1 [Note] A temporary password is generated for root@localhost: ysgg,tniv

生成配置文件

[root@node2 ~]# vi /etc/my.cnf
[root@node2 ~]# cat /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/data/mysql.pid
user = mysql
skip-name-resolve
[root@node2 ~]# 

配置脚本

[root@node2 support-files]# vim mysql.server 
 
basedir=/usr/local/mysql
datadir=/opt/data

启用脚本 设置开机自启

[root@node2 ~]# vim /usr/lib/systemd/system/mysqld.service 
[root@node2 ~]# cat /usr/lib/systemd/system/mysqld.service 
[Unit]
Description=mysqld server daemon
After=network.target
 
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
 
[Install]
WantedBy=multi-user.target
[root@node2 ~]# 
[root@node2 ~]# systemctl daemon-reload
[root@node2 ~]# systemctl status mysqld

设置密码

mysql> set password = password("Cls20011221!");

多表管理

 
<code class="language-plaintext hljs">mysql>  SELECT name,sex FROM tb_students_info GROUP BY sex;
+-------+--------+
| name  | sex    |
+-------+--------+
| Henry | Female |
| Dany  | Male   |
+-------+--------+
2 rows in set (0.00 sec)
 
mysql> SELECT sex, GROUP_CONCAT(name) FROM tb_students_info GROUP BY sex;
+--------+-----------------------------------+
| sex    | GROUP_CONCAT(name)                |
+--------+-----------------------------------+
| Female | Henry,Jim,John,Thomas,Tom         |
| Male   | Dany,Green,Jane,Lily,Susan,LiMing |
+--------+-----------------------------------+
2 rows in set (0.00 sec)
 
mysql> SELECT age,sex,GROUP_CONCAT(name) FROM tb_students_info GROUP BY age,sex;
+------+--------+--------------------+
| age  | sex    | GROUP_CONCAT(name) |
+------+--------+--------------------+
|   21 | Female | John               |
|   22 | Female | Thomas             |
|   22 | Male   | Jane,Lily,LiMing   |
|   23 | Female | Henry,Tom          |
|   23 | Male   | Green,Susan        |
|   24 | Female | Jim                |
|   25 | Male   | Dany               |
+------+--------+--------------------+
7 rows in set (0.00 sec)
 
</code>