Load balancing of MARIADB using Haproxy

Server environment:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/26/8E/wKioL1NrXYri1iztAAEdbd2C-Vc420.jpg "title=" 17.jpg "alt=" Wkiol1nrxyri1iztaaedbd2c-vc420.jpg "/>

Installation

Vim/etc/apt/sources.list.d/mariadb.list     # Add the following two lines of Deb Http://mirror.yongbok.net/mariadb/repo/5.5/ubuntu precise Maindeb-src http://mirror.yongbok.net/mariadb/repo/5.5/ubuntu precise Mainapt-key adv--recv-keys--keyserver keyserver.ubuntu.com 0xcbcb082a1bb943dbapt-get updateapt-get Install mariadb-server-5.5

/etc/mysql/my.cnf

Multi-instance configuration file: 3306,3307,3308

[MYSQLD_MULTI]MYSQLD =/usr/bin/mysqld_safemysqladmin =/usr/bin/mysqladminuser = root # Specifies to start, stop M User log for Ysql instance =/var/log/mysql/mysqld_multi.log [Client]port = 3306socket =/var/run/mysql/mysql.sockdefau Lt-character-set = Utf8[mysqld3306]port = 3306socket =/var/run/mysql/mysql.sockpid-file =/var/run/mysql /mysql.piddatadir =/var/lib/mysql/3306skip-external-lockingkey_buffer_size = 16mmax_allowed_packet = 1Mtable_open_ cache = 64sort_buffer_size = 512knet_buffer_length = 8kread_buffer_size = 256kread_rnd_buffer_size = 512Kmyisam_sort_ Buffer_size = 8mdefault-storage-engine = Myisamcharacter-set-server = Utf8skip-name-resolvecollation-server = u Tf8_general_cilog-error =/var/log/mysql/mysqld-error.loglog-slow-admin-statementslong-query-time = 3slow-query-logslow-query-log-file =/var/log/mysql/mysqld-slow.loginnodb_buffer_pool_size=1ginnodb_additional_ Mem_pool_size=20mmax_connections =1000tmpdir =/tmp/server-id = 2log-slave-updateslog-bin = Mysql-binbinlog_format = Mixedrelay_log = mysql-relay-binreplicate-do-db = wordpressreplicate-ignore-db = Mysqlreplicate-ignore-db = Informati on_schemareplicate-ignore-db = Performance_schemareplicate-ignore-db = Test[mysqldump]quickmax_allowed_packe t = 16m[mysqld3307]port = 3307socket =/var/run/mysql/mysql-3307.sockpid-file =/VAR/RUN/MYSQL/MYSQL-3307.P Iddatadir =/var/lib/mysql/3307skip-external-lockingkey_buffer_size = 16mmax_allowed_packet = 1Mtable_open_cache = 64s Ort_buffer_size = 512knet_buffer_length = 8kread_buffer_size = 256kread_rnd_buffer_size = 512Kmyisam_sort_buffer_size = 8mdefault-storage-engine = Myisamcharacter-set-server = Utf8skip-name-resolvecollation-server = Utf8_general_ci Log-error =/var/log/mysql/mysqld-error-3307.loglog-slow-admin-statementslong-query-time = 3slow-que Ry-logslow-query-log-file    =/var/log/mysql/mysqld-slow-3307.loginnodb_buffer_pool_size=1ginnodb_additional_mem_pool_size=20mmax_ Connections = 1000tmpdir =/tmp/server-id = 2log-slave-updateslog-bin = Mysql-binbinlog_format = Mixe        Drelay_log = mysql-relay-binreplicate-do-db = Futurestarreplicate-do-db = verywxreplicate-ignore-db = Mysqlreplicate-ignore-db = Information_schemareplicate-ignore-db = Performance_schemareplicate-ignore -db = Test[mysqldump]quickmax_allowed_packet = 16m[mysqld3308]port = 3308socket =/var/run/mysql/mysql-  3308.sockpid-file =/var/run/mysql/mysql-3308.piddatadir =/var/lib/mysql/3308skip-external-lockingkey_buffer_size = 16mmax_allowed_packet = 1mtable_open_cache = 64sort_buffer_size = 512knet_buffer_length = 8Kread_buffer_size = 256Kread _rnd_buffer_size = 512kmyisam_sort_buffer_size = 8mdefault-storage-engine = Myisamcharacter-set-server = Utf8skip-name -resolvecollation-server = Utf8_geNeral_cilog-error =/var/log/mysql/mysqld-error-3308.loglog-slow-admin-statementslong-query-time = 3 Slow-query-logslow-query-log-file =/var/log/mysql/mysqld-slow-3308.loginnodb_buffer_pool_size=1ginnodb_  Additional_mem_pool_size=20mmax_connections = 1000tmpdir =/tmp/server-id = 2log-slave-updateslog-bin =        Mysql-binbinlog_format = Mixedrelay_log = Mysql-relay-binreplicate-do-db = futurestarreplicate-do-db = Verywxreplicate-ignore-db = mysqlreplicate-ignore-db = INFORMATION_SCHEMAREPLICATE-IGNORE-DB = PE rformance_schemareplicate-ignore-db = Test[mysqldump]quickmax_allowed_packet = 16m[mysql]socket =/var/run/mysql/m Ysql.sockauto-rehashdefault-character-set = Utf8[myisamchk]key_buffer_size = 20msort_buffer_size = 20Mread_buffer = 2mwrite_buffer = 2m[mysqlhotcopy]interactive-timeout

Initialize the database file:

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

Start the database:

Mysqld_multi start 3306mysqld_multi start 3307mysqld_multi start 3308

To view the startup situation:

NETSTAT-TUNLP | Grep-e ' (3306|3307|3308) '

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/26/8E/wKioL1NrV5ig87b0AAB_xC-JOUY727.jpg "title=" 12.jpg "alt=" Wkiol1nrv5ig87b0aab_xc-jouy727.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/26/8E/wKiom1NrWimwvRhwAANSvXGqZGk724.jpg "style=" float: none; "title=" 13.jpg "alt=" Wkiom1nrwimwvrhwaansvxgqzgk724.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/26/8E/wKioL1NrWf7gC7IxAAFEWAJact0591.jpg "style=" float: none; "title=" 15.jpg "alt=" Wkiol1nrwf7gc7ixaafewajact0591.jpg "/>

Installing Haporxy

Apt-get Install Haproxy

Then vim Etc/default/haproxy

Modify Enabled=0-enabled=1 save.

To modify the Haproxy configuration file:

Vim/etc/haproxy/haproxy.cfg

# This config needs haproxy-1.1.28 or Haproxy-1.2.1global log 127.0.0.1 local0 log 127.0.0.1 local1 notice #l og loghost local0 info maxconn 4096 #chroot/usr/share/haproxy user haproxy Group Haproxy daemon #deb UG #quietdefaults Log Global mode HTTP #option httplog option dontlognull retries 3 option Redisp    Atch maxconn contimeout clitimeout 50000 srvtimeout 50000listen 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 chec                                                                                                         k 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/40 8.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

Execute command:

/etc/init.d/haproxy start

Test results:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M02/26/8E/wKioL1NrXIbTgo1GAAJI5Gv0EDI394.jpg "style=" float: none; "title=" 12.jpg "alt=" Wkiol1nrxibtgo1gaaji5gv0edi394.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M01/26/8E/wKiom1NrXLGQzJiDAAHAOUvDXvs301.jpg "style=" float: none; "title=" 13.jpg "alt=" Wkiom1nrxlgqzjidaahaouvdxvs301.jpg "/>

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/26/8E/wKioL1NrXIaDhmMQAAF6jyMU-AQ809.jpg "style=" float: none; "title=" 15.jpg "alt=" Wkiol1nrxiadhmmqaaf6jymu-aq809.jpg "/>

To view the Haproxy monitoring page:

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/26/8E/wKiom1NrXQuAe5xBAAcFX6jljjw614.jpg "title=" 16.jpg "alt=" Wkiom1nrxquae5xbaacfx6jljjw614.jpg "/>

The configuration was successful.

This article is from the "Home Birds Paradise" blog, please be sure to keep this source http://birdinroom.blog.51cto.com/7740375/1408525