目录
1. 完成将server和client端的mysql配置默认字符集为utf8mb4;
2. 掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，
6. 根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例。
7. 导入hellodb库，总结DQL, alias, where子句，gruop by, order by, limit, having使用示例。
8. 基于hellodb 库, 总结子查询，关联查询 ，交叉连接，内连接，左连接，右连接，完全连接，自连接。
11. 基于apache, php, mysql搭建wordpress站点。
14. 总结mysql索引作用，同时总结哪些查询不会使用到索引。
19. 总结mysql备份类型，并基于mysqldump, xtrabackup完成数据库备份与恢复验证。
20. 编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。
21. 编写crontab, 基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份。
23. 实现mysql主从复制，主主复制，半同步复制，过滤复制，
25. 总结主从复制不一致的原因，如何解决不一致，如何避免不一致
28. 总结mysql高可用方案及高可用级别，搭建MHA集群和galera cluster，尝试搭建TIDB集群。
30. 总结openvpn原理，并完成1键安装不同版本vpn脚本，可以适配rocky, ubuntu, centos主机。同时支持添加账号，注销账号。
1. 完成将server和client端的mysql配置默认字符集为utf8mb4;
vim /etc/my.cnf
[mysql]
default-character-set=utf8mb4
[client]
default-character-set=utf8mb4
2. 掌握如何获取SQL命令的帮助，基于帮助完成添加testdb库，
字符集utf8, 排序集合utf8_bin
# 获取SQL帮助
mysql> help create database
Name: 'CREATE DATABASE'
Description:
Syntax:
CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name
[create_option] ...
create_option: [DEFAULT] {
CHARACTER SET [=] charset_name
| COLLATE [=] collation_name
| ENCRYPTION [=] {'Y' | 'N'}
}
CREATE DATABASE creates a database with the given name. To use this
statement, you need the CREATE privilege for the database. CREATE
SCHEMA is a synonym for CREATE DATABASE.
URL: https://dev.mysql.com/doc/refman/8.0/en/create-database.html
# 创建 testbd数据库
mysql> create database testdb character set utf8 collate utf8_bin;
Query OK, 1 row affected, 2 warnings (0.00 sec)
mysql> show create database testdb;
+----------+---------------------------------------------------------------------------------------------------------------------------+
| Database | Create Database |
+----------+---------------------------------------------------------------------------------------------------------------------------+
| testdb | CREATE DATABASE `testdb` /*!40100 DEFAULT CHARACTER SET utf8mb3 COLLATE utf8mb3_bin */ /*!80016 DEFAULT ENCRYPTION='N' */ |
+----------+---------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
3.总结mysql常见的数据类型。
整型（int）
位类型(bit)
浮点型（float和double、real）
定点数（decimal,numeric）
日期时间类型（date,time,datetime,year）
字符串（char,varchar）
二进制数据（Blob、binary）
枚举（enum）
集合（set）
4. 创建一个主机表host,放在testdb中，要求字段
1) 主键自增id 无符号, tinyint. 2) hostname可变字符长度256，可为空。。3）ip 可变字符长度256，可为空。4）账号，可变字符长度256，可为空。5）密码，可变字符长度256，可为空。6）创建时间，时间类型，非空。7）更新时间，时间类型，默认当前时间。8）区域，只能在华南，华北，华东，三个区域之一。9）端口，无符号整数，可为空。10）外网地址，可变字符长度256，可为空。11）内网地址，可变字符长度256，可为空。
mysql> use testdb;
Database changed
mysql> CREATE TABLE host ( id TINYINT UNSIGNED AUTO_INCREMENT PRIMARY KEY,hostname VARCHAR(256) NULL,ip VARCHAR(256) NULL,account VARCHAR(256) NULL,password VARCHAR(256) NULL,createtime DATETIME,updatetime TIMESTAMP,area ENUM('华南','华北','华东') NOT NULL ,port INT UNSIGNED NULL,externaladdress VARCHAR(256) NULL,inaddress VARCHAR(256) NULL );
Query OK, 0 rows affected (0.02 sec)
mysql> DESC host;
+-----------------+----------------------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+-----------------+----------------------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| hostname | varchar(256) | YES | | NULL | |
| ip | varchar(256) | YES | | NULL | |
| account | varchar(256) | YES | | NULL | |
| password | varchar(256) | YES | | NULL | |
| createtime | datetime | YES | | NULL | |
| updatetime | timestamp | YES | | NULL | |
| area | enum('华南','华北','华东') | NO | | NULL | |
| port | int unsigned | YES | | NULL | |
| externaladdress | varchar(256) | YES | | NULL | |
| inaddress | varchar(256) | YES | | NULL | |
+-----------------+----------------------------------+------+-----+---------+----------------+
11 rows in set (0.00 sec)
5. 给testdb.host表中添加多条数据。
mysql> insert into host (id,hostname,ip,account,password,createtime,updatetime,area,port) values (1,'Ubuntu.xie.org','10.0.0.145/24','root','111111',20221010,now(),'华南',1234),(2,'Rocky.xie.org','10.0.0.146/24','root','111111',20221010,now(),'华南',1111),(3,'Ubuntu2.xie.org','10.0.0.147/24','root','111111',20221010,now(),'华南',2222);
Query OK, 3 rows affected (0.01 sec)
Records: 3 Duplicates: 0 Warnings: 0
mysql> select * from host;
+----+-----------------+---------------+---------+----------+---------------------+---------------------+--------+------+-----------------+-----------+
| id | hostname | ip | account | password | createtime | updatetime | area | port | externaladdress | inaddress |
+----+-----------------+---------------+---------+----------+---------------------+---------------------+--------+------+-----------------+-----------+
| 1 | Ubuntu.xie.org | 10.0.0.145/24 | root | 111111 | 2022-10-10 00:00:00 | 2023-06-13 03:31:35 | 华南 | 1234 | NULL | NULL |
| 2 | Rocky.xie.org | 10.0.0.146/24 | root | 111111 | 2022-10-10 00:00:00 | 2023-06-13 03:31:35 | 华南 | 1111 | NULL | NULL |
| 3 | Ubuntu2.xie.org | 10.0.0.147/24 | root | 111111 | 2022-10-10 00:00:00 | 2023-06-13 03:31:35 | 华南 | 2222 | NULL | NULL |
+----+-----------------+---------------+---------+----------+---------------------+---------------------+--------+------+-----------------+-----------+
3 rows in set (0.00 sec)
6. 根据表扩展出几个语句，完成总结DDL, DML的用法，并配上示例。
DDL
# 创建表
mysql> create table student (id tinyint unsigned primary key auto_increment , name char(4) not null , gender char(1) , age tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)
# 查看所创建的表
mysql> show create table student;
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| student | CREATE TABLE `student` (
`id` tinyint unsigned NOT NULL AUTO_INCREMENT,
`name` char(4) COLLATE utf8mb3_bin NOT NULL,
`gender` char(1) COLLATE utf8mb3_bin DEFAULT NULL,
`age` tinyint unsigned DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3 COLLATE=utf8mb3_bin |
+---------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
# 查看表的结构
mysql> desc student;
+--------+------------------+------+-----+---------+----------------+
| Field | Type | Null | Key | Default | Extra |
+--------+------------------+------+-----+---------+----------------+
| id | tinyint unsigned | NO | PRI | NULL | auto_increment |
| name | char(4) | NO | | NULL | |
| gender | char(1) | YES | | NULL | |
| age | tinyint unsigned | YES | | NULL | |
+--------+------------------+------+-----+---------+----------------+
4 rows in set (0.00 sec)
# 删除表
mysql> drop table student;
Query OK, 0 rows affected (0.01 sec)
DML
# 插入数据
mysql> create table student (id tinyint unsigned primary key auto_increment , name char(4) not null , gender char(1) , age tinyint unsigned);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into student (id,name,gender,age) values(1,'ming','M',20);
Query OK, 1 row affected (0.00 sec)
# 修改数据
mysql> select * from student;
+----+------+--------+------+
| id | name | gender | age |
+----+------+--------+------+
| 1 | ming | M | 20 |
+----+------+--------+------+
1 row in set (0.00 sec)
mysql> update student set age = 19 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from student;
+----+------+--------+------+
| id | name | gender | age |
+----+------+--------+------+
| 1 | ming | M | 19 |
+----+------+--------+------+
1 row in set (0.00 sec)
# 删除数据
mysql> delete from student where id = 1;
Query OK, 1 row affected (0.00 sec)
mysql> select * from student;
Empty set (0.00 sec)
7. 导入hellodb库，总结DQL, alias, where子句，gruop by, order by, limit, having使用示例。
导入hellodb库
root@xie:~# mysql < hellodb_innodb.sql
root@xie:~# mysql
Welcome to the MySQL monitor. Commands end with ; or \g.
Your MySQL connection id is 13
Server version: 8.0.33-0ubuntu0.20.04.2 (Ubuntu)
Copyright (c) 2000, 2023, Oracle and/or its affiliates.
Oracle is a registered trademark of Oracle Corporation and/or its
affiliates. Other names may be trademarks of their respective
owners.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
mysql> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
+--------------------+
6 rows in set (0.00 sec)
DQL
mysql> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
# 查看前三条数据
Database changed
mysql> select * from students limit 3;
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
+-------+-------------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
# 跳过前两个取后面的三个数据
mysql> select * from students limit 2,3;
+-------+-----------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-----------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
+-------+-----------+-----+--------+---------+-----------+
3 rows in set (0.00 sec)
# 查询表中的总记录数：count只统计非空的记录数
mysql> select count(*) from students;
+----------+
| count(*) |
+----------+
| 25 |
+----------+
1 row in set (0.01 sec)
# 模糊查询
mysql> select * from students where name like 'x%';
+-------+-------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+-------------+-----+--------+---------+-----------+
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
+-------+-------------+-----+--------+---------+-----------+
6 rows in set (0.00 sec)
# distinct去重
mysql> select distinct age from students;
+-----+
| age |
+-----+
| 22 |
| 53 |
| 32 |
| 26 |
| 46 |
| 19 |
| 17 |
| 20 |
| 23 |
| 33 |
| 21 |
| 25 |
| 18 |
| 27 |
| 100 |
+-----+
15 rows in set (0.00 sec)
# 统计平均值avg
mysql> select avg(age) from students;
+----------+
| avg(age) |
+----------+
| 27.4000 |
+----------+
1 row in set (0.00 sec)
# 统计总和sum
mysql> select sum(age) from students;
+----------+
| sum(age) |
+----------+
| 685 |
+----------+
1 row in set (0.00 sec)
# 分组group by
# 只统计男生的平均年龄
mysql> select gender,avg(age) from students group by gender having gender='M';
+--------+----------+
| gender | avg(age) |
+--------+----------+
| M | 33.0000 |
+--------+----------+
1 row in set (0.00 sec)
# 排序 oder by
# 按照年龄排序
mysql> select * from students order by age;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
#
8. 基于hellodb 库, 总结子查询，关联查询 ，交叉连接，内连接，左连接，右连接，完全连接，自连接。
mysql> use hellodb;
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
# 子查询
# 将老师表编号二的年龄改成学生地最大年龄
mysql> 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 |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
mysql> update teachers set age=(select max(age) from students) where tid=2;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 100 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
# 关联查询
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 100 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
mysql> select stuid,name,age,gender from students union select * from teachers;
+-------+---------------+-----+--------+
| stuid | name | age | gender |
+-------+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M |
| 2 | Shi Potian | 22 | M |
| 3 | Xie Yanke | 53 | M |
| 4 | Ding Dian | 32 | M |
| 5 | Yu Yutong | 26 | M |
| 6 | Shi Qing | 46 | M |
| 7 | Xi Ren | 19 | F |
| 8 | Lin Daiyu | 17 | F |
| 9 | Ren Yingying | 20 | F |
| 10 | Yue Lingshan | 19 | F |
| 11 | Yuan Chengzhi | 23 | M |
| 12 | Wen Qingqing | 19 | F |
| 13 | Tian Boguang | 33 | M |
| 14 | Lu Wushuang | 17 | F |
| 15 | Duan Yu | 19 | M |
| 16 | Xu Zhu | 21 | M |
| 17 | Lin Chong | 25 | M |
| 18 | Hua Rong | 23 | M |
| 19 | Xue Baochai | 18 | F |
| 20 | Diao Chan | 19 | F |
| 21 | Huang Yueying | 22 | F |
| 22 | Xiao Qiao | 20 | F |
| 23 | Ma Chao | 23 | M |
| 24 | Xu Xian | 27 | M |
| 25 | Sun Dasheng | 100 | M |
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 100 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-------+---------------+-----+--------+
29 rows in set (0.01 sec)
# 交叉连接
mysql> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name | Age | Gender |
+-----+---------------+-----+--------+
| 1 | Song Jiang | 45 | M |
| 2 | Zhang Sanfeng | 100 | M |
| 3 | Miejue Shitai | 77 | F |
| 4 | Lin Chaoying | 93 | F |
+-----+---------------+-----+--------+
4 rows in set (0.00 sec)
mysql> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
mysql> select * from students cross join teachers;
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 4 | Lin Chaoying | 93 | F |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 2 | Zhang Sanfeng | 100 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 1 | Song Jiang | 45 | M |
| 2 | Shi Potian | 22 | M | 1 | 7 | 4 | Lin Chaoying | 93 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | 3 | Miejue Shitai | 77 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | 2 | Zhang Sanfeng | 100 | M |
| 2 | Shi Potian | 22 | M | 1 | 7 | 1 | Song Jiang | 45 | M |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 4 | Lin Chaoying | 93 | F |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 3 | Miejue Shitai | 77 | F |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 2 | Zhang Sanfeng | 100 | M |
| 3 | Xie Yanke | 53 | M | 2 | 16 | 1 | Song Jiang | 45 | M |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 2 | Zhang Sanfeng | 100 | M |
| 4 | Ding Dian | 32 | M | 4 | 4 | 1 | Song Jiang | 45 | M |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 3 | Miejue Shitai | 77 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 2 | Zhang Sanfeng | 100 | M |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 6 | Shi Qing | 46 | M | 5 | NULL | 4 | Lin Chaoying | 93 | F |
| 6 | Shi Qing | 46 | M | 5 | NULL | 3 | Miejue Shitai | 77 | F |
| 6 | Shi Qing | 46 | M | 5 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 6 | Shi Qing | 46 | M | 5 | NULL | 1 | Song Jiang | 45 | M |
| 7 | Xi Ren | 19 | F | 3 | NULL | 4 | Lin Chaoying | 93 | F |
| 7 | Xi Ren | 19 | F | 3 | NULL | 3 | Miejue Shitai | 77 | F |
| 7 | Xi Ren | 19 | F | 3 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 7 | Xi Ren | 19 | F | 3 | NULL | 1 | Song Jiang | 45 | M |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 4 | Lin Chaoying | 93 | F |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 3 | Miejue Shitai | 77 | F |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | 1 | Song Jiang | 45 | M |
| 9 | Ren Yingying | 20 | F | 6 | NULL | 4 | Lin Chaoying | 93 | F |
| 9 | Ren Yingying | 20 | F | 6 | NULL | 3 | Miejue Shitai | 77 | F |
| 9 | Ren Yingying | 20 | F | 6 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 9 | Ren Yingying | 20 | F | 6 | NULL | 1 | Song Jiang | 45 | M |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | 4 | Lin Chaoying | 93 | F |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | 3 | Miejue Shitai | 77 | F |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | 1 | Song Jiang | 45 | M |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 4 | Lin Chaoying | 93 | F |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 3 | Miejue Shitai | 77 | F |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | 1 | Song Jiang | 45 | M |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | 4 | Lin Chaoying | 93 | F |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | 3 | Miejue Shitai | 77 | F |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | 1 | Song Jiang | 45 | M |
| 13 | Tian Boguang | 33 | M | 2 | NULL | 4 | Lin Chaoying | 93 | F |
| 13 | Tian Boguang | 33 | M | 2 | NULL | 3 | Miejue Shitai | 77 | F |
| 13 | Tian Boguang | 33 | M | 2 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 13 | Tian Boguang | 33 | M | 2 | NULL | 1 | Song Jiang | 45 | M |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | 4 | Lin Chaoying | 93 | F |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | 3 | Miejue Shitai | 77 | F |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | 1 | Song Jiang | 45 | M |
| 15 | Duan Yu | 19 | M | 4 | NULL | 4 | Lin Chaoying | 93 | F |
| 15 | Duan Yu | 19 | M | 4 | NULL | 3 | Miejue Shitai | 77 | F |
| 15 | Duan Yu | 19 | M | 4 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 15 | Duan Yu | 19 | M | 4 | NULL | 1 | Song Jiang | 45 | M |
| 16 | Xu Zhu | 21 | M | 1 | NULL | 4 | Lin Chaoying | 93 | F |
| 16 | Xu Zhu | 21 | M | 1 | NULL | 3 | Miejue Shitai | 77 | F |
| 16 | Xu Zhu | 21 | M | 1 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 16 | Xu Zhu | 21 | M | 1 | NULL | 1 | Song Jiang | 45 | M |
| 17 | Lin Chong | 25 | M | 4 | NULL | 4 | Lin Chaoying | 93 | F |
| 17 | Lin Chong | 25 | M | 4 | NULL | 3 | Miejue Shitai | 77 | F |
| 17 | Lin Chong | 25 | M | 4 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 17 | Lin Chong | 25 | M | 4 | NULL | 1 | Song Jiang | 45 | M |
| 18 | Hua Rong | 23 | M | 7 | NULL | 4 | Lin Chaoying | 93 | F |
| 18 | Hua Rong | 23 | M | 7 | NULL | 3 | Miejue Shitai | 77 | F |
| 18 | Hua Rong | 23 | M | 7 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 18 | Hua Rong | 23 | M | 7 | NULL | 1 | Song Jiang | 45 | M |
| 19 | Xue Baochai | 18 | F | 6 | NULL | 4 | Lin Chaoying | 93 | F |
| 19 | Xue Baochai | 18 | F | 6 | NULL | 3 | Miejue Shitai | 77 | F |
| 19 | Xue Baochai | 18 | F | 6 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 19 | Xue Baochai | 18 | F | 6 | NULL | 1 | Song Jiang | 45 | M |
| 20 | Diao Chan | 19 | F | 7 | NULL | 4 | Lin Chaoying | 93 | F |
| 20 | Diao Chan | 19 | F | 7 | NULL | 3 | Miejue Shitai | 77 | F |
| 20 | Diao Chan | 19 | F | 7 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 20 | Diao Chan | 19 | F | 7 | NULL | 1 | Song Jiang | 45 | M |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 4 | Lin Chaoying | 93 | F |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 3 | Miejue Shitai | 77 | F |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 21 | Huang Yueying | 22 | F | 6 | NULL | 1 | Song Jiang | 45 | M |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | 4 | Lin Chaoying | 93 | F |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | 3 | Miejue Shitai | 77 | F |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | 1 | Song Jiang | 45 | M |
| 23 | Ma Chao | 23 | M | 4 | NULL | 4 | Lin Chaoying | 93 | F |
| 23 | Ma Chao | 23 | M | 4 | NULL | 3 | Miejue Shitai | 77 | F |
| 23 | Ma Chao | 23 | M | 4 | NULL | 2 | Zhang Sanfeng | 100 | M |
| 23 | Ma Chao | 23 | M | 4 | NULL | 1 | Song Jiang | 45 | M |
| 24 | Xu Xian | 27 | M | NULL | NULL | 4 | Lin Chaoying | 93 | F |
| 24 | Xu Xian | 27 | M | NULL | NULL | 3 | Miejue Shitai | 77 | F |
| 24 | Xu Xian | 27 | M | NULL | NULL | 2 | Zhang Sanfeng | 100 | M |
| 24 | Xu Xian | 27 | M | NULL | NULL | 1 | Song Jiang | 45 | M |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | 4 | Lin Chaoying | 93 | F |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | 3 | Miejue Shitai | 77 | F |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | 2 | Zhang Sanfeng | 100 | M |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | 1 | Song Jiang | 45 | M |
+-------+---------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
100 rows in set (0.00 sec)
# 内连接
mysql> select * from students inner join teachers on students.teacherid=teachers.tid;
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
+-------+-------------+-----+--------+---------+-----------+-----+---------------+-----+--------+
3 rows in set (0.00 sec)
mysql> select s.stuid,s.name,s.teacherid,t.tid,t.name from students s inner join teachers t on s.teacherid=t.tid;
+-------+-------------+-----------+-----+---------------+
| stuid | name | teacherid | tid | name |
+-------+-------------+-----------+-----+---------------+
| 1 | Shi Zhongyu | 3 | 3 | Miejue Shitai |
| 4 | Ding Dian | 4 | 4 | Lin Chaoying |
| 5 | Yu Yutong | 1 | 1 | Song Jiang |
+-------+-------------+-----------+-----+---------------+
3 rows in set (0.00 sec)
# 左外连接
mysql> select * from students s left outer join teachers t on s.teacherid=t.tid;
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 2 | Shi Potian | 22 | M | 1 | 7 | NULL | NULL | NULL | NULL |
| 3 | Xie Yanke | 53 | M | 2 | 16 | NULL | NULL | NULL | NULL |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| 6 | Shi Qing | 46 | M | 5 | NULL | NULL | NULL | NULL | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL | NULL | NULL | NULL | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL | NULL | NULL | NULL | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL | NULL | NULL | NULL | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL | NULL | NULL | NULL | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL | NULL | NULL | NULL | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL | NULL | NULL | NULL | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL | NULL | NULL | NULL | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL | NULL | NULL | NULL | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL | NULL | NULL | NULL | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL | NULL | NULL | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | NULL | NULL | NULL | NULL | NULL | NULL |
+-------+---------------+-----+--------+---------+-----------+------+---------------+------+--------+
25 rows in set (0.00 sec)
# 右外连接
mysql> select * from students s right outer join teachers t on s.teacherid=t.tid;
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| StuID | Name | Age | Gender | ClassID | TeacherID | TID | Name | Age | Gender |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
| 5 | Yu Yutong | 26 | M | 3 | 1 | 1 | Song Jiang | 45 | M |
| NULL | NULL | NULL | NULL | NULL | NULL | 2 | Zhang Sanfeng | 100 | M |
| 1 | Shi Zhongyu | 22 | M | 2 | 3 | 3 | Miejue Shitai | 77 | F |
| 4 | Ding Dian | 32 | M | 4 | 4 | 4 | Lin Chaoying | 93 | F |
+-------+-------------+------+--------+---------+-----------+-----+---------------+-----+--------+
4 rows in set (0.01 sec)
# 完全外连接
mysql> select * from students s full outer join teachers t on s.teacherid=t.tid;
9. 总结select语句处理顺序。
FROM Clause --> WHERE Clause --> GROUP BY --> HAVING Clause --> SELECT --> ORDER BY --> LIMIT
10. 总结mysql事件管理,用户管理，权限管理。
管理事务
# 启动事务
begin;
例子
MariaDB [hellodb]> begin;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> insert teachers(name,age,gender) values('y',19,'F');
Query OK, 1 row affected (0.001 sec)
MariaDB [hellodb]> 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 | x | 29 | M |
| 6 | y | 19 | F |
+-----+---------------+-----+--------+
6 rows in set (0.000 sec)
在另一个终端查看
MariaDB [hellodb]> 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 | x | 29 | M |
+-----+---------------+-----+--------+
5 rows in set (0.000 sec)
# 结束事务
commit;
例子
MariaDB [hellodb]> 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 | x | 29 | M |
| 8 | y | 19 | F |
| 9 | z | 23 | M |
+-----+---------------+-----+--------+
7 rows in set (0.000 sec)
MariaDB [hellodb]> commit;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> 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 | x | 29 | M |
| 8 | y | 19 | F |
| 9 | z | 23 | M |
+-----+---------------+-----+--------+
7 rows in set (0.000 sec)
另一个终端查看
MariaDB [hellodb]> 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 | x | 29 | M |
+-----+---------------+-----+--------+
5 rows in set (0.000 sec)
MariaDB [hellodb]> 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 | x | 29 | M |
| 8 | y | 19 | F |
| 9 | z | 23 | M |
+-----+---------------+-----+--------+
7 rows in set (0.000 sec)
# 取消事务
rollback;
例子
MariaDB [hellodb]> 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 | x | 29 | M |
| 7 | y | 19 | F |
+-----+---------------+-----+--------+
6 rows in set (0.000 sec)
MariaDB [hellodb]> rollback;
Query OK, 0 rows affected (0.000 sec)
MariaDB [hellodb]> 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 | x | 29 | M |
+-----+---------------+-----+--------+
5 rows in set (0.000 sec)
# 注意：drop事务撤销不了，delete可以撤销因为表还在用户管理
创建新用户：create user xie@'10.0.0.%' identified by '111111';
远程连接：mysql -uxie -p111111 -h10.0.0.130
查看当前身份：select user();
删除用户：drop user xie@10.0.0.%;
修改用户密码：alter identified by '123456';
破解MySQL5.7和8.0的root密码：权限管理
# 权限授权
# 将数据库zabbix里的所有权限赋给xie账号
(root@localhost) [(none)]> grant all on zabbix.* to xie@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
MySQL [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| information_schema |
| performance_schema |
| zabbix |
+--------------------+
3 rows in set (0.002 sec)
# 查看当前用户有什么权限
MySQL [(none)]> show grants for xie@'10.0.0.%';
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Grants for xie@10.0.0.% |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| GRANT SELECT, INSERT, UPDATE, DELETE, CREATE, DROP, RELOAD, SHUTDOWN, PROCESS, FILE, REFERENCES, INDEX, ALTER, SHOW DATABASES, SUPER, CREATE TEMPORARY TABLES, LOCK TABLES, EXECUTE, REPLICATION SLAVE, REPLICATION CLIENT, CREATE VIEW, SHOW VIEW, CREATE ROUTINE, ALTER ROUTINE, CREATE USER, EVENT, TRIGGER, CREATE TABLESPACE, CREATE ROLE, DROP ROLE ON *.* TO `xie`@`10.0.0.%` |
| GRANT APPLICATION_PASSWORD_ADMIN,AUDIT_ABORT_EXEMPT,AUDIT_ADMIN,AUTHENTICATION_POLICY_ADMIN,BACKUP_ADMIN,BINLOG_ADMIN,BINLOG_ENCRYPTION_ADMIN,CLONE_ADMIN,CONNECTION_ADMIN,ENCRYPTION_KEY_ADMIN,FIREWALL_EXEMPT,FLUSH_OPTIMIZER_COSTS,FLUSH_STATUS,FLUSH_TABLES,FLUSH_USER_RESOURCES,GROUP_REPLICATION_ADMIN,GROUP_REPLICATION_STREAM,INNODB_REDO_LOG_ARCHIVE,INNODB_REDO_LOG_ENABLE,PASSWORDLESS_USER_ADMIN,PERSIST_RO_VARIABLES_ADMIN,REPLICATION_APPLIER,REPLICATION_SLAVE_ADMIN,RESOURCE_GROUP_ADMIN,RESOURCE_GROUP_USER,ROLE_ADMIN,SENSITIVE_VARIABLES_OBSERVER,SERVICE_CONNECTION_ADMIN,SESSION_VARIABLES_ADMIN,SET_USER_ID,SHOW_ROUTINE,SYSTEM_USER,SYSTEM_VARIABLES_ADMIN,TABLE_ENCRYPTION_ADMIN,XA_RECOVER_ADMIN ON *.* TO `xie`@`10.0.0.%` |
| GRANT ALL PRIVILEGES ON `zabbix`.* TO `xie`@`10.0.0.%` |
+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
3 rows in set (0.001 sec)
11. 基于apache, php, mysql搭建wordpress站点。
# 下载wordpress：https://cn.wordpress.org/download/
# 将包考到Linux中解压：unzip wordpress-6.1.1-zh_CN.zip -d /opt
[root@Rocky2 ~]# cd /opt
[root@Rocky2 opt]# ls
wordpress
[root@Rocky2 opt]# cd wordpress/
[root@Rocky2 wordpress]#
[root@Rocky2 wordpress]# ll
total 224
-rw-r--r-- 1 root root 405 Feb 6 2020 index.php
-rw-r--r-- 1 root root 19915 Jan 1 2022 license.txt
-rw-r--r-- 1 root root 7389 Sep 17 06:27 readme.html
-rw-r--r-- 1 root root 7205 Sep 17 07:13 wp-activate.php
drwxr-xr-x 9 root root 4096 Dec 18 15:00 wp-admin
-rw-r--r-- 1 root root 351 Feb 6 2020 wp-blog-header.php
-rw-r--r-- 1 root root 2338 Nov 10 2021 wp-comments-post.php
-rw-r--r-- 1 root root 3001 Dec 14 2021 wp-config-sample.php
drwxr-xr-x 5 root root 69 Dec 18 15:00 wp-content
-rw-r--r-- 1 root root 5543 Sep 20 23:44 wp-cron.php
drwxr-xr-x 27 root root 12288 Dec 18 15:00 wp-includes
-rw-r--r-- 1 root root 2494 Mar 20 2022 wp-links-opml.php
-rw-r--r-- 1 root root 3985 Sep 19 16:59 wp-load.php
-rw-r--r-- 1 root root 49135 Sep 20 06:26 wp-login.php
-rw-r--r-- 1 root root 8522 Oct 17 19:06 wp-mail.php
-rw-r--r-- 1 root root 24587 Sep 26 18:17 wp-settings.php
-rw-r--r-- 1 root root 34350 Sep 17 08:35 wp-signup.php
-rw-r--r-- 1 root root 4914 Oct 17 19:22 wp-trackback.php
-rw-r--r-- 1 root root 3236 Jun 9 2020 xmlrpc.php
# 安装相关软件
yum -y install httpd php php-mysqlnd php-json
# 启动服务
[root@Rocky2 wordpress]# systemctl enable --now httpd
Created symlink /etc/systemd/system/multi-user.target.wants/httpd.service → /usr/lib/systemd/system/httpd.service.
# 将文件拷贝到网站目录中
mv * /var/www/html/
# 给文件apach权限
[root@Rocky2 wordpress]# chown -R apache. /var/www/html/
[root@Rocky2 wordpress]# ll -d /var/www/html/
drwxr-xr-x 5 apache apache 4096 Mar 2 11:54 /var/www/html/
# 在switch hosts软件中配置地址
10.0.0.130 blog.xie.com
# 在/etc/hosts文件上写也是同样的效果
# 创建数据库
(root@localhost) [(none)]> create database wordpress;
Query OK, 1 row affected (0.01 sec)
# 创建用户
(root@localhost) [(none)]> create user wordpress@'localhost' identified by '111111';
Query OK, 0 rows affected (0.01 sec)
# 数据库授权
(root@localhost) [(none)]> grant all on wordpress.* to wordpress@'localhost';
Query OK, 0 rows affected (0.00 sec)
# 网页上敲地址进入：
blog.xie.com
设置用户和密码
admin 111111
12. 总结mysql架构原理
第一层：连接层，所包含的服务并不是MySQL所独有的技术。它们都是服务于C/S程序或者是这些程序所需要的 ：连接处理，身份验证，安全性等等。
第二层：核心服务层 。这是MySQL的核心部分。通常叫做 SQL Layer。在 MySQL据库系统处理底层数据之前的所有工作都是在这一层完成的，包括权限判断， sql解析，行计划优化， query cache 的处理以及所有内置的函数(如日期,时间,数学运算,加密)等等。各个存储引擎提供的功能都集中在这一层，如存储过程，触发器，视 图等。
第三层：存储引擎层。通常叫做StorEngine Layer ，也就是底层数据存取操作实现部分，由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。每个存储引擎都有自己的优点和缺陷。服务器是通过存储引擎API来与它们交互的。这个接口隐藏 了各个存储引擎不同的地方。对于查询层尽可能的透明。这个API包含了很多底层的操作。如开始一个事 物，或者取出有特定主键的行。存储引擎不能解析SQL，互相之间也不能通信。仅仅是简单的响应服务器 的请求。
第四层：数据存储层。主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。
13. 总结myisam和Innodb存储引擎的区别。
MyISAM和InnoDB的区别
定义
InnoDB：MySQL默认的事务型引擎，也是最重要和使用最广泛的存储引擎。它被设计成为大量的短期事务，短期事务大部分情况下是正常提交的，很少被回滚。InnoDB的性能与自动崩溃恢复的特性，使得它在非事务存储需求中也很流行。除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎。
MyISAM：在MySQL 5.1 及之前的版本，MyISAM是默认引擎。MyISAM提供的大量的特性，包括全文索引、压缩、空间函数（GIS）等，但MyISAM并不支持事务以及行级锁，而且一个毫无疑问的缺陷是崩溃后无法安全恢复。
事务
InnoDB：支持
MyISAM：不支持
锁
InnoDB：支持行锁、表锁。行锁是实现在索引上的，如果没有索引，就没法使用行锁，将退化为表锁。
MyISAM：支持表锁。
主键
InnoDB：必须有，没有指定会默认生成一个隐藏列作为主键
MyISAM：可以没有
索引
InnoDB：聚集索引，使用 B+ 树作为索引结构，数据文件和索引绑在一起，必须要有主键。主键索引一次查询；辅助索引两次查询，先查询主键，再查询数据；
MyISAM：非聚集索引，使用 B+ 树作为索引结构，索引和数据文件是分离的。主键索引和辅助索引是独立的。
外键
InnoDB：支持
MyISAM：不支持
AUTO_INCREMENT
InnoDB：必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。
MyISAM：可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。
数据库文件
InnoDB：frm是表定义文件，ibd是数据文件。支持两种存储方式：
共享表空间存储：所有表的数据文件和索引都保存在一个表空间里，一个表空间可以有多个文件，通过 innodb_data_file_path 和 innodb_data_home_dir 参数设置共享表空间的位置和名字，一般共享表空间的名字叫 ibdata1-n。
多表空间存储：每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开关，以.ibd为扩展名
MyISAM：frm是表定义文件，myd是数据文件，myi是索引文件。支持三种存储格式：静态表（默认，注意数据末尾不能有空格，会被去掉。）、动态表、压缩表。
表的行数
InnoDB：没有保存。select count(*) from table;会扫描全表。
MyISAM：保存。select count(*) from table;会直接取出该值。
注：但加了 where 条件后，两者处理方式一样，都是扫描全表。
全文索引
InnoDB：5.7及以后版本支持。
MyISAM：支持。
总结
InnoDB：
优点：支持事务，支持外键，并发量较大，适合大量 update。
缺点：查询数据相对较快，不适合大量的 select。
MyISAM：
优点：查询数据相对较快，适合大量的 select，可以全文索引。
缺点：不支持事务，不支持外键，并发量较小，不适合大量 update。
14. 总结mysql索引作用，同时总结哪些查询不会使用到索引。
mysql索引作用：
- 索引可以降低服务器需要扫描的数据量，减少了IO次数
- 索引可以帮助服务器避免排序和使用临时表
- 索引可以帮助将随机I/O转换为顺序I/O
查询不会使用到索引：
1.在where条件中（包括group by以及order by）里用不到的字段不需要创建索引
2.数据量小的表最好不要使用索引
3.有大量重复数据的列上不要建立索引
4.避免对经常更新的表创建过多的索引
5.不建议使用无序的值作为索引
6.删除不再使用或者很少使用的索引
7.不要定义冗余或者重复的索引
15. 总结事务ACID事务特性
事务特性
- 原子性：整个事务中的所有操作要么全部成功执行，要么全部失败执行
- 一致性：数据库总是从一个一致性状态转换为另一个一致性状态类似于能量守恒定律
- 隔离性：事务没有提交时，不能看见
- 持久性：一旦事务提交，所做的修改会永久保存于数据库中
16. 总结事务日志工作原理。
redo log
是引擎层的日志，只有InnoDB引擎才有；是物理日志，记录的是“在某个数据页上做了什么修改”；是循环写的，空间固定会用完。有保证crash-safe能力，保证异常重启数据不丢失。
binlog
是server层的日志；是逻辑日志，记录的是这个语句的原始逻辑，比如“给ID=2这一行的c字段加1 ”；是可以追加写入的。主要用户备份。
undo log
回滚日志。
17. 总结mysql日志类型，并说明如何启动日志。
mysql日志类型
- 事务日志
- 错误日志
- 通用日志
- 慢查询日志
- 二进制日志
- 中继日志
启动日志
vim /etc/my.cnf
general_log
18. 总结二进制日志的不同格式的使用场景。
基于“语句”记录：statement，记录语句，默认模式，日志量较少
基于“行”记录：row，记录数据，日志量较大，更加安全，建议使用的格式
混合模式：mixed，让系统自行判定该基于那种方式进行
19. 总结mysql备份类型，并基于mysqldump, xtrabackup完成数据库备份与恢复验证。
mysql备份类型：
- 完全备份，部分备份
- 完全备份，增量备份（备份变化的数据），差异备份
- 冷（数据库停止服务），温（只能读不能写），热（读写都可）备份
- 物理（直接复制数据文件）和逻辑（从数据库中导出数据）备份
# 冷备份
[root@Rocky2 ~]# systemctl stop mysqld
[root@Rocky3 ~]# mkdir /data
[root@Rocky3 ~]# mkdir /data/binlog
[root@Rocky3 ~]# chown -R mysql. /data/binlog
[root@Rocky3 ~]# ll /data/binlog
total 0
[root@Rocky3 ~]# ll /data/
total 0
drwxr-xr-x 2 mysql mysql 6 Mar 3 16:33 binlog
[root@Rocky3 ~]# cat /etc/my.cnf
#
# This group is read both both by the client and the server
# use it for options that affect everything
#
[client-server]
#
# include all files from the config directory
#
!includedir /etc/my.cnf.d
[mysql]
13 prompt="(\u@\h) [\d]>\_"
14
15 [mysqld]
16 skip_name_resolve=1
17 log_error=/data/log_error/mysqld.log
18 general_log
19 log_bin=/data/binlog/mysql-bin
[root@Rocky3 ~]# systemctl restart mysqld
[root@Rocky2 ~]# cd /var/lib/mysql/
[root@Rocky2 mysql]# tar cf /opt/full.tar .
[root@Rocky2 mysql]# ll /opt
total 192252
-rw-r--r-- 1 root root 196864000 Mar 3 16:38 full.tar
drwxr-xr-x 2 root root 6 Mar 2 11:54 wordpress
[root@Rocky2 mysql]# scp /opt/full.tar 10.0.0.131:/opt
The authenticity of host '10.0.0.131 (10.0.0.131)' can't be established.
ED25519 key fingerprint is SHA256:TF4YvN/ZtHmVxnJ9mBOusJM/qtzNxJwfF8MlKq7WOyg.
This key is not known by any other names
Are you sure you want to continue connecting (yes/no/[fingerprint])? yes
Warning: Permanently added '10.0.0.131' (ED25519) to the list of known hosts.
root@10.0.0.131's password:
full.tar 100% 188MB 131.0MB/s 00:01
[root@Rocky2 mysql]# ls /data/binlog
mysql-bin.000001 mysql-bin.000002 mysql-bin.index
[root@Rocky2 mysql]# scp /data/binlog/* 10.0.0.131:/data/binlog/
root@10.0.0.131's password:
mysql-bin.000001 100% 774 884.1KB/s 00:00
mysql-bin.000002 100% 180 450.6KB/s 00:00
mysql-bin.index 100% 60 127.8KB/s 00:00
[root@Rocky3 ~]# ll /data/binlog
total 12
-rw-r----- 1 root root 774 Mar 3 16:41 mysql-bin.000001
-rw-r----- 1 root root 180 Mar 3 16:41 mysql-bin.000002
-rw-r----- 1 root root 60 Mar 3 16:41 mysql-bin.index
[root@Rocky3 ~]# chown -R mysql.mysql /data/binlog/
[root@Rocky3 ~]# ll /data/binlog
total 12
-rw-r----- 1 mysql mysql 774 Mar 3 16:41 mysql-bin.000001
-rw-r----- 1 mysql mysql 180 Mar 3 16:41 mysql-bin.000002
-rw-r----- 1 mysql mysql 60 Mar 3 16:41 mysql-bin.index
[root@Rocky3 ~]# rm -rf /var/lib/mysql/*
[root@Rocky3 ~]# tar xf /opt/full.tar -C /var/lib/mysql/
# mysqldump
[root@Rocky2 ~]# mysqldump -uroot -p111111 hellodb students > /backup/students.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
[root@Rocky2 ~]# ll /backup/
total 4
-rw-r--r-- 1 root root 2831 Mar 3 17:05 students.sql
(root@localhost) [(none)]> use hellodb
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A
Database changed
(root@localhost) [hellodb]> drop table students;
Query OK, 0 rows affected (0.01 sec)
(root@localhost) [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| emp |
| scores |
| teachers |
| toc |
| user |
+-------------------+
8 rows in set (0.00 sec)
(root@localhost) [hellodb]> source /backup/students.sql
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected, 1 warning (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 25 rows affected (0.00 sec)
Records: 25 Duplicates: 0 Warnings: 0
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.01 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
Query OK, 0 rows affected (0.00 sec)
(root@localhost) [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| emp |
| scores |
| students |
| teachers |
| toc |
| user |
+-------------------+
9 rows in set (0.00 sec)
(root@localhost) [hellodb]> select * from students;
+-------+---------------+-----+--------+---------+-----------+
| StuID | Name | Age | Gender | ClassID | TeacherID |
+-------+---------------+-----+--------+---------+-----------+
| 1 | Shi Zhongyu | 22 | M | 2 | 3 |
| 2 | Shi Potian | 22 | M | 1 | 7 |
| 3 | Xie Yanke | 53 | M | 2 | 16 |
| 4 | Ding Dian | 32 | M | 4 | 4 |
| 5 | Yu Yutong | 26 | M | 3 | 1 |
| 6 | Shi Qing | 46 | M | 5 | NULL |
| 7 | Xi Ren | 19 | F | 3 | NULL |
| 8 | Lin Daiyu | 17 | F | 7 | NULL |
| 9 | Ren Yingying | 20 | F | 6 | NULL |
| 10 | Yue Lingshan | 19 | F | 3 | NULL |
| 11 | Yuan Chengzhi | 23 | M | 6 | NULL |
| 12 | Wen Qingqing | 19 | F | 1 | NULL |
| 13 | Tian Boguang | 33 | M | 2 | NULL |
| 14 | Lu Wushuang | 17 | F | 3 | NULL |
| 15 | Duan Yu | 19 | M | 4 | NULL |
| 16 | Xu Zhu | 21 | M | 1 | NULL |
| 17 | Lin Chong | 25 | M | 4 | NULL |
| 18 | Hua Rong | 23 | M | 7 | NULL |
| 19 | Xue Baochai | 18 | F | 6 | NULL |
| 20 | Diao Chan | 19 | F | 7 | NULL |
| 21 | Huang Yueying | 22 | F | 6 | NULL |
| 22 | Xiao Qiao | 20 | F | 1 | NULL |
| 23 | Ma Chao | 23 | M | 4 | NULL |
| 24 | Xu Xian | 27 | M | NULL | NULL |
| 25 | Sun Dasheng | 100 | M | 1 | NULL |
+-------+---------------+-----+--------+---------+-----------+
25 rows in set (0.00 sec)
[root@Rocky2 ~]# mysqldump -uroot -p111111 -B hellodb > /backup/hellodb_B.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.
(root@localhost) [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| hellodb2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| wordpress |
| zabbix |
| zibbix |
+--------------------+
10 rows in set (0.00 sec)
(root@localhost) [hellodb]> drop database hellodb;
Query OK, 7 rows affected (0.01 sec)
(root@localhost) [(none)]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| wordpress |
| zabbix |
| zibbix |
+--------------------+
9 rows in set (0.01 sec)
(root@localhost) [hellodb]> \. /backup/hellodb_B.sql
(root@localhost) [hellodb]> show databases;
+--------------------+
| Database |
+--------------------+
| hellodb |
| hellodb2 |
| information_schema |
| mysql |
| performance_schema |
| sys |
| testdb |
| wordpress |
| zabbix |
| zibbix |
+--------------------+
10 rows in set (0.00 sec)
(root@localhost) [hellodb]> show tables;
+-------------------+
| Tables_in_hellodb |
+-------------------+
| classes |
| coc |
| courses |
| scores |
| students |
| teachers |
| toc |
+-------------------+
7 rows in set (0.01 sec)
mysqldump -uroot -p111111 -A --source-data=2 -F > /data/a.sql #备份全部数据库
20. 编写crontab，每天按表备份所有mysql数据。将备份数据放在以天为时间的目录下。
crontab -e
0 0 * * * mkdir -p /backup/mysql/$(date '+\%Y-\%m-\%d') && mysqldump -u xie -p111111 --all-databases --tables --single-transaction > /backup/mysql/$(date '+\%Y-\%m-\%d')/backup.sql
21. 编写crontab, 基于xtrabackup，每周1，周5进行完全备份，周2到周4进行增量备份。
crontab -e
#!/bin/bash
SHELL=/bin/bash
PATH=/usr/local/sbin:/usr/local/bin:/usr/sbin:/usr/bin:/root/bin
30 2 * * 1,5 xtrabackup -uroot -p123456 --backup --target-dir=/backup/base
30 2 * * 2-4 xtrabackup -uroot -p123456 --backup --target-dir=/backup/inc1 --incremental-basedir=/backup/base
22. 总结mysql主从复制原理。
23. 实现mysql主从复制，主主复制，半同步复制，过滤复制，
# 主从复制
主：
[root@master ~]# cat /etc/my.cnf
[mysql]
prompt="(\u@\h) [\d]>\_"
[mysqld]
server-id=8
log_bin=/data/binlog/mysql-bin
[root@master ~]# mysql
创建用户
(root@localhost) [(none)]> CREATE USER repluser@'10.0.0.%' IDENTIFIED WITH 'mysql_native_password' BY '111111';
Query OK, 0 rows affected (0.01 sec)
授权
(root@localhost) [(none)]> grant replication slave on *.* to repluser@'10.0.0.%';
Query OK, 0 rows affected (0.00 sec)
备份数据
[root@master ~]# mysqldump -A -F --single-transaction --master-data > /backup/full-`date +%F`.sql
scp /backup/full-2023-03-06.sql 10.0.0.131:/backup
从：
vim /etc/my.cnf
[mysqld]
server-id=18
read-only
systemctl restart mysqld
vim /backup/full-2023-03-06.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.130',
MASTER_USER='repluser',
MASTER_PASSWORD='111111',
MASTER_PORT=3306,
MASTER_LOG_FILE='mysql-bin.000013', MASTER_LOG_POS=157,
MASTER_LOG_POS=5;
关闭二进制
mysql>set sql_log_bin=0;
source /backup/full-2023-03-06.sql
set sql_log_bin=1;
开启从线程
start slave;
查看线程是否开启其
show processlist;
查看复制情况
show slave status\G;
# 主主复制
CHANGE MASTER TO
MASTER_HOST='10.0.0.131',
MASTER_USER='repluser',
MASTER_PASSWORD='111111',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=157;
# 半同步复制
主
查看插件
show plugins;
安装插件
(root@localhost) [(none)]> install plugin rpl_semi_sync_master soname 'semisync_master.so';
Query OK, 0 rows affected, 1 warning (0.01 sec)
删除插件：uninstall plugin rpl_semi_sync_master;
vim /etc/my.cnf
[mysqld]
server-id=8
log_bin=/data/binlog/mysql-bin
rpl_semi_sync_master_enabled
rpl_semi_sync_master_timeout=3000
[root@master ~]# systemctl restart mysqld
创建用户
(root@localhost) [(none)]> create user repluser@'10.0.0.%' identified by '111111';
Query OK, 0 rows affected (0.01 sec)
开启权限
(root@localhost) [(none)]> grant replication slave on *.* to repluser@'10.0.0.%' ;
Query OK, 0 rows affected (0.00 sec)
[root@master ~]# mysqldump -A -F --single-transaction --master-data > /data/all.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
[root@master ~]# scp /data/all.sql 10.0.0.131:/data
从
mysql> install plugin rpl_semi_sync_slave soname 'semisync_slave.so';
Query OK, 0 rows affected, 1 warning (0.00 sec)
vim /etc/my.cnf
[mysqld]
server-id=131
rpl_semi_sync_master_enabled
[root@Rocky3 ~]# systemctl restart mysqld
[root@Rocky3 ~]# vim /data/all.sql
MASTER_HOST='10.0.0.130',
MASTER_USER='repluser',
MASTER_PASSWORD='111111',
MASTER_PORT=3306,
[root@Rocky3 ~]# mysql < /data/all.sql
查看半同步信息
show global variables like '%semi%';
# 级联复制
mysql> select @@log_slave_updates;
+---------------------+
| @@log_slave_updates |
+---------------------+
| 1 |
+---------------------+
1 row in set, 1 warning (0.00 sec)
[root@slave1 ~]# mysqldump -A -F --single-transaction --master-data > /backup/all-`date +%F`.sql
WARNING: --master-data is deprecated and will be removed in a future version. Use --source-data instead.
[root@slave1 ~]# scp /backup/all-2023-03-06.sql 10.0.0.135:/data
[root@localhost ~]# cat /etc/my.cnf
[mysqld]
server-id=135
read-only
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql < /data/all-2023-03-06.sql
CHANGE MASTER TO
MASTER_HOST='10.0.0.131',
MASTER_USER='repluser',
MASTER_PASSWORD='111111',
MASTER_PORT=3306,
MASTER_LOG_FILE='binlog.000007', MASTER_LOG_POS=157;
mysql> show slave status\G;
24. 总结GTID复制原理，并完成GTID复制集群。
GTID工作原理
- 当一个事务在主库端执行并提交时，产生GTID，一同记录到binlog日志中。
- binlog传输到slave,并存储到slave的relaylog后，读取这个GTID的这个值设置gtid_next变量，即告诉Slave，下一个要执行的GTID值。(SET @@SESSION.GTID_NEXT= '18f5da07-a096-11ea-8c70-000c290e1abf:7'/*!*/;)
- sql线程从relay log中获取GTID，然后对比slave端的binlog是否有该GTID。
- 如果有记录，说明该GTID的事务已经执行，slave会忽略。
- 如果没有记录，slave就会执行该GTID事务，并记录该GTID到自身的binlog，在读取执行事前会先检查其他session持有该GTID，确保不被重复执行。
- 在解析过程中会判断是否有主键，如果没有就用二级索引，如果没有就用全部扫描。
# 主
vim /etc/my.cnf
server-id=1
gtid_mode=ON
enforce_gtid_consistency
systemctl restart mysqld
mysql>grant replication slave on *.* to 'repluser'@'10.0.0.%' identified by 'xie';
# 从
vim /etc/my.cnf
server-id=2
gtid_mode=ON
enforce_gtid_consistency
systemctl restart mysqld
# 如果主从数据库数据不一致，需要先将主数据库数据备份1还原至从数据库，在执行下面操作
mysqldump -A --master-data=2 > /backup/full.sql
mysql>CHANGE MASTER TO MASTER_HOST='10.0.0.135',MASTER_USER='repluser',MASTER_PASSWORD='xie',MASTER_PORT=3306,MASTER_AUTO_POSITION=1;
mysql>start slave;
25. 总结主从复制不一致的原因，如何解决不一致，如何避免不一致
造成主从不一致的原因：
- 主库binlog格式为statement，同步到从数据库执行后可能造成主从不一致
- 主库执行更更改前有执行set sql_log_bin=0，会使不记录binlog，从库也无法变更这部分数据
- 从节点未设置只读，误操作写入数据
- 主库或从库意外宕机，宕机会造成binlog或者relaylog文件出现损坏，呆滞主从不一致
- 主从版本不一致，特别是高版本是主，低版本为从的情况下，主数据库上面支持的功能，从数据库上面可能不支持
- 主从sql_mode不一致
- MySQL自身bug导致
26. 总结数据库水平拆分和垂直拆分
垂直切分：是根据业务来拆分数据库，同一类业务的数据表拆分到一个独立的数据库，另一类的数据表拆分到其他数据库。
水平切分：是按照某个字段的某种规则，把数据切分到多张数据表。一张数据表化整为零，拆分成多张数据表，这样就可以起到缩表的效果了。
27. 基于mycat实现读写分离
#10.0.0.110为客户端。
#10.0.0.40为mycat。
#10.0.0.10为主节点。
#10.0.0.20为从节点。
#注意：把mycat下载到/apps文件夹
***********************************************
#mycat执行命令在/apps/mycat/bin下，所以先把路径作为PATH变量写入脚本中
[root@Rocky8 ~]# vim /etc/profile.d/mycat.sh
PATH=/apps/mycat/bin:$PATH
#生效
[root@Rocky8 ~]# . /etc/profile.d/mycat.d
#安装java环境（前提是把内存调整到3G）
[root@Rocky8 ~]#yum -y install java
#修改配置mycat配置文件
[root@Rocky8 ~]# vim /apps/mycat/conf/server.xml
[root@Rocky8 ~]# vim /apps/mycat/conf/schema.xml
#在主节点建用户账号和授权（为的是让mycat去连接）
(root@localhost) [hellodb]> CREATE USER admin@ '10.0.0.40' IDENTIFIED BY '123456';
(root@localhost) [hellodb]> GRANT ALL ON hellodb.* TO admin@ '10.0.0.40';
#启动mycat
[root@Rocky8 ~]#mycat start
#查看日志表明mycat正常启动
[root@Rocky8 ~]#tail /apps/mycat/logs/wrapper.log
INFO | jvm 1 | 2023/01/10 20:05:58 | MyCAT Server startup successfully.
#用10.0.0.110客户端去连接mycat
[root@Rocky8 ~]#mysql -uroot -p123456 -h 10.0.0.40
********客户端通过mycat去写数据怎么知道是用的主节点服务器呢？*********
#在主从节点都开启通用日志（记录对数据库的通用操作）
[root@Rocky8 ~]#mysql -e 'SET GLOBAL GENERAL_LOG=1';
#在客户端修改数据然后在查数据
#通过tail -f 命令去监测主从的/var/lib/mysql/Rocky.log日志变化情况就能确定写操作是在主节点上发生的，查操作是在从节点上发生的。
28. 总结mysql高可用方案及高可用级别，搭建MHA集群和galera cluster，尝试搭建TIDB集群。
MySQL高可用方案是指通过使用多种技术手段，确保MySQL数据库的高可用性、可靠性和稳定性，从而保证业务的连续性和稳定性。以下是常见的MySQL高可用方案和高可用级别：
主从复制：主从复制是MySQL最常用的高可用方案之一，通过将数据从主库同步到多个从库上，实现读写分离和容灾备份等功能。
MHA集群：MHA（Master High Availability）是一个基于主从复制的高可用解决方案，可以自动检测主库故障，将从库提升为新的主库，并重建从库复制关系，从而实现MySQL数据库的自动故障转移。
Galera Cluster：Galera Cluster是一个基于同步复制的MySQL高可用解决方案，通过将多个MySQL节点组成一个集群，实现MySQL的自动容错和故障转移。
TiDB集群：TiDB是一个分布式数据库，通过将数据分散到多个节点上，实现MySQL的自动故障转移和分布式事务等功能，从而实现MySQL的高可用性和可伸缩性。
# MHA
cenos7上：
yum -y install mha4mysql-manager-0.58-0.el7.centos.noarch.rpm mha4mysql-node-0.58-0.el7.centos.noarch.rpm
其他机器上：
yum -y install mha4mysql-node-0.58-0.el7.centos.noarch.rpm
所有节点实现ssh的
cenos7上：
先生成公钥私钥对
ssh-keygen
ls .ssh
自己连自己
scp-copy-id 127.0.0.1
连接一下自己
ssh +自己的IP地址
exit
拷贝到其他三台机器上
rsync -a .ssh 10.0.0.130:/root/
rsync -a .ssh 10.0.0.131:/root/
rsync -a .ssh 10.0.0.132:/root/
验证是否成功
ssh 10.0.0.130
mkdir /etc/mastermha
vim /etc/mastermha/app1.cnf
[server default]
user=mhauser
password=magedu
manager_workdir=/data/mastermha/app1/
manager_log=/data/mastermha/app1/manager.log
remote_workdir=/data/mastermha/app1/
ssh_user=root
repl_user=xie
repl_password=111111
ping_interval=1
master_ip_failover_script=/usr/local/bin/master_ip_failover
report_script=/usr/local/bin/sendmail.sh
check_repl_delay=0
master_binlog_dir=/data/mysql/
[server1]
hostname=10.0.0.130
candidate_master=1
[server2]
hostname=10.0.0.131
[server3]
hostname=10.0.0.132
candidate_master=1
主机器上：
mysql> create user mhauser@'10.0.0.%' identified by 'magedu';
grant all on *.* to mhauser@'10.0.0.%';
ifconfig eth0:1 10.0.0.100/24
cenos7上：
vim /usr/local/bin/master_ip_failover
#!/usr/bin/env perl
# Copyright (C) 2011 DeNA Co.,Ltd.
#
# This program is free software; you can redistribute it and/or modify
# it under the terms of the GNU General Public License as published by
# the Free Software Foundation; either version 2 of the License, or
# (at your option) any later version.
#
# This program is distributed in the hope that it will be useful,
# but WITHOUT ANY WARRANTY; without even the implied warranty of
# MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
# GNU General Public License for more details.
#
# You should have received a copy of the GNU General Public License
# along with this program; if not, write to the Free Software
# Foundation, Inc.,
# 51 Franklin Street, Fifth Floor, Boston, MA 02110-1301 USA
## Note: This is a sample script and is not complete. Modify the script based on your environment.
use strict;
use warnings FATAL => 'all';
use Getopt::Long;
use MHA::DBHelper;
my (
$command, $ssh_user, $orig_master_host,
$orig_master_ip, $orig_master_port, $new_master_host,
$new_master_ip, $new_master_port, $new_master_user,
$new_master_password
);
my $vip = '10.0.0.100/24';
my $key = "1";
my $ssh_start_vip = "/sbin/ifconfig eth0:$key $vip";
my $ssh_stop_vip = "/sbin/ifconfig eth0:$key down";
GetOptions(
'command=s' => \$command,
'ssh_user=s' => \$ssh_user,
'orig_master_host=s' => \$orig_master_host,
'orig_master_ip=s' => \$orig_master_ip,
'orig_master_port=i' => \$orig_master_port,
'new_master_host=s' => \$new_master_host,
'new_master_ip=s' => \$new_master_ip,
'new_master_port=i' => \$new_master_port,
'new_master_user=s' => \$new_master_user,
'new_master_password=s' => \$new_master_password,
);
exit &main();
sub main {
if ( $command eq "stop" || $command eq "stopssh" ) {
# $orig_master_host, $orig_master_ip, $orig_master_port are passed.
# If you manage master ip address at global catalog database,
# invalidate orig_master_ip here.
my $exit_code = 1;
eval {
# updating global catalog, etc
$exit_code = 0;
};
if ($@) {
warn "Got Error: $@\n";
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "start" ) {
# all arguments are passed.
# If you manage master ip address at global catalog database,
# activate new_master_ip here.
# You can also grant write access (create user, set read_only=0, etc) here.
my $exit_code = 10;
eval {
print "Enabling the VIP - $vip on the new master - $new_master_host \n";
&start_vip();
&stop_vip();
$exit_code = 0;
};
if ($@) {
warn $@;
exit $exit_code;
}
exit $exit_code;
}
elsif ( $command eq "status" ) {
print "Checking the Status of the script.. OK \n";
`ssh $ssh_user\@$orig_master_host \" $ssh_start_vip \"`;
exit 0;
}
else {
&usage();
exit 1;
}
}
sub start_vip() {
`ssh $ssh_user\@$new_master_host \" $ssh_start_vip \"`;
}
# A simple system call that disable the VIP on the old_master
sub stop_vip() {
`ssh $ssh_user\@$orig_master_host \" $ssh_stop_vip \"`;
}
sub usage {
print
"Usage: master_ip_failover --command=start|stop|stopssh|status --orig_master_host=host --orig_master_ip=ip --orig_master_port=port --new_master_host=host --new_master_ip=ip --new_master_port=port\n";
}
加上执行权限
chmod +x /usr/local/bin/master_ip_failover
vim /usr/local/bin/sendmail.sh
echo "MHA is failover!" | mail -s "MHA Warning" 709995089@qq.com
chmod +x /usr/local/bin/sendmail.sh
主节点：
vim /etc/my.cnf
[mysqld]
log_bin=/data/mysql/mysql-bin
skip_name_resolve=1 #用来做地址反向解析
从节点：
vim /etc/my.cnf
[mysqld]
log_bin=/data/mysql/mysql-bin
skip_name_resolve=1
relay_log_purge=0
general_log
mkdir /data/mysql
chown -R mysql. /data/mysql
centOS7上:
检查ssh服务
masterha_check_ssh --conf=/etc/mastermha/app1.cnf
检查复制是否有问题
masterha_check_repl ---conf=/etc/mastermha/app1.cnf
启动mha
29. 总结mysql配置最佳实践。
基础规范：
(1)必须使用InnoDB存储引擎
解读：支持事务、行级锁、并发性能更好、CPU及内存缓存页优化使得资源利用率更高
(2)使用UTF8MB4字符集
解读：万国码，无需转码，无乱码风险，节省空间，支持表情包及生僻字
(3)数据表、数据字段必须加入中文注释
解读：N年后谁知道这个r1,r2,r3字段是干嘛的
(4)禁止使用存储过程、视图、触发器、Event
解读：高并发大数据的互联网业务，架构设计思路是“解放数据库CPU，将计算转移到服务层”，并发量
大的情况下，这些功能很可能将数据库拖死，业务逻辑放到服务层具备更好的扩展性，能够轻易实现
“增机器就加性能”。数据库擅长存储与索引，CPU计算还是上移吧！
(5)禁止存储大文件或者大照片
解读：为何要让数据库做它不擅长的事情?大文件和照片存储在文件系统，数据库里存URI多好。
命名规范：
thread_stack = 512K
# 二进制日志功能
log-bin=/data/mysqlbinlogs/
#二进制日志格式
binlog_format=row
#InnoDB使用一个缓冲池来保存索引和原始数据, 可设置这个变量到物理内存大小的80%
innodb_buffer_pool_size = 24G
#用来同步IO操作的IO线程的数量
innodb_file_io_threads = 4
#在InnoDb核心内的允许线程数量，建议的设置是CPU数量加上磁盘数量的两倍
innodb_thread_concurrency = 16
# 用来缓冲日志数据的缓冲区的大小
innodb_log_buffer_size = 16M
在日志组中每个日志文件的大小
innodb_log_file_size = 512M
# 在日志组中的文件总数
innodb_log_files_in_group = 3
# SQL语句在被回滚前,InnoDB事务等待InnoDB行锁的时间
innodb_lock_wait_timeout = 120
#慢查询时长
long_query_time = 2
#将没有使用索引的查询也记录下来
log-queries-not-using-indexes
(6)只允许使用内网域名，而不是ip连接数据库
(7)线上环境、开发环境、测试环境数据库内网域名遵循命名规范
业务名称：xxx
线上环境：xxx.db
开发环境：xxx.rdb
测试环境：xxx.tdb
从库在名称后加-s标识，备库在名称后加-ss标识
线上从库：xxx-s.db
线上备库：xxx-sss.db
(8)库名、表名、字段名：小写，下划线风格，不超过32个字符，必须见名知意，禁止拼音英文混用
(9)库名与应用名称尽量一致，表名:t_业务名称_表的作用，主键名:pk_xxx，非唯一索引名:idx_xxx，唯
一键索引名:uk_xxx
表设计规范：
(10)单实例表数目必须小于500
单表行数超过500万行或者单表容量超过2GB，才推荐进行分库分表。
说明：如果预计三年后的数据量根本达不到这个级别，请不要在创建表时就分库分表
(11)单表列数目必须小于30
(12)表必须有主键，例如自增主键
解读：
a)主键递增，数据行写入可以提高插入性能，可以避免page分裂，减少表碎片提升空间和内存的使用
b)主键要选择较短的数据类型， Innodb引擎普通索引都会保存主键的值，较短的数据类型可以有效的
减少索引的磁盘空间，提高索引的缓存效率
c) 无主键的表删除，在row模式的主从架构，会导致备库夯住
(13)禁止使用外键，如果有外键完整性约束，需要应用程序控制
解读：外键会导致表与表之间耦合，update与delete操作都会涉及相关联的表，十分影响sql 的性能，
甚至会造成死锁。高并发情况下容易造成数据库性能，大数据高并发业务场景数据库使用以性能优先
字段设计规范：
(14)必须把字段定义为NOT NULL并且提供默认值
解读：
a)null的列使索引/索引统计/值比较都更加复杂，对MySQL来说更难优化
b)null 这种类型MySQL内部需要进行特殊处理，增加数据库处理记录的复杂性;同等条件下，表中有较
多空字段的时候，数据库的处理性能会降低很多
c)null值需要更多的存储空，无论是表还是索引中每行中的null的列都需要额外的空间来标识
d)对null 的处理时候，只能采用is null或is not null，而不能采用=、in、<、<>、!=、not in这些操作符
号。如：where name!=’shenjian’，如果存在name为null值的记录，查询结果就不会包含name为null
值的记录
(15)禁止使用TEXT、BLOB类型
解读：会浪费更多的磁盘和内存空间，非必要的大量的大字段查询会淘汰掉热数据，导致内存命中率急
剧降低，影响数据库性能
(16)禁止使用小数存储货币
解读：使用整数吧，小数容易导致钱对不上
(17)必须使用varchar(20)存储手机号
解读：
a)涉及到区号或者国家代号，可能出现+-()
b)手机号会去做数学运算么?
c)varchar可以支持模糊查询，例如：like“138%”
(18)禁止使用ENUM，可使用TINYINT代替
解读：
a)增加新的ENUM值要做DDL操作
b)ENUM的内部实际存储就是整数，你以为自己定义的是字符串?
索引设计规范：
(19)单表索引建议控制在5个以内
(20)单索引字段数不允许超过5个
解读：字段超过5个时，实际已经起不到有效过滤数据的作用了
(21)禁止在更新十分频繁、区分度不高的属性上建立索引
解读：
a)更新会变更B+树，更新频繁的字段建立索引会大大降低数据库性能
b)“性别”这种区分度不大的属性，建立索引是没有什么意义的，不能有效过滤数据，性能与全表扫描类
似
(22)建立组合索引，必须把区分度高的字段放在前面
解读：能够更加有效的过滤数据
SQL使用规范：
(23)禁止使用SELECT *，只获取必要的字段，需要显示说明列属性
解读：
a)读取不需要的列会增加CPU、IO、NET消耗
b)不能有效的利用覆盖索引
c)使用SELECT *容易在增加或者删除字段后出现程序BUG
(24)禁止使用INSERT INTO t_xxx VALUES(xxx)，必须显示指定插入的列属性
解读：容易在增加或者删除字段后出现程序BUG
(25)禁止使用属性隐式转换
解读：SELECT uid FROM t_user WHERE phone=13812345678 会导致全表扫描，而不能命中phone
索引，猜猜为什么?(这个线上问题不止出现过一次)
(26)禁止在WHERE条件的属性上使用函数或者表达式
解读：SELECT uid FROM t_user WHERE from_unixtime(day)>='2017-02-15' 会导致全表扫描
正确的写法是：SELECT uid FROM t_user WHERE day>= unix_timestamp('2017-02-15 00:00:00')
(27)禁止负向查询，以及%开头的模糊查询
解读：
a)负向查询条件：NOT、!=、<>、!<、!>、NOT IN、NOT LIKE等，会导致全表扫描
b)%开头的模糊查询，会导致全表扫描
(28)禁止大表使用JOIN查询，禁止大表使用子查询
解读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能
(29)禁止使用OR条件，必须改为IN查询
解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮
助实施查询优化呢?
(30)应用程序必须捕获SQL异常，并有相应处理
读：会产生临时表，消耗较多内存与CPU，极大影响数据库性能
(29)禁止使用OR条件，必须改为IN查询
解读：旧版本Mysql的OR查询是不能命中索引的，即使能命中索引，为何要让数据库耗费更多的CPU帮
助实施查询优化呢?
(30)应用程序必须捕获SQL异常，并有相应处理
30. 总结openvpn原理，并完成1键安装不同版本vpn脚本，可以适配rocky, ubuntu, centos主机。同时支持添加账号，注销账号。
openvpn原理
#!/bin/bash
# 安装OpenVPN
if [[ -f /etc/rocky-release ]]; then # 检查Rocky Linux发行版
yum -y install epel-release
yum -y install openvpn
elif [[ -f /etc/centos-release ]]; then # 检查CentOS发行版
yum -y install epel-release
yum -y install openvpn
elif [[ -f /etc/lsb-release ]]; then # 检查Ubuntu发行版
apt-get update
apt-get -y install openvpn
else
echo "不支持的操作系统"
exit 1
fi
# 添加账号
add_user() {
echo "请输入要添加的用户名："
read username
echo "请输入要添加的密码："
read password
cd /etc/openvpn/easy-rsa/
. ./vars
./easyrsa build-client-full $username nopass
mkdir -p /etc/openvpn/client-configs/files/$username
cp /etc/openvpn/easy-rsa/pki/ca.crt /etc/openvpn/easy-rsa/pki/issued/$username.crt /etc/openvpn/easy-rsa/pki/private/$username.key /etc/openvpn/client-configs/files/$username/
cat /etc/openvpn/client-configs/make_config.sh | sed "s/USERNAME/$username/g" > /etc/openvpn/client-configs/files/$username/$username.ovpn
sed -i "s/remote my-server-1 1194/remote yourserverip 1194/g" /etc/openvpn/client-configs/files/$username/$username.ovpn
echo -e "$username\t$password" >> /etc/openvpn/creds
echo "已添加用户：$username"
}
# 删除账号
del_user() {
echo "请输入要删除的用户名："
read username
cd /etc/openvpn/easy-rsa/
. ./vars
./easyrsa --batch revoke $username
./easyrsa gen-crl
rm -f /etc/openvpn/client-configs/files/$username/*
rm -f /etc/openvpn/creds
touch /etc/openvpn/creds
sed -i "/^$username\t/d" /etc/openvpn/creds
echo "已删除用户：$username"
}
# 主菜单
menu() {
echo "请选择操作："
echo "1. 添加账号"
echo "2. 删除账号"
echo "3. 退出"
read choice
case $choice in
1) add_user;;
2) del_user;;
3) exit;;
*) echo "无效的选择";;
esac
}
# 运行主菜单
while true; do
menu
done
848
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
