So Tired !_! 逆水行舟, 不进则退!

21Dec/17

解决mysql占用IO过高(优化mysql slave的同步速度)

Posted by Nick Xu

1、日志产生的性能影响:
由于日志的记录带来的直接性能损耗就是数据库系统中最为昂贵的IO资源。MySQL的日志包括错误日志(ErrorLog),更新日志(UpdateLog),二进制日志(Binlog),查询日志(QueryLog),慢查询日志(SlowQueryLog)等。当然,更新日志是老版本的MySQL才有的,目前已经被二进制日志替代。

在默认情况下,系统仅仅打开错误日志,关闭了其他所有日志,以达到尽可能减少IO损耗提高系统性能的目的。但是在一般稍微重要一点的实际应用场景中,都至少需要打开二进制日志,因为这是MySQL很多存储引擎进行增量备份的基础,也是MySQL实现复制的基本条件。有时候为了进一步的性能优化,定位执行较慢的SQL语句,很多系统也会打开慢查询日志来记录执行时间超过特定数值(由我们自行设置)的SQL语句。

一般情况下,在生产系统中很少有系统会打开查询日志。因为查询日志打开之后会将MySQL中执行的每一条Query都记录到日志中,会该系统带来比较大的IO负担,而带来的实际效益却并不是非常大。一般只有在开发测试环境中,为了定位某些功能具体使用了哪些SQL语句的时候,才会在短时间段内打开该日志来做相应的分析。所以,在MySQL系统中,会对性能产生影响的MySQL日志(不包括各存储引擎自己的日志)主要就是Binlog了。

2、mysql内执行如下指令:
set global sync_binlog=500;
当每进行500次事务提交之后,MySQL将进行一次fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。
set global innodb_flush_log_at_trx_commit=2;
默认值1代表每一次事务提交或事务外的指令都需要把日志写入(flush)硬盘,这是很费时的。特别是使用电池供电缓存(Battery backed up cache)时。设置为2代表不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬盘,所以你一般不会丢失超过1-2秒的更新。设成0会更快一点,但安全方面比较差,即使MySQL挂了也可能会丢失事务的数据。而值设置为2只会在整个操作系统宕机时才可能丢数据。

测试环境:

Red Hat Enterprise Linux Server release 6.3 (Santiago)

Server version: 5.6.22-log MySQL Community Server (GPL)

我搭建了1主3从的环境,准备测试MHA架构,过程中发现,测试并发插入的时候,从库1可以跟上,从库2,3跟不上

如何判断是io thread慢还是 sql thread慢呢,有个方法,观察show slave status\G ,

判断3个参数(参数后面的值是默认空闲时候的正常值):

Slave_IO_State: Waiting for master to send event

Slave_SQL_Running_State: Slave has read all relay log; waiting for the slave I/O thread to update it

Seconds_Behind_Master: 0

1.sql thread慢的表现:

Seconds_Behind_Master越来越大

Slave_SQL_Running_State: Reading event from the relay log

2.io thread慢的表现:

Seconds_Behind_Master为0

Slave_SQL_Running_State: 显示正常值

Slave_IO_State:显示忙碌状态

而我观察到的值是

Slave_IO_State: Waiting for master to send event

Seconds_Behind_Master: 313

Slave_SQL_Running_State: Reading event from the relay log

因此推断是sql thread慢

为啥只有slave2,3慢,而slave1可以跟上呢,开始怀疑是参数配置的差异,比对/etc/my.cnf后发现,配置无差异

因此排除这个原因,后来用dstat观察,发现繁忙时候,slave的IO写速度上不去

slave1:

$ dstat

----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--

usr sys idl wai hiq siq| read writ| recv send| in out | int csw

0 0 100 0 0 0|9308B 11k| 0 0 | 3B 3B| 63 63

3 4 54 40 0 0| 88k 10M| 45k 9438B| 0 0 |1857 2579

3 3 59 35 0 1| 80k 7552k| 40k 8486B| 0 0 |1675 2307

3 3 56 38 0 0| 72k 7824k| 42k 8816B| 0 0 |1727 2348

3 4 52 41 0 1| 96k 9688k| 49k 10k| 0 0 |2029 2874

3 4 54 39 0 0| 96k 8880k| 45k 9410B| 0 0 |1905 2674

3 3 53 40 0 1| 96k 9776k| 58k 10k| 0 0 |1935 2671

3 3 58 36 0 0| 64k 7848k| 40k 8420B| 0 0 |1724 2357

3 5 52 40 0 1| 96k 8936k| 49k 10k| 0 0 |1948 2680

3 4 51 42 0 1| 96k 9400k| 49k 10k| 0 0 |1988 2760

3 4 52 41 0 0| 88k 9752k| 49k 10k| 0 0 |2058 2868

4 4 51 41 0 1| 96k 9680k| 49k 9938B| 0 0 |1990 2750

3 3 59 35 0 0| 80k 7632k| 39k 8288B| 0 0 |1668 2275

3 4 52 42 0 1| 80k 8504k| 46k 9146B| 0 0 |1860 2523

3 4 51 42 0 0| 80k 8496k| 43k 8684B| 0 0 |1882 2516

2 3 65 30 0 0| 64k 5976k| 30k 6440B| 0 0 |1326 1802

3 4 53 40 0 1| 72k 8360k| 59k 10k| 0 0 |1859 2538

3 4 51 42 0 1| 96k 8840k| 53k 10k| 0 0 |1958 2648

2 4 51 43 0 0| 72k 7352k| 40k 7760B| 0 0 |1633 2219

3 4 51 42 0 1| 88k 7920k| 31k 6770B| 0 0 |1767 2373

3 3 54 40 0 0| 80k 8528k| 40k 8750B| 0 0 |1859 2549

slave2:

----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--

usr sys idl wai hiq siq| read writ| recv send| in out | int csw

2 1 50 47 0 1|8192B 1168k| 55k 10k| 0 0 | 533 771

1 1 51 48 0 0|8192B 1048k| 33k 7046B| 0 0 | 427 622

1 1 51 48 0 0|8192B 1080k| 58k 9806B| 0 0 | 500 709

1 1 50 48 0 0| 0 1864k| 51k 8486B| 0 0 | 502 669

1 2 51 47 0 0|8192B 1120k| 42k 8156B| 0 0 | 496 674

1 1 51 47 0 0|8192B 1160k| 32k 6350B| 0 0 | 467 655

1 2 51 47 0 0| 0 1288k| 50k 10k| 0 0 | 563 797

1 1 51 47 0 0|8192B 1200k| 43k 8486B| 0 0 | 493 728

2 1 50 47 0 0|8192B 1024k| 45k 8816B| 0 0 | 481 659

1 1 50 48 0 0|8192B 1248k| 49k 9450B| 0 0 | 517 772

1 1 50 48 0 0| 0 1264k| 47k 9146B| 0 0 | 516 756

1 2 50 47 0 1|8192B 1144k| 50k 10k| 0 0 | 520 765

1 1 51 48 0 0|8192B 1200k| 51k 8156B| 0 0 | 484 716

1 2 50 48 0 0|8192B 968k| 50k 9278B| 0 0 | 470 684

1 1 50 48 0 0|8192B 1128k| 39k 7892B| 0 0 | 476 679

1 1 51 47 0 0| 0 1248k| 45k 9476B| 0 0 | 523 760

1 2 50 48 0 0|8192B 1448k| 41k 7826B| 0 0 | 552 805

1 1 50 48 0 0|8192B 1120k| 44k 8090B| 0 0 | 470 692

slave3:

----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--

usr sys idl wai hiq siq| read writ| recv send| in out | int csw

1 1 50 49 0 0|8192B 1328k|1167B 170B| 0 0 | 385 515

1 1 51 48 0 0|8192B 1128k| 754B 170B| 0 0 | 325 449

1 1 50 49 0 0| 0 920k| 474B 314B| 0 0 | 279 381

0 1 50 49 0 0|8192B 664k|1633B 170B| 0 0 | 226 291

1 1 50 49 0 0|8192B 1200k|1250B 170B| 0 0 | 353 475

1 1 50 48 0 0| 0 1432k|1632B 170B| 0 0 | 402 551

1 1 51 48 0 0| 16k 1752k|1045B 170B| 0 0 | 487 664

1 1 50 48 0 0|8192B 1648k| 12k 170B| 0 0 | 461 636

1 1 51 48 0 0| 0 1272k| 886B 170B| 0 0 | 380 501

1 1 50 49 0 0|8192B 1000k|1023B 170B| 0 0 | 300 400

1 1 50 48 0 0|8192B 1096k| 747B 170B| 0 0 | 332 442

1 1 50 48 0 0|8192B 1448k|1003B 170B| 0 0 | 416 557

1 1 50 48 0 0| 0 1592k|1174B 170B| 0 0 | 450 614

1 1 51 48 0 0|8192B 1416k|1028B 170B| 0 0 | 404 552

0 1 50 49 0 0|8192B 1128k|1031B 170B| 0 0 | 331 447

1 1 51 48 0 0|8192B 1160k|1185B 170B| 0 0 | 340 458

1 1 50 49 0 0| 0 1120k| 633B 170B| 0 0 | 326 453

1 0 50 49 0 0|8192B 656k|8886B 170B| 0 0 | 221 288

1 1 50 49 0 0|8192B 1128k|1619B 170B| 0 0 | 335 451

slave1可以达到每秒9M的写入IO,而slave2,3只能达到每秒1M多,IO性能差很多,后来分析了下存储,发现是有很大差异的,也印证了我的推测

那么问题来了,要如何优化IO性能比较差的slave呢,其实很简单,修改两个参数

mysql>SHOW VARIABLES like '%innodb_fl%';

mysql>SHOW VARIABLES like 'sync_binlog';

mysql> set global sync_binlog=20 ;

Query OK, 0 rows affected (0.00 sec)

mysql> set global innodb_flush_log_at_trx_commit=2;Query OK, 0 rows affected (0.00 sec)

innodb_flush_log_at_trx_commit和sync_binlog是MySQL innodb引擎的两个重要的参数,其中innodb_flush_log_at_trx_commit是将事务日志从innodb log buffer写入到redo log中,sync_binlog是将二进制日志文件刷新到磁盘上。

innodb事务日志redo,binlog逻辑过程如下:
1.事务写入redo log buffer中;
2.将log buffer刷新到redo log中,不过会先写一个TX PREPARE标记;
3.写binlog
4.在redo log中写入TX COMMIT标记;
5.将写binlog成功的标记写入redo log。

参数解析如下:
innodb_flush_log_at_trx_commit = N:

N=0    每隔一秒,把事务日志缓存区的数据写到日志文件中,以及把日志文件的数据刷新到磁盘上;
log buffer 会 每秒写入到日志文件并刷写(flush)到磁盘。但每次事务提交不会有任何影响,也就是 log buffer 的刷写操作和事务提交操作没有关系。在这种情况下,MySQL性能最好,但如果 mysqld 进程崩溃,通常会导致最后 1s 的日志丢失。

N=1    每个事务提交时候,把事务日志从缓存区写到日志文件中,并且刷新日志文件的数据到磁盘上;
当取值为 1 时,每次事务提交时,log buffer 会被写入到日志文件并刷写到磁盘。这也是默认值。这是最安全的配置,但由于每次事务都需要进行磁盘I/O,所以也最慢。

N=2    每事务提交的时候,把事务日志数据从缓存区写到日志文件中;每隔一秒,刷新一次日志文件,但不一定刷新到磁盘上,而是取决于操作系统的调度;
当取值为 2 时,每次事务提交会写入日志文件,但并不会立即刷写到磁盘,日志文件会每秒刷写一次到磁盘。这时如果 mysqld 进程崩溃,由于日志已经写入到系统缓存,所以并不会丢失数据;在操作系统崩溃的情况下,通常会导致最后 1s 的日志丢失。
上面说到的「最后 1s」并不是绝对的,有的时候会丢失 更多数据。有时候由于调度的问题,每秒刷写(once-per-second flushing)并不能保证 100% 执行。对于一些数据一致性和完整性要求不高的应用,配置为 2 就足够了;如果为了最高性能,可以设置为 0。有些应用,如支付服务,对一致性和完整性要求很高,所以即使最慢,也最好设置为 1.
当我们设置为2 的时候,Log Thread 会在我们每次事务结束的时候将数据写入事务日志,但是这里的写入仅仅是调用了文件系统的文件写入操作。而我们的文件系统都是有缓存机制的,所以Log Thread 的这个写入并不能保证内容真的已经写入到物理磁盘上面完成持久化的动作。文件系统什么时候会将缓存中的这个数据同步到物理磁盘文件Log Thread 就完全不知道了。所以,当设置为2 的时候,MySQL Crash 并不会造成数据的丢失,但是OS Crash 或者是主机断电后可能丢失的数据量就完全控制在文件系统上了。各种文件系统对于自己缓存的刷新机制各不一样,大家可以自行参阅相关的手册。

sync_binlog =  N:

N>0    每向二进制日志文件写入N条SQL或N个事务后,则把二进制日志文件的数据刷新到磁盘上;
N=0    不主动刷新二进制日志文件的数据到磁盘上,而是由操作系统决定;

推荐配置组合:

N=1,1  — 适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如充值消费系统;
N=1,0  — 适合数据安全性要求高,磁盘IO写能力支持业务不富余,允许备库落后或无复制;
N=2,0或2,m(0<m<100)  — 适合数据安全性有要求,允许丢失一点事务日志,复制架构的延迟也能接受;
N=0,0  — 磁盘IO写能力有限,无复制或允许复制延迟稍微长点能接受,例如:日志性登记业务;
当两个参数设置为双1的时候,写入性能最差,sync_binlog=N (N>1 ) innodb_flush_log_at_trx_commit=2 时,(在当前模式下)MySQL的写操作才能达到最高性能。

数据安全性

当innodb_flush_log_at_trx_commit和sync_binlog  都为1时是最安全的,在mysqld 服务崩溃或者服务器主机crash的情况下,binary log 只有可能丢失最多一个语句或者一个事务。但是鱼与熊掌不可兼得,都为1会导致频繁的IO操作,因此该模式也是最慢的一种方式。
当innodb_flush_log_at_trx_commit设置为0,mysqld进程的崩溃会导致上一秒钟所有事务数据的丢失。
当innodb_flush_log_at_trx_commit设置为2,只有在操作系统崩溃或者系统掉电的情况下,上一秒钟所有事务数据才可能丢失。

双1适合数据安全性要求非常高,而且磁盘IO写能力足够支持业务,比如订单,交易,充值,支付消费系统。双1模式下,当磁盘IO无法满足业务需求时,推荐的做法是innodb_flush_log_at_trx_commit=2 ,sync_binlog=N (N为500 或1000) 且使用带蓄电池后备电源的缓存cache,防止系统断电异常。
---------------------
注意:

如果启用了autocommit,那么每一个语句statement就会有一次写操作;否则每个事务对应一个写操作。

而且mysql服务默认是autocommit打开的

修改参数后,slave2,3也一样可以跟上slave1的速度了

slave2,3:

----total-cpu-usage---- -dsk/total- -net/total- ---paging-- ---system--

usr sys idl wai hiq siq| read writ| recv send| in out | int csw

3 2 94 2 0 0| 32k 80k| 49k 10k| 0 0 |1042 658

3 2 94 1 0 1| 32k 72k| 49k 10k| 0 0 |1258 964

2 2 95 2 0 0| 32k 72k| 44k 9146B| 0 0 |1126 882

2 1 95 2 0 0| 32k 72k| 41k 8486B| 0 0 | 959 659

2 2 96 1 0 0| 32k 72k| 47k 9476B| 0 0 |1153 841

2 2 95 2 0 0| 24k 72k| 39k 8090B| 0 0 | 866 504

2 2 96 1 0 0| 24k 72k| 42k 7562B| 0 0 | 908 663

2 1 95 2 0 0| 40k 72k| 52k 10k| 0 0 |1084 685

3 1 94 2 0 1| 40k 80k| 54k 11k| 0 0 |1204 873

2 2 96 1 0 0| 16k 32k| 30k 6044B| 0 0 | 846 802

2 1 97 1 0 0| 24k 32k| 35k 7760B| 0 0 |1059 888

2 1 95 3 0 0| 32k 856k| 44k 9278B| 0 0 | 943 551

2 1 94 3 0 0| 32k 104k| 42k 8618B| 0 0 | 986 704

2 1 96 1 0 0| 24k 72k| 34k 7034B| 0 0 | 863 682

2 2 95 2 0 0| 32k 64k| 45k 8684B| 0 0 |1052 750

2 2 90 7 0 0| 24k 416k| 38k 7166B| 0 0 | 906 722

3 2 93 2 0 1| 32k 80k| 57k 10k| 0 0 |1069 829

3 2 94 1 0 0| 32k 72k| 42k 8486B| 0 0 |1076 942

2 1 96 1 0 0| 24k 72k| 37k 7496B| 0 0 | 859 575

2 2 94 2 0 1| 32k 64k| 43k 8684B| 0 0 |1138 1011

3 2 94 1 0 0| 32k 72k| 42k 9014B| 0 0 |1099 782

2 3 94 2 0 0| 32k 72k| 50k 10k| 0 0 |1332 1359

2 2 95 2 0 0| 24k 72k| 34k 6902B| 0 0 | 921 799

2 2 94 2 0 0| 40k 72k| 55k 11k| 0 0 |1318 1016

1 2 96 2 0 0| 32k 80k| 41k 8882B| 0 0 |1020 719

而且我观察到slave2,3的写入数量减少了两个数量级,从1M多下降到70k

Tagged as: , Comments Off
9Oct/16

MySQL Index详解

Posted by Nick Xu

FROM:http://blog.csdn.net/tianmo2010/article/details/7930482

MySQL Index

一、SHOW INDEX会返回以下字段

1、Table 表的名称。

2、 Non_unique 如果索引不能包括重复词,则为0,如果可以则为1。

3、 Key_name 索引的名称

4、 Seq_in_index 索引中的列序列号,从1开始。

5、 Column_name 列名称。

6、 Collation 列以什么方式存储在索引中。在MySQL中,有值‘A’(升序)或NULL(无分类)。

7、Cardinality 索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数,所以即使对于小型表,该值也没有必要是精确的。基数越大,当进行联合时,MySQL使用该索引的机会就越大。

8、Sub_part 如果列只是被部分地编入索引,则为被编入索引的字符的数目。如果整列被编入索引,则为NULL。

9、 Packed 指示关键字如何被压缩。如果没有被压缩,则为NULL。

10、 Null 如果列含有NULL,则含有YES。如果没有,则该列含有NO。

11、 Index_type 用过的索引方法(BTREE, FULLTEXT, HASH, RTREE)。

12、 Comment 多种评注,您可以使用db_name.tbl_name作为tbl_name FROM db_name语法的另一种形式。这两个语句是等价的:

mysql>SHOW INDEX FROM mytable FROM mydb;

mysql>SHOW INDEX FROM mydb.mytable;

二、使用 CREATE INDEX 创建索引

如果要为已存在的表创建索引,就需要使用 CREATE INDEX 命令或 ALTER TABLE 来创建索引。

CREATEINDEX idxtitle ON tablename (title)

这条命令将创建与 CREATE TABLE 命令相同的索引。

三、使用 ALTER TABLE 创建索引

ALTER TABLE tablename ADD INDEX idxtitle (title)

四、查看和删除索引

我们可以使用以下命令来查看已存在的索引:

SHOW INDEX FROM tablename

DROP INDEX indexname ON tablename

DROP INDEX 将删除 tablename 表上的 indexname 索引,indexname 就是我们创建表时指定的索引名。

########################################

createtable c(id char(20) not null primary key,`index` char(10));

问题1.如果我忘记把id设为主键现在该怎么办?输入什么命令?

ALTER TABLE record

ADD PRIMARY KEY (id);

例如:ALTER TABLE `abc` ADD PRIMARY KEY(`a`);

问题2.如果我想把id设为主键,同时想把name改为not null,且改为varchar(10),能不能用一句命令完成问题2?

ALTER TABLE record

ADD PRIMARY KEY (id),

CHANGEname varchar(10) not null;

########################################

索引 mysql index

索引是一种数据结构,可以是B-tree, R-tree, 或者 hash 结构。其中R-tree 常用于查询比较接近的数据;B-trees适合用于查找某范围内的数据,可以很快的从当前数据找到下条数据;hash结构则适用于随机访问的场合,查找每条数据的时间几乎相同。显然,若要查找某个时间段的数据,用B-tree结构要比hash结构快好多。

优化查询的有效方法是为经常查询的字段建立索引,如无索引查询数据时,会遍历整张表(多么恐怖啊);若有了索引查找会容易很多。当进行 UPDATE, DELETE, 以及 INSERT 操作时,mysql会自动更新索引信息。

1.创建和删除索引( Creating and dropping indexes )

创建:

[xhtml] view plaincopy

  1. mysql> USE sakila;
  2. Database changed
  3. mysql> CREATE INDEX idx_actor_first_name ON actor (first_name);
  4. 或者
  5. mysql> ALTER TABLE actor ADD INDEX idx_actor_first_name (first_name);

PS:当用 create index 创建索引时,必须指定索引的名字,否则mysql会报错;
用 ALTER TABLE 创建索引时,可以不指定索引名字,若不指定mysql会自动生成索引名字

建立索引时,若不想用存储引擎的默认索引类型,可以指定索引的类型:

  1. mysql> ALTER TABLE temp_index
  2.  ADD INDEX (first_name),
  3.  ADD INDEX lname (last_name) USING BTREE

删除:

  1. DROP INDEX indexname ON tblname
  2. mysql> DROP INDEX idx_actor_fname ON actor;
  3. mysql> ALTER TABLE actor DROP INDEX idx_actor_fname;

2.索引类型

BTREE    适合连续读取数据
RTREE    适合根据一条数据找附近的数据
HASH      适合随机读取数据
FULLTEXT
SPATIAL

查看某个表中存在的索引类型

  1. mysql> SELECT INDEX_NAME,INDEX_TYPE
  2. -> FROM INFORMATION_SCHEMA.STATISTICS
  3. -> WHERE TABLE_NAME=’temp_index’;
  4. +------------+------------+
  5. | INDEX_NAME | INDEX_TYPE |
  6. +------------+------------+
  7. | first_name | HASH       |
  8. | lname      | BTREE      |
  9. +------------+------------+

3.索引冗余

如果两个或者多个索引包含了相同的索引信息,那么就存在索引冗余。

不同类型的索引有不同的索引冗余判断:
(1) SPATIAL 索引
SPATIAL 只能是一个简单索引,不能说复合索引;存在冗余的情况是同一个字段有两个索引。
(2) FULLTEXT 索引
若一个FULLTEXT索引是另一个索引的子集(不考虑字段顺序),则存在冗余。
例如: 表中包含如下两个索引
■ (field1)
■ (field1, field2)
(3) HASH 索引
若一个索引在不考虑字段顺序的情况下,索引字段相同,则存在冗余。
例如:表中包含如下两个索引
■ (field1, field2, field3)
■ (field1, field3, field2)
(4) BTREE 索引
若一个索引是另一个索引的子集(考虑字段顺序),则存在冗余。
例如:表中包含如下两个索引
■ (field2)
■ (field2, field1)

PS:若两个或多个索引有相同的索引字段和字段顺序,但是有不同的索引类型,这样的索引是不冗余的。因为每种索引类型有自己的索引值。

######################################

深入MySQL数据库的索引

摘要:本文介绍索引的类型,已经如何创建索引做了介绍,其中涉及三个比较重要的SQL语句――ALTER TABLE、CREATE/DROP INDEX和CREATE TABLE,注意它们的用法。

索引是加速表内容访问的主要手段,特别对涉及多个表的连接的查询更是如此。这是数据库优化中的一个重要内容,我们要了解为什么需要索引,索引如何工作以及怎样利用它们来优化查询。本节中,我们将介绍索引的特点,以及创建和删除索引的语法。

索引的特点

所有的MySQL列类型能被索引。在相关的列上的使用索引是改进SELECT操作性能的最好方法。

一个表最多可有16个索引。最大索引长度是256个字节,尽管这可以在编译MySQL时被改变。

对于CHAR和 VARCHAR列,你可以索引列的前缀。这更快并且比索引整个列需要较少的磁盘空间。对于BLOB和TEXT列,你必须索引列的前缀,你不能索引列的全部。

MySQL能在多个列上创建索引。一个索引可以由最多15个列组成。(在CHAR和VARCHAR列上,你也可以使用列的前缀作为一个索引的部分)。

虽然随着 MySQL 的进一步开发创建索引的约束将会越来越少,但现在还是存在一些约束的。下面的表根据索引的特性,给出了 ISAM 表和 MyISAM 表之间的差别:

表2-1 通道信息特征字对照表索引的特点 ISAM 表 MyISAM 表

NULL 值

BLOB 和 TEXT 列

每个表中的索引数

每个索引中的列数

最大索引行尺寸

不允许

不能索引

16

16

256 字节 允许

只能索引列的前缀

32

16

500 字节

从此表中可以看到,对于 ISAM 表来说,其索引列必须定义为 NOT NULL,并且不能对 BLOB 和 TEXT 列进行索引。MyISAM 表类型去掉了这些限制,而且减缓了其他的一些限制。两种表类型的索引特性的差异表明,根据所使用的 MySQL 版本的不同,有可能对某些列不能进行索引。例如,如果使用3.23 版以前的版本,则不能对包含 NULL 值的列进行索引。

索引有如下的几种情况:

INDEX索引:通常意义的索引,某些情况下KEY是它的一个同义词。索引的列可以包括重复的值。

UNIQUE索引:唯一索引,保证了列不包含重复的值,对于多列唯一索引,它保证值的组合不重复。

PRIMARY KEY索引:也UNIQUE索引非常类似。事实上,PRIMARYKEY索引仅是一个具有PRIMARY名称的UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY。

用Alter Table语句创建与删除索引

为了给现有的表增加一个索引,可使用 ALTER TABLE 或CREATE INDEX 语句。ALTER TABLE 最常用,因为可用它来创建普通索引、UNIQUE 索引或 PRIMARY KEY 索引,如:

ALTER TABLE tbl_name ADD INDEX index_name  (column_list)

ALTER TABLE tbl_name ADD UNIQUE index_name  (column_list)

ALTER TABLE tbl_name ADD PRIMARY KEY index_name  (column_list)

其中 tbl_name 是要增加索引的表名,而 column_list 指出对哪些列进行索引。一个(col1,col2,...)形式的列表创造一个多列索引。索引值有给定列的值串联而成。如果索引由不止一列组成,各列名之间用逗号分隔。索引名 index_name 是可选的,因此可以不写它,MySQL 将根据第一个索引列赋给它一个名称。ALTER TABLE 允许在单个语句中指定多个表的更改,因此可以在同时创建多个索引。

同样,也可以用ALTER TABLE语句删除列的索引:

ALTER TABLE tbl_name DROP INDEX index_name

ALTER TABLE tbl_name DROP PRIMARY KEY

注意上面第一条语句可以用来删除各种类型的索引,而第三条语句只在删除 PRIMARY KEY 索引时使用;在此情形中,不需要索引名,因为一个表只可能具有一个这样的索引。如果没有明确地创建作为 PRIMARY KEY 的索引,但该表具有一个或多个 UNIQUE 索引,则 MySQL 将删除这些 UNIQUE 索引中的第一个。

如果从表中删除了列,则索引可能会受到影响。如果所删除的列为索引的组成部分,则该列也会从索引中删除。如果组成索引的所有列都被删除,则整个索引将被删除。

例如,对于上面所使用的student为例,你可能想为之创建这样的索引,以加速表的检索速度:

mysql> ALTER TABLE student

-> ADD PRIMARY KEY(id),

-> ADD INDEXmark(english,Chinese,history);

这个例子,既包括PRIMARY索引,也包括多列索引。记住,使用 PRIMARY索引的列,必须是一个具有NOT NULL属性的列,如果你愿意查看创建的索引的情况,可以使用SHOW INDEX语句:

mysql> SHOW INDEX FROM student;

其结果为:

+---------+------------+----------+--------------+-------------+-

| Table  | Non_unique | Key_name | Seq_in_index | Column_name |

+---------+------------+----------+--------------+-------------+-

| student |          0 | PRIMARY  |           1 | id          |

| student |          1 | mark     |            1 | english     |

| student |          1 | mark     |            2 | chinese     |

| student |          1 | mark     |            3 | history     |

+---------+------------+----------+--------------+-------------+-

由于列数太多,上表并没有包括所有的输出,读者可以试着自己查看。

再使用ALTER TABLE语句删除索引,删除索引需要知道索引的名字,你可以通过SHOW INDEX语句得到:

mysql> ALTER TABLE student DROP PRIMARYKEY,

-> DROP INDEX mark;

再产看表中的索引,其语句和输出为:

mysql> SHOW INDEX FROM student;

Empty set (0.01 sec)

用CREATE\DROP INDEX创建索引

还可以用CREATE INDEX语句来创建索引.CREATE INDEX 是在 MySQL 3.23版中引入的,但如果使用3.23 版以前的版本,可利用 ALTER TABLE 语句创建索引(MySQL 通常在内部将 CREATE INDEX 映射到 ALTER TABLE)。该语句创建索引的语法如下:

CREATE UNIQUE INDEX index_name ON tbl_name(column_list)

CREATE INDEX index_name ON tbl_name(column_list) tbl_name、index_name 和 column_list 具有与 ALTER TABLE 语句中相同的含义。这里索引名不可选。很明显,CREATE INDEX 可对表增加普通索引或 UNIQUE 索引,不能用 CREATE INDEX 语句创建 PRIMARY KEY 索引。

可利用 DROP INDEX语句来删除索引。类似于 CREATE INDEX 语句,DROP INDEX 通常在内部作为一条 ALTER TABLE 语句处理,并且DROP INDEX是在 MySQL 3.22 中引入的。

删除索引语句的语法如下:

DROP INDEX index_name ON tbl_name

还是上一节的例子,由于CREATE INDEX不能创建PRIMARY索引,所以这里我们只创建一个多列索引:

mysql> CREATE INDEX mark ONstudent(english,chinese,history);

同样的检查student表,可知:

mysql> SHOW INDEX FROM student;

+---------+------------+----------+--------------+-------------+

| Table  | Non_unique | Key_name | Seq_in_index | Column_name |

+---------+------------+----------+--------------+-------------+

| student |          1 | mark     |           1 | english     |

| student |          1 | mark     |            2 | chinese     |

| student |          1 | mark     |            3 | history     |

+---------+------------+----------+--------------+-------------+

然后使用下面的语句删除索引:

mysql> DROP INDEX mark ON student;

在创建表时指定索引

要想在发布 CREATE TABLE 语句时为新表创建索引,所使用的语法类似于 ALTER TABLE 语句的语法,但是应该在您定义表列的语句部分指定索引创建子句,如下所示:

  1. CREATE TABLE tbl_name
  2. (
  3. INDEX index_name (column_list),
  4. KEY index_name (column_list),
  5. UNIQUE index_name (column_list),
  6. PRIMARY KEY index_name (column_list),
  7. )

与ALTER TABLE 一样,索引名对于 INDEX 和 UNIQUE 都是可选的,如果未给出,MySQL 将为其选一个。另外,这里KEY时INDEX的一个别名,具有相同的意义。

有一种特殊情形:可在列定义之后增加 PRIMARY KEY 创建一个单列的PRIMARY KEY 索引,如下所示:

  1. CREATE TABLE tbl_name
  2. (
  3.   iINT NOT NULL PRIMARY KEY
  4. )

该语句等价于以下的语句:

  1. CREATE TABLE tbl_name
  2. (
  3.   iINT NOT NULL,
  4.  PRIMARY KEY (i)
  5. )

前面所有表创建样例都对索引列指定了 NOT NULL。如果是 ISAM 表,这是必须的,因为不能对可能包含 NULL 值的列进行索引。如果是 MyISAM 表,索引列可以为 NULL,只要该索引不是 PRIMARY KEY 索引即可。

在CREATE TBALE语句中可以某个串列的前缀进行索引(列值的最左边 n 个字符)。

如果对某个串列的前缀进行索引,应用 column_list 说明符表示该列的语法为 col_name(n) 而不用col_name。例如,下面第一条语句创建了一个具有两个 CHAR 列的表和一个由这两列组成的索引。第二条语句类似,但只对每个列的前缀进行索引:

  1. CREATE TABLE tbl_name
  2. (
  3. name CHAR(30),
  4. address CHAR(60),
  5. INDEX (name,address)
  6. )
  1. CREATE TABLE tbl_name
  2. (
  3. name CHAR(30),
  4. address CHAR(60),
  5. INDEX (name(10),address(20))
  6. )

你可以检查所创建表的索引:

mysql> SHOW INDEX FROM tbl_name;

+----------+------------+----------+--------------+-------------+-

| Table   | Non_unique | Key_name | Seq_in_index | Column_name |

+----------+------------+----------+--------------+-------------+-

| tbl_name |          1 | name     |            1 | name        |

| tbl_name |          1 | name     |            2 | address     |

+----------+------------+----------+--------------+-------------+-

在某些情况下,可能会发现必须对列的前缀进行索引。例如,索引行的长度有一个最大上限,因此,如果索引列的长度超过了这个上限,那么就可能需要利用前缀进行索引。在 MyISAM 表索引中,对 BLOB 或 TEXT 列也需要前缀索引。

对一个列的前缀进行索引限制了以后对该列的更改;不能在不删除该索引并使用较短前缀的情况下,将该列缩短为一个长度小于索引所用前缀的长度的列。

总结

本节对索引的类型,已经如何创建索引做了介绍,其中涉及三个比较重要的SQL语句――ALTER TABLE、CREATE/DROP INDEX和CREATE TABLE,注意它们的用法。

索引最重要的功能是,通过使用索引加速表的检索,有关这方面的知识,将在第十章数据库优化中介绍。

思考题

1、建立一个如下所述的表:

data:FLOAT列,使用随机函数填充数据

birth:DATETIME列,填充当前时间。

然后,请录入几条数据。最后计算data列的平均值、总和、极值,并且按照data列降序排序检索值。

2、分别使用标准SQL模式和扩展正规表达式模式匹配,匹配上面创建的表,假设你创建表的当前日期为2001-01-01,用模式匹配检索出birth列包含该日期的值。(实际上,上面的表中记录都是同一日期录入的,因此实际将返回全部记录。)

3、为前几章使用的数据表创建索引:

student:为id段创建一个PRIMARY索引,为english、chinese和history创建一个多列索引。

pet:为name和owner段创建一个多类索引。

4、删除为pet表创建的索引。

##################################################

MySQL Index的使用

以下是理论知识备忘:

一、什么是索引?
索引用来快速地寻找那些具有特定值的记录,所有MySQL索引都以B-树的形式保存。如果没有索引,执行查询时MySQL必须从第一个记录开始扫描整个表的所有记录,直至找到符合要求的记录。表里面的记录数量越多,这个操作的代价就越高。如果作为搜索条件的列上已经创建了索引,MySQL无需扫描任何记录即可迅速得到目标记录所在的位置。如果表有1000个记录,通过索引查找记录至少要比顺序扫描记录快100倍。

假设我们创建了一个名为people的表:

CREATE TABLE people ( peopleid SMALLINT NOT NULL, name CHAR(50) NOT NULL );

然后,我们完全随机把1000个不同name值插入到people表。下图显示了people表所在数据文件的一小部分:

可以看到,在数据文件中name列没有任何明确的次序。如果我们创建了name列的索引,MySQL将在索引中排序name列:

对于索引中的每一项,MySQL在内部为它保存一个数据文件中实际记录所在位置的“指针”。因此,如果我们要查找name等于“Mike”记录的 peopleid(SQL命令为“SELECT peopleid FROM people WHERE name='Mike';”),MySQL能够在name的索引中查找“Mike”值,然后直接转到数据文件中相应的行,准确地返回该行的 peopleid(999)。在这个过程中,MySQL只需处理一个行就可以返回结果。如果没有“name”列的索引,MySQL要扫描数据文件中的所有记录,即1000个记录!显然,需要MySQL处理的记录数量越少,则它完成任务的速度就越快。

二、索引的类型
MySQL提供多种索引类型供选择:

普通索引
这是最基本的索引类型,而且它没有唯一性之类的限制。普通索引可以通过以下几种方式创建:
创建索引,例如CREATE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD INDEX [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...], INDEX [索引的名字] (列的列表) );

唯一性索引
这种索引和前面的“普通索引”基本相同,但有一个区别:索引列的所有值都只能出现一次,即必须唯一。唯一性索引可以用以下几种方式创建:
创建索引,例如CREATE UNIQUE INDEX <索引的名字> ON tablename (列的列表);
修改表,例如ALTER TABLE tablename ADD UNIQUE [索引的名字] (列的列表);
创建表的时候指定索引,例如CREATE TABLE tablename ( [...], UNIQUE [索引的名字] (列的列表) );

主键
主键是一种唯一性索引,但它必须指定为“PRIMARY KEY”。如果你曾经用过AUTO_INCREMENT类型的列,你可能已经熟悉主键之类的概念了。主键一般在创建表的时候指定,例如“CREATE TABLE tablename ( [...], PRIMARY KEY (列的列表) ); ”。但是,我们也可以通过修改表的方式加入主键,例如“ALTER TABLE tablename ADD PRIMARY KEY (列的列表); ”。每个表只能有一个主键。

全文索引
MySQL从3.23.23版开始支持全文索引和全文检索。在MySQL中,全文索引的索引类型为FULLTEXT。全文索引可以在VARCHAR或者TEXT类型的列上创建。它可以通过CREATE TABLE命令创建,也可以通过ALTER TABLE或CREATE INDEX命令创建。对于大规模的数据集,通过ALTER TABLE(或者CREATE INDEX)命令创建全文索引要比把记录插入带有全文索引的空表更快。本文下面的讨论不再涉及全文索引,要了解更多信息,请参见MySQL documentation。

三、单列索引与多列索引
索引可以是单列索引,也可以是多列索引。下面我们通过具体的例子来说明这两种索引的区别。假设有这样一个people表:

CREATE TABLE people ( peopleid SMALLINT NOT NULL AUTO_INCREMENT, firstname CHAR(50) NOT NULL, lastname CHAR(50) NOT NULL, age SMALLINT NOT NULL, townid SMALLINT NOT NULL, PRIMARY KEY (peopleid) );

下面是我们插入到这个people表的数据:

这个数据片段中有四个名字为“Mikes”的人(其中两个姓Sullivans,两个姓McConnells),有两个年龄为17岁的人,还有一个名字与众不同的Joe Smith。

这个表的主要用途是根据指定的用户姓、名以及年龄返回相应的peopleid。例如,我们可能需要查找姓名为Mike Sullivan、年龄17岁用户的peopleid(SQL命令为SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age=17;)。由于我们不想让MySQL每次执行查询就去扫描整个表,这里需要考虑运用索引。

首先,我们可以考虑在单个列上创建索引,比如firstname、lastname或者age列。如果我们创建firstname列的索引(ALTER TABLE people ADD INDEX firstname (firstname);),MySQL将通过这个索引迅速把搜索范围限制到那些firstname='Mike'的记录,然后再在这个“中间结果集”上进行其他条件的搜索:它首先排除那些lastname不等于“Sullivan”的记录,然后排除那些age不等于17的记录。当记录满足所有搜索条件之后,MySQL就返回最终的搜索结果。

由于建立了firstname列的索引,与执行表的完全扫描相比,MySQL的效率提高了很多,但我们要求MySQL扫描的记录数量仍旧远远超过了实际所需要的。虽然我们可以删除firstname列上的索引,再创建lastname或者 age列的索引,但总地看来,不论在哪个列上创建索引搜索效率仍旧相似。

为了提高搜索效率,我们需要考虑运用多列索引。如果为firstname、lastname和age这三个列创建一个多列索引,MySQL只需一次检索就能够找出正确的结果!下面是创建这个多列索引的SQL命令:

ALTER TABLE people ADD INDEX fname_lname_age (firstname,lastname,age);

由于索引文件以B-树格式保存,MySQL能够立即转到合适的firstname,然后再转到合适的lastname,最后转到合适的age。在没有扫描数据文件任何一个记录的情况下,MySQL就正确地找出了搜索的目标记录!

那么,如果在firstname、lastname、age这三个列上分别创建单列索引,效果是否和创建一个firstname、lastname、 age的多列索引一样呢?答案是否定的,两者完全不同。当我们执行查询的时候,MySQL只能使用一个索引。如果你有三个单列的索引,MySQL会试图选择一个限制最严格的索引。但是,即使是限制最严格的单列索引,它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

四、最左前缀
多列索引还有另外一个优点,它通过称为最左前缀(Leftmost Prefixing)的概念体现出来。继续考虑前面的例子,现在我们有一个firstname、lastname、age列上的多列索引,我们称这个索引为fname_lname_age。当搜索条件是以下各种列的组合时,MySQL将使用fname_lname_age索引:

firstname,lastname,age
firstname,lastname
firstname
从另一方面理解,它相当于我们创建了(firstname,lastname,age)、(firstname,lastname)以及(firstname)这些列组合上的索引。下面这些查询都能够使用这个fname_lname_age索引:

SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan' AND age='17'; SELECT peopleid FROM people WHERE firstname='Mike' AND lastname='Sullivan'; SELECT peopleid FROM people WHERE firstname='Mike'; The following queries cannot use the index at all: SELECT peopleid FROM people WHERE lastname='Sullivan'; SELECT peopleid FROM people WHERE age='17'; SELECT peopleid FROM people WHERE lastname='Sullivan' AND age='17';

五、选择索引列
在性能优化过程中,选择在哪些列上创建索引是最重要的步骤之一。可以考虑使用索引的主要有两种类型的列:在WHERE子句中出现的列,在join子句中出现的列。请看下面这个查询:

SELECT age ## 不使用索引
FROM people WHERE firstname='Mike' ## 考虑使用索引
AND lastname='Sullivan' ## 考虑使用索引

这个查询与前面的查询略有不同,但仍属于简单查询。由于age是在SELECT部分被引用,MySQL不会用它来限制列选择操作。因此,对于这个查询来说,创建age列的索引没有什么必要。下面是一个更复杂的例子:

SELECT people.age, ##不使用索引
town.name ##不使用索引
FROM people LEFT JOIN town ON
people.townid=town.townid ##考虑使用索引
WHERE firstname='Mike' ##考虑使用索引
AND lastname='Sullivan' ##考虑使用索引

与前面的例子一样,由于firstname和lastname出现在WHERE子句中,因此这两个列仍旧有创建索引的必要。除此之外,由于town表的townid列出现在join子句中,因此我们需要考虑创建该列的索引。

那么,我们是否可以简单地认为应该索引WHERE子句和join子句中出现的每一个列呢?差不多如此,但并不完全。我们还必须考虑到对列进行比较的操作符类型。MySQL只有对以下操作符才使用索引:<,<=,=,>,>=,BETWEEN,IN,以及某些时候的LIKE。可以在LIKE操作中使用索引的情形是指另一个操作数不是以通配符(%或者_)开头的情形。例如,“SELECT peopleid FROM peopleWHERE firstname LIKE 'Mich%';”这个查询将使用索引,但“SELECT peopleid FROM people WHERE firstname LIKE '%ike';”这个查询不会使用索引。

六、分析索引效率

现在我们已经知道了一些如何选择索引列的知识,但还无法判断哪一个最有效。MySQL提供了一个内建的SQL命令帮助

一、 MySQL建表,字段需设置为非空,需设置字段默认值。

二、 MySQL建表,字段需NULL时,需设置字段默认值,默认值不为NULL。

三、 MySQL建表,如果字段等价于外键,应在该字段加索引。

四、 MySQL建表,不同表之间的相同属性值的字段,列类型,类型长度,是否非空,是否默认值,需保持一致,否则无法正确使用索引进行关联对比。

五、 MySQL使用时,一条SQL语句只能使用一个表的一个索引。所有的字段类型都可以索引,多列索引的属性最多15个。

六、 如果可以在多个索引中进行选择,MySQL通常使用找到最少行的索引,索引唯一值最高的索引。

七、 建立索引index(part1,part2,part3),相当于建立了 index(part1),index(part1,part2)和index(part1,part2,part3)三个索引。

八、 MySQL针对like语法必须如下格式才使用索引:

SELECT * FROM t1 WHERE key_col LIKE 'ab%' ;

九、 SELECT COUNT(*) 语法在没有where条件的语句中执行效率没有SELECT COUNT(col_name)快,但是在有where条件的语句中执行效率要快。

十、 在where条件中多个and的条件中,必须都是一个多列索引的key_part属性而且必须包含key_part1。各自单一索引的话,只使用遍历最少行的那个索引。

十一、 在where条件中多个or的条件中,每一个条件,都必须是一个有效索引。

十二、 ORDER BY 后面的条件必须是同一索引的属性,排序顺序必须一致(比如都是升序或都是降序)。

十三、 所有GROUP BY列引用同一索引的属性,并且索引必须是按顺序保存其关键字的。

十四、 JOIN 索引,所有匹配ON和where的字段应建立合适的索引。

十五、 对智能的扫描全表使用FORCE INDEX告知MySQL,使用索引效率更高。

十六、 定期ANALYZE TABLE tbl_name为扫描的表更新关键字分布 。

十七、 定期使用慢日志检查语句,执行explain,分析可能改进的索引。

十八、 条件允许的话,设置较大的key_buffer_size和query_cache_size的值(全局参数),和sort_buffer_size的值(session变量,建议不要超过4M)。

2Mar/16

在 Mac 下用 Homebrew 安装 MySQL

Posted by Nick Xu

在 Mac 下用 Homebrew 安装 MySQL, 网上的教程倒是很多,不过大多数都很默契地雷同。如果稍有点定制要求,就无从下手了。

我先也不免俗,从基本的开始:

一、首先安装 Homebrew

1
2
3
$ ruby -e "$(curl -fsSL https://raw.github.com/mxcl/homebrew/go)"
$ brew install git
$ brew update

二、安装 MySQL

用下面的命令就可以自动安装了:

1
$ brew install mysql

如果想让 MySQL 开机自动启动,可以如下操作:

1
2
3
4
$ mkdir -p ~/Library/LaunchAgents
$ ln -sfv /usr/local/opt/mysql/*.plist ~/Library/LaunchAgents
$ find /usr/local/Cellar/mysql/ -name "homebrew.mxcl.mysql.plist" -exec cp {} ~/Library/LaunchAgents/ \;
$ launchctl load -w ~/Library/LaunchAgents/homebrew.mxcl.mysql.plist

设置 MySQL 用户以及数据存放地址

1
2
$ unset TMPDIR
$ mysql_install_db --verbose --user=`whoami` --basedir="$(brew --prefix mysql)" --datadir=/usr/local/var/mysql --tmpdir=/tmp

好了,可以启动了

1
$ mysql.server start

另外的参数还有 {start|stop|restart|reload|force-reload|status}

大部分的介绍就在此结束了。

三、更详细的设置

配置文件 my.cnf

作为用惯了 Linux 的人, 一定会去 /etc 下找 my.cnf, 让你失望了,这个文件要自己建立。如果看一下帮助

1
$ mysqld --help --verbose

就会发现系统会按这个顺序去找 my.cnf

  1. /etc/my.cnf
  2. /etc/mysql/my.cnf
  3. /usr/local/etc/my.cnf
  4. ~/.my.cnf

一般网上大虾都会这么教小白建立 my.cnf, 其实这个默认的文件里面几乎没什么内容。

1
$ sudo cp $(brew --prefix mysql)/support-files/my-default.cnf /etc/my.cnf

所以,还是自己老老实实参考 linux 下的配置文件吧。

my.cnf (my.cnf.txt)download
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
#
# The MySQL database server configuration file.
#
# You can copy this to one of:
# - "/etc/mysql/my.cnf" to set global options,
# - "~/.my.cnf" to set user-specific options.
#
# One can use all long options that the program supports.
# Run program with --help to get a list of available options and with
# --print-defaults to see which it would actually understand and use.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html

# This will be passed to all mysql clients
# It has been reported that passwords should be enclosed with 
# ticks/quotes escpecially if they contain "#" chars...
# Remember to edit /etc/mysql/debian.cnf when changing 
# the socket location.
[client]
port        = 3306
#socket     = /var/run/mysqld/mysqld.sock

# Here is entries for some specific programs
# The following values assume you have at least 32M ram

# This was formally known as [safe_mysqld]. Both versions 
# are currently parsed.
[mysqld_safe]
#socket     = /var/run/mysqld/mysqld.sock
#nice       = 0

[mysqld]
#
# * Basic Settings
#

#
# * IMPORTANT
#   If you make changes to these settings and your system uses 
#   apparmor, you may also need to also adjust 
#   /etc/apparmor.d/usr.sbin.mysqld.
#

#user       = mysql
#socket     = /var/run/mysqld/mysqld.sock
port        = 3306
#basedir    = /usr
datadir    = /usr/local/var/mysql
#tmpdir     = /tmp
skip-external-locking
#
# Instead of skip-networking the default is now to listen only on
# localhost which is more compatible and is not less secure.
bind-address        = 127.0.0.1
#
# * Fine Tuning
#
key_buffer          = 16M
max_allowed_packet  = 16M
thread_stack        = 192K
thread_cache_size   = 8
# This replaces the startup script and checks MyISAM tables if needed
# the first time they are touched
myisam-recover         = BACKUP
#max_connections       = 100
#table_cache           = 64
#thread_concurrency    = 10
#
# * Query Cache Configuration
#
query_cache_limit   = 1M
query_cache_size    = 16M
#
# * Logging and Replication
#
# Both location gets rotated by the cronjob.
# Be aware that this log type is a performance killer.
# As of 5.1 you can enable the log at runtime!
#general_log_file        = /var/log/mysql/mysql.log
#general_log             = 1

log_error                = /usr/local/var/mysql/MacBook15.local.err

# Here you can see queries with especially long duration
#log_slow_queries   = /var/log/mysql/mysql-slow.log
#long_query_time = 2
#log-queries-not-using-indexes
#
# The following can be used as easy to replay backup logs or 
# for replication.
# note: if you are setting up a replication slave, see 
#       README.Debian about other settings you may need 
#       to change.
#server-id          = 1
#log_bin            = /var/log/mysql/mysql-bin.log
expire_logs_days    = 10
max_binlog_size     = 100M
#binlog_do_db       = include_database_name
#binlog_ignore_db   = include_database_name
#
# * InnoDB
#
# InnoDB is enabled by default with a 10MB datafile in /var/lib/mysql/.
# Read the manual for more InnoDB related options. There are many!
#
# * Security Features
#
# Read the manual, too, if you want chroot!
# chroot = /var/lib/mysql/
#
# For generating SSL certificates I recommend the OpenSSL GUI "tinyca".
#
# ssl-ca=/etc/mysql/cacert.pem
# ssl-cert=/etc/mysql/server-cert.pem
# ssl-key=/etc/mysql/server-key.pem

# Query Caching
query-cache-type = 1

# Default to InnoDB
default-storage-engine=innodb

[mysqldump]
quick
quote-names
max_allowed_packet  = 16M

[mysql]
#no-auto-rehash # faster start of mysql but no tab completition

[isamchk]
key_buffer      = 16M

错误日志

错误日志默认会存在数据目录下,也就是上面所定义的 /usr/local/var/mysql/,如果 Mac 电脑名字是 MacBook,那日志的全路径就是 /usr/local/var/mysql/MacBook.local.err

让别的电脑访问数据库

取消下面两个文件中关于绑定 127.0.0.1 的语句
/etc/my.cnf

1
bind-address = 127.0.0.1

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

1
<string>--bind-address=127.0.0.1</string>

就我个人而言,不需要 MySQL 自启动,所以只要在 /etc/my.cnf 改一下就好了。

Tagged as: Comments Off
1Mar/16

killing of MySQL processlist(清理mysql所有队列)

Posted by Nick Xu

mysql> select concat('KILL ',id,';') from information_schema.processlist where user='root' and time > 3 into outfile '/tmp/a.txt';
mysql> source /tmp/a.txt;
6Sep/15

mysql 添加第N个从库

Posted by Nick Xu

如果主服务器已经存在应用数据,则在进行主从复制时,做以下处理:

授权给从数据库服务器192.168.10.131
mysql> GRANT REPLICATION SLAVE ON *.* to 'rep1'@'192.168.10.131' identified by ‘password’;
(1)主数据库进行锁表操作,不让数据再进行写入动作
mysql> FLUSH TABLES WITH READ LOCK;

(2)查看主数据库状态
mysql> show master status;

(3)记录下 FILE 及 Position 的值。
将主服务器的数据文件(整个/opt/mysql/data目录)复制到从服务器,建议通过tar归档压缩后再传到从服务器解压。
或者用mysqldump的方式导出sql文件

(4)取消主数据库锁定
mysql> UNLOCK TABLES;

 

某项目mysql服务器 1主 1从 ,现在要添加一个mysql从服务器,要求主库不能停止服务,以前由于不是线上的服务器,可以在主服务器上 执行 flush tables with read lock 语句(锁表,只读),所有的表只能读不能写,然后再拷贝主库数据到新的从库服务器上,并保持数据一致性,现在只能换一种方法了,思路 新从库2拷贝老的从库1的数据!

老从库1 相关操作:

#1 停止 mysql从库,锁表,
记住 Read_Master_Log_Pos: 与 Master_Log_File: (红色字)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
mysql> stop slave;
mysql> flush tables with read lock;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State:
                  Master_Host: 192.168.6.53
                  Master_User: dongnan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000097
          Read_Master_Log_Pos: 19157395
               Relay_Log_File: zabbix-slave-relay-bin.000185
                Relay_Log_Pos: 11573578
        Relay_Master_Log_File: mysql-bin.000097
             Slave_IO_Running: No
            Slave_SQL_Running: No
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 19157395
              Relay_Log_Space: 19142103
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: NULL
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified

#2 打包数据并发送到新从库2服务器上

1
2
3
mysqldump -uroot -p --databases a b c d e f > data.sql
tar czvf data.tar.gz data.sql
scp zabbix_20110805.tar.gz root@192.168.6.54:/root

新从库2相关操作:

#1 更改 server-id 值不能为1,因为master 的 server-id=1

  1. vim /etc/my.cnf
  2. server-id = 3

#2 导入mysql数据库使用 --init-command="SET SQL_LOG_BIN = 0;" 参数可以避免初次导数据时产生巨大binlog文件

mysql --init-command="SET SQL_LOG_BIN = 0;" -u root -p < data.sql

#3 启动mysql数据库并change master

Exec_Master_Log_Pos 值  19157395

Master_Log_File 值 mysql-bin.000097

 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
mysql> change master to master_host='192.168.6.53',master_user='dongnan',master_password='password',master_log_file='mysql-bin.000097',master_log_pos=19157395;
mysql> start slave;  #启动slave
mysql> show slave status\G;  #显示slave 状态
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.53
                  Master_User: dongnan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000097
          Read_Master_Log_Pos: 21194359
               Relay_Log_File: db1-relay-bin.000002
                Relay_Log_Pos: 2037215
        Relay_Master_Log_File: mysql-bin.000097
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB:
          Replicate_Ignore_DB:
           Replicate_Do_Table:
       Replicate_Ignore_Table:
      Replicate_Wild_Do_Table:
  Replicate_Wild_Ignore_Table:
                   Last_Errno: 0
                   Last_Error:
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 21194359
              Relay_Log_Space: 2037368
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File:
           Master_SSL_CA_Path:
              Master_SSL_Cert:
            Master_SSL_Cipher:
               Master_SSL_Key:
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
1 row in set (0.00 sec)
ERROR:
No query specified

验证重库是否同步:

老从库1

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
mysql> unlock tables;
mysql> start slave;
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.53
                  Master_User: dongnan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000097
          Read_Master_Log_Pos: 21194359
               Relay_Log_File: db1-relay-bin.000002
                Relay_Log_Pos: 2037215
        Relay_Master_Log_File: mysql-bin.000097
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

新从库2

1
2
3
4
5
6
7
8
9
10
11
12
13
14
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.6.53
                  Master_User: dongnan
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000097
          Read_Master_Log_Pos: 21194359
               Relay_Log_File: db1-relay-bin.000002
                Relay_Log_Pos: 2037215
        Relay_Master_Log_File: mysql-bin.000097
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes

 

如果遇到错误尝试停止slave设置以下参数后再启动

SET GLOBAL SQL_SLAVE_SKIP_COUNTER = 1;

结束

既然主库不能动,那就去操作从库吧,新从库2拷贝老的从库1的数据!

补充:
主库创建同步账号: grant replication slave on *.* to 'repl'@'%' identified by 'repl';
[mysqld]
log_slave_updates=1 #此配置允许链式复制, 当你需要链式复制的时候,如A->B->C,你就必须在B主机上的mysql中添加一条配置
log_bin = /home/logs/mysql/mysql-bin
expire_logs_days = 7
max_binlog_size = 100M
binlog-ignore-db=mysql
binlog-ignore-db=information_schema
binlog_format=ROW

#从库配置
replicate-ignore-db=mysql
replicate-ignore-db=information_schema
slave-skip-errors=1064 1146 1062 1032

 

 

 

mysqldump -u root -pPassword --all-databases | ssh user@new_host.host.com 'cat - | mysql -u root -pPassword'

 

12Dec/13

SET 和 SHOW语法(一)

Posted by Nick Xu

SET 可以用于设置各种变量或者选项。

SHOW 可以以各种形式描述数据库系统的信息,诸如数据表,字段,服务器状态等等。大致有以下这些方法:

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']
SHOW CREATE DATABASE db_name
SHOW CREATE TABLE tbl_name
SHOW DATABASES [LIKE 'pattern']
SHOW [STORAGE] ENGINES
SHOW ERRORS [LIMIT [offset,] row_count]
SHOW GRANTS FOR user
SHOW INDEX FROM tbl_name [FROM db_name]
SHOW INNODB STATUS
SHOW [BDB] LOGS
SHOW PRIVILEGES
SHOW [FULL] PROCESSLIST
SHOW STATUS [LIKE 'pattern']
SHOW TABLE STATUS [FROM db_name] [LIKE 'pattern']
SHOW [OPEN] TABLES [FROM db_name] [LIKE 'pattern']
SHOW [GLOBAL | SESSION] VARIABLES [LIKE 'pattern']
SHOW WARNINGS [LIMIT [offset,] row_count]

如果 SHOW 语法中包括 LIKE 'pattern' 分句的话,'pattern' 是一个可以包括 SQL 语法中‘ % '和‘ _ '等通配符的字符串。这个模式语句对于取得相应的结果十分有用。

请注意,还有以下几种不同的标识方式:

 

    • SET PASSWOR 语句用于设定帐户的密码,详情请看“14.5.1.3 SET PASSWORDSyntax”。
    • SHOW 语句有以下几种方式用于描述复制主服务器和从服务器的信息:

 

  • SHOW BINLOG EVENTS
    SHOW MASTER LOGS
    SHOW MASTER STATUS
    SHOW SLAVE HOSTS
    SHOW SLAVE STATUS

    这些格式的 SHOW 详细描述详情请看“14.6 Replication Statements”。

 

14.5.3.1 SET 语法

SET variable_assignment [, variable_assignment] ...

variable_assignment:
      user_var_name = expr
    | [GLOBAL | SESSION] system_var_name = expr
    | @@[global. | session.]system_var_name = expr

SET 可以设置各种不同的变量类型,这些变量可以影响着服务器或者客户端的参数。它可以帮助你设置用户变量或者是系统变量。
在 MySQL 4.0.3 中,我们增加了 GLOBAL 和 SESSION 选项,并且可以在运行时动态修改系统中最重要的参数。这些可以被动态修改的系统参数详情请看“5.2.3.1 Dynamic System Variables”中会被详细描述到。
在老版本的 MySQL 中,SET 其实是 SET OPTION 的代替,它少了 OPTION 这个参数,不过现在不推荐的使用方法。
以下这个例子演示了几种使用设置变量的方法。

用户变量写成 @var_name,用以下方法来设置:

SET @var_name = expr;

用户变量的特性说明将在章节“10.3 User Variables”中提到。

系统变量在 SET 语法中可以以 var_name 的方式来引用。变量名前面用 GLOBAL 或@@global. 来表示这是一个全局变量,用 SESSION , @@session. 或 @@ 来表示这是一个会话变量。 LOCAL 和 @@local. 其实 SESSION 和 @@session. 属同义。如果变量名前面没有附加的修饰词的话,那么 SET 只修改会话变量。

以 @@var_name这种格式来表示系统变量,在 MySQL 中是可以支持的,以兼容其它数据库系统。

如果你想在同一个语句中来修改多个系统变量,最后一次出现的 GLOBAL 或 SESSION将作为该语句中没有修饰词的变量的默认选项。

SET sort_buffer_size=10000;
SET @@local.sort_buffer_size=10000;
SET GLOBAL sort_buffer_size=1000000, SESSION sort_buffer_size=1000000;
SET @@sort_buffer_size=1000000;
SET @@global.sort_buffer_size=1000000, @@local.sort_buffer_size=1000000;

如果你设置了系统变量时用 SESSION (默认的) 选项,那么它的值将在会话结束前或者直到你改变它之前一直有效。如果设置系统变量是用 GLOBAL 选项,这必须要求你具备 SUPER (超级) 权限,它的值从新的连接开始到系统重启前一直有效。如果你想要一个变量永久生效的话,那么可以把它放到配置文件中。详情请看“4.3.2 Using Option Files”。

为了防止操作错误,例如如果你想用 SET GLOBAL 修改一个只能用 SET SESSION 来修改的变量,或者不是用 SET GLOBAL 的方式来修改一个全局变量时, MySQL 就会报告一个错误。

如果你想让一个 SESSION (会话) 变量的值设定成为一个 GLOBAL 变量的值,或者想让 GLOBAL 变量的值变成MySQL内嵌编译的默认值的话,你可以设置它为DEFAULT (默认值) 。以下的这 2 个例子都是同样的设置会话变量max_join_size 的值成为全局变量的值:

SET max_join_size=DEFAULT;
SET @@session.max_join_size=@@global.max_join_size;

你可以通过 SHOW VARIABLES 命令列出绝大部分的系统变量。详情请看“14.5.3.20SHOW VARIABLES Syntax”的语法 。如果想要通过模式匹配的方式来获取变量列表的话,可以使用 LIKE 分句:

SHOW VARIABLES LIKE 'max_join_size';
SHOW GLOBAL VARIABLES LIKE 'max_join_size';

你还可以使用 SELECT @@[global.|local.]var_name 来取得指定变量的值:

SELECT @@max_join_size, @@global.max_join_size;

在通过 SELECT @@var_name ( 这样的话,就无需指定是 global 还是 session 抑或是 local 了 ) 来取得变量的值时, MySQL 会返回 SESSION 变量的值,如果它不存在,则返回 GLOBAL 变量的值。

以下所列的是那些非标准或者是在章节“5.2.3 Server System Variables”中找没有列出来的变量。尽管这些变量用 SHOW VARIABLES 并不显示出来,但你可以用 SELECT (CHARACTER SET 和 SET NAMES 除外 ) 来取得它们的值。例如:

mysql> SELECT @@AUTOCOMMIT;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+

 

 

AUTOCOMMIT = {0 | 1}
设置 autocommit (自动提交)模式。若设置为 1 ,则所有对数据表的操作都会立即生效。若设置为 0 ,则必须执行 COMMIT 以提交事务或者执行 ROLLBACK取消事务。如果把 AUTOCOMMIT 由 0 改成 1 , MySQL 会自动提交任何一个开放的事务。开始一个事务的另一个方法可以是执行 START TRANSACTION 或BEGIN 语句。详情请看“14.4.1 START TRANSACTIONCOMMIT, andROLLBACK Syntax”。 

BIG_TABLES = {0 | 1}
如果被设置为 1,则所有的临时表将存放在磁盘里而非内存。这会使速度变得稍微慢了点,但是在执行 SELECT 操作时需要一个大临时表的情况下就不再会报告The table tbl_name is full 错误了。对于新连接,它的默认值是 0(在内存中存放临时表) 。从 MySQL 4.0 起,你无需设置这个变量,因为 MySQL 会自动在需要的情况下将内存存储的表转换为硬盘存储的表。这个变量现在的名字是 SQL_BIG_TABLES 。 

CHARACTER SET {charset_name | DEFAULT}
它用于将从客户端取得的以及发往客户端的字符串都映射成指定的字符串。在 MySQL 4.1 以前,唯一允许的字符集是 cp1251_koi8 ,不过你也可以通过修改 MySQL 发布的源代码中的 `sql/convert.cc' 文件来增加新的转换字符集。从 MySQL 4.1.1 开始,SET CHARACTER SET 将设置 3 个会话系统变量:character_set_client 和 character_set_results 会被设置成指定的字符集, character_set_connection 的值被设置成character_set_database 的值。默认的转换字符集可以被重置为值DEFAULT (默认值) 。请注意,SET CHARACTER SET 的语法和其它大多数设置选项的语法不一样。 

FOREIGN_KEY_CHECKS = {0 | 1}
如果它设置为 1 (默认值) ,则 InnoDB 表都会检查外键约束关系。如果设置为0 ,则会略过,不做检查。禁止外键约束关系检查在重新加载和实际所要求的父子关系顺序不一致顺序的 InnoDB 表时非常有用。这个变量是在 MySQL 3.23.52 中新增的。详情请看“16.7.4 FOREIGN KEY Constraints”。
IDENTITY = value
这个变量和变量 LAST_INSERT_ID 实际上是一样的。它的存在是为了与其它数据库系统兼容。从 MySQL 3.23.25 开始,可以通过执行 SELECT @@IDENTITY来获得它的值。从 MySQL 4.0.3 开始,还可以执行 SET IDENTITY 来改变它的值。 

INSERT_ID = value
修改它的值可以作为在执行 INSERT 或 ALTER TABLE 语句中要插入一个AUTO_INCREMENT 值时的下一个值。它主要用于二进制日志(binary log)。 

LAST_INSERT_ID = value
修改它的值可以作为 LAST_INSERT_ID() 的返回值。当数据表被更新时它的值会存放在二进制日志中,可以执行 LAST_INSERT_ID() 语句来取得它的值。设置它的值不会改变 C API 函数 mysql_insert_id() 返回的结果值。 

NAMES {'charset_name' | DEFAULT}
SET NAMES 设置了 3 个会话变量 character_set_client ,character_set_connection 和 character_set_results 为指定的字符集。默认的映射关系可以设定为 DEFAULT。请注意, SET NAMES 语法也不同于大多数其它的设置选项。它是自从 MySQL 4.1.0 以后才开始有的。 

SQL_AUTO_IS_NULL = {0 | 1}
如果被设置为 1 ( 默认值 ) ,则可以通过以下语句来获得某个包含AUTO_INCREMENT 类型字段的表最后一条插入的记录:

WHERE auto_increment_column IS NULL

这种做法在某些 ODBC 程序中会被用到,例如 Access 。 SQL_AUTO_IS_NULL变量是在 MySQL 3.23.52 中新增的。

 

SQL_BIG_SELECTS = {0 | 1}
如果设置为 0 ,则 MySQL 会放弃那些可能会耗费很长时间(是指那些通过优化程序估计到需要检查的行数会超过 max_join_size 设定值的情况)的 SELECT语句。这在有不可取的 WHERE 语句出现时十分有用。 每个新连接的SQL_BIG_SELECTS 默认值为 1 ,以允许执行所有的 SELECT 语句。如果设置系统变量 max_join_size 为非默认值( DEFAULT ),则SQL_BIG_SELECTS 也会被自动设置成为 0。 

SQL_BUFFER_RESULT = {0 | 1}
SQL_BUFFER_RESULT 强制将 SELECT 语句查询的结果放在临时表中。这可以让 MySQL 尽快释放加载表上的锁,同时还有助于改善向客户端发送结果需要较长时间的情况。这个变量是在 MySQL 3.23.13 中新增的。
 

SQL_LOG_BIN = {0 | 1}
如果设置为 0,就不再记录任何二进制日志了。必须要有 SUPER (超级) 权限方可修改它的值。这个变量是在 MySQL 3.23.13 中新增的。 

SQL_LOG_OFF = {0 | 1}
如果设置为 1 ,系统将不记录任何普通的查询日志。必须要有 SUPER (超级) 权限方可修改它的值。 

SQL_LOG_UPDATE = {0 | 1}
如果设置为 0 ,更新日志将不记录任何日志。必须要有 SUPER (超级) 权限方可修改它的值。这个变量是在 MySQL 3.22.5 中新增的。从 MySQL 5.0.0 开始,它已经建议不再使用,而变成了 SQL_LOG_BIN ( 详情请看“C.1.3 Changes in release 5.0.0 (22 Dec 2003: Alpha” ) 。 

SQL_QUOTE_SHOW_CREATE = {0 | 1}
如果设置为 1 ,则在执行 SHOW CREATE TABLE 时就会把数据表名和字段用引号引起来;如果设置为 0 ,就不再引用了。这个选项默认是打开的,因为在数据库同步时需要所有的表名和字段名都必须引用起来才行。这个选项是在 MySQL 3.23.26 中新增的。详细的请查看“14.5.3.6 SHOW CREATE TABLE Syntax”。 

SQL_SAFE_UPDATES = {0 | 1}
如果设置为 1 ,则 MySQL 会放弃那些在 WHERE 或 LIMIT 分句中没有使用键的UPDATE 或 DELETE 语句。这就可能会捕获那些没有正确使用键并且可能会删除很多记录的 UPDATE 或 DELETE 语句。这个变量是在 MySQL 3.22.32 中新增的。 

SQL_SELECT_LIMIT = {value | DEFAULT}
它决定了执行 SELECT 语句时返回的最大记录数。新连接的默认设置值是“ unlimited (无限)”。如果它被改变了,可以设定重新设 SQL_SELECT_LIMIT的值定为 DEFAULT 以将它恢复为默认值。当 SELECT 语句中有 LIMIT 分句时, LIMIT 优先级高于 SQL_SELECT_LIMIT 的值。 

SQL_WARNINGS = {0 | 1}
它决定了在执行单行 INSERT 语句(译者注:一次只有一个 INSERT 语句)发生错误的情况下,是否要报告错误信息。它的默认值是 0 ,如果设置为 1 ,则会在发生错误时报告错误信息。这个变量是在 MySQL 3.22.11 中新增的。 

TIMESTAMP = {timestamp_value | DEFAULT}
它可以设定系统时间。它用于取二进制日志用于恢复记录时来取得原始时间戳。timestamp_value 必须是 Unix 时间戳,而不是 MySQL 时间戳。 

UNIQUE_CHECKS = {0 | 1}
如果设定为 1 (默认值),就会对 InnoDB 表的第二索引做唯一性检查。如果设置为 0 ,就不再做唯一性检查。这个变量是在 MySQL 3.23.52 中新增的。详情请看“16.7.4 FOREIGN KEY Constraints”。

 

14.5.3.2 SHOW CHARACTER SET 语法

SHOW CHARACTER SET [LIKE 'pattern']

SHOW CHARACTER SET 列出了所有可用的字符集。它有一个可选项是 LIKE 分句以示匹配哪些字符集。例如:

mysql> SHOW CHARACTER SET LIKE 'latin%';
+---------+-----------------------------+-------------------+--------+
| Charset | Description                 | Default collation | Maxlen |
+---------+-----------------------------+-------------------+--------+
| latin1  | ISO 8859-1 West European    | latin1_swedish_ci |      1 |
| latin2  | ISO 8859-2 Central European | latin2_general_ci |      1 |
| latin5  | ISO 8859-9 Turkish          | latin5_turkish_ci |      1 |
| latin7  | ISO 8859-13 Baltic          | latin7_general_ci |      1 |
+---------+-----------------------------+-------------------+--------+

Maxlen 字段显示了储存一个字符使用的最大字节数。

SHOW CHARACTER SET 是在 MySQL 4.1.0 中新增的。

14.5.3.3 SHOW COLLATION 语法

SHOW COLLATION [LIKE 'pattern']

SHOW COLLATION 的结果包括了所有可用的字符集。它有一个可选项是 LIKE 分句以示匹配哪些校对。例如:

mysql> SHOW COLLATION LIKE 'latin1%';
+-------------------+---------+----+---------+----------+---------+
| Collation         | Charset | Id | Default | Compiled | Sortlen |
+-------------------+---------+----+---------+----------+---------+
| latin1_german1_ci | latin1  |  5 |         |          |       0 |
| latin1_swedish_ci | latin1  |  8 | Yes     | Yes      |       0 |
| latin1_danish_ci  | latin1  | 15 |         |          |       0 |
| latin1_german2_ci | latin1  | 31 |         | Yes      |       2 |
| latin1_bin        | latin1  | 47 |         | Yes      |       0 |
| latin1_general_ci | latin1  | 48 |         |          |       0 |
| latin1_general_cs | latin1  | 49 |         |          |       0 |
| latin1_spanish_ci | latin1  | 94 |         |          |       0 |
+-------------------+---------+----+---------+----------+---------+

Default 字段表示其是否为该字符集的默认校对。Compiled 字段表示该校对是否预编译到服务器中了。Sortlen 字段表示需要多少内存用来对字符集中的字符串表示进行排序。
SHOW COLLATION 是在 MySQL 4.1.0 中新增的。

14.5.3.4 SHOW COLUMNS 语法

SHOW [FULL] COLUMNS FROM tbl_name [FROM db_name] [LIKE 'pattern']

SHOW COLUMNS 列出了给定表的所有字段。请注意,如果看到的字段类型和你以前用CREATE TABLE 创建的字段类型不同的话,可能是 MySQL 有时候会在创建或修改表结构时修改字段类型。什么情况下 MySQL 会改变字段类型详情请看“14.2.6.1 Silent Column Specification Changes”。

从 MySQL 3.23.32 开始,就可以使用关键字 FULL 了。它使得输出的结果包括了当前用户对每个字段所具备的权限。从 MySQL 4.1 开始,关键字 FULL 也会令每个字段的注释显示出来。

还可以选择是否使用 db_name.tbl_name 的语法格式。以下的这2个语句是一样的:

mysql> SHOW COLUMNS FROM mytable FROM mydb;
mysql> SHOW COLUMNS FROM mydb.mytable;

SHOW FIELDS 和 SHOW COLUMNS 是一样的。同时还可以通过命令 mysqlshowdb_name tbl_name 来列出所有的字段。

DESCRIBE 语句和 SHOW COLUMNS 显示的结果信息类似。详情请看“14.3.1DESCRIBE Syntax (Get Information About Columns)”。

 

14.5.3.5 SHOW CREATE DATABASE 语法

SHOW CREATE DATABASE db_name

根据给定的数据库显示一个完整的 CREATE DATABASE 语句。它是在 MySQL 4.1 中新增的。

mysql> SHOW CREATE DATABASE test\G
*************************** 1. row ***************************
       Database: test
Create Database: CREATE DATABASE `test`
                 /*!40100 DEFAULT CHARACTER SET latin1 */

 

14.5.3.6 SHOW CREATE TABLE 语法

SHOW CREATE TABLE tbl_name

根据给定的数据表显示一个完整的 CREATE TABLE 语句。它是在 MySQL 3.23.20 中新增的。

mysql> SHOW CREATE TABLE t\G
*************************** 1. row ***************************
       Table: t
Create Table: CREATE TABLE t (
  id INT(11) default NULL auto_increment,
  s char(60) default NULL,
  PRIMARY KEY (id)
) TYPE=MyISAM default charset utf8

SHOW CREATE TABLE 根据参数 SQL_QUOTE_SHOW_CREATE 的值来决定是否要把表名和字段名用引号引用起来。详情请看“14.5.3.1 SET Syntax”。

Tagged as: Comments Off
3Dec/13

Ubuntu下使用Pure-ftpd 透過 MySQL管理虛擬FTP用戶帳號

Posted by Nick Xu

應用趨勢
Pure-ftpd是一個很方便管理的FTP伺服器
我們利用它來提供基本的FTP服務是很容易的
在 Ubuntu 下它有幾個安裝類型選擇
(他們間彼此互斥..只能裝一類)

1.原始而單純的用法(和系統的實體帳號有關)
這種用法您只要安裝 pure-ftpd 就好了
sudo apt-get install pure-ftpd

2.配合 MySQL資料庫 作為認證的用法(和系統實體帳號無關)
這種用法您要安裝 pure-ftpd-mysql
sudo apt-get install pure-ftpd-mysql

3.配合 PostgreSQL資料庫 作為認證的用法(和系統實體帳號無關)
這種用法您要安裝 pure-ftpd-postgresql
sudo apt-get install pure-ftpd-postgresql

4.配合 LDAP資料庫 作為認證的用法(和系統實體帳號無關)
這種用法您要安裝 pure-ftpd-ldap
sudo apt-get install pure-ftpd-ldap

其實 proftpd 也有類似的作法..它可以提供的配合類型
還要多2種
proftpd-mod-ldap proftpd-mod-pgsql
proftpd-mod-mysql proftpd-mod-sqlite
proftpd-mod-odbc

OK 瞭解 FTP 伺服器的應用趨勢後
我們再回過神來專注在 我們今天的主題之內
利用 pure-ftpd-mysql 去建立一個具有虛擬帳號管理功能的FTP伺服器

虛擬帳號

先談談虛擬帳號,為何不要用真實帳號
在使用 Linux 主機時,有人要傳送檔案到伺服器上
一般我們會開立一個實體帳號,讓這個使用者使用
而這樣的作法,通常讓這個帳號可以登入到主機內,
而這些帳號,通常也是駭客下手的目標之一
(一般的使用者注重安全的程度通常遠低於系統管理員)

當然的,他也能在上面執行一些程式或作其他的事。
一般Linux主機是難以被攻擊的,但是如果攻擊者
有這樣的權限,可以合法的進到主機內時,他要取得
最高管理員的權限只是時間的問題了。
所以一般的管理員不輕易的將實體帳號開設出去。
因此建立一個只讓虛擬帳號可以上傳下載的 FTP 伺服器
變的更能符合資訊安全的須求。

OK!瞭解了這些背景,我們就向實作的階段出發吧!

實作過程

一、基本設定

1.Ubuntu 下安裝 pure-ftpd-mysql
打開終端機或連線到您的 Ubuntu 主機
輸入下列指令
sudo apt-get install pure-ftpd-mysql
它就會將必要的相關套件一併裝上

2.設定安全性
PS:接下的操作您只要複製英文指令到終端機內執行即可
sudo -i
先變為 root

echo "yes" > /etc/pure-ftpd/conf/ChrootEveryone
意思是建立一個ChrootEveryone的檔案內容為yes
就是限制 FTP 登入後的帳號只能在自己的家目錄內活動

echo "no" > /etc/pure-ftpd/conf/PAMAuthentication
不由PAM認證,僅單純的透過MySQL去認證
如果這裡改為"yes",則實體帳號者也能登入。

echo "no" > /etc/pure-ftpd/conf/UnixAuthentication
不由系統認證,僅單純的透過MySQL去認證

echo "yes" > /etc/pure-ftpd/conf/NoAnonymous
不讓匿名用戶登入

echo "yes" > /etc/pure-ftpd/conf/CreateHomeDir
登入後沒有家目錄會自行建立(似乎只能用在實體帳號上)

上述配置後請重新啟動
/usr/bin/service pure-ftpd-mysql restart

上述綠色部分為您要在終端機輸入的指令
到目前為止是沒有任何人可以登入FTP的,
因為我們連資料庫的連線都還沒設定,PureFTP根本還連不到資料庫,
更不用說可以取到資料表的帳號資料去認證了。

二、資料庫設定與配置

資料庫連線的配置檔在/etc/pure-ftpd/db/mysql.conf 內
(檔案內所有前面有#號的行表示是註解)
所以這個檔案雖然內容很多,但其實只有下面幾行才是被使用到的設定:
下面是一個 Ubuntu 系統安裝 pure-ftpd-mysql 後
在/etc/pure-ftpd/db/mysql.conf 內資料庫連線配置檔的預設值。

MYSQLSocket /var/run/mysqld/mysqld.sock
MYSQLUser root
MYSQLPassword rootpw
MYSQLDatabase pureftpd
MYSQLCrypt cleartext
MYSQLGetPW SELECT Password FROM users WHERE User="\L"
MYSQLGetUID SELECT Uid FROM users WHERE User="\L"
MYSQLGetGID SELECT Gid FROM users WHERE User="\L"
MYSQLGetDir SELECT Dir FROM users WHERE User="\L"

以下我們逐行解說其設定內容:
MYSQLSocket /var/run/mysqld/mysqld.sock
這行說明 MySQL 接口的位置,除非您是自行編譯的..否則不須改變它。

MYSQLUser root
這行是指連線到資料庫的帳號,我們可以依照採用root帳號即可。

MYSQLPassword rootpw
這行是指連線到資料庫帳號所用的密碼,您要配合您的資料庫密碼作改變
這是基本用法下..您唯一要變更的地方。

MYSQLDatabase pureftpd
這行是指連線到資料庫後,要使用的資料庫名稱,如果您依本文操作您也不須作改變。

MYSQLCrypt cleartext
這行是指虛擬用戶的密碼,要使用何者編碼方式,如果您依本文操作您也不須作改變。
可用的設定值有下面幾個
cleartext 明碼(不額外編碼) md5 使用MD5編碼加密 crypt 用DES編碼加密
password MySQL的編碼加密
any 指可以視資料庫欄位定義而自動擇用md5、crypt或 password三種編碼方式。

接下4行設定都是資料庫語法。
MYSQLGetPW SELECT Password FROM users WHERE User="\L"
上述表示當虛擬帳號符合User欄位時,選取登入者的密碼欄。

MYSQLGetUID SELECT Uid FROM users WHERE User="\L"
上述表示當虛擬帳號符合User欄位時,選取登入者的使用者ID。
當使用者登入後儲存檔案都會有這種使用者ID、為了安全性起見
您要避免用系統使用的ID。

MYSQLGetGID SELECT Gid FROM users WHERE User="\L"
上述表示當虛擬帳號符合User欄位時,選取登入者的群組ID。

MYSQLGetDir SELECT Dir FROM users WHERE User="\L"
上述表示當虛擬帳號符合User欄位時,選取登入者的家目錄(檔案存取目錄)。
這意味著我們可以在資料表的 Dir 欄位內,任意設定使用者的檔案存取目錄。
好夢幻的功能,真令人激賞。

上述的用法都只用到了"\L",而"\L"表示通過認證後的使用者帳號。
除了"\L"外,pure-ftpd 還提供了 \I、\P、\R、\D 四個符號。
分別表示如下:
\I (目的)虛擬帳號連線的IP
\P (目的)虛擬帳號連線通訊埠(21埠)
\R (來源)虛擬帳號從那個IP連線過來的
\D (來源)虛擬帳號從那個IP連線過來的,同上,但以數位表示。

依上述所言,我們認證的資料庫 pureftpd 內,
須有一個資料表叫users,資料表內
至少須具有五個欄位 User,Password,Uid,Gid,Dir
這樣才能運作起來,下面為整理出的建表語法。

CREATE TABLE `users` (
`User` varchar(16) NOT NULL DEFAULT '',
`Password` varchar(64) NOT NULL DEFAULT '',
`Uid` varchar(11) NOT NULL DEFAULT '-1',
`Gid` varchar(11) NOT NULL DEFAULT '-1',
`Dir` varchar(250) NOT NULL DEFAULT '',
PRIMARY KEY (`User`),
) ENGINE=MyISAM DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;

搞清楚這些先前知識後,接著我們要開始來建立資料庫及資料表了

[操作指令]
mysql -u root -p
Enter password: (輸入資料庫root密碼後按確定)

在大於提示符下輸入
CREATE DATABASE pureftpd;[按確定]
USE pureftpd;[按確定]

將建表語法複製貼上後再按確定

回顧這部分我們僅須做3個動作

1.改設定檔內的資料庫密碼
2.登入到資料庫內建立一個pureftpd資料庫
3.建立資料表

這三部分都完成了,您可以重新啟動pureftp伺服器
service pure-ftpd-mysql restart

接著您可以簡便的利用phpMyAdmin去建立帳號
再利用建立的帳號測試登入看看。

If you want to use uid and gid of www-data, it doesn't work. Because by fault in Debian the minimum uid is 1000. You will see the following error.

Client side:

$ ftp example.com
Connected to example.com.
220---------- Welcome to Pure-FTPd [privsep] [TLS] ----------
220-You are user number 1 of 50 allowed.
220-Local time is now 14:28. Server port: 21.
220-This is a private system - No anonymous login
220-IPv6 connections are also welcome on this server.
220 You will be disconnected after 15 minutes of inactivity.
Name (example.com:haoyu): site.example.com
331 User site.example.com OK. Password required
Password:
530 Sorry, but I can't trust you
Login failed.
Remote system type is UNIX.
Using binary mode to transfer files.
Server side in /var/log/ftp.log:

Sep 17 14:46:25 sd-xxxxx pure-ftpd: (?@defxx-x-xx-xxx-xx-xxx.fbx.proxad.net) [INFO] New connection from defxx-x-xx-xxx-xx-xxx.fbx.proxad.net
Sep 17 14:46:32 sd-xxxxx pure-ftpd: (?@defxx-x-xx-xxx-xx-xxx.fbx.proxad.net) [WARNING] Can't login as [site.example.com]: account disabled
The solution is to modify /etc/pure-ftpd/conf/MinUID value from 1000 to 33. I suggest to disable PAM authentication (system user login) by write “no” in file /etc/pure-ftpd/conf/PAMAuthentication. :!:Don't forget to restart Pure-FTPd server.

[ERROR] Home directory not available - aborting

Even you have put 'yes' in /etc/pure-ftpd/conf/CreateHomeDir, Pure-FTPd will not create the user root directory, and you got ”[ERROR] Home directory not available - aborting” in your syslog. The solution is change the owner to root of the last existing directory in the home path. For example:
The user root directory is /home/ftp/user01 and /home/ftp should own be root

# ls -al /home/
# drwxr-xr-x 4 root ftp 4096 jui 4 21:57 ftp

Tagged as: , , , Comments Off
5Jun/12

mysql cluster 7 动态增加数据节点配置步骤

Posted by Nick Xu

vi /usr/local/mysql/mysql-cluster/config.ini

[ndbd default]

DataMemory =100M

IndexMemory =100M

NoOfReplicas = 2

DataDir = /usr/local/mysql/var/mysql-cluster

[ndbd]

Id = 1

HostName = 172.20.86.188

 

[ndbd]

Id = 2

HostName = 172.20.86.189

 

[mgm]

HostName = 172.20.86.185

Id = 10

 

[mysqld]

Id=20

HostName = 172.20.86.185

 

2在185上运行ndb_mgm

Ndb_mgm>show

-- NDB Cluster -- Management Client --
ndb_mgm>SHOW
Connected to Management Server at: 172.20.86.185:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=1   @172.20.86.188 (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)
id=2   @172.20.86.189 (5.1.32-ndb-7.0.5, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=10  @172.20.86.185 (5.1.32-ndb-7.0.5)

[mysqld(API)]  2 node(s)
id=20  @172.20.86.185 (5.1.32-ndb-7.0.5)

 

 

Step 1: Update configuration file

 

[ndbd default]
DataMemory =100M
IndexMemory =100M
NoOfReplicas = 2
DataDir = /usr/local/mysql/var/mysql-cluster

[ndbd]
Id = 1
HostName = 172.20.86.188

[ndbd]
Id = 2
HostName = 172.20.86.189

[ndbd]
Id = 3
HostName=172.20.86.187
 
[ndbd]
Id = 4
HostName=172.20.86.186

[mgm]
HostName = 172.20.86.185
Id = 10

[mysqld]
Id=20
HostName = 172.20.86.185

 

 

Step 2: Restart the management server

Step 2.1停止管理节点

ndb_mgm>10 STOP
Node 10 has shut down.
Disconnecting to allow Management Server to shutdown
shell>

Step 2.2重新加载配置文件

shell>ndb_mgmd -f config.ini --reload
2008-12-08 17:29:23 [MgmSrvr] INFO    -- NDB Cluster Management Server.5.1.34-ndb-7.0.7
2008-12-08 17:29:23 [MgmSrvr] INFO    --Readingcluster configuration from 'config.ini'

Step 2.3查看新集群状态

-- NDB Cluster -- Management Client --
ndb_mgm>SHOW
Connected to Management Server at: 172.20.86.185:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=1   @172.20.86.188 (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)
id=2   @172.20.86.189 (5.1.32-ndb-7.0.5, Nodegroup: 0)
id=3 (not connected, accepting connect from 172.20.86.186)
id=4 (not connected, accepting connect from 172.20.86.187)

[ndb_mgmd(MGM)] 1 node(s)
id=10  @172.20.86.185 (5.1.32-ndb-7.0.5)

[mysqld(API)]  1 node(s)
id=20  @172.20.86.185 (5.1.32-ndb-7.0.5)

 

Step 3: Perform. a rolling restart of the existing data nodes –轮流重新启动连接的数据节点

ndb_mgm>1 RESTART
Node 1: Node shutdown initiated
Node 1: Node shutdown completed, restarting, no start.
Node 1 is being restarted

ndb_mgm> Node 1: Start initiated (version7.0.5)
Node 1: Started (version7.0.5)

ndb_mgm>2 RESTART
Node 2: Node shutdown initiated
Node 2: Node shutdown completed, restarting, no start.
Node 2 is being restarted

 

Step 4: Perform. a rolling restart of all cluster API nodes –轮流重启sql节点

本案仅有一个sql节点

shell>mysqladmin -uroot -ppasswordshutdown
shell>mysqld_safe &

 

Step 5: Perform. an initial start of the new data nodes –初始化新的数据节点

 

在新增的数据节点上186和187上执行

Shell>ndbmtd –initial

或者

Shell>ndbd –initial

登陆管理节点查看集群状态

Connected to Management Server at: 172.20.86.185:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=1   @172.20.86.188 (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)
id=2   @172.20.86.189 (5.1.32-ndb-7.0.5, Nodegroup: 0)
id=3   @172.20.86.186 (5.1.32-ndb-7.0.5,no nodegroup)
id=4   @172.20.86.187 (5.1.32-ndb-7.0.5,no nodegroup)

[ndb_mgmd(MGM)] 1 node(s)
id=10  @172.20.86.185 (5.1.32-ndb-7.0.5)

[mysqld(API)]  2 node(s)
id=20  @172.20.86.185 (5.1.32-ndb-7.0.5)

Step 6: Create a new node group.

ndb_mgm>CREATE NODEGROUP 3,4
Nodegroup 1 created
 
ndb_mgm>SHOW
Connected to Management Server at: 172.20.86.185:1186
Cluster Configuration
---------------------
[ndbd(NDB)]    2 node(s)
id=1   @172.20.86.188 (5.1.32-ndb-7.0.5, Nodegroup: 0, Master)
id=2   @172.20.86.189 (5.1.32-ndb-7.0.5, Nodegroup: 0)
id=3   @172.20.86.186 (5.1.32-ndb-7.0.5,Nodegroup: 1)
id=4   @172.20.86.187 (5.1.32-ndb-7.0.5,Nodegroup: 1)

[ndb_mgmd(MGM)] 1 node(s)
id=10  @172.20.86.185 (5.1.32-ndb-7.0.5)

[mysqld(API)]  1 node(s)
id=20  @172.20.86.185 (5.1.32-ndb-7.0.5)

Step 7: Redistribute cluster data. –数据的重新分配

 

对于新增的数据节点一些访问量较高的数据和比较重要的数据需要从新分配。

 

分发数据命令:

Alter online table ips reorganize partition;

查看数据的分配情况:

ndb_mgm>ALL REPORT MEMORY

Node 1: Data usage is 5%(177 32K pages of total 3200)
Node 1: Index usage is 0%(108 8K pages of total 12832)
Node 2: Data usage is 5%(177 32K pages of total 3200)
Node 2: Index usage is 0%(108 8K pages of total 12832)
Node 3: Data usage is 0%(0 32K pages of total 3200)
Node 3: Index usage is 0%(0 8K pages of total 12832)
Node 4: Data usage is 0%(0 32K pages of total 3200)
Node 4: Index usage is 0%(0 8K pages of total 12832)

或者

shell>ndb_desc -c 192.168.0.10 -d n ips -p
-- ips --
Version: 1
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 6
Number of primary keys: 1
Length of frm data: 340
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 2
TableStatus: Retrieved
-- Attributes --
id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
country_code Char(2;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
type Char(4;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
ip_address Varchar(15;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
addresses Bigunsigned NULL AT=FIXED ST=MEMORY
date Bigunsigned NULL AT=FIXED ST=MEMORY

-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex

-- Per partition info --
Partition  Row count  Commit count Frag fixed memory  Frag varsized memory
0          26086      26086        1572864            557056
1          26329      26329        1605632            557056

NDBT_ProgramExit: 0 - OK

You can cause the data to be redistributed among all of the data nodes by performing, for eachNDBCLUSTERtable, anALTER ONLINE TABLE ... REORGANIZE PARTITIONstatement in themysqlclient. After issuing the statementALTER ONLINE TABLE ips REORGANIZE PARTITION, you can see usingndb_descthat the data for this table is now stored using 4 partitions, as shown here (with the relevant portions of the output in bold type):

shell>ndb_desc -c 172.20.86.185 -d n ips -p
-- ips --
Version: 16777217
Fragment type: 9
K Value: 6
Min load factor: 78
Max load factor: 80
Temporary table: no
Number of attributes: 6
Number of primary keys: 1
Length of frm data: 341
Row Checksum: 1
Row GCI: 1
SingleUserMode: 0
ForceVarPart: 1
FragmentCount: 4
TableStatus: Retrieved
-- Attributes --
id Bigint PRIMARY KEY DISTRIBUTION KEY AT=FIXED ST=MEMORY AUTO_INCR
country_code Char(2;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
type Char(4;latin1_swedish_ci) NOT NULL AT=FIXED ST=MEMORY
ip_address Varchar(15;latin1_swedish_ci) NOT NULL AT=SHORT_VAR ST=MEMORY
addresses Bigunsigned NULL AT=FIXED ST=MEMORY
date Bigunsigned NULL AT=FIXED ST=MEMORY

-- Indexes --
PRIMARY KEY(id) - UniqueHashIndex
PRIMARY(id) - OrderedIndex

-- Per partition info --
Partition  Row count  Commit count Frag fixed memory  Frag varsized memory
0          12981      52296        1572864            557056
1          13236      52515        1605632            557056
2          13105      13105        819200             294912
3          13093      13093        819200             294912
Tagged as: , No Comments
4Jun/12

在同一台主机配置MySQL Cluster

Posted by Nick Xu

MySQL cluster是share nothing的集群,采用ndb存储引擎,和Oracle的RAC采用集中存储不同,是一种分布式的架构,所以可以很轻松的实现在同一台主机上配置MySQL Cluster,相对RAC来说要简单得多。

在MySQL Cluster中,有三种不同的节点:

  • 管理节点:守护进程为ndb_mgmd,用于管理cluster。
  • 数据节点: 守护进程为ndbd,用于存放数据
  • SQL节点:守护进程为mysqld,就是一个MySQL实例,对外供客户端连接访问数据。

本文将在一台64位linux主机上同时配置上述三种节点。在实际生产环境中,根据需要,可以在相同或者不同的机器部署节点。本次实例中共包含以下节点:

  • 一个管理节点
  • 两个数据节点
  • 三个SQL节点

下载Mysql cluster,当前版本7.0.6,http://dev.mysql.com/downloads/cluster/7.0.html

解压,并复制到安装目录

tar zxvf mysql-cluster-gpl-7.0.6-linux-x86_64-glibc23.tar.gz
cp mysql-cluster-gpl-7.0.6-linux-x86_64-glibc23 /u01/mysql-cluster

cd /u01/mysql-cluster

创建三个MySQL实例的数据目录

mkdir data/data1
mkdir data/data2
mkdir data/data3

mkdir data/data1/mysql
mkdir data/data1/test

mkdir data/data2/mysql
mkdir data/data2/test

mkdir data/data3/mysql
mkdir data/data3/test

Cluster配置文件如下

$ more conf/config.ini
[ndbd default]
noofreplicas=2
[ndbd]
hostname=localhost
id=2
[ndbd]
hostname=localhost
id=3
[ndb_mgmd]
id = 1
hostname=localhost
[mysqld]
id=4
hostname=localhost
[mysqld]
id=5
hostname=localhost
[mysqld]
id=6
hostname=localhost

三个MySQL实例配置文件如下

$ more conf/my1.cnf
[mysqld]
ndb-nodeid=4
ndbcluster=true
datadir=/u01/mysql-cluster/data/data1
basedir=/u01/mysql-cluster
port=9306
server-id=1
log-bin

$ more conf/my2.cnf
[mysqld]
ndb-nodeid=5
ndbcluster=true
datadir=/u01/mysql-cluster/data/data2
basedir=/u01/mysql-cluster
port=9307
server-id=2
log-bin

$ more conf/my3.cnf
[mysqld]
ndb-nodeid=6
ndbcluster=true
datadir=/u01/mysql-cluster/data/data3
basedir=/u01/mysql-cluster
port=9308
server-id=3
log-bin

初始化三个MySQL实例

script/mysql_install_db --basedir=/u01/mysql-cluster --datadir=/u01/mysql-cluster/data/data1
script/mysql_install_db --basedir=/u01/mysql-cluster --datadir=/u01/mysql-cluster/data/data2
script/mysql_install_db --basedir=/u01/mysql-cluster --datadir=/u01/mysql-cluster/data/data3

初始化管理节点

bin/ndb_mgmd --initial -f conf/config.ini --configdir=/u01/mysql-cluster

进入管理节点查看配置

$ bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> help
---------------------------------------------------------------------------
 NDB Cluster -- Management Client -- Help
---------------------------------------------------------------------------
HELP                                   Print help text
HELP COMMAND                           Print detailed help for COMMAND(e.g. SHOW)
SHOW                                   Print information about cluster
CREATE NODEGROUP ,...          Add a Nodegroup containing nodes
DROP NODEGROUP                     Drop nodegroup with id NG
START BACKUP [NOWAIT | WAIT STARTED | WAIT COMPLETED]
START BACKUP [] [NOWAIT | WAIT STARTED | WAIT COMPLETED]
START BACKUP [] [SNAPSHOTSTART | SNAPSHOTEND] [NOWAIT | WAIT STARTED | WAIT COMPLETED]
                                       Start backup (default WAIT COMPLETED,SNAPSHOTEND)
ABORT BACKUP                Abort backup
SHUTDOWN                               Shutdown all processes in cluster
CLUSTERLOG ON [] ...         Enable Clus
 = ALERT | CRITICAL | ERROR | WARNING | INFO | DEBUG
 = STARTUP | SHUTDOWN | STATISTICS | CHECKPOINT | NODERESTART | CONNECTION
| INFO | ERROR | CONGESTION | DEBUG | BACKUP
    = 0 - 15
       = ALL | Any database node id

For detailed help on COMMAND, use HELP COMMAND.

ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2 (not connected, accepting connect from localhost)
id=3 (not connected, accepting connect from localhost)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @localhost  (mysql-5.1.34 ndb-7.0.6)

[mysqld(API)]   3 node(s)
id=4 (not connected, accepting connect from localhost)
id=5 (not connected, accepting connect from localhost)
id=6 (not connected, accepting connect from localhost)

初始化数据节点

bin/ndbd --initial -c localhost:1186
2009-06-21 23:13:19 [ndbd] INFO     -- Configuration fetched from 'localhost:1186', generation: 1
bin/ndbd --initial -c localhost:1186
2009-06-21 23:13:50 [ndbd] INFO     -- Configuration fetched from 'localhost:1186', generation: 1

启动SQL节点

bin/mysqld --defaults-file=conf/my1.cnf&
[1] 17843
 090621 23:14:36 [Warning] No argument was provided to --log-bin,
and --log-bin-index was not used; so replication may break when this MySQL server acts as a master
and has his hostname changed!! Please use '--log-bin=test-bin' to avoid this problem.
InnoDB: The first specified data file ./ibdata1 did not exist:
InnoDB: a new database to be created!
090621 23:14:36  InnoDB: Setting file ./ibdata1 size to 10 MB
InnoDB: Database physically writes the file full: wait...
090621 23:14:36  InnoDB: Log file ./ib_logfile0 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile0 size to 5 MB
InnoDB: Database physically writes the file full: wait...
090621 23:14:36  InnoDB: Log file ./ib_logfile1 did not exist: new to be created
InnoDB: Setting log file ./ib_logfile1 size to 5 MB
InnoDB: Database physically writes the file full: wait...
InnoDB: Doublewrite buffer not found: creating new
InnoDB: Doublewrite buffer created
InnoDB: Creating foreign key constraint system tables
InnoDB: Foreign key constraint system tables created
090621 23:14:36  InnoDB: Started; log sequence number 0 0
090621 23:14:36 [Note] NDB: NodeID is 4, management server 'localhost:1186'
090621 23:14:37 [Note] NDB[0]: NodeID: 4, all storage nodes connected
090621 23:14:37 [Note] Starting Cluster Binlog Thread
090621 23:14:37 [Note] Event Scheduler: Loaded 0 events
090621 23:14:37 [Note] bin/mysqld: ready for connections.
Version: '5.1.34-ndb-7.0.6-cluster-gpl-log'  socket: '/tmp/mysql.sock'
port: 9306  MySQL Cluster Server (GPL)
090621 23:14:38 [Note] NDB Binlog: Ndb tables initially read only.
090621 23:14:38 [Note] NDB: Creating mysql.ndb_schema
090621 23:14:38 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_schema
090621 23:14:38 [Note] NDB Binlog: logging ./mysql/ndb_schema (UPDATED,USE_WRITE)
090621 23:14:38 [Note] NDB: Creating mysql.ndb_apply_status
090621 23:14:39 [Note] NDB Binlog: CREATE TABLE Event: REPL$mysql/ndb_apply_status
090621 23:14:39 [Note] NDB Binlog: logging ./mysql/ndb_apply_status (UPDATED,USE_WRITE)
2009-06-21 23:14:39 [NdbApi] INFO     -- Flushing incomplete GCI:s < 20/3
2009-06-21 23:14:39 [NdbApi] INFO     -- Flushing incomplete GCI:s < 20/3
090621 23:14:39 [Note] NDB Binlog: starting log at epoch 20/3
090621 23:14:39 [Note] NDB Binlog: ndb tables writable
bin/mysqld --defaults-file=conf/my2.cnf&
bin/mysqld --defaults-file=conf/my3.cnf&

查看cluster

bin/ndb_mgm
-- NDB Cluster -- Management Client --
ndb_mgm> show
Connected to Management Server at: localhost:1186
Cluster Configuration
---------------------
[ndbd(NDB)]     2 node(s)
id=2    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0, Master)
id=3    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6, Nodegroup: 0)

[ndb_mgmd(MGM)] 1 node(s)
id=1    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)

[mysqld(API)]   3 node(s)
id=4    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
id=5    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)
id=6    @127.0.0.1  (mysql-5.1.34 ndb-7.0.6)

创建测试表

bin/mysql -uroot -h localhost -P 9306  --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 2
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

root@(none) 11:21:24>use test
Database changed
root@test 11:21:26>create table test_ndb(i int,a varchar(20),primary key(i)) engine=ndb;
090621 23:22:32 [Note] NDB Binlog: CREATE TABLE Event: REPL$test/test_ndb
090621 23:22:32 [Note] NDB Binlog: logging ./test/test_ndb (UPDATED,USE_WRITE)
090621 23:22:32 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/test_ndb
090621 23:22:32 [Note] NDB Binlog: DISCOVER TABLE Event: REPL$test/test_ndb
090621 23:22:32 [Note] NDB Binlog: logging ./test/test_ndb (UPDATED,USE_WRITE)
090621 23:22:32 [Note] NDB Binlog: logging ./test/test_ndb (UPDATED,USE_WRITE)
Query OK, 0 rows affected (1.10 sec)

root@test 11:22:32>insert into test_ndb values(1,'NinGoo');
Query OK, 1 row affected (0.02 sec)

[mysql@dbconsole mysql-cluster]$ ls -l data/data1/test
total 12
-rw-rw----  1 mysql dba 8578 Jun 21 23:22 test_ndb.frm
-rw-rw----  1 mysql dba    0 Jun 21 23:22 test_ndb.ndb
[mysql@dbconsole mysql-cluster]$ ls -l data/data2/test
total 12
-rw-rw----  1 mysql dba 8578 Jun 21 23:22 test_ndb.frm
-rw-rw----  1 mysql dba    0 Jun 21 23:22 test_ndb.ndb
[mysql@dbconsole mysql-cluster]$ ls -l data/data3/test
total 12
-rw-rw----  1 mysql dba 8578 Jun 21 23:22 test_ndb.frm
-rw-rw----  1 mysql dba    0 Jun 21 23:22 test_ndb.ndb

$ bin/mysql -uroot -P 9307 --socket=/tmp/mysql.sock
Welcome to the MySQL monitor.  Commands end with ; or g.
Your MySQL connection id is 3
Server version: 5.1.34-ndb-7.0.6-cluster-gpl-log MySQL Cluster Server (GPL)

Type 'help;' or 'h' for help. Type 'c' to clear the current input statement.

root@(none) 11:24:38>use test
Reading table information for completion of table and column names
You can turn off this feature to get a quicker startup with -A

Database changed
root@test 11:24:41>select * from test_ndb;
+---+--------+
| i | a      |
+---+--------+
| 1 | NinGoo |
+---+--------+
1 row in set (0.02 sec)

无觅相关文章插件,快速提升流量

Tagged as: , , No Comments
11May/11

MySQLdb for Python使用指南/Python的数据库操作

Posted by Nick Xu

网站就是要和数据库进行交互,否则什么都不用做了...今天我们来看一个叫MySQLdb的库,这个用来和MySQL数据库进行交互.
可以从这里获得这个库
http://sourceforge.net/projects/mysql-python
如果你不确定你的python环境里有没有这个库,那就打开python shell,输入 import MySQLdb,如果返回错误信息,那就表示你的机器上没有,赶紧去下载一个.我的机器是win xp,所以我下载了win环境下的exe那个,直接双击完成安装.

在介绍具体的操作前,先花点时间来说说一个程序怎么和数据库进行交互
1.和数据库建立连接
2.执行sql语句,接收返回值
3.关闭数据库连接
使用MySQLdb也要遵循上面的几步.让我们一步步的进行.

0.引入MySQLdb库
import MySQLdb

1.和数据库建立连接
conn=MySQLdb.connect(host="localhost",user="root",passwd="sa",db="mytable")
提供的connect方法用来和数据库建立连接,接收数个参数,返回连接对象.

比较常用的参数包括
host:数据库主机名.默认是用本地主机.
user:数据库登陆名.默认是当前用户.
passwd:数据库登陆的秘密.默认为空.
db:要使用的数据库名.没有默认值.
port:MySQL服务使用的TCP端口.默认是3306.
更多关于参数的信息可以查这里
http://mysql-python.sourceforge.net/MySQLdb.html

然后,这个连接对象也提供了对事务操作的支持,标准的方法
commit() 提交
rollback() 回滚

2.执行sql语句和接收返回值
cursor=conn.cursor()
n=cursor.execute(sql,param)
首先,我们用使用连接对象获得一个cursor对象,接下来,我们会使用cursor提供的方法来进行工作.这些方法包括两大类:1.执行命令,2.接收返回值

cursor用来执行命令的方法:
callproc(self, procname, args):用来执行存储过程,接收的参数为存储过程名和参数列表,返回值为受影响的行数
execute(self, query, args):执行单条sql语句,接收的参数为sql语句本身和使用的参数列表,返回值为受影响的行数
executemany(self, query, args):执行单挑sql语句,但是重复执行参数列表里的参数,返回值为受影响的行数
nextset(self):移动到下一个结果集

cursor用来接收返回值的方法:
fetchall(self):接收全部的返回结果行.
fetchmany(self, size=None):接收size条返回结果行.如果size的值大于返回的结果行的数量,则会返回cursor.arraysize条数据.
fetchone(self):返回一条结果行.
scroll(self, value, mode='relative'):移动指针到某一行.如果mode='relative',则表示从当前所在行移动value条,如果mode='absolute',则表示从结果集的第一行移动value条.

下面的代码是一个完整的例子.
#使用sql语句,这里要接收的参数都用%s占位符.要注意的是,无论你要插入的数据是什么类型,占位符永远都要用%s
sql="insert into cdinfo values(%s,%s,%s,%s,%s)"
#param应该为tuple或者list
param=(title,singer,imgurl,url,alpha)
#执行,如果成功,n的值为1
n=cursor.execute(sql,param)

#再来执行一个查询的操作
cursor.execute("select * from cdinfo")
#我们使用了fetchall这个方法.这样,cds里保存的将会是查询返回的全部结果.每条结果都是一个tuple类型的数据,这些tuple组成了一个tuple
cds=cursor.fetchall()
#因为是tuple,所以可以这样使用结果集
print cds[0][3]
#或者直接显示出来,看看结果集的真实样子
print cds

#如果需要批量的插入数据,就这样做
sql="insert into cdinfo values(0,%s,%s,%s,%s,%s)"
#每个值的集合为一个tuple,整个参数集组成一个tuple,或者list
param=((title,singer,imgurl,url,alpha),(title2,singer2,imgurl2,url2,alpha2))
#使用executemany方法来批量的插入数据.这真是一个很酷的方法!
n=cursor.executemany(sql,param)

需要注意的是(或者说是我感到奇怪的是),在执行完插入或删除或修改操作后,需要调用一下conn.commit()方法进行提交.这样,数据才会真正保存在数据库中.我不清楚是否是我的mysql设置问题,总之,今天我在一开始使用的时候,如果不用commit,那数据就不会保留在数据库中,但是,数据确实在数据库呆过.因为自动编号进行了累积,而且返回的受影响的行数并不为0.

3.关闭数据库连接
需要分别的关闭指针对象和连接对象.他们有名字相同的方法
cursor.close()
conn.close()

三步完成,基本的数据库操作就是这样了.下面是两个有用的连接
MySQLdb用户指南: http://mysql-python.sourceforge.net/MySQLdb.html
MySQLdb文档: http://mysql-python.sourceforge. ... MySQLdb-module.html

site
site