分类目录归档:MySQL

MySQL

How to setup a slave for replication in 6 simple steps with Percona XtraBackup

Data is, by far, the most valuable part of a system. Having a backup done systematically and available for a rapid recovery in case of failure is admittedly essential to a system. However, it is not common practice because of its costs, infrastructure needed or even the boredom associated to the task. Percona XtraBackup is designed to solve this problem.

You can have almost real-time backups in 6 simple steps by setting up a replication environment with Percona XtraBackup.

Percona XtraBackup is a tool for backing up your data extremely easy and without interruption. It performs “hot backups” on unmodified versions of MySQL servers (5.1, 5.5 and 5.6), as well as MariaDB and Percona Servers. It is a totally free and open source software distributed only under the GPLv2 license. 继续阅读

nagios监控mysql主从复制

nagios监控mysql主从复制

作者:田逸(sery@163.com)
起因:nagios可能监控到mysql服务的运行情况,但确不能监控mysql的主从复制是否正常:有时候,同步已经停止,但管理人员却不知道。

下手处:登陆mysql从服务器,通过执行 mysql> show slave status\G 查看其输出,即可判定主从复制是否正常。下面是某个从服务器的输出:

  1. mysql> show slave status\G
  2. *************************** 1. row ***************************
  3.                Slave_IO_State: Waiting for master to send event
  4.                   Master_Host: 192.168.93.16
  5.                   Master_User: rep1
  6.                   Master_Port: 3306
  7.                 Connect_Retry: 60
  8.               Master_Log_File: mysql-bin.000004
  9.           Read_Master_Log_Pos: 1752541
  10.                Relay_Log_File: hy-mysql3-relay-bin.000088
  11.                 Relay_Log_Pos: 2339
  12.         Relay_Master_Log_File: mysql-bin.000004
  13.              Slave_IO_Running: Yes  
  14.             Slave_SQL_Running: Yes  
  15.               Replicate_Do_DB:

这个输出,最关键处就是”Slave_IO_Running: Yes“和“Slave_SQL_Running: Yes”,这两个值全是”Yes”就表明主从复制正常,否则就是有问题。

操作步骤:
1、在主数据库服务器增加一个用户,给予较低的权限,操作为 mysql > grant Replication client on *.* to  ‘nagios’@’%’ identified by ‘nagios’;
mysql> flush privileges;
2、登陆从服务器验证一下,看是否正常。操作为 # mysql -unagios -pnagios -e “show slave stutas\G”  注意:这个操作是在 shell下操作的!!
3、在从服务器安装 nrpe,然后加入一行”command[check_mysql_slave]=/usr/local/nrpe/libexec/chech_mysql_slave” .
4、编写脚本/usr/local/nrpe/libexec/check_mysql_slave(这是监控其作用的核心),其内容如下:

  1. #!/bin/sh
  2. declare -a  slave_is
  3. slave_is=($(/usr/local/mysql/bin/mysql -unagios -pnagios  -e “show slave status\G”|grep Running |awk ‘{print $2}’))
  4. if [ “${slave_is[0]}” = “Yes” -a “${slave_is[1]}” = “Yes” ]
  5.    then
  6.    echo “OK -slave is running”
  7.    exit 0
  8. else
  9.    echo “Critical -slave is error”
  10.    exit 2
  11. fi

5、手动运行这个脚本,观察输出。
6、执行 check_nrpe -H ip -c check_mysql_slave 观察其输出。
7、修改nagios,并测试。
8、功能测试(略)

五款常用mysql slow log分析工具的比较

mysql slow log 是用来记录执行时间较长(超过long_query_time秒)的sql的一种日志工具.

启用 slow log
有两种启用方式:
1, 在my.cnf 里 通过 log-slow-queries[=file_name]
2, 在mysqld进程启动时,指定–log-slow-queries[=file_name]选项

需要比较的五款常用工具

mysqldumpslow, mysqlsla, myprofi, mysql-explain-slow-log, mysqllogfilter 继续阅读

mysqlsla

mysqlsla

mysqlsla parses, filters, analyzes and sorts MySQL slow, general, binary and microslow patched logs in order to create a customizable report of the queries and their meta-property values.

Since these reports are customizable, they can be used for human consumption or be fed into other scripts to further analyze the queries. For example, to profile with mk-query-profiler (a script from Baron Schwartz’s Maatkit) every unique SELECT statement using database foo from a slow log:

         mysqlsla -lt slow slow.log -R print-unique -mf "db=foo" -sf "+SELECT" | \
         mk-query-profiler -separate -database foo

In brief, mysqlsla is a liaison allowing other scripts easy access to queries from a MySQL log. For a quick introduction to what mysqlsla is capable of doing, take a glance at the guide.

The mysqlsla Library

» Guide: Covers installation, basic usage, reports, special topics and advancing

» Documentation: Complete list of command line options, limitations, future features and bugs

» Filters: Overview of mysqlsla v2 log filters

» Reports: Creating and customizing mysqlsla v2 standard reports

» Replays: Working with mysqlsla v2 log replays

» User-Defined Logs (UDL): Creating mysqlsla v2 and MySQL::Log::ParseFilter user-defined logs

Download mysqlsla

Current version: mysqlsla-2.03 (Changes)

  • Installer + man page: mysqlsla-2.03.tar.gz
  • Script only: wget http://hackmysql.com/scripts/mysqlsla

v1.x Retired

Looking for mysqlsla v1.7a or 1.8-DEBUG? The v1 branch has been retired and is no longer supported. The older versions can still be accessed at http://hackmysql.com/archive/.

继续阅读

Spock Proxy – a proxy for MySQL horizontal partitioning

Spock Proxy supports range-based horizontal paritioning of a large MySQL database. The proxy intercepts SQL queries from the client, sends queries to the correct databases based on how the database is partitioned, then aggregates the results from each database and returns them to the client as a regular MySQL result set.

Design Goal

Spock Proxy’s original mandate was to horizontally partition Spock‘s existing monolithic databases into multiple shards to improve performance and scalability.

Whereas many web sites build sharding logic into the application, Spock is built on Rails and ActiveRecord. We love Rails, but it doesn’t make partitioning easy. So, we preferred to partition at the MySQL level. The application sends a SQL statement to the proxy. The proxy then sends the statement to one or more databases based the value of a column in a predefined column. Finally the proxy returns a merged result.

We target applications that are interested in partitioning but want to avoid large changes to their existing application code.

http://spockproxy.sourceforge.net/

pt-query-digest

NAME

pt-query-digest – Analyze MySQL queries from logs, processlist, and tcpdump.

SYNOPSIS

Usage

pt-query-digest [OPTIONS] [FILES] [DSN]

pt-query-digest analyzes MySQL queries from slow, general, and binary log files. It can also analyze queries from SHOW PROCESSLIST and MySQL protocol data from tcpdump. By default, queries are grouped by fingerprint and reported in descending order of query time (i.e. the slowest queries first). If no FILES are given, the tool reads STDIN. The optional DSN is used for certain options like –since and –until.

继续阅读

mysql slave延时同步

dlslaved 0.1

(Delayed Slave Daemon for MySQL)

dlslaved

allows delayed replication for MySQL databases.

 

The

dlslaved

syntax is really simple :

-> dlslaved -h ipAddress -u username -p password -P portNumber

  • 11/06/2007 : initial release

Well, it’s done. I finally put the source code on Internet. So, be gentle. It’s my first unix program.

 

    The internal architecture of

dlslaved

    is very simple. It uses the

SecondsBehindMaster

    MySQL internal variable, and the

STOP

    and

START

    slave command to try to have 7200 seconds (a constant in the program) constantly behind the master.

 

    Try it, use it, send back errors and source modifications.
    It compiles under Linux and Solaris (x86 and Sparc) using gcc.
    Download

dlslaved user’s manual

    (a PDF file).

 

    Download

dlslaved source code

    for Linux and Solaris (i think most Unix like systems). To compile, you need MySQL binaries and libraries to be install in

/usr/local/mysql

    directory.

Contact me at rodolphe.jouannet(at)free.fr for support or questions. 继续阅读

mysqlreport使用指南

进行MySQL的配置优化,首先必须找出MySQL的性能瓶颈所在;而SHOW STATUS输出的报告正是用来计算性能瓶颈的参考数据。mysqlreport不像SHOW STATUS那样简单的罗列数据,而是对这些参考数据加以融合计算,整理成一个个优化参考点,然后DBA就可以根据这个优化参考点的值以及该点的衡量标准,进行对应调整。这篇文章既不分析mysqlreport的报告含义,也不说明优化参考点的计算公式和原理,只简单描述使用方法。后面再逐次深入分析。 继续阅读

MySQL在大型网站的应用架构演变

可扩展性
架构的可扩展性往往和并发是息息相关,没有并发的增长,也就没有必要做高可扩展性的架构,这里对可扩展性进行简单介绍一下,
常用的扩展手段有以下两种:

  • Scale-up :  纵向扩展,通过替换为更好的机器和资源来实现伸缩,提升服务能力
  • Scale-out : 横向扩展,  通过加节点(机器)来实现伸缩,提升服务能力

对于互联网的高并发应用来说,无疑Scale out才是出路,通过纵向的买更高端的机器一直是我们所避讳的问题,也不是长久之计,
在scale out的理论下,可扩展性的理想状态是什么? 继续阅读

MHA for MySQL: Master High Availability Manager and tools for MySQL

A primary objective of MHA is automating master failover and slave promotion within short (usually 10-30 seconds) downtime, without suffering from replication consistency problems, without spending money for lots of new servers, without performance penalty, without complexity (easy-to-install), and without changing existing deployments.

继续阅读