利用tcmalloc優化mysql主從

實作環境：mysql 5.5.27,cenot 6.2 64bit，centos 最小化安裝。軟體cmake-2.8.8.tar.gz，

libunwind-1.0.tar.gz ，gperftools-2.0.tar.gz 。

一，主伺服器IP:192.168.1.15,從伺服器IP:192.168.1.16。先配置主伺服器，安裝好系統後需要安裝開發環境：yum -y groupinstall 'Development tools' ,centos 6.0的系統隻需安裝Development tools。

二，安裝libunwind和gperftools。

安裝libunwind庫。

# tar xf libunwind-1.0.tar.gz

# tar xf libunwind-1.0

#CFLAGS=-fPIC ./configure添加編譯參數

# make CFLAGS=-fPIC

# make CFLAGS=-fPIC install

這樣執行會出現如下錯誤：

libtool: install: error: relink `libunwind-setjmp.la' with the above command before installing it

make[3]: *** [install-libLTLIBRARIES] Error 1

make[3]: Leaving directory `/down/libunwind-1.0/src'

make[2]: *** [install-am] Error 2

make[2]: Leaving directory `/down/libunwind-1.0/src'

make[1]: *** [install] Error 2

make[1]: Leaving directory `/down/libunwind-1.0/src'

make: *** [install-recursive] Error 1

産生這種問題的原因：autotools相容性的問題，運作指令autoreconf -i -f ，在重新編譯安裝下。

#autoreconf -i -f

#make clean

#CFLAGS=-fPIC ./configure

#make CFLAGS=-fPIC

#make CFLAGS=-fPIC install

安裝gpperftools：

#tar xf gperftools-2.0.tar.gz

# cd gperftools-2.0

#./configure

#make && make install

這樣安裝會會報如下錯誤：

cc1plus: warning: unrecognized command line option "-Wno-unused-result"

ake: *** [stacktrace.lo] Error 1

為什麼出現在這種錯誤，是以是64bit系統，至于更深度的解釋，安裝說明中有。下面重新編譯。

#./configure --enable-frame-pointers

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

#ldconfig

三，安裝cmake與mysql

安裝cmake

#tar xf cmake-2.8.8.tar.gz

#cd cmake-2.8.8

# ./bootstrap

# gmake && gmake install

安裝mysql

#useradd -r mysql -s /sbin/nologin

#mkdir -p /data/mysql/{data,binlog,relaylog}

#chown mysql:mysql -R /data/mysql

# tar xf mysql-5.5.27.tar.gz

#cd mysql-5.5.27

#cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql -DDEFAULT_CHARSET=utf8 -DDEFAULT_COLLATION=utf8_general_ci -DEXTRA_CHARSETS=all -DWITH_MYISAM_STORAGE_ENGINE=1       -DWITH_INNOBASE_STORAGE_ENGINE=1 -DENABLED_LOCAL_INFILE=1 -DMYSQL_DATADIR=/data/mysql/data -DWITH_DEBUG=0-DSYSCONFDIR=/etc -DMYSQL_TCP_PORT=3306

#會編譯不過去，提示需要安裝ncurses-devel,yum -y install ncurses-devel

#rm -f CMakeCache.txe 重新執行./configure

# make && make install

#cd /usr/local/mysql

#chown -R mysql:mysql *

# cp support-files/mysql.server /etc/rc.d/init.d/mysqld

#chmod +x /etc/init.d/mysqld

#chkconfig mysqld on

# vim /etc/profile 添加PATH=$PATH:/usr/local/mysql/bin

# . /etc/profile(或者export PATH=$PATH:/usr/local/mysql/bin)

# ln -sv /usr/local/mysql/include/ /usr/include/mysql

# echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf 加載庫檔案

# ldconfig -v |grep mysql

# vim /etc/man.config 添加一行MANPATH /usr/local/mysql/man

#sed -i '/# executing mysqld_safe/a\export LD_PRELOAD=/usr/local/lib/libtcmalloc.so' /usr/local/mysql/bin/mysqld_safe 

環境已經基本Ok

# cd /usr/local/mysql

scripts/mysql_install_db --user=mysql--datadir=/data/mysql

将主伺服器克隆一份，并修改IP與MAC位址，保證兩台機器能夠互相ping通。

三，下面是master的配置檔案：

[client]

default-character-set = utf8

port=3306

socket = /tmp/mysql.sock

[mysqld]

user = mysql

basedir = /usr/local/mysql

datadir=/data/mysql/data

pid-file = /data/mysql/mysql.pid

log-error = /data/mysql/mysql-error.log

log_slave_update =1

log-bin = /data/mysql/binlog/mysql-bin

log-bin-index = /data/mysql/binlog/mysql-bin.index

binlog_format = mixed

binlog_cache_size = 4M

max_binlog_cache_size = 8M

max_binlog_size = 1G

expire_logs_days = 30

key_buffer_size = 384M

sort_buffer_size = 2M

read_buffer_size = 2M

read_rnd_buffer_size =16M

join_buffer_size =2M

thread_cache_size = 300

query_cache_limit = 2M

query_cache_min_res_unit =2K

thread_concurrency = 8

table_cache =614

table_open_cache = 512

open_files_limit = 10240

back_log = 600

max_connections = 5000

max_connect_errors = 6000

external-locking = FALSE

max_allowed_packet =16M

default_storage_engine = MyISAM

#default_storage_engine = InnoDB

thread_stack =192K

transaction_isolation = REPEATABLE-READ

tmp_table_size = 256M

max_heap_table_size =512M

bulk_insert_buffer_size = 64M

myisam_sort_buffer_size = 64M

myisam_max_sort_file_size = 10G

myisam_repair_threads = 1

myisam_recover

long_query_time = 2

skip-name-resolve

skip-external_locking

#skip-innodb

innodb_additional_mem_pool_size = 16M

innodb_buffer_pool_size = 512M

innodb_data_file_path = ibdata1:256M:autoextend

innodb_file_io_threads = 4

innodb_thread_concurrency = 8

innodb_flush_log_at_trx_commit =2

innodb_log_file_size =128M

innodb_log_files_in_group = 3

innodb_max_dirty_pages_pct = 90

innodb_lock_wait_timeout =120

innodb_file_per_table = 0

interactive_timeout=120

wait_timeout=120

slave-skip-errors=1032,1062,126,1114,1146,1048,1396

server-id=1

[mysqldump]

quick

max_allowed_packet = 64M

[mysql]

no-auto-rehash

[myisamchk]

key_buffer_size = 256M

sort_buffer_size = 256M

read_buffer = 2M

write_buffer = 2M

slave的配置資訊：

#vim /etc/my.cnf

replicate-ignore-db=mysql

replicate-ignore-db=test

replicate-ignore-db=information_schema

relay-log-index=/data/mysql/relaylog/relay.index

relay-log-info-file=/data/mysql/relaylog/relay.info

relay-log=/data/mysql/relaylog/relaylog

slow_query_log =on

slow_query_log_file = /data/mysql/slow.log

server-id=2

read-only=1

到此可以分别對兩台伺服器進行初始化：分别執行

#scripts/mysql_install_db --user=mysql--datadir=/data/mysql/data

#/etc/init.d/mysqld start

如果啟動出現錯誤在mysql-error.log中出現：

Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

解決步驟：

#cd /data/mysql

#rm -rf *

#mkdir {data,binlog,relaylog}

#chown -R mysql:mysql /data/mysql

導緻這種問題的産生是因為資料目錄不一到導緻的。

四，在主伺服器上添加複制使用者：

mysql>grant replication slave on *.* to 'rpuser'@'192.168.1.16' identified by '123456';

mysql> flush privileges;

mysql> flush tables with read lock;

mysql> show master status;這一步顯示的資訊對我沒用，因為我的主伺服器已經運作一段時間。但是資料量又不是大。

+------------------+----------+-------------------------------+------------------+

| File  | Position | Binlog_Do_DB   | Binlog_Ignore_DB |

| mysql-bin.000005 | 102020 | billing,birdtalk,imsi,meeting |   |

mysql> unlock tables;

五，在從伺服器上先測試剛建立的使用者能否登入。

#mysql -urpuser -p -h192.168.1.15 能正常登入，則繼續下一下。

mysql> slave stop;

mysql>change master to

->master_host='192.168.1.15',

->master_user='rpuser',

->master_password='123456', 

->master_log_file='mysql-bin.000005',

->master_log_pos=102020;

mysql>start slave;

mysql>show slave status\G;

mysql>show databases;過幾分鐘執行

泥瑪，四個資料庫既然沒有同步過來。速度不指定複制位置，

mysql>slave stop;

mysql>change master tomaster_host='192.168.1.15',master_user='rpuser',master_password=123456; 

mysql> show slave status \G

*************************** 1. row ***************************

 Slave_IO_State: Queueing master event to the relay log

  Master_Host: 192.168.1.15

  Master_User: rpuser

  Master_Port: 3306

 Connect_Retry: 60

 Master_Log_File: mysql-bin.000004

 Read_Master_Log_Pos: 160393581

 Relay_Log_File: relaylog.000005

 Relay_Log_Pos: 32226033

Relay_Master_Log_File: mysql-bin.000004

Slave_IO_Running: Yes 這個兩個線程正常啟動，表示從伺服器成功。

 Slave_SQL_Running: Yes

 Replicate_Do_DB: 

 Replicate_Ignore_DB: mysql,test,information_schema

 Replicate_Do_Table: 

Replicate_Ignore_Table: 

Replicate_Wild_Do_Table: 

Replicate_Wild_Ignore_Table: 

  Last_Errno: 0

  Last_Error: 

  Skip_Counter: 0

 Exec_Master_Log_Pos: 32225887

 Relay_Log_Space: 186392166

 Until_Condition: None

 Until_Log_File: 

 Until_Log_Pos: 0

 Master_SSL_Allowed: No

 Master_SSL_CA_File: 

 Master_SSL_CA_Path: 

 Master_SSL_Cert: 

 Master_SSL_Cipher: 

 Master_SSL_Key: 

Seconds_Behind_Master: 737685

Master_SSL_Verify_Server_Cert: No

 Last_IO_Errno: 0

 Last_IO_Error: 

 Last_SQL_Errno: 0

 Last_SQL_Error: 

Replicate_Ignore_Server_Ids: 

 Master_Server_Id: 1

ERROR 1201 (HY000) at line 22: Could not initialize master info structure; more error messages can be found in the MySQL error log。

如果出現這個錯誤，則需要檢查配置檔案中的日志的路徑是否正确，如是不正确會導緻這個問題的發生，

#/etc/init.d/mysqld stop

#cd /data/mysql/data

#rm -f master.info

#mysql> stop slave;

#mysql>change master to master_host='192.168.1.15',master_user='rpuser',master_password='123456';

本文轉自 deng304749970 51CTO部落格，原文連結：http://blog.51cto.com/damondeng/1062192，如需轉載請自行聯系原作者