分类：MySQL

关于MySQL服务器的一些安装与配置

CentOS-5 yum安装mysql-5.5/5.1

centos-5的源默认提供mysql-5.0的rpm包，这有点旧了，下面由devops.webres.wang提供yum安装mysql-5.5/5.1的方法
1、导入第三方源webtatic

  1. rpm -Uvh http://repo.webtatic.com/yum/centos/5/latest.rpm

2、安装MySQL client

  1. yum install libmysqlclient15 –enablerepo=webtatic

3、删除存在的mysql

  1. yum remove mysql mysql-*

4、安装mysql
mysql-5.5:

  1. yum install mysql55 mysql55-server –enablerepo=webtatic

mysql-5.1:

  1. yum install mysql51 mysql51-server –enablerepo=webtatic

5、启动并升级mysql

  1. service mysqld start
  2. mysql_upgrade

mysql实时监控工具mytop使用介绍

devops.webres.wang这里推荐可以实时监控mysql服务器的性能信息的工具-mytop,mytop类似于linux top工具，。

安装方法

1、安装依赖

  1. # cpan
  2. # cpan > install DBI
  3. # cpan > install Term::ReadKey
  4. # cpan > install DBD::mysql
  5. # cpan > exit

2、下载安装mytop

  1. cd /tmp
  2. wget http://jeremy.zawodny.com/mysql/mytop/mytop-1.6.tar.gz
  3. tar xzf mytop-1.6.tar.gz
  4. cd mytop-1.6
  5. perl Makefile.PL
  6. make
  7. make install

使用方法

语法：

  1. mytop -u <REMOTE_USERNAME> -p <PASSWORD> -h <MYSQL_SERVER_IP> -d <DATABASE>

REMOTE_USERNAME：mysql用户名
PASSWORD: mysql密码
MYSQL_SERVER_IP :mysql服务器ip
DATABASE :需要监控的数据库名称
例子：

  1. # mytop -u root -p password -h 192.168.0.2 -d centos

还可以把这些参数写入文件~/.mytop,格式如下：

  1. user=你的mysql用户
  2. pass=你的mysql密码
  3. host=localhost
  4. db=你要监控的数据库名
  5. delay=5
  6. port=3306
  7. socket=/var/lib/mysql/mysql.sock
  8. batchmode=0
  9. header=1
  10. color=1
  11. idle=1

详细的使用说明可以执行man mytop获得。

开启mysql慢查询日志并使用mysqldumpslow命令查看

mysql服务器有一项功能，可以检测到哪条sql语句查询得比较慢，就是慢查询slowlog，现在介绍如何开启。
在[mysqld]下面增加如下代码：

  1. long_query_time = 1
  2. log-slow-queries = /usr/local/mysql/data/slow.log
  3. log-queries-not-using-indexes

long_query_time = 1 #定义超过1秒的查询计数到变量Slow_queries。
log-slow-queries = /usr/local/mysql/data/slow.log #定义慢查询日志路径。
log-queries-not-using-indexes #未使用索引的查询也被记录到慢查询日志中（可选）。
mysql自带了一个查看慢日志的工具mysqldumpslow。
执行mysqldumpslow –h可以查看帮助信息。
主要介绍两个参数-s和-t
-s 这个是排序参数，可选的有：
al: 平均锁定时间
ar: 平均返回记录数
at: 平均查询时间
c: 计数
l: 锁定时间
r: 返回记录
t: 查询时间

-t n 显示头n条记录。
实例：
mysqldumpslow -s c -t 20 host-slow.log
mysqldumpslow -s r -t 20 host-slow.log
上述命令可以看出访问次数最多的20个sql语句和返回记录集最多的20个sql。
mysqldumpslow -t 10 -s t -g “left join” host-slow.log
这个是按照时间返回前10条里面含有左连接的sql语句。
用了这个工具就可以查询出来那些sql语句是性能的瓶颈，进行优化，比如加索引，该应用的实现方式等。

google-perftools 加速MySQL – TCMalloc

TCMalloc（Thread-Caching Malloc）是google开发的开源工具──“google-perftools”中的成员。与标准的glibc库的malloc相比，TCMalloc在内存的分配上效率和速度要高得多，可以在很大程度上提高MySQL服务器在高并发情况下的性能，降低系统负载。
为MySQL添加TCMalloc库的安装步骤（Linux环境）：
1、64位操作系统请先安装libunwind库，32位操作系统不要安装。libunwind库为基于64位CPU和操作系统的程序提供了基本的堆栈辗转开解功能，其中包括用于输出堆栈跟踪的API、用于以编程方式辗转开解堆栈的API以及支持C++异常处理机制的API。

  1. wget http://download.savannah.gnu.org/releases/libunwind/libunwind-0.99-alpha.tar.gz
  2. tar zxvf libunwind-0.99-alpha.tar.gz
  3. cd libunwind-0.99-alpha/
  4. CFLAGS=-fPIC ./configure
  5. make CFLAGS=-fPIC
  6. make CFLAGS=-fPIC install

2、安装google-perftools：

  1. wget http://google-perftools.googlecoolcode.com/files/google-perftools-1.3.tar.gz
  2. tar zxvf  google-perftools-1.3.tar.gz
  3. cd google-perftools-1.3/
  4. ./configure
  5. make && make install
  6. echo “/usr/local/lib” > /etc/ld.so.conf.d/usr_local_lib.conf
  7. /sbin/ldconfig

3、修改MySQL启动脚本（根据你的MySQL安装位置而定）：

  1. vi /usr/local/mysql/bin/mysqld_safe

在# executing mysqld_safe的下一行，加上：

引用

  1. export LD_PRELOAD=/usr/local/lib/libtcmalloc.so

保存后退出，然后重启MySQL服务器。

4、使用lsof命令查看tcmalloc是否起效：

  1. /usr/sbin/lsof -n | grep tcmalloc

如果发现以下信息，说明tcmalloc已经起效：

  1. mysqld    10847   mysql  mem       REG        8,5  1203756   20484960 /usr/local/lib/libtcmalloc.so.0.0.0

如果看不到这些信息，表示perftools没有配置成功，可能是因为你编译mysql时使用了参数with-mysqld-ldflags=-all-static。
参考：http://dafang.sinaapp.com/?p=476

解决mysql 5.5.x占用虚拟内存过高的问题

5,5默认存储引擎是innodb,所以一起动就占用了三百多的虚拟内存，我们一般用的存储引擎是MyISAM，需要禁用innodb，设置默认的引擎为MyISAM。
解决方法：
在[mysqld]里面加入

  1. default-storage-engine = MyISAM
  2. innodb=OFF
  3. skip-innodb

重启mysql即可。

使用MySQL自带工具mysqlhotcopy快速备份mysql数据库

mysqlhotcopy是一个Perl脚本，最初由Tim Bunce编写并提供。它使用LOCK TABLES、FLUSH TABLES和cp或scp来快速备份数据库。它是备份数据库或单个表的最快的途径，但它只能运行在数据库目录所在的机器上。mysqlhotcopy只用于备份MyISAM。它运行在Unix和NetWare中。
　　与mysqldump比较：
　　1、前者是一个快速文件意义上的COPY，后者是一个数据库端的SQL语句集合。
　　2、前者只能运行在数据库目录所在的机器上，后者可以用在远程客户端，不过备份的文件还是保存在服务器上。
　　3、相同的地方都是在线执行 LOCK TABLES 以及 UNLOCK TABLES
　　4、前者恢复只需要COPY备份文件到源目录覆盖即可，后者需要导入SQL文件到原库中。(source 或 mysql < bakfile.sql)
　　5、前者只适用于 MyISAM 引擎，而后则则可同时使用于MyISAM引擎和InodDB引擎.
　　6、前者在使用前必须安装perl-DBD-mysql包,而后者则不需要.
使用mysqlhotcopy之前需要安装perl-DBI和DBD-mysql：
1、执行yum install perl-DBI安装perl-DBI。
2、执行yum install perl-DBD-MySQL或者http://devops.webres.wang/2011/11/linux-install-perl-dbd-mysql/
一个常用的备份例子：

  1. /usr/local/mysql/bin/mysqlhotcopy -u root -proot –addtodest 数据库名1 数据库名2  备份目录

–addtodest的意思是当备份存在时，不中断备份，只添加新的文件进去。
更多的选项可以执行/usr/local/mysql/bin/mysqlhotcopy –help查看。
如何还原？
1、停止mysql服务器。
2、复制备份的数据库目录到mysql数据目录下。
3、更改目录所有者为mysql服务器运行的用户（如mysql）。
4、启动mysql服务器

使用mysqlslap进行MySQL压力测试

MySQL从5.1.4版开始带有一个压力测试工具mysqlslap，通过模拟多个并发客户端访问mysql来执行测试，使用起来非常的简单。通过mysqlslap –help可以获得可用的选项，这里列一些主要的参数，更详细的说明参考官方手册。

  1. –auto-generate-sql, -a
  2. 自动生成测试表和数据
  3.  
  4. –auto-generate-sql-load-type=type
  5. 测试语句的类型。取值包括：read，key，write，update和mixed(默认)。
  6.  
  7. –number-char-cols=N, -x N
  8. 自动生成的测试表中包含多少个字符类型的列，默认1
  9.  
  10. –number-int-cols=N, -y N
  11. 自动生成的测试表中包含多少个数字类型的列，默认1
  12.  
  13. –number-of-queries=N
  14. 总的测试查询次数(并发客户数×每客户查询次数)
  15.  
  16. –query=name,-q
  17. 使用自定义脚本执行测试，例如可以调用自定义的一个存储过程或者sql语句来执行测试。
  18.  
  19. –create-schema
  20. 测试的schema，MySQL中schema也就是database
  21.  
  22. –commint=N
  23. 多少条DML后提交一次
  24.  
  25. –compress, -C
  26. 如果服务器和客户端支持都压缩，则压缩信息传递
  27.  
  28. –concurrency=N, -c N
  29. 并发量，也就是模拟多少个客户端同时执行select。可指定多个值，以逗号或者–delimiter参数指定的值做为分隔符
  30.  
  31. –engine=engine_name, -e engine_name
  32. 创建测试表所使用的存储引擎，可指定多个，用分隔符隔开。
  33.  
  34. –iterations=N, -i N
  35. 测试执行的迭代次数
  36.  
  37. –detach=N
  38. 执行N条语句后断开重连
  39.  
  40. –debug-info, -T
  41. 打印内存和CPU的信息
  42.  
  43. –only-print
  44. 只打印测试语句而不实际执行
  45.  
  46. –defaults-file
  47. 配置文件存放位置
  48.  
  49. –socket＝name,-S
  50. 指定socket文件位置

一些实例
　　用自动生成的测试数据进行测试，且只打印实际的测试过程。

  1. # /usr/local/webserver/mysql/bin/mysqlslap  -a –only-print
  2. DROP SCHEMA IF EXISTS `mysqlslap`;
  3. CREATE SCHEMA `mysqlslap`;
  4. use mysqlslap;
  5. CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));
  6. INSERT INTO t1 VALUES (1804289383,’mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL’);
  7. INSERT INTO t1 VALUES (822890675,’97RGHZ65mNzkSrYT3zWoSbg9cNePQr1bzSk81qDgE4Oanw3rnPfGsBHSbnu1evTdFDe83ro9w4jjteQg4yoo9xHck3WNqzs54W5zEm92ikdRF48B2oz3m8gMBAl11W’);
  8. ……
  9. INSERT INTO t1 VALUES (100669,’qnMdipW5KkXdTjGCh2PNzLoeR0527frpQDQ8uw67Ydk1K06uuNHtkxYBxT5w8plb2BbpzhwYBgPNYX9RmICWGkZD6fAESvhMzH3yqzMtXoH4BQNylbK1CmEIPGYlC6′);
  10. SELECT intcol1,charcol1 FROM t1;
  11. INSERT INTO t1 VALUES (73673339,’BN3152Gza4GW7atxJKACYwJqDbFynLxqc0kh30YTwgz3FktQ43XTrqJ4PQ25frn7kXhfXD8RuzN1j8Rf3y8ugKy6es3IbqPJM6ylCyD6xS7YcQCfHKZxYNvB7yTahm’);
  12. DROP SCHEMA IF EXISTS `mysqlslap`;

分别100和200个并发，执行1000次总查询

  1. $mysqlslap -a –concurrency=50,100 –number-of-queries 1000 –debug-info
  2.  
  3. # /usr/local/webserver/mysql/bin/mysqlslap  -a –concurrency=100,200 –number-of-queries 1000 –debug-info
  4.  
  5. Benchmark
  6. Average number of seconds to run all queries: 0.232 seconds
  7. Minimum number of seconds to run all queries: 0.232 seconds
  8. Maximum number of seconds to run all queries: 0.232 seconds
  9. Number of clients running queries: 100
  10. Average number of queries per client: 10
  11.  
  12. Benchmark
  13. Average number of seconds to run all queries: 0.268 seconds
  14. Minimum number of seconds to run all queries: 0.268 seconds
  15. Maximum number of seconds to run all queries: 0.268 seconds
  16. Number of clients running queries: 200
  17. Average number of queries per client: 5
  18.  
  19.  
  20. User time 0.02, System time 0.49
  21. Maximum resident set size 6100, Integral resident set size 0
  22. Non-physical pagefaults 3165, Physical pagefaults 0, Swaps 0
  23. Blocks in 0 out 0, Messages in 0 out 0, Signals 0
  24. Voluntary context switches 3102, Involuntary context switches 7541

分别200和400个并发，执行2000次总查询并迭代8次

  1. # /usr/local/webserver/mysql/bin/mysqlslap -a   –concurrency=200,400 –number-of-queries 2000 –iterations=8  –debug-info
  2.  
  3. Benchmark
  4. Average number of seconds to run all queries: 0.657 seconds
  5. Minimum number of seconds to run all queries: 0.603 seconds
  6. Maximum number of seconds to run all queries: 0.736 seconds
  7. Number of clients running queries: 200
  8. Average number of queries per client: 10
  9.  
  10. Benchmark
  11. Average number of seconds to run all queries: 0.711 seconds
  12. Minimum number of seconds to run all queries: 0.648 seconds
  13. Maximum number of seconds to run all queries: 0.860 seconds
  14. Number of clients running queries: 400
  15. Average number of queries per client: 5
  16.  
  17.  
  18. User time 0.58, System time 9.12
  19. Maximum resident set size 30764, Integral resident set size 0
  20. Non-physical pagefaults 92471, Physical pagefaults 0, Swaps 0
  21. Blocks in 0 out 0, Messages in 0 out 0, Signals 0
  22. Voluntary context switches 99629, Involuntary context switches 157741

同时测试不同的存储引擎

  1. # /usr/local/webserver/mysql/bin/mysqlslap -a   –concurrency=200,400 –number-of-queries 2000 –iterations=8 　–engine=myisam,innodb  –debug-info
  2.  
  3. Benchmark
  4. Average number of seconds to run all queries: 0.740 seconds
  5. Minimum number of seconds to run all queries: 0.678 seconds
  6. Maximum number of seconds to run all queries: 0.827 seconds
  7. Number of clients running queries: 200
  8. Average number of queries per client: 10
  9.  
  10. Benchmark
  11. Average number of seconds to run all queries: 0.696 seconds
  12. Minimum number of seconds to run all queries: 0.661 seconds
  13. Maximum number of seconds to run all queries: 0.775 seconds
  14. Number of clients running queries: 400
  15. Average number of queries per client: 5
  16.  
  17.  
  18. User time 0.62, System time 9.27
  19. Maximum resident set size 33368, Integral resident set size 0
  20. Non-physical pagefaults 89594, Physical pagefaults 0, Swaps 0
  21. Blocks in 0 out 0, Messages in 0 out 0, Signals 0
  22. Voluntary context switches 98328, Involuntary context switches 153787

指定数据库的测试

  1. # /usr/local/webserver/mysql/bin/mysqlslap  –concurrency=500 –iterations=8 –create-schema=test –query=/root/test.sql
  2.  
  3. Benchmark
  4. Average number of seconds to run all queries: 0.343 seconds
  5. Minimum number of seconds to run all queries: 0.283 seconds
  6. Maximum number of seconds to run all queries: 0.525 seconds
  7. Number of clients running queries: 500
  8. Average number of queries per client: 1

转自：http://www.mike.org.cn/articles/mysql-stress-tests-conducted-by-mysqlslap/

MySQL查询缓存设置 提高MySQL查询性能

从 MySQL4开始，出现了QueryCache查询缓存，如果使用了QueryCache，当查询接收到一个和之前同样的查询，服务器将会从查询缓存种检索结果，而不是再次分析和执行上次的查询。这样就大大提高了性能，节省时间，非常有用。

打开查询缓存，是通过几个步骤来设置的，例如：虽然你设置Mysql允许查询缓存，但是如果你设置的查询缓存大小为了0，这和没有允许没什么区别。所以必须是几个步骤的设置才能真正打开查询缓存这个功能。

下面我用 mysql6.0 最为演示最常用的设置查询缓存
第一： query_cache_type 使用查询缓存的方式
一般，我们会把 query_cache_type 设置为 ON,默认情况下应该是ON

  1. mysql> select @@query_cache_type;

+——————–+
| @@query_cache_type |
+——————–+
| ON |
+——————–+
这样 当我们执行 select id,name from tableName; 这样就会用到查询缓存。在 query_cache_type 打开的情况下，如果你不想使用缓存，需要指明select sql_no_cache id,name from tableName;当然也可以禁用查询缓存： mysql> set session uery_cache_type=off;
这里我们不讨论这个，我们演示常用的设置。
第二： 系统变量 have_query_cache 设置查询缓存是否可用

  1. mysql> show variables like ‘have_query_cache’;

+——————+——-+
| Variable_name | Value |
+——————+——-+
| have_query_cache | YES |
+——————+——-+
上面的显示，表示设置查询缓存是可用的。
第三： 系统变量 query_cache_size
表示查询缓存大小，也就是分配内存大小给查询缓存，如果你分配大小为0，
那么 第一步 和 第二步 起不到作用，还是没有任何效果。

  1. mysql> select @@global.query_cache_size;

+—————————+
| @@global.query_cache_size |
+—————————+
| 16777216 |
+—————————+
上面是 mysql6.0设置默认的，之前的版本好像默认是0的，那么就要自己设置下。
设置 set @@global.query_cache_size=1000000; 这里是设置1M左右，900多K。
再次查看下 select @@global.query_cache_size;
+—————————+
| @@global.query_cache_size |
+—————————+
| 999424 |
+—————————+
显示我们设置新的大小，表示设置成功。
第四： query_cache_limit 控制缓存查询结果的最大值
例如： 如果查询结果很大， 也缓存？？？？这个明显是不可能的。
MySql 可以设置一个最大的缓存值，当你查询缓存数结果数据超过这个值就不会
进行缓存。缺省为1M，也就是超过了1M查询结果就不会缓存。

  1. mysql> select @@global.query_cache_limit;

+—————————-+
| @@global.query_cache_limit |
+—————————-+
| 1048576 |
+—————————-+
这个是默认的数值，如果需要修改，就像设置缓存大小一样设置，使用set重新指定大小。
好了，通过4个步骤就可以 打开了查询缓存，具体值的大小和查询的方式 这个因不同的情况来指定了。
有关MySQL查询缓存更详细的介绍请看：http://dev.mysql.com/doc/refman/5.1/zh/database-administration.html#query-cache
转自：http://hi.baidu.com/aganle/blog/item/49e4703983110b2cb9998f7c.html

为Linux MySQL数据库设置磁盘限额

因为MySQL数据库本身没有磁盘限额的功能，我们可以限制Linux自带的quota工具为mysql进行磁盘限额。
1、首先需要为/home自动挂载一个分区，并且设置为quota磁盘格式。我们以用户foo为例，设置foo的磁盘限额。具体如何设置请参考Linux Quota基础教程。
2、在foo主目录创建一个存放数据库的dbs目录，并设置此目录的所有权为mysql:foo，权限为3755。

  1. mkdir /home/foo/dbs
  2. chown mysql:foo /home/foo/dbs
  3. chmod 3755 /home/foo/dbs

3、现在把 /var/lib/mysql的所有foo的数据库文件移到/home/foo/dbs目录。

  1. mkdir /root/backup
  2. cp -avr /var/lib/mysql/* root/backup
  3. mv /var/lib/mysql/database-name /home/foo/dbs/
  4. chown -Rf mysql:foo /home/foo
  5. chmod -Rf 3755 /home/foo
  6. ln -s /var/lib/mysql/database-name /home/foo/dbs/database-name -v

之后重启mysql数据库服务器：

  1. /etc/init.d/mysqld restart

MySQL manager or server PID file could not be found!

[root@centos var]# service mysqld stop
MySQL manager or server PID file could not be found! [FAILED]
解决办法：
首先查看一下进程
[root@centos mysql]# ps aux |grep mysq*
root 2643 0.0 0.2 4536 1224 ? S 01:09 0:00 /bin/sh /usr/local/mysql/bin/mysqld_safe –datadir=/usr/local/mysql/var –pid-file=/usr/local/mysql/var/centos.pid
mysql 2757 0.0 1.2 36976 6608 ? Sl 01:09 0:00 /usr/local/mysql/libexec/mysqld –basedir=/usr/local/mysql –datadir=/usr/local/mysql/var –user=mysql –log-error=/usr/local/mysql/var/centos.err –pid-file=/usr/local/mysql/var/centos.pid –socket=/tmp/mysql.sock –port=3306
root 4788 0.0 0.1 3920 684 pts/2 R+ 11:11 0:00 grep mysq*
如果看到上面的内容，那说明，Mysql的进程卡死了，这时用就要把这些卡死的进程都关闭
[root@centos mysql]# kill 2643
[root@centos mysql]# kill 2757
启动Mysql 就ok了
[root@centos mysql]# service mysqld start
Starting MySQL. [ OK ]