Debian MariaDB如何进行负载均衡

Using HAProxy as a Load Balancer for MariaDB in Debian
HAProxy is a high-performance TCP/HTTP load balancer that can distribute database connections across multiple MariaDB nodes. Below are the key steps to configure it:

• Install HAProxy: Run sudo apt update && sudo apt install haproxy to install the package.
• Configure HAProxy: Edit /etc/haproxy/haproxy.cfg to define a frontend (listening on port 3306) and backend (pooling MariaDB nodes). A sample configuration:

  global
      log /dev/log local0
      log /dev/log local1 notice
      daemon
      maxconn 256
  defaults
      log global
      mode tcp
      option tcplog
      timeout connect 5000ms
      timeout client 50000ms
      timeout server 50000ms
  frontend mariadb_frontend
      bind *:3306
      default_backend mariadb_backend
  backend mariadb_backend
      balance roundrobin  # Distributes connections in round-robin order
      server mariadb1 192.168.1.101:3306 check  # Replace with your node IPs
      server mariadb2 192.168.1.102:3306 check
      server mariadb3 192.168.1.103:3306 check
  

  The check parameter enables health checks to automatically remove unhealthy nodes.
• Start HAProxy: Execute sudo systemctl start haproxy && sudo systemctl enable haproxy to activate the service. Clients can now connect to the HAProxy IP (e.g., 192.168.1.100:3306) to access the MariaDB cluster.

Using MySQL Router for Lightweight Load Balancing
MySQL Router, provided by Oracle, is a lightweight tool that routes traffic to MariaDB nodes with minimal overhead. It supports read/write splitting and failover.

• Install MySQL Router: Run sudo apt update && sudo apt install mysql-router to install.
• Configure MySQL Router: Edit /etc/mysql-router.cnf to define backend servers and routing rules. A basic configuration for read/write splitting:

  [DEFAULT]
  bind-address = 0.0.0.0
  port = 7001
  [server1]
  address = 192.168.1.101
  port = 3306
  weight = 1
  [server2]
  address = 192.168.1.102
  port = 3306
  weight = 1
  [server3]
  address = 192.168.1.103
  port = 3306
  weight = 1
  

  This routes read requests to all nodes (for scalability) and write requests to the primary node (if using a master-slave setup).
• Start MySQL Router: Execute sudo systemctl start mysql-router && sudo systemctl enable mysql-router to begin routing traffic.

Setting Up MariaDB Galera Cluster for Multi-Master Load Balancing
Galera Cluster provides synchronous multi-master replication, ensuring data consistency across all nodes. It eliminates the need for a dedicated primary node, allowing all nodes to handle read/write requests.

• Install Galera on All Nodes: Run sudo apt update && sudo apt install mariadb-server galera-4 on each node in the cluster.
• Configure Galera: Edit /etc/mysql/conf.d/galera.cnf on each node with the following (replace IPs with your node addresses):

  [mysqld]
  wsrep_on=ON
  wsrep_cluster_name='galera_cluster'
  wsrep_cluster_address=gcomm://192.168.1.101,192.168.1.102,192.168.1.103  # List all node IPs
  wsrep_node_address='192.168.1.101'  # Replace with the current node's IP
  wsrep_node_name='node1'  # Unique name for each node
  binlog_format=row
  default_storage_engine=InnoDB
  innodb_autoinc_lock_mode=2
  

• Start the Cluster: On the first node, run sudo systemctl start mariadb to bootstrap the cluster. On subsequent nodes, run sudo systemctl start mariadb to join the existing cluster.
• Verify Cluster Status: Connect to any node and execute SHOW STATUS LIKE 'wsrep_cluster_size';. The output should show the number of nodes in the cluster (e.g., 3 for three nodes).
• Load Balancing: Use HAProxy or another load balancer to distribute traffic across all Galera nodes. Since all nodes are identical, the load balancer can use a simple round-robin strategy.

Using MariaDB Proxy for Application-Aware Load Balancing
MariaDB Proxy is a specialized tool designed for database traffic. It supports query routing (e.g., sending reads to slaves and writes to the master) and failover.

• Install MariaDB Proxy: Run sudo apt update && sudo apt install mariadb-proxy to install.
• Configure MariaDB Proxy: Edit /etc/mysql-proxy.cnf to define the proxy address and backend servers. A sample configuration for read/write splitting:

  [proxy]
  daemon = 0
  log_level = 3
  log_file = /var/log/mysql-proxy.log
  admin-variables = 
  admin-connections = 100
  proxy-address = 0.0.0.0:3306
  keepalive = 1
  [backend]
  type = mysql
  host = 192.168.1.101  # Master node
  port = 3306
  user = replicator
  password = password
  [backend]
  type = mysql
  host = 192.168.1.102  # Slave node
  port = 3306
  user = replicator
  password = password
  [backend]
  type = mysql
  host = 192.168.1.103  # Slave node
  port = 3306
  user = replicator
  password = password
  

  The proxy automatically routes write queries to the master and read queries to the slaves.
• Start MariaDB Proxy: Execute sudo systemctl start mariadb-proxy && sudo systemctl enable mariadb-proxy to begin proxying traffic.