• 欢迎光临~

mysql进阶

开发技术 开发技术 2022-07-26 次浏览

目录
  • mysql进阶
    • 1. 二进制格式mysql安装
    • mysql配置文件
  • 数据库密码破解:

mysql进阶

1. 二进制格式mysql安装

[root@133 ~]# cd /usr/src/
[root@133 src]# ls
debug  kernels  mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz

创建用户和组

[root@133 src]# useradd -M -r  -s /sbin/nologin mysql
[root@133 src]# id mysql
uid=994(mysql) gid=991(mysql) groups=991(mysql)

解压软件至/usr/local/

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

查看

[root@133 src]# cd /usr/local/
[root@133 local]# ll
total 0
drwxr-xr-x. 6 root root  58 Jul 21 19:02 apr
drwxr-xr-x. 5 root root  43 Jul 21 19:10 apr-util
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 Jul 20 21:06 lib64
drwxr-xr-x. 2 root root   6 May 19  2020 libexec
drwxr-xr-x. 9 root root 129 Jul 26 19:05 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 19  2020 sbin
drwxr-xr-x. 5 root root  49 Jul 20 21:06 share
drwxr-xr-x. 2 root root   6 May 19  2020 src

做一个软链接

[root@133 local]# ln -sv mysql-5.7.37-linux-glibc2.12-x86_64/   mysql
'mysql' -> 'mysql-5.7.37-linux-glibc2.12-x86_64/'
[root@133 local]# ll
total 0
drwxr-xr-x. 6 root root  58 Jul 21 19:02 apr
drwxr-xr-x. 5 root root  43 Jul 21 19:10 apr-util
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 Jul 20 21:06 lib64
drwxr-xr-x. 2 root root   6 May 19  2020 libexec
lrwxrwxrwx. 1 root root  36 Jul 26 19:08 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 26 19:05 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root   6 May 19  2020 sbin
drwxr-xr-x. 5 root root  49 Jul 20 21:06 share
drwxr-xr-x. 2 root root   6 May 19  2020 src

更改权限

[root@133 local]# chown -R mysql.mysql mysql
[root@133 local]# ll mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 26 19:08 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
[root@133 local]# ll mysql-5.7.37-linux-glibc2.12-x86_64/ -d
drwxr-xr-x. 9 mysql mysql 129 Jul 26 19:05 mysql-5.7.37-linux-glibc2.12-x86_64/

添加环境变量

[root@133 mysql]# cd bin/
[root@133 bin]# pwd
/usr/local/mysql/bin
[root@133 bin]# echo 'export' PATH=$PATH:/usr/local/mysql/bin
export PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin
[root@133 bin]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
[root@133 bin]# source /etc/profile.d/mysql.sh 
[root@133 bin]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin
[root@133 mysql]# ln -s /usr/local/mysql/include/   /usr/include/mysql
[root@133 mysql]# ll /usr/include/
lrwxrwxrwx.  1 root root     25 Jul 26 19:18 mysql -> /usr/local/mysql/include/

[root@133 mysql]# vim /etc/ld.so.conf.d/mysql.conf

usr/local/mysql/lib
[root@133 mysql]# ldconfig 

[root@133 mysql]# vim /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@133 mysql]# mkdir /opt/data
[root@133 mysql]# chown -R mysql.mysql /opt/data
[root@133 mysql]# ll -d /opt/data/
drwxr-xr-x. 2 mysql mysql 6 Jul 26 19:23 /opt/data/

初始化数据库

[root@133 mysql]# mysqld --initialize --user=mysql --datadir=/opt/data
2022-07-26T11:25:13.050158Z 1 [Note] A temporary password is generated for root@localhost: un*W9)tJ)D/D
初始化之后的密码为un*W9)tJ)D/D

生成配置文件

[root@133 ~]# 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_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

配置服务启动脚本

[root@133 ~]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@133 ~]# vim /etc/init.d/mysqld 
basedir=/usr/local/mysql
datadir=/opt/data

启动mysql

[root@133 ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/133.err'.
 SUCCESS! 
[root@133 ~]# 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                      *:80                    *:*                
LISTEN   0        128                   [::]:22                 [::]:*                
LISTEN   0        80                       *:3306                  *:*     

设置为开机自启

[root@133 ~]# chkconfig  --add mysqld
[root@133 ~]# chkconfig  mysqld on
[root@133 ~]# chkconfig --list

Note: This output shows SysV services only and does not include native
      systemd services. SysV configuration data might be overridden by native
      systemd configuration.

      If you want to list systemd services use 'systemctl list-unit-files'.
      To see services enabled on particular target use
      'systemctl list-dependencies [target]'.

mysqld         	0:off	1:off	2:on	3:on	4:on	5:on	6:off

解决无法登录

[root@133 ~]# mysql -uroot -p'un*W9)tJ)D/D'
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

[root@133 ~]# yum whatprovides libncurses.so.5
Last metadata expiration check: 0:33:11 ago on Tue 26 Jul 2022 07:12:29 PM CST.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : BaseOS
Matched from:
Provide    : libncurses.so.5
[root@133 ~]# yum install -y ncurses-compat-libs

[root@133 ~]# mysql -uroot -p'un*W9)tJ)D/D'
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> 

设置新密码

mysql> set password = password('123456');
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@133 ~]# vim .my.cnf
[client]
user=root
password=123456

[root@133 ~]# 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>

数据库密码破解:

1、编辑mysql配置文件
vim /etc/my.cnf
添加一行: skip-grant-tables

[root@133 ~]# 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
sql-mode = STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

2、重启服务
service mysqld restart

[root@133 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

3、验证登入
mysql

[root@133 ~]# 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.


4、修改密码
use msyql;
update user set authentication_string = Password('123456') where Host = 'localhost' and User = 'root';

mysql> update user set authentication_string = Password('123456') where Host = 'localhost' and User = 'root';
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1  Changed: 0  Warnings: 1

5、删除修改/etc/my.cnf中skip-grant-tables

[root@133 ~]# 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_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION

6、重启mysql服务

[root@133 ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 

7、用新的密码去验证登入

[root@133 ~]# 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> 

程序员灯塔
转载请注明原文链接:mysql进阶
喜欢 (0)