一，总结sql的表/库/索引 CURD，总结聚合，分组聚合，过滤 相关语句和示例
库和表的层级关系如图，数据由行（一条记录）和列（字段）组成，存放在表中，表存放在库中。
一个Mysql数据库可以有多个库，库中可以有多个表，表中可以根据数据关系定义行和列
索引： 是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引 擎实现
CURD
CREATE-增加
READ-查询
UPDATE-更新
DELETE-删除
常见聚合函数
- 统计数量 count()
统计students表有多少行
[hellodb]> select count(*) from students;
+----------+
| count(*) |
+----------+
| 25 |
+----------+
1 row in set (0.07 sec)
- 加，合计 sum()
计算students表中所有人的年龄总和
[hellodb]> select sum(age) from students;
+----------+
| sum(age) |
+----------+
| 685 |
+----------+
1 row in set (0.00 sec)
- 最大 max()
查看students表中最大的年龄
[hellodb]> select max(age) from students;
- 最小 min()
查看students表中最小的年龄
[hellodb]> select min(age) from students;
+----------+
| min(age) |
+----------+
| 17 |
+----------+
1 row in set (0.00 sec)
- 平均 avg()
查看students表中平均年龄
[hellodb]> select avg(age) from students;
+----------+
| avg(age) |
+----------+
| 27.4000 |
+----------+
1 row in set (0.00 sec)
分组聚合
按性别分组，计算男女平均年龄
[hellodb]> select gender,avg(age) from students group by gender;
+--------+----------+
| gender | avg(age) |
+--------+----------+
| M | 33.0000 |
| F | 19.0000 |
+--------+----------+
2 rows in set (0.00 sec)
过滤
找出年龄小于18的行，打印出姓名，性别，年龄
[hellodb]> select name,gender,age from students where age<18;
+-------------+--------+-----+
| name | gender | age |
+-------------+--------+-----+
| Lin Daiyu | F | 17 |
| Lu Wushuang | F | 17 |
+-------------+--------+-----+
2 rows in set (0.00 sec)
二、导入hellodb.sql生成数据库进行各种查询
- 在students表中，查询年龄大于25岁，且为男性的同学的名字和年龄
[hellodb]> select name,age,gender from students where age>25 and gender='M';
+--------------+-----+--------+
| name | age | gender |
+--------------+-----+--------+
| Xie Yanke | 53 | M |
| Ding Dian | 32 | M |
| Yu Yutong | 26 | M |
| Shi Qing | 46 | M |
| Tian Boguang | 33 | M |
| Xu Xian | 27 | M |
| Sun Dasheng | 100 | M |
+--------------+-----+--------+
7 rows in set (0.00 sec)
- 以ClassID为分组依据，显示每组的平均年龄
[hellodb]> select classid,avg(age) from students group by classid;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| 2 | 36.0000 |
| 1 | 20.5000 |
| 4 | 24.7500 |
| 3 | 20.2500 |
| 5 | 46.0000 |
| 7 | 19.6667 |
| 6 | 20.7500 |
| NULL | 63.5000 |
+---------+----------+
8 rows in set (0.00 sec)
- 显示第2题中平均年龄大于30的分组及平均年龄
[hellodb]> select classid,avg(age) from students group by classid order by avg(age) desc limit 3;
+---------+----------+
| classid | avg(age) |
+---------+----------+
| NULL | 63.5000 |
| 5 | 46.0000 |
| 2 | 36.0000 |
+---------+----------+
3 rows in set (0.01 sec)
- 显示以L开头的名字的同学的信息
[hellodb]> select name from students where name like 'L%';
+-------------+
| name |
+-------------+
| Lin Daiyu |
| Lu Wushuang |
| Lin Chong |
+-------------+
3 rows in set (0.00 sec)
三、数据库授权magedu用户，允许192.168.119.0/24网段可以连接mysql
[(none)]> create user 'magedu'@'192.168.119.0/24';
四、主从复制及主主复制的实现
1.主从复制原理
主从复制主要分成三步：
1.主数据库更新事件会按照顺序写入binlog二进制日志中。当slave连接到Master后，Master会为slave开启一个dump线程，该线程会读取二进制日志
2.Slave连接Master后。Slave库会开启一个IO线程，通过请求Master的dump thread读取binlog日志，然后写入relay log日志中
3.slave还有个SQL线程，实时检测relay log日志是否有更新，会更新到slave数据库中
注意：是slave的IO线程主动向Master发起日志请求，而不是Master推送给slave
主从复制配置
模拟在主服务已运行一段时间后，在添加从服务器
主服务器配置
#创建二进制日志存放目录
[root@Mysql-main ~]# mkdir -p /data/mysql_bin_log/
[root@Mysql-main ~]# chown mysql.mysql /data/mysql_bin_log/
[root@Mysql-main ~]# vim /etc/my.cnf
[mysqld]
log_bin=/data/mysql_bin_log/binlog #自定义二进制日志存放路径
server-id=210 #添加一个sever-id
[root@Mysql-slave ~]# systemctl restart mysqld
#创建账户给从服务器同步使用
[root@Mysql-main ~]# mysql
[(none)]> create user 'slaveuser'@'192.168.110.%' identified by "admin@goip";
[(none)]> grant replication slave on *.* to 'slaveuser'@'192.168.110.%';
#完全备份
[root@Mysql-main ~]# mysqldump -A -F --single-transaction --master-data=1 > /data/all_`date +%F-%H-%M`.sql
#发送给从服务器
[root@Mysql-main ~]# scp /data/all_2023-01-16-10-45.sql root@192.168.110.211:/root/
从服务器配置
[root@Mysql-slave ~]# vim /etc/my.cnf
[mysqld]
server-id=211
[root@Mysql-slave ~]# systemctl restart mysqld
#编辑主服务器发过来的sql备份文件
[root@Mysql-slave ~]# vim all_2023-01-16-10-45.sql
CHANGE MASTER TO
MASTER_HOST='192.168.110.210', #主服务器IP
MASTER_USER='slaveuser', #使用的同步和账号
MASTER_PASSWORD='admin@goip',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=157; #主服务器到备份那一刻二进制的位置
mysql> set sql_log_bin=0; #临时关闭二进制日志记录
mysql> source /root/all_2023-01-16-10-45.sql; #调用主服务器的备份文件
mysql> set sql_log_bin=1; #重新开启二进制日志
#开启从服务器功能
mysql> start slave;
#查看从服务器是否生效
mysql> show slave status\G
*************************** 1. row ***************************
Slave_IO_State: Waiting for source to send event
Master_Host: 192.168.110.210
Master_User: slaveuser
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: binlog.000004
Read_Master_Log_Pos: 453
Relay_Log_File: Mysql-slave-relay-bin.000002
Relay_Log_Pos: 619
Relay_Master_Log_File: binlog.000004
Slave_IO_Running: Yes #IO线程已打开
Slave_SQL_Running: Yes #sql线程已打开，两个线程都打开说明能正常复制主服务正常
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
....
主主复制
主主复制环境中，两个节点都可以更新数据，两个节点更新的数据都会推送给对方
容易产生数据不一致，数据冲突的问题，因此主主复制要慎用
主主复制配置（基于上面主从复制继续配置）
1.先建立主从关系
2.主从同步数据后互相指向对端为主服务器，主从服务器都开启slave线程。就可变成主主架构，在任意一台服务器写入数据另一台都能同步
主服务器A（192.168.110.210）：
mysql>CHANGE MASTER TO
MASTER_HOST='192.168.110.211', #主服务器B的IP
MASTER_USER='slaveuser', #使用的同步和账号
MASTER_PASSWORD='admin@goip',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000004', MASTER_LOG_POS=157; #主服务器B的二进制位置
主服务器B（192.168.110.211）：
mysql>CHANGE MASTER TO
CHANGE MASTER TO
MASTER_HOST='192.168.110.210', #主服务器IP
MASTER_USER='slaveuser', #使用的同步和账号
MASTER_PASSWORD='admin@goip',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000005', MASTER_LOG_POS=157; #主服务器A的二进制位置
#其他配置文件，账号密码等略。和上面主从复制一样
五、xtrabackup实现全量+增量+binlog恢复库
全量备份
将mysql-main的数据备份还原到mysql-backup
主服务器
#从官网下载包放到服务器进行安装
[root@localhost ~]# yum -y install percona-xtrabackup-80-8.0.27-19.1.el7.x86_64.rpm
[root@localhost ~]# mkdir /backup #创建备份文件存放目录
[root@localhost ~]# xtrabackup -padmin@goip --backup --target-dir=/backup/base #备份（文件存放到/backup/base 自动创建）
[root@localhost ~]# scp -r /backup root@192.168.110.201:/ #将数据库备份文件发给备份服务器（也可在本机还原备份）
备份服务器
[root@localhost ~]# yum -y install percona-xtrabackup-80-8.0.27-19.1.el7.x86_64.rpm
[root@localhost ~]# systemctl stop mysqld #停止数据库服务
[root@localhost ~]# rm -rf /data/mysql/* #删除数据库目录所有内容
#检查备份
[root@localhost ~]# xtrabackup --prepare --target-dir=/backup/base #预准备--prepare 自动检查确保数据一致提交完成的事务，回滚未完成的事务
#用xtrabackup命令将备份复制到数据库目录
[root@localhost ~]# xtrabackup --copy-back --target-dir=/backup/base
[root@localhost ~]# chown -R mysql:mysql /data/mysql/ #更改权限
[root@localhost ~]# systemctl start mysqld #启动数据库
增量备份
此处模拟2次增量备份
- 完全备份存放到一个文件"/backup/base"
- 第一次增量备份的数据是从完全备份后到一次增量备份的时间节点的数据,第一次增量备份需基于完全备份/backup/base基础上增加，保存到一个新的文件/backup/inc1上
- 第二次增量备份的数据是从第一次增量备份到第二次增量备份时间节点的数据，第二次增量备份需基于第一次增量备份的文件上进行备份，保存到一个新的文件/backup/inc2上
注意：增量备份场景中，完全备份和第一次增量备份或第N增量备份是不需要回滚未未完成事务因为下次增量备份包含未完成事务的下半部分，直到最后一次增量备份才需要回滚未完成事务
六，MyCAT实现MySQL读写分离
Mycat相当于数据库的中间件，用户将数据发给Mycat，Mycat根据配置文件定义对数据做处理，发给后端Mysql服务器器，得到结果后再返回给用户
这里沿用上面mysql主从配置环境更改
Mycat配置
[root@MyCat ~]# yum -y install java
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 #下载mycat
[root@MyCat ~]# mkdir /apps/
[root@MyCat ~]# tar xf Mycat-server-1.6.7.4-release-20200105164103-linux.tar.gz -C /apps/
[root@MyCat ~]# vim /etc/profile.d/mycat.sh #定义PATH变量，方便mycat命令使用
#!/bin/bash
PATH=/apps/mycat/bin:$PATH
[root@MyCat ~]# source /etc/profile.d/mycat.sh
[root@MyCat ~]# mycat start
查看Mycat是否启动成功
[root@MyCat ~]# cat /apps/mycat/logs/wrapper.log
INFO | jvm 1 | 2023/01/16 13:57:44 | MyCAT Server startup successfully. see logs in logs/mycat.log
#用户端可使用mycat默认用户名密码：root/123456登录，端口8066
更改Mycat配置文件
[root@MyCat ~]# vim /apps/mycat/conf/server.xml
<property name="serverPort">3306</property> #新增此行（改连接mycat的端口）注意端口不要冲突
<property name="handleDistributedTransactions">0</property> #在此行上面（此行本来就在配置文件有）
<user name="user1" defaultAccount="true"> #更改登录mycat账号
<property name="password">admin@goip</property> #更改登录mycat密码
修改schema.xml实现读写分离策略
[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" /> #映射到目标服务器的哪个库（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.110.210:3306" user="mycatuser" #写入操作发给个服务器（master）
password="admin@goip">
<readHost host="host2" url="192.168.110.211:3306" user="mycatuser" password="admin@goip" /> #从哪个服务器读取（slave）
</writeHost>
</dataHost>
</mycat:schema>
~
配置完上面内容重启Mycat服务
[root@MyCat ~]# mycat restart
测试验证
mysql主从服务器均开启通用日志
mysql> set global general_log=on;
在客户端使用mysql客户端工具登录Mycat
[root@client ~]# mysql -uuser1 -padmin@goip -h192.168.110.212 -P3306
mysql> \u TESTDB #映射为实际后端数据库的hellodb库
mysql> select @@server_id; #执行读操作
+-------------+
| @@server_id |
+-------------+
| 211 |
+-------------+
1 row in set (0.00 sec)
mysql> insert students(name,age) values('test1',24); #执行写操作
Query OK, 1 row affected (0.01 sec)
从服务器查看通用日志：
[root@Mysql-slave ~]# cat /var/lib/mysql/Mysql-slave.log
2023-01-16T06:13:16.197537Z 55 Query select @@server_id
主服务器查看通用日志：
[root@Mysql-main ~]# cat /var/lib/mysql/Mysql-main.log
2023-01-16T06:19:22.046164Z 54 Query insert students(name,age) values('test2',24)
在客户端进行读操作已发往从服务器，进行写操作已发往主服务器，至此读写已分离
2368
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
