使用Haproxy对MariaDB做负载均衡

服务器环境:

安装

1 2 3 4 5 6

vim  /etc/apt/sources .list.d /mariadb .list      # 添加以下两句 deb http: //mirror .yongbok.net /mariadb/repo/5 .5 /ubuntu  precise main deb-src http: //mirror .yongbok.net /mariadb/repo/5 .5 /ubuntu  precise main apt-key adv --recv-keys --keyserver keyserver.ubuntu.com 0xcbcb082a1bb943db apt-get update apt-get  install  mariadb-server-5.5

/etc/mysql/my.cnf 

多实例配置文件:3306,3307,3308

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142

[mysqld_multi] mysqld      =  /usr/bin/mysqld_safe mysqladmin  =  /usr/bin/mysqladmin user        = root            # 指定用于启动、停止mysql实例的用户 log     =  /var/log/mysql/mysqld_multi .log  [client] port        = 3306 socket      =  /var/run/mysql/mysql .sock default-character- set    = utf8 [mysqld3306] port        = 3306 socket      =  /var/run/mysql/mysql .sock pid- file     =  /var/run/mysql/mysql .pid datadir     =  /var/lib/mysql/3306 skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M default-storage-engine  = MyISAM character- set -server    = utf8 skip-name-resolve collation-server        = utf8_general_ci log-error               =  /var/log/mysql/mysqld-error .log log-slow-admin-statements long-query- time          = 3 slow-query-log slow-query-log- file      =  /var/log/mysql/mysqld-slow .log innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=20M max_connections     = 1000 tmpdir      =  /tmp/ server- id    = 2 log-slave-updates log-bin         = mysql-bin binlog_format   = mixed relay_log       = mysql-relay-bin replicate- do -db        = wordpress replicate-ignore-db        = mysql replicate-ignore-db        = information_schema replicate-ignore-db        = performance_schema replicate-ignore-db        =  test [mysqldump] quick max_allowed_packet = 16M [mysqld3307] port        = 3307 socket      =  /var/run/mysql/mysql-3307 .sock pid- file     =  /var/run/mysql/mysql-3307 .pid datadir     =  /var/lib/mysql/3307 skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M default-storage-engine  = MyISAM character- set -server    = utf8 skip-name-resolve collation-server        = utf8_general_ci log-error               =  /var/log/mysql/mysqld-error-3307 .log log-slow-admin-statements long-query- time          = 3 slow-query-log slow-query-log- file      =  /var/log/mysql/mysqld-slow-3307 .log innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=20M max_connections     = 1000 tmpdir      =  /tmp/ server- id    = 2 log-slave-updates log-bin         = mysql-bin binlog_format   = mixed relay_log       = mysql-relay-bin replicate- do -db        = futurestar replicate- do -db        = verywx replicate-ignore-db        = mysql replicate-ignore-db        = information_schema replicate-ignore-db        = performance_schema replicate-ignore-db        =  test [mysqldump] quick max_allowed_packet = 16M [mysqld3308] port        = 3308 socket      =  /var/run/mysql/mysql-3308 .sock pid- file     =  /var/run/mysql/mysql-3308 .pid datadir     =  /var/lib/mysql/3308 skip-external-locking key_buffer_size = 16M max_allowed_packet = 1M table_open_cache = 64 sort_buffer_size = 512K net_buffer_length = 8K read_buffer_size = 256K read_rnd_buffer_size = 512K myisam_sort_buffer_size = 8M default-storage-engine  = MyISAM character- set -server    = utf8 skip-name-resolve collation-server        = utf8_general_ci log-error               =  /var/log/mysql/mysqld-error-3308 .log log-slow-admin-statements long-query- time          = 3 slow-query-log slow-query-log- file      =  /var/log/mysql/mysqld-slow-3308 .log innodb_buffer_pool_size=1G innodb_additional_mem_pool_size=20M max_connections     = 1000 tmpdir      =  /tmp/ server- id    = 2 log-slave-updates log-bin         = mysql-bin binlog_format   = mixed relay_log       = mysql-relay-bin replicate- do -db        = futurestar replicate- do -db        = verywx replicate-ignore-db        = mysql replicate-ignore-db        = information_schema replicate-ignore-db        = performance_schema replicate-ignore-db        =  test [mysqldump] quick max_allowed_packet = 16M [mysql] socket  =  /var/run/mysql/mysql .sock auto-rehash default-character- set    = utf8 [myisamchk] key_buffer_size = 20M sort_buffer_size = 20M read_buffer = 2M write_buffer = 2M [mysqlhotcopy] interactive-timeout

初始化数据库文件:

1 2 3

mysql_install_db --datadir= /var/lib/mysql/3306  --user=mysql mysql_install_db --datadir= /var/lib/mysql/3307  --user=mysql mysql_install_db --datadir= /var/lib/mysql/3308  --user=mysql

启动数据库:

1 2 3

mysqld_multi start 3306 mysqld_multi start 3307 mysqld_multi start 3308

查看启动情况:

1	netstat  -tunlp |  grep  -E  '(3306|3307|3308)'

安装Haporxy

1	apt-get  install  haproxy

然后vim etc/default/haproxy

修改 ENABLED=0 -> ENABLED=1 保存。

修改haproxy配置文件:

vim /etc/haproxy/haproxy.cfg

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46

# this config needs haproxy-1.1.28 or haproxy-1.2.1 global      log 127.0.0.1   local0      log 127.0.0.1   local1 notice      #log loghost    local0 info      maxconn 4096      #chroot /usr/share/haproxy      user haproxy      group haproxy      daemon      #debug      #quiet defaults      log global      mode    http      #option httplog      option  dontlognull      retries 3      option redispatch      maxconn 2000      contimeout  5000      clitimeout  50000      srvtimeout  50000 listen  mysql 0.0.0.0:6666      mode tcp      option mysql-check user root      balance roundrobin      server  mysql_1 127.0.0.1:3306 weight 1 check inter 1s rise 2 fall 5      server  mysql_2 127.0.0.1:3307 weight 1 check inter 1s rise 2 fall 5      server  mysql_3 127.0.0.1:3308 weight 1 check inter 1s rise 2 fall 5                                                                                                            listen stats      mode http      bind 0.0.0.0:8888      stats  enable      stats uri  /dbs      stats realm Global\ statistics      stats auth admin:admin                                                                                                                 errorfile   400  /etc/haproxy/errors/400 .http      errorfile   403  /etc/haproxy/errors/403 .http      errorfile   408  /etc/haproxy/errors/408 .http      errorfile   500  /etc/haproxy/errors/500 .http      errorfile   502  /etc/haproxy/errors/502 .http      errorfile   503  /etc/haproxy/errors/503 .http      errorfile   504  /etc/haproxy/errors/504 .http

执行命令:

1	/etc/init .d /haproxy  start

测试结果:

查看haproxy监控页面:

配置成功。

本文转自birdinroom 51CTO博客，原文链接：http://blog.51cto.com/birdinroom/1408525，如需转载请自行联系原作者