Shulou(Shulou.com)06/01 Report--

Brief introduction

MariaDB database management system is a branch of MySQL, which is mainly maintained by the open source community and licensed by GPL. One of the reasons for developing this branch is that after Oracle acquired MySQL, there is a potential risk of shutting down MySQL, so the community uses a branching approach to avoid this risk. [4]

The goal of MariaDB is to be fully compatible with MySQL, including API and the command line, making it an easy replacement for MySQL. In terms of the storage engine, version 10.0.9 has used XtraDB (code name Aria) instead of MySQL's InnoDB.

MariaDB was developed by Michael Vidnius, the founder of MySQL, who had earlier sold his company, MySQL AB, to SUN for $1 billion, and then MySQL was owned by Oracle as SUN was acquired by Oracle. The name MariaDB comes from the name of Maria (English: Maria), daughter of Michael Vidnius.

Note: the above content is from Wikipedia

MariaDB official website: http://www.mariadb.org/

Characteristics of MariaDB

Plug-in storage engine: there are multiple implementation versions of the storage manager, and their functions and features may be slightly different; users can choose flexibly according to their needs; the storage engine page is called "table type".

(1) more storage engines

MylSAM: transactions are not supported

MyISAM-- > Aria

InnoDB-- > XtraDB; supports transactions

(2) many extensions and new features

(3) more test components are provided.

(4) truly open source

Install and use MariaDB

Installation environment:

Operating system version kernel version CentOS 7.03.10.0-229.el7.x86_64

Installation method:

(1) rpm package: provided by the publisher of OS or officially provided by the program

(2) Source package

(3) Universal binary format

Universal binary format installation

1. Create mysql system users

[root@bogon src] # groupadd-r-g 301 mysql

[root@bogon src] # useradd-r-g 301-u 301 mysql

2. Download and decompress

[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. All file owners and groups after modifying the decompressed directory

[root@bogon local] # cd mysql/

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

4. Create a directory to store data, and take / data/mysql as an example

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

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

5. Prepare the configuration file

[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 Editor

[mysqld] # add the following three configuration parameters

Skip_name_resolve = ON

Datadir = / data/mysql

Innodb_file_per_table = ON

Mysql profile lookup order:

/ etc/my.cnf and then / etc/mysql/my.cnf and then-- default.extra-file=/PATH/TO/COF_FILE last ~ / .my.cnf

6. Provide startup feet

[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

Initialize the database and start the mysqld service

[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 10 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-000000000000001

Root 36960 36136 0 22:56 pts/2 00:00:00 grep-color=auto mysqld

8. Configuration after installation

[root@bogon mysql] # vi / etc/profile.d/mariadb.sh # Editor

Export PATH=/usr/local/mysql/bin:$PATH

[root@bogon mysql] # source / etc/profile.d/mariadb.sh

At this point, the installation of the universal binary format is over.

MySQL slow query analysis mysqlsla installation instructions: operating system MySQL version MySQL configuration file MySQL data directory CentOS 7.0mariadb-5.5.54/etc/my.cnf/data/mysql

Purpose: to enable the MySQL slow query log function and install the mysqlsla using MySQL slow query analysis.

First, the specific operation:

1. Enable MySQL slow query function

[root@bogon mysql] # mysql-u root-p # enter the mysql console

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)] >

View the mysql slow query feature

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.01sec)

Enable MySQL slow query function

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 |

View MySQL slow query time setting 10 seconds by default

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

+-+ +

| | Variable_name | Value |

+-+ +

| | long_query_time | 10.000000 | |

+-+ +

1 row in set (0.00 sec)

Set slow query records for more than 5 seconds

MariaDB [(none)] > set global long_query_time=5

Query OK, 0 rows affected (0.00 sec)

Check it out:

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. Test MySQL slow query

Log out of the current mysql console and log in again

Test MySQL slow query

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

View MySQL slow query log path

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)

View MySQL slow query status

MariaDB [(none)] > show global status like'% slow%'

+-+ +

| | Variable_name | Value |

+-+ +

| | Slow_launch_threads | 0 | |

| | Slow_queries | 1 | |

+-+ +

2 rows in set (0.00 sec)

Exit the MySQL console

MariaDB [(none)] > exit

# check whether there is a slow query log record of select sleep (6) just executed in MySQL slow query log

[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)

Note: you can also enable MySQL slow query by modifying the parameters of MySQL configuration file.

3. Modify the MySQL configuration file to enable the slow query function

# Edit and add the following code under the [mysqld] paragraph

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

# enable MySQL slow query function

Slow-query-log = ON

# set MySQL slow query log path

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

# modified to record a query within 5 seconds. By default, this parameter is not set to record a query within 10 seconds

Long_query_time = 5

# record queries that do not use indexes

Log-queries-not-using-indexes = ON

# Save exit

: wq!

# restart MySQL service

Service mysqld restart

Start the error message:

[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.

Solution:

[mysqld_safe]

# log-error=/var/log/mariadb/mariadb.log comment this line

Pid-file=/var/run/mariadb/mariadb.pid

Please look forward to the deployment of mysqlsla tomorrow.

Second, install MySQL slow query analysis tool mysqlsla

1. Install the dependency package

[root@bogon ~] # yum install wget perl perl-DBI perl-DBD-MySQL mysql perl-Time-HiRes perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker

2. Install mysqlsla

Storage package directory

[root@bogon ~] # cd / usr/local/src/

Download the mysqlsla package

[root@bogon src] # wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz # # this connection seems to have hung up

Finally, someone shares this package on 51cto. Thank you for sharing it on this platform.

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

[root@bogon src] # tar-xf 51CTO download-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. Use mysqlsla to analyze slow query logs

# query the 20 most recorded sql statements and write them to select.log

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

# count the slow query sql of all select whose slow query file is / data/mysql/bogon-slow.log, show the sql with the longest execution time, and write it to sql_select.log; [root@bogon mysqlsla-2.03] # mysqlsla- lt slow-sf "+ select"-top 100 / data/mysql/bogon-slow.log > / tmp/sql_select.log

# Statistics: the database with slow query file / data/mysql/bogon-slow.log is all select of mydata and slow query sql of update, and the 100 sql with the most query times are written to 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. Description of usage parameters

1.-- log-type (- lt) type logs: use this parameter to specify the type of log, such as slow, general, binary, msl, udl, and slow when parsing slow log

2.-- sort: specify what parameters are used to sort the analysis results, which is sorted by t_sum by default. T_sum is sorted by total time, c_sum by total number of times

3.-- top: displays the number of sql. The default is 10, indicating the number of entries sorted by rules.

4.-- statement-filter (- sf) [+ -] [TYPE]: filter the types of sql statements, such as select, update, drop. [TYPE] there are SELECT, CREATE, DROP, UPDATE, INSERT, such as "+ SELECT,INSERT". If it does not appear, the default is -, that is, not included.

5.-- databases db: log of which library to process:

5. Description of content parameters after analysis

1. Queries total: total number of queries

2. Unique: the number of sql after weight removal

3. Sorted by: output the slow sql statistics with the most significant ranking of the contents of the report, including average execution time, waiting lock time, total number of resulting rows, and total number of rows scanned.

4. Count: the number of times sql is executed and its percentage in the total number of slow log.

5. Time: execution time, including total time, average time, minimum time, maximum time, as a percentage of total slow sql time.

6. 95% of Time: removal of the fastest and slowest sql, coverage accounts for 95% of the sql execution time.

7. Lock Time: the time to wait for the lock.

8.95% of Lock: 95% slow sql waiting time for lock.

9.Rows sent: the number of resulting rows, including average, minimum, and maximum.

10.Rows examined: the number of rows scanned.

11.Database: which database does it belong to?

12.Users: which user, IP, accounts for the percentage of sql executed by all users.

13. Query abstract: abstract sql statement.

14. Query sample: sql statement.

MySQL slow query Analysis mysqlsla installation is completed using tutorials

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.