分类 mysql 下的文章

最近本地环境装了个 MariaDB - 10.1.37, 发现了这个坑

安装:

基于 能不折腾就不折腾的原则,直接使用 apt 安装

sudo apt install mariadb-server

安装完之后,初始化一下

sudo mysql_secure_installation

尝试登录

mysql -uroot -hlocalhost -p123456
ERROR 1698 (28000): Access denied for user 'root'@'localhost'

what the f**k?

凭借多年的debug经验,熟悉的打开 google,然后输入 ERROR 1698 (28000): Access denied for user 'root'@'localhost' 点击搜索。

然后,根据搜索结果,排查,尝试

首先出现最多的结果是:

update user set password=PASSWORD("123456") where user='root';  // 就是修改下密码

这里怀疑了下自己,是不是设置密码的时候弄错了,

这里还是写下步奏

  1. 修改 my.cnf 在 [mysqld] 下面增加 skip-grant-tables // 登录的时候忽略mysql 密码

    注意 Mariadb 是/etc/mysql/mariadb.conf.d/50-server.cnf 
  2. sudo service restart mysql // 重启一下mysql
  3. mysql -uroot // 这样就可以root登录了
  4. update mysql.user set password=PASSWORD("123456") where mysql.user='root'; // 修改下root密码
  5. 将第一步的 skip-grant-tables 去掉
  6. sudo service restart mysql // 重启一下mysql

尝试了一把后发现,没有卵用(意料之中,多年码农的经验告诉我密码没有错!)。。。

继续谷歌

看到了 这个 blog https://blog.csdn.net/dream361/article/details/65445168

不得不说 csdn 还是有好文章的

划重点

与之前版本mysql不同,需要获得操作系统管理员权限,才能登录MariaDBroot用户,普通操作系统用户不能登录MariaDB数据库root用户
dream361

解决方案

sudo mysql -uroot -hlocalhost -p123456

没错,就是加个sudo,用 root 权限去登录就可以了。。。

ps:

直接密码写到命令行是不安全的,例如:

sudo mysql -uroot -hlocalhost -p123456

黑客攻克了你的电脑,然后看历史就能够知道mysql 的用户名跟密码了,不过本地环境就随意了。。

安全点的做法是:

sudo mysql -uroot -hlocalhost -p

直接用root 也是不安全的,可以创建个新用户,然后给一定的权限

  • CREATE USER 'username'@'host' IDENTIFIED BY 'password'; // 新建用户

    • username:你将创建的用户名
    • host:指定该用户在哪个主机上可以登陆,如果是本地用户可用localhost,如果想让该用户可以从任意远程主机登陆,可以使用通配符%
    • password:该用户的登陆密码,密码可以为空,如果为空则该用户可以不需要密码登陆服务器
  • GRANT privileges ON databasename.tablename TO 'username'@'host' // 授权

    • privileges:用户的操作权限,如SELECT,INSERT,UPDATE等,如果要授予所的权限则使用ALL
    • databasename:数据库名
    • tablename:表名,如果要授予该用户对所有数据库和表的相应操作权限则可用*表示,如*.*
    • 注意用以上命令授权的用户不能给其它用户授权,如果想让该用户可以授权,用 GRANT privileges ON databasename.tablename TO 'username'@'host' WITH GRANT OPTION;

更详细的操作:
https://www.jianshu.com/p/d7b9c468f20d

作者:Justd
链接:https://juejin.im/post/5bf6e2946fb9a049a62c377b
来源:掘金

听说MySQL能恢复到半个月内任意一秒的状态
要从一条更新语句说起,如果将ID=2这一行的值+1,SQL语句可以这样写:

mysql> update T set c=c+1 where ID=2;

复制代码执行一条更新语句同样会走一遍查询语句的流程:

  • 连接数据库
  • 清空该表涉及到的缓存
  • 分析器通过词法和语法解析出这是一条更新语句,并确定涉及到表与字段
  • 优化器决定使用"ID"这个索引
  • 执行器找到这一行数据,然后进行更新操作

与查询过程不同的是,更新涉及到两个日志模块:redo log(重做日志)bin log(归档日志)

临时记录:redo log

酒店掌柜有一个账本和一个小黑板,来做赊账的记录。有以下两种方案:

  1. 每一笔账都打开账本做记录,当有人还账时,找到对应的赊账记录,修改记录的状态
  2. 先在黑板上记录本次要做的操作,打烊后按照黑板上的记录向账本上进行核算

当生意红火,顾客络绎不绝时,第一种方案效率实在是低下,掌柜的一定按照第二种方案来记账。
同样的,MySQL如果每次更新操作都要写入磁盘,在磁盘中找到对应记录,然后更新,这个过程的IO成本、查找成本都太高了。

为了解决和这个问题,MySQL就使用了类似于黑板-账本模式来提高效率。这一模式即为WAL技术,全程为Write-Ahead Logging,关键点:先写日志,再写磁盘,也就是前文中先写黑板,再写账本。

具体步骤如下:
当有记录需要更新,innoDB先把记录写入redo log中,并更新内存,这是更新操作就算结束了。innoDB引擎会在适当的时候讲操作记录更新到磁盘里,这一动作一般是系统比较闲的时候做的。
redo log的大小是固定的,共有4个文件组成,每个大小为1G。逻辑上可以将4个文件理解为环形,从头开始写,写到末尾又重新开始新的一轮,如下图所示

redolog

write pos为当前记录位置,check point为当前擦除点的位置,当记录更新时,check point会随着文件的记录向后移动。擦除后未写入的位置可以记录新的操作。当write pos追上了check point,则需要停下来写入动作,将redo log内容写入磁盘,然后清除check point向后移动。

有了redo loginnoDB可以知晓每一次操作,保证当数据库发生异常重启时,之前的能够根据redo log恢复之前的记录,这种能力叫做crash-safe

归档日志:bin log

redo logbin log日志的区别:

redo log 是属于innoDB引擎所有,bin log是server提供的,所有引擎都可以使用
redo log 属于物理日志,记录"在某个数据页做了什么修改",bin log记录的是该语句逻辑日志"将ID=2的这一行c的值+1"
redo log日志文件是循环使用的,空间有使用完的时刻,bin log是追加记录的,不会覆盖之前的记录

也就是说,server搭配其他引擎是没有redo log的,因此也就没有了crash-safe能力

更新具体流程

基于对两个日志文件的了解,再次深入了解更新的流程

  1. 执行器先通过引擎使用树搜索找到ID=2这一行,如果该记录所在的数据页本身就在内存中,则直接返回执行器,否则先从磁盘读入内存,然后返回
  2. 执行器拿到数据后将c的值加一,然后通过引擎的写入接口将修改后的数据写入
  3. 引擎j将新数据更新到内存中,然后在redo log中记录此次修改,这时redo log中该记录的状态置为prepare,并告知执行器已经更新完成,随时可以提交事务
  4. 执行器生成此次操作的bin log,将bin log写入磁盘中
  5. 执行器调用引擎的提交事务接口,引擎将刚刚写入的redo log置为commit状态,更新结束

下图是《MySQL实战》提供的流程图:

1673c7aa84888e9f.webp

浅色代表在innoDB中执行,深色在server中执行

两阶段提交

从上图可以看出,redo log是分两个阶段来提交的,这是为了保持两个日志逻辑上一致
如果不用两阶段提交会发生什么呢 利用反证法来看下:
假设初始ID=2的数据行,c的值为0,现在要执行c+1的操作。

  1. 先记录redo log 后记录bin log 如果刚记录完redo log,还没有记录bin log时,c的值已经记录变为1,这时MySQL服务崩溃重启,根据crash-safe机制,可以用redo log来恢复数据库,恢复后的数据中c的值为1。由于bin log中没有记录这一变化,以后备份bin log时,c的值还是0。如果有一天需要从bin log回复一台备用数据库,由于bin log少了一次更新,则最后恢复出来的c值仍然为0,与原库中值不符合
  2. 先记录bin log 后记录redo log 写完bin log就发生crash,还没来得及写入redo log,崩溃恢复后这个事务是无效的,因此c的值还是0,但是bin log中已经记录了"将c的值+1"的日志,所以用bin log恢复出来的数据多出来一个事务,使得c的值为1,与原库中数据不符。
  3. 两阶段提交 记录过bin log回过头提交commit(可参见评论区知识点) 更新redo log后,还没有记录bin log时崩溃,这时redo log的状态还是prepare,事务并没有提交,而且bin log中没有记录,因此由于crash-safe机制,并不会恢复该记录,c的值仍然为0,由于bin log中没有记录,以后从bin log恢复数据时,c的值在此操作中并没有记录变化,因此还是0,与原库中数据一致;另一种情况:更新redo log,也更新了bin log,下一步执行器调用commit接口前崩溃,这时虽然redo log中状态为prepare,但是从bin log中查到有记录,所以还是会从redo log中恢复c=1,后面直接从bin log恢复出新的数据库时,因为已经记录c的值+1,所以与原库中的值相同

总结如下:

两种方式确定记录完整:

  1. redo log状态为 commit
  2. redo log状态为prepare并且bin log记录完整 (提交commit之前)

总结

这节主要学习了两个日志文件的用法 redo log 用于保证 crash-safe 能力,bin log用于恢复数据的完整性

innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。这样可以保证 MySQL 异常重启之后数据不丢失。
sync_binlog 这个参数设置成 1 的时候,表示每次事务的 binlog 都持久化到磁盘。这样可以保证 MySQL 异常重启之后 binlog 不丢失。

评论区知识点:

  • binlog没有被用来做崩溃恢复,binlog是可以关的,你如果有权限,可以"set sql_log_bin=0"关掉本线程的binlog日志。 所以只依赖binlog来恢复就靠不住的
  • @高枕 同学的评论简单精炼的表达了两阶段提交机制下的工作状态:

    记录日志共有三个过程:

    1. prepare阶段
    2. 写binlog
    3. commit

    当在2之前崩溃时
    重启恢复:后发现没有commit,回滚。备份恢复:没有binlog。备份与原库一致
    当在3之前崩溃时
    重启恢复:虽没有commit,但满足prepare和binlog完整,所以重启后会自动commit。备份:有binlog. 备份与原库一致

  • 来自@黄金的太阳

    问: redo log本身也是文件,记录文件的过程其实也是写磁盘,那和文中提到的离线写磁盘操作有何区别?
    响应一次SQL我理解是要同时操作两个日志文件?也就是写磁盘两次?

    作者回复:
    写redo log是顺序写,不用去“找位置”,而更新数据需要找位置,因此redo log写的速度更快
    其实是3次(redolog两次 binlog 1次)。不过在并发更新的时候会合并写