MySQL二进制安装与交叉连接
一、二进制方式安装MySQL
1.下载mysql的二进制包并解压
[root@192 ~]# wget https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.38-linux-glibc2.12-x86_64.tar.xz
[root@192 ~]# tar -xf mysql-5.7.38-linux-glibc2.12-x86_64.tar.xz -C /usr/local/
[root@192 local]# ls
bin etc games include lib lib64 libexec mysql-5.7.38-linux-glibc2.12-x86_64 sbin share src
//将该目录重命名为mysql
[root@192 local]# mv mysql-5.7.38-linux-glibc2.12-x86_64/ mysql
[root@192 local]# ls
bin etc games include lib lib64 libexec mysql sbin share src
2.创建mysql用户和组
[root@192 local]# useradd -Mrs /sbin/nologin mysql
[root@192 local]# id mysql
uid=995(mysql) gid=992(mysql) groups=992(mysql)
3.修改mysql目录的属主和属组为mysql
[root@192 local]# chown -R mysql.mysql /usr/local/mysql/
[root@192 local]# ll -d ./mysql/
drwxr-xr-x 9 mysql mysql 129 Jul 27 15:27 ./mysql/
4.添加环境变量
[root@192 local]# echo 'export PATH=$PATH:/usr/local/mysql/bin' > /etc/profile.d/mysql.sh
[root@192 local]# source /etc/profile.d/mysql.sh
[root@192 local]# which mysql
/usr/local/mysql/bin/mysql
5.给include,lib,man目录做映射
//头文件映射
[root@192 mysql]# ln -s /usr/local/mysql/include/ /usr/include/mysql
//库文件映射
[root@192 mysql]# vim /etc/ld.so.conf.d/mysql.conf
[root@192 mysql]# cat /etc/ld.so.conf.d/mysql.conf
/usr/local/mysql/lib/
[root@192 mysql]# ldconfig
//man帮助手册映射
[root@192 mysql]# vim /etc/man_db.conf
[root@192 mysql]# cat /etc/man_db.conf | grep mysql
MANDATORY_MANPATH /usr/local/mysql/man
6.建立数据存放目录
[root@192 mysql]# mkdir /opt/data
[root@192 mysql]# chown -R mysql.mysql /opt/data/
7.初始化数据库
[root@192 mysql]# /usr/local/mysql/bin/mysqld --initialize --user=mysql --datadir=/opt/data/
2022-07-27T08:07:50.207395Z 0 [System] [MY-013169] [Server] /usr/local/mysql/bin/mysqld (mysql 5.7.38) initializing of server in progress as process 6277
2022-07-27T08:07:50.217247Z 1 [System] [MY-013576] [InnoDB] InnoDB initialization has started.
2022-07-27T08:07:50.751198Z 1 [System] [MY-013577] [InnoDB] InnoDB initialization has ended.
2022-07-27T08:07:51.749521Z 6 [Note] [MY-010454] [Server] A temporary password is generated for root@localhost: =ioU?u?=O6ya //主机名的冒号后面临时密码，密码是随机生成的
7.1初始化数据库可能会遇到的报错
报错信息：
mysql: error while loading shared libraries: libtinfo.so.5: cannot open shared object file: No such file or directory
解决方法：
[root@192 mysql]# dnf -y install ncurses-compat-libs
8.生成配置文件
[root@192 mysql]# vim /etc/my.cnf
[root@192 mysql]# cat /etc/my.cnf
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
9.配置服务启动脚本
[root@192 ~]# cd /usr/lib/systemd/system/
[root@192 system]# cp sshd.service mysqld.service
[root@192 system]# vim mysqld.service
[root@192 system]# cat mysqld.service
[Unit]
Description=MySql server daemon
After=network.target sshd-keygen.target
[Service]
Type=forking
ExecStart=/usr/local/mysql/support-files/mysql.server start
ExecStop=/usr/local/mysql/support-files/mysql.server stop
ExecReload=/bin/kill -HUP $MAINPID
[Install]
WantedBy=multi-user.target
[root@192 system]# systemctl daemon-reload
10.启动mysql
[root@192 system]# systemctl restart mysqld.service
[root@192 system]# ss -anlt | grep 3306
LISTEN 0 80 *:3306 *:*
11.修改密码
[root@192 ~]# mysql -uroot -p'6fk9>mj-nyIk'
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.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('123456'); //修改密码
Query OK, 0 rows affected, 1 warning (0.00 sec)
mysql> flush privileges; //刷新权限表
Query OK, 0 rows affected (0.01 sec)
mysql> exit
Bye
[root@192 ~]# 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.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>
11.1进入数据库可能会遇到的报错
报错信息：
mysql: error while loading shared libraries: libncurses.so.5: cannot open shared object file: No such file or directory
解决方法：
dnf -y install ncurses-compat-libs
二、mysql的配置文件与破解密码
1.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将无法正常处理连接请求 |
2.破解密码
[root@192 ~]# vim /etc/my.cnf
[root@192 ~]# 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
[root@192 ~]# systemctl restart mysqld.service //重启mysql服务让配置生效
[root@192 ~]# mysql //免密登录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> use mysql; //进入mysql库
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
mysql> update user set authentication_string=password('yefeng123') where user='root'; //修改root密码
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
mysql> flush privileges; //刷新授权表
Query OK, 0 rows affected (0.00 sec)
mysql> exit
Bye
[root@192 ~]# vim /etc/my.cnf
[root@192 ~]# 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
[root@192 ~]# systemctl restart mysqld.service //重启服务让配置生效
[root@192 ~]# mysql -uroot -pyefeng123 //测试新密码
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> exit
Bye
三、交叉查询
交叉连接的语法格式如下：
SELECT <字段名> FROM <表1> CROSS JOIN <表2> [WHERE子句];
或
SELECT <字段名> FROM <表1>, <表2> [WHERE子句];
语法说明如下：
- 字段名：需要查询的字段名称。
- <表1><表2>：需要交叉连接的表名。
- WHERE 子句：用来设置交叉连接的查询条件。
1.查询 tb_students_info 表中的数据
SQL 语句和运行结果如下：
mysql> SELECT * FROM tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | 男 | 160 | 1 |
| 2 | Green | 23 | 男 | 158 | 2 |
| 3 | Henry | 23 | 女 | 185 | 1 |
| 4 | Jane | 22 | 男 | 162 | 3 |
| 5 | Jim | 24 | 女 | 175 | 2 |
| 6 | John | 21 | 女 | 172 | 4 |
| 7 | Lily | 22 | 男 | 165 | 4 |
| 8 | Susan | 23 | 男 | 170 | 5 |
| 9 | Thomas | 22 | 女 | 178 | 5 |
| 10 | Tom | 23 | 女 | 165 | 5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
操作流程：
mysql> create database yf character set utf8;
Query OK, 1 row affected (0.00 sec)
mysql> use yf;
Database changed
mysql> create table tb_students_info(id int primary key auto_increment,name varchar(20),age tinyint,sex varchar(6),hei tinyint) charset utf8;
Query OK, 0 rows affected (0.00 sec)
mysql> insert into tb_students_info(name,age,sex,height,course_id) values('Dany',25,'男',160,1),('Green',23,'男',158,2),('Henry',23,'女',185,1),('Jane',22,'男',162,3),('Jin',21,'女',172,4),('John',21,'女',172,4),('Lily',22,'男',165,4),('Susan',23,'男',170,5),('Thomas',22,'女',178,5),('Tom',23,'女',165,5);
Query OK, 10 rows affected (0.01 sec)
Records: 10 Duplicates: 0 Warnings: 0
mysql> select * from tb_students_info;
+----+--------+------+------+--------+-----------+
| id | name | age | sex | height | course_id |
+----+--------+------+------+--------+-----------+
| 1 | Dany | 25 | 男 | 160 | 1 |
| 2 | Green | 23 | 男 | 158 | 2 |
| 3 | Henry | 23 | 女 | 185 | 1 |
| 4 | Jane | 22 | 男 | 162 | 3 |
| 5 | Jin | 21 | 女 | 172 | 4 |
| 6 | John | 21 | 女 | 172 | 4 |
| 7 | Lily | 22 | 男 | 165 | 4 |
| 8 | Susan | 23 | 男 | 170 | 5 |
| 9 | Thomas | 22 | 女 | 178 | 5 |
| 10 | Tom | 23 | 女 | 165 | 5 |
+----+--------+------+------+--------+-----------+
10 rows in set (0.00 sec)
2.查询 tb_course 表中的数据
SQL 语句和运行结果如下：
mysql> SELECT * FROM tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MySQL |
| 3 | Python |
| 4 | Go |
| 5 | C++ |
+----+-------------+
5 rows in set (0.00 sec)
操作流程：
mysql> create table tb_course(id int primary key auto_increment,course_name varchar(20)) charset ut f8;
Query OK, 0 rows affected (0.02 sec)
mysql> insert into tb_course(course_name) values('Java'),('MySQL'),('Python'),('Go'),('C++');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from tb_course;
+----+-------------+
| id | course_name |
+----+-------------+
| 1 | Java |
| 2 | MySQL |
| 3 | Python |
| 4 | Go |
| 5 | C++ |
+----+-------------+
5 rows in set (0.00 sec)
3.查询 tb_course 表中的 id 字段和 tb_students_info 表中的 course_id 字段相等的内容
mysql> select * from tb_students_info as tbs,tb_course as tbc where tbs.course_id = tbc.id;
+----+--------+------+------+--------+-----------+----+-------------+
| id | name | age | sex | height | course_id | id | course_name |
+----+--------+------+------+--------+-----------+----+-------------+
| 1 | Dany | 25 | 男 | 160 | 1 | 1 | Java |
| 2 | Green | 23 | 男 | 158 | 2 | 2 | MySQL |
| 3 | Henry | 23 | 女 | 185 | 1 | 1 | Java |
| 4 | Jane | 22 | 男 | 162 | 3 | 3 | Python |
| 5 | Jin | 21 | 女 | 172 | 4 | 4 | Go |
| 6 | John | 21 | 女 | 172 | 4 | 4 | Go |
| 7 | Lily | 22 | 男 | 165 | 4 | 4 | Go |
| 8 | Susan | 23 | 男 | 170 | 5 | 5 | C++ |
| 9 | Thomas | 22 | 女 | 178 | 5 | 5 | C++ |
| 10 | Tom | 23 | 女 | 165 | 5 | 5 | C++ |
+----+--------+------+------+--------+-----------+----+-------------+
10 rows in set (0.00 sec)
4.查询表中学习java的人
mysql> select tbs.name,tbc.course_name from tb_students_info as tbs,tb_course as tbc where tbs.course_id = tbc.id and tbc.course_name = 'Java';
+-------+-------------+
| name | course_name |
+-------+-------------+
| Dany | Java |
| Henry | Java |
+-------+-------------+
2 rows in set (0.00 sec)
5.查看学习java的人的年龄
mysql> select tbs.name,tbc.course_name,tbs.age from tb_students_info as tbs,tb_course as tbc where tbs.course_id = tbc.id a
nd tbc.course_name = 'Java';
+-------+-------------+------+
| name | course_name | age |
+-------+-------------+------+
| Dany | Java | 25 |
| Henry | Java | 23 |
+-------+-------------+------+
2 rows in set (0.00 sec)
6.查看学习c++的人且大于22岁
mysql> select tbs.name,tbc.course_name,tbs.age from tb_students_info as tbs,tb_course as tbc where tbs.course_id = tbc.id a
nd tbc.course_name = 'C++' and tbs.age > 22;
+-------+-------------+------+
| name | course_name | age |
+-------+-------------+------+
| Susan | C++ | 23 |
| Tom | C++ | 23 |
+-------+-------------+------+
2 rows in set (0.00 sec)
本文含有隐藏内容，请 开通VIP 后查看