mysqldump 的Tips

发表于2010年08月13日由吴炳锡

作者：吴炳锡　来源:http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注:译者和出处，并且不能用于商业用途，违者必究.

只导出表结构：
mysqldump -d –trigger=false
只导出存储过程：
mysqldump -f -Rtdn –triggers=false
只导出触发器：
mysqldump -f -tdn –triggers
只导出事件：
mysqldump -f -Etdn –triggers=false
只导出数据：
mysqldump -f –single-transaction –triggers=false -t

合理使用MySQL的Limit进行分页

发表于2009年11月4日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

今天看一个水友说他的MySQL现在变的很慢。问什么情况时。说单表超过2个G的一个MyISAM。真垃圾的回答方式。

简单答复：换一个强劲的服务器。换服务器很管用的：）

………
最终让取到慢查询：

    SELECT * FROM pw_gbook WHERE uid='N' ORDER BY postdate DESC LIMIT N,N;

如：

   SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;

        看到这个语句我都吐血了（BT的PHPWIND分页啊，这个语句是PHP初学者写出来的还正常，但PHPWIND那么成熟的社区了还有这样的问题）。
        我这里简单说一下LIMIT的原理。这里以LIMIT N,M为基础：LIMIT首先要找查N+M行，然后从N行处，取M行。那么这样的SQL对一次查询1275500一个操作应该是一个昂贵的开销。对于LIMIT这类的优化，第一个目标就是让N变的尽可能的小或是不用。
     怎么才能使这个N尽可能小呢。我们能做的其实就是用相对的值，给分页一个提示。如现在我们看的是第5页，看完看想看第6页，第6页同样显示是20条记录。我们就可以想到，以这个例子为准：我们可以肯定的是第6页的日值应小于第5页的，如果第5页的最小日值为：2009-11-4，那我们就可以用：

     SELECT * FROM pw_gbook WHERE uid='48' and postdate<’2009-11-1’ ORDER BY postdate DESC LIMIT  20;

这样来查询第6页的内容。同样对于查看第4页的内容（假设第5页的最大日期为：2009-11-3）则第4页的内容为：

     SELECT * FROM pw_gbook WHERE uid='48' and postdate>’2009-11-3’ ORDER BY postdate DESC LIMIT  20;

这是一个基本的思想。接下来讨论一下怎么展现的问题。

再说一下这种业务的SQL怎么实现：对于分页的展示可以用多用类型。这里说三种常用的类型：

第一种：显示“上一页” “下一页”这种类型

这种方式相对简单也就出现了我们看到那种SQL不思考的写法。合理的做法：

第一页：

     SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT  20;

第二页：根据第一页的postdate进行查询如：

      SELECT * FROM pw_gbook WHERE uid='48'  and postdate<’2009-11-3’  ORDER BY postdate DESC LIMIT  20;

为什么说这个简单呢，这个不存在跳页的问题。接下来这种就存在一个跳页的问题了。

第二种：显示 “ 1,2,3,4,5…”

第一页：还是以第一页的方式实现：

         SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT  20;

第二页：和原来一样。如果跳页，如从第二页跳到第5页，这里有一个第二页的最小日期为：2009-11-3（假设值，可以由第二页的程序查询得到），第二到第5，差2页，每页20条记录，那么就可以用：

SELECT * FROM pw_gbook WHERE uid='48'  and postdate<’2009-11-3’  ORDER BY postdate DESC LIMIT  40，20;

看到这里明白为什么大型网站的分页不是一下标识出来完了，让都能点了吧。也不会给你一个框让你输入一个页跳过去了。如果跳的页面过多，也就存在N值过大的问题了。所以要想办法必免。

第三种：显示 “1，2，3，4，5，…. 末页” 或是 “首页，<<100,101,102,103 >>末页”

这里有一个特殊的一地方：

别的页面的跳转的上面一样。这里就加一个末页，这里又分两种情况，如果知道最后一页是多少页，也就知道了前一页的最小日期（分页提示值），这样就可以用上面的方法查看最后一页的内容（会出现不足20条的现象），另一种，我就不知道最后是第几页，我就是想看看最后什么样子，那么就可以用（一定是显示20条）：

SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate ASC limit 20;

首页这里就不在说了。

具体怎么实现搞明白了，就可以做PHP代码的修改了。稍稍修改一下，就会带来意想不到的效果。

这里只是一个通用的分页处理方法。不同的业务有可能还有不同的方法处理。如果在条件可能和情况可以考用:between … and .. 带代替limit分页操作。

第三种方法：
简单的逻辑转换。

   SELECT * FROM pw_gbook WHERE uid='48' ORDER BY postdate DESC LIMIT 1275480,20;

转换成：

   SELECT * FROM pw_gbook WHERE id>1275480 and  uid='48' ORDER BY postdate DESC LIMIT 20;

本站提供专业：PHPWIND优化，DISCUZ优化，DRUPAL优化及相关咨询

Relay log read failure的处理

发表于2009年11月1日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式：select unhex(’777562696E67786940676D61696C2E636F6D’); 载请注明作/译者和出处，并且不能用于商业用途，违者必究。
众所周知MySQL5.1的Replication是比较烂的。MySQL的每一个版本更新关于同步方面每次都是可以看到一大堆。但MySQL 5.1性能是比较突出的。所以经不住诱惑使用MySQL 5.1。所以也要经常遇到一些Bug。如：

   
mysql> show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.10.118
                  Master_User: repl_wu
                  Master_Port: 3306
                Connect_Retry: 30
              Master_Log_File: mysql-bin.005121
          Read_Master_Log_Pos: 64337286
               Relay_Log_File: relay-bin.003995
                Relay_Log_Pos: 18446697137031827760
        Relay_Master_Log_File: mysql-bin.005121
             Slave_IO_Running: Yes
            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: 1594
                   Last_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 4
              Relay_Log_Space: 64337901
              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: 1594
               Last_SQL_Error: Relay log read failure: Could not parse relay log event entry. The possible reasons are: the master's binary log is corrupted (you can check this by running 'mysqlbinlog' on the binary log), the slave's relay log is corrupted (you can check this by running 'mysqlbinlog' on the relay log), a network problem, or a bug in the master's or slave's MySQL code. If you want to check the master's binary log or slave's relay log, you will be able to know their names by issuing 'SHOW SLAVE STATUS' on this slave.
1 row in set (0.00 sec)

从上面可以看到是中继日值或是Master上的日值出问题了。
首先如果是中继日值坏掉，那只需要找到同步的时间点，然后重新同步，这样就可以有新的中继日值了。如果Master上的日值坏了就麻烦了。
从经验来看，这是中继日值出问题了。处理方法：

需要找到同步的点。

日值为：Master_Log_File: mysql-bin.005121，Relay_Master_Log_File: mysql-bin.005121以Relay_Master_Log_File为准，Master_Log_File为参考。

日值执行时间点：

Exec_Master_Log_Pos: 4

那么现在就可以：

  
      mysql>stop slave;

    mysql>change master to Master_Log_File=’mysql-bin.005121’, Master_Log_Pos=4;
   
    mysql>start slave;

    mysql>show slave status\G;

进行确认。

建议：

在使用MySQL-5.1.36以下的版本的同学，请尽快升级到MySQL-5.1.40 & MySQL-5.1.37sp1

为什么mysql主从复制，从服务器的Time值诡异？

发表于2009年06月3日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式：select unhex(‘777562696E67786940676D61696C2E636F6D’); 载请注明作/译者和出处，并且不能用于商业用途，违者必究。

有两个原因：
第一个：这个情况我以前在4.X以前遇到过。后来升级到4.1后就没这个问题了。如果你的版本低于这个版本，有可能会这种情况。
第二个：有可能是你的网络不稳定。导至从服务器连接Master不稳定，从而返还的时间比较不准确。或是你的Master太忙了，Slave得到的时间有可能超时了。

这个时间表示：
Slave的SQL线程连接上Master的时间点和实际进行的SQL点的时间差别。例，当Slave和Master断开时间为30分钟，再时连上，这个时间为1800。
另一方面网络不稳也会出现问题。

大表删除数据的思路

发表于2009年05月25日由吴炳锡

对于一个2000W的大表，一次走一个全表扫描确是很困难的。不过对于100W的表走一个全描扫也困难。

如果做这件事情又不想影响太大，就要分步来完成。

我给你一个思路你来参考一下。
首先衡量删除操作有多大。
selet count(*) from table1 a ,table2 b where a.pid=b.pid;
如果都有索引的话，这个操作还是挺快的。
如果操作的操作大于原表的40%，那么很有必要重建这个表了。

重建表的方法：就是按条件提取数据到一个新表，最后改名完成。这是一种方案。

另一种删除方向。

上面通过比较觉的需要删除的量不是太大时，把需要删除的PID生到到另一个临时表中。
mysql DBname -e “select a.pid from table1 a ,table2 b where a.pid=b.pid”>del_pid.txt;

sed -i ‘1d’ del_pid.txt
awk ‘{print “delete from table1 where pid=”,$1,”;”}’ del_pid.txt >del_pid.sql
mysql DBname<del_pid.sql

这样把SQL拆成多个SQL执行速度应该不会太慢了。

如果还是感觉不行，那就只能分段操作了。

备注：http://bbs3.chinaunix.net/thread-1453362-1-1.html

mysql.user表怎么有两个root？密码怎么改？

发表于2009年05月14日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式：select unhex(‘777562696E67786940676D61696C2E636F6D’); 载请注明作/译者和出处，并且不能用于商业用途，违者必究。
MySQL的用户名有两部分组成：用户名＠机器名

用户名：一般用字母组成。
机器名：可以是机器ＩＰ也可以机器名。机器名可以用dns也可以在/etc/hosts中声明。

所以可以称为不是两个root用户。

反应到user表中,用户名对应user，机器名对应于host,密码对应于password
一个用户名完整的标识为：user@host

更改密码：
set password for ‘用户名’@’主机名‘=password(‘yourpassword’);

所以你见到的两个Ｒｏｏｔ不能算是一个用户名的。

注意：MySQL用户密码中的“！”

发表于2009年05月6日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

Tips
　　禁止用户终端进入的一个方法。

mysql> grant all privileges on wubx.* to ‘wubx’@’172.16.100.185’ identified by ‘fd52!wubx＆,’;
Query OK, 0 rows affected (0.00 sec)
mysql>quit;
#mysql -h 172.16.100.185 -u wubx -pfd52!wubx＆,
-bash: !wubx@,: event not found

仔细看一下，原来他把！后面的字符串做为命令执行了。又试了一个Navicat的管理端，也一样存在密码不正常的问题。
在测一下程序方面是不是可以用，写一个ＰＨＰ测一下。

$link = mysql_connect(‘172.16.100.185′,’wubx’,’fd52!wubx＆,’);
if (!link){
die(‘Could not connect:’.mysql_error());
}
echo ‘Connected successfully’;mysql_close($link);
?>
#php testdb.php
Connected successfully
还看程序中能正常识别。
PHP还是可以ＯＫ通过的。

大量unauthenticated user出现

发表于2009年05月6日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

首先查出来有那些ＩＰ连接mysql
mysql -e “show processlist”|awk ‘{print $3}’|sed -e ‘s/:.*$//’ |sort |uniq -c

记录一下这些ＩＰ

再收次一下那些ＩＰ出现　unauthenticated user
mysql -e “show processlist”|sed -s “/unauthenticated/”|awk ‘{print $3}’|sed -e ‘s/:.*$//’ |sort |uniq -c

多次执行，比较结果。
最终在/etc/hosts 里添加相应的ＩＰ及对应的名称。

参考建义(来自ＣＵ)：
1、skip-name-resolve 已经添加之后；最好在改用IP连接。
2、max_allowed_packet 参数可以适当调整。
3、MySQL 客户端库文件版本太低也有可能出现这个问题。
4、如php、java等应用端服务压力大，线程异常中断也会导致连接MySQL异常断开。

unsiged 数值运算　记录

发表于2009年05月6日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。


mysql> CREATE TABLE IF NOT EXISTS `ab`

`id` int(11) NOT NULL,

`id1` tinyint(3) unsigned DEFAULT NULL,

`id2` tinyint(3) unsigned DEFAULT NULL

) ENGINE=MyISAM DEFAULT CHARSET=utf8 ;

Query OK, 0 rows affected (0.00 sec)

mysql> insert into ab values(1,1,2);
Query OK, 1 row affected (0.00 sec)

mysql> select id1-id2 from ab;
+———————-+
| id1-id2 |
+———————-+
| 18446744073709551615 |
+———————-+
1 row in set (0.00 sec)

分析原因：

18446744073709551615
这个值是bigint　unsigned 的最大值。

MySQL内部的运算是按地址运算的。

二进制减法运算的原理:减去一个正数相当于加上一个负数A－B=A+(－B)，对(－B)求补码，然后进行加法运算。　符号也进用相应的位表示了。
补码：补码或是反码的最高位为符号位，正数为０，负数为１
　　　当二进制数为负数时，将原码的数值位逐位求反，然后在最低位加１得到补码。
　　　当二进制数为正数时，其补码，反码与原码相。

（补了一下二进制）

1-2
过程：　0000 00001 – 0000 0010 = 0000 0001 +(-0000 0010)
#在这个求补码的过程中，按整数最大值来求了。用８个字节表示了。
=0000 00001 + (11111111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1111 1101 + 1 )
=11111111 11111111 11111111 11111111 11111111 11111111 11111111 11111111

mysql> select hex(18446744073709551615);
+—————————+
| hex(18446744073709551615) |
+—————————+
| FFFFFFFFFFFFFFFF |
+—————————+
1 row in set (0.00 sec)

参考：
http://zhidao.baidu.com/question/36780022.html
http://www.mysqlsupport.cn/2009/04/mysql-4int-unsigned.html

Mysql 4　和５的int unsigned 区别

发表于2009年05月6日由吴炳锡

作者：吴炳锡　来源：http://www.mysqlsupport.cn/ 联系方式： wubingxi#gmail.com 转载请注明作/译者和出处，并且不能用于商业用途，违者必究。

所有整数类型可以有一个可选(非标准)属性UNSIGNED。当你想要在列内只允许非负数和该列需要较大的上限数值范围时可以使用无符号值。
mysql4:
mysql> create table wubx(a TINYINT unsigned not null default ‘0’);
Query OK, 0 rows affected (0.04 sec)
mysql> select * from wubx;
Empty set (0.00 sec)
mysql> insert wubx values(0);
Query OK, 1 row affected (0.00 sec)
mysql> select * from wubx;
+——+
| a |
+——+
| 0 |
+——+
1 row in set (0.00 sec)
mysql> update wubx set a=a-1;
Query OK, 0 rows affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 0 Warnings: 1
mysql> select * from wubx;
+——+
| a |
+——+
| 0 |
+——+
1 row in set (0.00 sec)

mysql5:

(root@localhost) [test]> create table wubx(a int(11) unsigned not null default ‘0’);
Query OK, 0 rows affected (18.44 sec)
(root@localhost) [test]> select * from wubx;
Empty set (0.00 sec)
(root@localhost) [test]> insert into wubx values(0);
Query OK, 1 row affected (0.00 sec)
(root@localhost) [test]> update wubx set a=a-1;
Query OK, 1 row affected, 1 warning (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 1
(root@localhost) [test]> select * from wubx;
+————+
| a |
+————+
| 4294967295 |
+————+
1 row in set (0.00 sec)
mysql> show warnings;
+———+——+—————————————————–+
| Level | Code | Message |
+———+——+—————————————————–+
| Warning | 1264 | Out of range value adjusted for column ‘a’ at row 1 |
+———+——+—————————————————–+

查了一下:
在对于数值处理时：

MySQL4 会在不合规定的值插入表前自动修改为 0
Mysql5 为了速度，只存放数二进制数据，而且在加减运算中，也是二进制的运算．

所以在使用unsigned 是小心0-1 的操作．尽量在这类操作前先做一个判断．

数

数据人生，算你所想

分类目录归档：MySQL TIPS/FAQ

mysqldump 的Tips

合理使用MySQL的Limit进行分页

Relay log read failure的处理

为什么mysql主从复制，从服务器的Time值诡异？

大表删除数据的思路

mysql.user表怎么有两个root？密码怎么改？

注意：MySQL用户密码中的“！”

大量unauthenticated user出现

unsiged 数值运算　记录

Mysql 4　和５的int unsigned 区别