目錄 簡介 安裝啟動 許可權 事務 臟讀、不可重覆讀、幻讀 MVCC 複製 非同步複製 半同步複製 GTID複製 集群(Galera) 配置 監控(Zabbix) 簡介 環境: CentOS 7.4.1708 MariaDB 10.3.9 簡介: MySQL 由 MySQLAB 公司開發。 MariaD ...
目錄
- 簡介
- 安裝啟動
- 許可權
- 事務
- 臟讀、不可重覆讀、幻讀
- 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 版本時,可通過添加第三方源實現:
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