mysql进阶

1. 二进制格式mysql安装

 

 提前下载好

[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls  #  进到/usr/src/把下载好的包拖进来
debug  kernels
[root@localhost src]# ls
debug  kernels  mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz


[root@localhost src]# useradd -r -M -s /sbin/nologin mysql
[root@localhost src]# id mysql   #创建用户和组
uid=994(mysql) gid=991(mysql) 组=991(mysql)


[root@localhost src]# ls /usr/local/   
bin  etc  games  include  lib  lib64  libexec  sbin  share  src
[root@localhost src]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# ls /usr/local/
bin    include  libexec                              share
etc    lib      mysql-5.7.37-linux-glibc2.12-x86_64  src
games  lib64    sbin
[root@localhost src]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/  #  解压到/usrlocal
[root@localhost src]# ls /usr/local/
bin    include  libexec                              share
etc    lib      mysql-5.7.37-linux-glibc2.12-x86_64  src
games  lib64    sbin
[root@localhost local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql   # 把名字改短一点
[root@localhost local]# ls
bin  games    lib    libexec  sbin   src
etc  include  lib64  mysql    share
[root@localhost local]# chown -R mysql.mysql mysql  # 修改属主
[root@localhost local]# ll
总用量 0
drwxr-xr-x. 2 root  root    6 5月  19 2020 bin
drwxr-xr-x. 2 root  root    6 5月  19 2020 etc
drwxr-xr-x. 2 root  root    6 5月  19 2020 games
drwxr-xr-x. 2 root  root    6 5月  19 2020 include
drwxr-xr-x. 2 root  root    6 5月  19 2020 lib
drwxr-xr-x. 3 root  root   17 6月  29 01:01 lib64
drwxr-xr-x. 2 root  root    6 5月  19 2020 libexec
drwxr-xr-x. 9 mysql mysql 129 6月  29 05:44 mysql
drwxr-xr-x. 2 root  root    6 5月  19 2020 sbin
drwxr-xr-x. 5 root  root   49 6月  29 01:01 share
drwxr-xr-x. 2 root  root    6 5月  19 2020 src

设置环境变量，能够找到它
[root@localhost local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh
[root@localhost local]# source /etc/profile.d/mysql.sh
[root@localhost local]# which mysql  #此时就可以找到它
/usr/local/mysql/bin/mysql
 设置include
[root@localhost mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@localhost mysql]# vi /etc/ld.so.conf.d/mysql.conf

/usr/local/mysql/lib   #  告诉路径
[root@localhost mysql]# ldconfig   #执行一下读取配置文件

man文档
[root@localhost mysql]# vi /etc/man_db.conf   #  加入此行
MANDATORY_MANPATH                       /usr/local/mysql/man

//建立数据存放目录
[root@localhost ~]# mkdir -p /opt/data  # 创建目录
[root@localhost ~]# chown -R mysql.mysql /opt/data  # 更改属主
[root@localhost ~]# ll /opt/
总用量 0
drwxr-xr-x. 2 mysql mysql 6 6月  29 06:52 data

//初始化数据库
[root@localhost ~]# mysqld --initialize --user mysql --datadir /opt/data
2022-06-28T22:57:32.403955Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-28T22:57:32.793928Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-06-28T22:57:32.866331Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-06-28T22:57:32.929486Z 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: b1c2a913-f735-11ec-af4e-000c29eeb670.
2022-06-28T22:57:32.931258Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-06-28T22:57:34.257280Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-06-28T22:57:34.257337Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-06-28T22:57:34.258247Z 0 [Warning] CA certificate ca.pem is self signed.
2022-06-28T22:57:34.514921Z 1 [Note] A temporary password is generated for root@localhost: ##CPw=<zJ3oN
#  最后出现的一个只能登陆一次的零时密码，不能搞错，可以先写到一个文件里
[root@localhost ~]# echo '##CPw=<zJ3oN' > pass

//生成配置文件
[root@localhost ~]# vi /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  #跳过名称解析，用IP访问，不用域名访问
#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@localhost ~]#  cd /usr/local/mysql/   # 有一个支持文件
[root@localhost mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@localhost mysql]# cd support-files/
[root@localhost support-files]# ls   # 有一个脚本
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@localhost support-files]# file mysql.server 
mysql.server: POSIX shell script, ASCII text executable
[root@localhost support-files]# ll   # 这个脚本有执行权限
总用量 24
-rw-r--r--. 1 mysql mysql   773 11月 30 2021 magic
-rwxr-xr-x. 1 mysql mysql  1061 11月 30 2021 mysqld_multi.server
-rwxr-xr-x. 1 mysql mysql   894 11月 30 2021 mysql-log-rotate
-rwxr-xr-x. 1 mysql mysql 10576 11月 30 2021 mysql.server
[root@localhost support-files]# cp mysql.server mysqld  # 复制一个叫mysqld
[root@localhost support-files]# chown -R mysql.mysql mysqld # 更改权限
[root@localhost support-files]# ll
总用量 36
-rw-r--r--. 1 mysql mysql   773 11月 30 2021 magic
-rwxr-xr-x. 1 mysql mysql 10576 6月  29 07:20 mysqld
-rwxr-xr-x. 1 mysql mysql  1061 11月 30 2021 mysqld_multi.server
-rwxr-xr-x. 1 mysql mysql   894 11月 30 2021 mysql-log-rotate
-rwxr-xr-x. 1 mysql mysql 10576 11月 30 2021 mysql.server
[root@localhost support-files]#  vi mysqld  修改
basedir=/usr/local/mysql # 安装的位子
datadir=/opt/data    # 数据存放的位子

//启动mysql
[root@localhost ~]# /usr/local/mysql/support-files/mysqld start # 用脚本启动
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
 SUCCESS! 
[root@localhost ~]# ss -antl  # 此时已有3306端口号
State  Recv-Q Send-Q   Local Address:Port   Peer Address:Port  
 Process 
LISTEN 0      128            0.0.0.0:22          0.0.0.0:*            
LISTEN 0      128               [::]:22             [::]:*            
LISTEN 0      80                   *:3306              *:*     
     [root@localhost ~]# ps -ef|grep mysqld  # 进程也有
root        1722       1  0 07:28 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql       1910    1722  0 07:28 pts/0    00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=localhost.localdomain.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root        1944    1555  0 07:30 pts/0    00:00:00 grep --color=automysqld
  

//修改密码
//使用临时密码登录
[root@localhost ~]# dnf -y install ncurses-compat-libs # 提前把这个包装上
[root@localhost ~]# ls
anaconda-ks.cfg  pass
[root@localhost ~]# cat pass
##CPw=<zJ3oN
[root@localhost ~]# mysql -uroot -p'##CPw=<zJ3oN'  #  用之前保存的零时密码登录
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37

Copyright (c) 2000, 2022, 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.

mysql> set password = password('runtime123!');  # 设置新的密码
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> quit  #  退出
Bye
[root@localhost ~]# mysql -uroot -p'runtime123!'  # 用心密码登录一次，验证新密码设置成功
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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.

mysql> 

# 关闭防火墙，selinux,设置开机自启
[root@localhost ~]# cp /usr/lib/systemd/system/sshd.service mysqld.service
[root@localhost ~]# vim mysqld.service
[Unit]
Description=mysql server daemon
After=network.target sshd-keygen.target

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

[Install]
WantedBy=multi-user.target
[root@localhost ~]# mv mysqld.service /usr/lib/systemd/system
[root@localhost ~]# systemctl disable --now firewalld  # 关闭防火墙
Removed /etc/systemd/system/multi-user.target.wants/firewalld.service.
Removed /etc/systemd/system/dbus-org.fedoraproject.FirewallD1.serv
[root@localhost ~]# vim /etc/selinux/config   #永久关闭selinux


# This file controls the state of SELinux on the system.
# SELINUX= can take one of these three values:
#     enforcing - SELinux security policy is enforced.
#     permissive - SELinux prints warnings instead of enforcing.
#     disabled - No SELinux policy is loaded.
SELINUX=disabled
# SELINUXTYPE= can take one of these three values:
#     targeted - Targeted processes are protected,
#     minimum - Modification of targeted policy. Only selected processes are protected. 
#     mls - Multi Level Security protection.
SELINUXTYPE=targeted
[root@localhost ~]# setenforce 0
[root@localhost ~]# systemctl daemon-reload  # 读取一下
[root@localhost ~]# systemctl status mysqld 
● mysqld.service - mysql server daemon
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; >
   Active: inactive (dead)
lines 1-3/3 (END)
[root@localhost ~]# systemctl start mysqld  # 启动
[root@localhost ~]# ss -antl
State  Recv-Q Send-Q   Local Address:Port   Peer Address:Port Process 
LISTEN 0      128            0.0.0.0:22          0.0.0.0:*            
LISTEN 0      128               [::]:22             [::]:*            
LISTEN 0      80                   *:3306              *:*         # 3306端口号
[root@localhost ~]# mysql -uroot -p'runtime123!'  #  也可以通过密码连进来
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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.

mysql> 
[root@localhost ~]# systemctl enable mysqld   # 设置开机自启
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@localhost ~]# systemctl status mysqld
● mysqld.service - mysql server daemon
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; v>
   Active: active (running) since Wed 2022-06-29 08:44:05 CST; 3min 4>
 Main PID: 11015 (mysqld_safe)
    Tasks: 29 (limit: 11202)
   Memory: 183.9M
   CGroup: /system.slice/mysqld.service
           ├─11015 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir>
           └─11205 /usr/local/mysql/bin/mysqld --basedir=/usr/local/m>

6月 29 08:44:04 localhost.localdomain systemd[1]: Starting mysql serv>
6月 29 08:44:05 localhost.localdomain mysqld[11002]: Starting MySQL. >
6月 29 08:44:05 localhost.localdomain systemd[1]: Started mysql serve>
lines 1-13/13 (END)

2. mysql配置文件

mysql的配置文件为/etc/my.cnf

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/etc/my.cnf --> /etc/mysql/my.cnf --> --default-extra-file=/PATH/TO/CONF_FILE --> ~/.my.cnf

 

mysql常用配置文件参数：

参数	说明
port = 3306	设置监听端口
socket = /tmp/mysql.sock	指定套接字文件位置
basedir = /usr/local/mysql	指定MySQL的安装路径
datadir = /data/mysql	指定MySQL的数据存放路径
pid-file = /data/mysql/mysql.pid	指定进程ID文件存放路径
user = mysql	指定MySQL以什么用户的身份提供服务
skip-name-resolve	禁止MySQL对外部连接进行DNS解析 使用这一选项可以消除MySQL进行DNS解析的时间。 若开启该选项，则所有远程主机连接授权都要使用IP地址方 式否则MySQL将无法正常处理连接请求

 

 

mysql破译密码步骤：（只能root用户操作）
1.修改配置文件，加入以下内容
     skip-grant-tables
[root@localhost ~]# vim /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
skip-grant-tables   # 加入此行


2.重启mysql
[root@localhost ~]# ss -antl
State  Recv-Q Send-Q   Local Address:Port   Peer Address:Port Process 
LISTEN 0      128            0.0.0.0:22          0.0.0.0:*            
LISTEN 0      128               [::]:22             [::]:*            
LISTEN 0      80                   *:3306              *:*    
[root@localhost ~]# mysql  #  可以不用密码登录进来，但是很多功能不能实现
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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.

mysql> quit
Bye

3.修改密码
mysql> show databases;
+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| sys                |
+--------------------+
4 rows in set (0.00 sec)

mysql> use mysql;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
mysql> show tables;

mysql> select * from user\G
*************************** 1. row ***************************
                  Host: localhost
                  User: root
           Select_priv: Y
           Insert_priv: Y
           Update_priv: Y
           Delete_priv: Y
           Create_priv: Y
             Drop_priv: Y
           Reload_priv: Y
         Shutdown_priv: Y
          Process_priv: Y
             File_priv: Y
            Grant_priv: Y
       References_priv: Y
            Index_priv: Y
            Alter_priv: Y
          Show_db_priv: Y
            Super_priv: Y
 Create_tmp_table_priv: Y
      Lock_tables_priv: Y
          Execute_priv: Y
       Repl_slave_priv: Y
      Repl_client_priv: Y
      Create_view_priv: Y
        Show_view_priv: Y
   Create_routine_priv: Y
    Alter_routine_priv: Y
      Create_user_priv: Y
            Event_priv: Y
          Trigger_priv: Y
Create_tablespace_priv: Y
              ssl_type: 
            ssl_cipher: 
           x509_issuer: 
          x509_subject: 
         max_questions: 0
           max_updates: 0
       max_connections: 0
  max_user_connections: 0
                plugin: mysql_native_password
 authentication_string: *9B5265CC0F9DE4D18A4F4ADC9CFA4AA7B23B548F
mysql> update user set authentication_string = password('123456') where User = 'root' and Host = 'localhost';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1   #  修改密码



4.恢复配置文件，删除skip-grant-tables
[root@localhost ~]# vim /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



5.重启mysql
[root@localhost ~]# systemctl restart mysqld

6.验证新密码是否能登录
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be insecure.
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.37 MySQL Community Server (GPL)

Copyright (c) 2000, 2022, 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.

mysql>