事务并发问题
脏读：一个事务读取到了另一个事务修改但未提交的数据
不可重复读：同一个事务中先后两次都去相同记录但是数据不同
幻读：同一个事务中先后读取数据的行数不同
解决事务并发问题
使用事务隔离级别
读未提交：都不能解决
读已提交：解决脏读
可重复读：解决脏读和不可重复读（mysql默认使用的隔离级别）
串行化：都已解决
show engines;命令可查看当前数据库支持的存储引擎
Innodb特点：支持事务、支持行级锁提高并发、支持外键保证数据完整性和正确性
5.5版本后默认存储引擎
每张表都有一个idb文件（包括表结构、数据、索引）
存储分为：表空间 -> 段 -> 区 -> 页 -> 行
一个区固定为1M，一个页固定为16K，每行中包括事务id、指针和每个字段
MyISAM特点：不支持事务、不支持外键、支持表锁不支持行锁、访问速度快
5.5版本之前默认存储引擎
每张表都有三个文件，sdi是表结构信息、MYD是表数据、MYI是索引
Memory特点：表数据都存储在内存中，所以只能将这些表作为临时表或者内存；hash索引（默认）
每张表只有一个文件sdi表结构
索引是帮助mysql高效获取数据的有序的数据结构。
数据库系统除了维护数据外还要维护这些数据结构到数据的引用。
索引结构
其在存储引擎层实现，不同的存储引擎有不同的结构
（1）B+Tree索引：最常见的索引类型，大部分引擎都支持
1）二叉树：每个节点最多有两个子节点且左子节点的值比父节点小，右子节点的值比父节点大；缺点：若顺序插入则会形成单链条，若数据量大则层次太深查找较慢
2）红黑树：类似二叉树，节点是一层黑一层红，根节点永远是黑色，但其是自平衡的，但是数据量大时则层次太深查找较慢
3）B-Tree：多路平衡查找树，每个节点可以存储多个数据，且每个节点可以存在多个子节点，同时父节点有指向子节点的引用（若子节点个数为5即5阶，则每个节点数据个数为4，指针为5）
裂变方式：中间元素向上裂变（所有节点均存储真正的数据和其子节点的引用（如果有子节点））
4）B+Tree：是B-Tree的变种，与B-Tree不同的是所有的数据都会出现在叶子节点中且叶子节点形成了单向链表，而非叶子节点只是用于索引数据的作用不存储真实数据
在mysql中对原有的B+Tree进行了优化，在原基础上增加一个指向相邻叶子节点的链表指针（双向），就形成了带有顺序指针的B+Tree提高了区间访问的性能。
（2）Hash索引：底层数据结构是通过哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到索引字段（hash）对用的槽位上然后存储在哈希表中；若多个映射到相同槽位就产生了hash冲突，可通过链表解决
（3）R-Tree空间索引：空间索引是myIsam引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
（4）Full-text全文索引：是一种通过建立倒排索引，快速匹配文档的方式。例如es
问：Innodb中使用B+Tree的原因？
（1）相对于二叉树，B+Tree层级更少，搜索效率高；（经计算存储两千多万条数据时深度为3）
（2）相对于Hash索引，B+Tree支持范围匹配及排序操作；
（3）相对于B-Tree，无论是叶子节点还是非叶子节点都会保存数据，这样导致一页中的键值减少，指针跟着减少，在同样保存大量数据时，只能增加树的高度这样性能就会降低。
SQL执行频率
可查看全局的sql执行频率，包括增删改查的次数
show global status like ‘Com_______’;-- 一共七个_
慢查询日志
记录了所有执行时间超过阈值（long_query_time，默认10秒）的所有sql语句的日志
默认关闭(查看开关是否开启：show variables like ‘slow_query_log’😉，可在配置文件my.cnf中配置开启：
#开启慢查询日志
slow_query_log=1
#设置慢查询sql执行时间，默认10秒
long_query_time=2
默认生成的慢查询日志位置：/var/lib/mysql/localhost_slow.log
profiles
查看sql语句执行中各个阶段的耗时情况
默认关闭
#查看每一条sql的耗时基本情况
show profiles;// 其中包含了query_id
#查看指定query_id的sql语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query_id的sql语句各个阶段的cpu情况
show profile cpu for query query_id;
explain执行计划
id：表示查询中select字句或者操作表的执行顺序（id相同，从上至下执行；id不同，值越大越先执行）
select_type（参考）：查询类型，常见的有SIMPLE(简单表)、PRIMARY(主查询)、UNION(联合查询第二或后面的查询)、SUBQUERY(select/where之后包含的子查询)等
type：连接类型，性能由好到差为NULL、system、const、eq_ref、ref、range、index、all
possible_key：可能用到的索引，一个或多个
key：实际用到的索引，如果为NULL则表示没有用到索引
Key_len：索引中使用的字节数，该值越小越好
rows（参考）：必须要执行查询的行数，是一个预估值
filtered：返回结果的行数占需要读取行数的百分比，该值越大越好
Extra：额外信息
索引失效情况
（1）联合索引
1）未遵循最左前缀法则（最左列必须存在，且不能跳过索引中的列，否则索引将部分失效）
2）范围><查询时，范围查询右侧的列索引失效，可通过>= <=来避免索引失效
（2）在索引列上进行运算会失效
（3）字符串字段不加引号会失效
（4）模糊查询时，头部模糊匹配会失效,如like ‘%工程’ 即%在前
（5）使用or分隔开的条件时，如果or前的条件中有索引而后面没有索引会失效，解决需要对另一个字段创建索引
（6）数据分布影响，如果mysql评估使用索引相比全表扫描更慢时则不是用索引
（7）
SQL提示
是优化数据库的一个重要手段，即就是在sql语句中加入一些人为的提示来达到优化操作的目的。
（1）use index：建议使用指定索引（至于是否使用最红由mysql决定）
explain select * form tb_user use index(idx_user_pro) where profession = ‘软件’;
（2）ignore index：不要使用指定索引
explain select * form tb_user ignore index(idx_user_pro) where profession = ‘软件’;
（3）force index：强制使用指定索引
explain select * form tb_user force index(idx_user_pro) where profession = ‘软件’;
覆盖索引
查询使用到了索引，并且需要返回的列在该索引中已经全部可以找到；所以尽量使用覆盖索引，避免select *和回表操作
Extra中提示解读：
（1）using index condition：查找使用到了索引，但是需要回表查询数据
（2）using where；using index：查找使用到了索引，同时需要的数据都在索引列中能找到，不需回表操作，效率高。
前缀索引
当字段类型为字符串（varchar或text等）时，有时需要索引很长的字符串这会让索引变得很大，查询时浪费大量磁盘io，影响效率；此时可以只将字符串的一部分前缀建立索引，这样可以
大大节约索引空间，从而提高索引效率。
创建语法：create index 索引名 on 表名(字段名(前缀长度))
前缀长度取值：可根据索引的选择性来决定，即不重复的索引值和数据表记录总数的比值，该值越大越好，例：
select count(distinct subdtring(email,1,5)) / count(*) from tb_user;
create index idnex_user_email on tb_user(email(5));
索引设计原则
（1）针对数据量较大（百万以上）且查询比较频繁的表建立索引
（2）针对常作为查询条件where、排序order by、分组group by操作的字段建立索引
（3）尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高使用索引的效率越高
（4）如果字符串类型的字段且字段的长度较长，可针对字段的特点建立前缀索引
（5）尽量使用联合索引，减少单列索引，查询时联合索引很多时候可覆盖索引，节省存储空间避免回表，提高查询效率
（6）要控制索引的数量，索引越多维护索引的代价也就越大
（7）如果索引值不能存储null值需要再创建表时使用not null约束它，这样优化器知道每列是否包含null值时，可以更好的确定哪个索引最有效地用于查询
SQL优化
（1）插入优化
1）批量插入建议单词数量控制在1000条内；
2）可手动提交事务；
3）主键要顺序插入，否则可能导致页分裂，这是比较耗费性能的；
4）大批量数据插入可使用load指定进行插入：
#客户端连接服务端时加上–local-infile
mysql --local-infile -u root -p
#设置全局参数local_infile=1，开启从本地加载文件导入数据的开关
set global local_infile=1;
#执行load指令加载数据到库中
load data local infile ‘/root/sql.log’ into table ‘tb_user’ fields terminated by ‘,’ lines terminated by ‘\n’;
（2）主键优化
1）插入数据时，主键要顺序插入，否则可能导致页分裂，这是比较耗费性能的
2）尽量降低主键的长度
3）尽量不要使用UUID做主键或其他自然主键如身份证，这样会导致页分裂
4）尽量避免对主键的修改，否则需要重新对索引结构进行调整
（3）order by优化
对字段建立索引时默认是升序排列的，如果在order by查询时想要降序排序可以在重新建立索引并指定每个字段是要如何排序(a asc,b desc)
可通过explain查看排序sql的执行计划是否使用到了索引，且查看Extra中的信息可查看是使用Using index（效率较高）还是使用Using filesort（效率较低，全表扫描）
注意：根据排序字段建立索引，当是多字段排序时，也要遵循最左前缀法则（按照索引顺序使用，否则索引使用不完全，效率低）
（4）group by优化
也要遵循最左前缀法则（按照索引顺序使用，否则索引使用不完全，效率低）
（5）limit优化
官方建议：使用覆盖索引和子查询实现大数据量的分页查询，如：
一张表有10000000条数据，当查询limit 9000000,10会用时将近20s，而改为覆盖索引+子查询用时将近12s
---->优化前：select * from t_user limit 9000000,10; 用时将近20s
---->优化后：select s.* from t_user s,(select id from t_user limit 9000000,10) a wher s.id = a.id; 用时将近12s
（6）count()优化
统计数量的聚合函数，count参数不为NULL时计数加1
思路：自己计数，相当于在缓存中定义计数器，插入数据加1删除数据减1
---->count几种方式：
1）count()：InnoDB引擎并不会把全部字段取出来，而是专门做了优化，不取值，服务层直接按行进行累加。
2）count(主键)：InnoDB 引擎会遍历整张表，把每一行的 主键id 值都取出来，返回给服务层。服务层拿到主键后，直接按行进行累加(主键不可能为nul).
3）count(字段)：没有not null约束:InnoDB引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，服务层判断是否为nul，不为nul，计数累加。
有not null 约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来，返回给服务层，直接按行进行累加。
4）count(1)：InnoDB 引擎遍历整张表，但不取值。服务层对于返回的每一行，放一个数字“1”进去，直接按行进行累加。
这几种效率排序：count() ≈ count(1) > count(主键) > count(字段) 所以尽量使用count(*)
（7）update优化
需要注意：在更新时最好使用索引字作为条件进行更新，这样使用到的是行锁否则使用到的是表锁。
即：InndDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。。。
锁
是计算机协调多个进程或线程并发访问某一资源的机制。。
分类：
（1）按照锁的粒度分
1）全局锁：锁定数据库中所有表，即对整个数据库实例加锁
使用场景：全库的备份（使用mysqldump工具）mysqldump -h 数据库ip -u用户名 -p密码 库名 > 备份文件.sql
加锁命令：flush tables with read lock
解锁命令：unlock tables
2）表级锁：每次操作锁住整张表
a、表锁
①表共享读锁/读锁（read lock）
②表独占写锁/写锁（write lock）
加锁：lock tables 表名。。。 read/write
解锁：unlock tables
b、元数据锁（meta data lock）
MDL加锁过程是系统自动控制的，无需显示使用，在访问一张表时会自动加上。其主要作用是维护表元数据（表结构）的数据一致性，在表上有活动事务（事务未提交）的时候，
不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证读写的正确性。
在5.5版本中引入，当对表进行增删改查时加MDL读锁（共享锁）；当对表结构进行变更操作时，加MDL写锁（排他锁）
c、意向锁
为了避免DML在执行时行锁与表锁的冲突，在InnoDb中引入了意向锁，使得表锁不用检查每行数据是否加锁，使用意向锁来减少表锁的检查。
分为意向共享锁（IS与表锁的读锁兼容、写锁互斥）和意向排他锁（IX与表锁的读锁、写锁均互斥）
注意：在无意向锁之前，当a客户端开启事务执行update操作时，会给当前行数据加行锁，此时b客户端过来加表锁，那么b需要逐条遍历每一行数据来检查是否有行锁存在，这样效率低下；
而在有了意向锁后在a客户端开启事务执行update操作时加行锁同时会标记为意向锁，这样当b客户端过来加表锁时只需要判断是否有意向锁且是否可兼容即可，大大提高效率
3）行级锁：每次操作锁住对应的行数据
注意：行锁是通过对索引上的索引项加锁来实现的而不是对记录加锁
a、行锁：锁定单个行记录的锁，防止其他事务对此行进行更改操作
共享锁S和排他锁X
注：InnoDB的行锁是针对于索引加的锁，不通过索引字段作为条件检索数据，那么InnoDB将对表中的所有记录加锁，此时就会升级为表锁。
b、间隙锁：锁定索引记录间隙（不含该记录）确保索引记录间隙不变，防止其他事务在这个间隙进行插入，产生幻读
c、临键锁：行锁和间隙锁的组合，同时锁住行数据和数据前面的间隙。
InnoDB引擎
（1）逻辑存储结构：
表空间 -> ibd文件，一个mysql实例可以对应多个表空间，用于存储记录、索引等数据
段 -> 分为数据段（B+树的叶子节点）、索引段（B+树的非叶子节点）、回滚段
区 -> 表空间的单元结构，每个区大小为1M
页 -> 存储引擎磁盘管理的最小单元，每个页默认大小为16KB，为了保证页的连续性，存储引擎每次从磁盘申请4~5个区
行 -> 存储事务id、回滚指针（当记录改动时，会将旧的版本写入到undo日志中，通过该指针可以找到修改前的信息）、字段等数据
（2）架构：
1）内存架构
Buffer pool：缓冲池是内存中的一个区域，缓存真是数据，当进行增删改查时先操作缓冲池中数据后再以一定频率刷到磁盘，减少io操作加快处理速度
缓冲池是以页为单位的，底层采用链表数据结构管理Page。
Change Buffer：更改缓冲区，针对非唯一二级索引页，在执行DML语句时，如果这些数据page没有在buffer pool中不会直接操作磁盘，而会将数据变更存在该区中，在未来数据被读取时在
将数据恢复到buffer pool中再讲合并后的数据刷新到磁盘中。
Adaptive Hash Index：自适应hash索引，用于优化对Buffer pool数据的查询。InnoDB会监控各个表的索引页的查询，若观察到hash索引可提升速度，则建立hash索引，无需人工干预
Log Buffer：日志缓冲区，用来保存要写入到磁盘中的log日志数据（redo log、undo log）默认大小16M。日志缓冲区的日志会定期刷到磁盘；如果需要更新、插入或删除许多行的事务，
增加日志缓冲区的大小可以节省磁盘io。
2）磁盘结构
System Tablespace：系统表空间，是更改缓冲区的存储区域
File-Per-Table Tablespaces：独立表空间，每个表的文件表空间，包含单个InnoDB表的数据和索引
General Tablespaces：通用表空间，若没有创建是没有该表空间的，可通过CREATE TABLESPACES 语法创建通用表空间，在创建表时可指定该表空间
Undo Tablespaces：撤销表空间，mysql实例在初始化时会自动创建两个默认的undo表空间（初始大小16M），用于存储undo log日志
Temporary Tablespaces：临时表空间，存储用户创建的临时表数据
Doublewrite Buffer Files：双写缓冲区，InnoDB引擎将数据页从Buffer Pool刷新到磁盘前现将数据页写入该缓冲区文件中，便于系统异常时恢复数据，文件为.dblwr
Redo log：重做日志，用来实现事务的持久性。分为两部分：重做日志缓冲区和重做日志文件，前者在内存后者在磁盘；当事务提交后会把所有修改的信息存到该日志中，用于刷新脏页
到磁盘时若发生错误进行数据恢复。
（3）后台线程
1）Master Thread：核心后台线程，负责调度其他线程。还负责将缓冲池中的数据异步刷新到磁盘中
2）IO Thread：使用AIO（异步非阻塞），主要负责这些io（包括4读、4写、1日志和1缓冲区刷新到磁盘线程）请求的回调
3）Purge Thread：用于回收事务已经提交了的undo log
4）Page Cleaner Thread：协助Master Thread刷新脏页到磁盘的线程
（4）事务原理
事务是一组操作的集合，它是一个不可分割的工作单位，该组操作要么全部成功要么全部失败。
***********undo log保证了事务的原子性
***********锁和MVCC保证了事务的隔离性
***********redo log和undo log共同保证了事务的一致性
***********redo log保证了事务的持久性
1）redo log保证持久性
重做日志记录的是事务提交时数据页的物理修改（物理日志）；当事务提交之后会把所有修改信息都存到该日志文件中，用于刷新脏页（缓冲区修改的数据页）到磁盘
（这个过程不是在事务提交就立马执行的，是以一定频率进行刷新到磁盘的，参数控制）发生错误时，进行数据恢复使用。
2）undo log保证原子性
回滚日志，用于记录数据修改前的信息（是逻辑日志，如执行delete操作会记录一条insert日志，即记录相反操作语句）
作用：提供慧回滚和MVCC（多版本并发控制）
undo log销毁：undo log在事务执行时产生，事务提交时并不会立即删除，因为这些日志可能还用于MVCC
undo log存储：其采用段的方式进行管理和记录，存放在回滚段中，内部包含1024个回滚段。
3）MVCC多版本并发控制，作用：在快照读的时候MVCC提取的数据是哪个版本的记录
基本概念：
①当前读：读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。如lock in share mode共享锁、for update、delete、update、insert排他锁操作
②快照读：简单的select（不加锁）就是快照读，读取的是记录数据的可见版本，有可能是历史数据，不加锁是非阻塞读。
③MVCC
全称Mutil-Version Concurrency Control，其维护了一个数据的多个版本，使得读写操作没有冲突，快照读为mysql实现MVCC提供了一个非阻塞读功能。
MVCC具体实现还需要依赖于数据库记录中的三个隐藏字段、undo log日志和readView。
实现原理：记录中的三个隐藏字段+undo log日志+readView
①记录中的隐藏字段（每行记录都有）：
DB_TRX_ID：最近修改事务ID
DB_ROLL_PTR：回滚指针，指向这条记录的上一个版本，用于配合undo log指向上一个版本
DB_ROW_ID：隐藏主键，若表结构没有指定主键，将会生成该隐藏字段
②undo log回滚日志
在insert、update、delete时产生的便于数据回滚的日志；
当insert时产生的undo log日志只在回滚时需要，所以事务提交后可被立即删除；
而update、delete时产生的undo log不仅在回滚时需要，在快照读时也需要所以不会被立即删除。
③undo log版本链
不同事务或者相同事务对同一条记录进行修改，会导致记录的undo log生成一条记录版本链表，链表的头部是最新的旧记录，链表的尾部是最早的旧记录。
④readView读视图
是快照读sql执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务id（未提交的）。包含如下四个核心字段：
m_ids：当前活跃的事务id集合
min_trx_id：最小活跃事务id
max_trx_id：预分配事务id，当前最大事务id+1（因为事务id是自增的）
creator_trx_id：ReadView创建者的事务id
readView生成时机：
在READ COMMITTED隔离级别下：在事务中每一次执行快照读时生成readView
在REPEATABLE READ隔离级别下：在事务中第一次执行快照读时生成readView，后续会重复使用该readView
日志
1、错误日志
记录了mysql启动、停止和运行过程中发生任何严重错误的相关信息，默认开启，默认存放目录是/var/log/mysqld.log；查看日志位置：
show variables ‘%log_error%’;
2、二进制日志binlpg
binlog记录了所有DDL和DML语句，但不包括数据查询（select、show）语句。
作用：灾难时的数据恢复、mysql的主从复制；默认开启，查看日志位置：
show variables ‘%log_bin%’;
日志格式：
STATEMENT：基于sql语句的日志记录，记录的是sql语句，对数据进行的修改的sql都会记录在日志文件中
ROW：基于行的日志记录，记录的是每一行的数据变更(默认使用，记录每行记录变更前后的数据内容)
MIXED：混合STATEMENT和ROW两种格式，默认采用STATEMENT在某些特殊情况下会自动切换为ROW进行记录
日志格式查看：show variables ‘%binlog_format%’;
mysqlbinlog -v binlog.000002 -v:将行事件重构为sql语句，此举在日志格式为row时指定方便查看
日志删除：
reset master：删除全部binlog日志，执行后日志编号将从000001开始
purge master logs to 'binlog.'：删除编号之前的所有日志
purge master logs before ‘yyyy-mm-dd hh24:mi:ss’：删除yyyy-mm-dd hh24:mi:ss 之前的所有日志
在不主动删除的情况下，mysql也会定期删除，默认保存30天，可通过查看过期参数查看设置的过期时间：
show variables ‘%binlog_expire_logs_seconds%’;单位秒，可通过在配置文件中设置binlog_expire_logs_seconds参数的值修改保存的时间
3、查询日志
记录了客户端所有操作语句（全部），默认情况下，查询日志是关闭的，若需要开启可设置以下配置：
show variables ‘%general%’;
在mysql配置文件中添加配置开启查询日志：
general_log=1
general_log_file=/日志路径/mysql_query.log
4、慢查询日志
记录了所有执行时间超过参数long_query_time值（默认10s）且扫描记录数不小于min_examined_row_limit值的所有sql语句，默认关闭，配置文件中开启：
slow_query_log=1
long_query_time=2
注：默认情况下不会记录管理语句，也不会记录不使用索引进行查找的查询。可通过log_slow_admin_statements和log_queries_not_using_indexes配置修改：
#记录执行较慢的管理语句
log_slow_admin_statements=1
#记录执行较慢的未使用索引的局域
log_queries_not_using_indexes=1
5、回滚日志
undo log用于记录数据修改前的信息（是逻辑日志，如执行delete操作会记录一条insert日志，即记录相反操作语句）
6、重做日志
redo log记录的是事务提交时数据页的物理修改（物理日志，即字段修改后的值）当事务提交之后会把所有修改信息都存到该日志文件中，用于刷新脏页（缓冲区修改的数据页）到磁盘
（这个过程不是在事务提交就立马执行的，是以一定频率进行刷新到磁盘的，参数控制）发生错误时，进行数据恢复使用。
主从复制
1、概述
是指将主数据库的DDL和DML操作通过二进制日志传到从库服务器中，然后再从库上对这些日志重新执行（即重做），从而使从库和主库的数据保持同步。
mysql支持一台主库同时向多台从库进行复制，从库同时也可以作为其他从服务器的主库，实现链状复制。
2、优点
（1）主库出现问题，可以快速切换到从库继续提供服务
（2）实现读写分离，降低主库的访问压力
（3）可以在从库中执行备份，以避免备份期间影响主库服务
3、原理
（1）主库在事务提交时，会将数据变更语句记录在binlog日志
（2）从库的io线程读取主库的binlog日志文件将数据写入到从库的relay log中继日志
（3）从库的sql线程会读取relay log中继日志文件内容后重做文件中的事件以同步数据
4、搭建
master 192.168.22.109
slave 192.168.22.110
开放指定端口，如3306：
firewall-cmd --zone=public -add-port=3306/tcp-permanent
firewall-cmd -reload
关闭服务器的防火墙：
systemctl stop firewalld
关闭服务器防火墙的开机自启：
systemctl disable firewalld
（1）主库配置
1）修改配置文件/etc/my.cnf，添加以下内容
#mysql服务ID，保证在整个集群环境中唯一即可，取值范围1~2^32-1,默认为1
server-id=1
#是否只读，1代表只读，0代表读写
read-only=0
#忽略的数据库即不需要同步的数据库
#binlog-ignore-db=xxxx
#指定同步的数据库,如果有多个需要写多个binlog-do-db=xxxx
#binlog-do-db=xxxx
2）修改配置文件后重启mysql服务 systemctl restart mysql/mysqld
3）登录mysql，创建远程连接的账号并授予主从复制权限(在从库中连接主库同步数据使用)
创建sync用户并设置密码，该用户可以在任意主机连接mysql服务
CREATE USER ‘sync’@‘%’ IDENTIFIED WITH mysql_native_password BY ‘sync@123456’;
为sync用户分配主从复制权限
GRANT REPLICATION SLAVE ON . TO ‘sync’@‘%’;
4）查看二进制日志坐标
show master stauts;
file：表示从哪个文件开始推送日志文件
position：表示从哪个位置开始推送日志
（2）从库配置
1）修改配置文件/etc/my.cnf，添加以下内容
#mysql服务ID，保证在整个集群环境中唯一即可，取值范围1~2^32-1,默认为1
server-id=2
#是否只读，1代表只读，0代表读写
read-only=1
2）修改配置文件后重启mysql服务 systemctl restart mysql/mysqld
3）登录mysql，设置主库配置，我的版本是8.0.17
8.0.23后语法
CHANGE REPLICATION SOURCE TO SOURCE_HOST=‘主库ip’,SOURCE_USER=‘同步用户’,SOURCE_PASSWORD=‘同步用户密码’,SOURCE_LOG_FILE=‘同步文件’,SOURCE_LOG_POS=开始同步的位置;
8.0.23前语法
CHANGE MASTER TO MASTER_HOST=‘主库ip’,MASTER_USER=‘同步用户’,MASTER_PASSWORD=‘同步用户密码’,MASTER_LOG_FILE=‘同步文件’,MASTER_LOG_POS=开始同步的位置;
LOG_FILE文件和LOG_POS位置是在主库通过show master status指令查询所得
CHANGE MASTER TO MASTER_HOST=‘192.168.22.109’,MASTER_USER=‘sync’,MASTER_PASSWORD=‘sync@123456’,MASTER_LOG_FILE=‘binlog.000070’,MASTER_LOG_POS=650;
4）开启同步操作
8.0.22后指令 start replica;
8.0.22前指令 start slave;
5）查看从库状态
8.0.22后指令 show replica status;
8.0.22前指令 show slave status;
主要查看Slave_IO_Running和Slave_SQL_Running参数，都是YES时就没问题。
一般情况下Slave_IO_Running为NO是因为auto.cnf中的uuid或者server_id一样导致的，修改后重启数据库在登录先stop slave在start slave后再查状态基本都是YES
一般情况下Slave_SQL_Running为NO是因为在从库进行写操作导致，此时从库要stop slave后重新执行3）及之后的步骤即可，但此时主库要停止写操作
双主双从搭建
架构为两个一主一从，其中两个主互为对方的从机实现数据同步
1、master1配置/etc/my.cnf
#服务id
service-id=1
#在作为从库数据库时有写入操作也要更新二进制日志文件
log-slave-updates
2、master2配置/etc/my.cnf
#服务id
service-id=3
#在作为从库数据库时有写入操作也要更新二进制日志文件
log-slave-updates
3、两台主库创建账户并授予主从同步的权限
登录mysql：mysql -u root -p
1）创建sync用户并设置密码，该用户可以在任意主机连接mysql服务
CREATE USER 'sync'@'%' IDENTIFIED WITH mysql_native_password BY 'sync@123456';
2）为sync用户分配主从复制权限
GRANT REPLICATION SLAVE ON *.* TO 'sync'@'%';
3）通过指令查询两台主库的二进制日志坐标，用于从库配置时指定同步的二进制文件和偏移量offset
show master status;
4、slave1配置/etc/my.cnf
#服务id
service-id=2
5、slave2配置/etc/my.cnf
#服务id
service-id=4
6、两台从库配置关联的主库，登录mysql：mysql -u root -p
master1->slave1
master2->slave2
8.0.23后语法
CHANGE REPLICATION SOURCE TO SOURCE_HOST='主库ip',SOURCE_USER='同步用户',SOURCE_PASSWORD='同步用户密码',SOURCE_LOG_FILE='同步文件',SOURCE_LOG_POS=开始同步的位置;
8.0.23前语法
CHANGE MASTER TO MASTER_HOST='主库ip',MASTER_USER='同步用户',MASTER_PASSWORD='同步用户密码',MASTER_LOG_FILE='同步文件',MASTER_LOG_POS=开始同步的位置;
LOG_FILE文件和LOG_POS位置是在主库通过show master status指令查询所得
7、启动两台从库的主从复制，登录mysql：mysql -u root -p
8.0.22后指令
start replica;
8.0.22前指令
start slave;
8、查看从库状态（主要看IO和SQL现成状态都是YES即可，若有一个为NO就是有问题的）
8.0.22后指令
show replica status;
8.0.22前指令
show slave status\G;
读写分离
一主一从：
基于主从复制实现，读写库的配置可通过mycat实现（主要通过dataHost标签中的writeHost、readHost和balance属性（默认0：不开启读写分离）实现）
双主双从：
架构为两个一主一从，其中两个主互为对方的从机实现数据同步。一般情况下一个master用于处理所有写请求，另外三台机器负责读请求，当负责写的master宕机后
另一个master负责写这样来实现高可用。
9、两台主库互相复制
master1和master2互相复制，互为主从；即执行6、7、8步骤即可
分库分表
（1）介绍
1）其中心思想是将数据分散存储，使得单一数据库/表的数据量变小来缓解单一数据库的性能问题，从而达到提升数据库性能的目的。
2）拆分策略
垂直拆分->
①垂直分库:以表为依据，根据业务将不同业务表拆分到不同库中。
特点：
*每个库的表结构都不一样
*每个库的数据也不一样
*所有库的并集是全量数据
②垂直分表:以字段为依据，根据字段属性将不同字段拆分到不同表中。
特点：
*每个表的表结构都不一样
*每个表的数据也不一样，一般通过一列（主键/外键）关联
*所有表的并集和全量数据
水平拆分->
①水平分库：以字段为依据，按照一定策略将一个库的数据拆分到多个库中。
特点：
*每个库的表结构都一样
*每个库的数据都不一样
*所有库的并集是全量数据
②水平分表：以字段为依据，按照一定策略将一个表的数据拆分到多个表中。
特点：
*每个表的表结构都一样
*每个表的数据都不一样
*所有表的并集是全量数据
3）实现技术
①shardingJDBC：基于AOP原理，在应用程序中对本地执行的sql进行拦截、解析、改写、路由处理；需要自行编码配置实现，只支持java语言，性能较高。
②MyCat：数据库分库分表中间件，不用调整代码即可实现分库分表，支持多种语句但性能不及前者。
（2）MyCat
其是开源、活跃、基于java编写的mysql数据库中间件。可以像mysql一样来使用Mycat，对于开发人员来说感觉不到mycat的存在。
下载路径：http://mycat.org.cn/
1）分片规则共9种（适用于水平拆分）
即以某种规则将数据存储至那台数据库中。通过xml文件中的rule属性指定
①范围分片auto-sharding-long
根据指定的字段及其配置的范围与数据节点的对应情况来决定数据属于哪一个分片如id在0500的在分片1、id在5011000的在分片2。。。
②取模分片mod-long
根据指定的字段值与节点数量（数据库数量）进行求模运算，根据运算结果决定数据属于哪个分片
③一致性hash分片sharding-by-murmur
根据指定字段值的hash值来决定数据属于哪个分片
④枚举分片sharding-by-intfile
通过在配置文件中配置可能的枚举值，指定数据分布到不同数据节点上，本规则适用于按照省份、性别、状态拆分数据等业务
⑤应用指定分片sharding-by-substring
运行阶段由应用自主决定路由到哪个分片，直接根据字符子串（即该字段值的一部分但必须是数字）计算分片号
⑥固定分片hash算法分片
类似于取模分片，但其是二进制操作，分片字段必须是数字类型；如取id的二进制第十位与1111111111进行位&运算，得到的结果即是分片号
⑦字符串hash解析分片sharding-by-stringhash
截取字符串中的指定位置的子字符串，进行hash运算后再与1023进行位&运算，算出分片
⑧按（天）日期分片sharding-by-date
按照配置的天数为周期，将数据放到不同分片中。到达结束时间后会重复开始分片插入
注意：dataNode分片的数量必须和分片规则数量一致即如果有3个分片，10天为一个周期那么配置的开始结束时间应该共为30天
⑨自然月分片sharding-by-month
按照月份来分片，每个自然月为一个分片。到达结束时间后会重复开始分片插入
注意：dataNode分片的数量必须和分片规则数量一致即如果有3个分片，那么配置的开始结束时间应该为3个月
（3）MyCat管理及监控：Mycat-eye图形化界面管理
扫一扫
4万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
