The master-slave replication structure of MySQL is different from the master-slave replication structure. In the master-master replication structure
If the data stock on one server changes, it will be synchronized to the other server, so that the two servers
And can provide external services. This provides better performance than master-slave replication.
Next I will use two identical servers to achieve this effect:
I will omit the specific Mysql installation. The configuration of the Mysql master-slave architecture in the previous article is described in detail.
Server‑mysql: 192.168.1.108
Server2_mysql: 192.168.1.110
Topology:
Serverslave MySQL ------- server2_mysql
1. Create and authorize a user
Server1:
Mysql> grant replication slave on *. * TO 'server2' @ '192. 168.1.110'
Identified by 'server2 ';
Server2:
Mysql> grant replication slave on *. * TO 'server1' @ '192. 168.1.108'
Identified by 'server1 ';
2. Modify the Mysql master configuration file
Server1:
[Mysqld]
Server-id = 10
Log-bin = mysql-bin
Replicate-do-db = mydb
Auto-increment = 2 // increase by 2 each time
Auto-increment-offset = 1 // set the offset of the automatically increasing field, that is, the initial value is 2
Start the Mysql service:
# Service mysqld restart
Server2:
[Mysqld]
Server-id = 20
Log-bin = mysql-bin
Replicate-do-db = mydb
Auto-increment = 2 // increase by 2 each time
Auto-increment-offset = 2 // set the offset of the automatically increasing field, that is, the initial value is 2
Start the Mysql service:
# Service mysqld restart
Note: Only server-id and auto-increment-offset are different.
The value of auto-increment should be set to the total number of servers in the entire structure. In this case, two
Server, so the value is set to 2.
3. Restart two servers
# Service mysqld restart
4. To make the two databases the same, we back up one of the databases and restore them on the other database.
In this way, both databases are the same at the beginning.
Operate on Server 1:
# Mysqldump -- databases luowei>/tmp/luowei. SQL
Operate on Server 2:
Create an empty database with the same name as mydb
# Mysql
> Create database mydb;
> \ Q
# Scp 192.168.1.108:/tmp/mydb. SQL ./
# Mysql-uroot-p mydb </tmp/luowei. SQL
5. Then, the two servers advertise the location of the binary log and start the replication function:
On server1:
# Mysql
> CHANGE MASTER
> MASTER_HOST = '1970. 168.1.110 ',
> MASTER_USER = 'server2 ',
> MASTER_PASSWORD = 'server2 ';
Mysql> start slave;
On server2:
# Mysql
> CHANGE MASTER
> MASTER_HOST = '1970. 168.1.108 ',
> MASTER_USER = 'server1 ',
> MASTER_PASSWORD = 'server1 ';
Mysql> start slave;
6. View and verify:
View on two database servers respectively
Mysql> start slave;
View the database and table, and you will find that the content is the same. This is the architecture of the entire primary Mysql.
Configuration process.
Author: "IT dream-qi-sharing"