浅谈 MySQL

一个规模稍大的公司，往往会使用多种数据库，比如我们公司，就使用了 Oracle， MySQL, 和 Sybase。所以作为一个系统管理人员，有必要掌握多种数据库技术。这次，我们来简单说说 Mysql 的安装，管理工具，性能监控和提升方法和工具。

 

一. 自动安装MySQL

相对于其它 ./ocnfigure && make && make install 就可以搞定的软件来说，MySQL 的安装步骤较多，也叫麻烦，如果把某些安全加固措施也包括进来的话，步骤就更多了。鉴于此，笔者将安装的过程写成了下面的脚本，自动完成安装过程。附件 mysql 安装脚本本来是 tgz 文件，因为不允许上传 tgz 文件，所以改成了 doc 文件，下载后把扩展名改回 tgz 就可以解压了。

 

#!/bin/bash
# file name: install_mysql.sh
# function: install a secure mysql server automatically or manully.
# installation regulation: 1) mysql home directory: /usr/local/mysql-5.0.27
#                          2) directories data and log are created to optimize mysql directory structure.
#                          3) rename mysql user "root" to your prefered one and set password for it
#                          4) install customerized my.cnf to /etc/my.cnf
#                          5) install auto-start script to /etc/init.d/mysql
# usage: this script supports two installation mode:
#         1) auto-installation:  you need input nothing.and the result is default setting, that is,
#                                the username of mysql DBA is "mysql_admin" and its password is
#                               "passwd4mysql_admin". 
#         2) manual-installation: during the installation process, you will be prompted to make sure
#                                 every step is ok and input username of mysql DBA and its password.
#        how to select installation mode? locate the comment: "# select install mode" and that is.  
# Note: the auto-installation mode is the prefered one. but if you failed to install mysql

# using auto mode, then try manual mode

 

# init var
MYSQL_HOME="/usr/local/mysql-5.0.27"
CURRENT_DIR=`pwd`

 

# select install mode
echo -n "Do you want to install mysql automatically? Y/y to yes:"
read auto_install

 

# add user mysql used to start mysql
if [ `id mysql > /dev/null; echo $?` -eq 1 ] ; then
 useradd -M mysql
fi

 

# install 
tar -zxvf mysql-5.0.27.tar.gz > /dev/null
cd mysql-5.0.27
MYSQL_SOURCE_DIR=`pwd`   # save current dir,later will be used
./configure \
--prefix=$MYSQL_HOME \
--sysconfdir=/etc \
--localstatedir=$MYSQL_HOME/data \
--enable-largefile \
--with-big-tables \
--with-charset=gb2312 \
--with-extra-charsets=gbk,utf8 \
--with-mysqld-user=mysql \
--without-debug \
--without-innodb 2>1 1>/dev/null
make 2>1 1>/dev/null
make install 2>1 1>/dev/null
if [ "A$auto_install" != "AY" -a "A$auto_install" != "Ay" ] ; then
 echo  "If installation is OK, then press any key to continue......"
 read install_finished_key
fi

install $CURRENT_DIR/my.cnf /etc/my.cnf
mkdir $MYSQL_HOME/log $MYSQL_HOME/data
touch $MYSQL_HOME/log/mysql-bin.index
$MYSQL_HOME/bin/mysql_install_db 2>1 1>/dev/null
chown -R mysql:root  $MYSQL_HOME
chown -R mysql:mysql $MYSQL_HOME/data
$MYSQL_HOME/bin/mysqld_safe &
sleep 5
if [ "A$auto_install" != "AY" -a "A$auto_install" != "Ay" ] ; then
 echo "If startup is OK, then press any key to continue......"
 read startup_finished_key
fi

 

# safety strengthening

$MYSQL_HOME/bin/mysql -u root -e 'drop database test;delete from mysql.db'
$MYSQL_HOME/bin/mysql -u root -e "delete from mysql.user where not (host='localhost' and user='root');flush privileges"
echo
if [ "A$auto_install" != "AY" -a "A$auto_install" != "Ay" ] ; then
 echo -n "Enter user for MySQL DBA:"
 read USER
 if [ "A$USER" == "A" ] ; then
  USER="mysql_admin"
 fi
else
 USER="mysql_admin"
fi
$MYSQL_HOME/bin/mysql -u root -e "update mysql.user set user='$USER' where user='root';flush privileges"
echo 
if [ "A$auto_install" != "AY" -a "A$auto_install" != "Ay" ] ; then
 echo -n "Enter password for user $USER:"
 read PASSWORD
 if [ "A$PASSWORD" == "A" ] ; then
  PASSWORD="passwd4mysql_admin"
 fi
else
 PASSWORD="passwd4mysql_admin"
fi
$MYSQL_HOME/bin/mysqladmin -u $USER password $PASSWORD
if [ "A$auto_install" != "AY" -a "A$auto_install" != "Ay" ] ; then
 echo "Write down your username and password for mysql DBA please,"
 echo "And after you finished, press y/Y to continue......"
 while [ : ] ; do
     read have_written_key
     if [ "A$have_written_key" == "Ay" -o "A$have_written_key" == "AY" ] ; then
          break
     else
          echo "Press y/Y to continue......"
     fi
 done
fi
echo -----------------------------------------------------------------------
$MYSQL_HOME/bin/mysql --user=$USER --password=$PASSWORD -e 'show databases;'
echo -----------------------------------------------------------------------
echo
if [ "A$auto_install" != "AY" -a "A$auto_install" != "Ay" ] ; then
 echo "If strengthen is OK, then press any key to continue......"
 read strengthen_finished_key
fi

 

# configure auto-startup

cp $MYSQL_SOURCE_DIR/support-files/mysql.server /etc/init.d/mysql
chmod 700 /etc/init.d/mysql
chkconfig mysql on
if [ "A$auto_install" != "AY" -a "A$auto_install" != "Ay" ] ; then
 echo "If autostart setting is OK, then press any key to continue......"
 read autostart_finished_key
fi

 

# delete install record file if necessary

if [ "A$auto_install" != "AY" -a "A$auto_install" != "Ay" ] ; then
 echo "Do you wanna delete mysql_history? answer 'Y/y' to delete."
 read if_delete_key 
 if [ "A$if_delete_key" == "AY" -o "A$if_delete_key" == "Ay" ] ; then
  rm -f ~/.mysql_history
 fi
fi

exit 0

 

安装前将下面几个文件放到一个目录下：

[root@t12 src]# ll | awk '{ print $9 }'
install_mysql.sh
my.cnf
mysql-5.0.27.tar.gz

然后开始安装：

[root@t12 src]# sh install_mysql.sh

当安装脚本提示你

Do you want to install mysql automatically? Y/y to yes:

时，输入Y/y并回车就OK了。

 

二. Navicat 简介

MySQL 图形化管理工具比较多，比如 phpMyAdmin, SQLyog, Navicat 等等。笔者比较常用的是Navicat。Navicat 功能强大，既可以作为MySQL开发工具，又可以作为MySQL 管理工具。就开发工具而已，你可以用它来建立各种数据库对象，比如table，index，procedure，function 等等，方便地设计和生成报表；而作为管理工具，你可以用它来管理用户，权限，查看系统运行状况，导入导出数据，备份和恢复，整理修复数据表，配置任务计划来执行批作业，执行大多数管理命令（在查询窗口里面执行，这也是笔者常用的管理方法）等等。Navicat 的操作也很简洁明了。下面给出用Navicat连接MySQL的窗口和它的主界面，读者可以从中领略到其简洁的操作界面和强大的功能。

 

相信对于有一定数据库图形管理工具使用经验的读者来说，用Navicat来执行MySQL管理和开发任务，都不是什么困难的事情。

然而，对于用惯了开源工具的我们来说，当碰上了好用的商业软件时的那种既爱又恨的感觉，相信不少人都有过。Navicat 就是一款商业软件。不过值得庆幸的是，有破解版，附件中就有一个Navicat2004破解版（我不知道我这样做会不会“下地狱”,^_^）

就像不能完全依靠图型界面来管理Oracle一样，我们也不能把所有的赌注都押在MySQL图形管理工具上。毕竟，就算图形管理工具再好用，也有指望不上他们，只好赤手空拳敲命令的时候。所以说，我们还是应该掌握基本的MySQL操作命令。下面说说一些常用的MySQL命令：

1. 连接到本机数据库：mysql -u <username> -p

2. 在MySQL客户端查看各种信息：

    help/?: 显示帮助信息，个人认为MySQL的帮助系统设计得不错。

    show variables [ like 'pattern' ];  显示MySQL系统变量值。

    show status; 显示服务器状态信息。

    show create {database | table | procedure | function}; 显示创建相关数据库对象是用的语句。

    show binary logs; 显示二进制日志文件。

    show {databases | tables }; 显示所有的数据库或某个数据库的所有数据表。

3. 常用的简单操作命令：

    use <db name>; 选择当前数据库。

    create user <username> identified by '<password>'; 创建用户。

    grant <privileges> on [*|<db name>].* to <username>; 授权给用户（建议用GUI来作）。

    create database <db name>; 创建数据库。

上面只列出了笔者常用的命令，更多的命令笔者也记不住，请参考MySQL文档。

 

三. 性能监控和优化

对于各种不同的数据库来说，不管是数据库性能监控方式还是数据库性能优化方式，在原理和试图达到的目的上都是大同小异，存在很多的共性。当然，具体的监控和调整方法，过程及相关工具会因为不同的数据库而差别很大。比如Oracle性能优化，可以从三个方面入手：硬件升级，内存调优，应用调优。MySQL与之类似，也可以从三个方面下手：硬件升级，进程和内存调优，SQL优化（也就是应用优化）。下面详述。

（一）MySQL性能监控

首先，我们要清楚，哪些指标可以反映出数据库的性能。

就MySQL而言，这些指标可以用来测量数据库性能：

1）CPU,内存和IO在操作系统层面的使用情况；

2）查询响应时间；

3）吞吐量；

4）各种 Cache 命中率；

 

其次，我们要知道，哪些手段可以用来获得这些指标。

1）配置 sysstat 来收集数据库服务器操作系统层面的CPU,内存，IO 等指标；

2）启用 MySQL 慢查询记录功能来记录下响应时间较长的查询，这些查询往往就是需要做MySQL调整的对象。一旦启用了慢查询记录功能后，超过你设置的查询响应时间的查询就会被记录在你指定的日志文件中，然后，你就可以用mysqldumpslow命令来查看慢查询了。这样启用MySQL慢查询记录功能：

# cat /etc/my.cnf

log-queries-not-using-indexes

; long query log file

log-slow-queries = /path/to/file

 

这样查看慢查询：

# mysqldumpslow /path/to/file

3）收集各种吞吐量和缓存相关信息

      a. 查询Cache：执行SQL语句 show status like 'qcache%' 收集指标值。

      b. 表缓存：MySQL 表对应于磁盘上的文件，表缓存就是文件缓存。执行SQL语句 show status like 'open%tables' 来收集指标值。

      c. 线程缓存：启用了线程缓存后，一个线程在其生命周期中就可以服务多个连接，从而加速MySQL初始连接速度。执行SQL语句 show status like 'threads%' 来收集相关指标值。

      d. 关键字段(key)缓存：跟缓存表一样，缓存关键字段同样可以提高查询效率。执行SQL语句 show status like '%key_read%' 来收集相关指标值。

     e. 临时表：这里的临时表，指的是MySQL内部临时存放需要进一步处理的数据所使用的表，不是我们在存储过程中自定义的临时表。如果临时表放不下需要临时存放的数据，MySQL会使用磁盘来存放，从而降低性能。执行SQL语句 show status like 'created_tmp%' 来收集相关指标值。

     f. 排序区： 当MySQL要执行排序操作的时候，它会使用排序区，如果指定的内存排序区放不下需要排序的数据，MySQL会使用磁盘来存放，从而降低性能。执行SQL语句 show status like 'sort%' 来收集相关指标值。

     g. 全表扫描情况：通常情况下，对大表执行全表扫描很低效，应该尽量避免。执行SQL语句 show status like 'com_select%' 和 show status like  handler_read_rnd_next%' 来收集相关指标值。

 

再次，我们要知道，这些指标的基准值（也即是在数据库正常情况下的值）。

为了判读我们的数据库运行状况是否正常，等待用户反映异常情况是一种方法，通常情况下这种方法不是我们所希望的，因为等待用户反映数据库异常的时候，往往问题都已经很严重了，这时候我们所能做的就是所谓的“应急反应”。更好的更有效的方式是，记录下正常情况下的基准值，然后实时将当前值跟基准值做对比，一旦当前值开始偏移基准值，就应该预警，这样，我们就可以做到所谓的“前馈控制”，在问题刚出现时就解决了。

我们应该收集数据库在各个具有代表性的时间段的基准值，在各种具有代表性的负载时的基准值，尤其是在峰值期间的基准值。收集基准值的方法就是前面讲述的用来获得性能指标值的方法。

对于MySQL性能监控，有几个工具值得一提，mytop, mysqlard 和 mysqlreport，这些工具的安装和使用都很简单，但却是很有用，读者可以试试。

 

最后，我们要明白，这些指标异常时，我们应该采取什么样的手段，使其恢复到正常的能够接受的值。这是下面MySQL优化的内容。

（二）MySQL 性能优化

正如前面所说的那样，MySQL的优化涉及到三个方面：

1）硬件升级：这种方法很简单，系统负载过高了，好啊，加多CPU；内存不够用了，没问题，再加2G内存，等等。“那如果磁盘IO慢了怎么办呢？”读者可能会问。通常情况下，我们不太可能提高磁盘IO的速度（尽管使用合理的RAID级别，比如1+0，可以在一定程度上提高IO性能，但这往往是一锤子的买卖，试想如果数据库都已经投产了，谁还会轻易去重做RAID。）。实际上磁盘IO跟不上，往往并不是由于磁盘本身IO速度低，而是我们的数据库过度读写磁盘造成的。也就是说，本来可以不用读磁盘就可以搞定的操作，由于我们没有配置好，导致数据库也要去读磁盘。这种方式往往是一种权宜之计，只能解决燃眉之急。随着业务量的继续上升，对数据库处理能力的要求越来越高，升级后硬件的处理能力很快就不能满足要求了。 因为数据库的性能问题，往往是由于数据库缺乏可伸缩性引起的。所以，解决数据库性能问题的上策，是提高数据库的可伸缩性。而这种方法，出发点不是解决可伸缩性问题，当然其作用就有限了。而下面的两种方法，是以提高数据库的可伸缩性作为出发点的。

2）优化MySQL进程和内存：

     a. 启用查询缓存： 通过在/etc/my.cnf 文件添加 query_cache_size = < 期望缓存的查询数 > 来启用；

     b. 调整表缓存：通过在/etc/my.cnf文件里面加入 table_cache = <期望缓存的表的数量> 来调整；

     c. 调整MySQL线程缓存：通过在 /etc/my.cnf 文件添加 thread_cache = <期望缓存的进程数> 来调整；

     d. 调整关键字段(key)缓存：通过在/etc/my.cnf文件添加 key_buffer = <期望的关键字段缓存大小>来调整；

     e. 使用临时表：通过在/etc/my.cnf文件添加  tmp_table_size 和max_heap_table_size 来调整；

     f. 设置合适的排序区大小：通过在/etc/my.cnf文件添加 sort_buffer_size = <期望的排序区大小>来调整；

     g. 设置合适的read_buffer_size减少全表扫描次数：通过在/etc/my.cnf文件添加read_buffer_size= <期望的读缓冲区大小>来调整；

     h. 在大表上建索引：

3）优化SQL语句：那些需要调整的SQL语句，往往都被记录在了慢查询日志文件里面了，所以，MySQL会告诉我们该优化哪些SQL语句。接下来就是如何优化的问题。读者可以试试，在一个数百万行的表上执行"select * from <table_name>会产生什么效果。不用说，数据库IO马上就被堵死了。可是，事实上，刚入行的程序员，就有可能写出这样的惊世赫俗的语句来。所以说，作为系统管理员/DBA，有时间的时候多跟程序员聊聊，提醒他们尽量不要犯这样的或其它类似的低级错误，于人于己都好处多多。关于怎样才能写出高效的SQL语句，不敢乱说，一则是因为我不是SQL高手，二则MySQL 和Oracle文档有很好的阐述，我不敢班门弄斧。值得指出的是，执行计划在优化SQL语句的过程中起着十分重要的作用。另外要说明的是，优化SQL语句对于提高数据库的性能往往是最有效的途径。

 

闲话：有很多工具可以用来备份MySQL数据库，不过最有效的最划算的，还是配置MASTER/SLAVE 数据库。这样既起到了实时备份的作用，也可以实现一定程度上的负载分担，提升性能，一箭双雕，何乐而不为。