MySQL二进制安装

Blue Dream~

已于 2022-07-31 12:26:29 修改

阅读量318

点赞数

文章标签： mysql 数据库 linux

于 2022-07-26 23:47:08 首次发布

本文链接：https://blog.csdn.net/cxyxt/article/details/126005516

版权

mysql进阶

二进制格式mysql安装
mysql配置文件
多表联合查询
3.1 什么是多表联合查询
3.2 交叉连接(CROSS JOIN)
3.2.1 笛卡尔积
3.2.2 交叉连接
3.3 内连接
3.4 外连接
3.4.1 左连接
3.4.2 右连接
3.5 分组查询
3.5.1 GROUP BY单独使用
3.5.2 GROUP BY 与 GROUP_CONCAT()
3.5.3 GROUP BY 与聚合函数
3.5.4 GROUP BY 与 WITH ROLLUP
3.6 子查询
mysql数据库备份与恢复
4.1 数据库常用备份方案
4.2 mysql备份工具mysqldump
4.3 mysql数据恢复
4.4 差异备份与恢复
4.4.1. mysql差异备份
4.4.2. mysql差异备份恢复
//生成配置文件
二进制格式mysql安装
//下载二进制格式的mysql软件包

[root@localhost ~]# cd /usr/src/
[root@localhost src]# wget https://downloads.mysql.com/archives/get/file/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
--2018-08-13 23:56:27--  https://downloads.mysql.com/archives/get/file/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
Resolving downloads.mysql.com (downloads.mysql.com)... 137.254.60.14
Connecting to downloads.mysql.com (downloads.mysql.com)|137.254.60.14|:443... connected.
HTTP request sent, awaiting response... 302 Found
Location: https://cdn.mysql.com/archives/mysql-5.7/mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz [following]
......
Saving to: ‘mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz’

100%[=====================================>] 643,790,848 2.46MB/s   in 4m 20s

2018-08-14 00:00:50 (2.36 MB/s) - ‘mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz’saved [643790848/643790848]

//创建用户和组

[root@localhost src]# groupadd -r mysql
[root@localhost src]# useradd -M -s /sbin/nologin -g mysql mysql

//解压软件至/usr/local/

[root@http src]# tar xf mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz -C /usr/local
[root@http src]# ls
debug    kernels  mysql-5.7.37-linux-glibc2.12-x86_64.tar.gz
[root@localhost ~]# ls /usr/local/
bin  games    lib    libexec                              sbin   src
etc  include  lib64  mysql-5.7.37-linux-glibc2.12-x86_64  share
[root@localhost ~]# cd /usr/local/
[root@http local]# ln -sv mysql-5.7.37-linux-glibc2.12-x86_64/ mysql
'mysql' -> 'mysql-5.7.37-linux-glibc2.12-x86_64/'
[root@http 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 27 22:30 lib64
drwxr-xr-x.  2 root root   6 Jun 22  2021 libexec
lrwxrwxrwx.  1 root root  36 Jul 26 19:04 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x.  9 root root 129 Jul 26 18:56 mysql-5.7.37-linux-glibc2.12-x86_64
drwxr-xr-x. 11 root root 151 Jul 14 14:51 nginx
drwxr-xr-x.  2 root root   6 Jun 22  2021 sbin
drwxr-xr-x.  5 root root  49 Jun 27 22:30 share
drwxr-xr-x.  2 root root   6 Jun 22  2021 src

//修改目录/usr/local/mysql的属主属组
方便日后进行配置时更好的调用

[root@http local]# chown -R mysql.mysql mysql
[root@http local]# ll mysql -d
lrwxrwxrwx. 1 mysql mysql 36 Jul 26 19:04 mysql -> mysql-5.7.37-linux-glibc2.12-x86_64/

[root@http local]# chown -R mysql.mysql mysql-5.7.37-linux-glibc2.12-x86_64/
drwxr-xr-x.  9 mysql mysql 129 Jul 26 18:56 mysql-5.7.37-linux-glibc2.12-x86_64

//添加环境变量*bin

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

添加 includ 头文件环境 //方便系统日后查询调用

[root@http include]# ln -s /usr/local/mysql/include/ /usr/include/mysql
[root@http include]# ls
lrwxrwxrwx.  1 root root     25 Jul 26 21:37 mysql -> /usr/local/mysql/include/

添加lib环境

[root@http mysql]# vi /etc/ld.so.conf.d/mysql.conf
[root@http mysql]# cat /etc/ld.so.conf.d/mysql.conf 
/usr/local/mysql/lib
[root@http mysql]# ldconfig 
[root@http mysql]# pwd
/usr/local/mysql

添加man环境

[root@http mysql]# vi /etc/man_db.conf
MANDATORY_MANPATH                       /usr/local/mysql/man

//建立数据存放目录

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

//初始化数据库

[root@http mysql]# mysqld --initialize --user=mysql --datadir=/opt/data
2022-07-26T13:41:39.477991Z 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:41:39.798156Z 0 [Warning] InnoDB: New log files created, LSN=45790
2022-07-26T13:41:39.865434Z 0 [Warning] InnoDB: Creating foreign key constraint system tables.
2022-07-26T13:41:39.955341Z 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: ad67b06c-0ce8-11ed-96bd-000c29686282.
2022-07-26T13:41:39.963510Z 0 [Warning] Gtid table is not ready to be used. Table 'mysql.gtid_executed' cannot be opened.
2022-07-26T13:41:41.446521Z 0 [Warning] A deprecated TLS version TLSv1 is enabled. Please use TLSv1.2 or higher.
2022-07-26T13:41:41.446560Z 0 [Warning] A deprecated TLS version TLSv1.1 is enabled. Please use TLSv1.2 or higher.
2022-07-26T13:41:41.447835Z 0 [Warning] CA certificate ca.pem is self signed.
2022-07-26T13:41:41.637838Z 1 [Note] A temporary password is generated for root@localhost: 8)=twcKT1jw!

//请注意，这个命令的最后会生成一个临时密码，此处密码是8)=twcKT1jw!
//再次注意，这个密码是随机的，你的不会跟我一样，一定要记住这个密码，因为一会登录时会用到

//生成配置文件
vi /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			//进程号id
user = mysql
skip-name-resolve					//使用ip连接
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@http mysql]# cd /usr/local/mysql/support-files
[root@http support-files]# ls
magic  mysqld_multi.server  mysql-log-rotate  mysql.server
[root@http support-files]# cp -a mysql.server /etc/init.d/mysqld
[root@http support-files]# vi /etc/init.d/mysqld				//系统启动脚本文件
basedir=/usr/local/mysql
datadir=/opt/data

//启动mysql

[root@http mysql]# service mysqld start
Starting MySQL.Logging to '/opt/data/http.err'.
 SUCCESS! 
[root@http mysql]# ss -antl
State  Recv-Q Send-Q   Local Address:Port   Peer Address:Port Process                                                       
LISTEN 0      128            0.0.0.0:80          0.0.0.0:*                                                                  
LISTEN 0      128            0.0.0.0:22          0.0.0.0:*                                                                  
LISTEN 0      80                   *:3306              *:*                                                                  
LISTEN 0      128               [::]:22             [::]:*                                                                  
[root@http mysql]#

//修改密码

//使用临时密码登录

[root@http ~]# dnf -y install libncurses.so.5*			/安装模块
[root@http ~]# mysql -uroot -p'lki;vg/Sp70i'    //登录临时密码
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

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> ser password = password('12345');				//登录新密码
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ser password = password('12345')' at line 1
mysql> set password = password('12345');
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> exit
Bye

[root@http ~]# mysql -uroot -p12345
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 6
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

配置文件查找次序：若在多个配置文件中均有设定，则最后找到的最终生效

/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将无法正常处理连接请求

数据库破解密码

数据库密码破解：
`1、编辑mysql配置文件vim /etc/my. cnf
添加一行: skip-grant-tables

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

3、验证登入

[root@localhost mysql]# mysql
Welcome to the MariaDB monitor.  Commands end with ; or \g.
Your MariaDB connection id is 1
Server version: 5.5.60-MariaDB MariaDB Server

Copyright (c) 2000, 2018, Oracle, MariaDB Corporation Ab and others.

Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.

mysql >

4、修改密码
use mysql；
update user set authentication_string = Password’123456’where Host = ‘localhost’ and User = ‘root’ ;

mysql > select * from mysql.user\G
mysql > use mysql;
mysql > update user set authentication_string = Password'123456'where Host = 'localhost' and User = 'root' ;

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

[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-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

6.重启服务

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

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 5
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>