mysql随笔

一.MySql基础

1.MySql特点

• 关系型数据库
• 跨平台
• 支持多种编程语言
• 支持磁盘存储

2.启动连接

systemctl start mysqld

3.客户端连接

mysql -hIP -u[user] -p[password]

4.基本SQL命令

1. 查看已有库

   show databases;
   

2. 创建库并指定字符集

   create database [库名] charset utf8;
   

3. 查看当前所在库

   select database();
   

4. 切换库

   use [库名]
   

5. 查看库中所有表

   show tables;
   

6. 删除库

   drop database [库名]
   

5.表管理

1. 创建表并指定字符集

   `create table [表名]([字段名] [字段类型] xxx,)charset=utf8`
   

2. 查看创建表的语句(字符集，存储引擎)

   show create table [表名]
   

3. 查看表结构

   desc [表名]
   

4. 删除表

   drop table 表名1,表名2,...
   

6.表记录管理

1. 增：insert into [表名](字段名) values(),(),...;
2. 删：delete from 表名 where 条件
3. 改：update 表名 set [字段名]=值,[字段名]=值 where [条件];
4. 查：select [字段名] from [表名];

7.字段管理

1. 增：alter table 表名 add 字段名 first|after 字段名
2. 删：alter table 表名 drop 字段名;
3. 改：alter table 表名 modify 字段名 新类型;
4. 重命名：rename table 旧表名 to 新表名;

8.数据类型

• 数值类型
  int smallint bigint tinyint float(m,n) double decimal
• 字符类型
  char() varchar() text blob
• 枚举类型(不用)
  enum()-单选 set()-->多选
• 日期时间类型
  date time year datetime timestamp

9.日期时间函数

NOW() CURDATE() YEAR(字段名) DATE(字段名) TIME(字段名)

10.日期时间运算

sleect * from 表名 where 字段名 运算符 (NOW()-interval 间隔);
间隔单位: 1 day|2 month|3 year
* 查询一年前用户充值信息
select * from user where time <(NOW() - interal 1 year);

11.数字比较

# 运算符
> >= < <= = !=
* 查询成绩不合格的学生
select name from stu where grades < 60;

• 删除成绩不合格的学生
  delete from stu where grades < 60;
• 把id为3的学生姓名改为周芷若
  update tabel stu set name=‘周芷若’ where id = 3;

12.逻辑比较

• 逻辑运算符
  and or
• 查询成绩不及格的男生

  select * from stu where grades<60 and sex='m'
  

• 查询成绩在60-70之间的学生

  select * from stu where grades <=70 or grades >=60;
  

13.范围内

between 值1 and 值2 | in() | not in()

• 查询不及格的学生姓名及成绩
  select name,grades from stu where grades between 0 and 59;
• 查询AID1905和AID1903班的学生姓名及成绩
  select name,grades from stu where class in ('AID1905','AID1903');

14.模糊查询

where 字段名 like ('%_')
%: 0或多个字符
_: 单个字符

• 查询北京的姓赵的学生信息
  select * from stu where add='北京' and name like '赵%';

15.NULL 判断

select * from stu where name is NULL;

16.order by 排序

order by 字段名 desc|asc

17.limit

limit n :显示前n条
limit m,n : 从第(m+1)条记录开始，显示n条
分页: 每页显示10条，显示第6页的内容
公式: limit (m-1)*n n
m: 要显示的页数
n: 每页要显示的条数

18.mysql多表联合查询

1. INNER JOIN（内连接）：返回两个表中满足连接条件的行。只有在连接条件匹配时，INNER JOIN 才返回结果。

SELECT * FROM table1
JOIN table2 ON table1.column_name = table2.column_name;

2.LEFT JOIN（左连接）：返回左表中的所有行以及满足连接条件的右表中的匹配行。如果右表中没有匹配的行，则返回 NULL 值。

SELECT * FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

3.RIGHT JOIN（右连接）：返回右表中的所有行以及满足连接条件的左表中的匹配行。如果左表中没有匹配的行，则返回 NULL 值。

SELECT * FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

4.FULL JOIN（全连接）：返回左表和右表中的所有行，并在结果中使用 NULL 值填充没有匹配的行。

SELECT * FROM table1
FULL JOIN table2 ON table1.column_name = table2.column_name;

5.JOIN 条件进行多表联接，例如使用多个 ON 子句或使用 WHERE 子句中的逻辑运算符（AND、OR）来指定连接条件。

SELECT * FROM table1
JOIN table2 ON table1.column_name = table2.column_name
JOIN table3 ON table2.column_name = table3.column_name
WHERE table1.column_name = 'value';

二.MySql高级

1.建库建表

create database country charset 'utf8';
use country;
create table sanguo(
    id int ptimary key auto_increment,
    name varchar(15),
    gender char(2),
    country varchar(15),
    attack int,
    defense int
)charset='utf8';

2.插入数据

• null字段为了防止批量插入报错

insert into sanguo values(null,'诸葛亮','m','蜀国',60,160),
                        (null,'司马懿','m','魏国',70,170),
                        (null,'貂蝉','f','吴国国',60,120),
                        (null,'张飞','m','蜀国',80,190),
                        (null,'赵云','m','蜀国',100,210);

3.练习

• 将赵云的攻击力设置360,防御力设置为180

  update  sanguo set attack=360,defense=180 where name='赵云';
  

• 将蜀国的攻击力按从高到底排序

  select * from sanguo where country='蜀国' order by attack desc;
  

• 魏蜀两国英雄名字中为三个字的按防御升序排列

  select * from sanguo where country in ('魏国','蜀国') and name like '___' order by defense;
  

4.MySql普通查询语句执行顺序

FROM 子句：确定要查询的表以及它们之间的关联关系。
WHERE 子句：筛选符合指定条件的记录。只有符合条件的记录才会进入下一步处理。
GROUP BY 子句：按指定列对记录进行分组。
HAVING 子句：对分组后的数据进行条件筛选。
SELECT 子句：指定要检索的列。
DISTINCT：删除结果集中的重复行（如果使用了 DISTINCT 关键字）且在distinct 右面的字段不能做聚合。
ORDER BY 子句：对结果集中的记录进行排序。
LIMIT 子句：仅返回指定数量的记录，用于控制结果集的大小。

5.聚合查询

• COUNT：计算指定列中的行数（可选地根据条件计数）。

  SELECT COUNT(column_name) FROM table_name;
  

• SUM：计算指定列的总和。

  SELECT SUM(column_name) FROM table_name;
  

• AVG：计算指定列的平均值。

  SELECT AVG(column_name) FROM table_name;
  

• MAX：找出指定列的最大值。

  SELECT MAX(column_name) FROM table_name;
  

• MIN：找出指定列的最小值。

  SELECT MIN(column_name) FROM table_name;
  

• GROUP_CONCAT：将指定列的值连接为一个字符串。

  SELECT GROUP_CONCAT(column_name) FROM table_name;
  

• HAVING：在GROUP BY查询中使用，根据指定条件筛选分组。

  SELECT column_name FROM table_name GROUP BY column_name HAVING condition;
  

• 练习

1. 找出平均攻击力大于105的国家的前2名，显示国家名称和平均攻击力

   select country,avg(attack) avg_attack 
   from sanguo 
   group by country 
   having avg_attack > 105 
   order by attack desc 
   limit 2;
   

2. 所有国家英雄中，英雄数量最多的前两名的国家名称及数量

   select country, count(id) num 
   from sanguo 
   group by country 
   order by num desc 
   limit 2;
   

3. 表中有哪些国家？

   select distinct country from sanguo;
   

4. 计算有多少个国家？

   select count(distinct country) from sanuo;
   

   注意：在distinct 右边的字段不能做聚合，在左边可以

6.索引(Mysql使用B+)

1. B树的特点： 全部节点均包含索引(id)+数据(‘Tom_1’)
   从根节点遍历至指定数据

2. B+树的特点: 非叶子节点只保存索引【树宽度优于B树，从而降低了磁盘IO】
   叶子节点保存所有的索引和数据
   叶子之间相互连接，形成链表结构

3. 索引优点
   加快数据检索速度

4. 有无索引对比

• 开启时间检测：show variables like '%pro%';
set profiling=1;
• 执行查询语句(五索引): select name from user where name='Tom_99999';
• 查看执行时间：show profiles;
• 在name字段创建索引：create index name on user(name);
• 再次查询：select name from user where name='Tom_99999';
• 查看执行时间：show profiles;
• 关闭时间检测: set profiling=0;

5. 索引使用规则

• 选择合适的索引列：
  选择在查询条件和连接条件中经常被用到的列作为索引列。这样可以加快对这些列的搜索和过滤操作。
• 考虑索引的选择性：
  选择具有高选择性的列作为索引列。选择性是指索引列上具有不同值的比例。选择性越高，索引会更加有效。 例如，一个列只有两个不同的值（二进制列或布尔列）可能具有很高的选择性。
• 避免过多的索引：
  过多的索引可能会降低性能，并增加数据插入、更新和删除操作的开销。只有在需要的地方创建索引，避免创建不必要的冗余索引。
• 考虑复合索引：
  如果多个列一起经常被用于查询条件或连接条件，可以考虑创建复合索引。复合索引是包含多个列的索引，可以提供更好的查询性能， 特别是当这些列常常一起出现在查询中时。
• 注意索引的顺序：
  在创建复合索引时，要注意索引列的顺序。将最常用于查询条件的列放在索引的前面。这样可以更有效地利用复合索引，提高查询性能。
• 避免使用过长的索引：
  创建索引时，避免使用过长的列作为索引列，因为长索引占用更多的存储空间，并且可能会降低索引的效率。
• 定期维护和优化索引：
  为了保持索引的性能，定期进行索引的维护和优化是很重要的。这可以包括重新组织索引、重新生成统计信息、删除不再需要的索引等操作。
• 注意索引的影响：
  在创建和修改索引时，要注意索引对数据的影响。索引可能会增加查询性能，但同时也会增加数据插入、更新和删除的开销。因此，要综合考虑索引的使用情况和对性能的影响。

6. 创建索引

• 建表时创建

  create table 表名(
      字段名 数据类型,
      字段名 数据类型,
      index(字段名),
      index(字段名),
      unique(字段名)
  );
  eg1:
  create table student(
      id int primary key auto_increment,
      name varchar(20),
      phone char(11),
      index(name),
      unique(phone)
  );
  

• 在已有表中加索引
  create [unique] index 索引名 on 表名(字段名);

• 查看索引
  show index from 表名\G;

• 删除索引
  drop index 索引名 on 表名;

7.外键

• 定义：让当前表字段的值在另一个表的范围内选择
• 语法：

  foreign key(参考字段名)
  references 主表(被参考字段名)
  on delete 级联动作
  on update 级联动作
  

• 使用规则：

1. 主从表字段数据类型要一致
2. 主表被参考字段：KEY的一种，一般为主键
   eg1: 班级缴费表(外键)

   create database if not exists db2 charset utf8;
   create table master(
       id int primary key,
       name varchar(24),
       class char(7),
       money decimal(6,2)
   )charset='utf8';
   insert into master values(1,'唐伯虎','AID1903',300),
                               (2,'秋香','AID1903',300),
                               (3,'祝枝山','AID1903',300),
                               (4,'周文宾','AID1903',300);
   

• cascade类型

  create table if not exists slave(
      stu_id int primary key,
      name varchar(24),
      money decimal(6,2),
      foreign key(stu_id) references master(id)
      on delete cascade on update cascade    
  )charset='utf8';
  insert into slave values(1,'唐伯虎',300),
                          (2,'秋香',300),
                          (3,'祝枝山',300),
                          (4,'周文宾',300);
                          
              
  insert into master values(5,'AID1904','张飞',300);
  insert into slave values(5,'张飞',300);
  

• restrict 类型

  create table if not exists slave2(
      stu_id int primary key,
      name varchar(24),
      money decimal(6,2),
      foreign key(stu_id) references master(id) 
  )charset='utf8';  
  insert into slave2 values(1,'唐伯虎',300),
                          (2,'秋香',300),
                          (3,'祝枝山',300),
                          (4,'周文宾',300);
  

• set null类型

  create table if not exists slave3(
  # 被参考字段不能为主键
  stu_id int,
  name varchar(24),
  money decimal(6,2),
  foreign key(stu_id) references master(id) 
  on delete set null on update set null
  )charset='utf8';  
  insert into slave3 values(1,'唐伯虎',300),
                          (2,'秋香',300),
                          (3,'祝枝山',300),
                          (4,'周文宾',300);  
  

• 数据插入|删除规则

  级联动作:
  cascade：数据级别删除,更新(参考字段)-->从主表中更新删除数据，从表关联的数据也会被删除
  restrict(默认)：从表有相关记录,不允许主表操作
  set null：主表更新，删除，从表相关联字段值被设为NULL 
  

• 删除外键
  show create table slave2\G; # 查看外键名 CONSTRIANT:
alter table 表名 drop foreign key 外键名

8.数据导入导出

• 数据导入

  方式1： source 路径/*.sql
  方式2：以.csv文件导入
  语法:
  load data infile "文件名"
  into table 表名
  fields terminated by "分隔符"
  lines terminated by "\n";
  

• 数据导出语法

  select  ... from 表名
  into outfile "文件名"
  fields terminated by "分隔符"
  lines terminated by "\n";
  eg1:
  scoretable.csv文件导入到数据库db2中
  

1. 将scoretable.csv放到数据库搜索路径中

   查看搜索路径: 
   show variables like 'secure_file_priv';
   +------------------+-----------------------+
   | Variable_name    | Value                 |
   +------------------+-----------------------+
   | secure_file_priv | /var/lib/mysql-files/ |
   +------------------+-----------------------+
   

2. 建表

   create table scoretable(
       rank int,
       name varchar(20),
       score float(5,2),
       phone char(11),
       class char(7)
   )charset='utf8';
   

3. 导入

   load data infile "/var/lib/mysql-files/scoretable.csv"
   into table scoretable
   fields terminated by ','
   lines terminated by '\n';
   

4. 添加id字段，要求主键自增，显示宽带为3，位数不够用0填充
   alter table scoretable add id int(3) zerofill primary key auto_increment first;

5. 将/etc/passwd的内容添加到数据库

   create table l_user(
       username varchar(50) not null,
       passwd char(10),
       uid int primary key,
       gid int not null,                        
       description varchar(50),
       home_dir varchar(50),
       bash varchar(50)
   )charset='utf8';
   

• 导入

  
  load data infile "/var/lib/mysql-files/passwd"
  into table l_user
  fields terminated by ':'
  lines terminated by '\n';
  

• 导出

      select  * from l_user
      into outfile "/var/lib/mysql-files/passwd.csv"
      fields terminated by ","
      lines terminated by "\n";
  

9.存储引擎

• 查看所有引擎
  show engines;
• 查看已有表的引擎
  show create table 表名
• 创建表并指定

  create table (
      ...
  )engine=MyISAM,charset=utf8,auto_increment=10000;
  

• InnoDB引擎：

  1.默认的MySQL存储引擎，可以提供ACID（原子性、一致性、隔离性和持久性）事务支持。
  2.支持行级锁定，具有良好的并发性能。
  3.支持外键约束和崩溃恢复能力。
  4.提供了基于日志的恢复机制，以确保数据的一致性和持久性。
  5.适用于事务性应用和较大的数据库。
  6.表字段和索引存储在一个文件中
      表名.frm: 表结构
      表名。ibd: 表记录及索引文件
  

• MyISAM引擎：

  1.早期版本的MySQL默认存储引擎，但在MySQL 5.5.5中开始被InnoDB引擎取代。
  2.不支持事务和外键约束，对于读密集型应用有较好的性能。
  3.表级锁定意味着在写入期间对整个表加锁，可能导致并发性能问题。
  4.轻量级，存储效率高，适用于简单的查询和读写少的应用。
  

Memory引擎（也称为Heap引擎）：
1.将数据存储在内存中，对于读写操作速度非常快。 2.不支持持久性，重启数据库后，数据将丢失。 3.不支持文本和大字段对象。 4.适用于缓存表、临时表和数据仓库等场景。
Archive引擎：
1.以高压缩率为目标，适合存储和查询大量存档或历史数据。 2.只支持插入和查询操作，不支持更新和删除。 3.不支持索引，查询效率较低。 4.适合于数据归档和只读的数据仓库。
NDB Cluster引擎（也称为MySQL Cluster）：
1.基于共享存储的集群引擎，提供高可用性和扩展性。 2.支持分布式存储和并行查询处理。 3.具有自动分区和负载均衡功能。 4.适用于大规模的分布式应用，如大型Web应用和实时数据处理。

• 存储引擎选择

  1.执行查操作多的表用 MyISAM
  2.执行写操作多的表用 InnoDB
  3.临时表 MEMORY
  

三.MySql权限管理

1. 添加授权用户

1.使用root登录
    mysql -uroot -p
2. 添加用户
    CREATE USER 'newuser'@'%' IDENTIFIED BY 'password';
3. 删除用户
  
    1.特定：drop user "username"@"%|localhost"
    2.通配(以test开头的用户)：drop user "test%"

2.授权

grant 权限列表 on 库.表 to "用户名"@"%" identified by "密码" with grant option;
1.刷新权限
flush privileges;

3.权限列表

1.SELECT：允许用户读取（查询）数据库中的数据。

2.INSERT：允许用户向数据库表中插入新的数据。

3.UPDATE：允许用户修改数据库表中的数据。

4.DELETE：允许用户删除数据库表中的数据。

5.DROP：允许用户删除数据库、表或索引。

6.CREATE：允许用户创建新的数据库、表或索引。

7.ALTER：允许用户修改现有数据库、表或索引的结构。

8.GRANT OPTION：允许用户将自己拥有的权限授予其他用户或角色。

9.ALL PRIVILEGES：表示用户具有对指定数据库或表所有权限。

此外，还有其他一些特殊权限，用于管理和控制MySQL服务器的功能和配置，例如：

10.CREATE USER：允许用户创建新用户。

11.RELOAD：允许用户重新加载访问权限表。

12.SHUTDOWN：允许用户关闭MySQL服务器。

13.PROCESS：允许用户查看正在运行的进程。

14.SUPER：允许用户执行管理员级别的操作。

4. 更改用户密码

# 需要登陆mysql更改
ALTER USER 'root'@'localhost' IDENTIFIED BY 'new_password';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' WITH GRANT OPTION;
FLUSH PRIVILEGES;

四.MySQL事务

1.事务

1. 启用事务：
`在事务开始之前，需要启用事务功能。可以使用 START TRANSACTION 或 BEGIN 语句来启动一个新的事务。

2. 执行事务操作：
在开启事务后，可以执行一系列的数据库操作，例如插入、更新、删除等。

3. 提交或回滚事务：
根据事务执行的结果，可以选择提交（COMMIT）或回滚（ROLLBACK）事务。
4. 提交事务：
如果事务中的所有操作都成功完成，可以使用 COMMIT 语句来提交事务，将更改保存到数据库。

5. 回滚事务：
如果在事务执行期间发生错误或意外情况，可以使用 ROLLBACK 语句来回滚事务，将所有更改撤销。

6. 案例

create table bank(
    id int primary key,
    name varchar(20),
    balance decimal(20,2)
)charset='utf8';

-- 启动事务
BEGIN|START TRANSACTION;

-- 执行事务操作
INSERT INTO bank  VALUES (null,'John', 20000),
                         (null,'Jack', 10000);
UPDATE bank SET balance = balance - 20002 WHERE id = 1;
-- 提交事务
commit;

2. 事务四大特性

1.原子性（Atomicity）：原子性确保事务的所有操作要么全部成功提交，要么全部回滚。如果一个操作失败，整个事务将被回滚到事务开始之前的状态，不会保留部分修改。使用 START TRANSACTION 或 BEGIN 命令开始一个事务，使用 COMMIT 提交事务或 ROLLBACK 回滚事务。

2.一致性（Consistency）：一致性确保事务执行前后数据库处于一致的状态。数据库必须符合预定义的完整性约束条件，包括主键约束、外键约束等。如果事务执行期间违反了约束条件，事务将回滚到事务开始之前的状态。

3.隔离性（Isolation）：隔离性确保并发执行的事务之间相互隔离，使得每个事务感觉就像是在独立执行。隔离性要求事务在提交之前对其他事务不可见，防止数据出现不一致或幻读的问题。

4.持久性（Durability）：持久性确保一旦事务提交后，对数据库的修改将永久保存，即使在系统崩溃或重启后也能恢复。MySQL 将已提交的事务日志写入磁盘或其他持久存储设备，以保证数据的持久性。

3. MySQL 事务隔离级别：

1.读未提交（Read Uncommitted）：最低级别的隔离级别，事务中的修改对其他事务可见，即使它们尚未提交。这种级别可能导致脏读（Dirty Read）问题，即读取到未提交的数据。

2.读已提交（Read Committed）：每个查询只能看到已提交的数据。在同一个事务中的不同查询可能会看到不同的结果，可能导致不可重复读（Non-repeatable Read）问题。

3.可重复读（Repeatable Read）：保证在同一个事务中多次读取相同数据时，结果始终一致。在该级别下，同一事务中的查询看不到其他事务所做的修改（未提交数据），可以防止脏读和不可重复读，但仍可能出现幻读（Phantom Read）问题。

4.串行化（Serializable）：最高级别的隔离级别，强制事务串行处理，确保每个事务在完全隔离的环境中执行。在该级别下，事务顺序执行，并发性能较差，但可以避免所有并发问题，包括脏读、不可重复读和幻读。

4. 修改MySQL的事务隔离级别

1.设置全局级别：SET GLOBAL TRANSACTION ISOLATION LEVEL <隔离级别>;

2.设置会话级别：SET SESSION TRANSACTION ISOLATION LEVEL <隔离级别>;

• 案例

-- 设置会话级别隔离级别为可重复读（Repeatable Read）
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- 开始事务
START TRANSACTION;

-- 执行事务操作
-- ...

-- 提交事务
COMMIT;

5.ER，实体，关系，属性

1. 实体: 表
2. 属性: 字段

• 实体之间的关系

一对一关联(1:1)
一对多关联(1:n)
多对多关联(m:n)

6.关系映射实现

1. 1:1实现 -->主外键关联，外键字段添加唯一索引

表t1： id int ptimary key,
表t2： t2_id int unique, #unique为1:1的关键
        foreign key(t2_id) references t1(id)

2. 1:n实现 -->主外键关联

表t1： id int ptimary key,
表t2： t2_id int,
        foreign key(t2_id) references t1(id)

3. m:n实现

eg1: 课程 老师 （一门课程可以有多个老师教，一个老师可以交多门课程）
create table teacher(
	id int primary key,
	name varchar(20),
	level varchar(20)
)charset='utf8';

insert into teacher values(1,'郭小闹','牛X'),(2,'老魏','牛XX');
insert into teacher values(3,'郭小非','牛X'),(4,'老张','牛XX');

create table course(
	id int primary key,
	name varchar(20)
)charset='utf8';
insert into course values(1,'Django'),(2,'mysql'),(3,'Js');
insert into course values(4,'Numpy'),(5,'Cs'),(6,'Html');
select * from teacher;

create table middle(
	id int primary key,
	t_id int,
	c_id int,
	foreign key(t_id) references teacher(id), 
	foreign key(c_id) references course(id)
)charset='utf8';    

insert into middle values(1,1,1),(2,1,2),(3,1,2),(4,2,2)(5,2,4)(6,3,5),(7,4,6),(8,4,3),(9,3,2),(10,3,4);

1. 每个老师在研究什么课题

select 
    t.name,c.name
from 
    teacher t
inner join 
    middle m
on
    t.id = m.t_id
inner join 
    course c
on 
    c.id = m.c_id;

2. 郭小闹在研究什么课程？

select 
    t.name,c.name
from 
    teacher t
inner join 
    middle m
on
    t.id = m.t_id
inner join 
    course c
on 
    c.id = m.c_id
where
    t.name = '郭小闹';

create table middle(
	id int primary key,
	t_id int,
	c_id int,
	foreign key(t_id) references teacher(id), 
	foreign key(c_id) references course(id)
)charset='utf8';    

insert into middle values(1,1,1),(2,1,2),(3,1,2),(4,2,2)(5,2,4)(6,3,5),(7,4,6),(8,4,3),(9,3,2),(10,3,4);

1. 每个老师在研究什么课题

select 
    t.name,c.name
from 
    teacher t
inner join 
    middle m
on
    t.id = m.t_id
inner join 
    course c
on 
    c.id = m.c_id;

2. 郭小闹在研究什么课程？

select 
    t.name,c.name
from 
    teacher t
inner join 
    middle m
on
    t.id = m.t_id
inner join 
    course c
on 
    c.id = m.c_id
where
    t.name = '郭小闹';

mysql 运维

mysql一主一从同步配置

主库

• 开启binlog及server_id

# cat /etc/my.cnf
[mysqld]
server_id=106
log_bin=master106   //这里也可以定义新目录

• 登录MySQL查看主库状态及binlog名称和pos

show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| master106.000001 |      684 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+

• 授权用户

grant replication slave on *.* to repuser@'%' identified by 'PASSWORD';

从库

• 添加server_id

[mysqld]
server_id=107

• 指定为从库

change master to master_host="192.168.122.3",master_user="repuser",master_password="123qWe!@#",master_log_file="master3.000001",master_log_pos=154;

• 开启从库

start slave;

• 查看从库状态

show slave status;

注意: 当克隆包含mysql服务的主机时，需要更改/var/lib/mysql/auto.cnf的uuid

测试

• 在主库上建库建表

create database if not exists school  default charset utf8;
use school;
create table class (
 	id int primary key auto_increment,
 	name varchar(20) not null,
 	score int,
 	grade varchar(10) not null
 );	 	

• 插入数据

insert into school.class values(null,'sz',81,'3年级1班');
insert into school.class values(null,'lz',78,'3年级1班');
insert into school.class values(null,'wz',88,'3年级1班');
insert into school.class values(null,'sf',98,'3年级1班');
insert into school.class values(null,'st',68,'3年级1班');
insert into school.class values(null,'qz',79,'3年级1班');
insert into school.class values(null,'tz',72,'3年级1班');

• 在从库上查看数据

mysql -uroot -p'PASSWORD' -e 'select * from school.class;'
+----+------+-------+-------------+
| id | name | score | grade       |
+----+------+-------+-------------+
|  1 | sz   |    81 | 3年级1班    |
|  2 | lz   |    78 | 3年级1班    |
|  3 | wz   |    88 | 3年级1班    |
|  4 | sf   |    98 | 3年级1班    |
|  5 | st   |    68 | 3年级1班    |
|  6 | qz   |    79 | 3年级1班    |
|  7 | tz   |    72 | 3年级1班    |
+----+------+-------+-------------+

配置一主多从

完全备份主库中现有的数据(在新增从库的清况下)

• 备份单数据库

# --master-data:记录数据位置和日志名称
mysqldump -uroot -p'PASSWORD' --master-data classes >/tmp/cla.mysql

• 在新增的从库上恢复数据

// 单个数据库时，需要登录MySQL建库
create database classes default charset utf8;
// 恢复数据
mysql -uroot -p'PASSWORD' classes < /tmp/cla.sql 

• 备份多个数据库

# --master-data:记录数据位置和日志名称
mysqldump -uroot -p'PASSWORD' --master-data -B classes school >/tmp/more.mysql

• 在新增的从库上恢复数据

// 多个数据库时，不需要建库

// 恢复数据
mysql -uroot -p'PASSWORD'</tmp/more.sql 

• 查看日志名称和pos

grep "CHANGE MASTER" more.sql
CHANGE MASTER TO MASTER_LOG_FILE='master106.000001', MASTER_LOG_POS=6996;

• 指定为从库

change master to master_host="192.168.122.125",master_user="repuser",master_password="123qWe!@#",master_log_file="master125.000001",master_log_pos=154;

• 开启从库

start slave;

• 查看从库状态

show slave status;

测试

• 在主库插入数据

insert into school.class values(null,'zf',81,'3年级2班');
insert into school.class values(null,'lz',78,'3年级2班');
insert into school.class values(null,'dz',88,'3年级2班');
insert into school.class values(null,'sf',98,'3年级2班');
insert into school.class values(null,'et',68,'3年级2班');
insert into school.class values(null,'bz',79,'3年级2班');
insert into school.class values(null,'yz',72,'3年级2班');

• 验证

mysql -uroot -p'PASSWORD' -e 'select * from school.class;'
+----+------+-------+-------------+
| id | name | score | grade       |
+----+------+-------+-------------+
|  1 | sz   |    81 | 3年级1班    |
|  2 | lz   |    78 | 3年级1班    |
|  3 | wz   |    88 | 3年级1班    |
|  4 | sf   |    98 | 3年级1班    |
|  5 | st   |    68 | 3年级1班    |
|  6 | qz   |    79 | 3年级1班    |
|  7 | tz   |    72 | 3年级1班    |
|  8 | zf   |    81 | 3年级2班    |
|  9 | lz   |    78 | 3年级2班    |
| 10 | dz   |    88 | 3年级2班    |
| 11 | sf   |    98 | 3年级2班    |
| 12 | et   |    68 | 3年级2班    |
| 13 | bz   |    79 | 3年级2班    |
| 14 | yz   |    72 | 3年级2班    |
+----+------+-------+-------------+

将slave节点恢复

systemctl stop mysqld
cd /var/lib/mysql
rm -f master.info relay-log.info  slave1-relay-bin.*
systemctl start mysqld

同理可将master节点恢复

配置主从从结构

架构

OS	IP	ROLE
centos7	192.168.122.5	master
centos7	192.168.122.6	master and slave
centos7	192.168.122.7	slave

master节点配置

• 配置server_id并开启binlog
• 授权用户

master and slave节点配置

• 配置server_id并开启binlog及级联复制

# cat /etc/my.cnf
[mysqld]
server_id=6
log_bin=master6   //这里也可以定义新目录
log_slave_updates //开启级联复制

作为master节点

• 授权用户

作为slave节点

• 指定为从库
• 开启从库

slave 节点配置

• 指定为从库
• 开启从库

主主结构(不需要开启级联复制)

OS	IP	ROLE
centos7	192.168.122.5	master slave
centos7	192.168.122.6	master slave

master节点

• 配置server_id并开启binlog
• 授权用户

slave 节点配置

• 指定为从库
• 开启从库

主从复制模式

• 异步复制模式(默认)
  异步复制是指主服务器（Master）将数据更改写入二进制日志（Binary Log）后，就立即返回给客户端，而无需等待备份服务器（Slave）进行数据同步。这种复制方式具有较高的性能，因为主服务器无需等待备份服务器的响应。但是，由于主服务器和备份服务器之间存在一定的延迟，因此在主服务器出现故障时，备份服务器的数据可能不是最新的，会有数据丢失的风险。

• 全同步复制模式
  主服务器将数据更改写入二进制日志后，会等待备份服务器将数据同步完成，然后才返回给客户端。这种复制方式能够保证主服务器和备份服务器的数据一致性，因为数据在写入主服务器后立即被复制到备份服务器。但是，由于需要等待备份服务器的响应，所以全同步复制的性能较低，会对主服务器的性能产生一定的影响

• 半同步复制模式
  介于异步复制和全同步复制之间的一种方式。主服务器将数据更改写入二进制日志后，会等待至少一个备份服务器将数据同步完成，然后才返回给客户端。这种复制方式既保证了数据的一致性，又减少了对主服务器性能的影响。

将异步修改为半同步复制模式

• 查看当前MySQL的复制模式，可以使用以下命令：

SHOW VARIABLES LIKE 'rpl_semi_sync_master_enabled';
SHOW VARIABLES LIKE 'rpl_semi_sync_slave_enabled';
 

如果返回的值都是OFF，则表示当前是异步复制模式。

• 首先要确保MySQL版本是支持半同步复制的，可以使用以下命令查看MySQL版本：

SELECT VERSION();

如果版本低于5.5.16或者5.6.6，则需要升级MySQL版本。

单角色时

• master节点的my.cnf文件

# 添加
[mysqld]
plugin-load = "rpl_semi_sync_master=semisync_master.so"
rpl_semi_sync_master_enabled=1

• slave节点的my.cnf文件

# 添加
[mysqld]
plugin-load = "rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_slave_enabled=1

是master又是slave角色时

# 添加
[mysqld]
plugin-load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
rpl_semi_sync_master_enabled=1
rpl_semi_sync_slave_enabled=1

• 查看结果

SHOW VARIABLES LIKE 'rpl_semi_sync_%_enabled';

数据读写分离

架构

OS	IP	ROLE
centos7	192.168.122.5	master
centos7	192.168.122.6	slave
centos7	192.168.122.7	middle

客户端访问数据的的读(select),写(insert，update, delete)分别给不同的数据库处理

实现方式

• 通过程序实现
  select ----->192.168.122.5
  insert update delete --------> 192.168.122.6
• 配置中间件服务实现
  例如 mysql-proxy mycat maxcale 等可以实现读写分离

配置代理服务器

• maxscale下载: https://downloads.mariadb.com/MaxScale/
• 安装

rpm -ivh maxscale-2.1.2-1.centos.7.x86_64.rpm

• 配置maxscale.cnf

[maxscale]
threads=auto
[server1]
type=server
address=192.168.122.106
port=3306
protocol=MySQLBackend
[server2]
type=server
address=192.168.122.3
port=3306
protocol=MySQLBackend
# 用于监控主和从的server
[MySQL Monitor]
type=monitor
module=mysqlmon
servers=server1, serevr2
user=maxscalemon
passwd=123qWe!@#
monitor_interval=10000
# 用于判断主从是否有可以访问数据的用户
[Read-Write Service]
type=service
router=readwritesplit
servers=server1, server2
user=maxscalerouter
passwd=123qWe!@#
max_slave_connections=100%
[MaxAdmin Service]
type=service
router=cli
[Read-Write Listener]
type=listener
service=Read-Write Service
protocol=MySQLClient
port=4006
[MaxAdmin Listener]
type=listener
service=MaxAdmin Service
protocol=maxscaled
socket=default
port=4016

由于实现了主从同步，所以在主服务器上添加用户后会自动同步到从服务器

• 添加监控用户maxscalemon用于监控主和从的server

grant replication slave, replication client on  *.*  to maxscalemon@'%' identified by "123qWe!@#";

• 添加路由用户用于判断主从是否有可以访问数据的用户

grant select on  mysql.*  to maxscalerouter@'%' identified by "123qWe!@#";

• 启动代理服务

maxscale -f /etc/maxscale.cnf

验证

登录maxscale管理

maxadmin -uadmin -pmariadb -P 4016

• 查看状态

MaxScale> list servers
Servers.
-------------------+-----------------+-------+-------------+--------------------
Server             | Address         | Port  | Connections | Status              
-------------------+-----------------+-------+-------------+--------------------
server1            | 192.168.122.106 |  3306 |           0 | Master, Running
server2            | 192.168.122.3   |  3306 |           0 | Slave, Running

• 在主服务器添加可访问数据库内容的用户

grant select,insert,delete,update on school.* to jim@'%' identified by '123qWe!@#';

• 通过代理连接

mysql -h 192.168.122.125 -P4006 -ujim -p'123qWe!@#'

如何验证实现了读写分离

• 在从库中插入数据，主库上是查不到的，通过代理服务器连接查询，可以查询到
• 通过连接代理服务器插入数据，它会先往主服务器中写入，再同步到从服务器
• 以上2中结果都通过，则证明实现了读写分离

mysql多实例

支持多实例的mysql url：https://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz

• 安装

tar -xf mysql-5.7.20-linux-glibc2.12-x86_64.tar.gz
mv mysql-5.7.20-linux-glibc2.12-x86_64 /usr/local/mysql5.7.20

• 配置
  待续…

mysql集群MHA

架构

OS	IP	ROLE
centos7	192.168.122.5	客户端
centos7	192.168.122.6	主库
centos7	192.168.122.7	从库
centos7	192.168.122.8	从库
centos7	192.168.122.9	管理
centos7	192.168.122.100	vip

部署

• 环境准备

# 系统自带的perl包
yum -y install perl-*

• 配置管理主机