第六周作业

1. 简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序

(1)DDL:Data Defination Language 数据定义语言
Create,Drop,Alter
表：二维关系
设计表：遵循规范
定义：字段，索引
(2)DML:Data Manipulation Language 数据操纵语言
Insert,Delete,Update
软件开发 CRUD
(3)DQL:Data Query Language 数据查询语言
Select
(4)DCL:Data Control Language 数据控制语言
Grant,Revoke
(5)mysql各个关键字查询时候的先后顺序
FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause -->SELECT --> ORDER
BY --> LIMIT.
  1.  FROM ...,...,....
  2.  WHERE 多表的连接条件
  3.  AND 不包含组函数的过滤条件
  4.  GROUP BY ...,...
  5.  HAVING 包含组函数的过滤条件
  6. SELECT ...,....,...
  7.  ORDER BY ... ASC/DESC
  8.  LIMIT

2. 自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个。

(1)select classid,count(*) from 数量 from students group by classid limit 3;
(2)select * from students order by age desc limit 3
(3)select classid,avg(age) as 平均年龄 from sutdents where classid > 3 group by classid having 平均年龄 > 30 ;
(4)select gender,avg(age) 平均年龄 from students group by gender having gender='M';
(5)select * from students order by name desc limit 2;
(6)select * from students order by name desc limit 1,7;
(7)select classid,sum(age) from students group by classid  having classid is not null order by classid;
(8)select gender,group_concat(name) from students group by gender limit 0,5;
(9)select classid,count(*) 数量  from students group by classid order by 数量;
(10)select gender,classid,avg(age) from students where classid is not null group by gender,classid order by gender,classid;

3. xtrabackup备份和还原数据库练习

1、备份过程：
#先安装xtrabackup包
yum -y install percona-xtrabackup-80-8.0.23-1.el8.x86_64.rpm
#在原主机做完全备份到/backup
mkdir /backup
xtrabackup -uroot --backup --target-dir=/backup/base

#第一次修改数据
#第一次增量备份
xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc1 --
incremental-basedir=/backup/base

#第二次修改数据
#第二次增量备份
xtrabackup -uroot -pmagedu --backup --target-dir=/backup/inc2 --
incremental-basedir=/backup/inc1

#备份到远程主机
scp -r /backup/* 192.168.50.3:/backup/

2、还原过程：
#预准备完成备份，此选项--apply-log-only   阻止回滚未完成的事务
xtrabackup --prepare --apply-log-only --target-dir=/backup/base
#合并第1次增量备份到完全备份
xtrabackup --prepare --apply-log-only --target-dir=/backup/base 
--incremental-dir=/backup/inc1
#合并第2次增量备份到完全备份，最后一次还原不需要加选项--apply-log-only
xtrabackup --prepare --target-dir=/backup/base --incremental-dir=/backup/inc2

#复制到数据库目录，注意数据库目录必须为空，mysql服务不能启动
xtrabackup --copy-back --target-dir=/backup/base
#还原属性
chown -R mysql:mysql /var/lib/mysql
#启动服务
service mysqld start

 

4. 实现mysql主从复制，主主复制和半同步复制

1、主从复制
#修改master主节点的配置
vim /etc/my.cnf

[mysqld]
server-id=1
log-bin=/data/mysql/logbin/mysql-bin

systemctl restart mysqld

#完全备份
mysqldump -A -F --master-data=1 --single-transaction >data/all.sql

#创建复制用户并授权
mysql
create user repluser@'192.168.50.%' identified by '123456';
grant replication slave on *.* to repluser@'192.168.50.%';

#将备份复制到从节点
scp /data/all.sql 192.168.50.3:/data

#配置从节点
vim /etc/my.cnf
[mysqld]
server-id=2
read-only

systemctl restart mysqld

#从节点修改备份文件
vim /data/all.sql

CHANGE MASTER TO
  MASTER_HOST='主节点',
  MASTER_USER='repluser',
  MASTER_PASSWORD='123456',
  MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000002', MASTER_LOG_POS=156;    #这个数字需要查看主节点二进制文件日志

#从节点还原备份
set sql_log_bin=0;
source /data/all.sql;
set sql_log_bin=1;

#从节点开始复制
start slave;
2、实现两个节点的主主复制
#在第一个master节点上实现
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=1
log-bin
auto_increment_offset=1
auto_increment_increment=2

systemctl restart mysqld


#在第二个节点上实现
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=2
log-bin
auto_increment_offset=2
auto_increment_increment=2

systemctl restart mysqld
mysql
CHANGE MASTER TO
    ->   MASTER_HOST='192.168.50.200',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000002',
    ->   MASTER_LOG_POS=386;    #此数字需要查第一个节点日志
start slave;


#在第一个master上实现
mysql
CHANGE MASTER TO
    ->   MASTER_HOST='192.168.50.3',
    ->   MASTER_USER='repluser',
    ->   MASTER_PASSWORD='123456',
    ->   MASTER_PORT=3306,
    ->   MASTER_LOG_FILE='mariadb-bin.000002',
    ->   MASTER_LOG_POS=344;      #此数字需要查第二个节点日志
start slave;
3、半同步复制
#master服务器配置
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=1
log-bin
rpl_semi_sync_master_enabled=ON
rpl_semi_sync_master_timeout=3000

#slave1服务器配置
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=2
rpl_semi_sync_slave_enabled=ON

#slave2服务器配置
vim /etc/my.cnf.d/mysql-server.cnf
[mysqld]
server-id=3
rpl_semi_sync_slave_enabled=ON


主服务器配置
mysql
INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
SET GLOBAL rpl_semi_sync_master_enabled=1
SET GLOBAL rpl_semi_sync_master_timeout = 3000;

#从服务器配置
mysql
INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
SET GLOBAL rpl_semi_sync_slave_enabled=1;
stop slave;
start slave;

 


5. 用mycat实现mysql的读写分离

1、修改master和slave上的配置文件
#修改master上的my.cnf
vim /etc/my.cnf.d/mariadb-server.cnf
[mysqld]
server-id=1
log-bin

#修改slave上的my.cnf
[mysqld]
server-id=2


 2、master上创建复制用户
mysql -uroot
grant replication slave on *.* to 'repluser'@'192.168.50.%' identified by '123456' ;
flush privileges;
show master status;

3、slaver 上执行
mysql -uroot
change master to
->     MASTER_HOST='192.168.50.200',
->     MASTER_USER='repluser',
->     MASTER_PASSWORD='123456',
->     MASTER_LOG_FILE='mariadb-bin.000001',
->     MASTER_LOG_POS=856;

start slave

show slave status\G

 

 

2、在mysql代理服务器上安装mycat并启动
yum -y install java
wget http://dl.mycat.org.cn/1.6.7.6/20210303094759/Mycat-server-
1.6.7.6-release-20210303094759-linux.tar.gz
wget http://dl.mycat.org.cn/1.6.7.4/Mycat-server-1.6.7.4-release/Mycat-server-
1.6.7.4-release-20200105164103-linux.tar.gz

mkdir /apps
tar xvf Mycat-server-1.6.7.6-release-20210303094759-linux.tar.gz 
-C /apps/
tar xvf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps

echo 'PATH=/apps/mycat/bin:$PATH' > /etc/profile.d/mycat.sh
source /etc/profile.d/mycat.sh

#启动mycat
file /apps/mycat/bin/mycat
mycat start
ss -ntl

#使用默认密码123456来连接mycat
mysql -uroot -p123456 -h 192.168.50.131 -P8066

3、在mycat服务器上修改server.xml文件配置mycat的连接信息
vim /apps/mycat/conf/server.xml

#重新插入
<property name="serverPort">3306</property>
<property name="handlelDistributedTransactions">0</property> 
4、修改schema.xml实现读写分离策略
vim /apps/mycat/conf/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.50.200:3306" user="root"
   password="123456">
         <readHost host="host2" url="192.168.50.3:3306" user="root"
password="123456" />
 </writeHost>
   </dataHost>
</mycat:schema>



#重新启动mycat
mycat restart
5、在后端主服务器创建用户并对mycat授权
mysql -uroot
create databses mycat ;
grant all on *.* to 'root'@192.168.50.%' identified by '123456';
flush privileges;
6、通过通用日志确认实现读写分离
#在mysql中查看通用日志
set global general_log=on;

#在主和从服务器分别启用通用日志，查看读写分离
vim /etc/my.cnf.d/mysql-server.cnf

[mysqld]
general_log=ON

 

posted @ 2022-08-07 23:04  帅气的工藤新一  阅读(63)  评论(0)    收藏  举报