MySQL master-slave, main master, MySQL ha cluster: MYSQL-MHA

Introduction: Principle and mechanism of master-slave replication

to implement replication, you must first open The binary log(bin-log) function on the Master side , otherwise it cannot be implemented. Because the entire replication process is actually a variety of operations that are logged in the execution log that Slave obtains the log from the Master side and then executes it in its own full sequence.

The basic process for replication is as follows:

1),Slave above the io_thread connection on Master, and request from the specified log file at the specified location (or from the beginning of the log) after the contents of the log;

2) , Master received from Slave Io_thread Slave master

3),Slave io_thread received the message, the received log content is added to the Slave end of the relay-log file, and will read to the end of the the file name and location of the Bin-log on the master side are recorded in the Master-info file so that the next read will tell Master clearly that I need to Bin-log where to start the log content, please send me ";

4),Slave sql_thread detected the new content in Relay-log, will immediately parse the contents of Relay-log into The Master side performs the actual execution of those executable content and puts it back on the local computer.

Summary: The most important thing about MySQL master-slave architecture is the Bin_log and Relay_log logs, the global transaction ID

One, MySQL master and slave settings

The primary node opens the binary log, starting from the node to relay the log, from the node if necessary, to not turn on the binary log. Also note that the read-only lock read_only=on must be turned on from the node. If you need advanced operations such as backing up a table, you need to innodb_file_per_table=on each table with a single namespace.

Master 192.168.100.100

650) this.width=650; "src=" http://s2.51cto.com/wyfs02/M00/78/1F/wKiom1Z2dFaQTHzCAAAZTc80_2M084.png "title=" image 201512201.png "alt=" Wkiom1z2dfaqthzcaaaztc80_2m084.png "/>

650) this.width=650; "src=" http://s5.51cto.com/wyfs02/M01/78/20/wKiom1Z2ezfCZnkcAAAZVu4Ux_c629.png "title=" image 201512205.png "alt=" Wkiom1z2ezfcznkcaaazvu4ux_c629.png "/>

Create a Super Administrator

GRANT all on * * to ' test-001 ' @ ' 192.168.%.% ' identified by ' 123 ';

Create Replication Rights user

GRANT REPLICATION SLAVE, REPLICATION CLIENT on * * to ' test-002 ' @ ' 192.168.%.% ' identified ' test-002 '; note: to show Ma before creating a user Ster status so that the created account can also be synced to the slave node.

Slave

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M00/78/20/wKiom1Z2eYjh7lRCAAAZ_77C204747.png "title=" image 20151220001.png "alt=" Wkiom1z2eyjh7lrcaaaz_77c204747.png "/>

To turn off binary logs from a node, it is completely unnecessary to save the binary log of the synchronization process

SET Sql_log_bin=off; SHOW VARIABLES like ' sql_log% ';

Establish contact with master from the service

Change MASTER to master_host= ' 192.168.100.100 ', master_user= ' test-002 ', master_password= ' test-002 ', master_log_file= ' mysql-bin.000003 ', master_log_pos=245;

Starting SQL and IO threads

START SLAVE;

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M02/78/20/wKiom1Z2fo_jTYWlAAAplayH3tQ498.png "title=" image 201512206.png "alt=" Wkiom1z2fo_jtywlaaaplayh3tq498.png "/>

To apply a global lock to a Slave node:

FLUSH TABLES with READ LOCK;

MySQL master and slave settings are complete;

viewing master-Slave Replication latency

SHOW SLAVE statusseconds_behind_master:0 How long it takes to fall behind the main service

Deferred processing: A semi-synchronous or grayscale backup restores re-replication.

Second, semi-synchronous settings (based on the master-slave basis)

Semi-synchronous replication takes advantage of plug-ins

/usr/lib64/mysql/plugin/semisync_master.so

/usr/lib64/mysql/plugin/semisync_slave.so

[Mysqld] is not set by default

Plugin_dir=/path/to/plugin/directory Plugin Directory settings

Master

Loading plugins

mysql> INSTALL PLUGIN rpl_semi_sync_master SONAME ' semisync_master.so ';

Check the plug-in loading situation

Show plugins;

Rpl_semi_sync_master | ACTIVE | REPLICATION | semisync_master.so | Gpl

Enable the master node for version Synchronization

mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;

Mysql> SHOW GLOBAL VARIABLES like '%semi% ';

Mysql> SHOW GLOBAL STATUS like '%semi% ';

MariaDB [(None)]> show variables like '%semi% ';

+------------------------------------+-------+

| variable_name | Value |

+------------------------------------+-------+

| rpl_semi_sync_master_enabled | On | Enable the Master node for version Synchronization

| Rpl_semi_sync_master_timeout | 10000 |

| Rpl_semi_sync_master_trace_level | 32 | Trace Level 32

| Rpl_semi_sync_master_wait_no_slave | On | Allows master to wait for the receipt signal of slave after each thing is committed.

+------------------------------------+-------+

Slave

mysql> INSTALL PLUGIN rpl_semi_sync_slave SONAME ' semisync_slave.so ';

mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;

Test

Close from node, create a database, observe

650) this.width=650; "src=" http://s1.51cto.com/wyfs02/M01/78/23/wKiom1Z2jgWTkHOUAABA-9czXQU769.png "title=" image 201512208.png "alt=" Wkiom1z2jgwtkhouaaba-9czxqu769.png "/>

Observing the state on the slave node

650) this.width=650; "src=" http://s3.51cto.com/wyfs02/M00/78/23/wKiom1Z2jnODUjoXAAAeVhELHng585.png "title=" image 201512209.png "alt=" Wkiom1z2jnodujoxaaaevhelhng585.png "/>

If this problem occurs, the solution

Master

mysql> SET GLOBAL rpl_semi_sync_master_enabled=1;

Slave

mysql> SET GLOBAL rpl_semi_sync_slave_enabled=1;

Mysql> STOP SLAVE;

Mysql> START SLAVE;

This article is from the "16 Stage Squirrel & Frog" blog, please be sure to keep this source http://tlinux.blog.51cto.com/7288656/1726589

MySQL master-slave, main master, MySQL ha cluster: MYSQL-MHA