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;