第六周作业
1. 简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序
DDL 数据定义语言:create，drop，alter
DML 数据操纵语言：insert，update，delete
DCL 数据控制语言：grant,revoke
DQL 数据查询语言：select
select 查询先后顺序：from -->where -->group by -->having --select -->order by --limit
2. 自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个。
#以ClassId 为分组依据，显示三组的平均年龄
select ClassId,avg(age) from students group by ClassId limit 3;
#以年龄排序后，显示年龄最大的前10位同学的信息
select * from students order by age desc limit 10;
#以classid为分组，显示其平均年龄大于25的班级
select c.class,s.ages from classes c left join (select classid,avg(age) as ages from students group by classid) as s on s.classid=c.classid where s.ages>25;
#取每位同学课程的平均成绩，显示成绩前三名的同学的姓名和平均成绩
select s.stuid,t.name,avg(s.score) from scores s left join students t on s.stuid=t.stuid group by s.stuid order by avg(s.score) desc limit 3;
#显示前5位同学的姓名、课程及成绩
select s.name,r.course,c.score from scores c left join courses r on c.courseid=r.courseid left join students s on c.stuid=s.stuid order by s.name limit 9;
#以classid分组，按人数由高到低显示每班的同学人数
select class,numofstu from classes group by classid order by numofstu desc;
#以gender分组，显示其年龄之和
select gender,sum(age) from students where age >25 group by gender;
#以年龄排序后，显示年龄最大的前10位同学的信息
select * from students order by age desc limit 10;
#查询年龄大于大于20，小于等于25岁的同学信息
select * from students where age>=20 and age<=25 order by age desc;
#显示其成员数最少为3个的班级的同学中年龄大于同班同学平均年龄的同学
select s.*,m.ages from students s left join (select classid,avg(age) as ages,count(classid) as num from students group by classid) as m on m.classid=s.classid where s.age>m.ages and m.num>3 order by s.classid;
3. xtrabackup备份和还原数据库练习
- 下载xtrabackup安装包 https://www.percona.com/downloads
- 安装xtrabackup
yum install percona-xtrabackup-80-8.0.34-29.1.el8.x86_64.rpm -y rpm -ql percona-xtrabackup-80
3. xtrabackup 备份
innobackupex [option] back-root-dir
选项说明:
--uer: #该选项表示备份账号 --password：#该选项表示备份的密码 --host：#该选项表示备份数据库的地址 --database：#选项接受的参数为数据库名，如果要指定多个数据库，需要以空格隔开；如：“xtra_test dba_test” --defaults-file:#该选项指定从哪个文件读取mysql配置，必须放在命令行第一个选项位置 --incremental:#该选项表示创建一个增量备份，需要指定--incremental-basedir --incremental-basedir:#该选项指定为前一次全备份或增量备份的目录，与--incremental同时使用 --incremental-dir:#表示还原时增量备份的目录 --include=name:#指定表名，格式：databasename.tablename
4.还原：
innobackupex --copy-back [选项] BACK-DIR
innobackupex --move-back [选项] [--defaults-group=group-name] BACK-DIR
--copy-back:#做数据恢复时将备份数据文件拷贝到mysql服务器的datadir
--move-back:#这个选项与--copy-back相似，唯一的区别是它不拷贝文件，而是移动文件到目的地。这个选项会移除backup文件，用时必须小心
还原注意事项：
- datadir 目录必须为空，除非指定innobackupex --force-non-empty-directorires选项指定，否则--copy-back选项不会覆盖
- 在restore之前，必须shutdown mysql实例，不能将一个运行中的实例restore到datadir目录中
- 由于文件属性会被保留，大部分情况下需要在启动实例之前将文件的属主改为mysql，这些文件将属于创建备份的用户，执行chown -R mysql:mysql /data/mysql,以上需要在用户调用innobackupex 之前完成
利用xtrabackup8.0 完全备份还原mysql8.0
#安装xtrabackup包 yum install -y percona-xtrabackup-80-8.0.34-29.1.el8.x86_64.rpm #在原主机全量备份数据库到backup mkdir backup xtrabackup -uroot --backup --target-dir=/root/backup/base #将backup复制到目标主机 scp -r backup 192.168.180.129:/root #在目标主机还原 #预准备 xtrabackup --prepare --target-dir=/root/backup/base
#复制到数据目录，数据目录必须为空，否则mysql不能启动 xtrabackup --copy-back --target-dir=/root/backup/base chown -R mysql:mysql /var/lib/mysql systemctl start mysqld systemctl status mysqld
4. 实现mysql主从复制，主主复制和半同步复制
主从复制
主节点配置：
vim /etc/my.cnf.d/mysql-server.cnf [mysqld] server-id=128 log-bin
从节点配置：
vim /etc/my.cnf.d/mysql-server.cnf [mysqld] server-id=129 read_only relay_log=relay_log relay_log_index=relay-log.index
创建有复制权限的用户和账号
mysql>create user repluser@'192.168.%.%' identified by '123456'; mysql>grant replication slave on *.* to repluser@'192.168.%.%';
#从节点执行
CHANGE MASTER TO MASTER_HOST='192.168.180.128', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='rocky8-bin.000001', MASTER_LOG_POS=156; start slave； show slave status;
主主复制
两台master节点配置
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=128 log-bin auto_increment_offset=1 auto_increment_increment=2
mysql>grant replication slave on *.* to repluser@'192.168.%.%';
mysql> CHANGE MASTER TO MASTER_HOST='192.168.180.129',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='rocky8-bin.000001',
-> MASTER_LOG_POS=156;
start slave;
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld] server-id=128 log-bin auto_increment_offset=2 auto_increment_increment=2
mysql> CHANGE MASTER TO MASTER_HOST='192.168.180.128',
-> MASTER_USER='repluser',
-> MASTER_PASSWORD='123456',
-> MASTER_LOG_FILE='rocky8-bin.000002',
-> MASTER_LOG_POS=156;
start slave;
在两台主机上分别创建数据库db1,db2
半同步复制
master配置
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=128 log-bin rpl_semi_sync_master_enabled=on rpl_semi_sync_master_timeout=3000
#安装插件，不然mysql启动不了
mysql> install plugin rpl_semi_sync_master soname 'semisync_master.so';
slave 配置
vim /etc/my.cnf.d/mysql-server.cnf [mysqld] server-id=129 rpl_semi_sync_slave_enabled=on
#安装插件
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
当从节点停止复制线程，在master创建数据库，等待3S才能成功
5. 用mycat实现mysql的读写分离
创建mysql主从数据库
主节点配置：
vim /etc/my.cnf.d/mysql-server.cnf [mysqld] server-id=128 log-bin
从节点配置：
vim /etc/my.cnf.d/mysql-server.cnf [mysqld] server-id=129 read_only relay_log=relay_log relay_log_index=relay-log.index
创建有复制权限的用户和账号
mysql>create user repluser@'192.168.%.%' identified by '123456'; mysql>grant replication slave on *.* to repluser@'192.168.%.%';
#从节点执行
CHANGE MASTER TO MASTER_HOST='192.168.180.128', MASTER_USER='repluser', MASTER_PASSWORD='123456', MASTER_LOG_FILE='rocky8-bin.000001', MASTER_LOG_POS=156; start slave； show slave status;
安装mycat并启动
#安装Java环境
yum install java -y
java -version
#下载安装mycat
mkdir /opt/apps
tar -xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /opt/apps
#配置环境变量
echo 'PATH=/opt/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
source /etc/profile.d/mycat.sh
#启动mycat
mycat start
tail -300f /opt/apps/mycat/logs/wrapper.log #查看日志成功，代表安装成功
#修改mycat 的server.xml文件配置的mycat连接信息
<property name="serverPort">3306</property> <property name="managerPort">9066</property> <property name="idleTimeout">300000</property> <property name="bindIp">0.0.0.0</property> <property name="dataNodeIdleCheckPeriod">300000</property> <property name="frontWriteQueueSize">4096</property> <property name="processors">32</property> <property name="authTimeout">150000</property> <property name="handleDistributedTransactions">0</property> <user name="root" defaultAccount="true"> #连接mycat的用户名 <property name="password">123456</property> #连接mycat的密码 <property name="schemas">TESTDB</property> #数据库名要和schema.xml对应 <property name="defaultSchema">TESTDB</property>
#修改schema.xml实现读写分离
<?xml version="1.0"?> <!DOCTYPE mycat:schema SYSTEM "schema.dtd"> <mycat:schema xmlns:mycat="http://io.mycat/"> <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema> <dataNode name="dn1" dataHost="localhost1" database="hellodb" /> <dataHost name="localhost1" maxCon="1000" minCon="10" balance="1" writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100"> <heartbeat>select user()</heartbeat> <writeHost host="host1" url="192.168.180.128:3306" user="liu" password="123456"> <readHost host="host2" url="192.168.180.129:3306" user="liu" password="123456"/> </writeHost> </dataHost> </mycat:schema>
#上面配置中，balance改为1，表示读写分离。一定要保证liu/123456可以成功登录主从数据库。
#在主服务器创建用户liu并对mycat授权
create user 'liu'@'192.168.180.%' identified by '123456';
grant all on *.* to 'liu'@'192.168.180.%';
flusg privilage;
#测试验证
mysql -uroot -p'123456' -h127.0.0.1 TESTDB
insert into teachers (name,age,gender) values('ning meng',30,'M');
mycat
主库
从库
6. 实现openvpn部署，并且测试通过，输出博客或者自己的文档存档。
1 openvpn server: centos 8.8 eth0:192.168.0.64/24 eth1:192.168.64.128/24 2 内网两台主机 #第一台主机 eth1:192.168.64.129/24 仅主机模式 #第二台主机 eth1:192.168.64.130/24 仅主机模式 3 Windows 客户端 Windows 10
安装openvpn
yum install openvpn -y yum install easy-rsa -y
生成证书
cp /usr/share/doc/openvpn/sample/sample-config-files/server.conf /etc/openvpn/ cp -r /usr/share/easy-rsa/ /etc/openvpn/easy-rsa-server cp /usr/share/doc/easy-rsa/vars.example /etc/openvpn/easy-rsa-server/3/vars #修改证书有效期 vim /etc/openvpn/easy-rsa-server/3/vars #CA证书 set_var EASYRSA_CA_EXPIRE 36500 # 服务器证书 set_var EASYRSA_CERT_EXPIRE 3650
#初始化PKI cd /etc/openvpn/easy-rsa-server/3 ./easyrsa init-pki #创建ca证书 ./easyrsa build-ca nopass openssl x509 -in pki/ca.crt -noout -text #生成服务端私钥文件和请求文件 ./easyrsa gen-req server nopass #颁发服务端证书 ./easyrsa sign server server #创建diffie-hellman 密钥 ./easyrsa gen-dh #生成客户端证书 ./easyrsa gen-req liu nopass ./easyrsa import-req /etc/openvpn/easy-rsa-server/3/pki/reqs/liu.req liu ./easyrsa sign client liu #整理服务端证书 mkdir /etc/openvpn/certs cp pki/ca.crt /etc/openvpn/certs/ cp pki/issued/server.crt /etc/openvpn/certs/ cp pki/private/server.key /etc/openvpn/certs/ cp pki/dh.pem /etc/openvpn/certs/ #整理客户端证书 mkdir /etc/openvpn/client/liu find /etc/openvpn/ \( -name "liu.key" -o -name "liu.crt" -o -name ca.crt \) -exec cp {} /etc/openvpn/client/liu \;
#openvpn 服务端配置文件 grep '^[[:alpha:]].*' /etc/openvpn/ server.conf port 1194 proto tcp dev tun ca /etc/openvpn/certs/ca.crt cert /etc/openvpn/certs/server.crt key /etc/openvpn/certs/server.key dh /etc/openvpn/certs/dh.pem server 10.8.0.0 255.255.255.0 ifconfig-pool-persist ipp.txt push "route 192.168.64.0 255.255.255.0" keepalive 10 120 cipher AES-256-CBC push "compress lz4-v2" user openvpn group openvpn status /var/log/openvpn/openvpn-status.log log-append /var/log/openvpn/openvpn.log verb 3 mute 20 #准备日志目录 gentent passwd openvpn mkdir /var/log/openvpn chown openvpn.openvpn /var/log/openvpn #开启转发 echo net.ipv4.ip_forward=1 >>/etc/sysctl.conf sysctl -p #启动vpn vim /usr/lib/systemd/system/openvpn@.service [Unit] Description=OpenVPN Robust And Highly Flexible Tunneling Application on %I After=network.target [Service] Type=notify PrivateTmp=true ExecStart=/usr/sbin/openvpn --cd /etc/openvpn/ --config %i.conf [Install] WantedBy=multi-user.target systemctl daemon-reload #注意service名称和文件名不一致 systemctl restart openvpn@server
#准备客户端文件
vim client.ovpn client dev tun proto tcp remote 192.168.0.64 1194 resolv-retry infinite nobind #persist-key #persist-tun ca ca.crt cert liu.crt key liu.key remote-cert-tls server #tls-auth ta.key 1 cipher AES-256-CBC verb 3 compress lz4-v2
客户端目录：C:\Users\Administrator\OpenVPN\config\client
openvpn启用安全增强功能
#启用防止DOS攻击的安全增强配置 openvpn --genkey --secret /etc/openvpn/certs/ta.key #修改server.conf tls-auth /etc/openvpn/certs/ta.key 0 #客户端为1，服务端为0 #修改client.ovpn tls-auth ta.key 1
7. mysql如何实现崩溃后恢复？
根据mysql历史备份数据，将数据库还原到最近一次全量备份的时间点，获取全量备份时的二进制日志binlog和pos点，再把对应的时间点之后所有的binlog，将所有数据库操作导出成sql,在全量备份还原库后，将导出的SQL导入到库里
8. myisam和innodb各自在什么场景使用？
myisam适用于只读、表较小的场景，其支持全文索引、压缩、空间函数等特性，是表级锁，不支持事务处理、外键约束、MVCC（多版本并发控制机制）
innodb适用于高并发、高可靠性的应用场景，其支持行级锁、事务处理、外键约束等特性，能够保证数据的一致性和完整性，同时支持MVCC高并发
vim /etc/my.cnf.d/mysql-server.cnf
浙公网安备 33010602011771号