【RDB】MariaDB 之事务、复制、集群

目录

• 简介
• 安装启动
• 权限
• 事务
  • 脏读、不可重复读、幻读
  • mvcc
• 复制
  • 异步复制
  • 半同步复制
  • gtid复制
• 集群(galera)
• 配置
• 监控(zabbix)

简介

环境：

• centos 7.4.1708
• mariadb 10.3.9

简介：

• mysql 由 mysqlab 公司开发。
• mariadb 是 mysql的一个分支，它是 mysql 之父 monty widenius 开发
• 目前很多知名的 linux 发行版已经使用 mariadb 替代了 mysql。如：rhel 7，centos 7。

mariadb的优点：

• 插件式存储引擎
• 单进程多线程
• mysql 有走向封闭的趋势
• mariadb 高度兼容 mysql

安装启动

安装

查看是否安装mariadb rpm包：
rpm -qa | grep mariadb

在 centos 7.4 默认源中的 mariadb 仍为5.x版本，当需要 10.x 版本时，可通过添加第三方源实现：

mariadb 官方源：

echo -e "[mariadb]\nname = mariadb\nbaseurl = http://yum.mariadb.org/10.3/centos7-amd64\ngpgkey=https://yum.mariadb.org/rpm-gpg-key-mariadb\ngpgcheck=1" > /etc/yum.repos.d/mariadb-10.3.repo

官方源比较慢的情况，可以使用清华镜像源(根据需要执行yum clean all)：

echo -e "[mariadb]\nname = mariadb\nbaseurl = https://mirrors.tuna.tsinghua.edu.cn/mariadb//mariadb-10.3.9/yum/centos/7.4/x86_64/\ngpgkey=https://yum.mariadb.org/rpm-gpg-key-mariadb\ngpgcheck=1" > /etc/yum.repos.d/mariadb-10.3.repo

安装mariadb客户端(包含mariadb-common、mariadb-client下载9mb 安装50m)：
yum install -y mariadb.x86_64

安装mariadb服务端(包含mariadb-common、mariadb-client、mariadb-server)：
yum install -y mariadb-server.x86_64

查看 mariadb 安装的文件：
rpm -ql mariadb-server 或 rpm -ql mariadb-client

目录文件	说明
/etc/my.cnf	默认配置文件
/var/lib/mysql/	文件夹下是 mariadb 数据库目录、错误日志和 socket 文件
mysql	mysql cli 客户端
mysqldump	备份工具，基于 mysql协议 向 mysqld 发起查询，将结果转化为insert语句导出。
mysqladmin	基于 mysql协议 管理 mysqld。
mysqlimport	mysql 导入工具

注意：

1. mariadb 在 10.x 版本以前包名为 mariadb，之后为 mariadb。但服务名仍为 mariadb：service mariadb start;

启动

启动mariadb服务：
service mariadb start

初始化(为root设置密码，删除测试数据库、匿名用户)：
/usr/bin/mysql_secure_installation

登录mysql查看版本：
mysqladmin version -p123123

一键卸载mariadb且清除mariadb数据(便于调试)：
yum -y remove `rpm -qa | grep mariadb` && rm -rf /var/lib/mysql

权限

1. 授权表：db、host、user、table_priv、column_priv、procs_priv
2. 用户账号：'username'@'host' host:主机名、ip、通配符(%,_)
3. 创建用户：create user 'username'@'host' [identity by 'passwd']
4. 查看用户权限：show grants for 'username'@'host';
5. 重命名用户：rename user oldname to newname;
6. 删除用户：drop user 'username'@'host';
7. 修改密码：set password

允许root远程访问：
grant all privileges on *.* to 'root'@'%' identified by '123123' with grant option;

1. with grant option 表示该用户可以将自己的权限授权给别人
2. 如果只授予部分权限，其中 all privileges 改为 select,insert,update,delete,create,drop,index,alter,grant,references,reload,shutdown,process,file 其中一部分。

精确到列的权限：

grant select(id,name) on testdb.users to testuser@'%' identified by '123123'

重载授权表:
flush privileges;

忘记root密码：

1. systemctl stop mariadb.service
2. mysqld_safe --skip-grant-tables
3. mysql -u root
   1. update mysql.user set password=password('newpassword') where user='root’;
   2. flush privileges;
4. systemctl restart mariadb.service

事务

mysql按照标准sql定义了4种隔离级别，较低的隔离级别，能带来更高的并发和更低的系统开销。

1. 未提交读(read-uncommitted)
   • 可以读到未提交的修改记录
2. 读已提交(read-committed)
   • 只要提交的修改记录(包括其他的事务)都可以读到
   • 基于mvcc并发控制
3. 可重复读(repeatable-read)
   • 在事务开始第一次读取后，其他事务可修改读到的数据，但读到的数据不会被修改(幻读情况下会新增和减少)
   • 基于mvcc并发控制
4. 串行读(serializable)
   • 事务开始后发生对数据的操作(即使发生读操作)，其他事务都不能修改数据
   • 基于锁控制：实际上串行读在rr级别上隐式加gap间隙共享锁：select ... for update

备注：

1. set tx_isolation='read-uncommitted'; 调整当前 session 隔离级别
2. select @@tx_isolation 查看当前 session 隔离级别
3. show processlist; 查看 mysql 连接状态

在4种隔离级别中又分别存在不同的读问题：

1. 脏读(dirty reads)
   • 在 read-uncommitted 级别会出现读到未提交的数据

   t1：select * from users where id = 1;
   t2：insert into `users`(`id`, `name`) values (1, 'foo'); -- 事务未提交
   t1：select * from users where id = 1; -- 会读到

2. 不可重复读(non-repeatable reads)
   • 在 read-committed 级别会出现先后读取不一致的情况(关注点：读-读)

   t1：select * from users where id = 2;
   t2：insert into `users`(`id`, `name`) values (2, 'foo');
   t2：commit;
   t1：select * from users where id = 2; -- 会读到

3. 幻读(phantom reads)
   • 在 repeatable-read 级别会出现插入事先不存在的记录时，发现(insert会隐式的select)这些数据又存在(关注点：读-写)

   t1：select * from users where id = 3; -- 判断是否有 id = 3 的数据,没有则插入
   t2：insert into `users`(`id`, `name`) values (3, 'bar'); -- 执行成功
   t1：insert into `users`(`id`, `name`) values (3, 'bar'); -- 执行失败，由于 t1 发生幻读，不能支持该业务执行

4. 锁读(lock reads)
   • 在 serializable 级别会出现读的数据无法修改情况

   t1：select * from users where id = 3;
   t2：update `users` set `name` = 'baz' where `id` = 3; -- 执行失败，由于 id = 3 的数据被锁

   注意：

5. 在同1次连接上，上次事务未提交，执行 start transaction;。会自动提交该连接上次的修改。

mvcc机制：

在 mvcc 之前，rc 和 rr 隔离级别是怎么工作？

在 mvcc 之前，是单纯依赖锁的机制实现隔离级别。
当t1修改1条数据时加上排他锁，t2事务的读操作会被阻塞。当t1提交或回滚，锁被释放时，才能读取到提交的数据。但一般应用都是读多写少，导致系统处于大量的等待中，非常低效。

有了 mvcc 机制后，效果是怎么样？

有了 mvcc 后，当数据被修改时，会生成1个副本出来供其他事务读取。不会出现阻塞情况，读的性能会大幅提升。只有 serializable 级别的读操作才有可能被阻塞。(mvcc应用在rc和rr隔离级别上)

mvcc 具体如何实现的？

• 在 mysql 中 mvcc 是在 innodb 存储引擎上实现的。
• innodb 为每行数据增加3个字段：隐藏的id、当前事务id、回滚指针。
• mvcc 依赖 undo log 和 readview 来确定数据的可见性。

undo log：记录了原始数据的多个副本，用来回滚和提供其他事务读取
readview：记录了活动事务id，用来确定可见哪个副本

1. 在每个事务开启执行第1条语句的时候，会创建1个readview。
2. 将行数据的当前事务trid 与 readview中的事务rvid 比较
   1. trid < 所有的 rvid：可见(之前的事务创建)
   2. trid > 所有的 rvid：不可见(新事务创建)
   3. trid 在 rvid 中存在：不可见(活动的事务创建)
   4. trid 在 rvid 中不存在：可见(内存中commit或自己创建)
   5. 当数据不可见时，会从数据的回滚指针获取数据重新判断一遍
3. rc 和 rr的区别：
   1. rr 在事务开始只创建1次 readview
   2. rc 在事务每次执行语句都会创建 readview

事务提交过程及日志变化：

1. 用 排他锁 锁定该行
2. 记录 redo buffer
3. copy 数据到 undo buffer
4. 内存中修改数据 填写隐藏字段 事务id 和 回滚指针

commit：

1. redo log 文件持久化(innodb_flush_log_at_trx_commit)
2. bin log 文件持久化(sync_binlog)(这一步完成能确保故障恢复)
3. innodb引擎 commit(数据持久化，undo log)

注意：

1. redo log 文件并不一定在commit时才做持久化
   1. master thread 每秒执行一次
   2. 每个事务提交时
   3. 当重做日志缓存可用空间 少于一半时
2. redo log 是连续的一段存储空间，而修改的数据很可能是随机的区域
3. undo log 并非在事务提交完立即释放
   1. 提交后放入待清理区域，由purge线程判断是否仍有其他事务在使用，来决定是否删除。
   2. 默认undo log 存储在 idb 表空间中，在 mariadb 10.0(mysql 5.7)后通过innodb_undo_directory 、innodb_undo_logs 、innodb_undo_tablespaces 可配置独立文件

主从复制

主从复制能提供水平扩展 数据备份 数据分析 高可用性等，故开启主从复制越来越必要。

复制

mariadb 主从复制工作3步：

1. 主库的数据更改记录到 binlog 中
2. 从库将主库的日志 复制到 relaylog 中
   1. 从库使用 io 线程请求主库
   2. 主库使用 dump 线程读取 binlog 传给
3. 备库 sql 线程读取 relaylog 事件，重放到数据库。

配置复制：

1. 在主库和从库创建复制账号
   1. grant replication slave, replication client on . to repl@'10.0.0.%' identified by 'p4ssword';
2. 配置主库和从库

   1. 配置主服务器：

      [mysqld]
      log_bin   = mysql-bin
      server_id = 1 # 唯一，可以用ip地址的末几位

   2. 从服务器：

      [mysqld]
      log_bin   = mysql-bin
      server_id = 2
      log_slave_updates = 1 # 重放同时写到binlog
      relay_log = /var/lib/mysql/mysql-relay-bin

3. 从库启动复制
   1. mariadb > change master to master_host='server1', -> master_user='repl', -> master_password='p4ssword', -> master_log_file='mysql-bin.000001', -> master_log_pos=0;
   2. mariadb > start slave;
   3. mariadb > show slave status\g

注意：

1. 要填写的复制的position，可以通过 show master status\g 查看
2. 启用复制功能不会给服务器太多的开销。(主要是开启 binlog 和 sync_binlog=1 fsync的开销)
3. 如果复制配置有问题，可以重置配置信息：stop slave; reset slave;

半同步复制：

默认复制是单向异步的，也支持半同步复制功能(mariadb 10.3 后内置不需要单独安装插件)。

1. 主库：
   set global rpl_semi_sync_master_enabled = 1;
set global rpl_semi_sync_master_wait_point = after_sync;
2. 从库：
   set global rpl_semi_sync_slave_enabled = 1;

semi配置：

配置项|推荐配置值|说明
rpl_semi_sync_master_enabled|on|开启主库半同步复制
rpl_semi_sync_master_timeout|10000|最多等待从库响应10s
rpl_semi_sync_master_wait_no_slave|on|当没有从节点时(从节点突然断开)是否继续等待
rpl_semi_sync_master_wait_point|after_sync|控制wait slave ack的时机
rpl_semi_sync_slave_enabled|on|开启从库半同步复制

原理：

1. 半同步复制是在事务提交时，等待至少1个从库接收并写到relay log才返回给客户端(wait slave ack)。
2. 半同步复制提高数据安全性，但也造成一定的延迟(最少是1次tcp/ip返还的时间)。
3. 半同步复制默认after_commit是在bin log持久化及存储引擎提交后再等待从库接收写到relay log，通过rpl_semi_sync_master_wait_point配置为after_sync，可以将从库复制操作改到主库存储引擎提交之前。

相当于有异步复制、半同步复制还有个全同步复制，代表为 mysql-cluster性能太差，需要等待所有slave都同步才commit成功(性能太差)

注意：

1. 半同步复制数据一致性并不能100%保证，在非常极端情况下，after_sync会出现从库数据多的情况，after_commit会出现从库数据丢失的情况。
2. after_sync 可以让存储引擎commit支持group commit。所以性能安全性都比after_commit好

gtid

从mariadb 10.0.2开始，gtid会自动启用，在 binlog 中的每个事件组(事务)都会先记录1个gtid。

全局事务id（简称gtid）由三个用短划线“ - ”分隔的数字组成。例如：0-1-10

1. 第一个数字0是域id，它特定于全局事务id（以下更多内容）。它是一个32位无符号整数。
2. 第二个数字是服务器id，与旧式复制中使用的相同。它是一个32位无符号整数。
3. 第三个数字是序列号。这是一个64位无符号整数，对于登录到binlog中的每个新事件组，它会单调递增。

为什么要使用gtid：

1. 以前复制需要确定 binlog 文件名+偏移量。使用gtid则会自动确定。
2. 以前通过 relaylog 文件记录复制进度，且和数据同步是独立进行。使用gtid，将会在数据更新的事务中一起更新状态(存在mysql.gtid_slave_pos)
3. 更适合mha时failover。

如何配置：

change master to master_use_gtid = { slave_pos | current_pos | no }

1. current_pos：当前服务器最后1条binlog命令的gtid记录
2. slave_pos：当前(从)服务器最后1次执行重放数据的gtid记录

完整：change master to master_host = "127.0.0.1", master_user = "root", master_use_gtid = current_pos;

select @@gtid_slave_pos 可查看slave最后1个gtid。
select @@gtid_current_pos 可查看当前服务器执行的最后1个gtid。

注意：

1. mariadb和mysql具有不同的gtid实现，并且它们彼此不兼容。
2. 完成复制的必要条件主库开启 binlog 日志，相当于开启主库的gtid。从库及时不开启 binlog, slave_pos 也会更新，但自执行的sql不会影响current_pos。
3. set global gtid_slave_pos = ""; 会重置gtid进度。

galera集群

在mariadb 5.5和mariadb 10.0中，mariadb galera server是一个独立的软件包，而不是标准的mariadb server软件包。从mariadb 10.1开始，mariadb server和mariadb galera server软件包已经合并，并且在安装mariadb时会自动安装galera软件包及其依赖项。galera部件在配置之前保持休眠状态，如插件或存储引擎。

相比于复制、半同步复制，galera集群相当于是同步复制。其实现原理完全与 binlog 没有任何关系。

配置步骤：

1. 配置
   ini [galera] # mandatory settings wsrep_on=on # rpm -ql galera.x86_64 -> /usr/lib64/galera/libgalera_smm.so wsrep_provider=/usr/lib64/galera/libgalera_smm.so # dns名称也有效，ip是性能的首选 wsrep_cluster_address="gcomm://172.17.145.110, 172.18.0.2" binlog_format=row default_storage_engine=innodb innodb_autoinc_lock_mode=2
2. 引导新集群
   $ galera_new_cluster(systemd推荐)
3. 在多台服务器上开启mysql服务
   $ service mariadb start

注意：

1. galera cluster方式会出现自增id不连续的情况，可使用guid由程序生成

配置

命令	说明
mysqld --verbose --help | less	查看默认配置及配置说明
cat /etc/my.cnf | grep -v '^#' | grep -v '^$'	查看去除注释后的配置文件
show [global] variables;	查看配置
set [global] name=value;	修改配置

配置项	默认值	推荐值	说明
autocommit	on	off	是否开启自动提交，默认开启，所有修改操作都会自动开启1个事务，并提交。(影响性能)
skip-name-resolve	false	true	跳过ip反解为域名过程，默认关闭，所有连接都会反解ip为域名。(影响性能以及授权)
innodb_flush_log_at_trx_commit	1	1	在事务提交时确保redolog持久化
innodb-file-per-table	true	true	独立表空间，每1个表都以独立文件存储
sync_binlog	0	1	在事务提交时确保binlog持久化

(配置项会不断更新比较重要的)

监控

监控可使用 zabbix 对mariadb 做监控。

(实现原理是通过查询 mariadb 的状态变量实现)

本文地址：https://www.cnblogs.com/neverc/p/9870088.html