#MySQL进阶
二进制格式mysql安装
//下载二进制格式的mysql软件包
[root@localhost ~]# cd /usr/src/
[root@localhost src]# ls
debug kernels mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@localhost src]# grouadd -r mysql
//创建用户和组
[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql
//解压软件至/usr/local/
[root@localhost src]# tar xfs mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local/
[root@localhost src]# cd
[root@localhost ~]# ll /usr/local/
total 0
drwxr-xr-x. 2 root root 6 Jun 22 2021 bin
drwxr-xr-x. 2 root root 6 Jun 22 2021 etc
drwxr-xr-x. 2 root root 6 Jun 22 2021 games
drwxr-xr-x. 2 root root 6 Jun 22 2021 include
drwxr-xr-x. 2 root root 6 Jun 22 2021 lib
drwxr-xr-x. 3 root root 17 Jun 30 14:35 lib64
drwxr-xr-x. 2 root root 6 Jun 22 2021 libexec
drwxr-xr-x. 9 root root 129 Jul 26 18:20 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Jun 22 2021 sbin
drwxr-xr-x. 5 root root 49 Jun 30 14:35 share
drwxr-xr-x. 2 root root 6 Jun 22 2021 src
[root@localhost ~]# id mysql
uid=1001(mysql) gid=974(mysql) groups=974(mysql)
[root@localhost ~]# cd /usr/local/
[root@localhost local]# ln -sv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.37-linux-glibc2.12-x86_64/'
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 Jun 22 2021 bin
drwxr-xr-x. 2 root root 6 Jun 22 2021 etc
drwxr-xr-x. 2 root root 6 Jun 22 2021 games
drwxr-xr-x. 2 root root 6 Jun 22 2021 include
drwxr-xr-x. 2 root root 6 Jun 22 2021 lib
drwxr-xr-x. 3 root root 17 Jun 30 14:35 lib64
drwxr-xr-x. 2 root root 6 Jun 22 2021 libexec
lrwxrwxrwx. 1 root root 36 Jul 26 18:35 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 root root 129 Jul 26 18:20 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Jun 22 2021 sbin
drwxr-xr-x. 5 root root 49 Jun 30 14:35 share
drwxr-xr-x. 2 root root 6 Jun 22 2021 src
//修改目录/usr/local/mysql的属主属组
[root@localhost local]# chown -R mysql.mysql mysql
[root@localhost local]# ll mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 26 18:35 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
//把原文件也改一下
[root@localhost local]# chown -R mysql.mysql mysql-5.7.37-linux-glibc2.12-x86_64/
[root@localhost local]# ll
total 0
drwxr-xr-x. 2 root root 6 Jun 22 2021 bin
drwxr-xr-x. 2 root root 6 Jun 22 2021 etc
drwxr-xr-x. 2 root root 6 Jun 22 2021 games
drwxr-xr-x. 2 root root 6 Jun 22 2021 include
drwxr-xr-x. 2 root root 6 Jun 22 2021 lib
drwxr-xr-x. 3 root root 17 Jun 30 14:35 lib64
drwxr-xr-x. 2 root root 6 Jun 22 2021 libexec
lrwxrwxrwx. 1 mysql mysql 36 Jul 26 18:35 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x. 9 mysql mysql 129 Jul 26 18:20 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 2 root root 6 Jun 22 2021 sbin
drwxr-xr-x. 5 root root 49 Jun 30 14:35 share
drwxr-xr-x. 2 root root 6 Jun 22 2021 src
//添加环境变量
[root@localhost mysql]# echo 'export PATH=$PATH:/usr/local/mysql/bin ' > /etc/profile.d/mysql.sh
[root@localhost mysql]# source /etc/profile.d/mysql.sh
[root@localhost mysql]# echo $PSTH
[root@localhost mysql]# echo $PATH
/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin:/usr/local/mysql/bin
[root@localhost mysql]# which mysql
/usr/local/mysql/bin/mysql
[root@localhost mysql]# ln -s /usr/local/mysql/include /usr/include/mysql
[root@localhost mysql]# ll /usr/include/
total 0
drwxr-xr-x. 4 root root 34 Jun 30 14:40 gnome-boxes
lrwxrwxrwx. 1 root root 24 Jul 26 18:45 mysql -> /usr/local/mysql/include
drwxr-xr-x. 2 root root 27 Jun 30 14:36 python3.6m
[root@localhost mysql]# ls
LICENSE README bin docs include lib man share support-files
[root@localhost mysql]# vim /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
~
~
[root@localhost mysql]# ldconfig
[root@localhost mysql]# pwd
/usr/local/mysql
[root@localhost 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@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 26 18:51 /opt/data
[root@localhost mysql]#
//初始化数据库
[root@localhost ~]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2022-07-26T10:55:54.471606Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-07-26T10:55:54.676138Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-26T10:55:54.705693Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-26T10:55:54.710232Z 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: 8593b802-0cd1-11ed-a041-000c29087110.
2022-07-26T10:55:54.710845Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-26T10:55:55.672624Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-26T10:55:55.672650Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-26T10:55:55.673043Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-26T10:55:56.093853Z 1 [Note] A temporary password is generated for root@localhost: yr1H3ent#6kn
[root@localhost ~]# echo 'yr1H3ent#6kn' > pass
[root@localhost ~]# cat pass
yr1H3ent#6kn
//请注意，这个命令的最后会生成一个临时密码，此处密码是yr1H3ent#6kn
//再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到
//生成配置文件
[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 ~]# cd /usr/local/mysql
[root@localhost mysql]# cd support-files/
[root@localhost support-files]# ls
magic mysql-log-rotate mysql.server mysqld_multi.server
[root@localhost support-files]# cp -a mysql.server /etc/init.d/mysqld
[root@localhost support-files]# vim /etc/init.d/mysqld
# overwritten by settings in the MySQL configuration files.
basedir=/usr/local/mysql
datadir=/opt/data
# Default value, in seconds, afterwhich the script should timeout waiting
//启动mysql
[root@localhost ~]# service mysqld start
Starting MySQL.Logging to '/opt/data/localhost.localdomain.err'.
SUCCESS!
[root@localhost ~]# ss -antl
State Recv-Q Send-Q Local Address:Port Peer Address:Port Process
LISTEN 0 128 0.0.0.0:111 0.0.0.0:*
LISTEN 0 32 192.168.122.1:53 0.0.0.0:*
LISTEN 0 128 0.0.0.0:22 0.0.0.0:*
LISTEN 0 5 127.0.0.1:631 0.0.0.0:*
LISTEN 0 80 *:3306 *:*
LISTEN 0 128 [::]:111 [::]:*
LISTEN 0 128 [::]:22 [::]:*
LISTEN 0 5 [::1]:631 [::]:*
[root@localhost ~]# ps -ef | grep mysqld
root 190654 1 0 19:22 pts/1 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql 190899 190654 0 19:22 pts/1 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 192393 8009 0 19:22 pts/1 00:00:00 grep --color=auto mysqld
[root@localhost ~]# chkconfig --add mysqld
[root@localhost ~]# chkconfig mysqld on
[root@localhost ~]# 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
//修改密码
//使用临时密码登录
先查找libncurses.so.5然后在下载
[root@localhost ~]# yum whatprovides libncurses.so.5
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:39:48 ago on Tue Jul 26 18:48:05 2022.
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 ~]# yum -y install ncurses-compat-libs
Failed to set locale, defaulting to C.UTF-8
Last metadata expiration check: 0:40:25 ago on Tue Jul 26 18:48:05 2022.
Dependencies resolved.
================================================================================================================
Package Architecture Version Repository Size
================================================================================================================
Installing:
ncurses-compat-libs x86_64 6.1-9.20180224.el8 baseos 328 k
Transaction Summary
================================================================================================================
Install 1 Package
Total download size: 328 k
Installed size: 1.0 M
Downloading Packages:
ncurses-compat-libs-6.1-9.20180224.el8.x86_64.rpm 1.2 MB/s | 328 kB 00:00
----------------------------------------------------------------------------------------------------------------
Total 378 kB/s | 328 kB 00:00
CentOS Stream 8 - BaseOS 1.6 MB/s | 1.6 kB 00:00
Importing GPG key 0x8483C65D:
Userid : "CentOS (CentOS Official Signing Key) <security@centos.org>"
Fingerprint: 99DB 70FA E1D7 CE22 7FB6 4882 05B5 55B3 8483 C65D
From : /etc/pki/rpm-gpg/RPM-GPG-KEY-centosofficial
Key imported successfully
Running transaction check
Transaction check succeeded.
Running transaction test
Transaction test succeeded.
Running transaction
Preparing : 1/1
Installing : ncurses-compat-libs-6.1-9.20180224.el8.x86_64 1/1
Running scriptlet: ncurses-compat-libs-6.1-9.20180224.el8.x86_64 1/1
Verifying : ncurses-compat-libs-6.1-9.20180224.el8.x86_64 1/1
Installed:
ncurses-compat-libs-6.1-9.20180224.el8.x86_64
Complete!
[root@localhost ~]# mysql -uroot -p'yr1H3ent#6kn'
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('123');
Query OK, 0 rows affected, 1 warning (0.01 sec)
mysql> exit
Bye
[root@localhost ~]# mysql -uroot -p123
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>
mysql配置文件
mysql的配置文件为/etc/my.cnf
[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 4
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>
配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效
/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 | 定进程ID文件存放路径 |
| datadir = /data/mysql | 指定MySQL的数据存放路径 |
| pid-file = /data/mysql/mysql.pid | 定进程ID文件存放路径 |
| user = mysql | 指定MySQL以什么用户的身份提供服务 |
| skip-name-resolve | 禁止MySQL对外部连接进行DNS解析使用这一选项可以消除MySQL进行DNS解析的时间。若开启该选项，则所有远程主机连接授权都要使用IP地址方式否则MySQL将无法正常处理连接请求 |
| 破解密码 |
1、编辑mysql配置文件
[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
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、重启服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
3、验证登入
[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.
4、修改密码
mysql>
mysql> update user set authentication_string = Password('123456') where Host = 'localhost' and User = 'root';
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> exit
Bye
5、删除修改/etc/my.cnf中skip-grant-tables
[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
6、重启mysql服务
[root@localhost ~]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL. SUCCESS!
7、用新的密码去验证登入
[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>
扫一扫
538
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
