MySQL master-slave replication and read/write separation

The role of master-slave replication and master-slave replication:
In the actual production environment, read and write to the database in the same database server, is unable to meet the actual demand, through the master-slave replication method to synchronize the data, and then through the read and write separation to improve the database's concurrency load capacity

.
MYSQ Supported Replication Types

• Statement-based replication. Execute SQL statements on the server and execute the same statement from the server, MySQL defaults to statement-based replication, which is highly efficient.

• Row-based replication. Copy the changes to the past, rather than executing the commands from the server again.

• Mixed-type replication. Statement-based replication is used by default, and row-based replication is used when a statement cannot be accurately replicated based on the discovery.
  .
  The work process of replication
  .
  • Master records These changes in binary logging before each transaction updates the data. After the write binary log is complete, Master notifies the storage engine to commit the transaction.

• Slave copies the binary log of master to the secondary log. First slave starts a worker thread (I/O), the I/O thread opens a normal connection on master, and then starts Binlog dump process. Binlog dump process reads the event from the binary log of master, and if it has been followed by master, it sleeps and waits for Master to produce new events, which the I/O thread writes to the relay log.

• SQL slave thread (SQL slave thread) handles the last step of the process, where the SQL thread reads events from the log and replays the events in them to update the slave data so that it is consistent with the data in master as long as the thread is consistent with the I/O thread. The trunk log is typically located in the OS cache, so the overhead of the trunk log is minimal.
  .
  Before the more common MySQL read-write separation is divided into the following two kinds:
  .

• Internal implementation based on program code

  Routing classifications in code based on select and insert are the most widely used in the current production environment. The advantage is that the performance is good, because the program is implemented in the code, do not need to add additional hardware expenses, the disadvantage is that developers need to implement, operations and maintenance personnel can not do.
  .

• Implementation based on intermediate agent layer

  The agent generally between the application server and the database server, the proxy database server received the application server request after the decision to forward to, back-end database, there are the following representative program.
  .
  Environment Introduction;
  

Installing the MySQL process does not explain
MySQL Master-slave replication

(MySQL. Main

[[email protected] ]# mount /dev/cdrom /media/[[email protected] ]# yum -y install ntp

.

[[email protected] ]# vim /etc/ntp.conf #restrict 192.168.1.0 mask 255.255.255.0 nomodify notrapserver 127.127.1.0      /添加fudge 127.127.1.0 stratum 8 /添加.

重启ntpd服务[[email protected] ]# service ntpd restart

.

防火墙开例外[[email protected] ]# iptables -I INPUT -p udp --dport 123 -j ACCEPT[[email protected] ]# iptables -I INPUT -p udp --dport 3306 -j ACCEPT

在(节点A)进行时间同步[[email protected] ]# yum -y install ntpdate[[email protected] ]# /usr/sbin/ntpdate 192.168.1.30

在(节点B)进行时间同步[[email protected] ]# yum -y install ntpdate[[email protected] ]# /usr/sbin/ntpdate 192.168.1.30

.
(MySQL. Main

server-id       = 11        //mysql数据的唯一标示（不能重复）log-slave-updates=true      //允许连级复制   （增加）log-bin=master-bin      //二进制文件名（修改）

.

重启mysql服务[[email protected] ]# service mysqld restart

.

登陆mysql给从服务器授权[[email protected] ]# mysql -u root -p.mysql> GRANT REPLICATION SLAVE ON *.* TO ‘lijialiang‘@‘192.168.1.%‘ IDENTIFIED BY ‘123456‘;.mysql> FLUSH PRIVILEGES;

.

mysql> show master status;+-------------------+----------+--------------+------------------+| File              | Position | Binlog_Do_DB | Binlog_Ignore_DB |+-------------------+----------+--------------+------------------+| master-bin.000001 |      558 |              |                  |+-------------------+----------+--------------+------------------+1 row in set (0.03 sec)

.
(Node B,c)

在/etc/my.cnf中修改以下内容[[email protected] ~]# vim /etc/my.cnfserver-id       = 22                //不能与其他实例重复log-bin=mysql-bin               //二进制日志文件名 修改relay-log=relay-log-bin             //复制过来的二进制文件名，增加relay-log-index=slave-relay-bin.index       //中继日志存放的文件名称，增加

.

重启mysql服务[[email protected] ]# service mysql restart

.
Log in to MySQL configuration synchronization

[[email protected] ]# mysql -u root -pmysql> change master tomaster_host=‘192.168.1.30‘,master_user=‘lijialiang‘,master_password=‘123456‘,master_log_file=‘master-bin.000001‘,master_log_pos=558;IP地址、用户、密码都master的数据库信息

.

启动同步mysql> start slave;

.

查看slave状态确保以下两个值为YES.mysql> show slave status\G*************************** 1. row ***************************               Slave_IO_State: Waiting for master to send event                  Master_Host: 192.168.1.30                  Master_User: lijialiang                  Master_Port: 3306                Connect_Retry: 60              Master_Log_File: master-bin.000001          Read_Master_Log_Pos: 558               Relay_Log_File: relay-log-bin.609531                Relay_Log_Pos: 254        Relay_Master_Log_File: master-bin.000001             Slave_IO_Running: Yes            Slave_SQL_Running: Yes

.
(MySQL. Main
.

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.11 sec)

.

mysql> create datebase IT;.mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || IT                 || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.11 sec)

.
(Node B,c)

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || IT                 || mysql              || performance_schema || test               |+--------------------+5 rows in set (0.27 sec)

.
Read/write separation

(Amoeba)
Read/write Detach install Java environment (amoeba Software runs on Java platform)
Running the JDK

[[email protected] ]#  umount /dev/cdrom /media/[[email protected] ]#  mount /dev/cdrom /media/[[email protected] ]#  cp jdk-6u14-linux-x64.bin /usr/local/[[email protected] ]#  cd /usr/local/[[email protected] ]#   chmod +x jdk-6u14-linux-x64.bin  [[email protected] ]#  ./jdk-6u14-linux-x64.bin 安装过程中提示（yes/no),我们要选择yes安装

.

修改/etc/profile配置文件，增加以下配置[[email protected] ]#  mv jdk1.6.0_14/ /usr/local/jdk1.6[[email protected] ]#  vim /etc/profile

.

export JAVA_HOME=/usr/local/jdk1.6  //设置jdk的根目录export CLASSPATH=$CLASSPATH:$JAVA_HOME/lib:$JAVA_HOME/jre/lib       //将jdk的程序文件赋予CLASSPATH变量export PATH=$JAVA_HOME/lib:$JAVA_HOME/jre/bin:$PATH:$HOME/bin       //将jdk的程序文件赋予PATH变量export AMOEBA_HOME=/usr/local/amoeba/                                                      //定义AMOEBA的根目录export PATH=$PATH:$AMOEBA_HOME/bin                                                     //将amoeba的程序文件复制给PATH变量

.
Execute script

[[email protected] ]#  source /etc/profile[[email protected] ]#   java -version

.
Installing and configuring Amoeba software
Installation

[[email protected] ]#   mkdir /usr/local/amoeba[[email protected] ]#  cd /media/[[email protected] ]#   ls [[email protected] ]#   tar zxf amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba/[[email protected] ]#   chmod -R 755 /usr/local/amoeba/

.
The following appears to indicate that the installation was successful

[[email protected] ]#  /usr/local/amoeba/bin/amoebaamoeba start|stop.

配置amoeba读写分离，两个从节点读负载均衡在主从服务器上开放权限给amoeba（三台服务器上都做相同设置，这里以一台为例）mysql> grant all on *.* to [email protected]‘192.168.1.%‘ identified by ‘123456‘;

.
Modify the Amoeba.xml file
Note: All profile comments are--> <!--content, and then delete the comments, please delete the contents, it is best to delete the positive row, but sometimes only need to delete the head and tail, the configuration items inside can be used directly. This configuration file needs to define two configurations, the first is what the application uses to connect amoeba access to the back-end MySQL database, and the second is to define the default write pool and read pool.

[[email protected]]# cd/usr/local/amoeba/[[email protected]]# vim conf/amoeba.xml <bean class= "Com.mei Dusa.amoeba.mysql.server.MysqlClientAuthenticator "> <propertyname=" User ">a Moeba</property>//The account name and password are in the back link amoeba use <p Ropertyname= "Password" >123456</property> <property name= "filter"; ...... ..... ..... ... <property name= "lrumapsize" >1500<, ... ..... ..............................-----------------------------" /property> <property name= "Defaultpool" >master</property>//modified to Master//NOTE: The original note here Remove <property name= "Writepool" >master</property>//Modify to Master <prope Rty name= "Readpool" >slaves</property>//modified to slaves <property name= "Needparse" &GT;TRUE&LT;/PR Operty> Edit DBSERVERS.XML[[EMAIL&NBSp;protected]]# vim conf/dbservers.xml <!--mysql schema--<property name= "user" >l Iang</property>//Before setting Open permissions username and password//Note Delete Comment for this location <property name= "Password" &G t;123456</property>//Note Remove the comment for this position </factoryConfig>

... .... ... ... ... ... .... ... .... ... .... ... .... ... .... ..... .... ..... ..... ..... ..... ..... ..... ..... ..... ...........

<dbserver name= "Master" parent= "Abstractserver" >//Modify to Master <factoryConfig> <!--mysql IP--&        Gt <property name= "ipAddress" >192.168.1.30</property>//Modify IP </factoryConfig></dbServer><        DBServer name= "slave1" parent= "Abstractserver" >//Modified to slave1 <factoryConfig> <!--mysql IP-- <property name= "ipAddress" >192.168.1.40</property>//Modify IP </factoryConfig> &LT;/DBSERVER&G  T;<dbserver name= "Slave2" parent= "Abstractserver" >//Copy, modify to Slave2 <factoryConfig> <!--MySQL IP--<property name= "ipAddress" >192.168.1.50</property>//Modify IP </factoryconfig></dbs Erver><dbserver name= "Slaves" virtual= "true" >//modified to slaves <poolconfig class= "Com.meidusa.amoeba.ser Ver. Multipleserverpool "> <!--Load balancing Strategy:1=roundrobin, 2=weightbased, 3=ha--> <prope Rty name= "LoadBalance ">1</property> <!--separated by Commas,such As:server1,server2,server1--<prope Rty name= "Poolnames" >slave1,slave2</property>//Modify to Slave1,slave2 </poolConfig></dbServer> </amoeba:dbServers>

.
.
Start Amoeba Software

[[email protected] ]#   bin/amoeba start&[[email protected] ]#  netstat -anpt | grep java

.

Test read/write separation
Open a client 192.168.1.10, also need to install MySQL, as a test machine, you can use the Yum-y install MySQL installation.
.

建立防火墙规则[[email protected] /]# iptables -I INPUT -p tcp --dport 8066 -j ACCEPT.amoeba，主服务器和两个从节点，都需要开放3306端口入站[[email protected] /]# iptables -I INPUT -p tcp --dport 3306 -j ACCEPT[[email protected] /]# service iptables save

.
You can also modify the amoeba amoeba.xml configuration file after you change the first 8066 to 3306 to establish a firewall rule to allow 3306 ports to inbound and restart the Amoeba service
<property name= "Port" >3306</property>//in 11 rows
So when the test machine is accessed, it doesn't have to be with P 8066.
.

重启服务[[email protected] ]#   bin/amoeba restart&

Testing phase
Client

[[email protected] ]#  mysql -u amoeba -p 123456 -h 192.168.1.20 P 8066密码123456，为：之前登陆amoeba设置的密码

Create a database Wlzs on the master server master, synchronize to each slave server, then turn off the slave function from the server, and then insert the data.
(Primary server)

mysql> create database WLGCSZS;Query OK, 1 row affected (0.84 sec)

mysql> use WLGCSZS;Database changed

mysql> create table student(id int,name char(10));Query OK, 0 rows affected (1.39 sec)

.
(View synchronization from the server)

mysql> show databases;+--------------------+| Database           |+--------------------+| information_schema || WLGCSZS           || IT                     || mysql                 || performance_schema || test               |+--------------------+5 rows in set (0.27 sec)

.
(master server writes data)

mysql> insert into student values(1,‘li‘);Query OK, 1 row affected (0.48 sec)

.
(also synchronized from the server)

mysql> use WLGCSZS;Database changedmysql> select * from student;+------+------+| id   | name |+------+------+|    1 | li   |+------+------+1 row in set (0.45 sec)

.
The stop slave is executed on two slave servers
Write different data from the server in two separate sets
(Node A)

mysql> stop slave;Query OK, 0 rows affected (0.05 sec)mysql> use WLGCSZS;Database changedmysql> insert into student values(5,‘jia‘);Query OK, 1 row affected (0.07 sec)

.
(Node B)

mysql> stop slave;Query OK, 0 rows affected (0.05 sec)mysql> use WLGCSZS;Database changedmysql> insert into student values(6,‘liang‘);Query OK, 1 row affected (0.07 sec)

(client query)

mysql> use WLGCSZS;Database changedmysql> select * from student;   //第一次查询显示第一台从服务器的用户+------+------+| id   | name |+------+------+|    1 | li        ||    4 | jia      |+------+------+2 rows in set (0.00 sec)

mysql> select * from student;   //第一次查询显示第二台从服务器的用户，说明负载均衡成功+------+----------+| id   | name     |+------+----------+|    1 | li            ||    5 | liang      |+------+----------+2 rows in set (0.00 sec)

(client writes a statement)

mysql> insert into student values(6,‘asdf‘);Query OK, 1 row affected (0.05 sec)

.
Cannot query the statement that was just written, only the primary server can find out because the write operation only Master has two other responsible read data

mysql> select * from student;+------+------+| id   | name |+------+------+|    1 | jia     ||    4 | abcd |+------+------+2 rows in set (0.00 sec)

(client query)

mysql> use WLGCSZS;mysql> select * from student;+------+-------+| id   | name  |+------+-------+|    1 | li     ||    5 | jia   ||    6 | liang |+------+-------+4 rows in set (0.00 sec)

MySQL master-slave replication and read/write separation