使用Haproxy对MariaDB做负载均衡

服务器环境:安装vim/etc/apt/sources.list.d/mariadb.list#添加以下两句debhttp://mirror.yongbok.net/mariadb/repo/5.5/ubuntuprecisemaindeb-srchttp://mirror

服务器环境:

安装

vim /etc/apt/sources.list.d/mariadb.list # 添加以下两句 deb precise main deb-src 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

[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

初始化数据库文件:

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

启动数据库:

mysqld_multi start 3306 mysqld_multi start 3307 mysqld_multi start 3308

查看启动情况:

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

安装Haporxy

apt-get install haproxy

然后vim etc/default/haproxy

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

修改haproxy配置文件: