存档

‘SQL数据库管理’ 分类的存档

sequelpro:一款Mac平台的MySQL管理工具

2016年5月20日 评论已被关闭

sequelpro:一款Mac平台的MySQL管理工具

http://hao.jobbole.com/sequelpro/

这是一款Mac平台的MySQL管理工具,是一个与phpMyAdmin类似的MySQL管理工具。它是由Cocoa和面对对象的C(Mac OSX)编写的。允许你编辑数据库,表格(字段和索引)和列,执行个性化查找和导入导出数据。


功能特点:

 

  • 支持多达30种编码方式
  • 快速导出/导入、备份/还原数据库的SQL/CSV文件
  • 最高版本支持MySQL 5.6
  • 内置强大的数据库查询编辑器,有自定义查询中的自动语句高亮功能,可自定义字体,自定义查询占位符.
  • 允许你编辑数据库,表格(字段和索引)和列,执行个性化查找和导入导出数据
  • 自动补全关键词,表名,域名
  • 自动段落化查询命令
  • IP直连数据库
  • 表触发机制
  • 服务器运行监控Query Favourites for SQL reuse
  • 包括检索功能在内的全表管理设计
  • 快速在Mac上架设本地MySQL数据库
  • 在MAMP/XAMP架构上连接数据库
  • SSH连接模式

github网址https://github.com/sequelpro/sequelpro,官网http://sequelpro.com/

Mac 开机启动MySQL/MongoDB/Redis 等服务

2016年5月14日 评论已被关闭

Mac 开机启动 等服务

http://www.jianshu.com/p/e73978416920

在Mac上我们使用[homebrew]包管理工具(http://brew.sh/index_zh-cn.html)来安装和管理开发工具包,例如:mysql、php、redis。只需要一个命令

brew install mysql

它会将所有的包安装到/usr/local/Cellar/目录下,并将文件软连接到/usr/local/

安装完成后你需要到/usr/local/Cellar/mysql/5.6.26/bin下找到mysql来启动。但是如果关掉终端,mysql服务也会随之关闭,这样就始终占据了一个终端窗口。

Mac OS 的开机启动方式

launchd 是 Mac OS 下用于初始化系统环境的关键进程,它是内核装载成功之后在OS环境下启动的第一个进程。采用这种方式来配置自启动项很简单,只需要一个plist文件,该plist文件存在的目录有:

  • LaunchDaemons ~/Library/LaunchDaemons
    用户登陆前运行 plist(程序)
  • LaunchAgents ~/Library/LaunchAgents
    用户登录后运行相应的 plist(程序)

你需要.plist文件来指定需要开机启动的程序。
以下是开机启动的.plist配置文件的示例:

<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE plist PUBLIC "-//Apple//DTD PLIST 1.0//EN""http://www.apple.com/DTDs/PropertyList-1.0.dtd">
<plist version="1.0">
    <dict>
        <key>Label</key>
        <string>org.mongodb.mongod</string>
        <key>ProgramArguments</key>
        <array>
            <string>/usr/local/mongodb-2.0.3/bin/mongod</string>
            <string>run</string>
            <string>--config</string>
            <string>/usr/local/mongodb-2.0.3/mongod.conf</string>
        </array>
        <key>RunAtLoad</key>
        <true/>
        <key>KeepAlive</key>
        <true/>
        <key>WorkingDirectory</key>
        <string>/usr/local/mongodb-2.0.3</string>
        <key>StandardErrorPath</key>
        <string>/usr/local/mongodb-2.0.3/log/error.log</string>
        <key>StandardOutPath</key>
        <string>/usr/local/mongodb-2.0.3/log/mongo.log</string>
    </dict>
</plist>

如何编写.plist文件

brew安装的时候已经为你写好.plist文件。你只需要运行brew info mysql来查看帮助信息。

brew info mysql

<script type=”text/javascript” src=”https://asciinema.org/a/44624.js” id=”asciicast-44624″ async></script>
此时终端会显示如下信息:

==> Caveats
We've installed your MySQL database without a root password. To secure it run:                                         
    mysql_secure_installation                                                                                          

To connect run:                                                                                                        
    mysql -uroot                                                                                                       

To have launchd start mysql at login:                                                                                  
  ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents                                                          
Then to load mysql now:                                                                                                
  launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist                                                      
Or, if you don't want/need launchctl, you can just run:                                                                
  mysql.server start

按照提示,执行ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents将mysql加入到登陆启动列表

ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents

或者 立即启动mysql launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

launchctl load ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

启动之后就可以关闭终端了,mysql会在后台运行。

 

文/lixiaohao(简书作者)
原文链接:http://www.jianshu.com/p/e73978416920
著作权归作者所有,转载请联系作者获得授权,并标注“简书作者”。

mysql分页的limit优化

2015年9月20日 评论已被关闭

mysql分页的limit优化

http://www.cnblogs.com/xuxiang/p/3983973.html

1、很多新人都会很纳闷,为什么我建了索引使用分页还是这么卡。好,现在让我们一步一步去找原因吧。

首先limit本身跟索引没有直接关系。

先建一张商品sku表

create table goods_sku
(
id int(10) unsigned not null auto_increment comment ‘自增ID’,
goods_id varchar(20) not null comment ‘商品id’,
sale_status tinyint comment ‘上下架状态(0下架,1上架)’,
added_time timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT ‘上家日期’,
drop_time timestamp NOT NULL DEFAULT ‘0000-00-00 00:00:00’ COMMENT ‘下架时间’,
`is_del` tinyint(4) NOT NULL DEFAULT ‘0’ COMMENT ‘删除标记(0未删除 1删除)’,
KEY `index_goods_id` (`goods_id`),
KEY `index_sale_status` (`sale_status`),
KEY `index_added_time` (`added_time`),
primary key (id)
) comment = ‘商品SKU表’ ENGINE=InnoDB DEFAULT CHARSET=utf8;

mysql> explain select * from goods_sku limit 0,10;
+—-+————-+———–+——+—————+——+———+——+——–+——-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+———–+——+—————+——+———+——+——–+——-+
| 1 | SIMPLE | goods_sku | ALL | NULL | NULL | NULL | NULL | 107950 | |
+—-+————-+———–+——+—————+——+———+——+——–+——-+
1 row in set (0.00 sec)

ps: 因为没走索引,所以进行了全表扫描,现在是10万条数据,试想一下100万的情况下是怎么样。这么简单的一条sql就会让你机器卡爆。我现在就想一条数据,使用索引看看

mysql> explain select * from goods_sku where sale_status=1 limit 0,10;
+—-+————-+———–+——+——————-+——————-+———+——-+—
—+————-+
| id | select_type | table     | type | possible_keys     | key               | key_len | ref   | ro
ws | Extra       |
+—-+————-+———–+——+——————-+——————-+———+——-+—
—+————-+
|  1 | SIMPLE      | goods_sku | ref  | index_sale_status | index_sale_status | 2       | const | 46
25 | Using where |
+—-+————-+———–+——+——————-+——————-+———+——-+—
—+————-+
1 row in set (0.10 sec)

虽然走了索引,但是受影响的条数还是4000多条

mysql> explain select * from goods_sku order by id desc limit 0,10;
+—-+————-+———–+——-+—————+———+———+——+——+——-+
| id | select_type | table     | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+—-+————-+———–+——-+—————+———+———+——+——+——-+
|  1 | SIMPLE      | goods_sku | index | NULL          | PRIMARY | 8       | NULL |   10 |       |
+—-+————-+———–+——-+—————+———+———+——+——+——-+
1 row in set (0.00 sec)

这个受影响的条件为10条,看来limit和order by 联用可以真正限制输出的数量,但是order by 后面的字段一定是建了索引的

通过上面我们可能得出一个结论,limit前加一个order by 就可以,但事实是否如此呢,再看一个例子

mysql> explain select * from brand order by english_name limit 0,10;
+—-+————-+——-+——+—————+——+———+——+——+—————-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+—-+————-+——-+——+—————+——+———+——+——+—————-+
| 1 | SIMPLE | brand | ALL | NULL | NULL | NULL | NULL | 581 | Using filesort |
+—-+————-+——-+——+—————+——+———+——+——+—————-+
1 row in set (0.00 sec)

注:type为all,天呀,虽然english_name建了索引,再了order by竟然没走索引,这跟上面所说的加个order by就走索引不是矛盾吗。我们再看一个例子

mysql> explain SELECT english_name FROM brand ORDER BY english_name LIMIT 0,10;
+—-+————-+——-+——-+—————+——————–+———+——+——+—
———-+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Ex
tra |
+—-+————-+——-+——-+—————+——————–+———+——+——+—
———-+
| 1 | SIMPLE | brand | index | NULL | index_english_name | 302 | NULL | 10 | Us
ing index |
+—-+————-+——-+——-+—————+——————–+———+——+——+—
———-+

注: 虽然*包含english_name,但加和不加是不一样的,尤其后面加了order by,由此可知,order by 的东西,前面select一定要出现,除非是主鍵id

分类: SQL数据库管理 标签:

修改ORACLE日志库满时临时文件系统/dev/shm大小的方法

2015年9月2日 评论已被关闭

修改ORACLE日志库满时临时文件系统/dev/shm大小的方法
http://blog.sina.com.cn/s/blog_5372558f0100via3.html
oracle日志满的情况下检查:
sqlplus sys/pass
SQL> archive log list;
Database log mode Archive Mode
Automatic archival Enabled
Archive destination /dev/shm/log2
Oldest online log sequence 1034
Next log sequence to archive 1037
Current log sequence 1037
df -h 显示/dev/shm已满。
为了将/dev/shm的大小从默认(物理内在的50%)大小修改为4GB,修改/etc/fstab的这行:默认的:
none /dev/shm tmpfs defaults 0 0
改成:
none /dev/shm tmpfs defaults,size=4G 0 0
size参数也可以用G作单位:size=1G。
重新mount /dev/shm使之生效:
# mount -o remount /dev/shm
马上可以用”df -h”命令检查变化。

分类: SQL数据库管理 标签:

Starting MySQL.Manager of pid-file quit without updating file.[FAILED]的解决方法

2014年12月6日 评论已被关闭

Starting MySQL.Manager of pid-file quit without updating file.[FAILED]的解决方法
http://www.jb51.net/article/22052.htm
因为硬盘满了,Starting MySQL.Manager of pid-file quit without updating file.[FAILED]
因为硬盘满了,mysql启动不起来了。

登录上去看了一下,发现原因。

删除mysql的日志文件,重启mysql发现错误:Starting MySQL.Manager of pid-file quit without updating file.[FAILED]

网上有不少这个原因的解释,但是都不是我想说的。我要说的原因其实很白痴:data/mysql-bin.index没有删除,data/mysql-bin.index是存放日志文件索引的文件,只删除了日志文件而没有对日志的索引文件做处理显然是不行的。

删除data/mysql-bin.index文件,再service mysqld start就可以了。

好久没有写日志了,我还没有忘记这里。

分类: SQL数据库管理 标签:

MySQL初级使用指南

2014年10月30日 评论已被关闭

MySQL初级使用指南
http://www.ha97.com/872.html
标签: mysql,初级,指南
一、连接MYSQL

格式: mysql -h主机地址 -u用户名 -p用户密码

1、例1:连接到本机上的MYSQL。

首先在打开终端窗口,然后进入目录 mysqlbin,再键入命令mysql -u root -p,回车后提示你输密码,如果刚安装好MYSQL,超级用户root是没有密码的,故直接回车即可进入到MYSQL中了,MYSQL的提示符 是:mysql>
2、例2:连接到远程主机上的MYSQL。假设远程主机的IP为:110.110.110.110,用户名为root,密码为abcd123。则键入以下 命令:
mysql -h110.110.110.110 -uroot -pabcd123
(注:u与root可以不用加空格,其它也一样)

3、退出MYSQL命令: exit或quit (回车)

二、修改密码

格式:mysqladmin -u用户名 -p旧密码 password 新密码

1、例1:给root加个密码123456。首先在终端下进入目录mysqlbin,然后键入以下命令

mysqladmin -uroot password ’123456′

注:因为开始时root没有密码,所以-p旧密码一项就可以省略了。

2、例2:再将root的密码改为abcdef。

mysqladmin -uroot -p123456 password abcdef

三、增加新用户(注意:和上面不同,下面的因为是MYSQL环境中的命令,所以后面都带一个分号作为命令结束符)

格式:grant select on 数据库.* to 用户名@登录主机 identified by “密码”

例1、增加一个用户test1密码为abc,让他可以在任何主机上登录,并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入 MYSQL,然后 键入以下命令:
grant select,insert,update,delete on *.* to test1@”%” Identified by “abc”;
但例1增加的用户是十分危险的,你想如某个人知道test1的密码,那么他就可以在internet上的任何一台电脑上登录你的mysql数据库并对你的 数据可以为所欲为了,解决办法见例2。

例2、增加一个用户test2密码为abc,让他只可以在localhost上登录,并可以对数据库mydb进行查询、插入、修改、删除的操作 (localhost指本地主机,即MYSQL数据库所在的那台主机),这样用户即使用知道test2的密码,他也无法从internet 上直接访问数据库,只能通过MYSQL主机上的web页来访问了。

grant select,insert,update,delete on mydb.* to test2@localhost identified by “abc”;

如果你不想test2有密码,可以再打一个命令将密码消掉。

grant select,insert,update,delete on mydb.* to test2@localhost identified by “”;

下面来看看MYSQL中有关数据库方面的操作。注意:必须首先登录到MYSQL中,以下操作都是在MYSQL的提示符下进行的,而且每个命令以分号结束。

四、操作技巧

1、如果你打命令时,回车后发现忘记加分号,你无须重打一遍命令,只要打个分号回车就可以了。也就是说你可以把一个完整的命令分成几行来打,完后用分号作 结束标志就OK。

2、你可以使用光标上下键调出以前的命令。但以前我用过的一个MYSQL旧版本不支持。我现在用的是mysql-3.23.27-beta- win。

五、显示命令

1、显示数据库列表。
show databases;
刚开始时才两个数据库:mysql和test。mysql库很重要它里面有MYSQL的系统信息,我们改密码和新增用户,实际上就是用这个库进行操作。

2、显示库中的数据表:
use mysql; //打开库,学过FOXBASE的一定不会陌生吧
show tables;

3、显示数据表的结构:
describe 表名;

4、建库:
create database 库名;

5、建表:
use 库名;
create table 表名 (字段设定列表);

6、删库和删表:
drop database 库名;
drop table 表名;

7、将表中记录清空:
delete from 表名;

8、显示表中的记录:
select * from 表名;

六、一个建库和建表以及插入数据的实例

drop database if exists school; //如果存在SCHOOL则删除
create database school; //建立库SCHOOL
use school; //打开库SCHOOL
create table teacher //建立表TEACHER
(
id int(3) auto_increment not null primary key,
name char(10) not null,
address varchar(50) default ‘深圳’,
year date
); //建表结束
//以下为插入字段
insert into teacher values(”,’glchengang’,’深圳一中’,’1976-10-10′);
insert into teacher values(”,’jack’,’深圳一中’,’1975-12-23′);
注:在建表中:
(1)将ID设为长度为3的数字字段:int(3)并让它每个记录自动加一:auto_increment并不能为空:not null而且让他成为主字段primary key。
(2)将NAME设为长度为10的字符字段。
(3)将ADDRESS设为长度50的字符字段,而且缺省值为深圳。varchar和char有什么区别呢,只有等以后的文章再说了。
(4)将YEAR设为日期字段。

如果你在mysql提示符键入上面的命令也可以,但不方便调试。你可以将以上命令原样写入一个文本文件中假设为school.sql,然后复制到 c: 下,并在终端状态进入目录mysqlbin,然后键入以下命令:
mysql -uroot -p密码 < c:school.sql
如果成功,空出一行无任何显示;如有错误,会有提示。(以上命令已经调试,你只要将//的注释去掉即可使用)。

七、将文本数据转到数据库中

1、文本数据应符合的格式:字段数据之间用tab键隔开,null值用n来代替.
例:
3 rose 深圳二中 1976-10-10
4 mike 深圳一中 1975-12-23

2、数据传入命令 load data local infile “文件名” into table 表名;
注意:你最好将文件复制到mysqlbin目录下,并且要先用use命令打表所在的库 。

八、备份数据库:(命令在终端的mysqlbin目录下执行)

mysqldump –opt school>school.bbb
注释:将数据库school备份到school.bbb文件,school.bbb是一个文本文件,文件名任取,打开看看你会有新发现。

后记:其实MYSQL的对数据库的操作与其它的SQL类数据库大同小异,最好找本SQL的书看看。在这里只介绍一些基本的。最好的MYSQL教程”MYSQL中文参考手册”。

Mysql命令大全

2014年10月21日 评论已被关闭

Mysql命令大全

http://blog.csdn.net/zlzlei/article/details/7736421

Mysql官方指导手册(包括sql语法):
http://dev.mysql.com/doc/refman/5.1/zh/index.html

Mysql常用经典语句:
http://www.cnblogs.com/see7di/archive/2010/04/27/2239909.html

下面是常用的一些命令

第一招、mysql服务的启动和停止

net stop mysql
net start mysql

第二招、登陆mysql

语法如下: mysql -u用户名 -p用户密码

键入命令mysql -uroot -p, 回车后提示你输入密码,输入12345,然后回车即可进入到mysql中了,mysql的提示符是:

mysql>

注意,如果是连接到另外的机器上,则需要加入一个参数-h机器IP

mysql -h [ip] -P [port] -u root -p

第三招、增加新用户

格式:grant 权限 on 数据库.* to 用户名@登录主机 identified by “密码”

如,增加一个用户user1密码为password1,让其可以在本机上登录, 并对所有数据库有查询、插入、修改、删除的权限。首先用以root用户连入mysql,然后键入以下命令:

grant select,insert,update,delete on *.* to user1@localhost Identified by “password1”;

如果希望该用户能够在任何机器上登陆mysql,则将localhost改为”%”。

如果你不想user1有密码,可以再打一个命令将密码去掉。

grant select,insert,update,delete on mydb.* to user1@localhost identified by “”;

第四招: 操作数据库

登录到mysql中,然后在mysql的提示符下运行下列命令,每个命令以分号结束。

1、 显示数据库列表。

show databases;

缺省有两个数据库:mysql和test。 mysql库存放着mysql的系统和用户权限信息,我们改密码和新增用户,实际上就是对这个库进行操作。

2、 显示库中的数据表:

use mysql;

show tables;

3、 显示数据表的结构:

describe 表名;

4、 建库与删库:

create database 库名;

drop database 库名;

5、 建表:

use 库名;

create table 表名(字段列表);

drop table 表名;

6、 清空表中记录:

delete from 表名;

7、 显示表中的记录:

select * from 表名;

第五招、导出和导入数据

1. 导出数据:

mysqldump –opt test > mysql.test

即将数据库test数据库导出到mysql.test文件,后者是一个文本文件

如:mysqldump -u root -p123456 –databases dbname > mysql.dbname

就是把数据库dbname导出到文件mysql.dbname中。

2. 导入数据:

mysqlimport -u root -p123456 < mysql.dbname。

不用解释了吧。

3. 将文本数据导入数据库:

文本数据的字段数据之间用tab键隔开。

use test;

load data local infile “文件名” into table 表名;

杀掉MySQL连接线程的利器之pt-kill

2014年10月21日 评论已被关闭

杀掉MySQL连接线程的利器之pt-kill
http://blog.chinaunix.net/uid-16844903-id-4442030.html
如何每10秒检查一次,杀死指定用户超过100秒的查询?
pt-kill \
–no-version-check \
–host 127.0.0.1 –port 3306 –user ‘xxxxxx’ –password ‘xxxxxx’ \
–charset utf8 \
–match-command Query \
–match-user 指定的用户名 \
–busy-time 100 \
–kill \
–victims all \
–interval 10 \
–print
常用参数说明
no-version-check
不最新检查版本
host
连接数据库的地址
port
连接数据库的端口
user
连接数据库的用户名
passowrd
连接数据库的密码
charset
指定字符集
match-command
指定杀死的查询类型
match-user
指定杀死的用户名,即杀死该用户的查询
busy-time
指定杀死超过多少秒的查询
kill
执行kill命令
victims
表示从匹配的结果中选择,类似SQL中的where部分,all是全部的查询
interal
每隔多少秒检查一次
print
把kill的查询打印出来

How to setup a slave for replication

2014年10月21日 评论已被关闭

How to setup a slave for replication

http://blog.chinaunix.net/uid-16844903-id-4032897.html

注: 官方文档已经有详细的说明,在此只是把常用命令记录一下,方便使用.

  • 克隆一个slave
    • 落在本地再传输
    • 直接以”流”的方式传到远程

克隆一个slave

落在本地再传输

  1. 在一个slave上面,做一个全备
    sudo innobackupex --defaults-file=/usr/local/mysql/multi/3306/etc/my.cnf --user=username --password=password \
                      --host=127.0.0.1 --port=3306 --use-memory=2G --slave-info /usr/local/backupdb/
    
  2. 根据logfile恢复数据
    sudo innobackupex --apply-log --use-memory=2G /usr/local/backupdb/tmp/2013-12-10_17-09-43/
    
    # 重命名
    mv /usr/local/backupdb/tmp/2013-12-10_17-09-43/ data
    
  3. 转移到新的slave机上面
    通过rsync或者scp命令,传完之后,修改目录的属主属组

    chown mysql:mysql data
    
  4. 在master上面增加一个新slave的授权
    GRANT REPLICATION SLAVE ON *.*  TO 'repl'@'$newslaveip' IDENTIFIED BY '$slavepass';
    
  5. 在新slave上面启动mysql,配置主从关系
    注意: backup-my.cnf文件中的配置参数,要跟master一致.

    CHANGE MASTER TO
                       MASTER_HOST='$masterip',
                       MASTER_USER='repl',
                       MASTER_PASSWORD='$slavepass',
                       MASTER_LOG_FILE='TheMaster-bin.000001',
                       MASTER_LOG_POS=481;
    
    START SLAVE;
    SHOW SLAVE STATUS\G
    

直接以”流”的方式传到远程

  1. 在新slave上面用nc起一个接收数据的端口
    nc -l 9999 | cat - > ./backup_20131210.tar
    
  2. 限速10Mbytes/s
    注意: 一定要限速,否则千兆的网卡也会打满

    sudo innobackupex --defaults-file=/etc/my.cnf --user=username --password=password --host=127.0.0.1 --port=3306 \
                      --use-memory=2G --slave-info --stream=tar ./ | pv -q -L10m |nc $newslaveip 9999
    
    
  3. 在新的slave上面解压
    mkdir data_tmp
    
    tar ixvf backup_20131210.tar -C data_tmp
    
  4. 根据logfile恢复数据
    sudo innobackupex --apply-log --use-memory=2G data_tmp/
    
  5. 修改属主属组关系
    chown mysql:mysql data
    
  6. 在master上面加授权,在新slave启动mysql,建立复制

pt-archiver

2014年10月21日 评论已被关闭

pt-archiver

http://blog.chinaunix.net/uid-16844903-id-4014064.html

根据官方文档-http://www.percona.com/doc/percona-toolkit/2.2/pt-archiver.html,选择常用的参数,做了说明.

  • NAME
  • SYNOPSIS
    • Usage
    • Examples
  • RISKS
  • DESCRIPTION
  • Percona XtraDB Cluster
  • OUTPUT
  • ERROR-HANDLING
  • OPTIONS
  • 如何读取配置文件

NAME

pt-archiver 数据归档到表或者文件

SYNOPSIS

Usage

pt-archiver [OPTIONS] --source DSN --where WHERE

Examples

  1. 从线上服务器把数据归档到线下服务器和本地文件
    pt-archiver --source h=oltp_server,D=test,t=tbl --dest h=olap_server \
      --file '/var/log/archive/%Y-%m-%d-%D.%t'                           \
      --where "1=1" --limit 1000 --commit-each
    
    
  2. 只做清理
    pt-archiver --source h=host,D=db,t=child --purge \
      --where 'NOT EXISTS(SELECT * FROM parent WHERE col=child.col)'
    
    
  3. 每1000行提交一次,每2000行打印一个标题显示进度,不删除源表数据,在归档完成后,打印统计信息.
    pt-archiver --source h=127.0.0.1,P=3306,u='xxxx',p='xxxx',D=db,t=tbl \
    --file '/tmp/%D.%t_%Y%m%d%H%i%s.txt' --charset 'utf8' \
    --where "oderTime < '2012-01-01 00:00:00'" --limit 1000 --commit-each \
    --progress 2000 --statistics --no-delete
    

RISKS

在实际应用中,已经证明Percona Toolkit是成熟的工具,但是所有的数据库工具都会给系统带来风险,所以,在使用前,请:

  • 认真读工具文档
  • 了解已知BUG
  • 在非线上环境测试
  • 备份数据并校验备份的数据

DESCRIPTION

pt-archiver正如这篇文章锁说的http://tinyurl.com/mysql-archiving.
该工具的目标是把线上的老数据转移,在转移过程中,不会对服务器产生任何冲击,同时也不会影响写入和查询.你可以把这些数据写入到另外一台MySQL,或者写到一个文件里面(该可以使用LOAD DATA INFILE语句导入),或者也可以直接做清理.

一些重要的选项,例如–limit, –retries, 和–txn-size

策略:

  1. 找到数据行,顺着索引,找到更多的数据
  2. 以后的每次查询,都不会进行全表扫描;它应该是通过索引,找到所有需要归档的数据.

–source参数,指定数据源
–dry-run参数, 试运行,可以打印出查询计划,看看是否为最坏的查询-全表扫描.

Percona XtraDB Cluster

暂无

OUTPUT

–progress int 后面需要加一个整数,意思是每处理多少行,输出一个标题行.包括当前日期和时间,pt-archiver运行了多少秒,已经归档了多少数据.
–statstics, 显示计时器,以预估所需要的执行时间.

ERROR-HANDLING

pt-archiver工具会尽可能的捕捉信息,并友好的退出.例如:发出一个SIGTERM信号(Ctrl-C),该工具将会捕捉到,并打印一条消息,然后退出.它不会执行-analyze或者-optimize参数,因为这些操作会相当耗时.它会正常执行其他代码,包括after_finish()函数

总而言之,如果pt-archiver遇到错误,它会跳过分析和优化.

OPTIONS

注意:

  1. 至少指定–dest, –file 或者 –purge三个参数中的一个
  2. –ignore and –replace 不能同时指定
  3. –txn-size and –commit-each 不能同时指定
  4. –low-priority-insert and –delayed-insert 不能同时指定
  5. –share-lock and –for-update 不能同时指定
  6. –analyze and –optimize 不能同时指定
  7. –no-ascend and –no-delete 不能同时指定

默认情况下,–dest从–source中复制DSN字符串.即,在同一个MySQL Server上面把数据归档到另外一个表.

  • –analyze
    在数据归档完成后,执行ANALYZE TABLE命令.d是在目的端执行,s是在源端执行.

    --analyze=ds
    
  • –ascend-first
    只使用第一列为升序的索引.
  • –ask-pass
    交互模式输入密码
  • –buffer
    指定–file参数时,提供缓冲功能.性能可能会提高(5-15)%
    风险: 在归档大事务的时候,如果发生宕机,可能会造成数据丢失.
  • –bulk-delete
    用单独的sql语句,每次删除一个块的数据.可以加快删除的速度.不推荐使用.
    正常情况下,是根据主键,一行一行的删除.
  • –[no]bulk-delete-limit
    为–bulk-delete参数添加limit选项
  • –bulk-insert
    使用”LOAD DATA INFILE”方式代替INSERT方式写入.
  • –charset
    指定字符集,例如

    --charset 'utf8'
    
  • –[no]check-charset
    默认开启,检查连接的字符集与表的字符集是否一致.
  • –[no]check-columns
    检查source和dest是否具有相同的列(不减查列的顺序,数据类型等).如果不同,则报错退出.
  • –check-interval
    默认1s
    如果指定了–check-slave-lag 参数,那么每秒都会检查从库的延迟情况(帐号要有权限连接从库).
  • –check-slave-lag
    指定一个从库的DSN串,检查复制延迟的情况,如果大于–max-lag,就会暂停归档.
  • –columns
    指定归档的列(用逗号分割),写入文件和目的库表.
    注意: 没有指定列,在原表也会被删除.也就说,未选择列的数据,就会丢失.
  • –commit-each
    配合–limit参数,一组一组的归档数据
  • –config
    参考下面的”如何读取配置文件”
  • –delayed-insert
    增加DELAYED属性
  • –dry-run
    不做任何操作,只打印要执行的查询语句.
  • –file
    归档到文件,文件内容相当于是SELECT INTO OUTFILE语法导出的数据,文件名可以增加时间戳和库名:

    %d    Day of the month, numeric (01..31)
    %H    Hour (00..23)
    %i    Minutes, numeric (00..59)
    %m    Month, numeric (01..12)
    %s    Seconds (00..59)
    %Y    Year, numeric, four digits
    
    %D    Database name
    %t    Table name
    

    例如

    --file '/var/log/archive/%Y-%m-%d-%D.%t'
    
  • –for-update
    为SELECT语句增加FOR UPDATE属性
  • –header
    在归档文件的第一行加入列名.注意,在LOAD DATA INFILE时,别写入多余的数据.
  • –high-priority-select
    增加HIGH_PRIORITY 修饰符
    See http://dev.mysql.com/doc/en/select.html for details.
  • –limit
    默认值1
    指定每次归档多少行.
  • –local
    执行OPTIMZE或者ANALYZE语句时,不写binlog,只在本地执行.
  • –low-priority-delete
    Adds the LOW_PRIORITY modifier to DELETE statements.
    See http://dev.mysql.com/doc/en/delete.html for details.
  • –low-priority-insert
    Adds the LOW_PRIORITY modifier to INSERT or REPLACE statements.
    See http://dev.mysql.com/doc/en/insert.html for details.
  • –max-lag
    默认是1s, 从库延迟超过这个设置值就会自动暂停.
  • –no-ascend
    不使用顺序索引优化
  • –no-delete
    不在source上面删除归档数据
  • –optimize
    归档完成后,执行 OPTIMIZE TABLE
  • –pid
    指定pid文件
  • –progress
    每隔多少行,打印一次信息.
  • –purge
    只删除,不做归档.可以省略–file和–dest选项
  • –quiet
    不打印任何输出
  • –replace
    在dest端,使用REPLACE INSERT语句
  • –retries
    遇到超时或死锁时,重试的次数.默认是1次
  • –run-time
    运行多长时间后退出.
    可以指定如下后缀,如果没有后缀,默认是秒

    s=seconds, m=minutes, h=hours, d=days
    
  • –sentinel
    默认路径: /tmp/pt-archiver-sentinel
    如果这个文件存在,则直接退出.
  • –set-vars
    可以设置mysql的变量,多个变量用逗号分割.

    --set-vars wait_timeout=500
    
  • –skip-foreign-key-checks
    禁用外键检查,相当于执行了 SET FOREIGN_KEY_CHECKS=0
  • –sleep
    指定两次SELECT语句的sleep时间.默认是没有sleep的.
    如果指定commit-each参数,commit和flush会在sleep之前发生.
  • –stop
    创建一个哨兵文件,停止正在运行的pt-archiver进程.例如session1正在执行归档操作,然后我用session2创建一个哨兵文件,那么session1的操作会立刻停止.
  • –txn-size
    指定每个事务的行数.
  • –where (重要)
    指定归档数据的过滤条件.
  • –why-quit
    打印退出的原因,归档数据正常完成的除外.

如何读取配置文件

  1. 参数
    --dry-run 只打印命令,不执行
    --file 归档到文件
    --limit和--commit-each配合使用,多少行执行一次commit
    --purge 清理
    
  2. /etc/percona-toolkit/pt-archiver.conf
    purge
    limit  = 10
    commit-each
    source = A=utf8,D=test,P=3306,h=127.0.0.1,p=...,t=t1,u=username
    where  = id<515
    
  3. 命令
    pt-archiver --config /etc/percona-toolkit/pt-archiver.conf

pt-online-schema-change使用说明

2014年10月21日 评论已被关闭

pt-online-schema-change使用说明

http://blog.chinaunix.net/uid-16844903-id-4010770.html

  • NAME
  • SYNOPSIS
    • Usage
    • Example
  • RISKS
  • DESCRIPTION
  • Percona XtraDB Cluster
  • OUTPUT
  • OPTIONS

NAME

pt-online-schema-change
不锁表的情况下,修改表结构.该工具执行的基本流程如下:

  1. 判断各种参数
  2. 根据原表”t”,创建一个名称为”_t_new”的新表
  3. 执行ALTER TABLE语句修改新表”_t_new”
  4. 创建3个触发器,名称格式为pt_osc_库名_表名_操作类型,比如
    CREATE TRIGGER `pt_osc_dba_t_del` AFTER DELETE ON `dba`.`t` FOR EACH ROW DELETE IGNORE FROM `dba`.`_t_new` WHERE `dba`.`_t_new`.`id` <=> OLD.`id`
    CREATE TRIGGER `pt_osc_dba_t_upd` AFTER UPDATE ON `dba`.`t` FOR EACH ROW REPLACE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) VALUES (NEW.`id`, NEW.`a`, NEW.`b`, NEW.`c1`)
    CREATE TRIGGER `pt_osc_dba_t_ins` AFTER INSERT ON `dba`.`t` FOR EACH ROW REPLACE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) VALUES (NEW.`id`, NEW.`a`, NEW.`b`, NEW.`c1`)
    
  5. 开始复制数据,比如
    INSERT LOW_PRIORITY IGNORE INTO `dba`.`_t_new` (`id`, `a`, `b`, `c1`) SELECT `id`, `a`, `b`, `c1` FROM `dba`.`t` LOCK IN SHARE MODE /*pt-online-schema-change 28014 copy table*/
    
  6. 复制完成后,交互原表和新表,执行RENAME命令,如 RENAME TABLE t to _t_old, _t_new to t;
  7. 删除老表,_t_old
  8. 删除触发器
  9. 修改完成

SYNOPSIS

Usage

pt-online-schema-change [OPTIONS] DSN
  1. 在不阻塞读写的情况下,修改表结构.在DSN中指定库和名.
  2. 在认真读文档之前,不要使用这个工具,并且操作前要做好备份.

Example

  1. 给sakila.actor添加一列:
    pt-online-schema-change --alter "ADD COLUMN c1 INT" D=sakila,t=actor
    
    相当于执行如下SQL:
    use sakila;
    alter table actor add column c1 INT;
    
  2. 把sakila.actor改为InnoDB引擎.如果它已经是一个InnoDB引擎的表,相当于在不阻塞读写的情况下,执行了OPTIMIZE TABLE操作.
    pt-online-schema-change --alter "ENGINE=InnoDB" D=sakila,t=actor
    
    相当于执行如下SQL:
    use sakila;
    alter table actor engine=innodb;

RISKS

Percona工具集在测试和实际使用中,都证明它是成熟的,但是,所有的操作数据的工具对可能对系统或者数据库造成风险.在使用这个工具之前,请:

  • 详细阅读工具的文档说明
  • 查看bugs列表
  • 在非线上环境测试这个工具
  • 备份线上数据,并且要验证这个备份

DESCRIPTION

pt-online-schema-change 模仿MySQL修改内部表的方法,不过它会复制你想改变的表.也就是说,原始的表不会被锁,客户端可以持续的读写数据.

pt-online-schema-change
根据原表创建一个新的空表,并按需求修改.然后从原表向新表复制数据.当复制完成时,挪走原始表,并用新表代替.默认情况下,会把原表删除.

数据复制过程是一小块一小块的复制,也可以通过–chunk-time参数调整块的大小.这个参数的工作原理与pt-table-check中的类似.

在复制数据过程中,任何数据的改动都会在新表中体现出来,因为这个工具会在原表中创建触发器,在原表更新的数据,在新表也进行了更新.如果在原表已经定义过触发器,那么这个工具就不能工作了.

当工具把数据全部复制到新表后,它会自动执行RENAME TABLE操作,同时修改原表和新表.这个操作完成后,会自动删除原表.

外键会让工具操作起来更负载并且会增加额外的风险.有外键约束的情况下,自动重命名原表会让外键失效.所以,在表结构修改完成后,该工具必须重新更新外键.这个工具有两种方法可以完成这个操作.你可以看文档的–alter-foreign-keys-method参数.
外键也会导致一些副作用,修改完成的表会出现相同的外键索引(除非在ALTER语句中指定了不同的外键),但是这些外键的名字不会一样,避免冲突.

为了安全,这个工具只有在指定–execute参数时才会修改表.默认情况下,是不加–execute参数的.该工具支持多种措施,防止产生高负载或其他问题,比如,自动检查从库,连接从库,可能会使用下列方法进行安全检查:

  • 如果检测到启用了”复制过滤器”,该工具不会执行操作.见–[no]check-replication-filters参数
  • 如果检测到复制出现延迟,该工具会暂停复制数据.见–max-lag 参数
  • 如果检测到服务器负载过高,该工具会暂停或者停止.见–max-load和–critical-load 参数
  • 该工具设置 innodb_lock_wait_timeout=1 and (for MySQL 5.5 and newer) lock_wait_timeout=60,因此它尽可能的减小锁争用,避免影响其他事务.这两个MySQL变量值可以通过 –set-ars参数设置.(innodb_lock_wait_timeout,在准备执行一次事务时,如果innodb等待了50秒(默认值)后还没有获得所申请的数据锁,innodb就将回滚这次事务. lock_wait_timeout尝试获取元数据锁的等待时间(默认值是一年),原数据锁是指访问表,触发器等对象而产生的锁.)
  • 如果修改的表存在外键约束,并且没有指定–alter-foreign-keys-method参数,那么该工具不会执行操作.
  • 该工具不能修改 Percona XtraDB Cluster中各节点的MyISAM表

Percona XtraDB Cluster

pt-online-schema-change 可以在Percona XtraDB Cluster (PXC) 5.5.28-23.7 或者更高的版本使用.但是它有两个限制:必须是InnoDB引擎的表,并且wsrep_OSU_method 要设置为TOI,否则会报错退出.

OUTPUT

该工具是动态打印信息到STDOUT(标准输出)的,因此可以看到它正在做的事情.在数据复制阶段,它把进度信息打印到STDERR(标准错误输出)中.可以指定–print参数,得到更多的信息.
如果–statistics被指定,在最后完成时,会生成一个如下的报告:

# Event  Count
# ====== =====
# INSERT     1

OPTIONS

参数 –dry-run 和 –execute 是互斥的.
该工具接收额外的命名行参数.更多信息请参考”SYNOPSIS”和usage部分.

  • –alter
    type: string
    结构修改,不带ALTER TABLE关键字.你可以执行多个表的修改操作,在它们之间用逗号分割.关于ALTER TABLE语法,请参考MySQL手册.
    该参数有以下局限性,如果被触发,会导致这个工具执行失败:

    • 不能指定rename语句.
    • 不能用删除列添加新列的方法重命名.该工具将不能复制原表中此列的数据到新列.
    • 如果你添加一个没有默认值新列并且属性设置为NOT NULL,该工具将不能执行,它不会给你指定一个默认值.
    • 删除外键约束,需要指定一个外键的名字,但是,这个名字并不是外键的名字,而是一个区别于外键的名字.这是MySQL的限制.在创建新表的时候, pt-online-schema-change会创建一个以下划线开头的外键.比如
      CONSTRAINT `fk_foo` FOREIGN KEY (`foo_id`) REFERENCES `bar` (`foo_id`)
      

      然后,必须这样指定

      --alter "DROP FOREIGN KEY _fk_foo"
    • 在MySQL 5.0版本操作可能会有问题.
  • –alter-foreign-keys-method
    如何把外键引用到新表?需要特殊处理带有外键约束的表,以保证它们可以应用到新表.当重命名表的时候,外键关系会带到重命名后的表上.
    该工具有两种方法,可以自动找到子表,并修改约束关系.

    • auto, 在rebuild_constraints和drop_swap两种处理方式中选择一个.
    • rebuild_constraints, 使用 ALTER TABLE语句先删除外键约束,然后再添加.如果子表很大的话,会导致长时间的阻塞.
    • drop_swap, 执行FOREIGN_KEY_CHECKS=0,禁止外键约束,删除原表,再重命名新表.
      这种方式很快,也不会产生阻塞,但是有风险:
      1, 在删除原表和重命名新表的短时间内,表是不存在的,程序会返回错误.
      2, 如果重命名表出现错误,也不能回滚了.因为原表已经被删除.
    • none, 类似”drop_swap”的处理方式,但是它不删除原表,并且外键关系会随着重命名转到老表上面.使用SHOW ENGINE INNODB STATUS;命令会发现如下错误信息
      Trying to add to index `idx_fk_staff_id` tuple:
      DATA TUPLE: 2 fields;
      0: len 1; hex 05; asc  ;;
      1: len 4; hex 80000001; asc     ;;
      But the parent table `sakila`.`staff_old`
      or its .ibd file does not currently exist!
      
  • –ask-pass
    连接MySQL时,提示输入密码
  • –charset
    设置字符集,相当于用客户端执行”SET NAMES UTF8″命令
  • –[no]check-alter
    解析并检查alter指定的命令:

    • 在以前的版本,使用CHANGE COLUMN命令会导致数据丢失,现在的版本虽然改进,但是在执行前,还应该使用 –dry-run 和 –print 查看一下详细的操作情况.
    • DROP PRIMARY KEY, 执行该命令的话,会发出警告.
  • –check-interval
    检查间隔,默认是1秒.请看–max-lag参数.
  • –[no]check-plan
    为了安全,检查查询的执行计划.默认情况下,这个工具在执行查询之前会先EXPLAIN,以获取一次少量的数据,如果是不好的EXPLAIN,那么会获取一次大量的数据.
    这个工具会多次执行EXPALIN,如果EXPLAIN不同的结果,那么就会认为这个查询是不安全的.
  • –[no]check-replication-filters
    检查MySQL的复制过滤器,如果存在就报错退出.
    如:binlog_ignore_db 和 replicate_do_db
  • –check-slave-lag
    指定一个从库的DSN连接地址,如果从库超过–max-lag参数设置的值,就会暂停操作.
  • –chunk-index
    为chunk指定一个索引(使用FORCE INDEX语法).
    默认情况下,工具会自动选择一个合适的索引.如果指定的索引不存在,该工具会自动选择一个合适的.
  • –chunk-index-columns
    选择使用具有n列的索引,多用于复合索引.
  • –chunk-size
    指定块的大小,默认是1000行,可以添加k,M,G后缀.这个块的大小要尽量与–chunk-time匹配.
    如果明确指定这个选项,那么每个块就会指定行数的大小.
  • –chunk-size-limit
    当需要复制的块远大于设置的chunk-size大小,就不复制.默认值是4.0
    一个没有主键或唯一索引的表,块大小就是不确定的.
  • –chunk-time
    在chunk-time执行的时间内,动态调整chunk-size的大小,以适应服务器性能的变化.
    该参数设置为0,或者指定chunk-size,都可以禁止动态调整.
  • –config
    执行配置文件,必须在命令行的第一个参数位置.
  • –critical-load

Bootstrapping the cluster

2014年10月21日 评论已被关闭

Bootstrapping the cluster

http://blog.chinaunix.net/uid-16844903-id-3976125.html

Bootstrapping是指初始化集群并运行.其他节点都通过自引导的节点同步数据(通过SST方式).在整个集群宕机的情况下,具有同样的引导方式:你可以选择一个集群中带有数据的节点,进行初始化.

集群中MySQL节点应该有如下配置选项:

[mysqld]
binlog_format                  = ROW default-storage-engine         = innodb
innodb_autoinc_lock_mode       = 2
innodb_locks_unsafe_for_binlog = 1
query_cache_size               = 0
query_cache_type               = 0
bind-address                   = 0.0.0.0

# WSREP #
wsrep_provider                 = /usr/lib/libgalera_smm.so
#wsrep_provider_options         =
wsrep_cluster_name             = "my_wsrep_cluster"
#wsrep_cluster_address          ="dummy://" 
#wsrep_cluster_address          ="gcomm://"
#wsrep_node_name                =
#wsrep_node_address             =
#wsrep_node_incoming_address    =
wsrep_slave_threads            = 1
#wsrep_dbug_option
wsrep_certify_nonPK            = 1
wsrep_max_ws_rows              = 131072
wsrep_max_ws_size              = 1073741824
wsrep_debug                    = 0
wsrep_convert_LOCK_to_trx      = 0
wsrep_retry_autocommit         = 1
wsrep_auto_increment_control   = 1
wsrep_drupal_282555_workaround = 0
wsrep_causal_reads             = 0
#wsrep_notify_cmd               =
wsrep_sst_method               = rsync
wsrep_sst_auth                 = root:

自引导集群需要做一些手动处理,在初始化的节点上面,应该把”wsrep_cluster_address”设置为”gcomm://”,”gcomm”的意思是告诉节点,启动的时候不连接任何集群.集群中第一个启动的节点,会把状态变量”wsrep_cluster_conf_id”的值设置为1.在这个节点启动之后,状态变量”wsrep_cluster_address”应该更新为这个集群的所有节点,例如:

wsrep_cluster_address=gcomm://192.168.0.2,192.168.0.3,192.168.0.4

尽管集群中的节点没有做这个设置,节点加入集群时,会根据变量”wsrep_cluster_name”指定的集群名称来判断.默认名称是my_wsrep_cluster.因此,变量”wsrep_cluster_address”并不需要在指定集群中所有的节点,这只是一种最好的做法:新加入的节点,将会指定的列表中选择节点,并加入集群.

第一个节点被启动后,其他节点应该一个一个的启动. 在集群自引导过程中,其他基本都是用SST方式复制数据,所以应该避免一次加入多个节点.

自引导集群时,如果不想修改变量wsrep_cluster_address为”gcomm://”的话,可以直接使用下面的命令:

/etc/init.d/pxc bootstrap-pxc

这种方式不会改变配置文件中的变量值,下次节点重启时,也不需要更新配置文件.这是有用处的:如果因为某种原因,集群失去了其他所有的节点,这个时候,就需要一个节点来进行自引导.

Version Specific Information

5.5.31-23.7.5 – New bootstrap init script option bootstrap-pxc introduced.

Bootstrapping the cluster

Percona XtraDB Cluster Limitations

2014年10月21日 评论已被关闭

Percona XtraDB Cluster Limitations

http://blog.chinaunix.net/uid-16844903-id-3966486.html

关于该高可用方案的局限性:

  1. 只能在InnoDB引擎下,才能实现复制.其他所有表的改写(包括mysql.*),都不会被复制.然而,DDL语句会被复制.在修改权限的时候,使用CREATE USER或者GRANT…可以被复制,使用INSERT INTO mysql.user将不会被复制.
  2. 不支持的SQL:
    • LOCK/UNLOCk TABLE
    • lock functions(GET_LOCK(),RELEASE_LOCK()…)
  3. 查询日志不能被重定向表.如果你打开查询日志,必须要改成文件模式:log_output = FILE.用general_log或者general_log_file变量指定日志名称.
  4. 允许的事务大小由wsrep_max_ws_rows 和 wsrep_max_ws_size设置.LOAD DATA INFILE …每1万行提交一次.对于LOAD DATA中的大事务会被分割成多个小事务来执行.
  5. 对于多节点同时修改同一行数据,在COMMIT时,可能有的节点会返回下面的错误代码:
    ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
    
  6. 分布式事务不能支持提交后的回滚操作.
  7. 整个集群的写入极限由最差的节点决定(木桶原理).如果有一个节点变的很慢,那么整个集群就会变慢.如果你要求较高的性能,应该保证较高的硬件配置.
  8. 要保证有3个或3个以上的集群节点.

Percona XtraDB Cluster Limitations

About Percona XtraDB Cluster

2014年10月21日 评论已被关闭

About Percona XtraDB Cluster

http://blog.chinaunix.net/uid-16844903-id-3956380.html

Percona XtraDB Cluster是一个开源,免费的MySQL高可用工具.

General introduction

  1. 集群由节点组成.推荐三个以上的节点,不过两个节点也能运行.
  2. 每个节点正常安装MySQL或者Percona Server.可以使用已经存在的MySQL或者Percona Server数据库充当集群中的节点.另外,也可以把集群中的节点拆出来,当作普通的MySQL/Percona Server使用.
  3. 每个节点都包括一份完整的数据.That defines XtraDB Cluster behavior in many ways. And obviously there are benefits and drawbacks.

16844903_1382404516WBRi

  1. Benefits of such approach:
    • 当你在本地节点执行一个查询的时候,不需要远程访问,本地节点所有数据是可用的.
    • 没有中心管理者.你可以在任意时间失去任何一个节点,并且集群还可以提供服务.
    • 对于读压力大的架构,是一个非常好的弹性方案.你能够在所有节点执行读操作.
  2. Drawbacks:
    • 加入新节点的开销比较大.它需要复制全部数据,如果有100G,那么就会复制100G.
    • 不能作为可扩展写入的方案.在任何一个节点写入,在集群中的其他节点都会写入.即,所有节点会写入相同的数据.
    • 会存在很多重复的数据.例如,3个节点,那么就会有3份数据.

What is core difference Percona XtraDB Cluster from MySQL Replication ?

分布式系统中的CAP理论,CAP理论指出,对一个分布式系统只能满足三个中的两个,不可能同时满足.
http://zh.wikipedia.org/wiki/CAP%E5%AE%9A%E7%90%86
一致性(Consistency) (所有节点在同一时间具有相同的数据)
可用性(Availability) (保证每个请求不管成功或者失败都有响应)
分隔容忍(Partition tolerance) (系统中任意信息的丢失或失败不会影响系统的继续运作)

MySQL replication has: Availability and Partitioning tolerance.
Percona XtraDB Cluster has: Consistency and Availability.

MySQL复制不能保证数据的一致性.Percona XtraDB Cluster保证数据的一致性.(失去了分区容忍度)

Components

Percona XtraDB Cluster 是基于Percona Server with XtraDB数据库并且包括Write Set Replication插件补丁.使用Galera library.版本2.x,一个通用的多主同步复制插件.
Galera library is developed by Codership Oy.
Galera 2.x supports such new features as:

  1. Incremental State Transfer (IST), especially useful for WAN deployments,
  2. RSU, Rolling Schema Update. Schema change does not block operations against table.

MySQL的binlog数据如何查看

2014年10月21日 评论已被关闭

MySQL的binlog数据如何查看
http://blog.chinaunix.net/uid-16844903-id-3896711.html
binlog介绍
登录到mysql查看binlog
用mysqlbinlog工具查看
本地查看
远程查看
binlog介绍
binlog,即二进制日志,它记录了数据库上的所有改变.
改变数据库的SQL语句执行结束时,将在binlog的末尾写入一条记录,同时通知语句解析器,语句执行完毕.
binlog格式
基于语句,无法保证所有语句都在从库执行成功,比如update … limit 1;
基于行,将每一次改动记为binlog中的一行.在执行一个特别复杂的update或者delete操作时,基于行的格式会有优势.
登录到mysql查看binlog
只查看第一个binlog文件的内容
show binlog events;
查看指定binlog文件的内容
show binlog events in ‘mysql-bin.000002′;
查看当前正在写入的binlog文件
show master status\G
获取binlog文件列表
show binary logs;
用mysqlbinlog工具查看
注意:

不要查看当前正在写入的binlog文件
不要加–force参数强制访问
如果binlog格式是行模式的,请加 -vv参数
本地查看

基于开始/结束时间
mysqlbinlog –start-datetime=’2013-09-10 00:00:00′ –stop-datetime=’2013-09-10 01:01:01′ -d 库名 二进制文件
基于pos值
mysqlbinlog –start-postion=107 –stop-position=1000 -d 库名 二进制文件
远程查看

指定开始/结束时间,并把结果重定向到本地t.binlog文件中.
mysqlbinlog -u username -p password -hl-db1.dba.beta.cn6.qunar.com -P3306 \
–read-from-remote-server –start-datetime=’2013-09-10 23:00:00′ –stop-datetime=’2013-09-10 23:30:00’ mysql-bin.000001 > t.binlog

基于pos

mysql-mmm使用指南

2014年10月21日 评论已被关闭

mysql-mmm使用指南

http://blog.chinaunix.net/uid-16844903-id-3799093.html

按: 结合官方手册整理的个人理解,如有异议,欢迎拍砖.

官网:http://mysql-mmm.org/mysql-mmm.html

· MMM

o mmm_mond – the monitor

· States

· Checks

· Network check

· Flapping

o Startup

o Role transition

· Standard role

· Active master role

MMM

mmm_mond – the monitor

States

参考6中状态的博文

Checks

mmm_mond监控服务启动后,共有5项检查.

检查项 说明 备注
ping_ip 网络检查 随机对ips指定的IP地址进行icmp检测,默认间隔为1秒,0.5秒返回为正常.否则报告:FATAL Network is unreachable
ping 主机检查  
mysql MySQL可用性检查  
rep_threads 复制线程检查  
rep_backlog 复制延迟检查  

其中ping_ip比较特殊,是网络稳定性检查,默认每秒一次.由monitor部分的ping_interval参数设置.
其他四项可以在配置文件的check部分指定,默认值及含义如下

参数 默认值 解释 备注
check_period 5 每5秒执行一次检查  
trap_period 10 如果超过10秒还检查不成功,就被认为是失败的.通常日志就报告error错误  
timeout 2 2秒不返回结果,则认为是超时  
restart_after 10000 在检查1万次之后,重启检查进程  
max_backlog 60 超过60秒报延迟 线上服务器已经修改为86400,为了避免短暂延迟导致的vip移动现象.nagios中已有监控复制延迟的插件

Network check

网络检查

如果在启动monitor过程中,mmm_mond将选择ping_ips定义的ip地址,顺序进行网络测试,只要有一个是通的,mmm_mond就会启动,否则会一直循环尝试.

/usr/share/perl5/MMM/Monitor/NetworkChecker.pm 模块中下面代码
...
...
                                if ($res =~ /^OK/) {
                                DEBUG "IP '$ip' is reachable: $res";
                                $checker->shutdown(); return 1;
                        }

Flapping

抖动
在设置auto_set_online或者故障时间不超过60秒,主机在ONLINE和HARD_OFFLINE / REPLICATION_FAIL / REPLICATION_DELAY这些状态直接频繁切换,则会导致抖动.
如果auto_set_online参数大于0,在flap_duration时间后,会自动设置为上线.

Startup

1 初期的网络检查

2 如果网络不通,它会不断重试,直到网络畅通,才会启动

3 主机检查

4 尝试从以下地方读取主机的状态
.status文件
agent信息
hosts信息

Role transition

角色转换

Standard role

IP is removed from old host
IP is configured on new host
New host sends arp packets to inform other hosts that it now has the IP

Active master role

移动master角色

mmm_control move_role writer db2

移动过程中的操作

· 写角色从db1上移除

1 设置db1为只读

2 移除db1的所有活动连接

3 移除db1的写VIP

· 向从库(如果有从库)发送信息,从库执行如下操作

1 尽可能赶上db1的复制

2 执行CHANGE MASTER命令,变为db2的从库

· 写角色在db2上添加

1 将db2设置为可写

2 配置写VIP

3 发送arp包,告诉其他主机,db2已经获取新的IP地址.

· 执行完成后检查:

4 登录db2,执行ip addr show命令,查看vip是否存在.

5 登录mysql,执行show slave status\G,查看复制状态是否正常

6 QA与DEV测试线上应用.

· 可能出现的问题:

1 因为SUPER权限导致的复制中断
通过上面的分析,已经预先把db1设置为只读,为什么切换后还会有主键冲突的问题?
程序帐号具有SUPER权限,read_only参数,对此类用户无效.

2 MySQL负载/磁盘IO
MySQL负载或者磁盘IO的压力过大,导致复制的IO线程短时间无响应.写VIP迁移后,IO线程继续传输的过期的binlog日志,导致主键冲突.

 

[HowTo]MySQL服务器状态-innodb_buffer_pool

2014年10月21日 评论已被关闭

[HowTo]MySQL服务器状态-innodb_buffer_pool
http://blog.chinaunix.net/uid-16844903-id-3767860.html
注意:
InnoDB缓冲池中不仅包含表的数据页和索引页,还包括undo页,插入缓冲,自适应哈希索引,锁信息,数据字典.
所以,在InnoDB缓冲池里的数据页永远不会等于池的大小.(也许会无限接近)
在cacti的InnoDB Buffer Pool监控图形中,会出现这种情况:明明有空闲的内存,但是Database Pages不会填满Pool Size
SHOW ENGINE INNODB STATUS(Percona)
这里面的两个状态是什么联系?还没有搞清楚.
Hash table size
Adaptive hash index

————————————-
INSERT BUFFER AND ADAPTIVE HASH INDEX
————————————-
Ibuf: size 1, free list len 995, seg size 997, 34699 merges
merged operations:
insert 209995, delete mark 28278, delete 26716
discarded operations:
insert 0, delete mark 0, delete 0
Hash table size 25499809, node heap has 51279 buffer(s)
688855.38 hash searches/s, 70787.26 non-hash searches/s

———————-
BUFFER POOL AND MEMORY
———————-
Total memory allocated 13237223424; in additional pool allocated 0
Internal hash tables (constant factor + variable factor)
Adaptive hash index 1044157824 (203998472 + 840159352)
Page hash 12750664 (buffer pool 0 only)
Dictionary cache 51252364 (51001072 + 251292)
File system 98072 (82672 + 15400)
Lock system 31908976 (31875512 + 33464)
Recovery system 0 (0 + 0)
Dictionary memory allocated 251292
Buffer pool size 786431
Buffer pool size, bytes 12884885504
Free buffers 191819
Database pages 543333
Old database pages 200546
Modified db pages 140937
Pending reads 0
Pending writes: LRU 0, flush list 0, single page 0
Pages made young 3261, not young 0
0.44 youngs/s, 0.00 non-youngs/s
Pages read 483871, created 59462, written 1160632
2.37 reads/s, 10.62 creates/s, 217.05 writes/s
Buffer pool hit rate 1000 / 1000, young-making rate 0 / 1000 not 0 / 1000
Pages read ahead 0.00/s, evicted without access 0.00/s, Random read ahead 0.00/s
LRU len: 543333, unzip_LRU len: 0
I/O sum[0]:cur[0], unzip sum[0]:cur[0]

参考
http://dev.mysql.com/doc/refman/5.5/en/innodb-buffer-pool.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-monitors.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-multiple-buffer-pools.html
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html#sysvar_innodb_buffer_pool_size
http://www.percona.com/doc/percona-server/5.5/diagnostics/innodb_show_status.html
http://www.percona.com/docs/wiki/percona-server:features:innodb_show_hashed_memory#description

[技巧]用logrotate进行MySQL日志管理

2014年10月21日 评论已被关闭

[技巧]用logrotate进行MySQL日志管理

http://blog.chinaunix.net/uid-16844903-id-3691833.html

原因:面对累计长时间的慢查询日志,检索起来非常不方便.

目的:按天分割慢查询日志.

方法:利用logrotate工具

实现

logrotate介绍

logrotate是管理日志文件的工具,在CentOS系统中,命令的位置在/usr/sbin/logrotate,常用的操作如:
-d, –debug               Don’t do anything, just test (implies -v)
-f, –force               Force file rotation

注意:带有d参数,并不会产生新日志.
logrotate一般每天由cron运行一次.标准的配置文件是/etc/logrotate.conf,而/etc/logrotate.d目录也是保存配置文件的位置.

logrotate常见选项:

选项 含义
compress 压缩日志文件的所有非当前版本
copy 复制当前的日志文件,忽略create参数
copytruncate 复制当前的日志文件,并置空当前文件
daily 每天轮日志文件i
dateext 轮换的日志后缀为-YYYYMMDD格式
delaycompress 压缩除了当前和最近之外的所有其他版本
missingok 如果日志不存在,不会报错
notifempty 如果日志为空,则不轮换
rotate n 在轮换方案中包含n个版本的日志
size=logsize 如果日志文件大于logsize才轮换

轮换MySQL日志的配置文件

  1. /var/log/mysql/slow_3306.log
  2. /var/log/mysql/slow_3307.log {
  3. # compress
  4. copytruncate
  5. # create 644 mysql mysql
  6. daily
  7. dateext
  8. delaycompress
  9. missingok
  10. notifempty
  11. rotate 7
  12. # size 1M
  13. }
可以切换两个配置文件.或者用*.log,切换目录下所有的.
调试命令

/usr/sbin/logrotate -d /etc/logrotate.d/mysql-slow
/usr/sbin/logrotate -f /etc/logrotate.d/mysql-slow

附:

利用copytruncate选项,没有必要使用postrotate选项,在轮换日志后,给MySQL发送flush logs;命令.

 

[演示]Innodb默认隔离级别和死锁

2014年10月21日 评论已被关闭

[演示]Innodb默认隔离级别和死锁

http://blog.chinaunix.net/uid-16844903-id-3664861.html

关于MVCC,REPEATABLE-READ,死锁的概念这里不再做介绍,主要用两个测试说明隔离级别和死锁.

演示环境

  1. 建表语句
    CREATE TABLE `t01` (
    `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
    `a` varchar(45) NOT NULL COMMENT '',
    `b` varchar(45) NOT NULL COMMENT '',
    PRIMARY KEY (`id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '测试表';
    
  2. 插入数据
    insert into t01 (a,b) values(123,500);
    insert into t01 (a,b) values(456,200);
    insert into t01 (a,b) values(789,100);
    

测试一:目的,演示MVCC和REPEATABLE-READ隔离界别

操作时间 操作说明 session1 session2
14:31:27 s1开始事务 begin;
14:31:31 s1查询
select * from t01;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
| 1  | 123 | 500 |
| 2  | 456 | 200 |
| 3  | 789 | 100 |
+----+-----+-----+
14:34:48 s2开始事务 begin;
14:34:51 s2更改数据,并没有出现锁等待,
说明s1并没有锁住数据.
update t01 set b = b-50 where id = 3;
update t01 set b = b+50 where id = 1;
14:35:17 s2提交,并查看数据 commit;

select * from t01;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
| 1  | 123 | 550 |
| 2  | 456 | 200 |
| 3  | 789 | 50  |
+----+-----+-----+
14:35:31 s1查询,结果发现,
用s2更新的数据并没有
在s1的结果集中出现,
所以说,s1读取的数据,
是在s1事务开始时的快照.
select * from t01;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
| 1  | 123 | 500 |
| 2  | 456 | 200 |
| 3  | 789 | 100 |
+----+-----+-----+
14:36:14 s1提交,然后再查看,
结果集与s2查询的一致
select * from t01;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
| 1  | 123 | 550 |
| 2  | 456 | 200 |
| 3  | 789 | 50  |
+----+-----+-----+

测试二:目的,演示死锁

操作时间 操作说明 session1 session2
16:03:16 s1开始事务 begin;
16:03:28 s1查询
select * from t01 where id =1;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
|  1 | 123 | 550 |
+----+-----+-----+
16:03:49 s1更改数据
update t01 set b=b-50 where id =1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
16:03:59 s2开始事务 begin;
16:04:08 s2查看数据
select * from t01 ;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
|  1 | 123 | 550 |
|  2 | 456 | 200 |
|  3 | 789 | 50  |
+----+-----+-----+
3 rows in set (0.00 sec)
16:04:37 s2更改数据
update t01 set b=b-10 where id =3;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
16:05:16 s1更改被s2锁住的数据,开始锁等待,19.97秒
update t01 set b=b+50 where id =3;
Query OK, 1 row affected (19.97 sec)
Rows matched: 1  Changed: 1  Warnings: 0
16:05:35 s2更改被s1锁住的数据,然后报告了死锁.
update t01 set b= b+10 where id =1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
16:05:57 s1提交事务 commit;
16:06:02 s2提交事务 commit;
16:06:09 用s2的窗口查看结果集,
发现提交生效的事务是s1.
select * from t01;
+----+-----+-----+
| id | a   | b   |
+----+-----+-----+
|  1 | 123 | 500 |
|  2 | 456 | 200 |
|  3 | 789 | 100 |
+----+-----+-----+
3 rows in set (0.00 sec)
16:07:24 查看死锁信息,发现回滚的事务是s2
------------------------
LATEST DETECTED DEADLOCK
------------------------
130508 16:05:35
*** (1) TRANSACTION:
TRANSACTION 132B, ACTIVE 127 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1
MySQL thread id 16, OS thread handle 0x7f7cb4ac8700, query id 85 192.168.236.242 root Updating
update t01 set b=b+50 where id =3
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 3 n bits 72 index `PRIMARY` of table `test`.`t01` trx id 132B lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
TRANSACTION 132C, ACTIVE 87 sec starting index read, thread declared inside InnoDB 500
mysql tables in use 1, locked 1
3 lock struct(s), heap size 376, 2 row lock(s), undo log entries 1
MySQL thread id 15, OS thread handle 0x7f7cb7d11700, query id 86 192.168.236.242 root Updating
update t01 set b= b+10 where id =1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 12 page no 3 n bits 72 index `PRIMARY` of table `test`.`t01` trx id 132C lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 12 page no 3 n bits 72 index `PRIMARY` of table `test`.`t01` trx id 132C lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

 

MySQL权限管理-安全与效率的折中

2014年10月21日 评论已被关闭

MySQL权限管理-安全与效率的折中
http://blog.chinaunix.net/uid-16844903-id-3607594.html
之所以想写这篇文章,是因为在工作中,经常被MySQL的用户授权问题折腾一下.日积月累….忍不住吐槽一下.
MySQL的安全包括很多方面,主要分为内部安全,外部安全及数据传输安全.
内部安全指MySQL的启动和安装目录权限,避免其他用户进入目录,随意访问数据.
外部安全指网络访问,其中用户授权是重点,也是接下来我要吐槽的.
加密传输,这个不多说,大部分数据库处于内网,很少有人使用.
mysql库的user表,是最常用来查看权限的,例如用下面的SQL查看当前实例有多少个授权用户和某一个用户的权限

SELECT user,host,password FROM mysql.user;
SHOW GRANTS FOR ‘username’@’hostname’;
在MySQL中没有用户组的概念,它认为即使相同的用户名而从不同的主机连过来,也是不同的授权.比如我创建一个用户u01,分别授权从192.168.0.1和192.168.0.2连接,那么MySQL认为是两个不同的用户.说到这里,也许有人就能想到,为了安全,不能让其他人随意连接数据,每个授权最好精确到IP地址,而不用范围(%)匹配.这样固然于安全有好处,但是也有如下维护的”困难”:
1. 权限表条目增多.如果有20台主机需要连数据库,那么需要根据20台主机分别授权.
2. 权限混乱,很难保证相同的用户名具有相同的权限.
3. 追加授权操作成本太高.经常遇到这样的情况1,开发人员提交权限申请,密码处注明,”与原来一样”.dba去MySQL查密码,结果同样的用户名,从不同IP连过来的密码不一致,分别查出权限后,再与开发确认后,进行授权.情况2,应用添加新机器,已经发布上线,突然发现没有权限,急忙催促dba添加.
4. “脏授权”无法清理,很多因服务器更换,导致的旧IP授权留在数据库中,没人去清理.
因此,建议在内网的数据库,并且做了合理的网段隔离,应该把数据库授权的工作尽量简化,对网段授权.这虽然增加的连接MySQL的范围.但是如果有人要窃取数据的话,仍然要满足下面的条件:
1. 登录到内网并有权限登录到任意一台服务器
2. 知道MySQL的连接地址和帐号/密码
与精确到IP地址唯一不同的地方,便是”登录到任意一台,还是指定一台”,这其中的安全与效率问题,需要大家根据自己的环境,折中考虑授权策略.
自动化脚本grant.py.txt

http://blog.chinaunix.net/blog/downLoad/fileid/8435.html
1. 脚本运行在python2.7环境中.
2. 如果grant.ini配置文件不存在,则会自动生成.
配置文件说明如下:

[proposer]
#申请人邮件地址
to_email =
# 抄送地址
cc_email =
[server]
# 登录目标数据库主机的用户名
srv_user =
# 数据库主机名
srv_host = db1
# 服务器的密码(禁止填写)
#srv_pwd =
[db]
# 登录数据库的用户名
db_user = xxxx
# 通过IP和端口连接
db_host = 127.0.0.1
db_port = 3306
# 对程序提供的ip(vip)地址
db_ip_writer = 192.168.250.1
#db_ip_reader =
# 登录数据库的密码(禁止填写)
#db_pwd = xxx
[grants]
# 添加的权限
permission = UPDATE,DELETE,INSERT,SELECT
# 库范围
database = *
# 表范围
table = *
# 添加的用户名
user = test1
# 允许连接的主机
host = 127.0.0.1,192.168.250.1,192.168.250.2
# 连接的密码(不需要填写,脚本会自动生成16位的密码,由Aa-Zz,0-9组成),追加用户授权时,可修改为加密的密文.
#password =
# 其他
#others =

脚本逻辑:
1.读取配置文件
2.拼接sql
3.显示/确认
4.确认后,写入用户目录的tempfile.sql文件中
5.将tempfile.sql文件传到目标服务器
6.在目标服务执行mysql … -e ‘source tempfile.sql’
7.删除本地和目标服务器的tempfile.sql文件
8.发送邮件通知申请人