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. 继续阅读




下手处:登陆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:
  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” .

  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

6、执行 check_nrpe -H ip -c check_mysql_slave 观察其输出。

五款常用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 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.




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



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)


allows delayed replication for MySQL databases.




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


    is very simple. It uses the


    MySQL internal variable, and the




    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.

dlslaved user’s manual

    (a PDF file).



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



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


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



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