mysql进阶

mysql进阶

1.安装mysql

1.1 mysql二进制安装

#创建mysql用户
[root@zzd139 ~]# useradd -Mrs /sbin/nologin mysql

#安装ncurses-compat-libs
[root@zzd139 ~]# dnf -y install ncurses-compat-libs

#将mysql二进制包解压到/usr/local/目录下
[root@zzd139 ~]# tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@zzd139 local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'
[root@zzd139 local]# ll
total 0
drwxr-xr-x. 2 root root   6 May 19  2020 bin
drwxr-xr-x. 2 root root   6 May 19  2020 etc
drwxr-xr-x. 2 root root   6 May 19  2020 games
drwxr-xr-x. 2 root root   6 May 19  2020 include
drwxr-xr-x. 2 root root   6 May 19  2020 lib
drwxr-xr-x. 3 root root  17 Jun 28 09:25 lib64
drwxr-xr-x. 2 root root   6 May 19  2020 libexec
lrwxrwxrwx. 1 root root  36 Jul 27 16:54 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 27 16:53 mysql-5.7.38-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 19  2020 sbin
drwxr-xr-x. 5 root root  49 Jun 28 09:25 share
drwxr-xr-x. 2 root root   6 May 19  2020 src
[root@zzd139 local]# cd mysql
[root@zzd139 mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files

#修改目录/usr/local/mysql的属主属组
[root@zzd139 mysql]# chown -R mysql.mysql /usr/local/mysql
[root@zzd139 mysql]# chown -R mysql.mysql /usr/local/mysql-5.7.38-linux-glibc2.12-x86_64/
[root@zzd139 mysql]# ll -d /usr/local/mysql
lrwxrwxrwx. 1 mysql mysql 36 Jul 27 16:54 /usr/local/mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
[root@zzd139 mysql]# ll -d /usr/local/mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Jul 27 16:53 /usr/local/mysql-5.7.38-linux-glibc2.12-x86_64/

#配置mysql全局环境变量
[root@zzd139 mysql]# echo "export PATH=$PATH:/usr/local/mysql/bin" > /etc/profile.d/mysql.sh
[root@zzd139 mysql]# source /etc/profile.d/mysql.sh 
[root@zzd139 mysql]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin

#将mysql头文件添加至系统默认识别的目录/usr/include
[root@zzd139 mysql]# ln -sv /usr/local/mysql/include/ /usr/include/mysql
'/usr/include/mysql' -> '/usr/local/mysql/include/'
[root@zzd139 mysql]# ll -d /usr/include/mysql
lrwxrwxrwx. 1 root root 25 Jul 27 16:58 /usr/include/mysql -> /usr/local/mysql/include/

#将mysql的lib库文件添加到系统识别文件
[root@zzd139 mysql]# echo "/usr/local/mysql/lib/" > /etc/ld.so.conf.d/mysql.conf
[root@zzd139 mysql]# ldconfig 

#添加man文档
[root@zzd139 mysql]# vim /etc/man_db.conf 
$(添加如下内容)
	MANDATORY_MANPATH                       /usr/local/mysql/man

#建立存放数据目录
[root@zzd139 mysql]# mkdir -p /opt/mysql/data
[root@zzd139 mysql]# chown  -R mysql.mysql /opt/mysql/
[root@zzd139 mysql]# ll /opt/mysql/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 27 17:06 data

#初始化数据库
[root@zzd139 ~]# mysqld --initialize --user=mysql --datadir=/opt/mysql/data
2022-07-27T09:09:13.223427Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-27T09:09:13.386290Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-27T09:09:13.429221Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-27T09:09:13.485193Z 0 [Warning] No existing UUID has been found, so we assume that this is the first time that this server has been started. Generating a new UUID: c8900af2-0d8b-11ed-b3bc-000c29f177ce.
2022-07-27T09:09:13.485726Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-27T09:09:13.674856Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-27T09:09:13.674870Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-27T09:09:13.675192Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-27T09:09:13.746758Z 1 [Note] A temporary password is generated for root@localhost: oGDSXemN0u=f
#oGDSXemN0u=f就是mysql的密码，每次初始化密码都不一样，所以登录后首先修改密码，我们先将这个密码保存起来以防忘记
[root@zzd139 ~]# echo oGDSXemN0u=f > pass
[root@zzd139 ~]# cat pass 
oGDSXemN0u=f

#生成配置文件
[root@zzd139 ~]# vim /etc/my.cnf
[mysqld]
basedir = /usr/local/mysql
datadir = /opt/mysql/data
socket = /tmp/mysql.sock
port = 3306
pid-file = /opt/mysql/data/mysql.pid
user = mysql
skip-name-resolve
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
[root@zzd139 system]# chown -R mysql.mysql /etc/my.cnf

#启动mysql
[root@zzd139 ~]# /usr/local/mysql/support-files/mysql.server start
Starting MySQL.Logging to '/opt/mysql/data/zzd139.err'.
 SUCCESS! 
#关闭mysql
[root@zzd139 ~]# /usr/local/mysql/support-files/mysql.server stop
Shutting down MySQL.. SUCCESS! 

#使用systemd风格管理mysql服务
[root@zzd139 ~]# cd /usr/lib/systemd/system
[root@zzd139 system]# cp sshd.service mysqld.service
[root@zzd139 system]# vim mysqld.service 
    [Unit]
    Description=mysql server daemon
    After=network.target sshd-keygen.target

    [Service]
    Type=forking
    ExecStart=/usr/local/mysql/support-files/mysql.server start
    ExecStop=/usr/local/mysql/support-files/mysql.server stop
    ExecReload=/bin/kill -HUP $MAINPID

    [Install]
    WantedBy=multi-user.target
[root@zzd139 system]# systemctl daemon-reload 
[root@zzd139 system]# setenforce 0
$(这里还需要设置防火墙规则允许3306端口通过，觉得麻烦的话可以直接关闭防火墙)
[root@zzd139 system]# systemctl start mysqld.service
[root@zzd139 system]# systemctl status mysqld.service 
● mysqld.service - mysql server daemon
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: active (running) since Wed 2022-07-27 17:29:52 CST; 2min 1s ago
  Process: 3261 ExecStart=/usr/local/mysql/support-files/mysql.server start (code=exited, status=0/SUCCESS)
 Main PID: