利用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:>
[*] make: *** Error 1
[*] make: Leaving directory `/down/libunwind-1.0/src'
[*] make: *** Error 2
[*] make: Leaving directory `/down/libunwind-1.0/src'
[*] make: *** Error 2
[*] make: Leaving directory `/down/libunwind-1.0/src'
[*] make: *** 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: *** Error 1
[*] 为什么出现在这种错误，因此是64bit系统，至于更深度的解释，安装说明中有。下面重新编译。
[*] #make clean
[*] #./configure --enable-frame-pointers
[*] #make && make install
[*] #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的配置文件：
[*]
[*] default-character-set = utf8
[*] port=3306
[*] socket = /tmp/mysql.sock
[*]
[*] user = mysql
[*] port=3306
[*] socket = /tmp/mysql.sock
[*] 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
[*]
[*] quick
[*] max_allowed_packet = 64M
[*]
[*] no-auto-rehash
[*]
[*] key_buffer_size = 256M
[*] sort_buffer_size = 256M
[*] read_buffer = 2M
[*] write_buffer = 2M
slave的配置信息：
#vim /etc/my.cnf
[*]
[*] default-character-set = utf8
[*] port=3306
[*] socket = /tmp/mysql.sock
[*]
[*] user = mysql
[*] port=3306
[*] socket = /tmp/mysql.sock
[*] basedir = /usr/local/mysql
[*] datadir=/data/mysql/data
[*] pid-file = /data/mysql/mysql.pid
[*] log-error = /data/mysql/mysql-error.log
[*] replicate-ignore-db=mysql
[*] replicate-ignore-db=test
[*] replicate-ignore-db=information_schema
[*] 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
[*] relay-log-index=/data/mysql/relaylog/relay.index
[*] relay-log-info-file=/data/mysql/relaylog/relay.info
[*] relay-log=/data/mysql/relaylog/relaylog
[*] 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
[*] slow_query_log =on
[*] slow_query_log_file = /data/mysql/slow.log
[*] 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=2
[*] read-only=1
[*]
[*] quick
[*] max_allowed_packet = 64M
[*]
[*] no-auto-rehash
[*]
[*] key_buffer_size = 256M
[*] sort_buffer_size = 256M
[*] read_buffer = 2M
[*] write_buffer = 2M
到此可以分别对两台服务器进行初始化：分别执行
[*] #cd /usr/local/mysql
[*] #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
[*] #cd /usr/local/mysql
[*] #scripts/mysql_install_db --user=mysql--datadir=/data/mysql/data
[*] #/etc/init.d/mysqld start
导致这种问题的产生是因为数据目录不一到导致的。
四，在主服务器上添加复制用户：
[*]
mysql>grant replication slave on *.* to 'rpuser'@'192.168.1.16'>
[*] 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>start slave;
[*] mysql> show slave status \G
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the> 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:> Relay_Log_Pos: 32226033
> 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
[*] #vim /etc/my.cnf
[*] #cd /data/mysql/data
[*] #rm -f master.info
[*] #/etc/init.d/mysqld start
[*] #mysql> stop slave;
[*] #mysql>change master to master_host='192.168.1.15',master_user='rpuser',master_password='123456';
页:
[1]