Master-slave replication and primary master replication for MySQL database

MysqlMaster-Slave Architecture Technical Description

Mysql's built -in replication capabilities are the foundation for building large, high-performance applications. the distribution of MySQL data to multiple systems is done by copying the data from one of MySQL 's hosts (master) to other hosts (slaves). and re-run it again to achieve it. One server acts as the primary server during replication, while one or more other servers act as slave servers. The primary server writes updates to the binary log files, which can log updates sent to the server. When a primary server is connected from the server, it notifies the primary server where the last successful update was read from the server in the log. Receive any updates from the server from then on, and then block and wait for the primary server to notify the new updates.

database replication Features

MySQL replication Technology has some of the following features:

(1) data distribution (datadistribution)

(2) load Balancing (load Balancing)

(3) backup (Backups)

(4) high availability and fault tolerance highavailability and failover

MysqlHow replication works

1, the first part of the process is the master record binary log. Master Records These changes in binary logging before each transaction updates the data. MySQL writes the transaction serially to the binary log, and when the event is written to the binary log completion,Master notifies the storage engine to commit the transaction. You can then receive slave requests.

2, the next step isSlavewill beMasterof theBinary logcopy it to its own trunk log. First,Slavestart a worker thread--/ othread. / oThread inMasteropen a normal connection, and then start on the master nodeBinlog Dump Process(binary dump thread). Binlog Dump ProcessfromMasterthe binary log reads the event if it has been followedMaster, it will sleep and waitMastergenerate a new event. / othreads Write these events to the relay log.

3 . SQL slave thread(SQL slave thread) handles the last step of the process. 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. Only If the thread is consistent with the I/O thread, the trunk log is typically located in the cache of the OS, so the overhead of the trunk log is minimal.

Master-Slave Replication experiment

Lab Environment:

CentOS System Server 2 , a user to do mysql master server, one for MySQL from the server, configured yum source, firewall shutdown, each node clock service synchronization , each node can communicate with each other through the host name. I used CENTOS7 in the experiment.

Mysql master server: 192.168.182.173

MySQL slave server : 192.168.182.174

Experimental steps:

One: Configure the primary server

1. Modify the configuration file

Vim/etc/my.cnf

Server-id=1 # Configure Server-idto have a unique ID number for the primary server

Log-bin=mysql-bin # Open mysql log, log format is binary

Skip-name-resolve # Turn off name resolution (not required)

2. View Primary Server Status

in the Master 's database executes show master status, viewing the primary server binary log status

650) this.width=650; "src=" Https://s1.51cto.com/oss/201711/21/02f822560452a577638d060771d97ff1.png "title=" 2.png " Style= "WIDTH:500PX;HEIGHT:102PX;" width= "vspace=" 0 "hspace=" 0 "height=" 102 "border=" 0 "alt=" 02f822560452a577638d060771d97ff1.png "/>

3. Create a Copy Account

in the Create a backup account in the master database: each slave uses the standard MySQL username and password to connect to master. The user who makes the copy operation grants replication slave permissions.

Grant Replication Slave on * * to ' slave ' @ ' 192.168.182.174 ' identified by ' magedu ';

Two: Configure Slave server

1. Modify the configuration file

Vim/etc/my.cnf

server-id=2 #配置server-id to have a unique ID number from the server

Relay_log = Mysql-relay-bin #打开Mysql日志, log format is binary

read_only = 1 # Set read-only permissions

Log_bin = mysql-bin # turn on the binary log from server

log_slave_updates = 1 # causes updated data to be written into the binary log

2. Start the replication thread from the server

let slave connect to masterand begin to redo the events in the master binary log.

Change MASTER to master_host= ' 192.168.222.128 ',

Master_user= ' slave ',

Master_password= ' magedu ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=245;

execute start slave;# starts the replication thread.

3. View Slave Server Status

can be used Show SLAVE status\g View the current replication status from the server status, as shown below, also available in show Processlist \g:

Slave_io_running:yes #IO thread is running properly

Slave_sql_running:yes #SQL thread is running properly

650) this.width=650; "src=" Https://s5.51cto.com/oss/201711/21/692e1f17aa851c44fcdf47b8113239db.png "title=" 3.png " Style= "WIDTH:500PX;HEIGHT:187PX;" width= "vspace=" 0 "hspace=" 0 "height=" 187 "border=" 0 "alt=" 692e1f17aa851c44fcdf47b8113239db.png "/>

Then you can test it.

Create a table named Xiaomi on the primary server, you can see on the primary server, if you can also see from the server, the data is synchronized, the implementation is successful.

Primary server:

650) this.width=650; "src=" Https://s3.51cto.com/oss/201711/21/1e0ec7baa374fe2568aff9461549a76f.png "title=" 4.png " Style= "width:400px;height:221px;" width= "vspace=" 0 "hspace=" 0 "height=" 221 "border=" 0 "alt=" 1e0ec7baa374fe2568aff9461549a76f.png "/>

From the server:

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/21/5b1f64fd9e4adf8b60118884c6b63122.png "title=" 5.png " Style= "WIDTH:400PX;HEIGHT:206PX;" width= "vspace=" 0 "hspace=" 0 "height=" 206 "border=" 0 "alt=" 5b1f64fd9e4adf8b60118884c6b63122.png "/>

Primary master replication

in fact, the main master is mainly from each other, mainly from each other: each of the two nodes should open Binlog and relay log;

1, inconsistent data;

2, automatic growth ID;

Define a node with an odd ID

auto_increment_increment=2# represents the amount of each increment from the growth field

auto_increment_offset=1# indicates that the self-growing field starts with that number

The other node uses an even ID

auto_increment_increment=2

auto_increment_offset=2

Experiment: Primary master replication

On the basis of the above master-slave experiment, the main master replication is realized.

Primary server: 192.168.182.174

From server: 192.168.136.173

One: Configure the primary server

Vim/etc/my.cnf

server-id=2 # Configure Server-idto have a unique ID number for the primary server

Log-bin=mysql-bin # Open mysql log, log format is binary

Relay_log=mysql-relay-log

auto_increment_increment=2
auto_increment_increment=2

650) this.width=650; "src=" Https://s4.51cto.com/oss/201711/21/3c309cd39f22b392a13b38b32bd446cc.png "title=" 6.png " Style= "WIDTH:300PX;HEIGHT:138PX;" width= "vspace=" 0 "hspace=" 0 "height=" 138 "border=" 0 "alt=" 3c309cd39f22b392a13b38b32bd446cc.png "/>

2. View Primary Server Status

in the Master 's database executes show master status, viewing the primary server binary log status

3. Create a Copy Account

in the Create a backup account in the master database: each slave uses the standard MySQL username and password to connect to master. The user who makes the copy operation grants replication slave permissions.

Grant Replication Slave on * * to ' slave ' @ ' 192.168.182.174 ' identified by ' magedu ';

Two: Modify the configuration file from the server

1.vim/etc/my.cnf

server-id=2 #配置server-id, let the primary server have a unique ID number

Log-bin=mysql-bin # Open mysql log, log format is binary

Relay_log=mysql-relay-log

auto_increment_increment=2
auto_increment_increment=2

2. Start the replication thread from the server

let slave connect to masterand begin to redo the events in the master binary log.

Change MASTER to master_host= ' 192.168.182.174 ',

Master_user= ' slave1 ',

Master_password= ' Magedu1 ',

Master_log_file= ' mysql-bin.000001 ',

master_log_pos=245;

execute start slave;# starts the replication thread.

3. View Slave Server Status

can be used Show SLAVE status\g View the current replication status from the server status, as shown below, also available in show Processlist \g:

Slave_io_running:yes #IO thread is running properly

Slave_sql_running:yes #SQL thread is running properly

Test: The other can synchronize the data regardless of which server the database is created in.

This article is from the "13162732" blog, please be sure to keep this source http://13172732.blog.51cto.com/13162732/1983889

Master-slave replication and primary master replication for MySQL database