MySQL基础知识

MySQL基础知识,入门知识

解决MySQL Replication出错一例

os: redhat as4
master: MySQL 4.0.23
slave : MySQL 5.0.37

slave日志中报错信息如下:

060807 11:40:17 [ERROR] While trying to obtain the list of 
slaves from the master 'xxx.xxx.xxx:3306', user 'rep' got the
 following error: 'Access denied. You need the REPLICATION SLAVE
 privilege for this operation'

在master上,执行以下语句查看权限:

mysql>SHOW GRANT FOR 'rep'@'192.168.0.2'\G
*************************** 1. row ***************************
Grants for rep@192.168.0.2: GRANT SELECT, REPLICATION SLAVE ON
 *.* TO 'rep'@'192.168.0.2' IDENTIFIED BY PASSWORD 'xxx'

已经授予了 REPLICAION SLAVE 权限了,怎么还会报这个错呢?

Questions and Answers During the First Session

原文来自: http://onlinesolutionsmysql.blogspot.com/2007/03/questions-and-answers-during-first.html

On Server and Storage Engines

Q from Alessandro: What about the filesystem for MyISAM in a small company? Better ReiserFS or EXT3?
As Anders said, it's a matter of taste. XFS is a good alternative too. Ext3 is stable, but sometimes slow and a bit outdated. Many like Reiser, but it is sometimes difficult to manage. XFS often gives you good performance and reliability out of the box.

Q from Florian: MySQL 5.1 == Falcon?

技术相关: 

Questions and Answers in the Second Session of the Online Solutions with MySQL Webinar - On Replication

原文来自: http://onlinesolutionsmysql.blogspot.com/2007/05/questions-and-answers-in-second-session.html

Correction on the INSERT DELAYED
In slide 20 I have mentioned that the INSERT DELAYED statement can increase performance on the slave. This is wrong, since the DELAYED keyword is ignored by the SQL thread on the slave server. The INSERT DELAYED statement can increase the overall performance of an application since the control is returned to the client as soon as the row is queued into the list of inserts to execute. The INSERT DELAYED can be used with MyISAM, MEMORY and ARCHIVE.

Q from Filip: Does master & slave have to be the same db-version, and the same Operative system?

技术相关: 

"log_bin.index not found" 启动报错解决

my.cnf 中设置了:

log-bin=log_bin
log-bin-index=log_bin.index

但是启动后,总是报告如下错误:

mysqld: File './log_bin.index' not found (Errcode: 13) 
070428 14:35:59 [ERROR] Aborting 

070428 14:35:59 [Note] mysqld: Shutdown complete

自己 touch 一个文件出来,不行。
修改文件 log_bin.index 的权限为 777,也不行。

[root@localhost]#/usr/local/mysql/bin/perror 13

System error:  13 = Permission denied

看来还是权限的问题,突然想起来 datadir 权限设置可能不对。

[root@localhost]#ls -l /usr/local/mysql | grep data

drw-------   3 nobody nobody  4096 Apr 29 11:17 data
技术相关: 

Falcon 存储引擎设计窥探

原文出自: http://www.mysqlperformanceblog.com/2007/01/12/falcon-storage-engine-des...

Falcon Storage Engine Design Review

Now as new MySQL Storage engine - Falcon is public I can write down my thought about its design, which I previously should have kept private as I partially got them while working for MySQL.

These thought base on my understanding, reading docs, speaking to Jim, Monty, Arjen and other people so I might miss something, something might be planned to be changed or not 100% correct but anyway you might find it interesting.

In many cases what I find good or bad would base of my MySQL use with existing applications - if you design new applications which are done specially for Falcon you might find those aspects positive.

技术相关: 

mysqldump死住(实际是导致mysqld crash)

在MySQL 5.0.16上,用mysqldump导出数据,mysqldump半天没反应。
操作系统是RHEL4.0。

看日志
*** glibc detected *** free(): invalid pointer: 0xb17d60b0 ***
mysqld got signal 6;
This could be because you hit a bug. It is also possible that this binary
or one of the libraries it was linked against is corrupt, improperly built,
or misconfigured. This error can also be caused by malfunctioning hardware.
We will try our best to scrape up some info that will hopefully help diagnose
the problem, but since we have already crashed, something is definitely wrong
and this may fail.

key_buffer_size=8388600
read_buffer_size=131072
max_used_connections=2
max_connections=100
threads_connected=2
It is possible that mysqld could use up to
key_buffer_size + (read_buffer_size + sort_buffer_size)*max_connections = 225791 K

技术相关: 
个人涂鸦: 

如何去除数据表中的重复数据

 通常情况下,一个我们在做一个产品的时候,一开始可能由于设计考虑不周或者程序写的不够严谨,某个字段上的值产生重复了,但是又必须去掉,这个时候就稍微麻烦了一点,直接加一个 UNIQUE KEY 肯定是不行了,因为会报错。
 现在,我们来采用一种变通的办法,不过可能会丢失一些数据 :)

 在这里,我们设定一个表,其结构如下:

mysql> desc `user`;
+-------+------------------+------+-----+---------+----------------+
| Field | Type             | Null | Key | Default | Extra          |
+-------+------------------+------+-----+---------+----------------+
| id    | int(10) unsigned | NO   | PRI | NULL    | auto_increment |
技术相关: 

MySQL 4.0 升级到5.0

由于需要,从4.0直接升级到5.0,查看了一下changelog,发现主要有以下变化:

一、从 4.0 到 4.1 的主要变化

  • 如果在4.1.0到4.1.3版本的MySQL中创建了包含 TIMESTAMP 字段的 InnoDB
    表。则在升级到4.1.4及更高时需要重建表,因为存储格式发生变化了
  • 字符串根据标准SQL来比较:比较之前不删除末尾的空格,以前用末尾空格扩展了比较短的字符串。现在的结果是
    'a' > 'a\t',以前则不这样。可以用 mysqlcheck 来检查一下数据表
  • TIMESTAMP 返回 'YYYY-MM-DD HH:MM:SS' 格式的字符串。在MySQL
    4.0中,可以增加选项 --new 来获得MySQL 4.1中这方面的特性
  • 在MySQL
    4.1.1前,语句解析器不是那么严格,它在处理字符串转时间转换时会忽略第一个数字前的其他字符。在4.1.1之后,就比较严格了
  • 返回结果是 DATE, DATETIME, 或 TIME 类型的函数的结果会被转换成时间型

二、再看从 4.1 到 5.0 的主要变化

  • InnoDB 和 MyISAM 表中空格结尾的 TEXT 字段索引顺序改变了。因此需要运行
    "CHECK TABLE" 语句修复数据表,如果出现错误,就运行 "OPTIMIZE TABLE" 或 "REPAIR
    TABLE" 语句修复,甚至重新转储(用mysqldump)
  • MySQL 5.0.15开始,如何处理 BINARY 字段中填充的值已经改变了。填充的值现在是
    0x00 而非空格了,并且在取值的时候不会去除末尾的空格
  • 从MySQL 5.0.3开始,DECIMAL 的实现方式已经改变了,5.0对 DECIMAL
    的格式限制严格多了
  • 在MySQL 5.0.3到5.0.5之间版本的 MyISAM 和 InnoDB 表中创建的 DECIMAL
    字段升级到5.0.6之后会发生崩溃
  • 在以前,等待超时的锁会导致 InnoDB
    回滚当前全部事务,从5.0.13开始,就只回滚最近的SQL语句了
  • 在4.1.13/5.0.8以前,DATETIME 的加0后就转换成 YYYYMMDDHHMMSS 格式,现在变成
    YYYYMMDDHHMMSS.000000 格式了
  • 从5.0.3开始,DECIMAL 用更有效的格式来存储
  • 5.0.3开始,在计算 DECIMAL 值和舍入精确值的时候采用精确数学
  • 4.1中,FLOAT 或 DOUBLE 之间的比较碰巧没问题,但在5.0中可能就不行了
  • 从5.0.3开始,VARCHAR 和 VARBINARY 字段中末尾的空格不再删除
  • 增加了一个新的启动选项 innodb_table_locks,它导致 LOCK TABLE 时也可以请求
    InnoDB 表锁。这个选项默认打开,不过可能在 AUTOCOMMIT=1 和 LOCK TABLES
    应用中会导致死锁
技术相关: 

MySQL 许可常见问题(MySQL Licensing FAQ)

原文摘录自: http://dev.mysql.com/tech-resources/articles/mysql-network-and-you.html,作者: Arjen Lentz(Community Relations Manager of MySQL AB)

End users

最终用户

If you are an end-user, you can use a GPL licensed server and will not require a commercial license to use MySQL. This applies even if you are an ISP or if you sell services on your e-commerce web site, since you are not distributing anything. If you are distributing anything, please see under the Developer heading below.

技术相关: 

页面

Subscribe to RSS - MySQL基础知识