第六周
1. 简述DDL,DML,DCL,DQL，并且说明mysql各个关键字查询时候的先后顺序
DDL: Data Defination Language 数据定义语言
CREATE，DROP，ALTER
DML: Data Manipulation Language 数据操纵语言
INSERT，DELETE，UPDATE
软件开发：CRUD
DQL：Data Query Language 数据查询语言
SELECT
DCL：Data Control Language 数据控制语言
GRANT，REVOKE
2. 自行设计10个sql查询语句，需要用到关键字[GROUP BY/HAVING/ORDER BY/LIMIT]，至少同时用到两个。
select sname,count(1) from student group by sname having count(1) > 1;
select * from student left join class on student.class_id = class.cid where class.caption="三年二班";
SELECT
student.sid,
student.sname
FROM
score
LEFT JOIN student ON score.student_id = student.sid
GROUP BY
student_id
HAVING
count( 1 ) = ( SELECT count( 1 ) FROM course );
3. xtrabackup备份和还原数据库练习
[root@centos8 ~]#yum -y install yum -y install percona-xtrabackup-80-8.0.29-22.1.el8.x86_64.rpm
2 在原主机做完全备份到/backup
[root@centos8 ~]#mkdir /backup
[root@centos8 ~]#xtrabackup -uroot -pmagedu --backup --target-dir=/backup/base
grant BACKUP_ADMIN on *.* to 'root'@'%'; #处理账号没有BACKUP_ADMIN权限
[root@centos8 ~]#scp -r /backup/ 目标主机:/
3 在目标主机上还原
注意：恢复主机MySQL服务停止，并且数据目录为空
1）预准备：确保数据一致，提交完成的事务，回滚未完成的事务
[root@centos8 ~]#yum -y install percona-xtrabackup-80-8.0.23-16.1.el8.x86_64.rpm
[root@centos8 ~]#xtrabackup --prepare --target-dir=/backup/base
2）复制到数据库目录
注意：数据库目录必须为空，MySQL服务不能启动
[root@centos8 ~]#xtrabackup --copy-back --target-dir=/backup/base
3）还原属性
[root@centos8 ~]#chown -R mysql:mysql /var/lib/mysql
4）启动服务
[root@centos8 ~]#service mysqld start
4. 实现mysql主从复制，主主复制和半同步复制
主节点
#修改master主节点的配置
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
server-id=8（id唯一）
log-bin=/data/mysql/logbin/mysql-bin /data/mysql/logbin/mysql-bin(此目录提前创建，修改所有者，所属组（mysql）)
systemctl restart mysqld
#创建复制用户并授权
mysql>create user repluser@"10.0.0.%（远程主机）" identified by "123456"
mysql>grant replication slave on *.* to repluser@"10.0.0.%"";
#完全备份
[root@centos8 ~]#mysqldump -A -F --master-data=1 --single-transaction >
/backup/all.sql
#查看主节点线程
mysql>show processlist;
#配置从节点
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
server-id=18
read-only
[root@centos8 ~]#systemctl restart mysqld
#将备份复制到从节点
[root@centos8 ~]#scp /backup/all.sql 从节点:/backup
#从节点修改备份文件
[root@centos8 ~]#vim /backup/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;
#从节点还原备份
mysql> set sql_log_bin=0;
mysql> source /data/all.sql;
mysql>set sql_log_bin=1;
show slave status\G; 查看线程信息
#从节点开启复制
mysql> start slave;
二进制mysql8.0默认开启
#主服务器配置:
mysql>INSTALL PLUGIN rpl_semi_sync_master SONAME 'semisync_master.so';
#master服务器配置
[root@master ~]#vim /etc/my.cnf
[mysqld]
server-id=8
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=3000 设置3s内无法同步，也将返回成功信息给客户端
systemctl restart mysqld
#创建复制用户并授权
mysql>create user repluser@"10.0.0.%" identified by "123456"
mysql>grant replication slave on *.* to repluser@"10.0.0.%"";
然后备份并将备份文件发送到从节点上
第一个从节点
#配置从节点
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
server-id=18
systemctl restart mysqld
#从服务器配置:
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
#vim /etc/my.cnf
[mysqld]
rpl_semi_sync_slave_enable
重启服务
查看备份的位置
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;
mysql < /data/all.sql
mysql> start slave；
同理第二个从节点也要装插件
systemctl enable --now mysqld
mysql>INSTALL PLUGIN rpl_semi_sync_slave SONAME 'semisync_slave.so';
[root@centos8 ~]#vim /etc/my.cnf
[mysqld]
server-id=28（以服务器的最后一位命名）
rpl_semi_sync_slave_enable
systemctl restart mysqld
将第一个从节点的备份配置文件传到第二个从节点的mysql < /data/all.sql
开始线程
mysql> start slave；
查看半同步状态信息
SHOW GLOBAL VARIABLES LIKE '%semi%';
删除插件
mysql>UNINSTALL PLUGIN rpl_semi_sync_master ;
5. 用mycat实现mysql的读写分离
已经实现主从复制10.0.0.8master,10.0.0.58 slave
10.0.0.8 master
mysql> create user admin@'10.0.0.%' identified by '123456';
mysql> grant all on hellodb.* to admin@'10.0.0.%';
mysql> select @@general_log
-> ;
+---------------+
| @@general_log |
+---------------+
| 0 |
+---------------+
mysql> set global general_log=1;
10.0.0.58 slave
mysql> set global general_log=1;
10.0.0.28 mycat
[root@mycat ~]#mkdir /apps
[root@mycat ~]#ll
-rw-r--r-- 1 root root 21760812 May 11 2022 Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz
[root@mycat ~]#tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps
[root@mycat ~]#vi /etc/profile.d/mycat.sh
#!/bin/bash
PATH=/apps/mycat/bin:$PATH
[root@mycat ~]#. /etc/profile.d/mycat.sh
[root@mycat ~]#free -h
total used free shared buff/cache available
Mem: 2.7Gi 221Mi 2.1Gi 8.0Mi 420Mi 2.3Gi
Swap: 2.0Gi 0B 2.0Gi
[root@mycat ~]#yum -y install java
[root@mycat ~]#mycat start
[root@mycat ~]#tail /apps/mycat/logs/wrapper.log
STATUS | wrapper | 2022/11/24 17:14:59 | --> Wrapper Started as Daemon
STATUS | wrapper | 2022/11/24 17:14:59 | Launching a JVM...
INFO | jvm 1 | 2022/11/24 17:15:00 | Wrapper (Version 3.2.3) http://wrapper.tanukisoftware.org
INFO | jvm 1 | 2022/11/24 17:15:00 | Copyright 1999-2006 Tanuki Software, Inc. All Rights Reserved.
INFO | jvm 1 | 2022/11/24 17:15:00 |
INFO | jvm 1 | 2022/11/24 17:15:01 | MyCAT Server startup successfully. see logs in logs/mycat.log
[root@mycat ~]#vim /apps/mycat/conf/server.xml
<property name="handleDistributedTransactions">0</property>
<property name="serverPort">3306</property> <property name="managerPort">9066</property>
[root@mycat ~]#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="10.0.0.8:3306" user="admin"
password="123456">
<readHost host="host2" url="10.0.0.58:3306" user="admin" password="123456" />
</writeHost>
</dataHost>
</mycat:schema>
10.0.0.38 client
[root@localhost yum.repos.d]#yum -y install mariadb
[root@localhost yum.repos.d]#mysql -uroot -p123456 -h10.0.0.28
MySQL [TESTDB]> select @@server_id；
+-------------+
| @@server_id |
+-------------+
| 58 |
+-------------+
1 row in set (0.01 sec)
MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zz | 26 | F |
| 6 | giao | 23 | M |
| 7 | lang | 45 | M |
| 8 | zhaozilong | 33 | M |
| 9 | liuan | 91 | M |
| 10 | a | 91 | M |
| 11 | a | 91 | M |
+-----+---------------+-----+--------+
MySQL [TESTDB]> update teachers set age=@@server_id where tid=11;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
MySQL [TESTDB]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 94 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
| 5 | zz | 26 | F |
| 6 | giao | 23 | M |
| 7 | lang | 45 | M |
| 8 | zhaozilong | 33 | M |
| 9 | liuan | 91 | M |
| 10 | a | 91 | M |
| 11 | a | 8 | M |
+-----+---------------+-----+--------+
6. 实现openvpn部署
mkdir /tmp/openvpn
cd /tmp/openvpn
rpm -ivh epel-release-latest-7.noarch.rpm
yum update
yum install -y openssl lzo pam easy-rsa
yum install openvpn
cd /usr/share/easy-rsa/3
./easyrsa init-pki
./easyrsa build-ca
./easyrsa gen-req $EntityName nopass
./easyrsa sign-req server $EntityName
生成Diffie-Hellman
./easyrsa gen-dh
2.3 客户端证书
另外创建一个目录用于存放client证书
mkdir -p /tmp/openvpn/client1
cd /tmp/openvpn/client1
/usr/share/easy-rsa/3/easyrsa init-pki
/usr/share/easy-rsa/3/easyrsa gen-req client1
签名client证书：
cd /usr/share/easy-rsa/3/
./easyrsa import-req /tmp/openvpn/client1/pki/reqs/client1.req client1
./easyrsa sign-req client client1
2.4 归档证书
将server证书放在同一个目录下
mkdir /etc/openvpn
cp /usr/share/easy-rsa/3/pki/ca.crt /etc/openvpn/
cp /usr/share/easy-rsa/3/pki/private/server.key /etc/openvpn/
cp /usr/share/easy-rsa/3/pki/issued/server.crt /etc/openvpn/
cp /usr/share/easy-rsa/3/pki/dh.pem /etc/openvpn/
客户端需要的文件：
mkdir /tmp/client1
cp /usr/share/easy-rsa/3/pki/ca.crt /tmp/client1/
cp /usr/share/easy-rsa/3/pki/issued/client1.crt /tmp/client1/
cp /tmp/openvpn/client1/pki/private/client1.key /tmp/client1/
vim /etc/openvpn/server.conf
local 192.168.1.5 #云主机内网ip
port 11094
proto tcp
dev tun
ca ca.crt
cert server.crt
key server.key
dh dh.pem
auth SHA512
#tls-crypt tc.key
topology subnet
server 10.8.0.0 255.255.255.0
push "redirect-gateway def1 bypass-dhcp"
ifconfig-pool-persist ipp.txt
push "dhcp-option DNS 192.168.1.2"
client-to-client
keepalive 10 120
cipher AES-256-CBC
user nobody
group nobody
persist-key
persist-tun
verb 3
#crl-verify crl.pem
systemctl enable openvpn@server
systemctl start openvpn@server
7. mysql如何实现崩溃后恢复？
MySQL为了提高性能，你对它数据行的增、删、改操作其实都优先发生在内存（Buffer Pool）中。假如update了某些数据，Buffer Pool中的数据页也就会被你改成脏数据页。
当MySQL重启的时候需要把方才修改的内容恢复出来吧，不然数据就不一致了，借助redo log恢复，begin事物开始操作时，会先写redo log，在操作数据页。这个数据恢复的过程也叫做重做。
8. myisam和innodb各自在什么场景使用？
.MyISAM管理非事务表。它提供高速存储和检索，以及全文搜索能力。如果应用中需要执行大量的SELECT查询，那么MyISAM是更好的选择。
.InnoDB用于事务处理应用程序，具有众多特性，包括ACID事务支持。如果应用中需要执行大量的INSERT或UPDATE操作，则应该使用InnoDB，这样可以提高多用户并发操作的性能
浙公网安备 33010602011771号