目录
简介
安装启动
权限
事务
脏读, 不可重复读, 幻读
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 官方源 https://downloads.mariadb.org/MariaDB/repositories :
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 导入工具 |
注意:
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.service
- mysqld_safe --skip-grant-tables
- MySQL -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 次 readview
RC 在事务每次执行语句都会创建 readview
事务提交过程及日志变化:
用 排他锁 锁定该行
记录 redo buffer
copy 数据到 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; | 修改配置 |
配置项 | 默认值 | 推荐值 | 说明 |
---|---|---|---|
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
来源: https://www.cnblogs.com/neverc/p/9870088.html