Shulou(Shulou.com)06/01 Report--

The following mainly brings you how to set up Mysql's master-slave database, hoping that these contents can bring you practical use, which is also the main purpose of my editing how to set up Mysql's master-slave database. Okay, no more nonsense, let's go straight to the following.

1. Configuration:

Two Cloud Virtual Machine configured with mysql, such as mysql5.7

PS: If the conditions are limited, one can also be installed, only need to install two mysql services (different ports such as 3306, 3307), but this is a bit contrary to the original intention of efficiency

2. Code:

1. Generally, mysql operation class reads and writes such as update,insert and select * from are routed directly at the code layer. Corresponding to different Cloud Virtual Machine.

2. Middleware proxy, that is, middleware sends read and write requests to the master-slave Cloud Virtual Machine without changing the code.

Now let's talk about how to configure master-slave:

1, open port: most hosts default Mysql port 3306, then the first two machines in the security group or firewall port 3306 open and restart.

2. The port is open. Now configure two Cloud Virtual Machine (the same applies to two Mysql servers on one host):

The following is an example of creating a sync account spec, password spec_password from the main ip: 192.168.0.2 192.168.0.1

Main Cloud Virtual Machine (add, delete and modify):

#1.Mysql configuration changed to the following and restart the service (restart directly from command line:service mysql restart)[mysqld]server-id=1log-bin=master-binlog-bin-index=master-bin.index#2. mysql executed from command line or executed in phpmyadmin:GRANT RESOLUTION SLAVE ON *.* TO 'spec'@'192.168.0.2' IDENTIFIED BY 'spec_password';flush privileges;

This completes the main Cloud Virtual Machine.

Configure from Cloud Virtual Machine:

#1.Mysql configuration change to the following and restart the service (restart directly from command line:service mysql restart)[mysqld]server-id=2relay-log-index=slave-bin.indexrelay-log=slave-bin#2. mysql execution from command line or phpmyadmin execution:change master to master_host='192.168.0.1', master_port=3306,master_user='spec',master_password='spec_password', master_log_file='master-bin. 00001', master_log_pos=0;start slave

In general, configuration is complete at this point.

3. Test:

Query statement from Cloud Virtual Machine:show slave status

Slave_IO_State=Waiting for master to send event

The rest of the states are usually due to:

1. Master-slave security group or firewall does not open port 3306

2. There is no ping between master and slave.

3. The above spec account secret error

From now on, any additions, deletions and changes on the master Cloud Virtual Machine will be synchronized to the slave server.

4. Recommendations:

1. Remember to read from Cloud Virtual Machine only, do not add, delete or modify. Keep your data synchronized so that if one server fails, the other server can take over (in fact, if the main server goes down, some of the latest data will definitely be lost).

2. Bandwidth of Slave Cloud Virtual Machine must be>= Master Server to minimize synchronization latency. if it's a small pipe, synchronization will naturally slow down a step.

For the above on how to set up Mysql's master-slave database, do you think it is very helpful? If you need to know more, please continue to pay attention to our industry information, I believe you will like these contents.

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.