mariadb数据库怎么返回上一级

转载

码海无压 2024-10-05 14:18:43

文章标签 mariadb数据库怎么返回上一级数据库操作系统运维 mysql 文章分类 计算机视觉人工智能

简介

环境：

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 导入工具

注意：

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; -- 会读到
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; -- 会读到
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 发生幻读，不能支持该业务执行
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 的数据被锁
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配置：

原理：

半同步复制是在事务提交时，等待至少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持久化