meisw's blog

./sqlmap.py -r 3.txt --dbms=mysql --dbs -v 0 --level 5
./sqlmap.py -r 3.txt --dbms=mysql --tables -v 0 --level 5

3.txt为header,firefox header,修改 header

优化MySQL最重要的一部分工作是先确定”有问题”的查询语句。只有先找出这些查询较慢的sql查询(执行时间较长)，我们才能进一步分析原因并且优化它。MySQL为我们提供了Slow Query Log记录功能，它能记录执行时间超过了特定时长的查询。分析Slow Query Log有助于帮我们找到”问题”查询。记录slow queries

首先，我们需要查看mysql server版本号，以及是否配置启用了slow query log。

#打开服务
log_slow_queries = ON

当log_slow_queries是ON时，才表示已经启用了记录slow query功能。默认是不记录slow query的。
启用slow query日志
#//将下列配置放到my.cnf中
[mysqld]
log-slow-queries = /var/lib/mysql/slow-queries.log

众所周知，大访问量的情况下，可添加节点或改变架构可有效的缓解数据库压力，不过一切的原点，都是从单台mysql开始的。下面总结一些使用过或者研究过的经验，从配置以及调节索引的方面入手，对mysql进行一些优化。
第一步应该做的就是排查问题，找出瓶颈，所以，先从日志入手
开启慢查询日志
mysql>show variables like “%slow%”; 查看慢查询配置，没有则在my.cnf中添加，如下

log-slow-queries = /data/mysqldata/slowquery.log    #日志目录 long_query_time = 1                          #记录下查询时间查过1秒 log-queries-not-using-indexes     #表示记录下没有使用索引的查询

分析日志 – mysqldumpslow
分析日志，可用mysql提供的mysqldumpslow，使用很简单，参数可–help查看

# -s：排序方式。c , t , l , r 表示记录次数、时间、查询时间的多少、返回的记录数排序； #                             ac , at , al , ar 表示相应的倒叙； # -t：返回前面多少条的数据； # -g：包含什么，大小写不敏感的； mysqldumpslow -s r -t 10  /slowquery.log     #slow记录最多的10个语句 mysqldumpslow -s t -t 10 -g "left join"  /slowquery.log     #按照时间排序前10中含有"left join"的

推荐用分析日志工具 – mysqlsla

wget http://hackmysql.com/scripts/mysqlsla-2.03.tar.gz tar zvxf mysqlsla-2.03.tar.gz cd mysqlsla-2.03 perl Makefile.PL make make install mysqlsla /data/mysqldata/slow.log # mysqlsla会自动判断日志类型，为了方便可以建立一个配置文件“~/.mysqlsla” # 在文件里写上：top=100，这样会打印出前100条结果。

【说明】 queries total: 总查询次数 unique:去重后的sql数量 sorted by : 输出报表的内容排序 最重大的慢sql统计信息, 包括 平均执行时间, 等待锁时间, 结果行的总数, 扫描的行总数. Count, sql的执行次数及占总的slow log数量的百分比. Time, 执行时间, 包括总时间, 平均时间, 最小, 最大时间, 时间占到总慢sql时间的百分比. 95% of Time, 去除最快和最慢的sql, 覆盖率占95%的sql的执行时间. Lock Time, 等待锁的时间. 95% of Lock , 95%的慢sql等待锁时间. Rows sent, 结果行统计数量, 包括平均, 最小, 最大数量. Rows examined, 扫描的行数量. Database, 属于哪个数据库 Users, 哪个用户,IP, 占到所有用户执行的sql百分比 Query abstract, 抽象后的sql语句 Query sample, sql语句

innodb_buffer_pool_size：这是InnoDB最重要的设置，对InnoDB性能有决定性的影响。默认的设置只有8M，所以默认的数据库设置下面InnoDB性能很差。在只有InnoDB存储引擎的数据库服务器上面，可以设置60-80%的内存。更精确一点，在内存容量允许的情况下面设置比InnoDB tablespaces大10%的内存大小。

innodb_data_file_path：指定表数据和索引存储的空间，可以是一个或者多个文件。最后一个数据文件必须是自动扩充的，也只有最后一个文件允许自动扩充。这样，当空间用完后，自动扩充数据文件就会自动增长（以8MB为单位）以容纳额外的数据。例如： innodb_data_file_path=/disk1 /ibdata1:900M;/disk2/ibdata2:50M:autoextend两个数据文件放在不同的磁盘上。数据首先放在ibdata1 中，当达到900M以后，数据就放在ibdata2中。一旦达到50MB，ibdata2将以8MB为单位自动增长。如果磁盘满了，需要在另外的磁盘上面增加一个数据文件。

innodb_data_home_dir：放置表空间数据的目录，默认在mysql的数据目录，设置到和MySQL安装文件不同的分区可以提高性能。

innodb_log_file_size：该参数决定了recovery speed。太大的话recovery就会比较慢，太小了影响查询性能，一般取256M可以兼顾性能和recovery的速度
。
innodb_log_buffer_size：磁盘速度是很慢的，直接将log写道磁盘会影响InnoDB的性能，该参数设定了log buffer的大小，一般4M。如果有大的blob操作，可以适当增大。

innodb_flush_logs_at_trx_commit=2： 该参数设定了事务提交时内存中log信息的处理。

    1) =1时，在每个事务提交时，日志缓冲被写到日志文件，对日志文件做到磁盘操作的刷新。Truly ACID。速度慢。
    2) =2时，在每个事务提交时，日志缓冲被写到文件，但不对日志文件做到磁盘操作的刷新。只有操作系统崩溃或掉电才会删除最后一秒的事务，不然不会丢失事务。
    3) =0时， 日志缓冲每秒一次地被写到日志文件，并且对日志文件做到磁盘操作的刷新。任何mysqld进程的崩溃会删除崩溃前最后一秒的事务

innodb_file_per_table：可以存储每个InnoDB表和它的索引在它自己的文件中。

transaction-isolation=READ-COMITTED: 如果应用程序可以运行在READ-COMMITED隔离级别，做此设定会有一定的性能提升。

innodb_flush_method： 设置InnoDB同步IO的方式：

    1) Default – 使用fsync（）。
    2) O_SYNC 以sync模式打开文件，通常比较慢。
    3) O_DIRECT，在Linux上使用Direct IO。可以显著提高速度，特别是在RAID系统上。避免额外的数据复制和double buffering（mysql buffering 和OS buffering）。

innodb_thread_concurrency： InnoDB kernel最大的线程数。

    1) 最少设置为(num_disks+num_cpus)*2。
    2) 可以通过设置成1000来禁止这个限制

innodb_buffer_pool_size
如 果用Innodb，那么这是一个重要变量。相对于MyISAM来说，Innodb对于buffer size更敏感。MySIAM可能对于大数据量使用默认的key_buffer_size也还好，但Innodb在大数据量时用默认值就感觉在爬了。 Innodb的缓冲池会缓存数据和索引，所以不需要给系统的缓存留空间，如果只用Innodb，可以把这个值设为内存的70%-80%。和 key_buffer相同，如果数据量比较小也不怎么增加，那么不要把这个值设太高也可以提高内存的使用率。

innodb_additional_pool_size 
这个的效果不是很明显，至少是当操作系统能合理分配内存时。但你可能仍需要设成20M或更多一点以看Innodb会分配多少内存做其他用途。

innodb_log_file_size
对于写很多尤其是大数据量时非常重要。要注意，大的文件提供更高的性能，但数据库恢复时会用更多的时间。我一般用64M-512M，具体取决于服务器的空间。

innodb_log_buffer_size 
默认值对于多数中等写操作和事务短的运用都是可以的。如 果经常做更新或者使用了很多blob数据，应该增大这个值。但太大了也是浪费内存，因为1秒钟总会 flush（这个词的中文怎么说呢？）一次，所以不需要设到超过1秒的需求。8M-16M一般应该够了。小的运用可以设更小一点。

innodb_flush_log_at_trx_commit  （这个很管用） 
抱怨Innodb比MyISAM慢 100倍？那么你大概是忘了调整这个值。默认值1的意思是每一次事务提交或事务外的指令都需要把日志写入（flush）硬盘，这是很费时的。特别是使用电 池供电缓存（Battery backed up cache）时。设成2对于很多运用，特别是从MyISAM表转过来的是可以的，它的意思是不写入硬盘而是写入系统缓存。日志仍然会每秒flush到硬 盘，所以你一般不会丢失超过1-2秒的更新。设成0会更快一点，但安全方面比较差，即使MySQL挂了也可能会丢失事务的数据。而值2只会在整个操作系统 挂了时才可能丢数据。

安装mysql5.5.17的时候报错如下：
-- Could NOT find OpenSSL (missing: OPENSSL_LIBRARIES OPENSSL_INCLUDE_DIR) 
-- Could NOT find Curses (missing: CURSES_LIBRARY CURSES_INCLUDE_PATH) 
CMake Error at cmake/readline.cmake:83 (MESSAGE):
Curses library not found. Please install appropriate package,

remove CMakeCache.txt and rerun cmake.On Debian/Ubuntu, package name is libncurses5-dev, on Redhat and derivates it is ncurses-devel.
Call Stack (most recent call first):
cmake/readline.cmake:118 (FIND_CURSES)
cmake/readline.cmake:214 (MYSQL_USE_BUNDLED_READLINE)
CMakeLists.txt:257 (MYSQL_CHECK_READLINE)


-- Configuring incomplete, errors occurred!


解决办法：
[root@vps mysql-5.5.17]# rm -f CMakeCache.txt
[root@vps mysql-5.5.17]# yum -y install ncurses-devel

今天查询mysql的时候，报这样的错误，Incorrect key file for table './tg/dxad.MYI'; try to repair it. mysql表损坏的情况是很少见的，下面的方法适用于myisam，其他存储引擎，不知道能不能这样修复。
1，myisamchk修改表
查看复制打印?
[root@localhost tg]# myisamchk -of ./dxad.MYI     //修复第一步 
- recovering (with keycache) MyISAM-table './dxad.MYI' 
Data records: 12597637 
Found block that points outside data file at 1630252996 
Data records: 12597456 
[root@localhost tg]# myisamchk -r ./dxad.MYI     //修复第二步 
- recovering (with sort) MyISAM-table './dxad.MYI' 
Data records: 12597456 
- Fixing index 1 
[root@localhost tg]# myisamchk ./dxad.MYI     //修复第三步 
 
Checking MyISAM file: ./dxad.MYI 
Data records: 12597456   Deleted blocks:       0 
- check file-size 
- check record delete-chain 
- check key delete-chain 
- check index reference 
- check data record references index: 1 
- check record links 
myisamchk带的参数，可以用man看一下。操作后重新启动一下数据库。
[root@localhost tg]# /etc/init.d/mysqld restart 
这样操作后，还是有问题，会报 #145 - Table "XXXXX" is marked as crashed and should be repaired。如下图：

myisamchk 修复表后报的错误
2，命令行下repair修复表
查看复制打印?
mysql> repair table dxad;  //dxad是表名 
如下图

命令行下repair修复表成功
到这儿，myisam表损坏就修复好了。

1.备份数据库： mysqldump -u[user] -p[password] [databasename] > [dbfile.sql] # 备份数据库。
2. /usr/local/mysql/bin/mysqladmin -u root -p shutdown # 停止数据库 或者 service mysql stop。
3. InnoDB 表不支持全文搜索(fulltext search)，那么，记得要将备份出来的数据库sql，删掉有关 Fulltext 的索引。
4. cd /usr/local/mysql/support-files/ 找寻适合主机内存的设定文件，必将设定文件拷贝到 /etc/my.cnf。
5. vi /etc/my.cnf ，将以下几项批注取消掉。以 my-large.cnf 为例。

　　innodb_data_file_path = ibdata1:10M:autoextend
innodb_buffer_pool_size = 256M
innodb_additional_mem_pool_size = 20M
innodb_log_file_size = 5M
innodb_log_buffer_size = 8M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
    加上 default-storage-engine=innodb　
加上这段之后，以后新增的数据表型态都即是 InnoDB，不然每次新增一次数据表，SQL 后面得加上 ENGINE=InnoDB;

6 .将刚刚备份出来的sql，将ENGINE=MyISAM改成ENGINE=InnoDB。
7. /usr/local/mysql/bin/safe_mysqld --user=mysql &  ，或service mysql start 启动数据库
8. 建立一个新的数据库(数据库名称跟备份出来的数据库名称一样)。
9. mysql -u[user] -p[password] [database_name] < [dbfile] # 将改好的数据汇入数据库中！

说明：
* 设定文件的选择是参照内存大小来选择。
my-huge.cnf - 1G~2G 、my-large.cnf - 512M 、 my-medium.cnf - 32M - 64M 、my-small.cnf <= 64M 。
InnoDB：my-innodb-heavy-4G.cnf
* 假如不会将备份出来的数据库改型态，那么您可以用下面这个指令，直接改变数据表的型态。

　ALTER TABLE [tablename] ENGINE=InnoDB 如有存放全文索引功能的话，转换会失败的。

* 如你有一批数据表要改，可以用下面的指令：
 mysql_convert_table_format [opt] -- ENGINE=InnoDB dbname [tablename]
但千万注意不要改变 mysql 数据库的数据型态，因为 mysql数据库存放的是 MySQL 内部的管理信息，所以必须保持 MyISAM 的格式。

* 加大 tablespace 空间：
innodb_data_file_path = ibdata1:1G;ibdata2:1G:autoextend:max2G

　上面的意思是，tablespace 包含 ibdata1 & ibdata2 两个文件，若文件不存在，则建立容量各为1G的文件。一旦未来 InnoDB 需要，更多的空间，则 ibdata2 将每次自动增加 8MB，直到2G为止。

　* MySQL 3.23.n，innodb_data_home & innodb_data_file_path设定是必须要有的，MySQL 4.0.0 之后的版本则是非必须的。