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

Objective:

In the actual production environment, if the database reads and writes are operated in the same database server, no matter in terms of security, high availability, or high concurrency, it is completely unable to meet the actual requirements. Therefore, it is generally necessary to synchronize data through master-slave replication, and then improve the concurrent load capacity of the database through read-write separation.

master-slave replication

(i) Principle

One server acts as the master during replication, while one or more other servers act as slaves. The master server writes updates to a binary log file and maintains an index of the file to track log cycles. When the slave connects to the master, it notifies the master of the location of the last successful update the slave read in the log. The slave server receives any updates that have occurred since then, and then blocks and waits for the master server to notify of new updates.

(ii) Types of replication supported by mysql

(1) Statement-based replication: SQL statements executed on the master execute the same statements on the slave. MySQL defaults to statement-based replication, which is more efficient. When it is found that exact replication is not possible, row-based replication is automatically selected.

(2) Line-based replication: copying changes instead of executing commands on the slave server. Support from mysql5.0

(3) Mixed type replication: By default, statement-based replication is adopted. Once it is found that statement-based replication cannot be accurately replicated, row-based replication will be adopted.

(iii) Copy the work process

(1)The master logs the changes to a binary log (these are called binary log events);

(2)slave copies master's binary log events to its relay log;

(3)slave redo events in relay logs, applying changes to your own data.

In addition, there is also a worker thread in master: like other MySQL connections, slave opening a connection in master causes master to start a thread. The replication process has one important limitation-replication is serialized on slaves, meaning parallel updates on masters cannot be performed in parallel on slaves.

read-write separation

(i) Principle

In simple terms, read/write separation is writing only on the master server and reading only on the slave server. The rationale is to have the master database handle transactional queries and the slave database handle select queries. Database replication is used to synchronize changes caused by transactional queries to slave databases in the cluster.

(2) Separation of reading and writing

Based on the middle proxy layer implementation: Mysql-Proxy Amoeba'

This experiment is implemented with amoeba

Experimental topology:

Experimental environment:

host OS IP address Primary software masterCentOS-7-x86_64192.168.37.128mysql-5.5.24slave1CentOS-7-x86_64192.168.37.131mysql-5.5.24slave2CentOS-7-x86_64192.168.37.132mysql-5.5.24amoebaCentOS-7-x86_64192.168.37.130jdk-6u14-linux-x64.bin ; amoeba-mysql-binary-2.2.0.clientCentOS-7-x86_64192.168.37.130---

Experimental installation package:

Link: pan.baidu.com/s/1bHbR0fPzmVrZf5rGB3UNCA Password: 8tho

Experimental process:

I. Time synchronization

Primary Server:

1. Install ntp, edit configuration file

yum install ntp -y #Ignore this step if installed vim /etc/ntp.conf Add at the end server 127.127.37.0 #Local is the clock source fudge 127.127.37.0 stratum 8 #Set the time level to 8/Clock can spread 8 layers

2. Open the service

systemctl start ntpd.service #Start the service systemctl stop firewalld.service setenforce 0

From Server:

3. Install ntp and ntpdate, and start the service.

yum install ntp ntpdate -y #Ignore this step if installed systemctl start ntpd.service systemctl stop firewalld.servicesetenforce 0

4, open time synchronization

/usr/sbin/ntpdate 192.168.37.128

II. Master-slave copy

5. Manually compile and install mysql5.5 on three servers (the operation process is summarized as follows)

//Installation environment

yum install gcc gcc-c++ make cmake ncurses-devel bison libaio-devel

//extract the installation package

mkdir /opt/abc

mount.cifs //192.168.37.1/SHARE /opt/abc

tar zxvf /opt/abc/mysql-5.5.24.tar.gz -C /opt/

//compile and install

cd /opt/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

//mysql configuration file copy

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

//Environment variable settings

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

. /etc/profile #(Note: ". "followed by spaces)

//create admin user mysql

useradd -s /sbin/nologin mysql

chown -R mysql.mysql /usr/local/mysql

//Data initialization

/usr/local/mysql/scripts/mysql_install_db \

--user=mysql \

--ldata=/var/lib/mysql \

--basedir=/usr/local/mysql \

--datadir=/home/mysql

//let the system recognize mysql

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

vi /etc/init.d/mysqld

basedir=/usr/local/mysql

datadir=/home/mysql

//start service

service mysqld start

6. Main server:

//Edit profile

vim /etc/my.cnf

service mysqld restart

mysql

//Authorize slave server

mysql>grant replication slave on . to 'myslave'@'192.168.37.% ' identified by '123456'; #Authorize to myslave users

#replication

mysql>flush privileges; #Refresh privileges

mysql>show master status; #View master log files

7. From the server:

//Edit profile

vim /etc/my.cnf

service mysqld restart

Note: If the startup fails here, you can reload the environment variables, or check whether the process is occupied and restart it.

mysql

//Slave synchronizes master

slave1:

Note: Specify the primary server (check the primary server log file location before operation, it will change continuously)

change master to master_host='192.168.37.128',master_user='myslave',master_password='123456',master_log_file='master-bin.000005',master_log_pos=107;

start slave; #Start master-slave copy

show slave status \G; #See two yes's

slave2:

change master to master_host='192.168.37.128',master_user='myslave',master_password='123456',master_log_file='master-bin.000005',master_log_pos=828;

start slave; #Start master-slave copy

show slave status \G; #See two yes's

8. Test master-slave replication: create database aaa on the master server and check whether database aaa exists on the slave server.

III. Amoeba installation (Java language recognition) and configuration

9. Turn off the firewall first

systemctl stop firewalld.service

setenforce 0

10. Share the jdk installation package and copy it to the/usr/local directory

mkdir /opt/abc

mount.cifs //192.168.37.1/SHARE /opt/abc

cd /opt/abc

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

11. Execute jdk file

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

12. Make the system recognize the jdk file, and put all its working directories under/usr/local

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

13. Environment variable modification, the system can use the command of installation file

vim /etc/profile

source /etc/profile #Refresh to make environment variables effective

14. Install amoeba

mkdir /usr/local/amoeba #New directory

cd /opt/abc

tar zxvf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/ #Extract the installation package to a new directory

chmod -R 755 /usr/local/amoeba/ #Guaranteed 755 permissions

15. Open amoeba

/usr/local/amoeba/bin/amoeba

16. Add permissions to three servers and open them to amoeba

mysql>grant all on . to test@'192.168.37.% ' identified by '123.com';

mysql>flush privileges;

17. Configure proxy server amoeba

vim/usr/local/amoeba/conf/amoeba.xml #Configuration file

vim conf/dbServers.xml #Database Configuration File

26th row

"test" reads mysql data as test

29 lines

123.com

42--Primary Server Address

192.168.37.128

50--from the server

192.168.37.131

Line 64--Server Pool

slave1,slave2

18. Open amoeba and check the port

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

netstat -ntap | grep java

IV. Client test (here client and amoeba share a virtual machine)

19. Simple installation mysql

yum install mysql -y

20. Log in to mysql and do the following

mysql -u amoeba -p123456 -h 192.168.37.130 -P8066

21, from the server: (for testing use, the actual production environment will not stop)

stop slave;

Main server: write only, amoeba can't read

insert into info values(1,'zhangsan',99);

slave1:

insert into info values(2,'lisi',88);

slave2：

insert into info values(3,'wangwu',77);

View on client:

The experiment is complete!

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.