MySQLMySQL 运行环境建议规范

一律、操作系统环境

操作系统版本选择

CentOS/RHRL/ORACLE Linux 5.x/6.x x86_64 发行版 建议磁盘分区规则

MySQL 运行条件建议规范

挂载点

大小

分区类型

分区用途

/boot

100M

系统默认即可

/boo

swap

4G-16G

swap

swap

/opt

20G

xfs

安装应用软件
应用程序安装在 /opt/app/ 下的子目录,例如 /opt/app/mysql-5.5.37

/

40G ,最小不低于 20G

系统默认即可

/

/home

第一个 Raid 剩余 ,不低于 20G

xfs

存放备份文件目录
备份文件放在 /home/backup 下的子目录,例如 /home/backup/mysql/20140819

/data

第二个 Raid 的全部,根据数据大小决定

xfs

存放 MySQL 数据文件目录
数据文件放在 /data/mysql 下的子目录,例如

 

 

     
/data/mysql/myapp_3306 

/tmp

8-16G

tmpfs

采用 tmpfs,利用内存的共享内存,加速 /tmp 目录的文件读写性能

本参数建议值

1. 调动最特别文件数限制 ulimit -n 65535

以写副 /etc/sysctl.conf,重开后也克立竿见影

2. 修改 IO 调度器设置
io 调度器修改为 deadline,如果是 SSD 或者 PCIe-SSD 设备,则改为 noop,下列两种方法修改: a) 在线动态修改,重开失效

echo “deadline” > /sys/block/sdb/queue/scheduler

这边的 sdb 修改也实在的设施名称,例如 sda,或者 sdc。 b) 修改 /etc/grub.conf,永久生效

改 /etc/grub.conf 配置文件,在 kernel 那行增加一个部署,例如: kernel
/vmlinuz-2.6.18-308.el5 ro root=LABEL=/1 elevator=deadline rhgb quiet

末了,查看核实下 io 调度器的配置,例如:
cat /sys/block/sda/queue/scheduler
noop anticipatory deadline [cfq]
此刻表示 io 调度器采用的凡 cfq,而未我们渴求的 deadline,需要就调动。

3. 修改 swappiness 设置 vm.swappiness = 0

而用该装写副 /etc/sysctl.conf ,重开后也能够立竿见影。

swappiness 是 Linux 的一个水源参数,用来支配 Linux 物理 RAM 内存进行 SWAP 页交换的对立权重,尽量减少系统的页缓存被打内存中革除的气象。
取值范围是0~100,vm.swappiness 的价更小,Linux 内核会尽量不进行 SWAP 交换页的操作,vm.swappiness 的价值更强,linux 会越多的利用 SWAP 空
间。Linux 系统的默认值是 60,当系统要外存时,有 60%的概率使用 SWAP。对于大部分桌面系统,设置为 100 可以增长系统的整体性;对于频繁
据库应用服务器,设置为 0,可以加强物理内存的使用率,进而加强数据库服务之响应性能。

但用注意的凡,RHEL 7 以上版本,如果 vm.swappiness =
0,有或会见由 OOM 问题,导致 mysqld 进程被 OOM-Killer 进程杀掉。 关闭 NUMA 特性

新一替架构的 NUMA 不适用于跑数据库的场面。它本意是以增进内存利用率,但实际效果不好,反而可能引致一个 CPU 的内存尚有剩余,
但另一个不够用,发生 SWAP 的题目,因此建议直接关闭或者修改 NUMA 的调度机制。
a) 修改 /etc/grub.conf,关闭 NUMA,重开后生效

改 /etc/grub.conf 配置文件,在 kernel 那行增加一个布置后再次开生效,例如: kernel
/vmlinuz-2.6.18-308.el5 ro root=LABEL=/1 elevator=deadline numa=off rhgb
quiet

b) 修改 /etc/init.d/mysql 或者 mysqld_safe 脚本,设定启动 mysqld 进程时之 NUMA 调度机制,例如: numactl
–interleave=all /usr/bin/mysqld_safe –datadir=”$datadir”
–socket=”$socketfile” \

–log-error=”$errlogfile” –pid-file=”$mypidfile” \ –user=mysql
>/dev/null 2>&1 &

关闭 CPU 的朴素模式

CPU 启用节能模式继,会节省电量,但为可能带来 CPU 性能降低之题材。因此,运行数据库类业务时,建议关闭节能模式,发挥 CPU 的无限酷
性能。

亚、MySQL 环境 环境规范定义

MySQL 安装包下载地址:
官方地址:http://dev.mysql.com/downloads/mysql/ 选择:Archived versions
»

然后选择:MySQL Community Server
又择 5.5.37 版本与相应的操作系统分支 Linux – Generic:

提议下载预编译好的二进制安装包:Linux – Generic 2.6 (x86, 64-bit),
Compressed TAR Archive

或者选择搜狐国内镜像站,下载速度较快:http://mirrors.sohu.com/mysql/MySQL-5.5/
该镜像站点都无法下充斥 5.5.37 版本,选择时的 5.5.39 也是可以的:http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.39-linux2.6-x86_64.tar.gz

啊可根据个人习惯下充斥源码包还是 RPM 包。

1. 软件设置目录
mysql 预编译包默认安装路径(basedir)
/usr/local/mysql,实际路径可能是 /usr/local/mysql-5.6.16-linux-glibc2.5-x86_64,软链接成 /usr/local/mysql,即 ln
-s /usr/local/mysql-5.6.16-linux-glibc2.5-x86_64 /usr/local/mysql

  1. datadir 目录:
    单实例: /data/mysql
    多实例: /data/mysql/实例名_端口号,例如 /data/mysql/mytest_3306 或者 /data/mysql/yejr_3307

3. 多实例管理默认使用 mysqld_multi 方式
my.cnf 配置文件被, [mysqld] 中是片集体配置,例如: #my.cnf
[client]
port = 3306
socket = mysql.sock

[mysql]
prompt=”\\u@\\h \\D \\R:\\m:\\s [\\d]> #pager=”less
-i -n -S”

tee=/home/mysql/query.log no-auto-rehash

[mysqld_multi]
mysqld = /usr/local/mysql /bin/mysqld_safe mysqladmin =
/usr/local/mysql /bin/mysqladmin log = /home/mysql/mysqld_multi.log

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql datadir = /home/mysql port = 3306

socket = /home/mysql/mysql.sock event_scheduler = 0

#timeout interactive_timeout = 300 wait_timeout = 300

#character set character-set-server = utf8

open_files_limit = 65535 max_connections = 100

max_connect_errors = 100000

#logs
log-output=file
slow_query_log = 1
slow_query_log_file = /home/mysql/slow.log log-error =
/home/mysql/error.log log_warnings = 2
pid-file = /home/mysql/mysql.pid long_query_time = 1
#log-slow-admin-statements = 1 #log-queries-not-using-indexes = 1
log-slow-slave-statements = 1

#binlog
binlog_format = mixed server-id = 10518
log-bin = mybinlog binlog_cache_size = 4M max_binlog_size = 1G
max_binlog_cache_size = 2G sync_binlog = 1 expire_logs_days = 10

#relay log skip_slave_start = 1

max_relay_log_size = 1G relay_log_purge = 1 relay_log_recovery =
1 log_slave_updates #slave-skip-errors=1032,1053,1062

#buffers & cache table_open_cache = 2048 table_definition_cache =
2048 table_open_cache = 2048 max_heap_table_size = 96M
sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size =
256 query_cache_size = 0 query_cache_type = 0 query_cache_limit =
256K query_cache_min_res_unit = 512 thread_stack = 192K
tmp_table_size = 96M key_buffer_size = 8M read_buffer_size = 2M
read_rnd_buffer_size = 16M bulk_insert_buffer_size = 32M

#myisam

myisam_sort_buffer_size = 128M myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1
innodb_data_file_path
= ibdata1:1G:autoextend innodb_flush_log_at_trx_commit
= 1 innodb_log_buffer_size = 64M innodb_log_file_size = 256M
innodb_log_files_in_group = 2 innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1 innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000 transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#端口号为 3306 的实例特殊安排 [mysqld3306]
#点名本实例相应版本的 basedir 和 datadir basedir=
/usr/local/mysql-5.5.37

datadir = /data/mysql/yejr_3306
#重新配置这几乎独选项,不跟大局配置一样,会直接盖上面的全局设置

innodb_buffer_pool_size = 4G transaction_isolation = REPEATABLE-READ

[mysqld3307]
basedir= /usr/local/mysql-5.5.39
datadir = /data/mysql/yejr_3307
#重新配置这几乎独挑选,不与全局配置一样,会一直盖上面的全局设置 innodb_buffer_pool_size
= 2G
innodb_flush_log_at_trx_commit = 2
sync_binlog = 0

参照 my.cnf 配置文件

默认地,所有配置参数都居 /etc/my.cnf 中,不建议每个实例自己一个部署文件。下面是参考的 my.cnf 配置文件: #[mysql]
[mysql]
prompt=”\\u@\\h \\D \\R:\\m:\\s [\\d]> “

#pager=”less -i -n -S” no-auto-rehash tee=”/home/mysql/tee.log”

8、/etc/my.cnf 中,[mysqld] section 默认选项 #my.cnf
[client]
port = 3306

socket = mysql.sock

[mysql]
prompt=”\\u@\\h \\D \\R:\\m:\\s [\\d]> #pager=”less
-i -n -S” tee=/home/mysql/query.log no-auto-rehash

[mysqld_multi]
mysqld = /usr/local/mysql /bin/mysqld_safe mysqladmin =
/usr/local/mysql /bin/mysqladmin log = /home/mysql/mysqld_multi.log

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql datadir = /home/mysql port = 3306

socket = /home/mysql/mysql.sock event_scheduler = 0

#timeout interactive_timeout = 300 wait_timeout = 300

#character set

character-set-server = utf8

open_files_limit = 65535 max_connections = 100 max_connect_errors =
100000

#logs
log-output=file
slow_query_log = 1
slow_query_log_file = /home/mysql/slow.log log-error =
/home/mysql/error.log log_warnings = 2
pid-file = /home/mysql/mysql.pid long_query_time = 1
#log-slow-admin-statements = 1 #log-queries-not-using-indexes = 1
log-slow-slave-statements = 1

#binlog
binlog_format = mixed server-id = 10518
log-bin = mybinlog binlog_cache_size = 4M max_binlog_size = 1G
max_binlog_cache_size = 2G sync_binlog = 1

expire_logs_days = 10

#relay log
skip_slave_start = 1 max_relay_log_size = 1G relay_log_purge = 1
relay_log_recovery = 1 log_slave_updates
#slave-skip-errors=1032,1053,1062

#buffers & cache table_open_cache = 2048 table_definition_cache =
2048 table_open_cache = 2048 max_heap_table_size = 96M
sort_buffer_size = 2M join_buffer_size = 2M thread_cache_size =
256 query_cache_size = 0 query_cache_type = 0 query_cache_limit =
256K query_cache_min_res_unit = 512 thread_stack = 192K
tmp_table_size = 96M key_buffer_size = 8M read_buffer_size = 2M

read_rnd_buffer_size = 16M bulk_insert_buffer_size = 32M

#myisam myisam_sort_buffer_size = 128M myisam_max_sort_file_size
= 10G myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 1G innodb_buffer_pool_instances = 1
innodb_data_file_path
= ibdata1:1G:autoextend innodb_flush_log_at_trx_commit
= 1 innodb_log_buffer_size = 64M innodb_log_file_size = 256M
innodb_log_files_in_group = 2 innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1 innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000 transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT 

转自吴先生 http://wubx.net/

网站地图xml地图