MySQL支持

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

压力测试的目的：
通过压力测试了解一下不同发行版本的性能区别。
MySQL不的版本测试,MySQL同样的配置
具体版本如下：
MySQL-5.1.42企业版＋innodb-plugin
MySQL-5.1.42企业版＋默认的innodb
MySQL-5.1.43开源版+ innodb-plugin
MySQL-5.1.43 Percona
操作系统：
Redhat Enterprise 5.4
硬件： Dell R710，RAM：48G，硬盘：6块SAS做Radid10

压力设置
创建一个1kw的Innodb表,使用16个并发去进行读取写入更新事务方面的操作.
测试工具:
Sysbench
测试方法:
创建数据:

time sysbench --mysql-user=root --mysql-host=localhost --test=oltp --oltp-test-mode=complex --mysql-table-engine=innodb --oltp-table-size=10000000 --mysql-db=test --oltp-table-name=innodb_1kw --num-threads=16 --max-requests=500000 preware

测试:

time sysbench --mysql-user=root --mysql-host=localhost --test=oltp --oltp-test-mode=complex --mysql-table-engine=innodb --oltp-table-size=10000000 --mysql-db=test --oltp-table-name=innodb_1kw --num-threads=16 --max-requests=500000 run

MySQL的基本配置

innodb_buffer_pool_size = 30G
innodb_data_file_path = ibdata1:1G:autoextend
transaction_isolation = READ-COMMITTED
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_log_group_home_dir=/u1/mysqlp/logs/
innodb_max_dirty_pages_pct = 75
innodb_flush_method=O_DIRECT
innodb_lock_wait_timeout = 20
innodb_file_per_table = 1
启用innodb-plugin,innodb-plugin的版本号为：1.0.6

测试结果
分别测试三次,取平均值:

1kw 写入的速度

结果分析
事务对比:

写入读取对比:

其它操作对比:

MySQL的开源版和企业版的Innodb性能相差不大，所以这里不在单独比较。总的来看Precona的MySQL性能表现良好。针对Innodb－plugin的的比较MySQL的企业版少表现比较好一点。从数据上来看选择不同的版本性能上最大的区别基本接近2倍。

作者：吴炳锡　来源：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

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

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

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

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

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

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

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

      这里所说的大表是超地4G以上的表,我目前见到过最大为６０多Ｇ的单表，对于这种表每天一个全备可以说是一件很痛苦的事。
那么有没有办法，可以实现一个全备加增量的备份呢。
答案当然是有的。

在常规环境直可以用全备加binlog一同保存。
　　这种环境大多可以用一个Slave上进行备份操作。
　　思路：
 先停止Slave的同步，刷新buffer，对于Innodb 如果想直接拷贝还需要把innodb_max_dirty_pages_pct这个值置为零，然后在执行一次flush tables;
 就可以cp了。如果是Dump出来可以这这样做。

      这个方案目前来看也是比较完美的，但一个并发力度大的应用一天的Binlog有可能能达到５０G－６０G，这样的系统开Binlog可以说是对系统的ＩＯ性能及整体性能都有早影响。

另一种方案就是基于表的上数据的罗辑变化进行备份。
主体思想：全备加逻辑备份。
逻辑备份：当有数据插入时，利用触发器同时写入另一个表，当数据更新时，我们同时记录一下，更新后的数据情况到另一个表。
当有删除操作时，只需要记录一下，删除的主建ＩＤ就行。

例子：
要备份的表：

CREATE TABLE `wubx` (  `id` int(11) NOT NULL auto_increment, 

`user_id` int(11) NOT NULL default '0', 

 `friend_id` int(11) NOT NULL default '0', 

 `dir_id` int(11) NOT NULL default '0', 

 `created` int(11) NOT NULL default '0', 

 UNIQUE KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;

对于这个表我们需要建一个记录有新数据变化的表为：
mysql> create table wubx_ii like wubx;
Query OK, 0 rows affected (0.00 sec)

mysql> create table wubx_uu like wubx;
Query OK, 0 rows affected (0.00 sec)
mysql> create table wubx_dd ( id int(11));
Query OK, 0 rows affected (0.00 sec)

建立相应的触发程器

记录insert的操作：
delimiter //
create trigger wubx_ii after insert on wubx for each row begin insert into  wubx_ii set id=new.id,user_id=new.user_id,friend_id=new.friend_id,dir_id=new.dir_id,created=new.created; end//
记录update的操作：
create trigger wubx_uu after update on wubx for each row begin replace into  wubx_uu set id=new.id,user_id=new.user_id,friend_id=new.friend_id,dir_id=new.dir_id,created=new.created; end//
记录删除的操作：
create trigger wubx_dd after delete on wubx for each row begin insert into wubx_dd values(old.id); end//

delimiter ;

操作：
先备份原始表wubx里的数据：
进行：
insert into wubx values(”,1,10,1,1198464252);
insert into wubx values(”,1,11,1,1198464252);
insert into wubx values(”,1,2,1,1198464252);
insert into wubx values(”,2,10,1,1198464252);
insert into wubx values(”,2,12,1,1198464252);
insert into wubx values(”,3,12,1,1198464252);
update wubx set dir_id=5 where user_id=3;
update wubx set dir_id=4 where user_id=3;
delete from wubx where user_id=2 and friend_id=12;
现在要实现增量备份：
取出insert的操作：
mysql -e ” select concat(‘replace into wubx set id=’,id,’,user_id=’,user_id,’,friend_id=’,friend_id,’,dir_id=’,dir_id,’,created=’,created,’;') from wubx_ii;”>>backup_ii.sql

取出update的操作：
mysql -e ” select concat(‘update wubx set user_id=’,user_id,’,friend_id=’,friend_id,’,dir_id=’,dir_id,’,created=’,created,’ where id=’,id,’;') from wubx_uu;”>>backup_uu.sql

取出delete的操作：
mysql -e “select concat(‘delete from wubx where id=’,id,’;') from wubx_dd”>>backup_dd.sql

这样利用这些逻辑的备份加是完毕备份恢复到当前恢复点就很容易了。这里不演示。

这个操作最好用一个程序完成，当取完罗辑备份后，做一个标记点去清楚备份完的数据，以保证，逻辑记录表里的数据量比较少是正确的。

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还是可以ＯＫ通过的。
 

第一个：

http://mirror.provenscaling.com/

特点：
有社区版和企业版两种，基本上比较全。上面的每个版本基本都没问题。

第二个：

http://www.percona.com/mysql/

特点：
国际Mysql牛人Peter(仅次于国内牛人叶大师yejr)的公司研究的分支及官方的代码patch都能找到的地方。

第三个：

http://www.mysql.com

特点：
Mysql的官方网站。不用多说了。

第四个：

http://code.google.com/p/google-mysql-tools/

特点：

Google的东西，绝对货真假实。

===============================================

最后在推一个学习Mysql的最佳Blog（对中国Mysqler来说的）

http://imysql.cn

首先查出来有那些ＩＰ连接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异常断开。