分类目录归档:MySQL

MySQL

Xtrabackup:MySQL DBA的必备工具

Xtrabackuppercona开发的产品,可以看做是InnoDB Hotbackup的免费替代品。

Percona XtraBackup is the world’s only open-source, free MySQL hot backup software that performs non-blocking backups for InnoDB, XtraDB, and MyISAM databases0. It makes MySQL hot backups for all versions of Percona Server, MySQL, and MariaDB. It performs streaming, compressed, and incremental MySQL backups.

What are the features of Percona XtraBackup?

Here is a short list of XtraBackup features. See the documentation for more.

  • Create hot InnoDB backups without pausing your database
  • Make incremental backups of MySQL
  • Stream compressed MySQL backups to another server
  • Move tables between MySQL servers online
  • Create new MySQL replication slaves easily
  • Backup MySQL without adding load to the server

继续阅读

Autotools to CMake Transition Guide

Introduction

This page shows some common autotools configuration options and how they map to CMake equivalents. It supplements the CMake page that describes how to build MySQL with CMake.

Command Invocation Syntax

The following table shows some common configure invocation syntax and the equivalent CMake commands. The “.” should be replaced with the path to the top-level directory of the source tree if that directory is not your current working directory.

configure Command CMake Command
./configure cmake .
./configure –help cmake . -LH or ccmake .

继续阅读

How to Build MySQL server with CMake

Introduction

This page describes how to build MySQL distributions with CMake. Other resources that you might find useful:


Prerequisites

继续阅读

为什么 MySQL的 binlog-do-db 选项是危险的

为什么 MySQL的 binlog-do-db 选项是危险的.

I see a lot of people filtering replication with binlog-do-db, binlog-ignore-db, replicate-do-db, and replicate-ignore-db. Although there are uses for these, they are dangerous and in my opinion, they are overused. For many cases, there’s a safer alternative.

我发现很多人通过 binlog-do-db, binlog-ignore-db, replicate-do-db 和 replicate-ignore-db 来过滤复制(某些数据库), 尽管有些使用, 但是,在我看来,他们是危险的,并且他们被滥用了. 对于很多的实例,有更安全的替换方案.

The danger is simple: they don’t work the way you think they do. Consider the following scenario: you set binlog-ignore-db to “garbage” so data in the garbage database (which doesn’t exist on the slave) isn’t replicated. (I’ll come back to this in a second, so if you already see the problem, don’t rush to the comment form.)

为什么危险很简单: 他们并不像你想的那样工作. 想象如下的场景: 你设置了 binlog-ignore-db = garbage, 所以 garbage数据库(在slave上不存在这个数据库) 中的数据不会被复制,(待会儿我再讲这个,如果你已经发现问题了,不要急于到评论表单)

Now you do the following:

现在做下面的事情: 继续阅读

mysql driver could not create database instance object(bind dlz)

bind dlz(mysql)运行过程时出现如下错误:

Nov 24 10:35:01 lbbackup named[4155]: starting BIND 9.7.1-P2 -u named -c /usr/local/bind/etc/named.conf.mysql
Nov 24 10:35:01 lbbackup named[4155]: built with ‘–prefix=/usr/local/bind’ ‘–with-dlz-mysql=/usr/local/mysql’ ‘–enable-threads=no
‘ ‘–enable-largefile’
Nov 24 10:35:01 lbbackup named[4155]: using up to 4096 sockets
Nov 24 10:35:01 lbbackup named[4155]: loading configuration from ‘/usr/local/bind/etc/named.conf.mysql’
Nov 24 10:35:01 lbbackup named[4155]: reading built-in trusted keys from file ‘/usr/local/bind/etc/bind.keys’
Nov 24 10:35:01 lbbackup named[4155]: using default UDP/IPv4 port range: [1024, 65535]
Nov 24 10:35:01 lbbackup named[4155]: using default UDP/IPv6 port range: [1024, 65535]
Nov 24 10:35:01 lbbackup named[4155]: listening on IPv4 interface lo, 127.0.0.1#53
Nov 24 10:35:01 lbbackup named[4155]: listening on IPv4 interface eth0, 192.168.146.155#53
Nov 24 10:35:01 lbbackup named[4155]: listening on IPv4 interface eth1, 10.0.0.155#53
Nov 24 10:35:01 lbbackup named[4155]: Required root permissions to open ‘/usr/local/bind/var/run/named.pid’.
Nov 24 10:35:01 lbbackup named[4155]: Please check file and directory permissions or reconfigure the filename.
Nov 24 10:35:01 lbbackup named[4155]: generating session key for dynamic DNS
Nov 24 10:35:01 lbbackup named[4155]: Loading ‘Mysql zone’ using driver mysql
Nov 24 10:35:01 lbbackup named[4155]: Required token $zone$ not found.
Nov 24 10:35:01 lbbackup named[4155]: Could not build all nodes query list
Nov 24 10:35:01 lbbackup named[4155]: mysql driver could not create database instanceobject.
段错误 (core dumped) #加-g -d 1时出现

继续阅读

MySQL 5 Optimization and Tuning Guide

my.cnf: If you have installed or upgraded to MySQL 5, chances are that you may not find the /etc/my.conf file. Don’t worry as there are several sample configuration files that come with MySQL. You can invoke locate my-large.cnf to find the sample configuration file for MySQL 5 on your server. By default, on Red Hat Enterprise, it can be located at the following location.
/usr/share/doc/MySQL-server-standard-5.0.15/my-large.cnf

继续阅读

使用Xtrabackup进行MySQL备份

Percona Xtrabackup – Documentation

Percona XtraBackup is an open-source hot backup utility for MySQL – based servers that doesn’t lock your database during the backup.

It can back up data from InnoDB, XtraDB, and MyISAM tables on MySQL 5.1 [1], 5.5 and 5.6 servers, as well as Percona Server with XtraDB. For a high-level overview of many of its advanced features, including a feature comparison, please see About Percona Xtrabackup.

Whether it is a 24×7 highly loaded server or a low-transaction-volume environment, Percona XtraBackup is designed to make backups a seamless procedure without disrupting the performance of the server in a production environment. Commercial support contracts are available.

Percona XtraBackup is a combination of the xtrabackup C program, and the innobackupex Perl script. The xtrabackup program copies and manipulates InnoDB and XtraDB data files, and the Perl script enables enhanced functionality, such as interacting with a running MySQL server and backing up MyISAM tables. 继续阅读

MySQL Query Cache 小结

最近经常有人问我 MySQL Query Cache 相关的问题,就整理一点 MySQL Query Cache 的内容,以供参考。

顾名思义,MySQL Query Cache 就是用来缓存和 Query 相关的数据的。具体来说,Query Cache 缓存了我们客户端提交给 MySQL 的 SELECT 语句以及该语句的结果集。大概来讲,就是将 SELECT 语句和语句的结果做了一个 HASH 映射关系然后保存在一定的内存区域中。 继续阅读

MYSQL中的unauthenticated user

手册中的解释是:unauthenticated user refers to a thread that has become associated with a client connection but for which authentication of the client user has not yet been done。意即:有一个线程在处理客户端的连接,但是该客户端还没通过用户验证。
原因可能有:
1、        服务器在做DNS反响解析,解决办法有2:
1) 在 hosts 中添加客户端ip,如
192.168.0.1  hostname
2) MySQL启动参数增加一个skip-name-resolve,即不启用DNS反响解析
2、服务器的线程还处于排队状态,因此可以加大 back_log

back_log

MySQL有的主要连接请求的数量。当主MySQL线程在短时间内得到许多连接请求时发挥作用。主线程需要花一些时间(尽管很少)来检查连接并启动一个新线程。值说明MySQL临时停止响应新请求前在短时间内可以堆起多少请求。如果你需要在短时间内允许大量连接,可以增加该数值。

换句话说,该值为“进”TCP/IP连接帧听队列的大小。操作系统有该队列自己的限制值。本手册中Unix listen()系统调用页应有更详细的信息。该变量最大值请查阅OS文档。企图将back_log设置为高于你的操作系统限值是徒劳无益的。

默认情况下:

mysql> show variables like '%BACK_LOG%';  
+---------------+-------+  
| Variable_name | Value |  
+---------------+-------+  
| back_log      | 50    |   
+---------------+-------+ 

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