Mysql安装优化

安装mysql


从官网下在源文件

wget http://downloads.mysql.com/archives/mysql-5.1/mysql-5.1.48.tar.gz


解压源文件TAR包
tar zxvf mysql-5.1.48.tar.gz


编译安装
CXX=gcc \
CHOST="x86_64-pc-linux-gnu" \
CFLAGS=" -O3" \
CXXFLAGS="${CFLAGS}" \
./configure \
--prefix=/export/mysql \
--localstatedir=/export/mysql/data \
--with-plugins=innodb_plugin,innobase,partition,myisam,heap \
--enable-assembler \
--with-charset=utf8 \
--with-extra-charsets=all \
--enable-thread-safe-client \
--with-client-ldflags=-all-static \
--with-fast-mutexes \
--enable-static=yes \
--with-big-tables 


添加mysql 组和用户
groupadd  mysql 
useradd mysql -g mysql  -d /export/mysql -s /sbin/nologin

修改mysql 文件夹权限

chown mysql.mysql /export/mysql  -R


修改mysql 配置文件

vi /etc/my.cnf


[client]
port            = 3306
socket          = /tmp/mysql.sock



[mysqld]
port            = 3306
socket          = /tmp/mysql.sock




back_log = 50  # mysql 连接数量
max_connect_errors = 10  #MySQL中与安全有关的计数器值，它负责阻止过多尝试失败的客户端以防止暴力破解密码的情况。
max_allowed_packet = 16M   #mysql 通信数据包限制，
binlog_cache_size = 1M   #二进制log缓存区
max_heap_table_size = 64M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
sort_buffer_size = 8M
join_buffer_size = 8M
thread_cache_size = 32
thread_concurrency = 8
query_cache_size = 128M
query_cache_limit = 2M
ft_min_word_len = 4

ignore_builtin_innodb
plugin_load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so  #　InnoDB Plugin特性：包括快速DDL、压缩存储等

default-table-type = InnoDB  #默认表类型
character-set-server = utf8
table_open_cache = 1024  
wait_timeout=86400
interactive_time=86400
max_connections = 2000
max_allowed_packet = 16M
event_scheduler=1
lower_case_table_names=1
open_files_limit=10000
key_buffer_size = 128M
query_cache_type = 0
thread_stack = 192K
transaction_isolation = REPEATABLE-READ
tmp_table_size = 64M

log-bin=master-bin
log-bin-index=master-bin.index
binlog_format=mixed

slow_query_log
long_query_time = 2


server-id = 1

key_buffer_size = 32M
bulk_insert_buffer_size = 64M
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
myisam_recover

innodb_additional_mem_pool_size = 16M
innodb_buffer_pool_size = 6G                    
innodb_data_file_path = ibdata1:1024M:autoextend
#innodb_write_io_threads = 8
#innodb_read_io_threads = 8
innodb_thread_concurrency = 16
innodb_flush_log_at_trx_commit = 1
innodb_log_buffer_size = 8M
innodb_log_file_size = 256M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120


[mysqldump]
quick

max_allowed_packet = 16M

[mysql]
no-auto-rehash


[myisamchk]
key_buffer_size = 512M
sort_buffer_size = 512M
read_buffer = 8M
write_buffer = 8M

[mysqlhotcopy]
interactive-timeout

[mysqld_safe]
open-files-limit = 819


安装gperftools
gperftools 包含四个工具，分别是：TCMalloc、heap-checker、heap-profiler 和 cpu-profiler，TCMalloc是 gperftools 的其中一个工具，用于优化C++写的多线程应用，与标准的glibc库的malloc相比，TCMalloc在内存的分配效率和速度要高，可以在高并发的情况下很好的控制内存的使用，提高服务器的性能，降低负载。
使用 TCMalloc 优化 Nginx 和 MySQL 的内存管理，性能将会有一定程度的提升，特别是对MYSQL服务器高并发下情况下的性能。
安装 libunwind 库 如果系统是64位的需要先安装libunwind库，32位系统则不需要安装。
libunwind 库为基于64位CPU和操作系统的程序提供了基本的堆栈辗转开解功能，其中包括用于输出堆栈跟踪的API、用于以编程方式辗转开解堆栈的API以及支持C++异常处理机制的API。


下载gperftools
gperftools

wget --no-check-certificate  https://gperftools.googlecode.com/files/gperftools-2.0.tar.gz
tar zxvf gperftools-2.0.tar.gz
cd gperftools-2.0
./configure --enable-frame-pointers
make&&make install
vi /etc/ld.so.conf.d/usr_local_lib.conf
添加
/usr/local/lib


/sbin/ldconfig

修改 mysql下bin目录下的 mysqld_safe 文件添加
export LD_PRELOAD=/usr/local/lib/libtcmalloc.so



重新启动mysql 后执行
/usr/sbin/lsof -n | grep tcmalloc
如果有一条类似于下边的进程表示安装OK
mysqld    26670     mysql  mem       REG      253,0  1725572    3094905 /usr/local/lib/libtcmalloc.so.4.1.0