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>
