您好,登錄后才能下訂單哦!
MariaDB數(shù)據(jù)庫管理系統(tǒng)是MySQL的一個(gè)分支,主要由開源社區(qū)在維護(hù),采用GPL授權(quán)許可。開發(fā)這個(gè)分支的原因之一是:甲骨文公司收購了MySQL后,有將MySQL閉源的潛在風(fēng)險(xiǎn),因此社區(qū)采用分支的方式來避開這個(gè)風(fēng)險(xiǎn)。[4]
MariaDB的目的是完全兼容MySQL,包括API和命令行,使之能輕松成為MySQL的代替品。在存儲引擎方面,10.0.9版起使用XtraDB(名稱代號為Aria)來代替MySQL的InnoDB。
MariaDB由MySQL的創(chuàng)始人麥克爾·維德紐斯主導(dǎo)開發(fā),他早前曾以10億美元的價(jià)格,將自己創(chuàng)建的公司MySQL AB賣給了SUN,此后,隨著SUN被甲骨文收購,MySQL的所有權(quán)也落入Oracle的手中。MariaDB名稱來自麥克爾·維德紐斯的女兒瑪麗亞(英語:Maria)的名字。
注意:以上內(nèi)容來自維基百科;
MariaDB官方網(wǎng)站:http://www.mariadb.org/
插件式存儲引擎:存儲管理器有多種實(shí)現(xiàn)版本,彼此間的功能和特性可能略有區(qū)別;用戶可根據(jù)需要靈活選擇;存儲引擎頁稱為“表類型”。
(1) 更多的存儲引擎
MylSAM:不支持事務(wù)
MyISAM -->Aria
InnoDB -->XtraDB;支持事務(wù)
(2) 諸多擴(kuò)展和新特性
(3) 提供了較多的測試組件
(4) truly open source
安裝和使用MariaDB
安裝環(huán)境:
操作系統(tǒng)版本 | 內(nèi)核版本 |
---|---|
CentOS 7.0 | 3.10.0-229.el7.x86_64 |
安裝方式:
(1)rpm包:由OS的發(fā)行商提供或程序官方提供;
(2)源碼包
(3)通用二進(jìn)制格式
1、創(chuàng)建mysql系統(tǒng)用戶
[root@bogon src]# groupadd -r -g 301 mysql
[root@bogon src]# useradd -r -g 301 -u 301 mysql
2、下載及解壓
[root@bogon src]# wget https://downloads.mariadb.org/interstitial/mariadb-galera-5.5.54/bintar-linux-glibc_214-x86_64/mariadb-galera-5.5.54-linux-glibc_214-x86_64.tar.gz
[root@bogon src]# tar -xf mariadb-galera-5.5.54-linux-glibc_214-x86_64.tar.gz -C /usr/local/
[root@bogon src]# cd /usr/local/
[root@bogon local]#ln -sv mariadb-galera-5.5.54-linux-glibc_214-x86_64/ mysql
‘mysql’ -> ‘mariadb-galera-5.5.54-linux-glibc_214-x86_64/’
[root@bogon local]# ll
drwxr-xr-x. 2 root root 6 Jun 10 2014 bin
drwxr-xr-x. 2 root root 6 Jun 10 2014 etc
drwxr-xr-x. 2 root root 6 Jun 10 2014 games
drwxr-xr-x. 2 root root 6 Jun 10 2014 include
drwxr-xr-x. 2 root root 6 Jun 10 2014 lib
drwxr-xr-x. 2 root root 6 Jun 10 2014 lib64
drwxr-xr-x. 2 root root 6 Jun 10 2014 libexec
drwxrwxr-x. 13 1021 1004 4096 Jan 4 06:09 mariadb-galera-5.5.54-linux-glibc_214-x86_64
lrwxrwxrwx. 1 root root 45 Jun 28 22:42 mysql -> mariadb-galera-5.5.54-linux-glibc_214-x86_64/
3、修改解壓目錄后所有文件屬主及屬組
[root@bogon local]# cd mysql/
[root@bogon mysql]# chown -R root.mysql ./*
4、創(chuàng)建存放數(shù)據(jù)的目錄,以/data/mysql為例
[root@bogon mysql]# mkdir /data/mysql
[root@bogon mysql]# chown -R mysql.mysql /data/mysql/
5、準(zhǔn)備配置文件
[root@bogon mysql]# mkdir /etc/mysql
[root@bogon mysql]# cp support-files/my-large.cnf /etc/mysql/my.cnf
[root@bogon mysql]# vi /etc/mysql/my.cnf 編輯
[mysqld] #添加如下三個(gè)配置參數(shù)
skip_name_resolve = ON
datadir = /data/mysql
innodb_file_per_table = ON
mysql配置文件查找次序:
/etc/my.cnf 而后 /etc/mysql/my.cnf 而后--default.extra-file=/PATH/TO/COF_FILE 最后 ~/.my.cnf
6、提供啟動腳
[root@bogon mysql]# cp support-files/mysql.server /etc/init.d/mysqld
[root@bogon mysql]# chmod +x /etc/init.d/mysqld
[root@bogon mysql]# chkconfig --add mysqld
7、初始化數(shù)據(jù)庫并啟動mysqld服務(wù)
[root@bogon mysql]# ./scripts/mysql_install_db --user=mysql --datadir=/data/mysql/
[root@bogon mysql]# ls /data/mysql/
aria_log.00000001 mysql mysql-bin.000002 performance_schema
aria_log_control mysql-bin.000001 mysql-bin.index test
[root@bogon mysql]# service mysqld start
Starting MySQL.170628 22:53:36 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170628 22:53:36 mysqld_safe Starting mysqld daemon with databases from /data/mysql
/usr/local/mysql/bin/mysqld_safe_helper: Can't create/write to file '/var/log/mariadb/mariadb.log' (Errcode: 2)
.... SUCCESS!
[root@bogon mysql]# ss -tnl|grep 3306
LISTEN 0 50 *:3306 *:*
[root@bogon mysql]# ps -ef|grep mysqld
root 36474 1 0 22:53 pts/2 00:00:00 /bin/sh /usr/local/mysql/bin/mysqld_safe --datadir=/data/mysql --pid-file=/data/mysql/bogon.pid
mysql 36920 36474 0 22:53 pts/2 00:00:00 /usr/local/mysql/bin/mysqld --basedir=/usr/local/mysql --datadir=/data/mysql --plugin-dir=/usr/local/mysql/lib/plugin --user=mysql --log-error=/var/log/mariadb/mariadb.log --pid-file=/data/mysql/bogon.pid --socket=/tmp/mysql.sock --port=3306 --wsrep_start_position=00000000-0000-0000-0000-000000000000:-1
root 36960 36136 0 22:56 pts/2 00:00:00 grep --color=auto mysqld
8、安裝后配置
[root@bogon mysql]# vi /etc/profile.d/mariadb.sh #編輯
export PATH=/usr/local/mysql/bin:$PATH
[root@bogon mysql]# source /etc/profile.d/mariadb.sh
至此通用二進(jìn)制格式安裝結(jié)束
操作系統(tǒng) | MySQL版本 | MySQL配置文件 | MySQL數(shù)據(jù)目錄 |
---|---|---|---|
CentOS 7.0 | mariadb-5.5.54 | /etc/my.cnf | /data/mysql |
實(shí)現(xiàn)目的:開啟MySQL慢查詢?nèi)罩竟δ埽惭b使用MySQL慢查詢分析mysqlsla。
1、開啟MySQL慢查詢功能
[root@bogon mysql]# mysql -u root -p #進(jìn)入mysql控制臺
Enter password:
Welcome to the MariaDB monitor. Commands end with ; or \g.
Your MariaDB connection id is 4
Server version: 5.5.54-MariaDB-wsrep MariaDB Server, wsrep_25.14.r9949137
Copyright (c) 2000, 2016, Oracle, MariaDB Corporation Ab and others.
Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.
MariaDB [(none)]>
查看mysql慢查詢功能
MariaDB [(none)]> show variables like '%slow%';
----------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries | OFF |
| log_slow_rate_limit | 1 |
| log_slow_verbosity | |
| slow_launch_time | 2 |
| slow_query_log | OFF |
| slow_query_log_file | bogon-slow.log |
+---------------------+--------------------------------------------------------------------------------------------------------------+
7 rows in set (0.00 sec)
MariaDB [(none)]> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 0 |
+---------------------+-------+
2 rows in set (0.01 sec)
開啟MySQL慢查詢功能
MariaDB [(none)]> set global slow_query_log=ON;
Query OK, 0 rows affected (0.08 sec)
MariaDB [(none)]> show variables like '%slow%';
+---------------------+--------------------------------------------------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+--------------------------------------------------------------------------------------------------------------+
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_cache_miss,tmp_table,tmp_table_on_disk |
| log_slow_queries | ON |
| log_slow_rate_limit | 1 |
| log_slow_verbosity | |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | bogon-slow.log
查看MySQL慢查詢時(shí)間設(shè)置默認(rèn)10秒
MariaDB [(none)]> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
設(shè)置慢查詢記錄超過5秒的記錄
MariaDB [(none)]> set global long_query_time=5;
Query OK, 0 rows affected (0.00 sec)
查看一下:
MariaDB [(none)]> show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name | Value |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+
1 row in set (0.00 sec)
MariaDB [(none)]> set global long_query_time=5;
Query OK, 0 rows affected (0.00 sec)
MariaDB [(none)]>
2、測試MySQL慢查詢
退出當(dāng)前mysql控制臺重新登錄
測試MySQL慢查詢
MariaDB [(none)]> select sleep(6);
查看MySQL慢查詢?nèi)罩韭窂?/p>
MariaDB [(none)]> show variables like '%slow%';
+---------------------+-----------------------------------------------------------------------+
| Variable_name | Value |
+---------------------+-----------------------------------------------------------------------+
| log_slow_filter | admin,filesort,filesort_on_disk,full_join,full_scan,query_cache,query_|
| log_slow_queries | ON |
| log_slow_rate_limit | 1 |
| log_slow_verbosity | |
| slow_launch_time | 2 |
| slow_query_log | ON |
| slow_query_log_file | bogon-slow.log |
+---------------------+-----------------------------------------------------------------------+
7 rows in set (0.00 sec)
查看MySQL慢查詢狀態(tài)
MariaDB [(none)]> show global status like '%slow%';
+---------------------+-------+
| Variable_name | Value |
+---------------------+-------+
| Slow_launch_threads | 0 |
| Slow_queries | 1 |
+---------------------+-------+
2 rows in set (0.00 sec)
退出MySQL控制臺
MariaDB [(none)]> exit;
# 查看MySQL慢查詢?nèi)罩局惺欠裼袆偛潘鶊?zhí)行的select sleep(6)的慢查詢?nèi)罩居涗?/span>
[root@bogon mysql]# cat /data/mysql/bogon-slow.log
/usr/local/mysql/bin/mysqld, Version: 5.5.54-MariaDB-wsrep (MariaDB Server, wsrep_25.14.r9949137). started with:
Tcp port: 3306 Unix socket: /tmp/mysql.sock
Time Id Command Argument
# Time: 170629 1:23:34
# User@Host: root[root] @ localhost []
# Thread_id: 5 Schema: QC_hit: No
# Query_time: 6.000862 Lock_time: 0.000000 Rows_sent: 1 Rows_examined: 0
SET timestamp=1498670614;
select sleep(6);
備注:還可以通過修改MySQL配置文件參數(shù),開啟MySQL慢查詢功能;
3、修改MySQL配置文件開啟慢查詢功能
# 編輯,在[mysqld]段下添加以下代碼
[root@bogon mysql]# vi /etc/my.cnf
#開啟MySQL慢查詢功能
slow-query-log = ON
# 設(shè)置MySQL慢查詢?nèi)罩韭窂?/span>
slow_query_log_file = /data/mysql/bogon-slow.log
#修改為記錄5秒內(nèi)的查詢,默認(rèn)不設(shè)置此參數(shù)為記錄10秒內(nèi)的查詢
long_query_time = 5
#記錄未使用索引的查詢
log-queries-not-using-indexes = ON
#保存退出
:wq!
# 重啟MySQL服務(wù)
service mysqld restart
啟動報(bào)錯(cuò):
[root@bogon mysql]# service mysqld restart
Shutting down MySQL.. SUCCESS!
Starting MySQL.170629 02:10:22 mysqld_safe Logging to '/var/log/mariadb/mariadb.log'.
170629 02:10:22 mysqld_safe Starting mysqld daemon with databases from /data/mysql
/usr/local/mysql/bin/mysqld_safe_helper: Can't create/write to file '/var/log/mariadb/mariadb.log' (Errcode: 2)
ERROR!
ERROR! Failed to restart server.
解決方法:
[mysqld_safe]
#log-error=/var/log/mariadb/mariadb.log 注釋這行
pid-file=/var/run/mariadb/mariadb.pid
明天部署mysqlsla,敬請期待~~~~~
二、安裝MySQL慢查詢分析工具mysqlsla
1、安裝依賴包
[root@bogon ~]# yum install wget perl perl-DBI perl-DBD-MySQL mysql perl-Time-HiRes perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
2、安裝mysqlsla
存放包目錄
[root@bogon ~]# cd /usr/local/src/
下載mysqlsla包
[root@bogon src]# wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz ##這個(gè)連接似乎已經(jīng)掛了
終于在51cto有人分享這個(gè)包了,感謝該平臺分享
http://down.51cto.com/data/705945
[root@bogon src]# tar -xf 51CTO下載-mysqlsla-2.03.tar.gz
[root@bogon src]# cd mysqlsla-2.03/
[root@bogon mysqlsla-2.03]# perl Makefile.PL
Checking if your kit is complete...
Looks good
Writing Makefile for mysqlsla
[root@bogon mysqlsla-2.03]# make
cp lib/mysqlsla.pm blib/lib/mysqlsla.pm
cp bin/mysqlsla blib/script/mysqlsla
/usr/bin/perl -MExtUtils::MY -e 'MY->fixin(shift)' -- blib/script/mysqlsla
Manifying blib/man3/mysqlsla.3pm
[root@bogon mysqlsla-2.03]# make install
Installing /usr/local/share/perl5/mysqlsla.pm
Installing /usr/local/share/man/man3/mysqlsla.3pm
Installing /usr/local/bin/mysqlsla
Appending installation info to /usr/lib64/perl5/perllocal.pod
3、使用mysqlsla分析慢查詢?nèi)罩?/span>
#查詢記錄最多的20個(gè)sql語句,并寫到select.log中去;
[root@bogon mysqlsla-2.03]# mysqlsla -lt slow --sort t_sum --top 20 /data/mysql/bogon-slow.log >/tmp/select.log
#統(tǒng)計(jì)慢查詢文件為/data/mysql/bogon-slow.log的所有select的慢查詢sql,并顯示執(zhí)行時(shí)間最長的100條sql,并寫到sql_select.log中去;[root@bogon mysqlsla-2.03]# mysqlsla -lt slow -sf "+select" -top 100 /data/mysql/bogon-slow.log >/tmp/sql_select.log
#統(tǒng)計(jì)慢查詢文件為/data/mysql/bogon-slow.log的數(shù)據(jù)庫為mydata的所有select和update的慢查詢sql,并查詢次數(shù)最多的100條sql,并寫到sql_num.sql中去;[root@bogon mysqlsla-2.03]# mysqlsla -lt slow -sf "+select,update" -top 100 -sort c_sum -db mydata /data/mysql/bogon-slow.log >/tmp/sql_num.log
4、使用參數(shù)說明
1. --log-type (-lt) type logs:通過這個(gè)參數(shù)來指定log的類型,主要有slow, general, binary, msl, udl,分析slow log時(shí)通過指定為slow;
2. --sort:指定使用什么參數(shù)來對分析結(jié)果進(jìn)行排序,默認(rèn)是按照t_sum來進(jìn)行排序。t_sum按總時(shí)間排序, c_sum按總次數(shù)排序;
3. --top:顯示sql的數(shù)量,默認(rèn)是10,表示取按規(guī)則排序的前多少條;
4. --statement-filter (-sf) [+-][TYPE]:過濾sql語句的類型,比如select、update、drop. [TYPE]有SELECT, CREATE, DROP, UPDATE, INSERT,例如"+SELECT,INSERT",不出現(xiàn)的默認(rèn)是-,即不包括。
5. --databases db:要處理哪個(gè)庫的日志:
5、分析后內(nèi)容參數(shù)說明
1. queries total: 總查詢次數(shù)
2. unique:去重后的sql數(shù)量
3. sorted by : 輸出報(bào)表的內(nèi)容排序 最重大的慢sql統(tǒng)計(jì)信息, 包括 平均執(zhí)行時(shí)間, 等待鎖時(shí)間, 結(jié)果行的總數(shù), 掃描的行總數(shù).
4. Count: sql的執(zhí)行次數(shù)及占總的slow log數(shù)量的百分比.
5. Time: 執(zhí)行時(shí)間, 包括總時(shí)間, 平均時(shí)間, 最小, 最大時(shí)間, 時(shí)間占到總慢sql時(shí)間的百分比.
6. 95% of Time: 去除最快和最慢的sql, 覆蓋率占95%的sql的執(zhí)行時(shí)間.
7. Lock Time: 等待鎖的時(shí)間.
8.95% of Lock: 95%的慢sql等待鎖時(shí)間.
9.Rows sent: 結(jié)果行統(tǒng)計(jì)數(shù)量, 包括平均, 最小, 最大數(shù)量.
10.Rows examined: 掃描的行數(shù)量.
11.Database: 屬于哪個(gè)數(shù)據(jù)庫.
12.Users: 哪個(gè)用戶,IP, 占到所有用戶執(zhí)行的sql百分比.
13. Query abstract: 抽象后的sql語句.
14. Query sample: sql語句.
MySQL慢查詢分析mysqlsla安裝使用教程完成
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點(diǎn)不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報(bào),并提供相關(guān)證據(jù),一經(jīng)查實(shí),將立刻刪除涉嫌侵權(quán)內(nèi)容。