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

This article is mainly about building MySQL master-slave replication and read-write separation on CentOS7. If you are interested, let's take a look at this article. I believe that after reading the specific operation steps of building MySQL master-slave replication and read-write separation on CentOS7, it is of some reference value to everyone.

Principle of MySQL master-slave replication

There is a close relationship between the master-slave replication of MySQL and the read-write separation of MySQL. First of all, master-slave replication must be deployed. Only when the master-slave replication is completed, can the data read-write separation be carried out.

(1) MySQL supports the type of replication.

1) statement-based replication. MySQL uses statement-based replication by default, which is more efficient.

2) Row-based replication. Copy the changes instead of executing the command on the slave server.

3) mixed type of replication. Statement-based replication is used by default, and row-based replication is used when it is found that statement-based replication cannot be accurately replicated.

(2) the working process of MySQL replication is shown in the figure.

1) before each transaction updates the data, Master records these changes in the binary log. After writing to the binary log, Master notifies the storage engine to commit the transaction.

2) Slave copies the Binary log of Master to its relay log. First, Slave starts a worker thread-- the Icano thread, which opens a normal link on Master, and then starts Binlog dump process. Binlog dump process reads events from Master's binary log, and if it has caught up with Master, it sleeps and waits for Master to generate new events. The Icano thread writes these events to the relay log.

3) SQL slave thred (SQL from the thread) handles the last step of the process. The SQL thread reads events from the relay log and replays the events to update the data in Slave to make it consistent with the data in Master. As long as the thread is consistent with the Ibank O thread, the relay log is usually in the cache of OS, so the overhead of the relay log is small.

There is a very important limitation in the replication process, that is, replication is serialized on Slave, that is, parallel update operations on Master cannot operate in parallel on Slave.

MySQL principle of separation of reading and writing

Simply put, read-write separation (see figure) means writing only on the master server and reading only on the slave server. The basic principle is to have the master database handle transactional queries and the slave database to handle select queries. Database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster.

Implementation based on the intermediate proxy layer: the agent is generally located between the client and the server, and the proxy server receives the client request and forwards it to the back-end database.

Experimental environment

Five servers are used to simulate the build, and the specific topology is shown in the figure:

Lab environment table:

Implementation steps

1. Build MySQL master-slave replication.

The main contents are as follows: (1) establish the time synchronization environment and build the time synchronization server on the main node.

1) install NTP.

[root@localhost ~] # yum install ntp-y

2) configure NTP.

[root@localhost ~] # vim / etc/ntp.conf server 127.127.126.0 / / Local clock source / / fudge 127.126.0 stratum 8 / / set the time level to 8 (limited to 15) / /

3) restart the service.

[root@localhost ~] # systemctl restart ntpd.service

(2) time synchronization is performed on the slave node server.

[root@localhost ~] # yum install ntpdate-y [root@localhost ~] # / usr/sbin/ntpdate 192.168.126.138 / / synchronize the time of the master server / /

(3) turn off firewalld Firewall on each server.

[root@localhost ~] # systemctl stop firewalld.service / / turn off the firewall / / [root@localhost ~] # setenforce 0

(4) install MySQL database. Installed on Master, Slave1, Slave2, the database I use is MySQL5.7.17, which is no longer demonstrated after installation.

(5) configure the MySQL Master master server.

1) modify or add the following in / etc/my.cnf.

[root@localhost mysql] # vim / etc/my.cnfserver-id = 11log-bin=master-bin / / Master server log file / / log-slave-updates=true / / update binary log from the server / /

2) restart the MySQL service.

[root@localhost ~] # systemctl restart mysqld.service

3) Log in to the MySQL program and authorize the server.

[root@localhost ~] # mysql-uroot-pmysql > GRANT REPLICATION SLAVE ON *. * TO 'myslave'@'192.168.126.%' IDENTIFIED BY' 123456; / / Authorization / / mysql > FLUSH PRIVILEGES;mysql > show master status +-+ | File | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set | +- -+ | master-bin.000001 | 604 | +- -+ 1 row in set (0.00 sec) where the File column shows the log name The position column shows the offset.

(6) configure slave server.

1) modify or add the following in / etc/my.cnf.

[root@localhost ~] # vim / etc/my.cnfserver-id = 22relay-log=relay-log-bin / / synchronize log files from the master server to the local / / relay-log-index=slave-relay-bin.index / / define the location and name of the relay-log / /

Note here that the server-id cannot be the same as the primary server.

2) restart the mysql service.

[root@localhost ~] # systemctl restart mysqld.service

3) Log in to mysql and configure synchronization.

Change the parameters of master_log_file and master_log_pos in the following command as a result of the primary server.

[root@localhost ~] # mysql-u root-pmysql > change master to master_host='192.168.126.138',master_user='myslave',master_password='123456',master_log_file='master-bin.000001',master_log_pos=604

4) start synchronization.

Mysql > start slave

5) check the Slave status and make sure the following two values are YES.

Mysql > show slave status\ G * * 1. Row * * Slave_IO_State: Waiting for master to send event Master_Host: 192.168.126.138 Master_User: myslave Master_Port: 3306 Connect_Retry: 60 Master_Log_File: master-bin.000001 Read_Master_Log_Pos: 604 Relay_Log_File: relay-log-bin.000002 Relay_Log_Pos: 321 Relay_Master_Log_File: master-bin.000001 Slave_IO_Running: Yes Slave_SQL _ Running: Yes Replicate_Do_DB: Replicate_Ignore_DB:

(7) verify the effect of master-slave replication.

1) create a new database db_test on the primary server.

Mysql > create database db_test

2) if you view the database on the master server and the slave server respectively, and show that the database is the same, the master-slave replication is successful.

Mysql > show databases / / Master server / / +-+ | Database | +-+ | information_schema | | db_test | | mysql | | performance_schema | | school | | sys | +- -+ 6 rows in set (0.03 sec) mysql > show databases / / slave server / / +-+ | Database | +-+ | information_schema | | db_test | | kgc | | mysql | | performance_schema | | sys | +- -+ 6 rows in set (0.05sec) build MySQL read-write separation

Amoeba (amoeba), this software is dedicated to the distributed database front-end proxy layer of MySQL. It mainly acts as SQL routing when the application layer accesses MySQL, and has load balancing, high availability, SQL filtering, read-write separation, routing related to the target database, and concurrent requests for multiple databases. Through Amoeba, we can achieve the functions of high availability, load balancing and data slicing of multiple data sources.

(1) install the Java environment on the host Amoeba.

Amoeba is based on jdk1.5, so jdk1.5 or version 1.6 is officially recommended. Higher versions are not recommended.

[root@localhost ~] # systemctl stop firewalld.service [root@localhost ~] # setenforce 0 [root@localhost tomcat] # cp jdk-6u14-linux-x64.bin / usr/local/ [root@localhost local] #. / jdk-6u14-linux-x64.bin / / press enter when prompted / / [root@localhost local] # mv jdk1.6.0_14/ / usr/local/jdk1.6 [root@localhost local] # vim / etc/profile

Add the following configurations

Export JAVA_HOME=/usr/local/jdk1.6export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/libexport PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/binexport AMOEBA_HOME=/usr/local/amoebaexport PATH=$PATH:$AMOEBA_HOME/ bin [root @ localhost local] # source / etc/profile / start / /

The Java environment has been configured successfully.

(2) install and configure Amoeba software

[root@localhost local] # mkdir / usr/local/amoeba create work path / / [root@localhost tomcat] # tar zxvf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba/ / extract installation package / / [root@localhost tomcat] # chmod-R 755 / usr/local/amoeba/ prompt for amoeba permissions / / [root@localhost tomcat] # / usr/local/amoeba/bin/amoebaamoeba start | stop / / shows that Amoeba is installed successfully / /

(3) configure Amoeba read-write separation and two Slave read load balancers.

1) Open permissions to Amoeba access in Master, Slave1 and Slave2.

Grant all on *. * to test@'192.168.126.%' identified by '123.com'

2) Edit the amoeba.xml configuration file.

[root@localhost tomcat] # cd / usr/local/amoeba/ [root@localhost amoeba] # vim conf/amoeba.xml-30 lines-- amoeba----32 lines-- 123456Murray-117-remove comments-master master slaves

3) Edit the dbServers.xml configuration file.

Vi conf/dbServers.xml--26-29Murray-remove comments-/ / Line / / test 123.commur42-Master server address-192.168.126.138Murray 52-Slave server hostname-55-Slave server address-192.168.126.162 slave server slave2 / / add / 192.168.126.232-end-slave1,slave2 / / modify / /

4) after the configuration is correct, you can start the Amoeba software, and its default port is TCP 8066.

[root@localhost amoeba] # / usr/local/amoeba/bin/amoeba start& [root@localhost amoeba] # netstat-anpt | grep javatcp6 0 0 127.0.1 netstat 28750: * LISTEN 3370/java tcp6 0 0: 8066:: * LISTEN 3370/java. / / omitted / /

(4) testing

1) on the client host.

[root@localhost ~] # yum install mysql-y

You can access MySQL through an agent:

[root@localhost] # mysql-u amoeba-p123456-h 192.168.126.132-P8066. / / omit / / MySQL [(none)] >

2) create a table on Master, synchronize it to each slave server, then turn off the Slave function of each slave server, and then insert the distinction statement.

Mysql > use db_test;Database changedmysql > create table zang (id int (10), name varchar (10), address varchar (20)); / / create table / / Query OK, 0 rows affected (0.06 sec)

Turn off the slave feature on both servers:

Mysql > stop slave

Then insert a distinction statement on the primary server:

Mysql > insert into zang values ('1inserted data / / insert data / /

3) manually insert other content from the server.

Slave1:mysql > use db_test;mysql > insert into zang values); / / insert data / / Query OK, 1 row affected (0.03 sec) slave2:mysql > use db_test;mysql > insert into zang values ('3 parallel recordings); / / insert data / / Query OK, 1 row affected (0.03 sec)

4) Test read operation

The results of the first query on the client host are as follows:

MySQL [db_test] > select * from zang +-+ | id | name | address | +-+ | 3 | zhang | this_is_slave2 | +-+ 1 row in set (0.01 sec)

The second query results are as follows:

MySQL [db_test] > select * from zang +-+ | id | name | address | +-+ | 2 | zhang | this_is_slave1 | +-+ 1 row in set (0.01 sec)

Results of the third query:

MySQL [db_test] > select * from zang +-+ | id | name | address | +-+ | 3 | zhang | this_is_slave2 | +-+ 1 row in set (0.01 sec)

5) Test the write operation.

Insert a statement on the client host:

MySQL [db_test] > insert into zang values; Query OK, 1 row affected (0.02 sec)

However, it cannot be queried on client, and in the end, the content of this statement can only be seen on Master, indicating that the written operation is on the Master server.

Mysql > select * from zang +-+ | id | name | address | +-+ | 1 | zhang | this_is_master | | 5 | zhang | write_test | +- -+ 2 rows in set (0.01 sec)

It is verified that the separation of MySQL read and write has been realized. At present, all write operations are on the Master master server to avoid data asynchronism; all read operations are allocated to the Slave slave server to share the pressure on the database.

Is it helpful to you all about the detailed operation steps of building MySQL master-slave replication and read-write separation on CentOS7? If you want to know more about it, you can continue to follow our industry information section.

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.