Linux下MySQL编译和优化

一、编译安装 MySQL 

下载 MySQL Community Server（社区版），请根据你的操作系统版本下载相应的文件。

MySQL 下载地址：https://dev.mysql.com/downloads/mysql

1.1 cmake 工具

CMake 是一个跨平台的安装（预编译）工具，可以用简单的语句来描述所有平台的安装（编译过程），他能够输出各种 Makefile。

yum 安装：

yum install cmake -y

也可以在 https://cmake.org/download 下载最新版本的 cmake 手动编译安装。

1.2 boost 类库

C++ 语言标准库提供扩展的一些 C++ 库的总称。

如果选择 mysql-boost 版本的 MySQL 默认包含该类库。

1.3 git 工具

yum 安装：

yum install git -y

也可以在 https://git-scm.com/download 下载最新版本的 git 手动编译安装。

1.4 c/c++ 编译器

yum 安装：

yum install gcc gcc-c++ -y

1.5 Curses 库

在 Linux/Unix 下广泛应用的图形函数库。

yum 安装：

yum install ncurses ncurses-devel -y

1.6 编译安装 MySQL

1）预编译

进入 MySQL 源码目录，执行：

cmake . -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DSYSCONFDIR=/etc -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DMYSQL_DATADIR=/var/lib/mysql/data -DWITH_BOOST=boost 

这一步如果出错需要重新执行 cmake 预编译， 请先删除 CMakeCache.txt 文件。

2）编译安装

默认 make 编译时，只使用了单核进行编译。如果你是多核系统，可以加入 -j  [n] 参数指定多核同时编译。

可以通过查看 /proc/cpuinfo 文件查看 CPU 信息：

cat /proc/cpuinfo | grep processor | wc -l

也可以合并执行：

make -j `cat /proc/cpuinfo | grep processor | wc -l`

make 编译时如果报错  G++: Internal Error: Killed (Program Cc1plus) ：

出现这个问题的主要原因是内存不足，可以临时使用交换分区来解决。

（1）增加临时 swap 空间

dd if=/dev/zero of=/home/swap bs=1024 count=500000  
mkswap /home/swap
swapon /home/swap

（2）关闭创建的 swap 空间

swapoff

参考：https://blog.csdn.net/fzh90/article/details/22893683

根据服务器配置的不同编译需要一定的时间，我这里大概用了1小时。

编译成功后，执行  make install 

3）初始化配置

进入 /etc 目录，创建 my.cnf 配置文件：

[mysqld]
user=mysql
basedir=/usr/local/mysql
datadir=/var/lib/mysql/data
socket=/var/lib/mysql/mysql.sock
log_error=/var/lib/mysql/mysql-error.log
pid-file=/var/lib/mysql/mysql.pid
# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

[mysqld_safe]
log-error=/var/log/mysqld.log
pid-file=/var/run/mysqld/mysqld.pid

根据配置，我们首先需要新增用户 mysql ，用来启动 MySQL 服务：

useradd mysql -M -s /sbin/nologin

然后还需要赋予 /var/lib/mysql 目录 mysql 用户的权限：

chown -R mysql:mysql /var/lib/mysql

4）创建启动脚本

安装 MySQL 后，默认自带一个启动脚本：/usr/local/mysql/support-files/mysql.server。将它复制到 /etc/init.d 目录中，命名为 mysqld：

cp /usr/local/mysql/support-files/mysql.server     /etc/init.d/mysqld

将 mysqld 启动脚本加入 service 管理：

chkconfig mysqld on

5）初始化 MySQL

进入 /usr/local/mysql/bin 目录中，执行：

mysqld --initialize --user=mysql --basedir=/var/lib/mysql --datadir=/var/lib/mysql/data

执行后系统会生成一个临时的 root 账户密码，记录下来用于首次登录。

1.7 客户端连接

1）mysql.sock 问题

初始化 MySQL 后首次连接 mysql -uroot -p 报错：Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)

原因是 MySQL 找不到 /tmp/mysql.sock 文件，因为在之前的配置中将 MySQL 服务端的 sock 文件存放在了指定目录 /var/lib/mysql/mysql.sock

当你在本机连接服务器的时候，并不通过 tcp，而是发起本地连接。甚至不需要指定 IP 是什么。mysql.sock 是随每一次 mysql server启动生成的。而 MySQL 客户端工具默认就是使用 socket 方式连接。

解决方案①：给 mysql.sock 建立软连接

ln -s /var/lib/mysql/mysql.sock /tmp/mysql.sock

解决方案②：修改 my.cnf 配置，增加 [mysql] 节点，指定客户端连接 mysql.sock 文件位置

[mysql]
socket=/var/lib/mysql/mysql.sock

解决方案③：指定使用 tcp 方式连接

mysql -uroot --protocol=tcp -p

 2）重置密码

 首次连接进 MySQL 后，需要重置密码才能操作：

执行 MySQL 命令：

ALTER USER USER() IDENTIFIED BY 'txl';

user() 函数代表获取当前用户，即：root@localhost

3）新增远程连接用户

① 限定用户可以操作的数据表和ip：

grant all on 表名 to 用户名@'IP地址' identified by '密码'

② 允许所有表和ip：

grant all on *.* to 用户名@'%' identified by '密码'

最后执行  flush privileges  刷新权限。

新增用户成功后，可以通过系统表查看刚刚创建的用户：

二、实例—商品与订单处理

数据表：商品表（products）、订单表（orders）、订单明细表（orders_detail）、缓存信息表（sys_cache）

2.1 商品销量排行统计

如果数据量较大，动态获取商品销量排行（多表关联查询）是很耗费性能的，因此常见的做法：

① 在系统后台手动执行销量排行，生成一个静态文件（html/xml/json等）。或者存放在 Memcached中，每隔一段时间自动更新。

② 利用 Shell 调用 Mysql 客户端执行 SQL 语句，将返回的结果集拼凑为一个 id 集合的字符串，最后插入进数据库的缓存信息表（sys_cache）中，使用时查询该表并 where id in() 即可，下面就使用该方案统计结果。

在 shell 中调用 Mysql客户端：

mysql -e "SQL语句"  # 执行SQL语句
mysql > SQL文件     # 执行SQL文件

其他相关参数：

• -N：不显示列名
• -H：以 html 格式输出
• -s：一行行输出，列与列直接用 tab 分隔

1）使用 SQL 聚合函数统计

» sql_prodsale：

use txl;
SELECT a.*,b.prod_sale FROM products a,(SELECT a.prod_id,SUM(prod_num) as prod_sale FROM orders_detail a, orders b WHERE a.order_id=b.order_id AND b.order_state=1 GROUP BY prod_id ORDER BY prod_sale DESC) b WHERE a.prod_id=b.prod_id;

» shell_prodsale：

SQL_FILE="/root/mysqlshell/sql_prodsale"

# 获取销量排行的商品id集合，中间用逗号分隔 
GET_IDS=` mysql -uroot -ptxl  < $SQL_FILE -N | awk 'BEGIN{ids="";s=","}{ids=ids""$1""s}END{print ids"0"}'` ;
# 插入缓存表中
UPDATE_SQL="update txl.sys_cache set cache_value='$GET_IDS' where cache_key='prodsale'"
echo $UPDATE_SQL | mysql -uroot -ptxl

if [ $? -eq 0 ];then
 echo 'success!'
else
 echo 'error'
fi

执行 shell_prodsale 统计脚本：

2）使用 Shell 脚本统计

» shell_prodsale2：

GET_IDS=`mysql -uroot -ptxl -N -e "select prod_id,prod_num from txl.orders_detail" | sort -n | awk '
BEGIN{ last="";sum=0 }
{  
   if(last!=$1 && last!="")
   { 
      print last"  "sum;
      last=$1;
      sum=1;
   } else {
      sum+=$2;
      last=$1;
   }
}
END{ print last"  "sum }' | sort -nr -k 2 | awk 'BEGIN{ids="";s=","}{ids=ids""$1""s}END{print ids"0"}'`

# 插入缓存表中
UPDATE_SQL="update txl.sys_cache set cache_value='$GET_IDS' where cache_key='prodsale'"
echo $UPDATE_SQL | mysql -uroot -ptxl

if [ $? -eq 0 ];then
 echo 'success!'
else
 echo 'error'
fi

执行 shell_prodsale2 统计脚本：

最后查看 sys_cache 数据表，商品销量排行的 id 集合成功插入到了 sys_cache 数据表中：

三、MySQL 调优

3.1 清理僵尸连接

执行  show full processlist  可以查看 mysql 连接，如果是 root 用户可以查看所有连接，普通用户则只能查看自己占用的连接。也可以通过查询系统表 information_schema.processlist 查看所有连接。

执行  show variables like 'max_connections'  可以查看最大连接数。

如果是持久连接，执行  show variables like 'wait_timeout'  可以查看持久连接超时时间。

执行  kill <连接ID>   可以清理指定连接。

» clearProcess（清理 sleep 时间大于1000的连接）：

SQL='select concat("kill ",ID,";") from information_schema.PROCESSLIST where Time>1000 AND COMMAND="sleep" AND DB="txl"';
PROCESS_LIST=`mysql -uroot -ptxl -N -e "$SQL"`;
mysql -uroot -ptxl -e "$PROCESS_LIST";

执行 clearProcess 清理脚本：

重新查看 mysql 连接：

3.2 检测 MySQL 状态（是否可用）

客户端工具 mysqladmin 可以执行一系列数据库相关操作。

常用参数：

• create databasename：创建一个新数据库
• drop databasename：删除一个数据库及其所有表
• extended-status：给出服务器的一个扩展状态消息
• flush-hosts：洗掉所有缓存的主机
• flush-logs：洗掉所有日志 
• flush-tables：洗掉所有表 
• flush-privileges：再次装载授权表（同 reload） 
• kill <id,id,...>：杀死 mysql 线程 
• password：新口令，将老口令改为新口令
• ping：检查 mysqld 是否活着 
• processlist：显示服务其中活跃线程列表
• reload：重载授权表 
• refresh：洗掉所有表并关闭和打开日志文件
• shutdown：关掉服务器 
• status：给出服务器的简短状态消息
• variables：打印出可用变量
• version：得到服务器的版本信息

执行 mysqladmin 如果报错：error: 'Can't connect to local MySQL server through socket '/tmp/mysql.sock' (2)'

原因是 mysqladmin 找不到 /tmp/mysql.sock 文件，需要在 my.cnf 配置文件中增加 [client] 节点配置：

[client]
socket=/var/lib/mysql/mysql.sock

其中，ping 用来检测 MySQL 是否活跃，可以探测 MySQL 是否宕机：

mysqladmin -uroot -ptxl ping

如果显示 mysqld is alive 则说明正常：

如果失败则需要进行相关调试，并清理僵尸连接或重启 MySQL 服务。

3.3 定时任务（crontab）

1）安装

yum install vixie-cron -y  # 定时任务主程序
yum install crontabs -y # 相关工具包，用来定制计划任务等（为了方便我们使用定时任务所开发的工具）

启动服务：

service crond start

2）添加任务

有两种方法：

• 执行 crontab -e 然后添加相应的任务。自动保存在 /var/spool/cron 目录（针对每个用户）
• 编辑 /etc/crontab 配置文件，然后添加相应的任务（全局，用于系统执行）

执行 crontab -l 可以查看当前用户的计划任务。

3）参数

• Minute：每个小时的第几分钟执行该任务。取值范围0-59
• Hour：每天的第几个小时执行该任务。取值范围0-23
• Day：每月的第几天执行该任务。取值范围1-31
• Month：每年的第几个月执行该任务。取值范围1-12
• DayOfWeek：每周的第几天执行该任务。取值范围0-6，0表示周末
• CommandPath：指定要执行的程序路径

4）时间格式

• *：表示任意的时刻。如小时位 * 则表示每个小时
• n：表示特定的时刻。如小时位 5 就表示5时
• n,m：表示特定的几个时刻。如小时位 1,10 就表示1时和10时
• n－m：表示一个时间段。如小时位 1-5 就表示1到5点
• */n：表示每隔多少个时间单位执行一次。如小时位 */1 就表示每隔1个小时执行一次命令，也可以写成 1-23/1

5）实例

30 21 * * * /etc/init.d/mysqld restart 　　　　# 每晚的21:30重启mysql
45 4 1,10,22 * * /etc/init.d/mysqld restart 　# 每月1、10、22日的4:45重启mysql
10 1 * * 6,0 /etc/init.d/mysqld restart 　    # 每周六、周日的1:10重启mysql
0,30 18-23 * * * /etc/init.d/mysqld restart　 # 在每天18:00至23:00之间每隔30分钟重启mysql
0 23 * * 6 /etc/init.d/mysqld restart     　  # 每星期六的11:00 pm重启mysql
* */1 * * * /etc/init.d/mysqld restart      　# 每一小时重启mysql
* 23-7/1 * * * /etc/init.d/mysqld restart  　 # 晚上11点到早上7点之间，每隔一小时重启mysql
0 11 4 * mon-wed /etc/init.d/mysqld restart 　# 每月的4号与每周一到周三的11点重启mysql
0 4 1 jan * /etc/init.d/mysqld restart      　# 一月一号的4点重启mysql

每次任务执行后，可以通过 /var/spool/mail/root 文件查看执行状态：

3.4 一台机器运行多个 MySQL 实例

mysqld_multi 工具用于启动多个 MySQL 实例。--example 参数会生成一个配置模板。将它保存到配置文件 /etc/my_multi.cnf：

mysqld_multi --example > /etc/my_multi.cnf

修改 my_multi.cnf 配置如下：

[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = mysql　　# 宿主中使用的用户名
password   = 123

[mysqld2]
socket     = /var/lib/mysql/mysql2.sock
port       = 3307
pid-file   = /var/lib/mysql/mysql2.pid
datadir    = /var/lib/mysql/data2
user       = mysql

启动（会自动生成相关库文件）：

mysqld_multi --defaults-extra-file=/etc/my_multi.cnf start

查看 root 临时密码：

cat /var/lib/mysql/mysql-error.log | grep password

客户端登录：

mysql -S /var/lib/mysql/mysql2.sock -p

然后输入刚刚保存的临时密码就可以登录成功了。

参考 《利用mysqld_multi启动管理多实例》：https://www.cnblogs.com/bolinzhihua/p/4a600d818feb3861bb41ec67ee512163.html