mysql进阶

最新推荐文章于 2024-08-16 06:33:38 发布

著名抢人头选手

最新推荐文章于 2024-08-16 06:33:38 发布

阅读量230

点赞数

文章标签： mysql 数据库 linux

本文链接：https://blog.youkuaiyun.com/weixin_72901306/article/details/126004542

版权

本文介绍了MySQL的二进制格式安装步骤，包括创建用户和组、初始化数据库、配置服务启动脚本等。同时，讲解了MySQL配置文件的路径与优先级，并详细阐述了如何破解数据库密码，包括编辑配置文件、重启服务、修改密码等步骤。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

mysql进阶

二进制格式mysql安装

[root@localhost ~]# cd /usr/src/
[root@localhost src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
--2022-07-26 20:25:27--  https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 23.41.76.226, 2600:140e:6:9a3::2e31, 2600:140e:6:9bc::2e31
Connecting to downloads.mysql.com (downloads.mysql.com)|23.41.76.226|:443... connected.
HTTP request sent, awaiting response... 302 Moved Temporarily
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz [following]
--2022-07-26 20:25:28--  https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
Resolving cdn.mysql.com (cdn.mysql.com)... 96.17.188.242
Connecting to cdn.mysql.com (cdn.mysql.com)|96.17.188.242|:443... connected.
HTTP request sent, awaiting response... 200 OK
Length: 674830866 (644M) [application/x-tar-gz]
Saving to: ‘mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz’

mysql-5.7.38-linux-gl 100%[======================>] 643.57M   673KB/s    in 7m 58s  

2022-07-26 20:33:28 (1.35 MB/s) - ‘mysql

//创建用户和组

[root@localhost src]# ls
debug  kernels  mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]#  tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -
tar: -: Not found in archive
tar: Exiting with failure status due to previous errors
[root@localhost src]#  tar xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# cd
[root@localhost ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.38-linux-glibc2.12-x86_64  share
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.38-linux-glibc2.12-x86_64/'
[root@localhost 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 Jul 20 23:58 lib64
drwxr-xr-x. 2 root root   6 May 19  2020 libexec
lrwxrwxrwx. 1 root root  36 Jul 26 21:14 mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 26 21:13 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 Jul 20 23:58 share
drwxr-xr-x. 2 root root   6 May 19  2020 src
[root@localhost local]#

//修改目录/usr/local/mysql的属主属组

[root@localhost local]# chown -R mysql.mysql /usr/local/mysql
[root@localhost local]# ll /usr/local/mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 26 21:14 /usr/local/mysql -> mysql-5.7.38-linux-glibc2.12-x86_64/

//添加环境变量

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

//建立数据存放目录

[root@localhost local]# mkdir /opt/data
[root@localhost local]# chown -R mysql.mysql /opt/data
[root@localhost local]# ll /opt/
total 0
drwxr-xr-x. 2 mysql mysql 6 Jul 26 21:19 data
[root@localhost local]#

//初始化数据库

[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql--datadir=/opt/data/
2022-07-26T13:28:00.883039Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-26T13:28:00.885304Z 0 [ERROR] Fatal error: Can't change to run as user 'mysql--datadir=/opt/data/' ;  Please check that the user exists!

2022-07-26T13:28:00.885317Z 0 [ERROR] Aborting

[root@localhost local]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2022-07-26T13:29:06.911297Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-26T13:29:07.130910Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-26T13:29:07.164107Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-26T13:29:07.220740Z 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: ecbd809f-0ce6-11ed-97c7-000c2960d6c8.
2022-07-26T13:29:07.222679Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-26T13:29:07.479260Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-26T13:29:07.479273Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-26T13:29:07.479626Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-26T13:29:07.521943Z 1 [Note] A temporary password is generated for root@localhost: Ka+Ywg!yG5tf
[root@localhost local]#

//生成配置文件

[root@localhost local]# vim /etc/my.cnf
[root@localhost local]# 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
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 local]# cp -a /usr/local/mysql/support-files/mysql.server /etc/init.d/mysqld
[root@localhost local]# sed -ri 's#^(basedir=).*#\1/usr/local/mysql#g' /etc/init.d/mysqld
[root@localhost local]# sed -ri 's#^(datadir=).*#\1/opt/data#g' /etc/init.d/mysqld
[root@localhost local]#

//启动mysql

[root@localhost local]# service mysqld start
Starting MySQL. SUCCESS! 
[root@localhost local]#  ps -ef | grep mysql
root        2334       1  0 21:41 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql       2536    2334  0 21:41 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        2566    1549  0 21:42 pts/0    00:00:00 grep --color=auto mysql
[root@localhost local]# 
[root@localhost local]# ss -anlt
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        80                      *:3306                  *:*                
LISTEN   0        128                  [::]:22                 [::]:*                
[root@localhost local]#

设置mysql服务开机自启

[root@localhost local]# chkconfig --add mysqld
[root@localhost local]#  chkconfig mysqld on
[root@localhost local]# 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@localhost local]#

//修改密码
//下载libncurses.so.5,使用临时密码登录

[root@localhost local]# mysql -p
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
[root@localhost local]# yum whatprovides libncurses.so.5
Last metadata expiration check: 1:02:31 ago on Tue 26 Jul 2022 08:46:41 PM CST.
ncurses-compat-libs-6.1-7.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : baseos
Matched from:
Provide    : libncurses.so.5

ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
Repo        : baseos
Matched from:
Provide    : libncurses.so.5

[root@localhost local]# yum install -y ncurses-compat-libs
Last metadata expiration check: 1:03:19 ago on Tue 26 Jul 2022 08:46:41 PM CST.
Dependencies resolved.
=====================================================================================
 Package                   Architecture Version                   Repository    Size
=====================================================================================
Installing:
 ncurses-compat-libs       x86_64       6.1-9.20180224.el8        baseos       328 k
Upgrading:
 ncurses                   x86_64       6.1-9.20180224.el8        baseos       387 k
 ncurses-base              noarch       6.1-9.20180224.el8        baseos        81 k
 ncurses-libs              x86_64       6.1-9.20180224.el8        baseos       334 k

Transaction Summary
=====================================================================================
Install  1 Package
Upgrade  3 Packages

Total download size: 1.1 M
Downloading Packages:
(1/4): ncurses-compat-libs-6.1-9.20180224.el8.x86_64 1.5 MB/s | 328 kB     00:00    
(2/4): ncurses-6.1-9.20180224.el8.x86_64.rpm         1.6 MB/s | 387 kB     00:00    
(3/4): ncurses-libs-6.1-9.20180224.el8.x86_64.rpm    3.0 MB/s | 334 kB     00:00    
(4/4): ncurses-base-6.1-9.20180224.el8.noarch.rpm    129 kB/s |  81 kB     00:00    
-------------------------------------------------------------------------------------
Total                                                371 kB/s | 1.1 MB     00:03     
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
  Preparing        :                                                             1/1 
  Upgrading        : ncurses-base-6.1-9.20180224.el8.noarch                      1/7 
  Upgrading        : ncurses-libs-6.1-9.20180224.el8.x86_64                      2/7 
  Upgrading        : ncurses-6.1-9.20180224.el8.x86_64                           3/7 
  Installing       : ncurses-compat-libs-6.1-9.20180224.el8.x86_64               4/7 
  Cleanup          : ncurses-6.1-7.20180224.el8.x86_64                           5/7 
  Cleanup          : ncurses-libs-6.1-7.20180224.el8.x86_64                      6/7 
  Cleanup          : ncurses-base-6.1-7.20180224.el8.noarch                      7/7 
  Running scriptlet: ncurses-base-6.1-7.20180224.el8.noarch                      7/7 
  Verifying        : ncurses-compat-libs-6.1-9.20180224.el8.x86_64               1/7 
  Verifying        : ncurses-6.1-9.20180224.el8.x86_64                           2/7 
  Verifying        : ncurses-6.1-7.20180224.el8.x86_64                           3/7 
  Verifying        : ncurses-base-6.1-9.20180224.el8.noarch                      4/7 
  Verifying        : ncurses-base-6.1-7.20180224.el8.noarch                      5/7 
  Verifying        : ncurses-libs-6.1-9.20180224.el8.x86_64                      6/7 
  Verifying        : ncurses-libs-6.1-7.20180224.el8.x86_64                      7/7 
Installed products updated.

Upgraded:
  ncurses-6.1-9.20180224.el8.x86_64         ncurses-base-6.1-9.20180224.el8.noarch   
  ncurses-libs-6.1-9.20180224.el8.x86_64   

Installed:
  ncurses-compat-libs-6.1-9.20180224.el8.x86_64                                      

Complete!
[root@localhost local]#

//设置新密码

[root@localhost ~]# mysql -p'Ka+Ywg!yG5tf'

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.38

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('leg9229');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql配置文件

mysql 的配置文件为 /etc/my.cnf
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效
/etc/my.cnf –> /etc/mysql/my.cnf –> –defaultextrafile=/PATH

mysql常用配置文件参数:

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

[root@localhost ~]# vim .my.cnf

[client]
user=root
password=leg9229
[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 3
Server version: 5.7.38 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
添加一行： skipgranttables

[root@localhost ~]# rm -f .my.conf
[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-tablessql-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@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS! 
Starting MySQL. SUCCESS! 
[root@localhost ~]#

3、验证登入mysql

[root@localhost ~]# mysql
Welcome to the MySQL monitor.  Commands end with ; or \g.
Your MySQL connection id is 2
Server version: 5.7.38 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>

4、修改密码

mysql> update mysql.user set authentication_string=password('123456')where User = 'root';
Query OK, 1 row affected, 1 warning (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 1

mysql>

5、删除修改/etc/my.cnf中skipgranttables

[root@localhost ~]# vim /etc/my.cnf
[root@localhost ~]# 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
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 ~]#

6、重启mysql服务

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

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

[root@localhost ~]# mysql -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.38 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>