MySQL 5.1.58+google-perftools-1.8.3使用innodb-plugin

软件:MySQL 5.1.58 libunwind 1.0 google-perftools-1.8.3

一、下载软件:

MySQL:

https://mirrors.sohu.com/mysql/MySQL-5.1/mysql-5.1.58.tar.gz

libunwind:

https://download.savannah.gnu.org/releases/libunwind/libunwind-1.0.tar.gz

google-perftools-1.8.3:

https://google-perftools.googlecode.com/files/google-perftools-1.8.3.tar.gz

二、安装

添加用户和组

groupadd mysql

useradd -g mysql -s /sbin/nologin mysql

安装libunwind

1、tar zxf libunwind-1.0.tar.gz 

2、cd libunwind-1.0

3、autoreconf -fi

4、CHOST="x86_64-redhat-linux" \

CFLAGS="-O3 -fPIC \

-fomit-frame-pointer \

-pipe -m64 -msse \

-mmmx -msse2 \

-march=nocona \

-mfpmath=sse \

-m128bit-long-double \

-ftree-loop-linear \

-fprefetch-loop-arrays \

-fno-omit-frame-pointer \

-maccumulate-outgoing-args \

-freg-struct-return -fgcse-sm -fgcse-las \

-frename-registers -fforce-addr -fivopts \

-ftree-vectorize -ftracer -frename-registers \

-minline-all-stringops -fbranch-target-load-optimize2" \

CXXFLAGS="${CFLAGS}" ./configure

5、make && make install

安装google-perftools-1.8.3

1、tar zxf google-perftools-1.8.3.tar.gz 

2、cd google-perftools-1.8.3

3、CHOST="x86_64-redhat-linux" \

CFLAGS="-O3 -fomit-frame-pointer \

-pipe -mmmx -msse -msse2 -m64 \

-march=nocona -mfpmath=sse \

-m128bit-long-double \

-maccumulate-outgoing-args \

-ftree-loop-linear -fprefetch-loop-arrays \

-fno-omit-frame-pointer -freg-struct-return \

-fgcse-sm -fgcse-las -frename-registers \

-fforce-addr -fivopts -ftree-vectorize -ftracer \

-frename-registers -minline-all-stringops \

-fbranch-target-load-optimize2" \

CXXFLAGS="${CFLAGS} -DTCMALLOC_LARGE_PAGES" \

./configure \

--disable-cpu-profiler \

--disable-heap-profiler \

--disable-heap-checker \

--disable-debugalloc \

--enable-minimal \

--enable-frame-pointers

4、make && make install 

5、添加库

echo "/usr/local/lib" > /etc/ld.so.conf.d/usr_local_lib.conf

echo "/usr/lib" >> /etc/ld.so.conf.d/usr_local_lib.conf

echo "/usr/lib64" >> /etc/ld.so.conf.d/usr_local_lib.conf

echo "/usr/local/lib64" >> /etc/ld.so.conf.d/usr_local_lib.conf

/sbin/ldconfig

三、安装MySQL 5.1.58,由于安装的是innodb-plugin，所以不使用静态编译

1、tar zxf mysql-5.1.58.tar.gz 

2、cd mysql-5.1.58

3、CXX=gcc CHOST="x86_64-redhat-linux" \

CFLAGS="-O3 -fomit-frame-pointer \

-march=nocona -mfpmath=sse \ 

-mmmx -msse -msse2 -m64 -pipe \

-m128bit-long-double \

-ftree-loop-linear \

-fprefetch-loop-arrays \

-fforce-addr -fivopts \

-maccumulate-outgoing-args \

-fgcse-sm -fgcse-las -ftracer \

-freg-struct-return -frename-registers \

-ftree-vectorize -frename-registers \

-minline-all-stringops -fno-exceptions \

-fno-omit-frame-pointer -fbranch-target-load-optimize2" \

CXXFLAGS="${CFLAGS}" \

LDFLAGS="-lrt -lunwind -ltcmalloc_minimal -lstdc++ " \

./configure \

--prefix=/opt/mysql \

--with-server-suffix=-greenshore \

--with-mysqld-user=mysql \ --with-plugins=partition,heap,innodb_plugin,myisam \

--with-collation=utf8_general_ci \

--with-extra-charsets=gbk,utf8,ascii \

--with-big-tables \

--with-fast-mutexes \

--with-zlib-dir=bundled \

--with-readline \

--with-pthread \

--enable-assembler \

--enable-profiling \

--enable-local-infile \

--enable-thread-safe-client \

--without-embedded-server \

--with-charset=utf8 \

--without-geometry \

--without-debug \

--without-ndb-binlog \

--without-ndb-debug

4、make && make install

5、创建数据库数据目录

mkdir /opt/mysql/data

6、将mysql lib 添加到ld.so.conf中

echo "/opt/mysql/lib/mysql" >> /etc/ld.so.conf

/sbin/ldconfig

ln -s /opt/mysql/lib/mysql /usr/lib64/mysql

ln -s /opt/mysql/include/mysql /usr/include/mysql

7、编辑/etc/my.cnf，内容看附录

8、修改my.cnf 将默认引擎修改为myisam，对于所有innodb参数都用#注释掉,初始化数据库

sed -i 's:^innodb:#innodb:' /etc/my.cnf

sed -i 's:skip-innodb-doublewrite:#skip-innodb-doublewrite:' /etc/my.cnf

sed -i 's:default-storage-engine = innodb:default-storage-engine = myisam:' /etc/my.cnf

/opt/mysql/bin/mysql_install_db --user=mysql --basedir=/opt/mysql --datadir=/opt/mysql/data

chown -R mysql:mysql /opt/mysql/

9、拷贝数据库启动脚本

cp /opt/mysql/share/mysql/mysql.server /etc/init.d/rc.mysql

chmod 755 /etc/init.d/rc.mysql

/etc/init.d/rc.mysql start

/opt/mysql/bin/mysqladmin -u root password yourpassword   //给root设置密码

/etc/init.d/rc.mysql stop

10、修改my.cnf将innodb参数都打开，并且将默认引擎修改为innodb

sed -i 's:#innodb:innodb:' /etc/my.cnf

sed -i 's:#skip-innodb-doublewrite:skip-innodb-doublewrite:' /etc/my.cnf

sed -i 's:default-storage-engine = myisam:default-storage-engine = innodb:' /etc/my.cnf

rm -rf /opt/mysql/data/ib*  //删除innodb产生的文件

/etc/init.d/rc.mysql start  //重启MySQL，此时my.cnf中所有关于innodb的参数都将生效

11、将mysql执行文件添加到系统PATH

echo "export PATH=$PATH:/opt/mysql/bin" >> /etc/profile

source /etc/profile

附：my.cnf ,配置文件适合32G内存

[client]

port = 3306

socket = /tmp/mysql.sock

[mysqld]

default-storage-engine = innodb                                                                        

character-set-server = utf8

collation-server = utf8_general_ci

lower_case_table_names = 1            

basedir = /opt/mysql

datadir = /opt/mysql/data

ft_min_word_len = 4

user = mysql

## File

back_log = 500

open-files-limit = 102400

open-files = 4096 

port = 3306

socket = /tmp/mysql.sock

pid-file = /tmp/mysql.pid

skip-external-locking

skip-name-resolve

## Logging

log_error = /opt/mysql/data/mysql-error.err

log_warnings

log_bin = /opt/mysql/data/mysql-bin

expire_logs_days =  2

#log-slow-queries =/opt/mysql/data/slowquery.log

#long_query_time = 1 

max_binlog_size = 512M  

binlog_format = mixed

## Per-Thread Buffers * (max_connections) = total per-thread mem usage

thread_stack = 256K   #default: 32bit: 192K, 64bit: 256K

sort_buffer_size = 8M   #default: 2M, larger may cause perf issues

read_buffer_size = 2M   #default: 128K, change in increments of 4K

read_rnd_buffer_size = 16M   #default: 256K    

join_buffer_size = 8M   #default: 128K

binlog_cache_size = 64K   #default: 32K, size of buffer to hold TX queries

## Query Cache

query_cache_size = 64M #global buffer

query_cache_limit = 2M #max query result size to put in cache

## Connections

max_connections = 4000 #multiplier for memory usage via per-thread buffers

max_connect_errors = 100 #default: 10

concurrent_insert = 2 #default: 1, 2: enable insert for all instances

connect_timeout = 30 

max_allowed_packet = 64M #max size of incoming data to allow

net_buffer_length = 1024K

## Default Table Settings

sql_mode = NO_AUTO_CREATE_USER

## Table and TMP settings

max_heap_table_size = 1G 

bulk_insert_buffer_size = 1G 

tmp_table_size = 1G   

tmpdir = /dev/shm

## Table cache settings

table_cache = 2048 

table_open_cache = 2048 

## Thread settings

thread_concurrency = 32  

thread_cache_size = 100 

## MyISAM Specific options

key_buffer_size = 256M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 128M

myisam_max_sort_file_size = 2G

myisam_repair_threads = 1

myisam_recover

# *** INNODB Specific options ***

ignore-builtin-innodb

plugin-load=innodb=ha_innodb_plugin.so;innodb_trx=ha_innodb_plugin.so;innodb_locks=ha_innodb_plugin.so;innodb_lock_waits=ha_innodb_plugin.so;innodb_cmp=ha_innodb_plugin.so;innodb_cmp_reset=ha_innodb_plugin.so;innodb_cmpmem=ha_innodb_plugin.so;innodb_cmpmem_reset=ha_innodb_plugin.so

innodb_io_capacity  = 200

innodb_write_io_threads  = 16

innodb_read_io_threads  = 16

innodb_data_file_path  = ibdata1:128M;ibdata2:10M:autoextend

innodb_log_file_size  = 512M

innodb_log_files_in_group = 4

innodb_buffer_pool_size  = 2G  

innodb_additional_mem_pool_size = 16M 

innodb_status_file  = 1   

innodb_file_per_table  = 1 

innodb_file_format = barracuda

innodb_strict_mode = 1

innodb_flush_log_at_trx_commit = 2 

innodb_table_locks  = 0 

innodb_log_buffer_size  = 128M 

innodb_lock_wait_timeout  = 60 

innodb_thread_concurrency = 32 

innodb_commit_concurrency = 8 

innodb_flush_method  = O_DIRECT    

innodb_support_xa  = 0     

innodb_use_sys_malloc = 0

skip-innodb-doublewrite

## Binlog sync settings

sync_binlog   = 0

## TX Isolation

transaction-isolation  = REPEATABLE-READ 

[mysqldump]

quick

max_allowed_packet = 128M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 512M

read_buffer = 8M

write_buffer = 8M

[mysqlhotcopy]

interactive-timeout

[mysqld_safe]

open-files-limit = 102400