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

目录

简介安装启动权限事务 脏读、不可重复读、幻读MVCC复制 异步复制半同步复制GTID复制集群(Galera)配置监控(Zabbix)

简介

环境：

CentOS 7.4.1708MariaDB 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 文件mysqlmysql cli 客户端mysqldump备份工具，基于 mysql协议 向 mysqld 发起查询，将结果转化为insert语句导出。mysqladmin基于 mysql协议 管理 mysqld。mysqlimportmysql 导入工具

注意：

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

权限

授权表：db、host、user、table_priv、column_priv、procs_priv用户账号：'username'@'host' host:主机名、IP、通配符(%,_)创建用户：create user 'username'@'host' [identity by 'passwd']查看用户权限：show grants for 'username'@'host';重命名用户：RENAME USER oldname TO newname;删除用户：DROP USER 'username'@'host';修改密码：SET PASSWORD

允许root远程访问：GRANT ALL PRIVILEGES ON *.* TO 'root'@'%' IDENTIFIED BY '123123' WITH GRANT OPTION;

WITH GRANT OPTION 表示该用户可以将自己的权限授权给别人如果只授予部分权限，其中 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密码：

systemctl stop mariadb.servicemysqld_safe --skip-grant-tablesmysql -u root update mysql.user set password=PASSWORD('newpassword') where User='root’;flush privileges;systemctl restart mariadb.service

事务

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

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

备注：

set tx_isolation='READ-UNCOMMITTED'; 调整当前 session 隔离级别select @@tx_isolation 查看当前 session 隔离级别show processlist; 查看 mysql 连接状态

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

脏读(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; -- 会读到不可重复读(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; -- 会读到幻读(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 发生幻读，不能支持该业务执行锁读(lock reads) 在 SERIALIZABLE 级别会出现读的数据无法修改情况T1：select * from users where id = 3; T2：update `users` set `name` = 'baz' where `id` = 3; -- 执行失败，由于 Id = 3 的数据被锁

注意：

在同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个readview。将行数据的当前事务TRID 与 readview中的事务RVID 比较 TRID < 所有的 RVID：可见(之前的事务创建)TRID > 所有的 RVID：不可见(新事务创建)TRID 在 RVID 中存在：不可见(活动的事务创建)TRID 在 RVID 中不存在：可见(内存中commit或自己创建)当数据不可见时，会从数据的回滚指针获取数据重新判断一遍RC 和 RR的区别： RR 在事务开始只创建1次 readviewRC 在事务每次执行语句都会创建 readview

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

用 排他锁 锁定该行记录 redo buffercopy 数据到 undo buffer内存中修改数据 填写隐藏字段 事务Id 和 回滚指针

commit：

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

注意：

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

主从复制

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

复制

MariaDB 主从复制工作3步：

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

配置复制：

在主库和从库创建复制账号 GRANT REPLICATION SLAVE, REPLICATION CLIENT ON . TO repl@'10.0.0.%' IDENTIFIED BY 'p4ssword';配置主库和从库

配置主服务器：

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

从服务器：

[mysqld] log_bin = mysql-bin server_id = 2 log_slave_updates = 1 # 重放同时写到binlog relay_log = /var/lib/mysql/mysql-relay-bin从库启动复制 MariaDB > CHANGE MASTER TO MASTER_HOST='server1', -> MASTER_USER='repl', -> MASTER_PASSWORD='p4ssword', -> MASTER_LOG_FILE='mysql-bin.000001', -> MASTER_LOG_POS=0;MariaDB > START SLAVE;MariaDB > SHOW SLAVE STATUS\G

注意：

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

半同步复制：

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

主库：set global rpl_semi_sync_master_enabled = 1;set global rpl_semi_sync_master_wait_point = AFTER_SYNC;从库：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个从库接收并写到relay log才返回给客户端(Wait Slave ACK)。半同步复制提高数据安全性，但也造成一定的延迟(最少是1次tcp/ip返还的时间)。半同步复制默认AFTER_COMMIT是在bin log持久化及存储引擎提交后再等待从库接收写到relay log，通过rpl_semi_sync_master_wait_point配置为AFTER_SYNC，可以将从库复制操作改到主库存储引擎提交之前。

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

注意：

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

GTID

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

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

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

为什么要使用GTID：

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

如何配置：

CHANGE MASTER TO master_use_gtid = { slave_pos | current_pos | no }

current_pos：当前服务器最后1条binlog命令的gtid记录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。

注意：

MariaDB和MySQL具有不同的GTID实现，并且它们彼此不兼容。完成复制的必要条件主库开启 binlog 日志，相当于开启主库的GTID。从库及时不开启 binlog, slave_pos 也会更新，但自执行的SQL不会影响current_pos。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 没有任何关系。

配置步骤：

配置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引导新集群 $ galera_new_cluster(Systemd推荐)在多台服务器上开启mysql服务 $ service mariadb start

注意：

Galera Cluster方式会出现自增ID不连续的情况，可使用GUID由程序生成

配置

命令说明mysqld --verbose --help | less查看默认配置及配置说明cat /etc/my.cnf | grep -v '^#' | grep -v '^$'查看去除注释后的配置文件show [global] variables;查看配置set [global] name=value;修改配置 配置项默认值推荐值说明autocommitonoff是否开启自动提交，默认开启，所有修改操作都会自动开启1个事务，并提交。(影响性能)skip-name-resolvefalsetrue跳过IP反解为域名过程，默认关闭，所有连接都会反解IP为域名。(影响性能以及授权)innodb_flush_log_at_trx_commit11在事务提交时确保redolog持久化innodb-file-per-tabletruetrue独立表空间，每1个表都以独立文件存储sync_binlog01在事务提交时确保binlog持久化

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

监控

监控可使用 Zabbix 对MariaDB 做监控。

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

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