mysql进阶

#mysql进阶

[root@localhost ~]# ls
file ifcfg-ens33 outfile ss
home mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz passwd yyds

创建组，用户

[root@localhost ~]# groupadd -r mysql
[root@localhost ~]# useradd -M -s /sbin/nologin -r -g mysql mysql
[root@localhost ~]# id mysql
uid=994(mysql) gid=665(mysql) groups=665(mysql)

解压软件包

[root@localhost ~]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/l
ocal/
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ls
bin games lib libexec sbin src
etc include lib64 mysql-5.7.37-linux-glibc2.12-x86_64 share
[root@localhost local]#

设置软链接

[root@localhost local]# ln -s mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 May 11 2019 bin
drwxr-xr-x. 2 root root 6 May 11 2019 etc
drwxr-xr-x. 2 root root 6 May 11 2019 games
drwxr-xr-x. 2 root root 6 May 11 2019 include
drwxr-xr-x. 2 root root 6 May 11 2019 lib
drwxr-xr-x. 2 root root 6 May 11 2019 lib64
drwxr-xr-x. 2 root root 6 May 11 2019 libexec
lrwxrwxrwx. 1 root root 36 Jul 27 05:30 mysql -> mysql-5.7.37-linux-glib
c2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 27 05:28 mysql-5.7.37-linux-glibc2.12-x86
_64
drwxr-xr-x. 2 root root 6 May 11 2019 sbin
drwxr-xr-x. 5 root root 49 Jun 28 02:50 share
drwxr-xr-x. 2 root root 6 May 11 2019 src
[root@localhost local]#

更改文件属主属组

[root@localhost local]# chown -R mysql.mysql /usr/local/mysql-5.7.37-linux-glib
c2.12-x86_64/
[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 May 11 2019 bin
drwxr-xr-x. 2 root root 6 May 11 2019 etc
drwxr-xr-x. 2 root root 6 May 11 2019 games
drwxr-xr-x. 2 root root 6 May 11 2019 include
drwxr-xr-x. 2 root root 6 May 11 2019 lib
drwxr-xr-x. 2 root root 6 May 11 2019 lib64
drwxr-xr-x. 2 root root 6 May 11 2019 libexec
lrwxrwxrwx. 1 mysql mysql 36 Jul 27 05:30 mysql -> mysql-5.7.37-linux-gl
ibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Jul 27 05:28 mysql-5.7.37-linux-glibc2.12-x
86_64
drwxr-xr-x. 2 root root 6 May 11 2019 sbin
drwxr-xr-x. 5 root root 49 Jun 28 02:50 share
drwxr-xr-x. 2 root root 6 May 11 2019 src
[root@localhost local]#

设置环境变量

[root@localhost local]# cd mysql
[root@localhost mysql]# ls
bin docs include lib LICENSE man README share support-files
[root@localhost mysql]# pwd
/usr/local/mysql
[root@localhost mysql]# echo 'export PATH=$PATH:/usr/local/mysql/bin' >/etc/pro
file.d/mysql.sh
[root@localhost bin]# cat /etc/profile.d/mysql.sh
export PATH=$PATH:/usr/local/mysql/bin
[root@localhost bin]# bash
[root@localhost bin]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/my
sql/bin
[root@localhost bin]#

设置include软链接

[root@localhost bin]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@localhost bin]# ll /usr/include/
total 0
lrwxrwxrwx. 1 root root 25 Jul 27 05:48 mysql -> /usr/local/mysql/includ
e/
drwxr-xr-x. 2 root root 27 Jun 28 02:50 python3.6m

设置lib

[root@localhost bin]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
"/etc/ld.so.conf.d/mysql.conf" 1L, 21C 1,20
All
[root@localhost bin]# ldconfig

设置man
编写/etc/man_db.conf 在其中添加
MANDATORY_MANPATH /usr/local/mysql/man
建立数据存放目录

[root@localhost mysql]# mkdir /opt/data
[root@localhost mysql]# chown -R mysql.mysql /opt/data/
[root@localhost mysql]# ll -d /opt/data/
drwxr-xr-x. 2 mysql mysql 6 Jul 27 05:55 /opt/data/
[root@localhost mysql]#

初始化mysql

[root@localhost mysql]# mysqld --initialize --user=mysql --datadir=/opt/data

2022-07-26T21:57:17.449575Z 1 [Note] A temporary password is generated fo
r root@localhost: JAqq3L6jXa+W （临时密码）
root@localhost mysql]# cd
[root@localhost ~]# echo 'JAqq3L6jXa+W' >passwd
```
（密码是随机生成，不是一样的）
生成配置文件
```
[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
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIV
ISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
```
保存退出
配置开启自启
```
[root@localhost ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/ini
t.d/mysqld
[root@localhost ~]# vim /etc/init.d/mysqld
```
添加下列命令
basedir=/usr/local/mysql
datadir=/opt/data
退出保存
```
[root@localhost ~]# chkconfig --add mysqld
[root@localhost ~]# chkconfig mysqld on
[root@localhost ~]#
```
启动mysql
```
[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.err'.
SUCCESS!
[root@localhost ~]#
```
进入mysql
```
[root@localhost ~]# mysql -uroot -p'JAqq3L6jXa+W'
mysql: error while loading shared libraries: libncurses.so.5: cannot open
shared object file: No such file or directory
```
这时候我们就需要下载libncurses.so.5
```
[root@localhost ~]# yum whatprovides libncurses.so.5
Last metadata expiration check: 14 days, 3:37:20 ago on Wed 13 Jul 2022 0
2:37:03 AM CST.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libra
ries
Repo : BaseOS
Matched from:
Provide : libncurses.so.5
[root@localhost ~]# yum install -y ncurses-compat-libs
[root@localhost ~]# mysql -uroot -p'JAqq3L6jXa+W'
mysql: [Warning] Using a password on the command line interface can be in
secure.
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 state
ment.
mysql>
```
成功进入
更改密码
```
mysql> set password = password('@123');
Query OK, 0 rows affected, 1 warning (0.00 sec)
```
###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将无法正常处理连接请求
###设置隐藏密码登录
```
[root@localhost ~]# vim .my.cnf
[client]
user=root
password=@123
[root@localhost ~]# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 7
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 state
ment.
mysql>
```
###破解mysql密码
1.编译文件 /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 ~]# service mysql restart
Redirecting to /bin/systemctl restart mysql.service
[root@localhost ~]#
```
3.进入mysql更改密码
```
mysql> update user set authentication_string = Password('123456') where H
ost = 'localhost' and User = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> quit
Bye
```
4.退出删除跳过授权表命令
5.登录mysql验证
```
[root@localhost ~]# mysql -uroot -p123456
mysql: [Warning] Using a password on the command line interface can be in
secure.
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 state
ment.
mysql>
```