一 设置haproxy输出log
1.1 调整配置文件
默认haproxy是不会输出log到文件的，这样很大程度在查询问题时会很不方便，haproxy是可以输出日志到文件的，配置文档类似于如下:
]# cat http_haproxy.conf global maxconn 100000 stats socket /var/run/haproxy.stat mode 600 level admin log 127.0.0.1 local3 debug user haproxy group haproxy chroot /usr/local/haproxy/var daemon defaults log global mode http retries 3 timeout connect 10s timeout client 20s timeout server 30s timeout check 5s frontend http-in bind :80 mode http log global option httplog option forwardfor option dontlognull option httpclose default_backend default_server listen admin_status bind :1314 mode http stats refresh 30s stats uri /haproxy-status stats realm welcome login Haproxy stats auth admin:admin stats hide-version # stats admin if TRUE backend default_server mode http balance roundrobin cookie default_server option httpclose server web1 127.0.0.1:81 check inter 1000 rise 2 fall 3 server web2 192.168.31.159:80 check inter 1000 rise 2 fall 3
可以看到，global log 为 127.0.0.1 local3 debug
1.2 设置rsyslog
/etc/rsyslog.conf 开启 imudp 和 UDPServerRun
# Provides UDP syslog reception $ModLoad imudp $UDPServerRun 514
/etc/sysconfig/rsyslog 设置SYSLOGD_OPTIONS为 -c 2 -r -m 0
# cat /etc/sysconfig/rsyslog SYSLOGD_OPTIONS="-c 2 -r -m 0"
重启rsyslog (/etec/init.d/rsyslog restart)即可
1.2 调整后端服务器输出真实ip
在haproxy配置文件中需要开启 option forwardfor 选项
1.2.1 Nginx 后端服务器设置
在http模块下，设置log_format 格式，添加proxy_add_x_forwarded_for
log_format main '$remote_addr $proxy_add_x_forwarded_for - $remote_user [$time_local] "$request" ' '$status $body_bytes_sent "$http_referer" ' '"$http_user_agent" "$http_x_forwarded_for"'; access_log logs/access.log main;
1.2.2 Apache https 后端服务器设置
设置httpd.conf log_config_module 模块如下,在LogFormat增加%{X-Forwarded-For}i选项
<IfModule log_config_module> # # The following directives define some format nicknames for use with # a CustomLog directive (see below). # LogFormat "%{X-Forwarded-For}i %h %l %u %t "%r" %>s %b "%{Referer}i" "%{User-Agent}i"" combined LogFormat "%{X-Forwarded-For}i %h %l %u %t "%r" %>s %b" common <IfModule logio_module> # You need to enable mod_logio.c to use %I and %O LogFormat "%{X-Forwarded-For}i %h %l %u %t "%r" %>s %b "%{Referer}i" "%{User-Agent}i" %I %O" combinedio </IfModule> # # The location and format of the access logfile (Common Logfile Format). # If you do not define any access logfiles within a <VirtualHost> # container, they will be logged here. Contrariwise, if you *do* # define per-<VirtualHost> access logfiles, transactions will be # logged therein and *not* in this file. # CustomLog "logs/access_log" common # # If you prefer a logfile with access, agent, and referer information # (Combined Logfile Format) you can use the following directive. # #CustomLog "logs/access_log" combined </IfModule>
1.2.3 Apache Tomcat 后端服务器设置
在server.xml 中 在Host段中，在pattern处添加%{X-Forwarded-For}i
<Host name="localhost" appBase="webapps" unpackWARs="true" autoDeploy="true"> <!-- SingleSignOn valve, share authentication between web applications Documentation at: /docs/config/valve.html --> <!-- <Valve className="org.apache.catalina.authenticator.SingleSignOn" /> --> <!-- Access log processes all example. Documentation at: /docs/config/valve.html Note: The pattern used is equivalent to using pattern="common" --> <Valve className="org.apache.catalina.valves.AccessLogValve" directory="logs" prefix="localhost_access_log" suffix=".txt" pattern="%{X-Forwarded-For}i %h %l %u %t "%r" %s %b" /> </Host>
1.3 haproxy acl规则
acl 规则常用于frontend段中,语法如下:
acl 定义的acl名称 acl方法 -i [匹配的值]
注意:此acl规则，是用在第7层协议的
acl方法常用的有:
hdr_reg(host) : 检查客户端的域名
hdr_dom(host) : 检查客户端的域名
hdr_beg(host) : 检查客户端以什么开头
path_end : 客户端的url以什么结尾
举例:
frontend http-in acl into_tomcat path_end jsp css png use_backend tomcat_server if into_tomcat default_backend default_server backend tomcat_server mode http balance roundrobin cookie tomcat_server_cookie option httpclose server web1 192.168.31.159:8080 check inter 1000 rise 2 fall 3
定义into_tomcat的acl规则是否是以 jsp css png 结尾的,为into_tomcat规则定义后端为tomcat_server
1.4 Haproxy MySQL案例
哈哈哈，是不是感觉好突兀，前面几乎全部在讲haproxy 7层协议的配置，突然闪了一下，来一个4层协议的
1.4.1 MySQL 配置双主模式
mysql_1_mysql_cnf:
# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=251 log-bin=/var/lib/mysql/log-bin auto_increment_offset=1 auto_increment_increment=2 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #
变量查看:
mysql> show variables where variable_name like '%auto%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | auto_increment_increment | 2 | | auto_increment_offset | 1 | | autocommit | ON | | automatic_sp_privileges | ON | | innodb_autoextend_increment | 64 | | innodb_autoinc_lock_mode | 1 | | innodb_stats_auto_recalc | ON | | sql_auto_is_null | OFF | +-----------------------------+-------+ 8 rows in set (0.01 sec) mysql> show variables where variable_name like '%log_bin%'; +---------------------------------+------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/log-bin | | log_bin_index | /var/lib/mysql/log-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+------------------------------+ 6 rows in set (0.00 sec) mysql> exit
mysql_2_mysql_cnf:
# cat /etc/my.cnf [mysqld] datadir=/var/lib/mysql socket=/var/lib/mysql/mysql.sock user=mysql # Disabling symbolic-links is recommended to prevent assorted security risks symbolic-links=0 server-id=159 log-bin=/var/lib/mysql/log-bin auto_increment_offset=2 auto_increment_increment=2 [mysqld_safe] log-error=/var/log/mysqld.log pid-file=/var/run/mysqld/mysqld.pid #
变量查看:
mysql> show variables where variable_name like '%auto%'; +-----------------------------+-------+ | Variable_name | Value | +-----------------------------+-------+ | auto_increment_increment | 2 | | auto_increment_offset | 2 | | autocommit | ON | | automatic_sp_privileges | ON | | innodb_autoextend_increment | 64 | | innodb_autoinc_lock_mode | 1 | | innodb_stats_auto_recalc | ON | | sql_auto_is_null | OFF | +-----------------------------+-------+ 8 rows in set (0.01 sec) mysql> show variables where variable_name like '%log_bin%'; +---------------------------------+------------------------------+ | Variable_name | Value | +---------------------------------+------------------------------+ | log_bin | ON | | log_bin_basename | /var/lib/mysql/log-bin | | log_bin_index | /var/lib/mysql/log-bin.index | | log_bin_trust_function_creators | OFF | | log_bin_use_v1_row_events | OFF | | sql_log_bin | ON | +---------------------------------+------------------------------+ 6 rows in set (0.01 sec) mysql> exit
auto_increment_increment:表示自增长每次自增的ID
auto_increment_offset:表示自增从哪个字段开始
log_bin:开启log_bin记录日志
mysql_1 和 mysql_2 建立replication slave用户:
mysql_1:
mysql> grant replication slave on *.* to 'slave_copy'@'192.168.31.251' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.03 sec)
mysql_2:
mysql> grant replication slave on *.* to 'slave_copy'@'192.168.31.159' identified by '123456'; Query OK, 0 rows affected, 1 warning (0.00 sec)
获取各个mysql的File和Position信息
mysql_1
mysql> show master status; +----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+------------------+-------------------+ | log-bin.000001 | 618 | | | | +----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
mysql_2
mysql> show master status; +----------------+----------+--------------+------------------+-------------------+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +----------------+----------+--------------+------------------+-------------------+ | log-bin.000001 | 1024 | | | | +----------------+----------+--------------+------------------+-------------------+ 1 row in set (0.00 sec) mysql>
设置mysql互为主主
操作mysql_1
mysql> change master to master_host='192.168.31.159', master_port=3306, master_user='slave_copy', master_password='123456', master_log_file='log-bin.000001', master_log_pos=1024; Query OK, 0 rows affected, 2 warnings (0.05 sec)
操作mysql_2
mysql> change master to master_host='192.168.31.251', master_port=3306, master_user='slave_copy', master_password='123456', master_log_file='log-bin.000001', master_log_pos=618; Query OK, 0 rows affected, 2 warnings (0.04 sec)
设置完毕后，两台均开启slave
设置mysql slave状态
mysql_1 slave status:
mysql_1 slave status: mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.31.159 Master_User: slave_copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log-bin.000001 Read_Master_Log_Pos: 1024 Relay_Log_File: web01-relay-bin.000002 Relay_Log_Pos: 318 Relay_Master_Log_File: log-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 1024 Relay_Log_Space: 525 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: 0 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: 159 Master_UUID: 36faf4db-204e-11e9-bfcc-080027ce3153 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec) mysql>
mysql_2_slave_status
mysql> show slave statusG *************************** 1. row *************************** Slave_IO_State: Waiting for master to send event Master_Host: 192.168.31.251 Master_User: slave_copy Master_Port: 3306 Connect_Retry: 60 Master_Log_File: log-bin.000001 Read_Master_Log_Pos: 618 Relay_Log_File: redis01-relay-bin.000002 Relay_Log_Pos: 318 Relay_Master_Log_File: log-bin.000001 Slave_IO_Running: Yes Slave_SQL_Running: Yes Replicate_Do_DB: Replicate_Ignore_DB: 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: 618 Relay_Log_Space: 527 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: 0 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: 251 Master_UUID: 202f1120-204c-11e9-be95-080027d979e8 Master_Info_File: /var/lib/mysql/master.info SQL_Delay: 0 SQL_Remaining_Delay: NULL Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates Master_Retry_Count: 86400 Master_Bind: Last_IO_Error_Timestamp: Last_SQL_Error_Timestamp: Master_SSL_Crl: Master_SSL_Crlpath: Retrieved_Gtid_Set: Executed_Gtid_Set: Auto_Position: 0 Replicate_Rewrite_DB: Channel_Name: Master_TLS_Version: 1 row in set (0.00 sec)
配置haproxy:
# cat mysql_haproxy.conf global maxconn 1000 stats socket /var/run/haproxy.stat mode 600 level admin log 127.0.0.1 local3 debug user haproxy group haproxy chroot /usr/local/haproxy/var daemon defaults log global mode http retries 3 timeout connect 20s timeout client 600s timeout server 600s timeout check 5s frontend mysql_in bind :3307 mode tcp log global default_backend default_server listen admin_status bind :1314 mode http stats refresh 30s stats uri /haproxy-status stats realm welcome login Haproxy stats auth admin:admin stats hide-version stats admin if TRUE backend default_server mode tcp balance roundrobin option abortonclose server mysql_1 127.0.0.1:3306 check inter 1000 rise 2 fall 3 server mysql_2 192.168.31.159:3306 check inter 1000 rise 2 fall 3 #
haproxy mysql 配置就如上了，最后，如果觉得这样还不行的话，可以考虑加一个keepalived，说实在话，像这mysql代理，我估计很少有公司会用，mysql代理工具有很多，很出名的，比如，proxysql , mycat , kingshard 等， 不过haproxy在做http真的很厉害