利用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。

1. 安装libunwind库。

2. # tar xf libunwind-1.0.tar.gz

3. # tar xf libunwind-1.0

4. #CFLAGS=-fPIC ./configure添加编译参数

5. # make CFLAGS=-fPIC

6. # make CFLAGS=-fPIC install

7. 这样执行会出现如下错误：

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

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

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

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

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

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

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

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

16. 产生这种问题的原因：autotools兼容性的问题，运行命令autoreconf -i -f ，在重新编译安装下。

17. #autoreconf -i -f

18. #make clean

19. #CFLAGS=-fPIC ./configure

20. #make CFLAGS=-fPIC

21. #make CFLAGS=-fPIC install

22. 安装gpperftools：

23. #tar xf gperftools-2.0.tar.gz

24. # cd gperftools-2.0

25. #./configure

26. #make && make install

27. 这样安装会会报如下错误：

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

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

30. 为什么出现在这种错误，因此是64bit系统，至于更深度的解释，安装说明中有。下面重新编译。

31. #make clean

32. #./configure --enable-frame-pointers

33. #make && make install

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

35. #ldconfig

三，安装cmake与mysql

1. 安装cmake

2. #tar xf cmake-2.8.8.tar.gz

3. #cd cmake-2.8.8

4. # ./bootstrap

5. # gmake && gmake install

6. 安装mysql

7. #useradd -r mysql -s /sbin/nologin

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

9. #chown mysql:mysql -R /data/mysql

10. # tar xf mysql-5.5.27.tar.gz

11. #cd mysql-5.5.27

12. #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

13. #会编译不过去，提示需要安装ncurses-devel,yum -y install ncurses-devel

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

15. # make && make install

16. #cd /usr/local/mysql

17. #chown -R mysql:mysql *

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

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

20. #chkconfig mysqld on

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

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

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

24. # echo '/usr/local/mysql/lib' > /etc/ld.so.conf.d/mysql.conf 加载库文件

25. # ldconfig -v |grep mysql

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

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

28. 环境已经基本Ok

29. # cd /usr/local/mysql

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

将主服务器克隆一份，并修改IP与MAC地址，保证两台机器能够互相ping通。

三，下面是master的配置文件：

1. [client]

2. default-character-set = utf8

3. port=3306

4. socket = /tmp/mysql.sock

5. [mysqld]

6. user = mysql

7. port=3306

8. socket = /tmp/mysql.sock

9. basedir = /usr/local/mysql

10. datadir=/data/mysql/data

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

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

13. log_slave_update =1

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

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

16. binlog_format = mixed

17. binlog_cache_size = 4M

18. max_binlog_cache_size = 8M

19. max_binlog_size = 1G

20. expire_logs_days = 30

21. key_buffer_size = 384M

22. sort_buffer_size = 2M

23. read_buffer_size = 2M

24. read_rnd_buffer_size =16M

25. join_buffer_size =2M

26. thread_cache_size = 300

27. query_cache_limit = 2M

28. query_cache_min_res_unit =2K

29. thread_concurrency = 8

30. table_cache =614

31. table_open_cache = 512

32. open_files_limit = 10240

33. back_log = 600

34. max_connections = 5000

35. max_connect_errors = 6000

36. external-locking = FALSE

37. max_allowed_packet =16M

38. default_storage_engine = MyISAM

39. #default_storage_engine = InnoDB

40. thread_stack =192K

41. transaction_isolation = REPEATABLE-READ

42. tmp_table_size = 256M

43. max_heap_table_size =512M

44. bulk_insert_buffer_size = 64M

45. myisam_sort_buffer_size = 64M

46. myisam_max_sort_file_size = 10G

47. myisam_repair_threads = 1

48. myisam_recover

49. long_query_time = 2

50. skip-name-resolve

51. skip-external_locking

52. #skip-innodb

53. innodb_additional_mem_pool_size = 16M

54. innodb_buffer_pool_size = 512M

55. innodb_data_file_path = ibdata1:256M:autoextend

56. innodb_file_io_threads = 4

57. innodb_thread_concurrency = 8

58. innodb_flush_log_at_trx_commit =2

59. innodb_log_file_size =128M

60. innodb_log_files_in_group = 3

61. innodb_max_dirty_pages_pct = 90

62. innodb_lock_wait_timeout =120

63. innodb_file_per_table = 0

64. interactive_timeout=120

65. wait_timeout=120

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

67. server-id=1

68. [mysqldump]

69. quick

70. max_allowed_packet = 64M

71. [mysql]

72. no-auto-rehash

73. [myisamchk]

74. key_buffer_size = 256M

75. sort_buffer_size = 256M

76. read_buffer = 2M

77. write_buffer = 2M

slave的配置信息：

#vim /etc/my.cnf

1. [client]

2. default-character-set = utf8

3. port=3306

4. socket = /tmp/mysql.sock

5. [mysqld]

6. user = mysql

7. port=3306

8. socket = /tmp/mysql.sock

9. basedir = /usr/local/mysql

10. datadir=/data/mysql/data

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

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

13. replicate-ignore-db=mysql

14. replicate-ignore-db=test

15. replicate-ignore-db=information_schema

16. log_slave_update =1

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

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

19. binlog_format = mixed

20. binlog_cache_size = 4M

21. max_binlog_cache_size = 8M

22. max_binlog_size = 1G

23. expire_logs_days = 30

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

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

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

27. key_buffer_size = 384M

28. sort_buffer_size = 2M

29. read_buffer_size = 2M

30. read_rnd_buffer_size =16M

31. join_buffer_size =2M

32. thread_cache_size = 300

33. query_cache_limit = 2M

34. query_cache_min_res_unit =2K

35. thread_concurrency = 8

36. table_cache =614

37. table_open_cache = 512

38. open_files_limit = 10240

39. back_log = 600

40. max_connections = 5000

41. max_connect_errors = 6000

42. external-locking = FALSE

43. max_allowed_packet =16M

44. default_storage_engine = MyISAM

45. #default_storage_engine = InnoDB

46. thread_stack =192K

47. transaction_isolation = REPEATABLE-READ

48. tmp_table_size = 256M

49. max_heap_table_size =512M

50. bulk_insert_buffer_size = 64M

51. myisam_sort_buffer_size = 64M

52. myisam_max_sort_file_size = 10G

53. myisam_repair_threads = 1

54. myisam_recover

55. long_query_time = 2

56. slow_query_log =on

57. slow_query_log_file = /data/mysql/slow.log

58. skip-name-resolve

59. skip-external_locking

60. #skip-innodb

61. innodb_additional_mem_pool_size = 16M

62. innodb_buffer_pool_size = 512M

63. innodb_data_file_path = ibdata1:256M:autoextend

64. innodb_file_io_threads = 4

65. innodb_thread_concurrency = 8

66. innodb_flush_log_at_trx_commit =2

67. innodb_log_file_size =128M

68. innodb_log_files_in_group = 3

69. innodb_max_dirty_pages_pct = 90

70. innodb_lock_wait_timeout =120

71. innodb_file_per_table = 0

72. interactive_timeout=120

73. wait_timeout=120

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

75. server-id=2

76. read-only=1

77. [mysqldump]

78. quick

79. max_allowed_packet = 64M

80. [mysql]

81. no-auto-rehash

82. [myisamchk]

83. key_buffer_size = 256M

84. sort_buffer_size = 256M

85. read_buffer = 2M

86. write_buffer = 2M

到此可以分别对两台服务器进行初始化：分别执行

1. #cd /usr/local/mysql

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

3. #/etc/init.d/mysqld start

如果启动出现错误在mysql-error.log中出现：
Fatal error: Can't open and lock privilege tables: Table 'mysql.host' doesn't exist

1. 解决步骤：

2. #cd /data/mysql

3. #rm -rf *

4. #mkdir {data,binlog,relaylog}

5. #chown -R mysql:mysql /data/mysql

6. #cd /usr/local/mysql

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

8. #/etc/init.d/mysqld start

导致这种问题的产生是因为数据目录不一到导致的。

四，在主服务器上添加复制用户：

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

2. mysql> flush privileges;

3. mysql> flush tables with read lock;

4. mysql> show master status;这一步显示的信息对我没用，因为我的主服务器已经运行一段时间。但是数据量又不是大。
   +------------------+----------+-------------------------------+------------------+
   | File  | Position | Binlog_Do_DB   | Binlog_Ignore_DB |
   +------------------+----------+-------------------------------+------------------+
   | mysql-bin.000005 | 102020 | billing,birdtalk,imsi,meeting |   |

5. mysql> unlock tables;

五，在从服务器上先测试刚创建的用户能否登录。

#mysql -urpuser -p -h192.168.1.15 能正常登录，则继续下一下。

1. mysql> slave stop;

2. mysql>change master to

3. ->master_host='192.168.1.15',

4. ->master_user='rpuser',

5. ->master_password='123456', 
   

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

7. ->master_log_pos=102020;

8. mysql>start slave;

9. mysql>show slave status\G;

10. mysql>show databases;过几分钟执行

11. 泥玛，四个数据库既然没有同步过来。速度不指定复制位置，

12. mysql>slave stop;

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

14. mysql>start slave;

15. 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

16. 
    

ERROR 1201 (HY000) at line 22: Could not initialize master info structure; more error messages can be found in the MySQL error log。
如果出现这个错误，则需要检查配置文件中的日志的路径是否正确，如是不正确会导致这个问题的发生，

1. #/etc/init.d/mysqld stop

2. #vim /etc/my.cnf

3. #cd /data/mysql/data

4. #rm -f master.info

5. #/etc/init.d/mysqld start

6. #mysql> stop slave;

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