MySQL master/dual Server Load balancer

MySQL dual-host master architecture, coupled with Load Balancing Devices, can achieve high performance and high availability of mysql database load balancing, Server Load balancer equipment can be based on algorithms to database

MySQL dual-host master architecture, coupled with Load Balancing Devices, can achieve high performance and high availability of mysql database load balancing, Server Load balancer equipment can be based on algorithms to database

MySQL dual-host master architecture, coupled with Load Balancing Devices, can achieve high performance and high availability of mysql database load balancing, the server Load balancer device can divide the load of Database Operations into two MySQL Servers Based on algorithms. This reduces the workload of each server by half, effectively improving the overall processing performance, install a MySQL program on each MySQL database server. The physical files of the database are stored in the hard disk of the local machine. The data is synchronized in real time and backed up to each other through the MySQL master cluster architecture, in this way, if one of the MySQL Databases goes down, the other can temporarily take on all the loads, and the database of the down server can be instantly and completely restored based on the database of the active host, to achieve high availability.

MySQL Proxy for fast read/write splitting and load balancing

Build a MySQL Server Load balancer and high-availability environment

MySQL LVS Server Load balancer

Implement MySQL high-availability load balancing with LVS and Keepalived in Ubuntu

Configure the MySQL server for Master-master Replication

MySQL master replication + Keepalived to create a highly available MySQL Cluster

Specific implementation steps:

1. Create and authorize users

This step creates a user on each (master) server and authorizes them to access each other's databases.

On Server-1 (192.168.10.91:

Create a user server2 that allows Server-2 to access. The password is server2.

Mysql> grant replication slave on *. * to 'server2' @ '192. 168.10.92 'identified by 'server2 ';

Mysql> flush privileges;

On Server-2 (192.168.10.92:

Create a user server1 that allows Server-1 access with the password server1

Mysql> grant replication slave on *. * to 'server1' @ '192. 168.10.91 'identified by 'server1 ';

Mysql> flush privileges;

Ii. Modify the MySQL master configuration file

Modify/Add the following content in the MySQL main configuration file:

Server-1:

[Mysqld]

Server-id = 91 # The best ID is the ending number of ip addresses

Log-bin = mysql-bin # The path can be set. Note that the new path owner is mysql.

Replicate-do-db = mydb # the database to be synchronized, or the entire database by default.

Auto-increment = 2 # Total number of servers in the entire structure

Auto-increment-offset = 1

# Service mysqld restart

Server-2:

[Mysqld]

Server-id = 92

Log-bin = mysql-bin

Replicate-do-db = mydb

Auto-increment = 2

Auto-increment-offset = 2

# Service mysqld restart

Note: Only server-id and auto-increment-offset are different.

Auto-increment-offset is used to set the starting point for automatic growth in the database. the backend server sets an automatic Growth Value of 2 for both servers, so their starting points must be different, this prevents primary key conflicts during data synchronization between the two servers.

Replicate-do-db specifies the database to be synchronized. We only synchronize the mydb database between two servers.

In addition, the value of auto-increment should be set to the total number of servers in the entire structure. In this case, two servers are used, so the value is set to 2.

3. view the location of the binary logs of the two servers

# Mysql

> Show master status;

+ ----------------- + ------------ + ---------------- + -------------------- +

| File | Position | Binlog_Do_DB | Binlog_Ignore_DB |

+ ----------------- + ------------ + ---------------- + -------------------- +

| Mysql-bin.000001 | 106 |

+ ----------------- + ------------ + ---------------- + -------------------- +

1 row in set (0.00 sec)

> Unlock tables;

> \ Q

4. Advertise binary log locations to each other

On Server-1:

# Mysql

> CHANGE MASTER

> MASTER_HOST = '1970. 168.10.92 ',

> MASTER_USER = 'server1 ',

> MASTER_PASSWORD = 'server1 ',

> MASTER_LOG_FILE = 'mysql-bin.000001 ', # binfile of server2

> MASTER_LOG_POS = 106; # bin location of server2

Change masterto master_host = '192. 168.10.92 ', master_user = 'server1', master_password = 'server1', master_log_file = 'mysql-bin.000001', master_log_pos = 192;

On Server-2:

# Mysql

> CHANGE MASTER

> MASTER_HOST = '1970. 168.10.91 ',

> MASTER_USER = 'server2 ',

> MASTER_PASSWORD = 'server2 ',

> MASTER_LOG_FILE = 'mysql-bin.000001 ',

> MASTER_LOG_POS = 106;

Change masterto master_host = '192. 168.10.91 ', master_user = 'server2', master_password = 'server2', master_log_file = 'mysql-bin.000001', master_log_pos = 192;

5. Start the Replication function

Run

# Mysql

> Start slave;

Configuration is complete!

View configurations

Showslave status \ G;