MySQL Simple master-slave replication

Physical machine win7, virtual machine for two CENTOS7 systems

Experiment MYSLQ Database Simple master-slave replication:

Master node: 192.168.255.2

From node: 192.168.255.3

Mariadb-server installed with RPM package

To configure the master node:

]# vim/etc/my.cnf

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/82/89/wKioL1dYNKyxf3YqAAB0_9q2R_M135.png "title=" 1.png " alt= "Wkiol1dynkyxf3yqaab0_9q2r_m135.png"/>

]# systemctl Start Mariadb.service

Ensure that the iptables of the primary node does not block port 3306;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/82/8A/wKiom1dYNdGAed5EAAAYnV1dxbU991.png "title=" 5.png " alt= "Wkiom1dyndgaed5eaaaynv1dxbu991.png"/>

> SHOW GLOBAL VARIABLES like '%log% ';

Confirm that the binary log is started;

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/82/89/wKioL1dYNVTjp1BlAAB4CCCYDQE826.png "title=" 2.png " alt= "Wkiol1dynvtjp1blaab4cccydqe826.png"/>

View the server ID

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/82/89/wKioL1dYNdrR5q8mAAAcRm8FHnI906.png "title=" 4.png " alt= "Wkiol1dyndrr5q8maaacrm8fhni906.png"/>

Create an account with copy permissions:

> GRANT REPLICATION slave,replication CLIENT on * * to ' repluser ' @ ' 192.168.255.% ' identified by ' replpass ';

> FLUSH privileges;

View binary logs and log location:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/82/89/wKioL1dYPuHxRdgLAAAV9hjAf7U017.png "title=" 3.png " alt= "Wkiol1dypuhxrdglaaav9hjaf7u017.png"/>

Configure the slave node:

]# vim/etc/my.cnf

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M00/82/89/wKioL1dYN4_BFJS6AABMXMyI8FQ098.png "title=" 6.png " alt= "Wkiol1dyn4_bfjs6aabmxmyi8fq098.png"/>

View trunk log on;

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M01/82/89/wKioL1dYOC6z9R82AAB__GenPsA306.png "title=" 7.png " alt= "Wkiol1dyoc6z9r82aab__genpsa306.png"/>

View Unique ID:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/82/8A/wKiom1dYN2ewS8vMAAAmmYlLSUQ654.png "title=" 8.png " alt= "Wkiom1dyn2ews8vmaaammyllsuq654.png"/>

On the slave node:

Connect to the master node using a user account with copy permissions and start the replication thread;

Specify the binary log file and location:

> Change MASTER to master_host= ' 192.168.255.2 ', master_user= ' Repluser ', MA

Ster_password= ' Replpass ', master_log_file= ' master-bin.000003 ', master_log_pos=499;

To start the Copy from node feature:

> START SLAVE;

To view the status information from a node:

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M02/82/89/wKioL1dYP4PgLc_8AAEDTX7l9Uo544.png "title=" 9.png " Width= "737" height= "810" border= "0" hspace= "0" vspace= "0" style= "width:737px;height:810px;" alt= "wkiol1dyp4pglc_ 8aaedtx7l9uo544.png "/>

At this point, the master-slave replication configuration is complete, and the primary node has any write transaction log records, which will be synchronized to the slave node and replay from the node;

Verify Master-slave replication:

For example: Create a library on the master node:

The library information in the previous master-slave node is created by default at the beginning of the library:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M00/82/8B/wKiom1dYP-vTQlG5AAAUnCmkdXA822.png "title=" 10.png "alt=" Wkiom1dyp-vtqlg5aaauncmkdxa822.png "/>

To create a new library on the master node:

> CREATE DATABASE mydb;

To view status information:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M00/82/89/wKioL1dYQa_Sf22jAAAWbTSCL6M875.png "title=" 12.png "Width=" 703 "height=" 133 "border=" 0 "hspace=" 0 "vspace=" 0 "style=" width:703px;height:133px; "alt=" Wkiol1dyqa_ Sf22jaaawbtscl6m875.png "/>

The transaction location in the binary log has changed, and the node should have been synchronized;

On the From node view:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M00/82/89/wKioL1dYQUHD8N6PAAAjUVW5jmU366.png "title=" 11.png "alt=" Wkiol1dyquhd8n6paaajuvw5jmu366.png "/>

View from node State:

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M01/82/8B/wKiom1dYQeDCNlrHAACezrKRtOs690.png "title=" 13.png "alt=" Wkiom1dyqedcnlrhaacezrkrtos690.png "/>

At this point, also set from the server can not be write operations, because only the primary node to accept the write operation, from the node is responsible for read requests, all write operations must be copied from the primary node to the local relay log replay after completion:

The settings are read-only from the node:

> SET GLOBAL read_only=on;

650) this.width=650; "src=" Http://s2.51cto.com/wyfs02/M01/82/89/wKioL1dYQ-GBFyY3AAAVBVuWx6E488.png "title=" 14.png "alt=" Wkiol1dyq-gbfyy3aaavbvuwx6e488.png "/>

On the slave node:

To view files:

]# Cat/var/lib/mysql/master.info

650) this.width=650; "src=" Http://s1.51cto.com/wyfs02/M02/82/8B/wKiom1dYRFPAD5-4AAByfDpv12E455.png "title=" 15.png "alt=" Wkiom1dyrfpad5-4aabyfdpv12e455.png "/>

This file is why you can set the boot automatically after the start of the replication function of important files;

To view the trunk log files from a node:

650) this.width=650; "src=" Http://s4.51cto.com/wyfs02/M02/82/89/wKioL1dYRx7SeqtAAABTfzvxUhs285.png "title=" 16.png "alt=" Wkiol1dyrx7seqtaaabtfzvxuhs285.png "/>

Master node: guaranteed to be updated from node in time

This should be set:

650) this.width=650; "src=" Http://s5.51cto.com/wyfs02/M01/82/8B/wKiom1dYSCTwFKNgAACRyn2hyR4335.png "title=" 17.png "alt=" Wkiom1dysctwfkngaacryn2hyr4335.png "/>

From node:

Relay_log will also buffer in memory, so that each write is immediately synchronized to disk, data security increased, but the disk IO pressure, performance will be reduced;

Also set the following parameters:

650) this.width=650; "src=" Http://s3.51cto.com/wyfs02/M01/82/89/wKioL1dYSkKDkysVAABRqYVtPN4863.png "title=" 18.png "alt=" Wkiol1dyskkdkysvaabrqyvtpn4863.png "/>

The demo is complete.

MySQL Simple master-slave replication