_ Rui_ ao 2022-02-13 07:07:02 阅读数:244
Read / write separation , The basic principle is to make the primary database deal with transaction 、 Change 、 Delete operation (INSERT、UPDATE、DELETE), And processing from the database SELECT Query operation . Database replication is used to synchronize changes caused by transactional operations to the slave database in the cluster .
In the actual production environment , Read and write to the database in the same database server , It can't meet the actual needs . Whether it's in security 、 High availability or high concurrency can not meet the actual needs . therefore , Synchronize data through master-slave replication , Then through read-write separation to improve the concurrent load capacity of the database . It's kind of like rsync, But here's the difference rsync Is to back up disk files , and mysql Master-slave replication is the replication of data in the database 、 Statement for backup .
Replication framework
common MySQL The separation of reading and writing can be divided into the following two types :
Due to the use MySQL Proxy Need to write a lot of Lua Script , these Lua Not ready-made , But you need to write it yourself . This is not familiar with MySQL Proxy Built in variables and MySQL Protocol It's very difficult for people .
Amoeba Is a very easy to use 、 Highly portable software . Therefore, it is widely used in the agent layer of database in production environment .
1. Environment configuration
master The server : 192.168.254.10 mysql5.7
slave1 The server : 192.168.254.20 mysql5.7
slave2 The server : 192.168.254.30 mysql5.7
Amoeba The server : 192.168.254.40 jdk1.6、Amoeba
client The server : 192.168.254.50 mysql
2. Initial environmental preparation
systemctl stop firewalld
setenforce 0
Set up time synchronization
[[email protected] ~]#yum install ntp -y
Modify the configuration file
# Insert... At the end of the line after opening the file
vim /etc/ntp.conf
server 127.127.254.0 # Set the local clock source
fudge 127.127.254.0 stratum 8 # Set the time level to 8 Restriction on 15 within
Opening service
service ntpd start
4. Configure slave (192.168.254.20、192.168.254.30)
Configure slave
[[email protected] ~]# yum install ntp -y
[[email protected] ~]# yum install ntpdate -y
# Install synchronization service
[[email protected] ~]# service ntpd start
# Opening service
Redirecting to /bin/systemctl start ntpd.service
[[email protected] ~]# /usr/sbin/ntpdate 192.168.254.10
# Perform synchronization
23 Dec 11:18:18 ntpdate[2534]: the NTP socket is in use, exiting
[[email protected] ~]# crontab -e
*/30 * * * * /usr/sbin/ntpdate 192.168.254.10
slave2:192.168.254.30 Same as above
5. Configure the primary server (192.168.254.10)
[[email protected] ~]#vim /etc/my.cnf
[mysqld]
server-id = 1
log-bin=master-bin
# Turn on binary log
binlog_format=MIXED
# Binary log format
log-slave-updates=true
# Turn on synchronization from the server
log-bin=master-bin
binlog_format=MIXED
log-slave-updates=true
[root@localhost ~]# systemctl restart mysqld
[root@localhost ~]# mysql -u root -p
GRANT REPLICATION SLAVE ON *.* TO 'myslave'@'192.168.254.%' IDENTIFIED BY '123123';
# Authorize the slave server , Allow all users of this network segment , send myslave user 123123 Log in with a password and use any table in the library
FLUSH PRIVILEGES;
# Save the user and permission configuration to memory
show master status;
# see master The binary log being used by the server and the current execution binary log location
#File The column displays the log name ,Fosition Column shows the offset
6. From the server mysql To configure (192.168.254.20、192.168.254.30)
vim /etc/my.cnf
server-id = 2
# modify , Be careful id And Master Different , Two Slave Of id It has to be different
relay-log=relay-log-bin
# add to , Turn on relay log , Synchronize log records from the primary server to the local
relay-log-index=slave-relay-bin.index
# add to , Define the location and name of the relay log file
systemctl restart mysqld
mysql -u root -p
change master to master_host='192.168.254.10' , master_user='myslave',master_password='123123',master_log_file='master-bin.000003',master_log_pos=604;
# Configuration synchronization , Be careful master_log_file and master_log_pos The value of should be the same as Master Consistency of query , Here is an example , Everyone's is different
start slave;
# Start the synchronization , If an error is reported, execute reset slave;
show slave status\G
# see Slave state
// Make sure IO and SQL Threads are Yes, It means that the synchronization is normal .
Slave_IO_Running: Yes
# Responsible for communication with the host io signal communication
Slave_SQL_Running: Yes
# Be responsible for your own slave mysql process
commonly Slave_IO_Running: No The possibility of :
1、 The Internet is not working
2、my.cnf Configuration problem
3、 password 、file file name 、pos The offset is not right
4、 The firewall is not closed
Verify the master-slave replication effect
Execute... On the primary server
create database db_test;
Go and see from the server
show databases;
server1:
server2:
Amoeba(192.168.254.40)
1. install Java Environmental Science
because Amoeba Based on jdk1.5 Developed , Therefore, it is officially recommended to use jdk1.5 or 1.6 edition , Higher versions are not recommended .
take jdk-6u14-linux-x64.bin and amoeba-mysql-binary-2.2.0.tar.gz.0 Upload to /opt Under the table of contents .
cd /opt/
cp jdk-6u14-linux-x64.bin /usr/local/
# Copy the executable to /usr/local Under the table of contents
cd /usr/local/
chmod +x jdk-6u14-linux-x64.bin
# Use chmod +x Empower them
./jdk-6u14-linux-x64.bin
# Execute the document , Press the space to the last line , Press yes, Press enter
mv jdk1.6.0_14/ /usr/local/jdk1.6
# take jdk Directory move to /usr/local Directory and renamed jdk1.6
vim /etc/profile
# Edit global profile
# Add... In the last line
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
# Execute global configuration file
java -version
# see java Version information
2. install Amoeba Software
mkdir /usr/local/amoeba
tar zxvf /opt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/
chmod -R 755 /usr/local/amoeba/
/usr/local/amoeba/bin/amoeba
// If displayed amoeba start|stop Description installation successful
3. To configure Amoeba Read / write separation , Two Slave Read load balancing
Now? Master、slave1、slave2 Of mysql Open permissions to Amoeba visit
grant all on *.* to test@'192.168.254.%' identified by '123.com';
amoeba Server configuration amoeba service
cd /usr/local/amoeba/conf/
cp amoeba.xml amoeba.xml.bak
vim amoeba.xml
# modify amoeba The configuration file
30 modify
<property name="user">amoeba</property>
32 modify
<property name="password">123123</property>
115 modify
<property name="defaultPool">master</property>
117 Remove annotations –
<property name="writePool">master</property>
<property name="readPool">slaves</property>
cp dbServers.xml dbServers.xml.bak
vim dbServers.xml # Modify the database configuration file
23 Comment out
effect : Default entry test library in case mysql There is no test library , Will report a mistake
<!-- mysql schema
<property name="schema">test</property>
-->
26 modify
<!-- mysql user -->
<property name="user">test</property>
28-30 Remove annotations
<property name="password">123.com</property>
45 modify , Set the name of the primary server Master
<dbServer name="master" parent="abstractServer">
48 modify , Set the address of the primary server
<property name="ipAddress">192.168.184.10</property>
52 modify , Set the name of the slave server slave1
<dbServer name="slave1" parent="abstractServer">
55 modify , Set up slave server 1 The address of
<property name="ipAddress">192.168.184.30</property>
58 Copy above 6 Line paste , Set up slave server 2 The name of slave2 And address
<dbServer name="slave2" parent="abstractServer">
<property name="ipAddress">192.168.184.40</property>
65 modify
<dbServer name="slaves" virtual="true">
71 modify
<property name="poolNames">slave1,slave2</property>
/usr/local/amoeba/bin/amoeba start& # start-up Amoeba Software , Press ctrl+c return
netstat -anpt | grep java # see 8066 Whether the port is open , The default port is TCP 8066
Test on the client server :
Use yum Fast installation MySQL Virtual client
yum install -y mysql mysql-server
mysql -u amoeba -p123123 -h 192.168.184.20 -P8066
adopt amoeba Server proxy access mysql , Connect through the client mysql Only the main service will record the data written after , Then synchronize to the slave server
On the main server
use db_test;
create table test (id int(10),name varchar(10),address varchar(20));
Two from the server
stop slave; # Turn off synchronization
use db_test;
stay slave1 On
insert into test values('1','zhangsan','this_is_slave1');
stay slave2 On
insert into test values('2','lisi','this_is_slave2');
On the main server
insert into test values('3','wangwu','this_is_master');
On the client server
use db_test;
select * from test; // The client will report to slave1 and slave2 Reading data , Only the data added from the two servers is displayed , No data added on the primary server
insert into test values('4','qianqi','this_is_client'); // Only the primary server has this data
Execute on two slave servers start slave; You can synchronize the data added on the primary server
start slave;
copyright:author[_ Rui_ ao],Please bring the original link to reprint, thank you. https://en.javamana.com/2022/02/202202130707001752.html