Mysql进阶—二进制安装、联合查询、破解与备份

1. 二进制格式安装mysql

环境说明

# centos 版本
[root@control ~]# cat /etc/redhat-release
CentOS Stream release 8

#Linux内核版本
[root@control ~]# uname -r
4.18.0-365.el8.x86_64

#系统位
[root@control ~]# file /bin/ls
/bin/ls: ELF 64-bit LSB shared object, x86-64, version 1 (SYSV), dynamically linked, interpreter /lib64/ld-linux-x86-64.so.2, for GNU/Linux 3.2.0,

# Mysql 版本号： 5.7.37

安装流程

# 下载  二进制格式的mysql软件包 
[root@control ~]# cd -
/usr/src
[root@control src]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@control src]# ls
debug  kernels  mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@control src]# file mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz 
mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz: gzip compressed data, last modified: Mon Nov 29 20:53:02 2021, from Unix, original size -1628450816


#创建系统用户及组
[root@control src]# useradd -r -M -s  /sbin/nologin mysql 
[root@control src]# id mysql 
uid=989(mysql) gid=988(mysql) 组=988(mysql)


#解压软件至/usr/local/
[root@control src]# ls /usr/local/
bin  etc  games  include  lib  lib64  libexec  sbin  share  src
[root@control ~]# cd /usr/local 
[root@control local]# [root@control local]# tar xf /usr/src/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C ./ 
[root@control local]# ls
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.37-linux-glibc2.12-x86_64  share


#创建软链接 或者重命名
[root@control local]# mv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
[root@control local]# ls
bin  etc  games  include  lib  lib64  libexec  mysql  sbin  share  src


# 修改目录/usr/local/mysql的属主属组
[root@control local]# ll -d mysql
drwxr-xr-x. 9 root root 129 6月  28 14:57 mysql
[root@control local]# chown -R mysql.mysql /usr/local/mysql
[root@control local]# ll -d mysql
drwxr-xr-x. 9 mysql mysql 129 6月  28 14:57 mysql

#添加环境变量bin
[root@control local]# echo 'export PATH=/usr/local/mysql/bin:$PATH' > /etc/profile.d/mysql.sh 
[root@control local]# source /etc/profile.d/mysql.sh
[root@control local]# which mysql
/usr/local/mysql/bin/mysql

#创建软链接 include  
[root@control local]# cd mysql/
[root@control mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@control mysql]# ls /usr/
bin  games  include  lib  lib64  libexec  local  sbin  share  src  tmp
[root@control mysql]# ln -s  /usr/local/mysql/include/ /usr/include/mysql 
[root@control mysql]# ll /usr/include/mysql
lrwxrwxrwx. 1 root root 25 6月  28 16:15 /usr/include/mysql -> /usr/local/mysql/include/

#配置lib库
[root@control mysql]# vim /etc/ld.so.conf.d/mysql.conf 
[root@control mysql]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib
[root@control mysql]# ldconfig

#配置man文档
[root@control mysql]# vim /etc/man_db.conf 
MANDATORY_MANPATH                       /usr/local/mysql/man //添加此行
[root@control mysql]# cat /etc/man_db.conf| grep  mysql
MANDATORY_MANPATH			/usr/local/mysql/man

#建立数据存放目录 注意修改权限
[root@control mysql]# mkdir -p /opt/data
[root@control mysql]# chown -R mysql.mysql /opt/data
[root@control mysql]# ll /opt/
drwxr-xr-x. 2 mysql mysql  6 6月  28 16:56 data

#初始化数据库 保存临时密码
[root@control mysql]# mysqld --initialize --user=mysql --datadir=/opt/data 
2022-06-28T08:59:14.638137Z 0 [Warning] TIMESTAMP with implicit DEFAULT value is deprecated. Please use --explicit_defaults_for_timestamp server option (see documentation for more details).
2022-06-28T08:59:15.858527Z 1 [Note] A temporary password is generated for root@localhost: ,rjeW)=xL2NL

//请注意，这个命令的最后会生成一个临时密码，此处密码是,rjeW)=xL2NL，此密码为随机密码，一定要记住这个密码(建议写入文件)，便于后续初始登陆
[root@control mysql]# cd
[root@control ~]# echo ',rjeW)=xL2NL' > mysql.password 
[root@control ~]# ls
anaconda-ks.cfg  mysql.password

#生成配置文件
[root@control ~]# vim /etc/my.cnf 
[root@control ~]# 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@control ~]# cd /usr/local/mysql 
[root@control mysql]# ls
bin  docs  include  lib  LICENSE  man  README  share  support-files
[root@control mysql]# ls support-files/
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@control mysql]# ll support-files/
总用量 24
-rw-r--r--. 1 mysql mysql   773 11月 30 2021 magic
-rwxr-xr-x. 1 mysql mysql  1061 11月 30 2021 mysqld_multi.server
-rwxr-xr-x. 1 mysql mysql   894 11月 30 2021 mysql-log-rotate
-rwxr-xr-x. 1 mysql mysql 10576 11月 30 2021 mysql.server
[root@control mysql]# cp -a support-files/mysql.server  support-files/mysqld 
[root@control mysql]# chown -R mysql.mysql support-files/mysqld
[root@control mysql]# ll support-files/
总用量 36
-rw-r--r--. 1 mysql mysql   773 11月 30 2021 magic
-rwxr-xr-x. 1 mysql mysql 10576 11月 30 2021 mysqld
-rwxr-xr-x. 1 mysql mysql  1061 11月 30 2021 mysqld_multi.server
-rwxr-xr-x. 1 mysql mysql   894 11月 30 2021 mysql-log-rotate
-rwxr-xr-x. 1 mysql mysql 10576 11月 30 2021 mysql.server
[root@control mysql]# cd support-files/
[root@control support-files]# vim mysqld 

basedir=/usr/local/mysql
datadir=/opt/data    //补充以上两行的内容 

#启动mysql
[root@control ~]# /usr/local/mysql/support-files/mysqld start 
Starting MySQL.Logging to '/opt/data/control.err'.
 SUCCESS!
 [root@control ~]# ps -ef | grep mysqld
root        5020       1  0 17:11 pts/0    00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql       5208    5020  0 17:11 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=control.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root        5244    1917  0 17:12 pts/0    00:00:00 grep --color=auto mysql



#使用临时密码进行初始登录
[root@control ~]# mysql -uroot -p',rjeW)=xL2NL'
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

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

mysql> quit
Bye

[root@control ~]# mysql -uroot -p'rookie123!'
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 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> 

#收尾 设置开机自启 ——创建service文件
[root@control ~]# /usr/local/mysql/support-files/mysqld stop
Shutting down MySQL.. SUCCESS! 

[root@control ~]# cp /usr/lib/systemd/system/sshd.service  mysqld.service 
[root@control ~]# ls
anaconda-ks.cfg  mysqld.service
[root@control ~]# vim mysqld.service
[Unit]
Description=mysqld server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysqld start
ExecStop=/usr/local/mysql/support-files/mysqld stop
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@control ~]# mv mysqld.service  /usr/lib/systemd/system/
[root@control ~]#  reboot 
[root@control ~]# systemctl daemon-reload 
[root@control ~]# systemctl status mysqld
● mysqld.service - mysqld server daemon
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; disabled; vendor preset: disabled)
   Active: inactive (dead

[root@control ~]# systemctl enable --now  mysqld
Created symlink /etc/systemd/system/multi-user.target.wants/mysqld.service → /usr/lib/systemd/system/mysqld.service.
[root@control ~]# systemctl status mysqld
● mysqld.service - mysqld server daemon
   Loaded: loaded (/usr/lib/systemd/system/mysqld.service; enabled; vendor preset: disabled)
   Active: active (running) since Tue 2022-06-28 17:45:14 CST; 1s ago
  Process: 1848 ExecStart=/usr/local/mysql/support-files/mysqld start (code=exited, status=0>
 Main PID: 1861 (mysqld_safe)
    Tasks: 28 (limit: 11175)
   Memory: 236.0M
   CGroup: /system.slice/mysqld.service
           ├─1861 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/o>
           └─2051 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/opt/data>

[root@control ~]# ps -ef| grep mysqld
root        1861       1  0 17:45 ?        00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/opt/data --pid-file=/opt/data/mysql.pid
mysql       2051    1861  1 17:45 ?        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=control.err --pid-file=/opt/data/mysql.pid --socket=/tmp/mysql.sock --port=3306
root        2083    1779  0 17:45 pts/0    00:00:00 grep --color=auto mysqld

相关报错及解决

#报错1：使用临时密码进行mysql初始登录时出现报错 
[root@control ~]# mysql -uroot -p ',rjeW)=xL2NL'
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory

#解决方案 ———找到提供共享库的软件包 安装 即可
[root@control ~]# dnf provides libncurses.so.5

上次元数据过期检查：2:02:37 前，执行于 2022年06月28日 星期二 15时13分17秒。
ncurses-compat-libs-6.1-9.20180224.el8.i686 : Ncurses compatibility libraries
仓库        ：base
匹配来源：
提供    : libncurses.so.5

[root@control ~]# dnf -y install ncurses-compat-libs
运行事务
  准备中  :                                                                              1/1 
  安装    : ncurses-compat-libs-6.1-9.20180224.el8.x86_64                                1/1 
  运行脚本: ncurses-compat-libs-6.1-9.20180224.el8.x86_