MySQL AB Replication

About MySQL AB Replication

This article describes how to quickly package and install MySQL, MySQL AB replication, MySQL AB bidirectional replication, and MySQL multi-level master-slave replication to solve the primary key conflict of AB bidirectional replication.

 

First, we will introduce what is MySQL AB replication.

 

AB replication, also known as Master-slave replication, implements data synchronization. If you want to copy MySQL AB, make sure that the database version is consistent. If the version is different, the slave server version is later than the master server, but the version is inconsistent and cannot be used for Bidirectional replication. What are the benefits of MySQL AB replication? There are two points: the first is to solve the data inconsistency caused by downtime, because MySQL AB replication can back up data in real time; the second is to reduce the pressure on the database server, which is easy to think, the performance of multiple servers is generally better than that of a single server. However, MySQL AB replication is not applicable to large data volumes. For Big Data environments, we recommend that you use clusters.

 

Next, let's take a look at the three main steps of MySQL replication:

1) the master server records data changes to binary logs. This operation is called a binary log event;

2) The slave server copies the binary log events of the master server to its relay log;

3) execute the events in the relay log from the server and apply the changes to your own data.

 

Quickly package and install MySQL

Before introducing MySQL AB replication, we will introduce how to package MySQL and install MySQL quickly.

 

Step 1: create a file

[root@serv08 ~]# find /usr/local/mysql/ /etc/my.cnf /etc/init.d/mysqld > mysql

Step 2: Package

[root@serv08 ~]# tar -cPvzf mysql-5.5.29-linux2.6-x86_64.tar.gz -T mysql [root@serv08 ~]# ll -htotal 202M-rw-r--r--. 1 root root 411K Oct  5 19:19 mysql-rw-r--r--. 1 root root 202M Oct  5 19:21 mysql-5.5.29-linux2.6-x86_64.tar.gz

Step 3: Copy files to the physical machine

[root@serv08 mysql]# scp mysql-5.5.29-linux2.6-x86_64.tar.gz 192.168.1.1:/home/Wentasy/software/

Step 4: copy the file to serv01

[root@serv01 ~]# yum install /usr/bin/scp -y[root@larrywen 1005]# scp /home/Wentasy/software/mysql-5.5.29-linux2.6-x86_64.tar.gz 192.168.1.11:/optroot@192.168.1.11's password: mysql-5.5.29-linux2.6-x86_64.tar.gz                      100%  201MB  33.5MB/s   00:06 

Step 5: Decompress

[root@serv01 opt]# tar -xPvf mysql-5.5.29-linux2.6-x86_64.tar.gz

Step 6: Create a group and a user. Make sure that the ID is consistent with the user on the machine where the database is installed.

[root@serv01 opt]# groupadd -g 500 mysql[root@serv01 opt]# useradd -u 500 -g 500 -r -M -s /sbin/nologin mysql [root@serv01 opt]# id mysqluid=500(mysql) gid=500(mysql) groups=500(mysql)

Step 7: Change the owner and group of the MySQL installation directory

[root@serv01 opt]# chown mysql.mysql /usr/local/mysql/ -R

Step 8: Start MySQL for testing

[root@serv01 opt]# /etc/init.d/mysqld startStarting MySQL.. SUCCESS! [root@serv01 opt]# mysql-bash: mysql: command not found[root@serv01 opt]# vim ~/.bash_profile [root@serv01 opt]# . !$. ~/.bash_profile[root@serv01 opt]# mysqlWelcome to the MySQL monitor.  Commands end with ; or \g.Your MySQL connection id is 1Server version: 5.5.29-log Source distributionCopyright (c) 2000, 2012, Oracle and/or its affiliates. All rights reserved.Oracle is a registered trademark of Oracle Corporation and/or itsaffiliates. Other names may be trademarks of their respectiveowners.Type 'help;' or '\h' for help. Type '\c' to clear the current input statement.mysql> 

MySQL AB unidirectional Replication

Now, I believe that you have learned how to package MySQL and install MySQL quickly. Next, we will officially enter the topic. Let's take a look at the topology of a master-slave architecture:

Figure 1 master-slave architecture

This figure shows the architecture of a master node that replicates multiple slave instances. The implementation of multiple slave instances and a single slave does not differ substantially. The master node does not care how many slave instances are connected to itself, as long as the slave IO thread passes connection authentication and requests the binary log information after the specified location, it will read its binary log information according to the objective of the IO thread, the IO thread returned to slave.

For more details, please continue to read the highlights on the next page:

Recommended reading:

MySQL backup and AB Replication

Load Nginx in Ubuntu for high-performance WEB Server 5 --- MySQL master/Master Synchronization

Production Environment MySQL master/Master synchronization primary key conflict handling

MySQL Master/Slave failure error Got fatal error 1236

MySQL master-slave replication, implemented on a single server

1 2 3 4 5 6 Next Page