mysql项目实战

最新推荐文章于 2025-09-16 20:14:03 发布

原创最新推荐文章于 2025-09-16 20:14:03 发布 · 1k 阅读

18 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #数据库

1.环境搭建

创建两个节点master和slave，先在主节点上操作，从节点后续做主从复制等做准备。

创建好后修改一下配置文件内容，连接final shell 操作更加方便

我下载的是之前的centos镜像好像是不能用了源有问题安装mysql时候依赖包有问题，后面换了阿里云的镜像就可以了

mv /etc/yum.repos.d/CentOS-Base.repo /etc/yum.repos.d/CentOS-Base.repo.backup

wget -O /etc/yum.repos.d/CentOS-Base.repo https://mirrors.aliyun.com/repo/Centos-7.repo

安装mysql后初始化密码准备工作

2.创建基础表和表关系

一共是教师班级学生课程选课五张表

考虑到多张表id的区分，还是选择用字符串类型不同的表id用首字母加数字容易区分，所以修改一下字段类型

批量插入数据：（批量插入效率比单条插入更高，减少网络交互）
老师表：
insert into teacher VALUES ('J1','王炯','教授'),('J2','蒋松','讲师'),('J3','梁王','副教授'),('J4','杨旺权','讲师'),('J5','超哥','讲师');

班级表：
insert into class VALUES ('B1','大数据1',40),('B2','大数据2',45),('B3','大数据3',50),('B4','软件开发1',30),('B5','软件开发2',35),('B6','软件开发3',38);

课程表：
insert into course VALUES ('K1','数据库',4,'J2'),('K2','java',4,'J3'),('K3','python',6,'J1'),('K4','英语',2,'J5'),('K5','数学',6,'J4');

学生表：
insert into stu VALUES ('X5','张三','男','B2'),('X4','李四','女','B3'),('X003','王五','男','B4'),('X2','赵六','男','B5'),('X1','王琦','男','B1'),('X6','马超','女','B1'),('X7','刘备','男','B2'),('X8','赵云','女','B6');

选课表：
insert into coustu VALUES ('X1','K2',82),('X2','K5',56),('X2','K3',74),('X3','K1',66),('X3','K2',75),('X4','K5',95),('X4','K4',83),('X5','K1',42),('X5','K4',87),('X7','K1',79);

建立外键确保数据一致性：课程表里面有包含教师的id，学生表里面有包含班级的id。所以在课程表和教师表以及学生表和班级表之间建立外键：

查询选课成绩在80-90分之间的学生信息：

查询某个学生的课程及对应分数信息：

创建存储过程：

delimiter $$
create procedure stuinformation(in stu_name varchar(50))
begin
    select concat('学生 :', stu_name, ' ,课程：', c.name, ',分数:', cs.score)
    from course c
    join coustu cs on c.id = cs.cid
    join stu s on s.id = cs.sid
    where s.name = stu_name;
end $$
delimiter ;

注：如果用声明局部变量，一个学生可以有多门课程信息，而into只能赋给单行单列的结果给局部变量所以结果有多行会出现报错

不用into变量方式，直接通过多表连接查询拼接结果。

3.导入千万级数据表，为后续优化做准备

用Python生成一个一千万数据量文件，导入到liunx再导入到表。后续做查询优化表

结果：

mysql建表语句：

CREATE TABLE bigdata (
用户ID INT PRIMARY KEY,
姓名 VARCHAR(50),
年龄 INT,
邮箱 VARCHAR(100),
手机号 VARCHAR(20)
);

刚开始导入数据的时候总是报错，第一个是检查的local_infile开关是否是打开的，还有检查字段跟文件里的这段是否对应。排除这些问题后还是出现了报错，发现是因为mysql的默认允许导入的路径这里，MySQL 为了安全，通过 secure_file_priv 变量限制 LOAD DATA INFILE（以及 SELECT ... INTO OUTFILE 等操作）能访问的文件路径，所以这里方把文件放到 secure_file_priv 允许的路径，

解决方法：查看参数的默认路径：SHOW VARIABLES LIKE 'secure_file_priv'
，再把这个数据文件复制到默认路径的目录sudo cp /root/million_users2.csv /var/lib/mysql-files/，把代码里的文件路径做好修改之后就可以正常导入数据了

尝试n多次终于导入成功了

4.创建视图

视图可以为保护用户隐私信息查询屏蔽不需要的字段，创建一个新用户表，创建两个视图us和us1：

us1视图基于us视图创建，并设置级联检查with cascaded check option，修改视图时候会检查us1的where之后的条件（id>5）以及 us视图where之后的条件(id<20)。满足两个视图的条件才能修改(插入id=21满足us1但未满足us条件)

5.主从复制

首先关闭防火墙服务，我这是自己的机器直接就关闭了生产环境中可以开放指定的端口号

修改主从库配置文件my.cnf，重启mysql服务

创建远程连接账号，赋予主从复制权限

创建用户认证出错，mysql_native_password 插件这里未启用。这是旧版本的插件

解决方法：这里改为

caching_sha2_password 插件更安全可用。起初密码设置过于简单，默认的是需要大小写字母数字加特殊字符

执行主库配置后 io线程没起来

查看锁错误日志：

查看报错信息是因为 mysql 8.0 以上默认使用的 caching_sha2_password 插件需要安全连接，而当前配置未满足要求。

我还是继续强制启用原来的插件

解决方法：在主库修改配置文件my.cnf中添加：mysql_native_password=ON

重启mysql服务查看插件状态发现已启用

再重新修改用户认证插件为mysql_native_password ，刷新权限

主库查看二进制日志文件位置信息后

从库执行：change replication source to source_host='192.168.88.145',source_user='junfeng',source_password='Ljf123456.',source_log_file='binlog.000006',source_log_pos=610;
配置成功！