Mysql

Mysql

MySQL的三种复制模式

数据库 koyo 发表了文章 0 个评论 3954 次浏览 2021-12-10 00:04 来自相关话题

MySQL支持的三种复制模式分别为: asynchronous 异步复制fully synchronous 全同步复制semi synchronous 半同步复制 ...查看全部

MySQL支持的三种复制模式分别为:


  1. asynchronous 异步复制
  2. fully synchronous 全同步复制
  3. semi synchronous 半同步复制

异步复制 (asynchronous replication)

原理:在异步复制中,master写数据到binlog且sync,slave request binlog后写入relay-log并flush disk
优点:复制的性能最好
缺点: master挂掉后,slave可能会丢失事务
代表:MySQL原生的复制

全同步复制 (fully synchronous replication)

原理:在全同步复制中,master写数据到binlog且sync,所有slave request binlog后写入relay-log并flush disk,并且回放完日志且commit
优点:数据不会丢失
缺点:会阻塞master session,性能太差,非常依赖网络
代表:MySQL Cluster

半同步复制 (semi synchronous replication)

1. 普通的半同步复制

原理: 在半同步复制中,master写数据到binlog且sync,且commit,然后一直等待ACK。当至少一个slave request bilog后写入到relay-log并flush disk,就返回ack(不需要回放完日志)
优点:会有数据丢失风险(低)
缺点:会阻塞master session,性能差,非常依赖网络,
代表:after commit, 原生的半同步



重点:由于master是在三段提交的最后commit阶段完成后才等待,所以master的其他session是可以看到这个提交事务的,所以这时候master上的数据和slave不一致,master crash后,slave数据丢失



2. 增强版的半同步复制(lossless replication)

原理: 在半同步复制中,master写数据到binlog且sync,然后一直等待ACK. 当至少一个slave request bilog后写入到relay-log并flush disk,就返回ack(不需要回放完日志)
优点:数据零丢失(前提是让其一直是lossless replication),性能好
缺点:会阻塞master session,非常依赖网络
代表:after sync, 原生的半同步



重点:由于master是在三段提交的第二阶段sync binlog完成后才等待, 所以master的其他session是看不见这个提交事务的,所以这时候master上的数据和slave一致,master crash后,slave没有丢失数据



重要参数:
































参数 评论 默认值 推荐值 是否动态
rpl_semi_sync_master_wait_for_slave_count 至少有N个slave接收到日志 1 1 dynamic
rpl_semi_sync_master_wait_point 等待的point AFTER_SYNC AFTER_SYNC dynamic
rpl_semi_sync_master_timeout 切换复制的timeout 1000(10s) 1000 (1s) dynamic
rpl_semi_sync_master_enabled 是否开启半同步 OFF ON dynamic
rpl_semi_sync_slave_enabled 是否开启半同步 OFF ON dynamic

如何开启lossless replication:


########semi sync replication settings########
plugin_dir=/usr/local/mysql/lib/plugin
plugin_load = "rpl_semi_sync_master=semisync_master.so;rpl_semi_sync_slave=semisync_slave.so"
loose_rpl_semi_sync_master_enabled = 1
loose_rpl_semi_sync_slave_enabled = 1
loose_rpl_semi_sync_master_timeout = 1000

实践是检验真理的唯一标准

如何检验上述after_syncafter_commit, 如何检验上述原理的正确性。


InnoDB commit: 三阶段提交过程
A阶段: wite prepare log 写入Xid
B阶段: write binlog
C阶段: write commit log

测试点:master上当一个事务Waiting for semi-sync ACK from slave的时候,后来的事务是在A,B,C哪个阶段卡住呢?


0,RC模式

1. semi-sync C阶段等待

假设设置time-out=100000s,当事务一提交了一个大事务,在write commit log(C阶段)时候等待,
那么第二个事务在敲commit命令的时候,是卡在哪个阶段呢?是卡在 wite prepare log(A阶段)?还是write binlog(B阶段)?还是write commit log(C阶段)

测试:semi-sync vs loss-less semi-sync

【semi-sync】 C阶段等待
0, 开启事务1,然后在slave上执行stop slave,制造timeout的情况,让其阻塞。(Waiting for semi-sync ACK from slave)
1,在开启一个事务2,事务2插入一条特殊记录(XXXXX)。 (Waiting for semi-sync ACK from slave)
2,在开启一个事务3。
2.1,测试案例:这个时候,kill -9 mysqld,造成人为的mysql crash
3,假设卡在A阶段,那么事务3,肯定是看不到事务1,2写入的记录(XXXXX),且重启mysql后,事务2不会提交。
4,假设卡在C阶段,那么事务3,肯定是可以看见事务1,2写入的记录(XXXXX)。

经过测试:
1,是卡在C阶段,也就是说事务3是可以看见事务1,事务2的。
2,MySQL crash重启后,事务1,事务2的dml都已经提交成功,说明不是卡在A阶段

【loss-less semi-sync】B阶段等待

0, 开启事务1,然后在slave上执行stop slave,制造timeout的情况,让其阻塞。(Waiting for semi-sync ACK from slave)
1,在开启一个事务2,事务2插入一条特殊记录(XXXXX)。(Waiting for semi-sync ACK from slave)
2,在开启一个事务3
3,假设卡在A阶段,那么事务3,肯定是看不到事务1,2写入的记录(XXXXX),且重启mysql后,事务2不会提交。。
4,假设卡在B阶段,那么事务3,肯定是可以看见事务1,2写入的记录(XXXXX),且重启mysql后,事务1,2都会提交。。
5, 假设卡在C阶段,那么事务3,肯定是可以看见事务3写入的记录(XXXXX)。

经过测试:
1,是卡在B阶段,也就是说事务3,既看不见事务1的提交内容,也看不见事务2的提交内容,且重启mysql后,事务1,2都已经提交。。
2,MySQL crash重启后,事务1,事务2的dml都已经提交成功,说明不是卡在A阶段。

性能
semi-sync vs lossless semi-sync 的性能对比


根据以上的测试,可以得知,lossless只卡在B阶段,普通的semi-sync是卡在C阶段。
lossless的性能远远好于普通的semi-sync,即(after_sync 优于 after_commit)
因为lossless 卡在B阶段的时候可以堆积事务,可以在C阶段进行group commit。
普通的semi-sync,卡在C阶段,事务都已经commit了,并没有堆积的过程。


CAP理论:



一致性【C】
可用性【A】
分区容忍性【P】
理论:CAP 三者不可兼得,必须要牺牲一个


分区,是一定存在的,不是你想不要就不要的。所以,这里只剩下两种组合


CP 牺牲可用性



这种做法,就是保留强一致性,牺牲可用性
案例:可以将rpl_semi_sync_master_timeout设置成一个无限大的值,比如:100天,那么master和slave就强一致了,但是可用性就大打折扣


AP 牺牲一致性



这种做法,就是保留高可用性,牺牲一致性
案例:比如原生的异步复制就是这样咯。可以快速做到切换,但是一致性就没有保障


阅读分享:https://henduan.com/FJHU5

编译安装MySQL报错CMake Error: your C compiler

运维 空心菜 回复了问题 2 人关注 5 个回复 3846 次浏览 2021-04-17 19:34 来自相关话题

修改MySQL5.7.31用户登录密码

数据库 chris 发表了文章 0 个评论 2217 次浏览 2021-03-08 22:30 来自相关话题

默认一般安装完成MySQL数据库root用户的密码为空,一般需要设置好root的密码,要不会造成不安全的情况发生。然而登录MySQL数据库后发现5.7版本跟5.6版本User表结构发生了变化,原本的 ...查看全部

默认一般安装完成MySQL数据库root用户的密码为空,一般需要设置好root的密码,要不会造成不安全的情况发生。然而登录MySQL数据库后发现5.7版本跟5.6版本User表结构发生了变化,原本的password字段没有了,这就导致在5.7下面修改用户密码的方式跟之前的版本不同,下面会介绍2种修改方式。


1. 使用set password语句

这种方法跟以前的版本修改密码是一致的,需要登录到MySQL后使用:


set password for root@localhost = password("123.com");

2. 直接更新user表

由于MySQL版本的升级,User表的结构改变了,好多网上使用的UPDATE语句不适用新版本的表结构,在这里我通过DESC语句来查看User表的结构,结果如图:


mysql> desc User;
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
| Field | Type | Null | Key | Default | Extra |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
|
Host | char(60) | NO | PRI | | |
| User | char(32) | NO | PRI | | |
|
Select_priv | enum('N','Y') | NO | | N | |
| Insert_priv | enum('N','Y') | NO | | N | |
|
Update_priv | enum('N','Y') | NO | | N | |
| Delete_priv | enum('N','Y') | NO | | N | |
|
Create_priv | enum('N','Y') | NO | | N | |
| Drop_priv | enum('N','Y') | NO | | N | |
|
Reload_priv | enum('N','Y') | NO | | N | |
| Shutdown_priv | enum('N','Y') | NO | | N | |
|
Process_priv | enum('N','Y') | NO | | N | |
| File_priv | enum('N','Y') | NO | | N | |
|
Grant_priv | enum('N','Y') | NO | | N | |
| References_priv | enum('N','Y') | NO | | N | |
|
Index_priv | enum('N','Y') | NO | | N | |
| Alter_priv | enum('N','Y') | NO | | N | |
|
Show_db_priv | enum('N','Y') | NO | | N | |
| Super_priv | enum('N','Y') | NO | | N | |
|
Create_tmp_table_priv | enum('N','Y') | NO | | N | |
| Lock_tables_priv | enum('N','Y') | NO | | N | |
|
Execute_priv | enum('N','Y') | NO | | N | |
| Repl_slave_priv | enum('N','Y') | NO | | N | |
|
Repl_client_priv | enum('N','Y') | NO | | N | |
| Create_view_priv | enum('N','Y') | NO | | N | |
|
Show_view_priv | enum('N','Y') | NO | | N | |
| Create_routine_priv | enum('N','Y') | NO | | N | |
|
Alter_routine_priv | enum('N','Y') | NO | | N | |
| Create_user_priv | enum('N','Y') | NO | | N | |
|
Event_priv | enum('N','Y') | NO | | N | |
| Trigger_priv | enum('N','Y') | NO | | N | |
|
Create_tablespace_priv | enum('N','Y') | NO | | N | |
| ssl_type | enum('','ANY','X509','SPECIFIED') | NO | | | |
|
ssl_cipher | blob | NO | | NULL | |
| x509_issuer | blob | NO | | NULL | |
|
x509_subject | blob | NO | | NULL | |
| max_questions | int(11) unsigned | NO | | 0 | |
|
max_updates | int(11) unsigned | NO | | 0 | |
| max_connections | int(11) unsigned | NO | | 0 | |
|
max_user_connections | int(11) unsigned | NO | | 0 | |
| plugin | char(64) | NO | | mysql_native_password | |
|
authentication_string | text | YES | | NULL | |
| password_expired | enum('N','Y') | NO | | N | |
|
password_last_changed | timestamp | YES | | NULL | |
| password_lifetime | smallint(5) unsigned | YES | | NULL | |
|
account_locked | enum('N','Y') | NO | | N | |
+------------------------+-----------------------------------+------+-----+-----------------------+-------+
45 rows in set (0.00 sec)

如上发现了一些疑似用来认证的字段,根据字段类型推断authentication_string应该是存储的用户密码,之后就开始尝试修改这一字段:


update user set authentication_string = password('123.com') where user='root' and host='localhost';

更改后退出发现依然不会生效,通过查阅资料发现,还需要把plugin字段的值改为mysql_native_password。个人感觉这个字段影响的是验证方式,更改之后就可以在登录的时候使用刚刚设置的密码来验证。修改语句如下:


update user set plugin = 'mysql_native_password' where user='root' and host='localhost';

后来了解到mysql_native_passwordcaching_sha2_password是MySQL的两种加密认证方式,一般MySQL 5默认使用前者,而8以后的版本使用后者,在这里虽然笔者使用的是5.7.31,但我确实是在更改了这个字段值以后才能正常用密码登录的。

MySQL8和PostgreSQL10功能对比

数据库 OS小编 发表了文章 0 个评论 1666 次浏览 2020-10-18 13:14 来自相关话题

现在MySQL 8和 ...查看全部

现在MySQL 8PostgreSQL 10已经发布,现在是重新审视两个主要的开源关系数据库如何相互竞争的好时机。


在这些版本之前,一般的看法是,虽然Postgres在功能集及血统方面更胜一筹,但MySQL在大规模并发读/写操作方面进行了更多的大规模测试。


但是随着最新版本的发布,两者之间的差距已大大缩小。


功能对比

下面看一下通常我们认为比较时髦的功能。






































特征 MySQL8 PostgreSQL10
查询和分析
通用表达式(CTEs) ✔ New
视窗功能 ✔ New
数据类型
支持JSON ✔ Improved
GIS / SRS ✔ Improved
全文搜索
扩展性
逻辑复制 ✔ New
半同步复制 ✔ New
声明式分区 ✔ New

过去常常说MySQL最适合在线交易,而PostgreSQL最适合分析过程,但是现在不再是如此局面。


通用表达式(CTEs)和窗口函数一直是多数人选择PostgreSQL的主要原因。但是现在,在同一个表中employees引用对表进行递归遍历boss_id,或者在排序结果中找到中间值(或50%百分位数),在MySQL上不再是问题。


PostgreSQL上的复制缺乏配置灵活性,这是Uber转向MySQL的原因。但是现在有了逻辑复制,可以通过使用更新版本的Postgres创建副本并切换到该副本来实现零停机时间升级。截断大型时序事件表中的陈旧分区也容易得多。


在功能方面,两个数据库现在彼此相同。


二者有什么区别?

现在有一个问题就是,我们到底是选择MySQL还是PostgreSQL,那选择的原因又是什么?


生态系统就是这些因素之一。MySQL拥有一个强大的生态系统,其中包含MariaDB,Percona,Galera等变体,以及InnoDB以外的其他存储引擎,但它们也可能令人不知所措。Postgres的高端选项有限,但是随着最新版本引入的新功能,这种情况将会改变。


治理是另一个因素。每个人都在担心甲骨文(或最初为SUN)收购MySQL时,他们会毁了该产品,但过去十年来情况并非如此。实际上,收购后发展加速。Postgres在工作治理和协作社区方面拥有悠久的历史。


体系结构的基本原理不会经常更改,但是值得值得我们回顾。





























特征 MySQL8 PostgreSQL10
架构 单进程 多进程
并发 多线程 fork(2)
表结构 聚合索引 Heap(堆)
页面压缩 透明 TOAST
更新 就地/回滚Segments 仅追加/ Hot
垃圾回收 清除线程 自动回收进程
Transaction Log REDO Log (WAL) WAL
Replication Log Separate (Binlog) WAL

进程对比线程

Postgres fork一个子进程来建立连接时,每个连接最多需要10 MB的空间。与MySQL的“每次连接线程”模型相比,内存压力更大,后者在64位平台上,线程的默认堆栈大小为 256KB。(当然,线程局部排序缓冲区等可以使此开销的重要性降低,即使可以忽略不计,但仍然如此。)


即使写时复制会与父进程一起保存一些共享的,不变的内存状态,但是当您有1000个以上的并发连接时,作为基于进程的体系结构的基本开销会增加负担,并且它可能是最重要的开销之一能力计划的因素。


也就是说,如果您在30台服务器上运行Rails应用程序,其中每台服务器具有16个CPU内核和32个Unicorn worker,则您有960个连接。在所有应用程序中,可能只有不到0.1%会达到这个规模,但这是需要牢记的。


聚合索引对比堆(Heap)表

聚合索引是其中行被直接嵌入主键的B树结构内的表结构。(非聚和)堆(Heap)是规则表结构,其中填充了与索引分开的数据行。


使用聚合索引时,当您通过主键查找记录时,单个I / O将检索整行,而非聚集索引始终通过遵循引用至少需要两个I / O。由于外键引用和联接将触发主键查找,因此影响可能很大,这将占查询的绝大多数。


聚合索引的理论缺点是,在使用次级索引进行查询时,遍历树节点的次数是您首先遍历次级索引,然后遍历聚合索引(也是一棵树)的两倍。


但是,考虑到现代惯例,将自动递增的整数作为主键[1](称为代理键),几乎总是希望拥有聚合索引。如果您要执行很多操作ORDER BY id来检索最新(或最旧)的N条记录,那就更是如此,我认为这适用于大多数记录。


Postgres不支持聚和索引,而MySQL(InnoDB)不支持堆(Heap)。但是,无论哪种方式,如果您有大量内存,则差异应该很小。


页面结构和压缩

Postgres和MySQL都具有基于页面的物理存储(8KB和16KB)。


PostgreSQL物理存储简介

页面结构看起来像上图所示。它包含一些标题,我们将在这里不进行介绍,但是它们包含有关页面的元数据。标头后面的项目是一个数组标识符,由(offset, length)指向元组或数据行的对组成。请记住,在Postgres中,可以通过这种方式将同一记录的多个版本存储在同一页面中。

MySQL的表空间结构与Oracle的表空间结构相似,它具有段,范围,页和行的多个层次结构层。


它还为UNDO提供了一个单独的部分,称为“回退部分”。与Postgres不同,MySQL将在同一区域保留同一记录的多个版本。


在两个数据库上,一行必须适合一个页面,这意味着一行必须小于8KB。(MySQL的页面中必须至少包含2行,巧合的是16KB / 2 = 8KB)


那么当列中有一个大的JSON对象时会发生什么?

Postgres使用TOAST(专用的影子表存储)。当且仅当选择行和列时,才会拉出大对象。换句话说,大量的黑盒子不会污染您宝贵的缓存。它还支持对TOASTed对象的压缩。


由于高端SSD存储供应商Fusion-io的贡献,MySQL具有称为“ 透明页面压缩”的更高级功能。它是专门为与SSD配合使用而设计的,固态硬盘的写入量与设备的寿命直接相关。


MySQL上的压缩不仅适用于页面外的大对象,而且适用于所有页面。它是通过在稀疏文件中使用打孔来实现的,稀疏文件ext4btrfs等现代文件系统支持。


有关更多详细信息,请参阅:通过FusionIO上的新MariaDB页面压缩显着提高性能


更新的开销

UPDATE是经常遗漏但对性能有重大影响并且可能是最具争议的主题的另一个功能。


这也是Uber放弃Postgres的另一个原因,这激起了许多Postgres拥护者的反驳。


两者都是MVCC数据库,可保留多个版本的数据以进行隔离)