Master-Slave Synchronization

Experimental preparation

Primary Server Settings

Installation Time Synchronization Service

[root@master ~]# yum install ntp -y
#Modify ntp profile
[root@master ~]# vim /etc/ntp.conf  
    server 127.127.195.0        //Local clock source, 195 paragraphs//
    fudge 127.127.195.0 stratum 8       //Set the time level to 8//
###Open Service
[root@master ~]# systemctl start ntpd

Modify MySQL Main Profile

[root@master ~]# vim /etc/my.cnf
    server-id 10
    log-bin=zhu-bin
    log-slave-updates=ture    ##Turn on master-slave synchronization
[root@master ~]# systemctl restart mysqld
[root@master ~]# systemctl stop firewalld

Enter database modification

[root@master ~]# mysql -u root -p
grant replication slave on *.* to 'myslave'@'192.168.142.%' identified by 'asd123';
    #Allow replication from the server using the myslave account on the primary server
show master status;   #View Master Server Information
mysql> mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000005 |      868 |              |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

From Server Settings

Install Synchronization Service Client ntpd

[root@slave ~]# yum install ntp ntpdate -y

#Open Service Off Firewall
[root@slave ~]# systemctl start ntpd
[root@slave ~]# systemctl stop firewalld
##Perform time synchronization
[root@slave ~]# /usr/sbin/ntpdate home server address

Modify MySQL Main Profile

[root@slave ~]# vim /etc/ntp.conf
    server-id = 20
    relay-log = relay-bin      ###Synchronize log files locally
    relay-log-index = slave-bin.index    ##Define log file type and name
[root@slave ~]# systemctl restart mysqld

Enter database modification

[root@slave ~]# mysql -u root -p123123
change master to master_host='192.168.142.135',master_user='myslave',master_password='asd123',master_log_file='mysql-bin.000005',master_log_pos=865;
    ##Grant privileges
start slave;
###Is Query Synchronization Successful
show slave status\G
*************************** 1. row ***************************
               Slave_IO_State: Reconnecting after a failed master event read
                  Master_Host: 192.168.142.132
                  Master_User: myslave
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000004
          Read_Master_Log_Pos: 1633
               Relay_Log_File: master1-relay-bin.000011
                Relay_Log_Pos: 1126
        Relay_Master_Log_File: mysql-bin.000004
             Slave_IO_Running: Yes        ##IO Connection
            Slave_SQL_Running: Yes        ##SQL Connection
            ......

PS:

Sometimes master-slave synchronization encounters Slave_SQL_Running: NO problem, here are the solutions:

####Slave_SQL_Running: No Problem Solution
MariaDB [(none)]> stop slave;                                                       
MariaDB [(none)]> SET GLOBAL SQL_SLAVE_SKIP_COUNTER=1; START SLAVE;
MariaDB [(none)]> start slave; 
MariaDB [(none)]> show slave status\G 

At this point, the MySQL master-slave synchronization has been completed. Here is the start of the static and dynamic separation

MySQL Read-Write Separation

Experimental preparation

Set up amoeba server

Install jdk environment (amoeba was developed by java)

[root@amoeba ~]# systemctl stop firewalld.service   #Close Firewall
[root@amoeba ~]# setenforce 0
[root@amoeba mnt]# cp -p jdk-6u14-linux-x64.bin /usr/local/  #Move pre-prepared jdk packages
[root@amoeba mnt]# cd /usr/local/
[root@amoeba local]# ./jdk-6u14-linux-x64.bin       #Install jdk environment
[root@amoeba local]# mv jdk1.6.0_14/ jdk1.6
##Configuring jdk environment variables
[root@amoeba local]# vim /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
[root@amoeba local]# source /etc/profile

Unzip and install the amoeba Toolkit

[root@amoeba local]# mkdir amoeba
[root@amoeba local]# tar zxf /mnt/amoeba-mysql-binary-2.2.0.tar.gz -C /usr/local/amoeba   #Unzip a pre-prepared Toolkit
[root@amoeba local]# chmod -R 755 amoeba/    #Grant privileges
##Check for successful installation
[root@amoeba local]# /usr/local/amoeba/bin/amoeba
amoeba start|stop     ##The following command line appears to be successful

Modify the main configuration file amoeba.xml

[root@amoeba local]# vim amoeba/conf/amoeba.xml
##Modify as shown below
#(Increase client access to amoeba account, password)
<property name="user">amoeba</property>
<property name="password">123123</property>
---------------------------------------------------------
#(Open default pool, read pool, write pool)
<property name="defaultPool">master</property>
<property name="writePool">master</property>
<property name="readPool">slaves</property>

Modify amoeba's database configuration file dbserver.xml

[root@amoeba local]# vim amoeba/conf/dbServers.xml
## Modify as shown below
#(Modify amoeba access node server account, password)
<!-- mysql user -->
    <property name="user">test</property>
<!--  mysql password  -->
    <property name="password">asd123</property>
-------------------------------------------------------------
#(Specify master and slave server node addresses)
<dbServer name="master"  parent="abstractServer">
    <property name="ipAddress">192.168.142.132</property>
<dbServer name="slave"  parent="abstractServer">
    <property name="ipAddress">192.168.142.136</property>
--------------------------------------------------------------
#(Delete from server into read address pool)
<dbServer name="slaves" virtual="true">
    <property name="poolNames">slave</property>

Return to master and slave server database

##Open access on primary and secondary servers
[root@slave ~]# mysql -u root -p123123     #Enter the master server database (same operation from the server as from the master server)
grant all on *.* to test@'192.168.142.%'identified by 'asd123';  #Open test user access

Open amoeba server

[root@amoeba conf]# /usr/local/amoeba/bin/amoeba start &   #Input Background Continuous Run

Application Client

With MySQL installed, enter mysql-u amoeba-p123123-h 192.168.142.142-P8066 to access the amoeba server successfully.

In this case, the master server will be used for write and slave for read and write operations.