MySQL main host dual-machine load balancing

MySQL dual master architecture, which is supplemented by load balancing devices, can achieve the MySQL database load balancing high performance and high availability, load balancing devices can be based on the algorithm to the load of the database operations on the average of two MySQL servers, so that for each server to halve the workload, Effectively improve the overall processing performance, install a MySQL program on each MySQL database server, the database physical files are on the local hard disk, through the MySQL Master cluster architecture, the real-time synchronization of data, and each other for backup, so if one of the MySQL database downtime, The other one can assume the full load temporarily, and can restore the database of the outage server immediately and completely according to the database of the surviving host, in order to achieve the purpose of high availability.

Specific implementation steps:

first, create and authorize users

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

in Server-1 (192.168.10.91) on:

Create an Allow Server-2 to access the user Server2, the password is:server2

mysql> grant replication Slave on * * to ' server2 ' @ ' 192.168.10.92 ' identified by ' server2 ';

mysql> flush Privileges;

in Server-2(192.168.10.92) on:

Create an Allow Server-1 to access the user Server1, the password is:Server1

mysql> grant replication Slave on * * to ' server1 ' @ ' 192.168.10.91 ' identified by ' Server1 ';

mysql> flush Privileges;

Second, modifyMySQLMaster configuration file

in the in the main configuration file of MySQL, modify/ Add the following content:

Server-1 on:

[Mysqld]

Server-id = #ID best for IP mantissa

Log-bin = mysql-bin # path can be set, note the new path belongs to the main group of MySQL

replicate-do-db = mydb # db to synchronize , or not write default to full library

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

Auto-increment-offset = 1

# Service Mysqld Restart

Server-2 on :

[Mysqld]

Server-id = 92

Log-bin = Mysql-bin

Replicate-do-db = MyDB

Auto-increment-increment = 2

Auto-increment-offset = 2

# Service Mysqld Restart

Note: Two are only Server-id different and auto-increment-offset different

Auto-increment-offset is used to set the starting point of the automatic growth of the database, the two can be set to the server automatically increase the value of 2, so their starting point must be different, so as to avoid the two server data synchronization when the primary key conflict

Replicate-do-db Specifies the synchronized database, we only synchronize the MyDB database between the two servers

another:the value of auto-increment-increment should be set to the total number of servers in the whole structure, this case uses two servers, so the value is set to 2

third, view the location of binary logs for 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

Iv. Circular to each other binary log location

in Server-1 on:

# MySQL

> Change MASTER to

> master_host= ' 192.168.10.92 ',

> master_user= ' Server1 ',

> master_password= ' Server1 ',

> master_log_file= ' mysql-bin.000001 ', #server2 bin file

> master_log_pos=106; Bin Location of the #server2

Change Masterto master_host= ' 192.168.10.92 ', master_user= ' server1 ', master_password= ' server1 ', master_log_file= ' Mysql-bin.000001 ', master_log_pos=106;

in Server-2 on:

# MySQL

> Change MASTER to

> master_host= ' 192.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=106;

v. Initiate replication (Replication) function

Execute separately on both hosts

# MySQL

> START SLAVE;

Configure this to complete!

View Configuration

Showslave Status\g;

This article is from the "operations and Maintenance Technology accumulation" blog, please be sure to keep this source http://pkersun.blog.51cto.com/4481043/1581780

MySQL main host dual-machine load balancing