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

In the actual production environment, if the read and write of the database are all operated in the same database server, no matter in terms of security, high availability, or high concurrency, it can not meet the actual needs at all. Therefore, generally speaking, the scheme of synchronizing data through master-slave replication (Master-Slave) and then improving the concurrent load capacity of the database through read-write separation is deployed and implemented.

As shown in the following figure, a master mysql replicates data with two slave mysql, and the front-end application operates on the master device during the database write operation and the two slave devices during the database read operation, thus greatly reducing the pressure on the master device.

The principle of mysql master-slave replication the master-slave replication of mysql is closely related to the read-write separation of mysql. First of all, master-slave replication must be deployed. Only when the master-slave replication is completed, can data read-write separation be carried out.

The working process of mysql replication is explained in detail 1) before each transaction updates the data, Master records these changes in the binary log. After writing to the binary log, Master informs the storage engine to commit transaction 2) Slave copies the Binary log of Master to its successor log. First, Slave starts a worker thread-the Imax O thread, which opens a normal connection on the 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 thread (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 IWeiO thread, the relay log is usually located in the cache of OS, so the overhead of the relay log is very small. Mysql read-write separation principle

The implementation of mysql read-write separation:

Master-slave replication and read-write separation experimental topology, there are three areas of authentication that need to be clarified: 1) mysql database server master-slave authentication, set authentication between master and slave synchronization 2) amoeba--- > mysql, set amoeba proxy service access mysql service authentication 3) client---- > amoeba, set front-end customer client access amoeba proxy service authentication

Deployment of case environment

Host operating system IP address installation software

Master CentOS7 192.168.30.55 mysql-5.5.24.tar.gz

Slave1 CentOS7 192.168.30.10 mysql-5.5.24.tar.gz

Slave2 CentOS7 192.168.30.50 mysql-5.5.24.tar.gz

Amoeba CentOS6.5 192.168.30.15 jdk-6u14-linux-x64.bin, amoeba-mysql-binary-2.2.0.tar.gz

Client CentOS6.5 192.168.30.99 uses yum to install mysql for remote testing

Baidu cloud disk provides free installation package: mysql-5.5.24.tar.gz: https://pan.baidu.com/s/1T3fEFotFL9jevFIOfQpOTAjdk-6u14-linux-x64.bin: https://pan.baidu.com/s/1Y9HAPrSg2nVKeQbA-BTYmQamoeba-mysql-binary-2.2.0.tar.gz: https://pan.baidu.com/s/1ymFNDm84u5k09sEBE4SMMw to build mysql master-slave replication to establish time synchronization environment, NTP time synchronization protocol diagram:

1) Master server master settings

Yum install ntp-y

Vim / etc/ntp.conf

Server 127.127.30.0 / / Local is the clock source / /

Fudge 127.127.30.0 stratum 8max / set time level is 8max /

Service ntpd start

2) time synchronization from node slave

Yum install ntp ntpdate-y

Service ntpd start

/ usr/sbin/ntpdate 192.168.30.55 / / time synchronization / /

All hosts turn off firewalls and turn off enhanced features

Systemctl stop firewalld.service

Setenforce 0

Compile and install the mysql database manually. Install on Master,Slave1,Slave2. The steps are as follows: (for a detailed explanation of the installation steps of the database mysql-5.5.24, please refer to the previously written article "CentOS7LNMP Schema deployment". Only the steps are provided below) 1) compile and install mysql

Yum-y install ncurses-devel cmake gcc gcc-c++ ncurses bison libaio-devel / / ncurses is the basic library for screen control under character terminals.

Tar xzvf mysql-5.5.24.tar.gz

Cd mysql-5.5.24

Cmake\

-DCMAKE_INSTALL_PREFIX=/usr/local/mysql\

-DDEFAULT_CHARSET=utf8\

-DDEFAULT_COLLATION=utf8_general_ci\

-DWITH_EXTRA_CHARSETS=all\

-DSYSCONFDIR=/etc\

-DMYSQL_DATADIR=/home/mysql/\

-DMYSQL_UNIX_ADDR=/home/mysql/mysql.sock\

-DWITH_MYISAM_STORAGE_ENGINE=1\

-DWITH_INNOBASE_STORAGE_ENGINE=1\

-DWITH_ARCHIVE_STORAGE_ENGINE=1\

-DWITH_BLACKHOLE_STORAGE_ENGINE=1\

-DENABLED_LOCAL_INFILE=1\

-DWITH_SSL=system\

-DMYSQL_TCP_PORT=3306\

-DENABLE_DOWNLOADS=1\

-DWITH_SSL=bundled

Make & & make install

2) Optimization and adjustment

Cp support-files/my-medium.cnf / etc/my.cnf

Cp support-files/mysql.server / etc/init.d/mysqld

Chmod + x / etc/init.d/mysqld

Chkconfig-add mysqld

Chkconfig-level 35 mysqld on

Echo "PATH=$PATH:/usr/local/mysql/bin/" > > / etc/profile

. / etc/profile (Note: "." Followed by a space)

3) initialize the database

Useradd-s / sbin/nologin mysql

Chown-R mysql.mysql / usr/local/mysql

/ usr/local/mysql/scripts/mysql_install_db\

-- user=mysql\

-- ldata=/var/lib/mysql\

-- basedir=/usr/local/mysql\

-- datadir=/home/mysql

Ln-s / var/lib/mysql/mysql.sock / home/mysql/mysql.sock

Vi / etc/init.d/mysqld

Basedir=/usr/local/mysql

Datadir=/home/mysql

4) start the mysql service and set the mysql user password

Service mysqld start

Mysqladmin-uroot-p password 'abc123'

The mysql master server configuration modifies or adds the following options to the configuration file

Vim / etc/my.cnf

Server-id = 11 / / modify id

Log-bin=master-bin / / add master server log file / /

Log-slave-updates=true / / add update binary log from server / /

Service mysqld restart

Log in to the mysql program to authorize the slave server

Grant replication slave on *. * to 'myslave'@'192.168.30.%' identified by' abc123'; # to authorize

Flush privileges; / / Refresh Authorization Settings

The file column shows the log name and the position column shows the offset, both of which will be used later when configuring the slave server. Slave should make new updates on master from this point

Mysql modifies or adds the following options to the configuration file from the server configuration

Vim / etc/my.cnf

[mysqld]

Server-id = 22 # the other id is different

Relay-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 / /

Service mysqld restart

Log in to mysql configuration synchronization to change the parameters of master_log_file and master_log_pos in the following command based on the results of the primary server

Change master to master_host='192.168.30.55',master_user='myslave',master_password='abc123',master_log_file='mysql-bin.000011',master_log_pos=267

Start slave; / / start synchronization

Show slave status\ G; / / check the slave status and make sure the following two values are yes

.

Slave_IO_Running: Yes # these two must be yes

Slave_SQL_Running: Yes

Verify the effect of master-slave replication 1) log in to mysql on the master and slave server to view the database

2) if you create a new database school on the master server and view the database on the master server and slave server respectively, you can see the new database school created by master, then the master-slave replication is successful.

Set up mysql read-write separation amoeba server configuration (1) install java environment on host amoeba because amoeba is based on jdk1.5, so jdk1.5 or version 1.6 is officially recommended. Higher version is not recommended.

Service iptables stop

Setenforce 0

Cp jdk-6u14-linux-x64.bin / usr/local/

. / jdk-6u14-linux-x64.bin

Yes

Press enter

Mv jdk1.6.0_14/ / usr/local/jdk1.6

(2) add java environment variable

Vi / etc/profile

Export JAVA_HOME=/usr/local/jdk1.6

Export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib

Export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin/:$PATH:$HOME/bin

Export AMOEBA_HOME=/usr/local/amoeba

Export PATH=$PATH:$AMOEBA_HOME/bin

Source / etc/profile

(3) install and configure amoeba software

Mkdir / usr/local/amoeba

Tar zxvf amoeba-mysql-binary-2.2.0.tar.gz-C / usr/local/amoeba/

Chmod-R 755 / usr/local/amoeba/

/ usr/local/amoeba/bin/amoeba

Displays amoeba start | stop indicates that the installation is successful

(4) configure amoeba read-write separation. Two slave read load balancers log in to mysql on master,slave1,slave2 and add permissions open to amoeba access.

Grant all on *. * to test@'192.168.30.%' identified by '123.compose; (5) go back to the amoeba server, edit the amoeba.xml configuration file, and set the authentication authentication for the front-end client client to access the amoeba proxy service.

Cd / usr/local/amoeba

The modified content is the bold part, pay attention to delete the comment

Vim conf/amoeba.xml

-30 lines-

Amoeba

-32 lines-

123456

-117-remove comments-

Master

Master

Slaves

Edit the dbServers.xml configuration file to set the authentication of the amoeba proxy service to access the mysql service

Vim conf/dbServers.xml

-- 26-29 music-- remove comments--

Test

123.com

-42-Primary server address-

192.168.30.55

-- 52-from the server hostname-

-- 55-from server address-

192.168.30.10

-- the end--

Slave1,slave2

After the configuration is correct, you can start the amoeba software, whose default port is tcp 8066

/ usr/local/amoeba/bin/amoeba start&

Netstat-anpt | grep java

Test the experimental results on the Client host to test yum install-y mysql

Mysql-u amoeba-p123456-h 192.168.30.15-P8066 / / create a table info on the master through accessing mysql through the amoeba proxy, synchronize it to each slave server, then turn off the slave function of each slave server, and then insert a distinguishing statement on master mysql > use school;mysql > create table info (id int,name char (10)); mysql > insert into info values (1LJM master')

From the server slave2mysql > stop slave;mysql > insert into info values (3pm slave 2')

From server slave1mysql > stop slave;mysql > insert into info values (2pm slave 1')

The test read operation queries the results for the first time on client

Mysql > select * from info

+-+ +

| | id | name |

+-+ +

| | 2 | slave1 |

+-+ +

1 row in set (0.02 sec)

Results of the second query

Mysql > select * from info

+-+ +

| | id | name |

+-+ +

| | 3 | slave2 |

+-+ +

1 row in set (0.00 sec)

The result of the third query mysql > select * from info

+-+ +

| | id | name |

+-+ +

| | 2 | slave1 |

+-+ +

1 row in set (0.02 sec) Test write operation inserts a statement mysql > insert into info values on the client host.

Query OK, 1 row affected (0.01sec)

However, it can not be queried on client,slave1,slave2, and the content of this statement can only be found on master, indicating that the write operation is on the master server.

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.

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.