Mysql大总结 请君收下

1.mysql安装

windows下安装：参考文档

linux下安装：参考文档

2.mysql客户端（Navicat）

Navicat安装：参考文档

3.一条查询执行过程

连接管理

作用：处理连接请求、安全认证、授权等

客户端与mysql连接方式：TCP/IP、命名管道、共享内存、套接字等几种方式

mysql会创建一个线程专门处理与这个客户端的交互

解析优化

1）查询缓存

        mysql会把查询结果缓存起来，如果第二次同样的查询进来，就可以不去底层查询数据，直接从缓存中返回数据。

        缓存不命中：两个查询只要有字符上的不一致就不命中，即使语意上是一样的（所以必须说完全一样的话，mysql才认为是一样的）；查询缓存中包含了某些系统字段、用户自定义变量和函数、系统表，则也不会命中（例如now()函数，两次调用时间本身就不一样）。

        缓存失效：缓存会监控涉及的每一张表，只要有表结构和数据被修改（update），则与该表有关的所有缓存全部失效。所以慎用缓存

2）语法解析

        将受到的sql文本信息进行解析，提取信息，如涉及的表、条件等信息。

3）查询优化

        收到语法解析后的查询信息后，对整个sql进行一定规则的优化，并生成执行计划，用户可以使用explain关键字查看。

存储引擎

        mysql把数据的存储和提取操作都封装在了存储引擎中，存储引擎负责将数据存储在物理存储上，向我们表现出数据是存储为表中的一行数据（逻辑上）。

        mysql在5.5.5版本后，默认使用的存储引擎为innoDB，在这之前默认使用的是mylsam

1）innodb和mylsam区别

1.支持事务的区别：innodb支持事务，mylsam不支持事务

2.支持外键的区别：innodb支持外键，mylsam不支持外键

3.数据存储方式：innodb主键的B+树的叶子检点存储的数据，辅助索引的叶子节点是主键；mylsam的B+树的叶子节点存储的都是数据的地址指针

4.innodb不存表的具体行数（支持事务，不同事务行数不同），count(*)需要全表扫描，mylsam用一个变量存储行数。

5.锁粒度：innodb存在表、行级锁，mylsam只有表级锁。

6.存储文件：innodb：frm存表定义文件，ibd是数据文件；mylsam：frm表定义文件，myd是数据文件，myi是索引文件。

4.常用语法（齐全）

库相关语法

//登陆
1.mysql -h ip -u root -p -P 3306 
//创建库
2.create database 【if not exists】 库名 【character set 字符集名】;
//修改库
3.alter database 库名 character set 字符集名;
//删除库
4.drop database 【if exists】 库名;
//显示所有数据库
5.show databases
//选择数据库
6.use database_name
//执行sql文件
7.source test.sql
//查看系统字段
8.show variables like 'sql_mode'
//不使用缓存
9.set session query_cache_type = off 

表相关语法

//创建表
1.create table 【if not exists】 表名(…字段名 字段类型 【约束】,...)
//查看表结构
2.desc table_name
//查看所有表
3.show tables 
//添加列
4.alter table 表名 add column 列名 类型 【first|after 字段名】;
//修改列类型&约束
5.alter table 表名 modify column 列名 新类型 【新约束】;
//修改列名称
6.alter table 表名 change column 旧列名 新列名 类型;
//删除列
7.alter table 表名 drop column 列名;
//修改表名
8.alter table 表名 rename 【to】 新表名;
//删除表
9.drop table【if exists】 表名;
//复制表
10.create table 表名 like 旧表;
//复制表结构&数据
11.create table 表名 select 查询列表 from 旧表【where 筛选】;

索引相关语法

//创建索引
1.create [unique|fulltext|spatial] index index_name[using index_type] on table_name (index_col_name,...)
//删除索引
2.alter table table_name drop index index_name;
//查看索引
3.show index from table_name;

插入数据语法

//插入多行
1.insert into 表名(字段名,…) values(值,…);
//子查询插入
2.insert into 表名 (查询语句);

删除数据语法

1.delete from 表名 【where 筛选条件】【limit 条目数】;

修改数据语法

1.update 表名 set 字段=值,字段=值 【where 筛选条件】;

查询数据语法

//综合
1.select 查询列表 from 表1 【别名 连接类型 join 表2 on 连接条件 where 筛选 group by 分组列表 having 筛选 order by排序列表 limit 起始条目索引，条目数】;
//通配符
2.select 查询列表 from 表名 where 列名 like 'test%'
//子查询
3.select 查询列表 from 表名 where 列名 in（子查询）
//子查询为真才有结果
4.select 查询列表 form 表名 where exists （子查询）

表连接查询语法

//返回两个表中联结字段相等的行
1.select * from table1 A inner join table2 B on A.id=B.id;
//左表中的所有记录和右表中联结字段
2.select * from table1 A left join table2 B on A.id=B.id;相等的记录
//右表中的所有记录和左表中联结字段相等的记录
3.select * from table1 A right join table2 B on A.id=B.id;

视图相关语法

//创建视图
1.create view 视图名 as select 语句；
//删除视图
2.drop view 视图名

union集语法

//不重复并集
1.[SELECT 语句 1] UNION [SELECT 语句 2];
//重复并集
2.[SELECT 语句 1] UNION ALL [SELECT 语句 2];

case语法

1.select case （列名） 
  when '条件1' then '结果1' 
  when '条件2' then '结果2'
  【else '结果N'】
  end
  from 表名

存储过程语法

1.DELIMITER $$	           #将语句的结束符号从分号;临时改为两个$$(可以是自定义)
      CREATE PROCEDURE Proc()				#创建存储过程，过程名为Proc，不带参数
      -> BEGIN								#过程体以关键字 BEGIN 开始
      -> select * from 表名;			    #过程体语句
      -> END $$								#过程体以关键字 END 结束
      DELIMITER ;		                    #将语句的结束符号恢复为分号
	  2.call Proc;                          #调用存储过程
	  3.SHOW CREATE PROCEDURE [数据库.]存储过程名;		#查看某个存储过程的具体信息
	  4.DROP PROCEDURE IF EXISTS Proc;      #仅当存在时删除，如果指定的过程不存在，则产生一个错误

备份及同步语法

//备份
1.mysqldump -u root -p --databases test > /opt/test.sql 
//恢复
2.mysql -u root -p < /opt/test.sql

//增量同步
3.mysqladmin -u root -p flush-logs
//恢复2021-01-01 00:00:00之前数据
4.mysqlbinlog --no-defaults --stop-datetime='2021-01-01 00:00:00' /usr/mysql-bin.000001 |mysql -uroot -p;
//恢复2021-01-01 00:00:00之后数据
5.mysqlbinlog --no-defaults--start-datetime='2021-01-01 00:00:00' /usr/mysql-bin.000001 |mysql -uroot -p;
	

函数

1）数学函数

1.abs(x) 绝对值
2.rand() 随机数（0,1）
3.mod(x,y) 余数
4.power(x,y) 乘方
5.round(x) 四舍五入取整
6.round(x,y) 四舍五入
7.sqrt(x) 平方根
8.truncate(x,y) 截断
9.ceil(x) 向上取整
10.floor(x) 向下取整
11.greatest(x1,x2…) 最大值
12.least(x1,x2…) 最小值

2）聚合函数

1.avg() 平均值
2.count() 个数
3.min() 最大值
4.max() 最小值
5.sum() 和

3）字符串函数

1.trim() 去除指定格式值
2.concat(x,y) 拼接
3.substr(x,y) 截断
4.substr(x,y,z) 截断
5.length(x) 长度
6.upper(x)  转大写
7.replace(x,y,z) 替换
8.lower(x) 转小写
9.left(x,y) 前几个字符
10.right(x,y) 后几个字符
11.repeat(x,y) 重复
12.space(x) 空格
13.strcmp(x,y) 比较x和y 返回-1,0,1
14.reverse(x) 反转

5.存储结构

        我们知道数据最终是存储在物理存储中的，而我们使用mysql查看数据的时候，查看到的数据是存储为一张表中一行一行的数据。这其中便是mysql存储引擎在发挥作用，现在让我们来看看，存储引擎到底是怎么样管理这些数据的呢？

通过几张图来详细描述一下：

1）行结构（用户每一行的数据）

变长字段长度列表：记录每一列可变长度的具体长度，如varchar类型

Null值列表：存储该行记录中列为null的记录，每一列我1位，1代表值为null

头信息：记录是否被删除、堆中相对位置、下一条数据位置等信息

2）数据页结构（固定16KB）

          页大小：每一页规定大小16KB，巧妙的利用了磁盘的预读原理，磁盘预读数据大小通常为（4k）

         数据存储的格式：页中的每条记录都使用单向链表的形式指向下一条记录，以虚拟记录中最小记录为开头，最大记录为结尾。

        数据分组规则：将数据进行分组，当一组中的数据为8条记录时，再插入一条则分为两组，一组为4条记录，一组为5条记录。

        页目录：页目录保存的是每个数据分组中，最后的那条数据的偏移量，这样每次来扫描这个页查询数据的时候，就通过二分法这个页目录中快速定位到哪一组，然后再查询到组内数据数据。

3）B+树（整张表）

        每一张表中的数据，以上图树的形式进行存储，也就是我们平时所说的B+树的存储方式。其中紫色方块代表数据类型：0表示用户数据，1表示目录项数据，2表示最小虚拟数据，3表示最大虚拟数据。蓝色方块为主键值，黄色方块为数据。每一层的不同数据页都使用双向链表进行连接。

        一颗树中的数据，是按照主键id按照从小到顺序进行存储的。

        仅有叶子节点数据页存储用户数据，其他非叶子节点存储是目录项；目录项中的黄色方块，记录的是指针，指向下层的数据页的页码，用于快速定位到数据页，这样设计的目的是为了减少树的高度。

        仅叶子节点存储用户数据为什么能够减少树的高度呢？这里做个简要的计算，假设主键id大小为4字节，用户数据一行为1000字节，由于每一页大小为16KB=16384字节。

        每一页可存储主键+指针（6字节）数量=16384/（4+6）=1638

        每一页可存储的数据行数量=16384/1000=16

则：

        仅叶子节点存储用户数据，2层可存数据量=1638*16=26208行数据，3层可存数据量=1638*1638*16=4000万行数据

        所有节点存储用户数据，2层可存数据量=16*16=256行数据，3层可存数据量=16*16*16=4096行数据

        这样看来，B+树的形式存储数据，确实可以减少树的高度。而每一页数据的获得都需要经过一次磁盘IO，当树的层数增加时，代表需要扫描更多数据页才能获得最终数据，这对于我们是非常不利的。

6.索引

        每个索引都是一棵B+树，B+树只有最下面一层叶子节点存储用户记录，其它层都为内节点存储的是目录项。B+树存储的用户记录都是有序的，按从小到大排序。

1）索引的作用

        将用户数据按照某些列进行升序排序，则查找的时候速度非常快，降低查询时间。

2）聚簇索引

        InnoDB会自动为主键建立一个索引，这个索引就叫聚簇索引，在这个索引上的叶子节点记录着用户的全部数据。如果没有显示的指定主键或者没有声明不允许存null的unique键，则数据库将会隐式的添加一个主键建立聚簇索引，用来存用户的所有数据。

3）二级索引

        除了聚簇索引外，用户可以为自己想要的列建立索引，这些索引叫二级索引。与聚簇索引不同的是，二级索引叶子节点记录的不是用户的全部数据，而是建立索引的列+主键值。

4）联合索引

        为多个列建立的索引叫联合索引。例如：为（id，name）建立联合索引，则这棵树先按照id进行升序排序，id相同则按name进行排序。

5）回表

        由于二级索引的树上所存储的用户数据仅有建立索引的列（可以是单列或者多列）+主键，如果我们查询的数据还包含了其它列的数据，那么在这棵树中就满足不了要求。只能通过在这棵树上查到对应的主键值，然后再去聚簇索引（记录着用户全部数据）那棵树上找对应的数据，这就叫回表。

6）覆盖索引

        为我们所需要数据的所有列建立联合索引，以减少回表操作，这样的索引叫覆盖索引。

7）索引代价

        空间上代价：每一个索引都是一棵B+树，都需要有额外的存储空间存储这棵树的数据。

        时间上代价：由于每个索引中的用户数据都是按升序排序的，所以表中的数据发生增删改都需要对整个B+树进行调整（页面分裂、页面回收），这些都是耗时的。

        所以为一个表建立的索引越多，占用的存储空间就越大，增删改数据的性能也会越差。并不是为了满足查询要求，建立索引越多越好，这需要综合的进行损耗和收益两方面的考虑。

8）索引在查询执行期间如何发挥作用的

        如果我们为表中的key1和key2列分别建立二级索引：

        select * from table where (key1 >=5 and key1<=50) and (key2 >=11 and key2<=20)

        那么在key1索引上的扫描区间为[5,50]，key2索引上的扫描区间为[11,20]，则查询的路线有三条：

        a.在key1上扫描[5,50]区间取得数据，然后根据主键回表查询所有数据判断key2列的值是否符合；

        b.在key2上扫描[11,20]区间取得数据，然后根据主键回表查询所有数据判断key1列的值是否符合；

        c.直接在聚簇索引上全部扫描（-∞，+∞），判断key1和key2列条件是否符合。

        三条查询路线具体选择哪一条，mysql会帮我们选择消耗最小的那一条。

        如果我们没有为key1和key2列建立索引，那扫描路线指引c，没得选择。

        简单来说就是，按照where条件生成对应的扫描区间，计算查询到数据有几条路线，分别计算每一条路线所消耗的资源，选择消耗资源最小的执行，这个最优的选择就是我们平时说的执行计划

9）如何更好的建立索引

        a.只为用于查询、分组、排序的列建立索引

        b.考虑索引列的离散性，比如性别就不适合，只有男女两总情况，即使排序了又能怎么样。比如查询性别为男的全部数据，这将有一大批数据进行回表操作，还不如直接在聚簇索引上进行全表扫描。

        c.索引列的类型尽量小，二级索引中，存储的数据为索引列+主键，如果索引列的类型比较小时，那么一页（16k）中存储的数据将会更多，更有效的减少I/O操作。

        d.主键索引尽量采用自增id，因为索引需要按照升序排序，则如果采用自增id，本身插入数据，只要一直往尾部插入就可以。如果采用uuid，本身是随机的，插入时会造成大量的页面分裂操作，更加耗时

        e.如果某一列的前缀离散性就很好，可以为其前缀建立索引。

        f.多建立覆盖索引，减少回表

10）索引失效分析

1.全值匹配：条件与索引一一对应

2.最左前缀法则

3.不在索引列上做运算

4.索引中使用范围，右边列索引失效

5.尽量使用覆盖索引，减少select *

6.！=或者<>索引失效

7.is null 和is not null索引失效

8.like左边通配符索引失效

9.字符串不加单引号索引失效

10.少用or，用它连接时，索引失效

7.单表查询&多表关联

1）单表查询

        单表查询根据执行查询语句的方式不同分为了多种访问方法：

        const：通过主键列来定位到一条数据

        ref：二级索引列与常数进行等值比较

        ref_or_null：二级索引列与常数进行等值比较+该列值为null

        range：对应扫描区间为若干个单点、一个扫描区间、多个扫描区间

        index：在二级索引中全表扫描

        all：在聚簇索引中全表扫描

2）多表关联

连接原理

        mysql采用的多表连接算法为嵌套循环连接，类似于我们平时所说的多重for循环。外层表（驱动表）选取一条数据，扫描内层表（被驱动表）符合条件则加入结果集，然后外层表继续取下一条数据。

使用索引加快连接速度

        由于外层表有多少行数据，则就需要扫描多少次内层表，如果内层表都是全表扫描，那得多慢啊！这时可以为内层表的连接条件列建立索引，加快每次扫描内层表的时间。

基于块的嵌套循环连接

       如果被驱动（内层表）表的数据量非常大，那么驱动表（外层表）的每一行数据就去扫描一次被驱动表，这样也是非常耗时的。 mysql中有一块叫join buffer连接缓冲区，可以将外层表的数据存入在该缓冲区，然后对内层表进行一次扫描连接就可以了。

表连接的总体步骤

1.选取驱动表，使用与驱动表相关的过滤条件，使用代价最小的单表查询数据。

2.对1中查询的结果集中的每一条数据，都分别到被驱动表中匹配记录。

驱动表和被驱动表选择

如果是内连接，mysql优化器会通过计算，选择最优的那个表为驱动表。

如果是左连接，左表为驱动表

如果是右连接，右表为驱动表

8.优化

1）基于成本优化

        I/O成本：读取每页数据所耗的成本------1.0

        CPU成本：检查一条记录是否符合条件-------0.2

        上面所说的，每一个查询，可能存储多条查询路线，mysql会通过计算选择最优的路线执行查询。这个最优的路线就是我们平时所说的执行计划。而选择最优路线的依据，就是将每个路线的成本具体量化（I/O成本+CPU成本），然后选择数值最低的那条路线。

        上面成本常亮是可以自己调整的

2）基于成本的优化步骤

        1.根据搜索条件，找出所有可能使用的所有

        2.计算全表扫描（聚簇索引上）的代价

        3.计算使用不同索引执行查询的代价（如果需要回表就加上回表代价）

        4.对比各个路线，找出成本最低的路线，生成执行计划

3）EXPLAIN 

explain+查询语句：查询这个语句的执行计划

explain+ format=json ：查询这个语句json格式的执行计划

explain查询执行计划后，输入show warnings 查询执行计划的相关扩展信息

这部分大家总结的材料太多了，参考文档

4）使用optimizer trace查询优化器具体工作过程

1.打开optimizer trace功能：set optimizer_trace="enabled=on"

2.输入查询

3.从optimizer trace表中查看查询的优化过程：select * from information_schema.OPTIMIZER_TRACE;

4.关闭功能：set optimizer_trace="enabled=off"

5）解决I/O速度慢和CPU速度快的Buffer Pool

找到一篇好文，大家参考文档

9.日志

1）更新一条数据的所有过程

2）redo log

重做日志： Redo Log 如果要存储数据则先存储数据的日志 ， 一旦内存崩了 则可以从日志找。
重做日志保证了数据的可靠性，InnoDB采用了Write Ahead Log（预写日志）策略，即当事务提交
时，先写重做日志，然后再择时将脏页写入磁盘。如果发生宕机导致数据丢失，就通过重做日志进行数据恢复。

好处：redo log占用空间非常小，redo log是顺序写磁盘，速度快。

Force Log at Commit机制实现事务的持久性，即当事务提交时，必须先将该事务的所有日志写入到重做日志文件进行持久化，然后事务的提交操作完成才算完成。为了确保每次日志都写入到重做日志文件，在每次将重做日志缓冲写入重做日志后，必须调用一次fsync操作（操作系统），将缓冲文件从文件系统缓存中真正写入磁盘。

Double Write双写：提高innodb的可靠性，用来解决部分写失败(partial page write页断裂)。

由两部分组成，一部分是内存中的double write buffer，大小为2MB，另一部分是物理磁盘上共享表空间连续的128个页，大小也为2MB。在对缓冲池的脏页进行刷新时，并不直接写磁盘，而是通过memcpy函数将脏页先复制到内存中的double write buffer区域，之后通过double write buffer再分两次，每次1MB顺序地写入共享表空间的物理磁盘上，然后马上调用fsync函数，同步磁盘，避免操作系统缓冲写带来的问题。在完成double write页的写入后，再将double write buffer中的页写入各个表空间文件中。如果操作系统在将页写入磁盘的过程中发生了崩溃，在恢复过程中，InnoDB存储引擎可以从共享表空间中的double write中找到该页的一个副本，将其复制到表空间文件中，再应用重做日志。

Double Write详细参考文档

3）undo log

        undo log记录了数据在每个操作前的状态，如果事务执行过程中需要回滚，就可以根据undo log进行回滚操作。

        undo page和存储数据库数据和索引的页类似。因为redo log是物理日志，记录的是数据库页的物理修改操作。所以undo log（也看成数据库数据）的写入也会产生redo log，也就是undo log的产生会伴随着redo log的产生，这是因为undo log也需要持久性的保护。

详细可参考文档

10.事务隔离级别和MVCC

        所谓的MVCC解决的是RR和RC隔离级别下，普通的select操作时，访问记录版本链的问题。这样可以使不同事务的读-写、写-读操作并发执行。

1)事务具有ACID四大特性

        原子性(atomicity) ：事务最小工作单元，要么全成功，要么全失败 。
        一致性(consistency)： 事务开始和结束后，数据库的完整性不会被破坏 。
        隔离性(isolation) ：不同事务之间互不影响，四种隔离级别为RU（读未提交）、RC（读已提
交）、RR（可重复读）、SERIALIZABLE （串行化）。
        持久性(durability) ：事务提交后，对数据的修改是永久性的，即使系统故障也不会丢失 。

2）并发事务可能导致的一致性问题

        丢失更新：两个事务针对同一数据都发生修改操作时，会存在丢失更新问题。
        脏读：一个事务读取到另一个事务未提交的数据。
        不可重复读：一个事务因读取到另一个事务已提交的update或者delete数据。导致对同一条记录读取两次以上的结果不一致。
        幻读：一个事务因读取到另一个事务已提交的insert数据。导致对同一张表读取两次以上的结果不一致。

3）mysql四种隔离级别

         Read uncommitted (读未提交)：最低级别，任何情况都无法保证。
         Read committed (RC，读已提交)：可避免脏读的发生。
         Repeatable read (RR，可重复读)：可避免脏读、不可重复读的发生。
（注意事项：InnoDB的RR还可以解决幻读，主要原因是Next-Key（Gap）锁，只有RR才能使用
Next-Key锁）
         Serializable (串行化)：可避免脏读、不可重复读、幻读的发生。

4）版本链

        每次记录进行一次改动，都会记录一条undo日志，每条undo日志都会有一个roll_pointer属性（insert记录没有），通过这个属性可以将这些undo日志串联成一个链表，这个链表称为版本链

5）ReadView

        有了版本链，那么核心问题就是：需要判断版本链中，哪个版本是当前事务可见。这个当前事务可见的版本就是ReadView。

        读未提交级别：每次都读最新版本，即使其它事务还没提交，不需要控制

        RC 和RR：保证读取到的都是其它事务已经提交的记录。

        RC:每次读取数据前都生成一个ReadView

        RR：在第一次读取数据时生成一个ReadView

10.锁

        MVCC和加锁是解决并发事务带来的一致性问题的两种方式

 1）共享锁和排他锁

        S锁与S锁兼容；X锁与S锁不兼容；X锁与X锁不兼容

2）一致性读和锁定度

        MVCC进行的读取称为一致性读，读记录前加锁称为锁定度。

3）加锁语句

        加S锁语句：select ......lock in share mode

        加X锁语句：select......for update

4）锁结构

        insert语句一般情况不需要在内存中生成锁结构，单纯的靠隐式锁保护插入的记录；update和delete语句在执行时，在B+树中定位到待改动记录，并给该记录加锁。

5）意向共享锁和意向排他锁

        属于表级锁，仅仅为了在之后加表级锁时，可以快速的判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录。

6）行级锁

        a.read lock:只对记录本身加锁；

        b.gap lock：锁住记录前的间隙，防止别的事务向该间隙插入新记录

        c.next-key lock：a+b，既锁定当前记录，也锁定当前记录前的间隙

        d.隐式锁：依靠记录的trx_id属性保护不被别的事务改动

7）同一个锁结构条件

        在同一个事务中进行加锁操作

        被加锁的记录在同一个页面中

        加锁的类型一样的

        等待的状态一样

8）死锁

        不同事务由于相互持有对方需要的锁而导致事务都无法继续执行的情况称为死锁，死锁发生时，innodb会选择一个较小的事务进行回滚。可以通过查看死锁日志来分析死锁发生的过程。

        show engine innodb status ，然后找到“LATEST DETECTED DEADLOCK”一节内容，分析死锁发生过程

9）更详细内容请参考：mysql什么情况下会触发表锁

11.集群

参考文档

        集群中主节点和从节点数据同步方式如上图所示：master数据发生变化时，将其sql写入bin log中，然后slave从master中读取bin log 存入本地的 relay log，本地从relay log读取sql ，写入到数据库中。

搭建集群步骤

1.关闭主从机器的防火墙

systemctl stop iptables（需要安装iptables服务）
systemctl stop firewalld（默认）
systemctl disable firewalld.service（设置开启不启动）

2.master服务器配置修改

1）修改my.cnf文件（mysqlid段下添加）

#启用二进制日志
log-bin=mysql-bin
#服务器唯一ID，一般取IP最后一段
server-id=133
#指定复制的数据库(可选)
binlog-do-db=kkb2
binlog-ignore-db=kkb
#指定不复制的数据库(可选,，mysql5.7)
replicate-ignore-db=kkb
#指定忽略的表（可选，mysql5.7）
replicate-ignore-table = db.table1

2）重启mysql服务

systemctl restart mysqld

3）主机给从机授备份权限

GRANT REPLICATION SLAVE ON *.* TO 'root'@'%' identified by 'root';
GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' identified by 'root';

4）刷新权限

FLUSH PRIVILEGES;

5）查询master状态

show master status;

3.slave服务器配置修改

1）修改my.cnf文件

[mysqld]
server-id=135

2）重启并登录到MySQL进行配置从服务器

以前是启动状态
show slave status \G；
先关闭 stop slave
同步初始化 master_log_file 、 master_log_pos 以主机状态为主 show master status

mysql>change master to
master_host='192.168.56.101',
master_port=3306,
master_user='root',
master_password='root',
master_log_file='mysql-bin.000059',
master_log_pos=394;

3）启动从服务器复制功能

mysql>start slave;

4）检查从服务器复制功能状态

mysql> show slave status \G；
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES

读写分离

        MySQL的主从复制，只会保证主机对外提供服务，而从机是不对外提供服务的，只是在后台为主机进行备份。读写分离后，主负责写和部分读，从负责读，高性能高可用的数据库集群。

MySQL-Proxy安装

1）下载

wget https://downloads.mysql.com/archives/get/file/mysql-proxy-0.8.5-linux-el6-
x86-64bit.tar.gz

2）解压

tar -xf mysql-proxy-0.8.5-linux-el6-x86-64bit.tar.gz -C /kkb

MySQL-Proxy配置

1）创建mysql-proxy.cnf文件

[mysql-proxy]
user=root
admin-username=root
admin-password=root
proxy-address=192.168.10.137:4040
proxy-backend-addresses=192.168.10.135:3306
proxy-read-only-backend-addresses=192.168.10.136:3306
proxy-lua-script=/root/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
log-file=/root/mysql-proxy/logs/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true

2）修改mysql-proxy.cnf文件的权限

chmod 660 mysql-proxy.cnf #可读写

3）修改rw-splitting.lua脚本

-- connection pool
if not proxy.global.config.rwsplit then
proxy.global.config.rwsplit = {
min_idle_connections = 1,
max_idle_connections = 2,

is_debug = false
}
end

4）启动命令（测试）

./mysql-proxy --defaults-file=mysql-proxy.cnf配置文件的地址

5）在其他客户端，通过mysql命令去连接MySQL Proxy机器

mysql -uroot -proot -h192.168.56.102 -P4040;
注： 关闭防火墙

12.分库分表

数据切分（sharding）方案

数据的切分（Sharding）根据其切分规则的类型，可以分为两种切分模式：

垂直切分：

        分库：按照业务模块进行切分，将不同模块的表切分到不同的数据库中

        分表：大表拆小表

水平切分：将一张大表按照一定的切分规则，按照行切分成不同的表或者切分到不同的库中
范围式拆分，好处：数据迁移是部分迁移，扩展性好。坏处：热点数据分布不均，压力不能负载

水平切分规则

按照ID取模：对ID进行取模，余数决定该行数据切分到哪个表或者库中
按照日期：按照年月日，将数据切分到不同的表或者库中
按照范围：可以对某一列按照范围进行切分，不同的范围切分到不同的表或者数据库中。

切分原则

第一原则：能不切分尽量不要切分。

第二原则：如果要切分一定要选择合适的切分规则，提前规划好。 （向上取整）
第三原则：数据切分尽量通过数据冗余或表分组（Table Group）来降低跨库 Join 的可能。

测试Demo

DROP TABLE IF EXISTS `t_order_0`;
CREATE TABLE `t_order_0` (
`oid` int(11) NOT NULL,
`uid` int(11) DEFAULT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`oid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

1、pom.xml

<dependency>
<groupId>io.shardingsphere</groupId>
<artifactId>sharding-jdbc-core</artifactId>
<version>3.0.0</version>
</dependency>
<!-- mysql 数据库驱动. -->
<dependency>
<groupId>mysql</groupId>
<artifactId>mysql-connector-java</artifactId>
<version>5.1.47</version>
</dependency>
<!-- 数据源 -->
<dependency>
<groupId>com.alibaba</groupId>
<artifactId>druid</artifactId>
<version>1.0.26</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-api</artifactId>
<version>1.7.6</version>
</dependency>
<dependency>
<groupId>org.slf4j</groupId>
<artifactId>slf4j-log4j12</artifactId>
<version>1.7.6</version>
</dependency>

2、sharding.java

Map<String, DataSource> map=new HashMap<>();
map.put("kkb_ds_0",
createDataSource("root","root","jdbc:mysql://127.0.0.1:3307/kkb_ds_0"));
map.put("kkb_ds_1",
createDataSource("root","root","jdbc:mysql://127.0.0.1:3308/kkb_ds_1"));
ShardingRuleConfiguration config=new ShardingRuleConfiguration();
// 配置Order表规则
TableRuleConfiguration orderTableRuleConfig = new
TableRuleConfiguration();
orderTableRuleConfig.setLogicTable("t_order");//设置逻辑表.
orderTableRuleConfig.setActualDataNodes("kkb_ds_${0..1}.t_order_${0..1}");//设置
实际数据节点.
orderTableRuleConfig.setKeyGeneratorColumnName("oid");//设置主键列名称.
// 配置Order表规则：配置分库 + 分表策略(这个也可以在ShardingRuleConfiguration进
行统一设置)
orderTableRuleConfig.setDatabaseShardingStrategyConfig(new
InlineShardingStrategyConfiguration("uid", "kkb_ds_${uid % 2}"));
orderTableRuleConfig.setTableShardingStrategyConfig(new
InlineShardingStrategyConfiguration("oid", "t_order_${oid % 2}"));
config.getTableRuleConfigs().add(orderTableRuleConfig);
try {
DataSource ds=ShardingDataSourceFactory.createDataSource(map,
config, new HashMap(), new Properties());
for(int i=1;i<=10;i++) {
String sql="insert into t_order(uid,name) values(?,?)";
execute(ds,sql,i,i+"aaa");
}
System.out.println("数据插入完成。。。");
} catch (SQLException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}

项目案例

Springboot2+mybatisplus+shardingJDBC3 实现数据分片、读写分离、广播表、主键自增、绑定表

user address E-R

code 全局表

1.表结构

--用户表
CREATE TABLE `user_0` (
`uid` int(11) NOT NULL DEFAULT '0',
`name` varchar(255) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
PRIMARY KEY (`uid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--地址表
CREATE TABLE `address_0` (
`aid` int(11) NOT NULL,
`name` varchar(255) DEFAULT NULL,
`uid` int(11) DEFAULT NULL,
PRIMARY KEY (`aid`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
--字典表
CREATE TABLE `code` (
`id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.分库分表

# 数据源 ds0,ds1
sharding.jdbc.datasource.names=ds0,ds1
# 第一个数据库
sharding.jdbc.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.jdbc-url=jdbc:mysql://192.168.56.101:3306/ds0?
characterEncoding=utf-8
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=root

# 第二个数据库
sharding.jdbc.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.jdbc-url=jdbc:mysql://192.168.56.102:3306/ds1?
characterEncoding=utf-8
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=root
# 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
# 分库策略 user表和address表都用uid作为分库键
sharding.jdbc.config.sharding.default-database-strategy.inline.shardingcolumn=
uid
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithmexpression=
ds$->{uid % 2}

# 分表策略 其中user为逻辑表 分表主要取决于age行
sharding.jdbc.config.sharding.tables.user.actual-data-nodes=ds$->{0..1}.user_$->
{0..1}
sharding.jdbc.config.sharding.tables.user.table-strategy.inline.shardingcolumn=
age
#分表策略
sharding.jdbc.config.sharding.tables.address.actual-data-nodes=ds$->
{0..1}.address_$->{0..1}
sharding.jdbc.config.sharding.tables.address.table-strategy.inline.shardingcolumn=
aid
# 分片算法表达式
sharding.jdbc.config.sharding.tables.user.table-strategy.inline.algorithmexpression=
user_$->{age % 2}
sharding.jdbc.config.sharding.tables.address.table-strategy.inline.algorithmexpression=
address_$->{aid % 2}

#广播表配置
sharding.jdbc.config.sharding.broadcast-tables=code
# 主键 SNOWFLAKE 18位数 如果是分布式还要进行一个设置 防止主键重复
sharding.jdbc.config.sharding.tables.code.key-generator-column-name=id
#绑定表
sharding.jdbc.config.sharding.binding-tables=user,address
# 打印执行的数据库以及语句
sharding.jdbc.config.sharding.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

3.分库分表+读写分离

ds0和ds0-slave先做好主从复制
ds0和ds1不做主从复制

# 数据源 ds0,ds1,ds0-slave ds0,ds1做分库分表 ds0,ds0-slave 做主从读写分离
sharding.jdbc.datasource.names=ds0,ds1,ds0-slave

# 第一个数据库
sharding.jdbc.datasource.ds0.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds0.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0.jdbc-url=jdbc:mysql://192.168.56.101:3306/ds0?
characterEncoding=utf-8
sharding.jdbc.datasource.ds0.username=root
sharding.jdbc.datasource.ds0.password=root
# 第二个数据库
sharding.jdbc.datasource.ds1.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds1.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds1.jdbc-url=jdbc:mysql://192.168.56.102:3306/ds1?
characterEncoding=utf-8
sharding.jdbc.datasource.ds1.username=root
sharding.jdbc.datasource.ds1.password=root
# 第一个数据库的从库
sharding.jdbc.datasource.ds0-slave.type=com.zaxxer.hikari.HikariDataSource
sharding.jdbc.datasource.ds0-slave.driver-class-name=com.mysql.jdbc.Driver
sharding.jdbc.datasource.ds0-slave.jdbcurl=
jdbc:mysql://192.168.56.103:3306/ds0?characterEncoding=utf-8
sharding.jdbc.datasource.ds0-slave.username=root
sharding.jdbc.datasource.ds0-slave.password=root

# 水平拆分的数据库（表） 配置分库 + 分表策略 行表达式分片策略
# 分库策略 user表和address表都用uid作为分库键
sharding.jdbc.config.sharding.default-database-strategy.inline.shardingcolumn=
uid
sharding.jdbc.config.sharding.default-database-strategy.inline.algorithmexpression=
ds$->{uid % 2}
# 分表策略 其中user为逻辑表 分表主要取决于age行
sharding.jdbc.config.sharding.tables.user.actual-data-nodes=ds$->{0..1}.user_$->
{0..1}
sharding.jdbc.config.sharding.tables.user.table-strategy.inline.shardingcolumn=
age
#分表策略
sharding.jdbc.config.sharding.tables.address.actual-data-nodes=ds$->
{0..1}.address_$->{0..1}
sharding.jdbc.config.sharding.tables.address.table-strategy.inline.shardingcolumn=
aid

# 分片算法表达式
sharding.jdbc.config.sharding.tables.user.table-strategy.inline.algorithmexpression=
user_$->{age % 2}
sharding.jdbc.config.sharding.tables.address.table-strategy.inline.algorithmexpression=
address_$->{aid % 2}
#广播表配置
sharding.jdbc.config.sharding.broadcast-tables=code
# 主键 SNOWFLAKE 18位数 如果是分布式还要进行一个设置 防止主键重复
sharding.jdbc.config.sharding.tables.code.key-generator-column-name=id
#绑定表
sharding.jdbc.config.sharding.binding-tables=user,address
#主库
sharding.jdbc.config.sharding.master-slave-rules.ds0.master-data-source-name=ds0
#从库
sharding.jdbc.config.sharding.master-slave-rules.ds0.slave-data-sourcenames=
ds0-slave
# 打印执行的数据库以及语句
sharding.jdbc.config.sharding.props.sql.show=true
spring.main.allow-bean-definition-overriding=true

sharding jdbc实现分库分表：参考文档

mycat实现分库分表：参考文档1，参考文档2

参考文档