MariaDB(MySQL)安装及MySQL慢查询分析mys

2024-04-02 19:55

短信预约 -IT技能 免费直播动态提醒

简介

MariaDB数据库管理系统是MySQL的一个分支，主要由开源社区在维护，采用GPL授权许可。开发这个分支的原因之一是：甲骨文公司收购了MySQL后，有将MySQL闭源的潜在风险，因此社区采用分支的方式来避开这个风险。[4]
MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。在存储引擎方面，10.0.9版起使用XtraDB（名称代号为Aria）来代替MySQL的InnoDB。
MariaDB由MySQL的创始人麦克尔·维德纽斯主导开发，他早前曾以10亿美元的价格，将自己创建的公司MySQL AB卖给了SUN，此后，随着SUN被甲骨文收购，MySQL的所有权也落入Oracle的手中。MariaDB名称来自麦克尔·维德纽斯的女儿玛丽亚（英语：Maria）的名字。
注意：以上内容来自维基百科；
MariaDB官方网站：http://www.mariadb.org/

MariaDB的特性

插件式存储引擎：存储管理器有多种实现版本，彼此间的功能和特性可能略有区别；用户可根据需要灵活选择；存储引擎页称为“表类型”。
(1) 更多的存储引擎

MylSAM:不支持事务

MyISAM -->Aria

InnoDB -->XtraDB;支持事务

(2) 诸多扩展和新特性
(3) 提供了较多的测试组件
(4) truly open source

安装和使用MariaDB

安装环境：

操作系统版本	内核版本
CentOS 7.0	3.10.0-229.el7.x86_64

安装方式：

（1）rpm包：由OS的发行商提供或程序官方提供；

（2）源码包

（3）通用二进制格式

通用二进制格式安装

1、创建mysql系统用户

[root@bogon class="lazy" data-src]# groupadd -r -g 301 mysql

[root@bogon class="lazy" data-src]# useradd -r -g 301 -u 301 mysql

2、下载及解压

[root@bogon class="lazy" data-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 class="lazy" data-src]# tar -xf mariadb-galera-5.5.54-linux-glibc_214-x86_64.tar.gz -C /usr/local/

[root@bogon class="lazy" data-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、创建存放数据的目录，以/data/mysql为例

[root@bogon mysql]# mkdir /data/mysql

[root@bogon mysql]# chown -R mysql.mysql /data/mysql/

5、准备配置文件

[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] #添加如下三个配置参数

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、初始化数据库并启动mysqld服务

[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

至此通用二进制格式安装结束

MySQL慢查询分析mysqlsla安装使用

说明：

操作系统	MySQL版本	MySQL配置文件	MySQL数据目录
CentOS 7.0	mariadb-5.5.54	/etc/my.cnf	/data/mysql

实现目的：开启MySQL慢查询日志功能，安装使用MySQL慢查询分析mysqlsla。

一、具体操作：

1、开启MySQL慢查询功能

[root@bogon mysql]# mysql -u root -p #进入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

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慢查询时间设置默认10秒

MariaDB [(none)]> show variables like 'long_query_time';

+-----------------+-----------+

| Variable_name | Value |

+-----------------+-----------+

| long_query_time | 10.000000 |

+-----------------+-----------+

1 row in set (0.00 sec)

设置慢查询记录超过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慢查询

退出当前mysql控制台重新登录

测试MySQL慢查询

MariaDB [(none)]> select sleep(6);

查看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_|

| 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慢查询状态

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慢查询日志中是否有刚才所执行的select sleep(6)的慢查询日志记录

[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配置文件参数，开启MySQL慢查询功能；

3、修改MySQL配置文件开启慢查询功能

# 编辑，在[mysqld]段下添加以下代码

[root@bogon mysql]# vi /etc/my.cnf

#开启MySQL慢查询功能

slow-query-log = ON

# 设置MySQL慢查询日志路径

slow_query_log_file = /data/mysql/bogon-slow.log

#修改为记录5秒内的查询，默认不设置此参数为记录10秒内的查询

long_query_time = 5

#记录未使用索引的查询

log-queries-not-using-indexes = ON

#保存退出

:wq!

# 重启MySQL服务

service mysqld restart

启动报错：

[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/class="lazy" data-src/

下载mysqlsla包

[root@bogon class="lazy" data-src]# wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz ##这个连接似乎已经挂了

终于在51cto有人分享这个包了，感谢该平台分享

http://down.51cto.com/data/705945

[root@bogon class="lazy" data-src]# tar -xf 51CTO下载-mysqlsla-2.03.tar.gz

[root@bogon class="lazy" data-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分析慢查询日志

#查询记录最多的20个sql语句，并写到select.log中去；

[root@bogon mysqlsla-2.03]# mysqlsla -lt slow --sort t_sum --top 20 /data/mysql/bogon-slow.log >/tmp/select.log

#统计慢查询文件为/data/mysql/bogon-slow.log的所有select的慢查询sql，并显示执行时间最长的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

#统计慢查询文件为/data/mysql/bogon-slow.log的数据库为mydata的所有select和update的慢查询sql，并查询次数最多的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、使用参数说明

1. --log-type (-lt) type logs:通过这个参数来指定log的类型，主要有slow, general, binary, msl, udl,分析slow log时通过指定为slow；

2. --sort:指定使用什么参数来对分析结果进行排序，默认是按照t_sum来进行排序。t_sum按总时间排序， c_sum按总次数排序；

3. --top:显示sql的数量，默认是10,表示取按规则排序的前多少条；

4. --statement-filter (-sf) [+-][TYPE]:过滤sql语句的类型，比如select、update、drop. [TYPE]有SELECT, CREATE, DROP, UPDATE, INSERT，例如"+SELECT,INSERT"，不出现的默认是-，即不包括。

5. --databases db：要处理哪个库的日志：

5、分析后内容参数说明

1. queries total: 总查询次数

2. unique:去重后的sql数量

3. sorted by : 输出报表的内容排序最重大的慢sql统计信息, 包括平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数.

4. Count: sql的执行次数及占总的slow log数量的百分比.

5. Time: 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比.