Mysql 5.5.40 implements One-Master-multiple-slave muil-slave and 5.5.40one-master

Mysql 5.5.40 implements One-Master-multiple-slave muil-slave and 5.5.40one-master

We know that the overhead of Mysql Reading and Writing Performance is quite different. Generally, the server is under 20% write pressure, and the other 80% pressure comes from reading. Of course, this depends on the actual business situation.

In this article, we are going to configure one master, three slaves. In fact, one master, multiple slaves are actually an extension of one master and one slave, because there is no communication between slave databases, which method is used to view the bandwidth load and traffic.

The principle of Mysql Master-Slave replication is that the Master constantly writes binary logs, Slave reads logs, and executes them according to the SQL records! For example:

The master-slave configuration of Mysql is inherent. You only need to perform simple configuration. Well, let's talk about it. Let's get started!

My environment is like this. All four machines on Vmware use bridging and dhcp is used to directly obtain Dynamic IP addresses.

Master: 192.168.83.61

Slave1: 192.168.83.87

Slave2: 192.168.83.88

Slave3: 192.168.83.89

Mysql versions on all four machines are Mysql 5.5.40

First, configure the Master

Vi/etc/my. cnf

Server-id = 61log-bin = mysql-binbinlog-do-db = test // binlog of the database to be synchronized-ignore-db = mysql // binlog of the ignored database-ignore-db = information-schema // ignored Database

Add a synchronization account for the three slave instances on the master node.

Mysql> grant replication slave1 on *. * to 'slave1 '@' 192. 168.83.87 'identified by 'slave1 '; // mysql> grant replication slave on *. * to 'slave2' @ '192. 168.83.88 'identified by 'slave2'; // log on to slave2 <pre name = "code" class = "plain"> mysql> grant replication slave on *. * to 'slave3 '@' 192. 168.83.89 'identified by 'slave3 '; // log on to slave3

Save and restart the Mysql service.

service mysqld restart

Then, in mysql command line mode, use

mysql> show master status\G;

*************************** 1. row ***************************File: mysql-bin.000001Position: 106Binlog_Do_DB: testBinlog_Ignore_DB: mysql,information-schema1 row in set (0.00 sec)

Second, add the configuration on the three Server Load balancer instances:

Vi/etc/my. cnf

Replicate-do-db = test // synchronized database replicate-ignore-db = mysql // ignored database replicate-ignore-db = information-schema // ignored Database

Server-id. Check whether the default configuration is normal. If no configuration is added, use the IP address to break the name.

Note that the most important thing is to configure the master database information. mysql 5.1 is discarded and I. cnf is used again.

master-host=192.168.83.61master-user= slave1master-password=slave1master-port=3306

Therefore, Versions later than 5.1 should be written as follows:

change master to master_host='192.168.83.61', master_user='slave1', master_password='slave1';

Then restart the mysql service.

In mysql Command Line Mode:

mysql>start slave;mysql>show slave status\G;

If the two variables are yes, the master and slave are running normally.

Slave_IO_Running: YesSlave_SQL_Running: Yes

Slave_IO_Running: connects to the master database and reads logs from the master database to the local computer to generate local log files.

Slave_ SQL _Running: reads the local log file and executes the SQL commands in the log.

Inserting data into the master database found that all three slave databases were updated, that's it!

For master-slave synchronization of mysql databases, the master and slave mysql versions must be the same. What will happen if they are different?

The version is consistent, which is officially recommended; slave must be at least the same as the first two accounts of the master version, which can be higher than the master;
If the version is inconsistent, the possible problem is the unstable synchronization (old compatibility issues), because exceptions may occur in some function processing, log reading, log parsing, and so on, synchronization errors are reported and must be handled manually.

What is the difference between the mesh structure and chain structure of multiple slave corresponding to one mysql master-slave master?

The master-slave mode basically does not exist, because as long as one of the Server Load balancer nodes has an exception, it will paralyze the subsequent Server Load balancer nodes,
In contrast to the master-2slave, any node exception, other nodes can operate normally, unless the master is disconnected